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" <andrea brancatelli.it>
To: "DBMail mailinglist" <dbmaildbmail.org>
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"
<mikemobrien.com> 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: <andreabrancatelli.it>
>To: <fredharrissystemsengineer.ca>; "'DBMail
mailinglist'"
><dbmaildbmail.org>
>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: dbmail-bouncesdbmail.org [mailto:dbmail-bouncesdbmail.org] Per conto
>di fredharrissystemsengineer.ca
>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: <andreabrancatelli.it>
>To: <dbmaildbmail.org>
>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
>andreabrancatelli.it, 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 [segreteriasimeu.it] 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('segreteriasimeu.it') 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 segreteriasimeu.it 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
>[segreteriasimeu.it] 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='__!internal_delivery_user!__']
>
>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
>Dbmaildbmail.org
>htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>
>_______________________________________________
>Dbmail mailing list
>Dbmaildbmail.org
>htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
Dbmail mailing list
Dbmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
Dbmail mailing list
Dbmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|