sqlite: failling drop of column trusted in axes_accessattempt

[Lem]

I tried to install a project which uses django-axes but the step where the database gets initialized failed:

[....]
  Applying axes.0001_initial... OK
  Applying axes.0002_auto_20151217_2044... OK
  Applying axes.0003_auto_20160322_0929... OK
  Applying axes.0004_auto_20181024_1538... OK
  Applying axes.0005_remove_accessattempt_trusted...Traceback (most recent call last):
  File "/home/th/.local/share/virtualenvs/dcugovernor-oaz32AxZ/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/home/th/.local/share/virtualenvs/dcugovernor-oaz32AxZ/lib/python3.10/site-packages/django/db/backends/sqlite3/base.py", line 357, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: error in index axes_accessattempt_trusted_0eddf52e after drop column: no such column: trusted
[...]

Trying to drop the column manually:

└─$ sqlite3 db.sqlite3                                                                                                                                                                                                1 ⨯
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
sqlite> .schema axes_accessattempt
CREATE TABLE IF NOT EXISTS "axes_accessattempt" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "user_agent" varchar(255) NOT NULL, "ip_address" char(39) NULL, "trusted" bool NOT NULL, "http_accept" varchar(1025) NOT NULL, "path_info" varchar(255) NOT NULL, "attempt_time" datetime NOT NULL, "get_data" text NOT NULL, "post_data" text NOT NULL, "failures_since_start" integer unsigned NOT NULL CHECK ("failures_since_start" >= 0), "username" varchar(255) NULL);
CREATE INDEX "axes_accessattempt_user_agent_ad89678b" ON "axes_accessattempt" ("user_agent");
CREATE INDEX "axes_accessattempt_ip_address_10922d9c" ON "axes_accessattempt" ("ip_address");
CREATE INDEX "axes_accessattempt_trusted_0eddf52e" ON "axes_accessattempt" ("trusted");
CREATE INDEX "axes_accessattempt_username_3f2d4ca0" ON "axes_accessattempt" ("username");
sqlite> alter table axes_accessattempt drop column trusted;
Runtime error: error in index axes_accessattempt_trusted_0eddf52e after drop column: no such column: trusted
sqlite> 

It seems that sqlite has a problem if a column gets dropen if there is still an index depending on it.

Versions used in this case (if needed):

─$ pip list                                                                                                                                                                                                         
Package             Version
------------------- -----------
asgiref             3.5.2
beautifulsoup4      4.11.1
bootstrap4          0.1.0
certifi             2022.6.15
cffi                1.15.1
charset-normalizer  2.1.1
cryptography        37.0.4
defusedxml          0.7.1
Django              4.1
django-axes         5.39.0
django-bootstrap4   22.2
django-crispy-forms 1.14.0
django-ipware       4.0.2
djangosaml2         1.5.1
elementpath         3.0.2
graphviz            0.20.1
gunicorn            20.1.0
idna                3.3
pip                 22.2
psycopg2-binary     2.9.3
pycparser           2.21
pyOpenSSL           22.0.0
pysaml2             7.2.0
python-dateutil     2.8.2
pytz                2022.2.1
PyYAML              6.0
requests            2.28.1
setuptools          65.2.0
six                 1.16.0
soupsieve           2.3.2.post1
sqlparse            0.4.2
urllib3             1.26.11
wheel               0.37.1
xmlschema           2.0.2

[Lem]

After modifying your migrations locally by altering the field back to db_index=False another column is affected to by an index depending on a to-be-deleted column:

error in index axes_accesslog_trusted_496c5681 after drop column: no such column: trusted

[k-brahma]

same problem happens when upgraded djnago 4.0 to 4.1. when downgraded to 4.0 the error not occurred.

[aleksihakli]

Good catch. Is there a straightforward fix for this available?

[denisSurkov]

This issue will be solved in 4.1.1 Django release: https://docs.djangoproject.com/en/4.1/releases/4.1.1/

Original issue: https://code.djangoproject.com/ticket/33899