A NOTE has been added to this issue. ====================================================================== http://www.dbmail.org/mantis/view.php?id=937 ====================================================================== Reported By: john Assigned To: ====================================================================== Project: DBMail Issue ID: 937 Category: installation scripts Reproducibility: always Severity: major Priority: normal Status: new target: ====================================================================== Date Submitted: 07-Nov-11 16:36 CET Last Modified: 08-Nov-11 15:13 CET ====================================================================== Summary: 2_2-3_0.mysql: cleanup required for dbmail_sievescripts Description: The MySQL upgrade script[1] for a 2.2 -> 3.0 migration does not work for the dbmail 2.2 dbmail_sievescripts table format.
This is because prior to dbmail 2.3, there is no unique constraint for (owner_idnr, name). See Bug http://www.dbmail.org/mantis/view.php?id=636 [2] for details, it seems this was not backported to 2.2, and if so, there are still users with a vanilla 2.2 schema. Please provide a workaround, or extend the script to do some cleanup of this table. The problematic statement is: ALTER TABLE dbmail_sievescripts ADD UNIQUE INDEX dbmail_sievescripts_1 (owner_idnr, name); [1] http://git.dbmail.eu/paul/dbmail/tree/sql/mysql/2_2-3_0.mysql [2] http://www.dbmail.org/mantis/view.php?id=636 ====================================================================== ---------------------------------------------------------------------- (0003323) paul (administrator) - 08-Nov-11 09:23 http://www.dbmail.org/mantis/view.php?id=937#c3323 ---------------------------------------------------------------------- I don't see how this might be fixed automatically. Concatenating all active scripts with the same user/name combo is definitely not valid. Looks like a manual inspection and intervention of all duplicates is the only way forward. ---------------------------------------------------------------------- (0003324) john (reporter) - 08-Nov-11 15:13 http://www.dbmail.org/mantis/view.php?id=937#c3324 ---------------------------------------------------------------------- For the record, here is a basic query to find multiple scripts per owner: mysql> SELECT owner_idnr, count(name) as n FROM dbmail_sievescripts GROUP BY owner_idnr HAVING n > 1; +------------+---+ | owner_idnr | n | +------------+---+ | 71 | 2 | | 1041 | 2 | | 1503 | 2 | | 1515 | 2 | | 1547 | 2 | | 1551 | 2 | | 1665 | 2 | | 1700 | 2 | | 1703 | 2 | | 1720 | 2 | | 1756 | 2 | | 1757 | 2 | +------------+---+ 12 rows in set (0.00 sec) This doesn't look as bad as expected (in my case), so I'm going to fix them manually. On a side note, what happens if there are multiple active scripts of the same name per owner? How, and in which order does the sieve machinery execute them? It seems the 2.2 code[1], as well as the 3.0-rc code[2] expects a single script (which is guaranteed by the unique constraint in 2.3+, however, in 2.2 there is none): SELECT script FROM dbmail_sievescripts WHERE owner_idnr = ? AND name = ? This would actually simply concatenate them, in random order as returned by the MySQL server, which looks broken? I'm not sure how to fix this in a sane way. It's probably best to dump these identical named scripts and merge them manually without breaking their logic. [1] http://git.dbmail.eu/paul/dbmail/tree/db.c?id=v2.2.17#n601 [2] http://git.dbmail.eu/paul/dbmail/tree/src/dm_sievescript.c?id=v3.0.0-rc3#n31 Issue History Date Modified Username Field Change ====================================================================== 07-Nov-11 16:36 john New Issue 08-Nov-11 09:23 paul Note Added: 0003323 08-Nov-11 15:13 john Note Added: 0003324 ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
