Hi Paul,
Attached is the schema dump. I do see constraints on the tables, maybe
they're incorrect?
Thanks,
Josh.
Paul J Stevens wrote:
Please send a schema dump (mysqldump -d dbmail)
Sounds like your foreign key constraints aren't in place.
-- MySQL dump 10.11
--
-- Host: localhost Database: dbmail
-- ------------------------------------------------------
-- Server version 5.0.32-Debian_7etch1-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `autoresponder_data`
--
DROP TABLE IF EXISTS `autoresponder_data`;
CREATE TABLE `autoresponder_data` (
`active` tinyint(1) NOT NULL default '1',
`message` mediumtext NOT NULL,
`address` varchar(250) NOT NULL default '',
PRIMARY KEY (`address`),
KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_acl`
--
DROP TABLE IF EXISTS `dbmail_acl`;
CREATE TABLE `dbmail_acl` (
`user_id` bigint(21) NOT NULL,
`mailbox_id` bigint(21) NOT NULL,
`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_index` (`user_id`),
KEY `mailbox_id_index` (`mailbox_id`),
CONSTRAINT `dbmail_acl_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_acl_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_aliases`
--
DROP TABLE IF EXISTS `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 `alias_index` (`alias`),
KEY `client_idnr_index` (`client_idnr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_auto_notifications`
--
DROP TABLE IF EXISTS `dbmail_auto_notifications`;
CREATE TABLE `dbmail_auto_notifications` (
`user_idnr` bigint(21) NOT NULL,
`notify_address` varchar(100) NOT NULL,
KEY `user_idnr_index` (`user_idnr`),
CONSTRAINT `dbmail_auto_notifications_ibfk_1` FOREIGN KEY (`user_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_auto_replies`
--
DROP TABLE IF EXISTS `dbmail_auto_replies`;
CREATE TABLE `dbmail_auto_replies` (
`user_idnr` bigint(21) NOT NULL default '0',
`start_date` datetime NOT NULL,
`stop_date` datetime NOT NULL,
`reply_body` mediumtext,
KEY `user_idnr_index` (`user_idnr`),
CONSTRAINT `dbmail_auto_replies_ibfk_1` FOREIGN KEY (`user_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_ccfield`
--
DROP TABLE IF EXISTS `dbmail_ccfield`;
CREATE TABLE `dbmail_ccfield` (
`physmessage_id` bigint(20) NOT NULL,
`id` bigint(20) 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`),
CONSTRAINT `dbmail_ccfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_datefield`
--
DROP TABLE IF EXISTS `dbmail_datefield`;
CREATE TABLE `dbmail_datefield` (
`physmessage_id` bigint(20) NOT NULL,
`id` bigint(20) 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`),
CONSTRAINT `dbmail_datefield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_envelope`
--
DROP TABLE IF EXISTS `dbmail_envelope`;
CREATE TABLE `dbmail_envelope` (
`physmessage_id` bigint(20) NOT NULL,
`id` bigint(20) NOT NULL auto_increment,
`envelope` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `physmessage_id` (`physmessage_id`,`id`),
CONSTRAINT `dbmail_envelope_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_fromfield`
--
DROP TABLE IF EXISTS `dbmail_fromfield`;
CREATE TABLE `dbmail_fromfield` (
`physmessage_id` bigint(20) NOT NULL,
`id` bigint(20) 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`),
CONSTRAINT `dbmail_fromfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_headername`
--
DROP TABLE IF EXISTS `dbmail_headername`;
CREATE TABLE `dbmail_headername` (
`id` bigint(20) NOT NULL auto_increment,
`headername` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `headername` (`headername`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_headervalue`
--
DROP TABLE IF EXISTS `dbmail_headervalue`;
CREATE TABLE `dbmail_headervalue` (
`headername_id` bigint(20) NOT NULL,
`physmessage_id` bigint(20) NOT NULL,
`id` bigint(20) 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`),
CONSTRAINT `dbmail_headervalue_ibfk_1` FOREIGN KEY (`headername_id`) REFERENCES `dbmail_headername` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_headervalue_ibfk_2` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_mailboxes`
--
DROP TABLE IF EXISTS `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 `owner_idnr_name_index` (`owner_idnr`,`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 ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_messageblks`
--
DROP TABLE IF EXISTS `dbmail_messageblks`;
CREATE TABLE `dbmail_messageblks` (
`messageblk_idnr` bigint(21) NOT NULL auto_increment,
`physmessage_id` bigint(21) NOT NULL default '0',
`messageblk` longblob 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_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_messages`
--
DROP TABLE IF EXISTS `dbmail_messages`;
CREATE TABLE `dbmail_messages` (
`message_idnr` bigint(21) NOT NULL auto_increment,
`mailbox_idnr` bigint(21) NOT NULL default '0',
`physmessage_id` 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 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`),
CONSTRAINT `dbmail_messages_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`mailbox_idnr`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_pbsp`
--
DROP TABLE IF EXISTS `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,
PRIMARY KEY (`idnr`),
UNIQUE KEY `ipnumber_index` (`ipnumber`),
KEY `since_index` (`since`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_physmessage`
--
DROP TABLE IF EXISTS `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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_referencesfield`
--
DROP TABLE IF EXISTS `dbmail_referencesfield`;
CREATE TABLE `dbmail_referencesfield` (
`physmessage_id` bigint(20) NOT NULL,
`id` bigint(20) NOT NULL auto_increment,
`referencesfield` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `physmessage_id` (`physmessage_id`,`referencesfield`),
CONSTRAINT `dbmail_referencesfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_replycache`
--
DROP TABLE IF EXISTS `dbmail_replycache`;
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=latin1;
--
-- Table structure for table `dbmail_replytofield`
--
DROP TABLE IF EXISTS `dbmail_replytofield`;
CREATE TABLE `dbmail_replytofield` (
`physmessage_id` bigint(20) NOT NULL,
`id` bigint(20) 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`),
CONSTRAINT `dbmail_replytofield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_sievescripts`
--
DROP TABLE IF EXISTS `dbmail_sievescripts`;
CREATE TABLE `dbmail_sievescripts` (
`owner_idnr` bigint(21) NOT NULL default '0',
`name` varchar(100) NOT NULL,
`script` text,
`active` tinyint(1) NOT NULL default '0',
KEY `name` (`name`),
KEY `owner_idnr` (`owner_idnr`),
KEY `owner_idnr_2` (`owner_idnr`,`name`),
CONSTRAINT `dbmail_sievescripts_ibfk_1` FOREIGN KEY (`owner_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_subjectfield`
--
DROP TABLE IF EXISTS `dbmail_subjectfield`;
CREATE TABLE `dbmail_subjectfield` (
`physmessage_id` bigint(20) NOT NULL,
`id` bigint(20) NOT NULL auto_increment,
`subjectfield` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `physmessage_id` (`physmessage_id`,`subjectfield`),
CONSTRAINT `dbmail_subjectfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_subscription`
--
DROP TABLE IF EXISTS `dbmail_subscription`;
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`),
CONSTRAINT `dbmail_subscription_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_subscription_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_tofield`
--
DROP TABLE IF EXISTS `dbmail_tofield`;
CREATE TABLE `dbmail_tofield` (
`physmessage_id` bigint(20) NOT NULL,
`id` bigint(20) 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`),
CONSTRAINT `dbmail_tofield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `dbmail_usermap`
--
DROP TABLE IF EXISTS `dbmail_usermap`;
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=latin1;
--
-- Table structure for table `dbmail_users`
--
DROP TABLE IF EXISTS `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',
`curmail_size` bigint(21) NOT NULL default '0',
`maxsieve_size` bigint(20) NOT NULL default '0',
`cursieve_size` bigint(20) NOT NULL default '0',
`encryption_type` varchar(20) NOT NULL default '',
`last_login` datetime NOT NULL default '1979-11-03 22:05:58',
PRIMARY KEY (`user_idnr`),
UNIQUE KEY `userid_index` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `domains`
--
DROP TABLE IF EXISTS `domains`;
CREATE TABLE `domains` (
`domain` varchar(100) NOT NULL,
PRIMARY KEY (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `forwards`
--
DROP TABLE IF EXISTS `forwards`;
CREATE TABLE `forwards` (
`id` int(11) NOT NULL auto_increment,
`origto` varchar(100) NOT NULL,
`deliverto` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `origto` (`origto`,`deliverto`),
KEY `email` (`origto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `recipient_access`
--
DROP TABLE IF EXISTS `recipient_access`;
CREATE TABLE `recipient_access` (
`id` int(10) unsigned NOT NULL auto_increment,
`source` varchar(128) NOT NULL default '',
`access` varchar(128) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `relay_domains`
--
DROP TABLE IF EXISTS `relay_domains`;
CREATE TABLE `relay_domains` (
`domain` varchar(100) NOT NULL,
PRIMARY KEY (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
-- Dump completed on 2008-05-08 22:00:47
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
