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: <andrea brancatelli.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
|