Sorry, my first post was unclear To upgrade was from 5.1.50 to 5.1.51 The machine is running with 5.1 since begin and with compressed tables since many releases
Here are the structure-outputs from phpMyAdmin CREATE TABLE `dbmail_acl` ( `user_id` bigint(10) unsigned NOT NULL, `mailbox_id` bigint(10) unsigned NOT NULL, `lookup_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `read_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `seen_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `write_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `insert_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `post_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `create_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `delete_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `administer_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', 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=COMPRESSED; CREATE TABLE `dbmail_aliases` ( `alias_idnr` int(10) unsigned NOT NULL AUTO_INCREMENT, `alias` varchar(100) NOT NULL DEFAULT '', `deliver_to` varchar(250) NOT NULL DEFAULT '', `client_idnr` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`alias_idnr`), UNIQUE KEY `alias` (`alias`,`deliver_to`), KEY `alias_index` (`alias`), KEY `client_idnr_index` (`client_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_auto_notifications` ( `user_idnr` bigint(10) unsigned NOT NULL, `notify_address` varchar(100) NOT NULL, KEY `user_idnr_index` (`user_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_auto_replies` ( `user_idnr` bigint(10) unsigned NOT NULL DEFAULT '0', `start_date` datetime NOT NULL, `stop_date` datetime NOT NULL, `reply_body` mediumtext, KEY `user_idnr_index` (`user_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_ccfield` ( `physmessage_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `ccname` varchar(100) NOT NULL DEFAULT '', `ccaddr` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`id`), KEY `dbmail_ccfield_1` (`ccname`), KEY `dbmail_ccfield_2` (`ccaddr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_datefield` ( `physmessage_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `datefield` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`datefield`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_envelope` ( `physmessage_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `envelope` text NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_fromfield` ( `physmessage_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `fromname` varchar(100) NOT NULL DEFAULT '', `fromaddr` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`id`), KEY `dbmail_fromfield_1` (`fromname`), KEY `dbmail_fromfield_2` (`fromaddr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_headername` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `headername` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `headername` (`headername`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_headervalue` ( `headername_id` bigint(20) unsigned NOT NULL, `physmessage_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `headervalue` text NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`id`), UNIQUE KEY `physmessage_id_2` (`physmessage_id`,`headername_id`,`headervalue`(255)), KEY `headername_id` (`headername_id`), KEY `physmessage_id_3` (`physmessage_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_mailboxes` ( `mailbox_idnr` bigint(10) unsigned NOT NULL AUTO_INCREMENT, `owner_idnr` bigint(10) unsigned NOT NULL DEFAULT '0', `name` varchar(100) NOT NULL DEFAULT '', `seen_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `answered_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `deleted_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `flagged_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `recent_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `draft_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `no_inferiors` tinyint(3) unsigned NOT NULL DEFAULT '0', `no_select` tinyint(3) unsigned NOT NULL DEFAULT '0', `permission` tinyint(3) unsigned DEFAULT '2', PRIMARY KEY (`mailbox_idnr`), UNIQUE KEY `owner_idnr_name_index` (`owner_idnr`,`name`), KEY `name_index` (`name`), KEY `owner_idnr_index` (`owner_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_messageblks` ( `messageblk_idnr` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0', `messageblk` longblob NOT NULL, `blocksize` bigint(20) unsigned NOT NULL DEFAULT '0', `is_header` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`messageblk_idnr`), KEY `physmessage_id_index` (`physmessage_id`), KEY `physmessage_id_is_header_index` (`physmessage_id`,`is_header`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_messages` ( `message_idnr` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `mailbox_idnr` bigint(10) unsigned NOT NULL DEFAULT '0', `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0', `seen_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `answered_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `deleted_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `flagged_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `recent_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `draft_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `unique_id` varchar(70) NOT NULL DEFAULT '', `status` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`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`), KEY `mailbox_status` (`mailbox_idnr`,`status`), KEY `deleted_flag_index` (`deleted_flag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_pbsp` ( `idnr` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `since` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `ipnumber` varchar(40) NOT NULL, PRIMARY KEY (`idnr`), UNIQUE KEY `ipnumber_index` (`ipnumber`), KEY `since_index` (`since`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_physmessage` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `messagesize` bigint(20) unsigned NOT NULL DEFAULT '0', `rfcsize` bigint(20) unsigned NOT NULL DEFAULT '0', `internal_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_referencesfield` ( `physmessage_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `referencesfield` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`referencesfield`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_replycache` ( `to_addr` varchar(100) NOT NULL DEFAULT '', `from_addr` varchar(100) NOT NULL DEFAULT '', `handle` varchar(100) NOT NULL DEFAULT '', `lastseen` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', UNIQUE KEY `replycache_1` (`to_addr`,`from_addr`,`handle`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_replytofield` ( `physmessage_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `replytoname` varchar(100) NOT NULL DEFAULT '', `replytoaddr` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`id`), KEY `dbmail_replytofield_1` (`replytoname`), KEY `dbmail_replytofield_2` (`replytoaddr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_sievescripts` ( `owner_idnr` bigint(10) unsigned NOT NULL DEFAULT '0', `name` varchar(100) NOT NULL, `script` text, `active` tinyint(3) unsigned NOT NULL DEFAULT '0', KEY `name` (`name`), KEY `owner_idnr` (`owner_idnr`), KEY `owner_idnr_2` (`owner_idnr`,`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_subjectfield` ( `physmessage_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `subjectfield` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`subjectfield`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_subscription` ( `user_id` bigint(10) unsigned NOT NULL DEFAULT '0', `mailbox_id` bigint(10) unsigned 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=COMPRESSED; CREATE TABLE `dbmail_tofield` ( `physmessage_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `toname` varchar(100) NOT NULL DEFAULT '', `toaddr` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`id`), KEY `dbmail_fromfield_1` (`toname`), KEY `dbmail_fromfield_2` (`toaddr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_usermap` ( `login` varchar(100) NOT NULL, `sock_allow` varchar(100) NOT NULL, `sock_deny` varchar(100) NOT NULL, `userid` varchar(100) NOT NULL, UNIQUE KEY `usermap_idx_1` (`login`,`sock_allow`,`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; CREATE TABLE `dbmail_users` ( `user_idnr` bigint(10) unsigned NOT NULL AUTO_INCREMENT, `userid` varchar(100) NOT NULL DEFAULT '', `passwd` varchar(34) NOT NULL DEFAULT '', `client_idnr` int(10) unsigned NOT NULL DEFAULT '0', `maxmail_size` bigint(20) unsigned NOT NULL DEFAULT '0', `curmail_size` bigint(20) unsigned NOT NULL DEFAULT '0', `maxsieve_size` bigint(20) unsigned NOT NULL DEFAULT '0', `cursieve_size` bigint(20) unsigned NOT NULL DEFAULT '0', `encryption_type` varchar(20) NOT NULL DEFAULT '', `last_login` datetime NOT NULL DEFAULT '1979-11-03 22:05:58', `user_realname` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`user_idnr`), UNIQUE KEY `userid_index` (`userid`), KEY `client_idnr` (`client_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; ALTER TABLE `dbmail_acl` ADD CONSTRAINT `dbmail_acl_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_acl_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_auto_notifications` ADD CONSTRAINT `dbmail_auto_notifications_ibfk_1` FOREIGN KEY (`user_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_auto_replies` ADD CONSTRAINT `dbmail_auto_replies_ibfk_1` FOREIGN KEY (`user_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_ccfield` ADD CONSTRAINT `dbmail_ccfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_datefield` ADD CONSTRAINT `dbmail_datefield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_envelope` ADD CONSTRAINT `dbmail_envelope_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_fromfield` ADD CONSTRAINT `dbmail_fromfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_headervalue` ADD CONSTRAINT `dbmail_headervalue_ibfk_1` FOREIGN KEY (`headername_id`) REFERENCES `dbmail_headername` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_headervalue_ibfk_2` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_mailboxes` ADD CONSTRAINT `dbmail_mailboxes_ibfk_1` FOREIGN KEY (`owner_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_messageblks` ADD CONSTRAINT `dbmail_messageblks_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_messages` ADD CONSTRAINT `dbmail_messages_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`mailbox_idnr`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_referencesfield` ADD CONSTRAINT `dbmail_referencesfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_replytofield` ADD CONSTRAINT `dbmail_replytofield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_sievescripts` ADD CONSTRAINT `dbmail_sievescripts_ibfk_1` FOREIGN KEY (`owner_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_subjectfield` ADD CONSTRAINT `dbmail_subjectfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_subscription` ADD CONSTRAINT `dbmail_subscription_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_subscription_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_tofield` ADD CONSTRAINT `dbmail_tofield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; Am 07.10.2010 15:49, schrieb Paul J Stevens: > So why 'upgrade' to mysql-5.1? > > Mentioned message stinks! Make a schema dump of your database and take a > long hard look at it! Or post it here, if you think that's appropriate. > > And start planning a downgrade to 5.0 if 5.1 turns out to be buggy > (again/still/forever). > > > > On 10/07/2010 02:36 PM, Reindl Harald wrote: >> Hm this should be unchanged since last summer >> and some days before with mysql 5.0.50 >> unconnected messages were removed successfully >> >> Am 07.10.2010 14:32, schrieb Paul J Stevens: >>> On 10/07/2010 01:12 PM, Reindl Harald wrote: >>>> Has anybody the same messages since MySQL 5.1? >>>> The last days it was only one, the second since tonight >>>> _____________________ >>>> >>>> Repairing DBMAIL physmessage integrity... >>>> Ok. Found [2] unconnected physmessagesWarning: could not delete orphaned >>>> physmessages. Check log. >>>> >>>> InnoDB: Cannot delete/update rows with cascading foreign key constraints >>>> that exceed max depth of 250 >>>> Please drop excessive foreign constraints and try again >>> >>> That sounds like a circular key restraint. Better check your schema! >> _______________________________________________ >> DBmail mailing list >> [email protected] >> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail > > -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
