mailman-web upgrade issue: broken sqlite database

Bug description

Upgrading mailman3 from NixOS 20.03 configured as documented in mailman wiki to NixOS 20.09 configured as documented in the manual seams to break the SQLite database.

To Reproduce

Steps to reproduce the behavior:

install and configure mailman3 in NixOS 20.03
use mailman3, all is working fine
upgrade to NixOS 20.09, reconfigure as documented in the manual
mailman-web-setup-start fails

Expected behavior

A clean migration of the sqlite database.

Logs

This was the log of mailman-web-setup-start after the upgrade:

 mailman mailman-web-setup-start[1832]: ### Using settings module from /etc/mailman3/settings.py   ####
 mailman mailman-web-setup-start[1832]: Operations to perform:
 mailman mailman-web-setup-start[1832]:   Apply all migrations: account, admin, auth, contenttypes, django_mailman3, django_q, hyperkitty, postorius, sessions, sites, socialaccount
 mailman mailman-web-setup-start[1832]: Running migrations:
 mailman mailman-web-setup-start[1832]:   Applying admin.0003_logentry_add_action_flag_choices...Traceback (most recent call last):
 mailman mailman-web-setup-start[1832]:   File "/nix/store/7zdwp0laa7cfgvp6j85a9ckp0rq62asn-python3.8-mailman-web-unstable-2019-09-29/bin/..mailman-web-wrapped-wrapped", line 9, in <module>
 mailman mailman-web-setup-start[1832]:     sys.exit(main())
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/mailman_web/manage.py", line 23, in main
 mailman mailman-web-setup-start[1832]:     execute_from_command_line(sys.argv)
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
 mailman mailman-web-setup-start[1832]:     utility.execute()
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/core/management/__init__.py", line 375, in execute
 mailman mailman-web-setup-start[1832]:     self.fetch_command(subcommand).run_from_argv(self.argv)
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/core/management/base.py", line 323, in run_from_argv
 mailman mailman-web-setup-start[1832]:     self.execute(*args, **cmd_options)
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/core/management/base.py", line 364, in execute
 mailman mailman-web-setup-start[1832]:     output = self.handle(*args, **options)
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped
 mailman mailman-web-setup-start[1832]:     res = handle_func(*args, **kwargs)
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 232, in handle
 mailman mailman-web-setup-start[1832]:     post_migrate_state = executor.migrate(
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
 mailman mailman-web-setup-start[1832]:     state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
 mailman mailman-web-setup-start[1832]:     state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/db/migrations/executor.py", line 247, in apply_migration
 mailman mailman-web-setup-start[1832]:     migration_recorded = True
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/db/backends/sqlite3/schema.py", line 34, in __exit__
 mailman mailman-web-setup-start[1832]:     self.connection.check_constraints()
 mailman mailman-web-setup-start[1832]:   File "/nix/store/g5ai9as67mk8gjxm8q2jkywkz3ifawx6-python3-3.8.5-env/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py", line 313, in check_constraints
 mailman mailman-web-setup-start[1832]:     raise utils.IntegrityError(
 mailman mailman-web-setup-start[1832]: django.db.utils.IntegrityError: The row in table 'hyperkitty_attachment' with primary key '1' has an invalid foreign key: hyperkitty_attachment.email_id contains a value '7' that does not have a corresponding value in hyperkitty_email__old.id.

Further investigation

The very same problem was reported by Claas A. as issue 328 in Hyperkitty GitLab issues.

I was not able to solve the problem and meanwhile I overwrote the original database.

If I try to rebuild the database from scratch with:

/nix/store/0n5f675a5nk2rbnpbdwdli7iw7f0qh8s-python3-3.8.5-env/bin/mailman-web migrate

I get an empty database but I always get this error if I try to access the web interface:

 Jan 26 11:34:30 c18e env[18167]: django.db.utils.OperationalError: no such table: django_site
 Jan 26 11:34:30 c18e env[18167]: [pid: 18167|app: 0|req: 2/2] 93.56.169.211 () {48 vars in 921 bytes} [Tue Jan 26 10:34:29 2021] GET /postorius/lists/ => generated 1107 bytes in 620 msecs (HTTP/1.1 500) 5 headers in 182 bytes (1 switches on core 0)

I'm looking for a solution or "workaround" to try to save my lists content and archives, and a way to (partially) rebuild the database and repopulate the missing records from mailman core subcription information in a new mailman-web database.

I asked on the mailman3 users list if there is a way to do this and Stephen J. Turnbull replied this is probably an sqlite3 issue not related to mailman3, he also recommends to use a PGSQL database and not an sqlite one.

Notify maintainers

@lheckemann @peti @alyssais

Metadata Please run nix-shell -p nix-info --run "nix-info -m" and paste the result.

these paths will be fetched (0.05 MiB download, 0.28 MiB unpacked):
  /nix/store/ajidkqg53y0zvh0m5cjgqiw3pf9cvlf0-bash-interactive-4.4-p23-dev
copying path '/nix/store/ajidkqg53y0zvh0m5cjgqiw3pf9cvlf0-bash-interactive-4.4-p23-dev' from 'https://cache.nixos.org'...
 - system: `"x86_64-linux"`
 - host os: `Linux 4.19.113, NixOS, 20.09.2660.da3378c4aaf (Nightingale)`
 - multi-user?: `yes`
 - sandbox: `yes`
 - version: `nix-env (Nix) 2.3.10`
 - channels(root): `"nixos-20.09.2660.da3378c4aaf"`
 - channels(gbiscuolo): `"nixos-19.03.172764.50d5d73e22b"`
 - nixpkgs: `/nix/var/nix/profiles/per-user/root/channels/nixos`

NixOS / nixpkgs

mailman-web upgrade issue: broken sqlite database #111549