Opened 6 months ago

Closed 6 months ago

Last modified 2 months ago

#30055 closed Cleanup/optimization (fixed)

Drop support for SQLite < 3.8.3

Reported by: Tim Graham Owned by: Tim Graham
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Simon Charette Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

#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)

Change History (12)

comment:1 Changed 6 months ago by Tim Graham

Has patch: set

comment:2 Changed 6 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In d5af14aa:

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

comment:3 Changed 5 months ago by Tim Graham

Cc: Simon Charette added

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.

comment:4 Changed 5 months ago by 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.

comment:5 Changed 5 months ago by 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.

comment:6 Changed 5 months ago by 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.

comment:7 Changed 5 months ago by 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.

comment:8 Changed 5 months ago by GitHub <noreply@…>

In 7444f32:

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

comment:9 Changed 5 months ago by Tim Graham <timograham@…>

In d3b4f4b9:

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

Backport of 7444f3252757ed4384623e5afd7dcfeef3e0c74e from master.

comment:10 Changed 5 months ago by Tim Graham <timograham@…>

In 4763c971:

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

comment:11 Changed 5 months ago by Tim Graham <timograham@…>

In d5b43d7:

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

Backport of 4763c9719135f4de17924d9c8524f1cfe41bc645 from master.

comment:12 Changed 2 months ago by 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 :)

Note: See TracTickets for help on using tickets.
Back to Top