Well. Yes. That explains a lot. Thanks, Andrea.
First run this:
/usr/local/sbin/dbmail-util -a -y
The bug is not in dbmail-util. But I suggest that running the utility
incorrectly can cause some pretty severe problems over time. A word to the
wise should be sufficient: MAKE SURE YOU RUN dbmail-util with *"-y'* which
means go ahead and commit the changes. ;o)
You may still have residual problems. Read on.
The inserts fail at weird times because everything might look good and then
suddenly dbmail-lmtpd or dbmail-smtp finds an out-of-place idnr in the chain
and fails its current insert process with a foreign key constraint issue
messsage which isn't a complete explanation and leads one to question the
schema as I first did. The insert daemon can't possibly be expected to
cascade tables creating rows that exist already IOW. You *may* need to get
rid of all the orphaned dbmail_messageblks and dbmail_physmessage rows.
The dbmail-util utility is pretty good but doesn't do this:
# Check for and delete orphaned messages which are found in
dbmail_messageblks and dbmail_physmessage but not attached to an existing
mailbox or usr_idnr.
build an array of all existing dbmail_physmessages (select id from
dbmail_physmessages) and build an array of these
now for each item in this array, look for ones that fall out of the chain as
orphans:
(SELECT COUNT(*) FROM dbmail_messages, dbmail_users, dbmail_mailboxes
WHERE dbmail_messages.mailbox_idnr=dbmail_mailboxes.mailbox_idnr
AND dbmail_users.user_idnr=dbmail_mailboxes.owner_idnr
AND dbmail_messages.physmessage_id=?)
and then build an array where the orphans occur and for each or*han:
delete from dbmail_messages where physmessage_id = ?
delete from dbmail_physmessage where id = ?
delete from dbmail_messageblks where physmessage_id = ?
Suggest you
1)Backup database now
2) install DbMailAdministrator from anywhere it can access the database.
http://dbma.mobrien.com/
DbMailAdministrator accumulates and retains a number of repair procedures
which accommodate bugs of the past.
Run the DBMA Database Cleanup function.
Then run from the command line exactly this:
/usr/local/sbin/dbmail-util -a -y
Installing DBMail 2.0.9 on top of 2.07 should be next and should be painless
on top of your old database.
That should get you back up and running.
If not you are back to something like this.
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
Mike
----- Original Message -----
From: "Andrea Brancatelli" <[EMAIL PROTECTED]>
To: "DBMail mailinglist" <[email protected]>
Sent: Tuesday, February 28, 2006 10:14 AM
Subject: Re: [Dbmail] help! :) --> dbmysql.c,db_query: mysql_real_query
failed: Cannot add or update achildrow:aforeign key constraint fails
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
_______________________________________________
Dbmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
