Error with sites migration when using sqlite backend

[amstilp]

I know that sqlite is not officially supported, so this issue is primarily an FYI for anyone else using the django.db.backends.sqlite3 sqlite backend in initial development. The sites.0003_set_site_domain_and_name migration was updated in a recent commit (b58f0e7). After this change, when trying to run python manage.py migrate to create the database, I get the following error:

  Applying sites.0003_set_site_domain_and_name...Traceback (most recent call last):
  File "/Users/amstilp/devel/django/prelim/django-mocking/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
...
...(lots of traceback output)...
...
  File "/Users/amstilp/devel/django/prelim/django-mocking/venv/lib/python3.9/site-packages/django/db/backends/sqlite3/base.py", line 421, in execute
    return Database.Cursor.execute(self, query)
django.db.utils.OperationalError: no such table: django_site_id_seq

I was able to fix this by using the previous version of the sites.0003_set_site_domain_and_name migration file.

[browniebroke]

The original issue we were trying to address is #3507. The SQL in that migration isn't database agnostic, it's specific to PostgreSQL. This bug was also mentioned on Stackoverflow, where the project was generated from a MySQL fork...

Maybe we could skip this piece of code if the DB backend isn't PostgreSQL, although I'm tempted to say that we only maintain PostgreSQL, and hence using another backend is out of scope here.

[github-actions]

Assuming the question was answered, this will be automatically closed now.

[patvdleer]

@amstilp used the following which so far seems to work;

        max_id = site_model.objects.order_by('-id').first().id

        with connection.cursor() as cursor:
            if connection.vendor == "postgresql":
                cursor.execute("SELECT last_value from django_site_id_seq")
                (current_id,) = cursor.fetchone()
                if current_id <= max_id:
                    cursor.execute(
                        "alter sequence django_site_id_seq restart with %s",
                        [max_id + 1],
                    )
            elif connection.vendor == "mysql":
                cursor.execute("SELECT MAX(id) FROM django_site")
                (current_id,) = cursor.fetchone()
                if current_id <= max_id:
                    cursor.execute(
                        "ALTER TABLE django_site AUTO_INCREMENT=%s",
                        [max_id + 1],
                    )