A NOTE has been added to this issue. ====================================================================== http://www.dbmail.org/mantis/view.php?id=650 ====================================================================== Reported By: Numbski Assigned To: ====================================================================== Project: DBMail Issue ID: 650 Category: Database layer Reproducibility: always Severity: feature Priority: normal Status: acknowledged target: ====================================================================== Date Submitted: 08-Oct-07 14:05 CEST Last Modified: 15-Feb-10 13:50 CET ====================================================================== Summary: When using NDBCLUSTER, inserts begin to fail. Description: This was originally posted as a bug to MySQL (not by me), only to have it recanted as being a "script bug".
http://bugs.mysql.com/bug.php?id=28619 In order to get dbmail set up, the database setup script had to be changed ENGINE=NDBCLUSTER, and a constraint removed: http://www.mail-archive.com/dbm...@dbmail.org/msg12193.html As a result, the matching section in my create_ndb_tables.mysql script looks like this: CREATE TABLE dbmail_headervalue ( headername_id BIGINT NOT NULL, physmessage_id BIGINT NOT NULL, id BIGINT NOT NULL AUTO_INCREMENT, headervalue TEXT, PRIMARY KEY (id), UNIQUE (physmessage_id, id), UNIQUE (physmessage_id, headername_id), INDEX (headername_id), INDEX (physmessage_id), FOREIGN KEY (headername_id) REFERENCES dbmail_headername(id) ON UPDATE CASCADE ON DELETE CASCADE FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE NDBCluster DEFAULT CHARSET=utf8 ; Everything appears to work normally, until I try to migrate my mailboxes using imapsync from my old server to the new dbmail server. The errors, sans the related timestamps and user info, look exactly like that of the first link in this post: May 23 15:08:46 dcspct1 dbmail/lmtpd[35859]: Error [Duplicate entry '' for key '*UNKNOWN*'] [INSERT INTO dbmail_headervalue (headername_id, physmessage_id, headervalue) VALUES (12,3,'from gferro.xxx.it (a-b-c-d.ip10.xxx.it [a.b.c.d]) by x.y.z (Postfix) with ES MTP id 78A7633C5F for <gfe...@xxx.it>; Wed, 23 May 2007 15:08:46 +0200 (CEST)')] The poster in the MySQL bug states that it was a problem with the insertion script, not MySQL, but fails to go into further detail. I have attempted several times to e-mail the original bug submitter with no response. ====================================================================== ---------------------------------------------------------------------- (0002394) Numbski (reporter) - 09-Oct-07 02:44 http://www.dbmail.org/mantis/view.php?id=650#c2394 ---------------------------------------------------------------------- I didn't mean to make this authentication layer....oops. :( ---------------------------------------------------------------------- (0002396) paul (administrator) - 09-Oct-07 09:07 http://www.dbmail.org/mantis/view.php?id=650#c2396 ---------------------------------------------------------------------- I've updated the category. I've also changed the severity since NBD has never been a supported platform. ---------------------------------------------------------------------- (0002530) theki (reporter) - 25-Mar-08 00:18 http://www.dbmail.org/mantis/view.php?id=650#c2530 ---------------------------------------------------------------------- Fixed it. Since MySQL-Cluster does not support Foreign Keys (like MyISAM): http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations-unsupported-missing.html you need a create script without any unique foreign keys. That's a bad workaround, but it finally works. Attached working create script. ---------------------------------------------------------------------- (0002531) paul (administrator) - 25-Mar-08 09:23 http://www.dbmail.org/mantis/view.php?id=650#c2531 ---------------------------------------------------------------------- Please be advised that without the foreign key restraints the database will not remain healthy. DBMail relies on the cascades to remove messages when a mailbox is deleted, remove mailboxes when a user is deleted, update owner idnrs when a user changes id, etc, etc.... Also, lack of full support for transactions, i.e. rollbacks, and the fact that ndb only supports read committed isolation on transactions means that imap compliancy under high concurrencies will be poor. ---------------------------------------------------------------------- (0002532) theki (reporter) - 25-Mar-08 09:32 http://www.dbmail.org/mantis/view.php?id=650#c2532 ---------------------------------------------------------------------- Exactly. That's why we need "real" support for clusters on dbmail site. Maybe I will checkout the actual version and work on that. Transactions are still a problem then. ---------------------------------------------------------------------- (0002533) paul (administrator) - 25-Mar-08 10:33 http://www.dbmail.org/mantis/view.php?id=650#c2533 ---------------------------------------------------------------------- theki, You're most welcome to help out working on dbmail's scalability. Dbmail's trunk development has shifted from using hown-grown database drivers to using libzdb (http://www.tildeslash.com/libzdb/), and libevent rather than the basic select-accept eventloop used in 2.2. Please see: http://git.dbmail.eu/paul/dbmail/ ---------------------------------------------------------------------- (0003027) nethzt (reporter) - 15-Feb-10 13:50 http://www.dbmail.org/mantis/view.php?id=650#c3027 ---------------------------------------------------------------------- any news on ndb support ? Issue History Date Modified Username Field Change ====================================================================== 08-Oct-07 14:05 Numbski New Issue 08-Oct-07 14:07 Numbski Issue Monitored: Numbski 09-Oct-07 02:44 Numbski Note Added: 0002394 09-Oct-07 09:07 paul Note Added: 0002396 09-Oct-07 09:07 paul Severity major => feature 09-Oct-07 09:07 paul Status new => acknowledged 09-Oct-07 09:07 paul Category Authentication layer => Database layer 26-Nov-07 21:55 m2000 Issue Monitored: m2000 25-Mar-08 00:15 theki Issue Monitored: theki 25-Mar-08 00:18 theki File Added: create_ndb_tables.mysql 25-Mar-08 00:18 theki Note Added: 0002530 25-Mar-08 09:23 paul Note Added: 0002531 25-Mar-08 09:32 theki Note Added: 0002532 25-Mar-08 10:33 paul Note Added: 0002533 15-Feb-10 13:50 nethzt Note Added: 0003027 ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev