On Tue, Oct 03, 2023 at 05:22:44PM +0900, Stephen J. Turnbull wrote:
Ryan Curtin writes:
However, no messages are actually relayed to subscribers. I spent a while digging into logs and found that every message sent to the list results in an error like this in /var/log/mailman3/mailman.log:
Oct 02 17:01:26 2023 (2957556) Uncaught runner exception: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.OperationalError) database is lockedWe don't recommend using sqlite in production, but you know your needs. However, this might be a good moment to switch to a database server like PostgreSQL or MySQL. In my experience, PostgreSQL just works. MySQL has one nit, namely it assumes that UTF-8 is at most 3 bytes, but emoji and a few other special characters in common use, as well as characters required by some less common languages, need 4 bytes. So make sure that the Mailman database are configured for utf8mbcs4 (or something like that, you'll know it when you see it in the docs).
Thanks for the quick response.
I tried your suggestion to switch to MySQL, and this seemed to work. For the sake of posterity I will briefly document what I did, since the migration is not trivial (and not totally documented as far as I could find). This is specifically for the migration of mailman3 core, not mailman3 web, which does occasionally give database locked errors and perhaps should be switched to MySQL too, but, I would rather write my software than debug its mailing list. In any case:
Modify mailman.cfg using the commented-out examples to use the MySQL backend instead of the sqlite3 backend.
Dump old sqlite3 databases with the help of the Python script found at https://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-... a few answers down:
sudo sqlite3 /var/lib/mailman3/data/mailman.db .dump |
python3 /tmp/sqlite3-to-mysql.py > /tmp/mailman.db.dumpNote that the final line of that script needs to be changed from "print line," to the Python3 "print(line)".
Manually modify some things that the Python script didn't get in /tmp/mailman.db.dump. Specifically, any time "VARCHAR" shows up as a type in a "CREATE TABLE", it needs to be changed to "VARCHAR(n)" for some size "n". I just guessed sizes based on the name of the variable, usually 128, 512, or 4096. That's very arbitrary and probably unsafe and I should have done a better job. Whatever.
Add "SET FOREIGN_KEY_CHECKS = 0;" to the top of /tmp/mailman.db.dump, and "SET FOREIGN_KEY_CHECKS = 1;" to the bottom, to relax the constraints while importing.
Create the "mailman3" database in MySQL, then create the user/pass specified in /etc/mailman3/mailman.cfg and grant all privileges on the "mailman3" database.
Import the dump: mysql -u mailman -p mailman3 < /tmp/mailman.db.dump. Hopefully that succeeds. Probably minor changes need to be made here and there if not.
Restart mailman, cross your fingers, then send an email to the list and if you're lucky you'll receive it.
Hopefully that helps someone. (With the big caveat that I don't know about mailman and probably almost everything suggested there is unsafe. But it seems to have worked for me... for now.)
-- Ryan Curtin | "What? Facts?" ryan@ratml.org | - Joe Cairo