Drop support for SQLite < 3.8.3

[Tim Graham]

#30054 requires emulating a TRUNCATE CASCADE which requires a recursive retrieval of foreign key constraints. Recursive CTEs are only supported on SQLite 3.8.3+.

Ubuntu Trusty ships with SQLite 3.8.2 and will EOL in April 2019. Debian Jessie ships with 3.8.7+.

SQLite 3.8.2 was released in December 2013. SQLite version support seems like a similar situation as GEOS libraries which we generally support about 5 years after released.

(​discussion)

[Tim Graham]

​PR

[Tim Graham <timograham@…>]

In d5af14aa:

Fixed #30055 -- Dropped support for SQLite < 3.8.3.

[Tim Graham]

I think I ran into this when adding Django 2.2 testing to django-hosts. Travis CI still uses trusty (Ubuntu 14.04) by default which has SQLite 3.8.2. All the tests ​failed like this:

self = <django.db.backends.utils.CursorWrapper object at 0x2b19ed9ba9b0>
sql = '\n                            SELECT REFERRING.`id`, REFERRING.`site_id` FROM `tests_author` as REFERRING\n          ...                         WHERE REFERRING.`site_id` IS NOT NULL AND REFERRED.`id` IS NULL\n                            '
params = None
ignored_wrapper_args = (False, {'connection': <django.db.backends.sqlite3.base.DatabaseWrapper object at 0x2b19ed23a390>, 'cursor': <django.db.backends.utils.CursorWrapper object at 0x2b19ed9ba9b0>})
    def _execute(self, sql, params, *ignored_wrapper_args):
        self.db.validate_no_broken_transaction()
        with self.db.wrap_database_errors:
            if params is None:
>               return self.cursor.execute(sql)

and

self = <django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x2b19ed99d1f8>
query = '\n                            SELECT REFERRING.`id`, REFERRING.`site_id` FROM `tests_author` as REFERRING\n          ...                         WHERE REFERRING.`site_id` IS NOT NULL AND REFERRED.`id` IS NULL\n                            '
params = None
    def execute(self, query, params=None):
        if params is None:
>           return Database.Cursor.execute(self, query)
E           sqlite3.OperationalError: no such table: django_site

I think we should either warn about this in the documentation (e.g. if you get this error, check your SQLite version) or add a SQLite version check somewhere to show a helpful message.

I thought the issue was an unsupported query in ​DatabaseWrapper.check_constraints() but I bisected the failures to 7289874adceec46b5367ec3157cdd10c711253a0.

More investigation might be needed to ensure that SQLite 3.8.3 resolves this issue.

[Simon Charette]

I think we should either warn about this in the documentation (e.g. if you get this error, check your SQLite version) or add a SQLite version check somewhere to show a helpful message.

I think that at this point error'ing loudly when using SQLite < 3.8.3 might be the best course of action, I foresee a lot of reports even if we document it.

More investigation might be needed to ensure that SQLite 3.8.3 resolves this issue.

Agreed, it looks like yet another edge case might be at play here.

[Tim Graham]

The error above also reproduces on Ubuntu 16.04 (SQLite 3.11). I'll investigate later and follow up elsewhere as it doesn't seem related to this ticket.

[Simon Charette]

It's really odd that it fails on manual constraint checking as connection.introspection.table_names should only return existing table names (thus django_site should exist).

Also ​constraint checking should not be performed in the first place during tests on SQLite < 3.20. I wonder if it has to do with something pytest or pytest-django does.

[Tim Graham]

Bisected the issue to 7289874adceec46b5367ec3157cdd10c711253a0. The addition of self.connection.check_constraints() in __exit__() is the cause. ​PR to (re)document (following its improper removal in 7e8cf74dc74539f40f4cea53c1e8bba82791fcb6) that apps without migrations must not have relations to apps with migrations. This discussion should have been on #30033.

[GitHub <noreply@…>]

In 7444f32:

Refs #30055 -- Added a helpful error when SQLite is too old.

[Tim Graham <timograham@…>]

In d3b4f4b9:

[2.2.x] Refs #30055 -- Added a helpful error when SQLite is too old.

Backport of 7444f3252757ed4384623e5afd7dcfeef3e0c74e from master.

[Tim Graham <timograham@…>]

In 4763c971:

Refs #30055 -- Bumped minimum SQLite version in GIS docs.

[Tim Graham <timograham@…>]

In d5b43d7:

[2.2.x] Refs #30055 -- Bumped minimum SQLite version in GIS docs.

Backport of 4763c9719135f4de17924d9c8524f1cfe41bc645 from master.

[Wolph]

Just a little heads-up for others having this issue when running Travis. Simply adding dist: xenial to your .travis.yml will most likely fix the issue for you :)