Hi, I added ON UPDATE CASCADE to all foreign keys. The problem is now solved.
Meanwhile I found out ON UPDATE CASCADE was added starting 2.0.7 It wasn't mentioned in UPGRADE that I should add them! I wonder how many people have incomplete FKs now... thanks, Anne > Comparing with create_tables_innoDB.mysql : > >> -- MySQL dump 9.11 >> -- >> -- Host: localhost Database: dbmail >> -- ------------------------------------------------------ >> -- Server version 4.0.26-log >> >> -- >> -- Table structure for table `dbmail_acl` >> -- >> >> CREATE TABLE dbmail_acl ( >> user_id bigint(21) NOT NULL default '0', >> mailbox_id bigint(21) NOT NULL default '0', >> lookup_flag tinyint(1) NOT NULL default '0', >> read_flag tinyint(1) NOT NULL default '0', >> seen_flag tinyint(1) NOT NULL default '0', >> write_flag tinyint(1) NOT NULL default '0', >> insert_flag tinyint(1) NOT NULL default '0', >> post_flag tinyint(1) NOT NULL default '0', >> create_flag tinyint(1) NOT NULL default '0', >> delete_flag tinyint(1) NOT NULL default '0', >> administer_flag tinyint(1) NOT NULL default '0', >> PRIMARY KEY (user_id,mailbox_id), >> KEY user_id (user_id), >> KEY mailbox_id (mailbox_id), >> CONSTRAINT `dbmail_acl_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES >> `dbmail_users` (`user_idnr`) ON >> DELETE CASCADE, >> CONSTRAINT `dbmail_acl_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES >> `dbmail_mailboxes` (`mailbox_ >> idnr`) ON DELETE CASCADE > > FOREIGN KEY user_id_fk (user_id) > REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON > UPDATE CASCADE, > FOREIGN KEY mailbox_id_fk (mailbox_id) > REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE > CASCADE > ON UPDATE CASCADE > > >> ) TYPE=InnoDB; >> >> -- >> -- Table structure for table `dbmail_aliases` >> -- >> CREATE TABLE dbmail_aliases ( >> alias_idnr bigint(21) NOT NULL auto_increment, >> alias varchar(100) NOT NULL default '', >> deliver_to varchar(250) NOT NULL default '', >> client_idnr bigint(21) NOT NULL default '0', >> PRIMARY KEY (alias_idnr), >> KEY client_idnr_index (client_idnr), >> KEY alias_index (alias) >> ) TYPE=InnoDB; >> >> -- >> -- Table structure for table `dbmail_auto_notifications` >> -- >> >> CREATE TABLE dbmail_auto_notifications ( >> user_idnr bigint(21) NOT NULL default '0', >> notify_address varchar(100) default NULL, >> PRIMARY KEY (user_idnr), >> KEY user_idnr (user_idnr), >> CONSTRAINT `dbmail_auto_notifications_ibfk_1` FOREIGN KEY >> (`user_idnr`) >> REFERENCES `dbmail_users` >> (`user_idnr`) ON DELETE CASCADE > > FOREIGN KEY user_idnr_fk (user_idnr) > REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON > UPDATE CASCADE > > >> ) TYPE=InnoDB; >> >> -- >> -- Table structure for table `dbmail_auto_replies` >> -- >> >> CREATE TABLE dbmail_auto_replies ( >> user_idnr bigint(21) NOT NULL default '0', >> reply_body mediumtext, >> einddatum date default '0000-00-00', <--- custom field >> PRIMARY KEY (user_idnr) >> ) TYPE=MyISAM; >> >> -- >> -- Table structure for table `dbmail_mailboxes` >> -- >> >> CREATE TABLE dbmail_mailboxes ( >> mailbox_idnr bigint(21) NOT NULL auto_increment, >> owner_idnr bigint(21) NOT NULL default '0', >> name varchar(100) NOT NULL default '', >> seen_flag tinyint(1) NOT NULL default '0', >> answered_flag tinyint(1) NOT NULL default '0', >> deleted_flag tinyint(1) NOT NULL default '0', >> flagged_flag tinyint(1) NOT NULL default '0', >> recent_flag tinyint(1) NOT NULL default '0', >> draft_flag tinyint(1) NOT NULL default '0', >> no_inferiors tinyint(1) NOT NULL default '0', >> no_select tinyint(1) NOT NULL default '0', >> permission tinyint(1) default '2', >> PRIMARY KEY (mailbox_idnr), >> UNIQUE KEY mailboxidnr_2 (mailbox_idnr) > > INDEX name_index (name), > INDEX owner_idnr_index (owner_idnr), > UNIQUE INDEX owner_idnr_name_index (owner_idnr, name), > FOREIGN KEY owner_idnr_fk (owner_idnr) > REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON > UPDATE CASCADE > > >> ) TYPE=InnoDB; >> >> -- >> -- Table structure for table `dbmail_messageblks` >> -- >> >> CREATE TABLE dbmail_messageblks ( >> messageblk_idnr bigint(21) NOT NULL auto_increment, >> physmessage_id bigint(21) NOT NULL default '0', >> messageblk longtext NOT NULL, >> blocksize bigint(21) NOT NULL default '0', >> is_header tinyint(1) NOT NULL default '0', >> PRIMARY KEY (messageblk_idnr), >> KEY physmessage_id_index (physmessage_id), >> KEY physmessage_id_is_header_index (physmessage_id,is_header), >> CONSTRAINT `dbmail_messageblks_ibfk_1` FOREIGN KEY (`physmessage_id`) >> REFERENCES `dbmail_physmessa >> ge` (`id`) ON DELETE CASCADE > > FOREIGN KEY physmessage_id_fk (physmessage_id) > REFERENCES dbmail_physmessage (id) ON DELETE CASCADE ON > UPDATE > CASCADE > > >> ) TYPE=InnoDB; >> >> -- >> -- Table structure for table `dbmail_messages` >> -- >> >> CREATE TABLE dbmail_messages ( >> message_idnr bigint(21) NOT NULL auto_increment, >> mailbox_idnr bigint(21) NOT NULL default '0', >> seen_flag tinyint(1) NOT NULL default '0', >> answered_flag tinyint(1) NOT NULL default '0', >> deleted_flag tinyint(1) NOT NULL default '0', >> flagged_flag tinyint(1) NOT NULL default '0', >> recent_flag tinyint(1) NOT NULL default '0', >> draft_flag tinyint(1) NOT NULL default '0', >> unique_id varchar(70) NOT NULL default '', >> status tinyint(3) unsigned zerofill NOT NULL default '000', >> physmessage_id bigint(21) NOT NULL default '0', >> PRIMARY KEY (message_idnr), >> UNIQUE KEY message_idnr_2 (message_idnr), >> KEY message_idnr (message_idnr), >> KEY physmessage_id_index (physmessage_id), >> KEY mailbox_idnr_index (mailbox_idnr), >> KEY seen_flag_index (seen_flag), >> KEY unique_id_index (unique_id), >> KEY status_index (status), >> CONSTRAINT `dbmail_messages_ibfk_1` FOREIGN KEY (`mailbox_idnr`) >> REFERENCES `dbmail_mailboxes` (`m >> ailbox_idnr`) ON DELETE CASCADE, >> CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`physmessage_id`) >> REFERENCES `dbmail_physmessage` >> (`id`) ON DELETE CASCADE > > FOREIGN KEY physmessage_id_fk (physmessage_id) > REFERENCES dbmail_physmessage (id) ON DELETE CASCADE ON > UPDATE > CASCADE, > FOREIGN KEY mailbox_idnr_fk (mailbox_idnr) > REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE > CASCADE > ON UPDATE CASCADE > > >> ) TYPE=InnoDB; >> >> -- >> -- Table structure for table `dbmail_pbsp` >> -- >> >> CREATE TABLE dbmail_pbsp ( >> idnr bigint(21) NOT NULL auto_increment, >> since datetime NOT NULL default '0000-00-00 00:00:00', >> ipnumber varchar(40) NOT NULL default '', >> PRIMARY KEY (idnr), >> UNIQUE KEY ipnumber_idx (ipnumber), >> KEY since_idx (since) >> ) TYPE=MyISAM; >> >> -- >> -- Table structure for table `dbmail_physmessage` >> -- >> >> CREATE TABLE dbmail_physmessage ( >> id bigint(21) NOT NULL auto_increment, >> messagesize bigint(21) NOT NULL default '0', >> rfcsize bigint(21) NOT NULL default '0', >> internal_date datetime NOT NULL default '0000-00-00 00:00:00', >> PRIMARY KEY (id) >> ) TYPE=InnoDB; >> >> -- >> -- Table structure for table `dbmail_subscription` >> -- >> >> CREATE TABLE dbmail_subscription ( >> user_id bigint(21) NOT NULL default '0', >> mailbox_id bigint(21) NOT NULL default '0', >> PRIMARY KEY (user_id,mailbox_id), >> KEY user_id (user_id), >> KEY mailbox_id (mailbox_id), >> CONSTRAINT `dbmail_subscription_ibfk_1` FOREIGN KEY (`user_id`) >> REFERENCES `dbmail_users` (`user_i >> dnr`) ON DELETE CASCADE, >> CONSTRAINT `dbmail_subscription_ibfk_2` FOREIGN KEY (`mailbox_id`) >> REFERENCES `dbmail_mailboxes` ( >> `mailbox_idnr`) ON DELETE CASCADE > > FOREIGN KEY user_id_fk (user_id) > REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON > UPDATE CASCADE, > FOREIGN KEY mailbox_id_fk (mailbox_id) > REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE > CASCADE > ON UPDATE CASCADE > > >> ) TYPE=InnoDB; >> >> -- >> -- Table structure for table `dbmail_users` >> -- >> >> CREATE TABLE dbmail_users ( >> user_idnr bigint(21) NOT NULL auto_increment, >> userid varchar(100) NOT NULL default '', >> passwd varchar(34) NOT NULL default '', >> client_idnr bigint(21) NOT NULL default '0', >> maxmail_size bigint(21) NOT NULL default '0', >> encryption_type varchar(20) NOT NULL default '', >> last_login datetime NOT NULL default '1979-11-03 22:05:58', >> curmail_size bigint(21) NOT NULL default '0', >> PRIMARY KEY (user_idnr), >> KEY userid_index (userid) > > UNIQUE INDEX userid_index (userid) > > >> ) TYPE=InnoDB; >> >> End of dump. > > Please, correct tables structure acording to create_tables_innoDB.mysql > May be this helps. > > -- > Oleg Lapshin > > _______________________________________________ > Dbmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail >
