Joan, The 'foreign keys' update failed because the data is inconsistent. How old is that database? There are subscriptions on mailboxes that don't belong there. Possibly a mailbox belonging to user-A pointing to a non-existant user-B.
A simple join should expose the culprit. Joan Moreau wrote: > this is the output: > > mysql> Alter table dbmail_subscription add FOREIGN KEY user_id_fk > (user_id) REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON > UPDATE CASCADE; > ERROR 1452 (23000): Cannot add or update a child row: a foreign key > constraint fails (`services_dbmail`.`#sql-f3e_8b08`, CONSTRAINT > `#sql-f3e_8b08_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` > (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE) > mysql> Alter table dbmail_subscription add FOREIGN KEY mailbox_id_fk > (mailbox_id) REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE > CASCADE ON UPDATE CASCADE; > ERROR 1452 (23000): Cannot add or update a child row: a foreign key > constraint fails (`services_dbmail`.`#sql-f3e_8b08`, CONSTRAINT > `#sql-f3e_8b08_ibfk_1` FOREIGN KEY (`mailbox_id`) REFERENCES > `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE) > mysql> > > > I would love to be able to run the "fix foreign keys" script :-) > > Jorge Bastos wrote: >> >> When you created the tables something went wrong or you created them >> in MyISAM and convert to InnoDB. >> >> The foreign keys are on the create_tables.sql >> >> >> >> Alter table dbmail_subscription add FOREIGN KEY user_id_fk (user_id) >> REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE; >> >> Alter table dbmail_subsctiption add FOREIGN KEY mailbox_id_fk >> (mailbox_id) REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE >> CASCADE ON UPDATE CASCADE; >> >> >> >> >> >> >> >> *From:* [email protected] [mailto:[email protected]] >> *On Behalf Of *Joan Moreau >> *Sent:* domingo, 15 de Fevereiro de 2009 19:29 >> *To:* DBMail mailinglist >> *Subject:* Re: [Dbmail] Segfault in the git >> >> >> >> CREATE TABLE `dbmail_subscription` ( >> `user_id` bigint(21) NOT NULL DEFAULT '0', >> `mailbox_id` bigint(21) NOT NULL, >> PRIMARY KEY (`user_id`,`mailbox_id`), >> KEY `user_id_index` (`user_id`), >> KEY `mailbox_id_index` (`mailbox_id`) >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED >> >> >> Jorge Bastos wrote: >> >> Maybe can you update the "fix foreign keys" script in sql/mysql/ , so I >> >> can see if this is the case ? >> >> >> >> >> Show us the output of: >> >> >> show create table dbmail_subscription; >> >> _______________________________________________ >> DBmail mailing list >> [email protected] <mailto:[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 -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
