Giovanni Biscuolo writes:
After the upgrade I got this very same issue: https://gitlab.com/mailman/hyperkitty/-/issues/328
Looking at the discussion in that issue, I wonder if there is a bug in sqlite3. The reference to 'hyperkitty_email__old' which apparently doesn't exist in the database or in Mailman migration code suggests that this is internal to sqlite3 when you do a schema change. You could try posting to sqlite3 channels. In general, this is NOT a Mailman problem, it's a database problem. We'll do what we can to help you, but we know a lot of Mailman and Python, and a little bit of SQL. If the database gets corrupted, database internals is not our area of expertise.
FWIW, Postgres is currently the recommended RDBMS. MySQL/MariaDB are also usable, but it's somewhat fragile in its default configuration (I think one Mailman issue depending on implementation dependent behavior is now fixed and there's a workaround for MySQL's default refusal to handle emoji, but OS distributions are not necessarily up to date, and MySQL's history has been one thing after another for us). sqlite3 is supported primarily so Mailman unit tests don't depend on a separate full-scale RDBMS being installed. As far as I know it works fine within its limits, but I believe there are some limits that Mailman installations occasionally run into, and the full-scale databases perform better.
I'm not saying "don't use sqlite3"; you have to consider whether you are likely to run into hard limits or performance issues, and that will probably never happen if you are running a couple of low-traffic lists. However, given that that your database is currently seriously damaged and may be irreparable (see below), it might be a good time to consider switching RDBMS and starting from scratch. You probably do have Postgres or MySQL (or the MariaDB variant) installed already, as many applications pull one or the other in.
Actually the django_site table is in the database so I can't understand why of this message.
One cause of this kind of situation is that there are several copies of the database lying around, and the database you found the table in is not the database HyperKitty is using. Even if there's only one copy visible on disk: if you have a process that attached to an old database file still running, that file could be deleted (ie, removed from the directory and marked for deletion) but the data will still be preserved and used by that process until the process is terminated. I think this is unlikely: database managers usually are quite protective of the files they use both as data and as they are managed by the OS, and I would think sqlite3 would notice if its database file were removed by another process. But the general principle stands: make sure all processes have been stopped, move any database files that aren't current somewhere Mailman can't find them (eg, removable media and remove it!), and restart the Mailman system.
Please is there a way to recreate the mailman-web database from scratch or to fix this kind of problems?
That depends. If you have a user base that has interacted with the web interface, creating users, authenticating email addresses, changing their profiles, then no, you can't recreate it from scratch. You lose all their authentication and profile data, although you might be able to get a mostly complete list of users and addresses from the subscription database in Mailman core.
Otherwise, I would first dump the corrupted database to an SQL script to recreate the database, then reinstall HyperKitty, then find an SQL expert to help me figure out which parts of the SQL script are relevant, which are going to cause errors (eg, trying to create tables that already exist because of the installation process), and what I need to do by hand because it is part of the data that got corrupted.
Mark may have other ideas, but he seems to have covered most of the possibilities in the issue #328 already.