Re: [Dbmail] unconnected mailboxes all the time

[Matthew O'Connor]

It might be a good idea to document the current schema including FKs and 
recommeded indexes to the wiki or somewhere.


[EMAIL PROTECTED] wrote:
Hi,

For situations like this one, for who update via SVN, that Paul and Aaron to 
add that information to the Changelog file.
I have a similar situation that it was needed a update to the tables but them i 
went to sql/mysql directory and i saw that was something new, it was the 
change_headervalues.sql or something.

What do you think Paul/Aaron ?

Jorge



On Thu, 14 Sep 2006 11:56:37 +0200 (CEST), "Anne" <[EMAIL PROTECTED]> wrote:
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
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail


_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail