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
