obviously i mean dbmail-users -d when I'm talking about deleting users...
In data 28/2/2006, "Andrea Brancatelli" <[EMAIL PROTECTED]> ha
scritto:
>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
>
