I have an install of dbmail, which has gone through version 1.x, 2.0.x,
2.1.x and now is at 2.2.1. Quite a lot of migrate scripts worked on the
db, in the end there were tables which still weren't InnoDB, fixed that
manually.
I found that at least two tables have constraints that shouldn't be there:
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 `mailbox_idnr` (`mailbox_idnr`),
KEY `seen_flag` (`seen_flag`),
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` (`mailbox_idnr`) ON DELETE
CASCADE,
CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`mailbox_idnr`)
REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE
CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_messages_ibfk_3` FOREIGN KEY (`physmessage_id`)
REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCAD
E ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
and
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',
`kred_log_actions` tinyint(1) NOT NULL,
`kred_delete_folder_flag` tinyint(1) NOT NULL,
PRIMARY KEY (`mailbox_idnr`),
UNIQUE KEY `mailboxidnr_2` (`mailbox_idnr`),
UNIQUE KEY `name_owner_idnr_index` (`owner_idnr`,`name`),
KEY `owner_idnr` (`owner_idnr`),
KEY `name` (`name`),
KEY `name_index` (`name`),
KEY `owner_idnr_index` (`owner_idnr`),
CONSTRAINT `dbmail_mailboxes_ibfk_1` FOREIGN KEY (`owner_idnr`)
REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE,
CONSTRAINT `dbmail_mailboxes_ibfk_2` FOREIGN KEY (`owner_idnr`)
REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
I don't know what impact it has on performace or integrity, but they
sure got to go. Is it safe to DROP dbmail_messages_ibfk_1 and
dbmail_mailboxes_ibfk_1?
There seem to be no more additional constraints in other tables but
maybe there are other inconsistencies like missing indexes or changed
column types. I just don't know and it seems too much to check manually.
The only idea how to make sure everything is OK seams to be to dump the
database. sed s/ the dump file to remove the DROP TABLE IF EXISTS and
CREATE TABLE commands. Create the tables using the create_tables.sql
from latest dbmail and then source the edited dump file.
sed'ing the dump won't be fun though, it's about 14GBytes. Maybe there's
a way to make mysql ignore DROP and CREATE commands while sourcing the dump?
Any more convenient way to make sure all the tables etc are OK?
Thanks,
Alex
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
