A NOTE has been added to this issue. ====================================================================== http://www.dbmail.org/mantis/view.php?id=250 ====================================================================== Reported By: ezaton Assigned To: ====================================================================== Project: DBMail Issue ID: 250 Category: IMAP daemon Reproducibility: always Severity: minor Priority: normal Status: new ====================================================================== Date Submitted: 18-Aug-05 01:08 CEST Last Modified: 03-Sep-05 04:47 CEST ====================================================================== Summary: DB failure when trying to subscribe to an imap folder Description: Reproducable with both SquirrelMail and Mozilla-Thunderbird, both using the same mailbox. The DB was migrated from 1.x, long time ago. When trying to subscribe, the following error message is shown on the server:
Aug 18 01:56:11 rafael dbmail/imap4d[32142]: _ic_login(): user (id:1, name ezaton) tries login Aug 18 01:56:11 rafael dbmail/imap4d[32142]: _ic_login(): user (id 1, name ezaton) login accepted @ 2005-08-18 01:56:11 Aug 18 01:56:11 rafael dbmail/imap4d[32142]: dbmysql.c,db_query: query [INSERT INTO dbmail_subscription (user_id, mailbox_id) VALUES ('1', '41')] failed Aug 18 01:56:11 rafael dbmail/imap4d[32142]: dbmysql.c,db_query: mysql_real_query failed: Cannot add or update a child row: a foreign key constraint fails Aug 18 01:56:11 rafael dbmail/imap4d[32142]: db.c,db_subscribe: could not insert subscription Aug 18 01:56:11 rafael dbmail/imap4d[32142]: imap4.c,IMAPClientHandler: command return with error [subscribe] Aug 18 01:56:11 rafael dbmail/imap4d[32142]: IMAPClientHandler(): Closing connection for client from IP [127.0.0.1] Trace Level 2 on dbmail-imapd The client disconnects and reports imap error. This problem (although I've never tried to submit a bug here before) is reproducable on all dbmail 2.0.x ====================================================================== ---------------------------------------------------------------------- ilja - 18-Aug-05 10:25 ---------------------------------------------------------------------- Can you show a log with log level 5? That would give us some more information. ---------------------------------------------------------------------- ezaton - 19-Aug-05 04:22 ---------------------------------------------------------------------- Both errors when trying to subscribe to a folder (the first case, the name is W2L, the 2nd case is to a folder called layer7). Note: If not using subscription (Thunderbird support accessing folders without subscribing), everything's work just fine. It's just the act of subscribing which fails. Thanks. Ez ---------------------------------------------------------------------- paul - 31-Aug-05 14:11 ---------------------------------------------------------------------- There has to be some weirdness in your table setup. Please send us: mysqldump -d dbmail mysql -e "select user_idnr,userid from dbmail_users where userid='ezaton' or user_idnr=1" dbmail mysql -e "select * from dbmail_mailboxes where owner_idnr=1 and mailbox_idnr=41" dbmail ---------------------------------------------------------------------- ezaton - 02-Sep-05 10:12 ---------------------------------------------------------------------- I've added the "select user" and the "select * from dbmail_mailbixes", as you requested. I cannot upload my dbmail db, since it's about 6 or 7 GB sized dump. ---------------------------------------------------------------------- paul - 02-Sep-05 11:02 ---------------------------------------------------------------------- I didn't ask for the database. Just for the schema-dump. ---------------------------------------------------------------------- ezaton - 02-Sep-05 11:57 ---------------------------------------------------------------------- Correct. Sorry for that. I've attached the file, under the name dbmail.schema.sql. Thanks. ---------------------------------------------------------------------- paul - 02-Sep-05 16:32 ---------------------------------------------------------------------- got it. Your schema is broken. for example in the dbmail_acl table it reads: CONSTRAINT `dbmail_acl_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_idnr`) ON DELETE CASCADE, CONSTRAINT `dbmail_acl_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `mailboxes` (`mailbox_idnr`) ON DELETE CASCADE But there ain't no table 'users'. This key should reference dbmail_users instead. Same goes for the dbmail_subscription table. Also, I see a lot of redundant keys there. You seem to have stacked several upgrades of dbmail on top of each other. (long time user?). I'm also not sure how the charset latin1 you're using for tables will behave if you start inserting utf8 messages etc. Please use charset us-ascii for your tables. My advise would be to fix the schema: mysqldump -t dbmail > dbmail.dump mysqladmin drop dbmail mysqladmin create dbmail mysql dbmail < create_tables_innoDB.mysql mysql dbmail < dbmail.dump ---------------------------------------------------------------------- ezaton - 02-Sep-05 19:49 ---------------------------------------------------------------------- Thanks. I'm following your advice now. I'm an old time user, indeed. Been using it, and happy with it, for two years, or so. I haven't changed the encoding, and it remains latin1. I use UTF-8 mail, and it works correctly, so I won't change (you fix only what's broken). ---------------------------------------------------------------------- ezaton - 03-Sep-05 03:45 ---------------------------------------------------------------------- I've done as you've suggested, and wasted most of the day on both dump and inset. It didn't go quite well. When done, it seems there was something broken, and all my mail was unavailable. I've decided to recreate the whole db from the backup I've already have, and just live onwards, with the Subscription problem. I will rebuild the DB on some other occasion. Thanks, anyhow. ---------------------------------------------------------------------- aaron - 03-Sep-05 04:47 ---------------------------------------------------------------------- Probably a prefix issue. Take a look in one of the dumps. Do the tables begin with dbmail_ or not? The new schema files all have dbmail_ prefixes, which were added during the ill-named rc series leading up to 2.0. The prefix is configurable from the dbmail.conf file, and defaults to dbmail_, but can be set to nothing (which you've probably done, come to think of it...). Issue History Date Modified Username Field Change ====================================================================== 18-Aug-05 01:08 ezaton New Issue 18-Aug-05 10:25 ilja Note Added: 0000843 19-Aug-05 04:19 ezaton File Added: dbmail-error1.txt 19-Aug-05 04:20 ezaton File Added: dbmail-error2.txt 19-Aug-05 04:22 ezaton Note Added: 0000844 31-Aug-05 14:11 paul Note Added: 0000898 02-Sep-05 10:09 ezaton File Added: dbmail-mailboxes.txt 02-Sep-05 10:11 ezaton File Added: dbmail-select-user.txt 02-Sep-05 10:12 ezaton Note Added: 0000902 02-Sep-05 11:02 paul Note Added: 0000903 02-Sep-05 11:56 ezaton File Added: dbmail.sechema.sql 02-Sep-05 11:57 ezaton Note Added: 0000904 02-Sep-05 16:32 paul Note Added: 0000905 02-Sep-05 19:49 ezaton Note Added: 0000906 03-Sep-05 03:45 ezaton Note Added: 0000907 03-Sep-05 04:47 aaron Note Added: 0000908 ======================================================================