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
======================================================================
