Hello Mike,
ok, I'll try to be a little more clear on the timeline :-)
The server we are talking about (reddevil) used to have something like 40
domains on it... Now I have another server and I moved about a dozen of
domains from that server to another one.
I run fetchmail from the new server to empty all the mailboxes on
reddevil (fetchmail -K bla bla bla) and I must say I was pretty puzzled
by the fact that dbmail_messageblks tables used to be 140+MB before and
still was after... but I didn't care much about it.
After a few days having finished moving those 12 domains, I deleted all
the corresponding mailboxes and aliases from reddevil with dbmail-util -d
Yet dbmail_messagebls kept at over 140MB.
So I investigated and found out that the cron job that was supposed to
run dbmail-util -a wasn't running (I mispelled the path, dbmail-util
was in /usr/sbin and I pointed at /usr/bin).. so I run it manually and
it complained that the database was not ok. Sorry but I can't recall
the exact problem it complaint about.
Note that at this moment everything was working as expected: mail
delivery was working, POP3, IMAP, everything was working ok.
I tought that it could be related to having deleted many mailboxes (a
couple of them had more than 1000 msgs) so I run dbmail-util -a -y
without much worries.
It worked much and after a couple of minutes it stated it fixed
everything and suggested to rerun dbmail-util -a to check everything.
I did and the check was ok.
I fixed the crontabbed job and didn't pay attention to wether delivery
wasn't working or not.
Two days after an user called because he wasn't receiving any mail, so I
snapped into maillog just to find the error I wrote you about.
To answer all your question, the table definitions are ok (after all he
says the problem is on a contraints of a foreign key, not on table
definitions), the database is up to date with the binary (also because
it was working since months with no problem).
I did some (many) checks on the database and everything correlated with
the user seems to be ok. The only strange thing is that I did find a
different number of entries between dbmail_messages and
dbmail_physmessages when I suppose there should be a 1:1 relation
between the two tables...
So I run a quer to delete the unreferenciated messages in physmessages
(there were about 230 of them) the table aligned, but the problem in
mail delivery still persisted.
I realized that there are many unreferenciated message block in
dbmail_messageblks as well so maybe I should try to delete those but I
don't think they are the problem in the query we highligthed before.
(just for the page of history it's still over 140MB of datas)
Dbmail-util keeps saying everything's ok... funny program.
I have a database backup but unfortunately it's later than the first
dbmail-util so it's pretty useless in this case...
Sorry to say but I have some strong feelings that there is some kind of
bug in dbmail-util when it's doing the cross-reference checking that
somehow scrambled everything when many mailboxes and many messages were
orphaned by the mailboxes deletion.
Actually I'm moving out all the users from that server so if you want we
can run any kind of crazy query on the database to understand where the
problem may be.
Finally, the specs.
The system is FreeBSD 4.9, dbmail is 2.0.something (sorry I'm not there
and I don't remember) - telnet 66.98.144.71 on POP3 or IMAPD port and
you'll find out (I can't telnet from here), while mysql is 4.0.15...
Everything is running behind sendmail.
Questions? Hints? Someone wants a dump of my database (as of the first
situation, not after my experiments) to experiment by himself?
Thanks for your help.
In data 28/2/2006, "M. J. [Mike] OBrien" <[EMAIL PROTECTED]> ha scritto:
>Andrea said >> Umh... I'm not migrating anything. Dbmail-util just trashed
>the message base in it's standard lifecycle....
>
>Dbmail-util doesn't trash healthy databases so I suggest look for another
>problem.
>Database schema seems botched.
>What version of DBMail is this?
>MySQL version?
>Is your database schema in sync with the version of DBMail you are using?
>Dump the shema and check it against sql/mysql/create_tables_innoDB.mysql in
>the tarball.
>dbmail_ prefix set in dbmail.conf?
>dbmail_ prefix schema (tables begin with "dbmail_" ) ?
>
>OK I am a little puzzled too. So what does this mean, Andrea, when you say:
>"I pulled out at least 80Mb of mailboxes" AND "I am moving a lot of
>mailboxes from this server to another one" in your remarks: "... I am moving
>a lot of mailboxes from this server to another one but my dbmail_messageblks
>won't get smaller. it used to be 140MB, and it's still 140MB, even if I am
>sure I pulled out at least 80Mb of mailboxes."
>
>"Moving a lot of mailboxes from this server to another" is what many call
>"migration". In the alternative, what *did* you mean?
>
>Are you able to restore a backup like Fred H suggested? But if your schema
>is broken, which appears to be the case ("Cannot add or update a child row:
>a foreign key constraint fails") that would retain the problem.
>
>Did you check the database structure for the account referenced in the trace
>93568 ('segreteria')?
>i.e.: RUN > mysql -e "select * from dbmail_users where userid='segreteria'"
>dbmail
>
>Have you tried / creating account / sending it mail / checking the mail /
>deleting account / with dbmail-util to see if all cascades are working?
>
>If you do not have a backup (make one) and if you want to restore your
>database schema with a correct schema for the DBMail version you are using
>you might consider the following with amendments to suit your system:
>
>mysqldump -t dbmail > dbmail.feb282006
>/usr/local/bin/mysqladmin drop dbmail
>/usr/local/bin/mysqladmin create dbmail
>mysql dbmail < sql/mysql/create_tables_innoDB.mysql
>mysql dbmail < dbmail.feb282006
>
>Kindest regards,
>Mike
>
>
>
>----- Original Message -----
>From: <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>; "'DBMail mailinglist'"
><[email protected]>
>Sent: Monday, February 27, 2006 5:15 PM
>Subject: R: [Dbmail] help! :) --> dbmysql.c,db_query: mysql_real_query
>failed: Cannot add or update achild row:aforeign key constraint fails
>
>
>Umh... I'm not migrating anything. Dbmail-util just trashed the message base
>in it's standard lifecycle....
>
>
>
>-----Messaggio originale-----
>Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Per conto
>di [EMAIL PROTECTED]
>Inviato: lunedì 27 febbraio 2006 20.48
>A: DBMail mailinglist
>Oggetto: [Dbmail] help! :) --> dbmysql.c,db_query: mysql_real_query failed:
>Cannot add or update achild row: aforeign key constraint fails
>
>It looks like you have been systematically destroying your database. Do you
>have a recent mysqldump or tarball of your database. It looks like that is
>what is needed.
>
>dbmail-util does some good checks. Have you also tried the "Database
>Cleanup" function in DbMailAdministrator
>(http://library.mobrien.com/dbmailadministrator/) It has a couple extra
>relationship checks.
>
>If you read about mysql replication you will also run into what the MySQL
>experts suggest as the best way to migrate databases.
>http://dev.mysql.com/doc/refman/5.0/en/replication.html
>
>Perhaps you should now restore your database to its backed-up position and
>then do the above referenced more sane procedure for migration.
>
>Regards
>Fred
>
>
>----- Original Message -----
>From: <[EMAIL PROTECTED]>
>To: <[email protected]>
>Sent: Monday, February 27, 2006 4:12 AM
>Subject: [Dbmail] help! :) --> dbmysql.c,db_query: mysql_real_query failed:
>Cannot add or update achild row: a foreign key constraint fails
>
>
>Hello everybody!
>
>
>I need serious help with this issue that suddenly came out:
>
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query: executing
>query [SELECT 1=1 FROM dbmail_physmessage LIMIT 1 OFFSET 0]
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: header.c,consume_header_line:
>end of header found
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: header.c,get_rfc_size:
>remaining_len = zd
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: mime_readheader(): entering
>mime loop
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: mime_readheader(): found
>double
>newline; header size: 28 lines
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: mail_adr_list(): mimelist
>currently has [17] nodes
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: mail_adr_list(): mail address
>parser starting
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: mail_adr_list(): total fields
>in header 17
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: mail_adr_list(): scanning for
>Return-Path
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: mail_adr_list(): found
>[EMAIL PROTECTED], next in list is <null>
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: mail_adr_list(): found 1
>emailaddresses
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: mail_adr_list(): mail address
>parser finished
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]:
>authsql.c,auth_check_user_ext:
>checking user [EMAIL PROTECTED] in alias table
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query: executing
>query [SELECT deliver_to FROM dbmail_aliases WHERE lower(alias) =
>lower('[EMAIL PROTECTED]') AND lower(alias) <> lower(deliver_to)]
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]:
>authsql.c,auth_check_user_ext:
>into checking loop
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]:
>authsql.c,auth_check_user_ext:
>checking user [EMAIL PROTECTED] to 44
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]:
>authsql.c,auth_check_user_ext:
>checking user [44] in alias table
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query: executing
>query [SELECT deliver_to FROM dbmail_aliases WHERE lower(alias) =
>lower('44') AND lower(alias) <> lower(deliver_to)]
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]:
>authsql.c,auth_check_user_ext:
>adding [44] to deliver_to address
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dsn.c, dsnuser_resolve: user
>[EMAIL PROTECTED] found total of [1] aliases
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query: executing
>query [BEGIN]
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query: executing
>query [SELECT user_idnr FROM dbmail_users WHERE
>userid='[EMAIL PROTECTED]@__']
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: misc.c,create_unique_id:
>created: 2c4721ebc49d32a22d71b434920e59ed
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query: executing
>query [SELECT mailbox_idnr FROM dbmail_mailboxes WHERE name='INBOX' AND
>owner_idnr='1']
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: db.c, db_find_create_mailbox:
>mailbox [INBOX] found
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query: executing
>query [INSERT INTO dbmail_physmessage (messagesize, internal_date) VALUES
>('0', CURRENT_TIMESTAMP)]
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query: executing
>query [INSERT INTO dbmail_messages(mailbox_idnr, physmessage_id,
>unique_id,recent_flag, status) VALUES ('2', '423701',
>'2c4721ebc49d32a22d71b434920e59ed', '1', '5')]
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query: query
>[INSERT INTO dbmail_messages(mailbox_idnr, physmessage_id,
>unique_id,recent_flag, status) VALUES ('2', '423701',
>'2c4721ebc49d32a22d71b434920e59ed', '1', '5')] failed
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: dbmysql.c,db_query:
>mysql_real_query failed: Cannot add or update a child row: a foreign key
>constraint fails
>
>Feb 27 09:58:14 reddevil dbmail/smtp[93568]: db.c,db_insert_message: query
>failed
>
>
>
>The message base "crashed" (i.e. the error came out) after running
>dbmail-util -a -y
>
>
>
>There's also a very strange symptom: I am moving a lot of mailboxes from
>this server to another one but my dbmail_messageblks won't get smaller. it
>used to be 140MB, and it's still 140MB, even if I am sure I pulled out at
>least 80Mb of mailboxes.
>
>
>
>I run dbmail-util -a every night. the removed mailboxes have all been
>deleted, but the one that are left show this error.
>
>
>
>I think that some garbage may be left in dbmail_messages and
>dbmail_messageblks that dbmail-util is not catching up.
>
>
>
>How to check the correctness of referentiality?
>
>
>Any hint?
>
>
>Help, help, help.
>
>
>Thanks
>
>
>
>
>_______________________________________________
>Dbmail mailing list
>[email protected]
>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
>_______________________________________________
>Dbmail mailing list
>[email protected]
>https://mailman.fastxs.nl/mailman/listinfo/dbmail
