Oh you have MySQL, sorry, my mistake. That's good, I know it better :-)
So first you DO want to take a backup. Of course we will all try not to
break anything, but no guarantees.
Run the attached MySQL script (instructions are in the file just after
the copyright) and you should be in business again!
Aaron
On Mon, 2007-05-14 at 14:42 +0200, Andrea Brancatelli wrote:
> Your suspicion was right.
>
>
> Now I have two foreign key definitions, one with the on update and one
> without.
>
>
> The problem is that the message base was exported and reimported
> between two server with mysqldump and mysqldump doesn't dump this kind
> of information (at least not that I can see).
>
>
> How can I fix everything quickly (maybe the other tables as well?)
>
>
> Maybe I can run any of the upgrade scripts (like the 2.0 to 2.2 one)?
>
> Il giorno 14/mag/07, alle ore 14:11, Aaron Stone ha scritto:
>
> > Please log into your database with psql on the command line, and
> > post
> > back the output of '\d dbmail_datefield'. I suspect that you might
> > be
> > missing the "ON UPDATE CASCADE ON DELETE CASCADE" part of the
> > foreign
> > key definition.
> >
> >
> > Aaron
> >
> >
> > On Wed, 2007-05-09 at 14:22 +0200, Andrea Brancatelli wrote:
> > > Some users have been reporting errors with their mail since a
> > > while,
> > > today I sneeked a look and found this:
> > >
> > >
> > >
> > >
> > >
> > >
> > > May 9 14:08:31 carota dbmail/smtp[363]: Error [Cannot delete or
> > > update a parent row: a foreign key constraint fails
> > > (`dbmail`.`dbmail_datefield`, CONSTRAINT `dbmail_datefield_ibfk_1`
> > > FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage`
> > > (`id`))] [DELETE FROM dbmail_physmessage WHERE id = 147004]
> > >
> > >
> > > May 9 14:08:31 carota dbmail/smtp[363]: Error failed to delete
> > > temporary message [243478]
> > >
> > >
> > >
> > >
> > >
> > >
> > > In the log….
> > >
> > >
> > >
> > >
> > >
> > >
> > > I run dbmail-util –a –y (that is running every night anyhow)
> > > and it
> > > gave me this:
> > >
> > >
> > >
> > >
> > >
> > >
> > > Opening connection to database...
> > >
> > >
> > > Opening connection to authentication...
> > >
> > >
> > > Ok. Connected.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Repairing DBMAIL messageblocks integrity...
> > >
> > >
> > > Ok. Found [0] unconnected messageblks.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Checking DBMAIL message integrity...
> > >
> > >
> > > Ok. Found [0] unconnected messages.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Checking DBMAIL mailbox integrity...
> > >
> > >
> > > Ok. Found [0] unconnected mailboxes.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Repairing DBMAIL for NULL messages...
> > >
> > >
> > > Ok. Found [0] NULL messages.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Checking DBMAIL for NULL physmessages...
> > >
> > >
> > > Ok. Found [0] physmessages without messageblocks.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Deleting messages with DELETE status...
> > >
> > >
> > > Error [Cannot delete or update a parent row: a foreign key
> > > constraint
> > > fails (`dbmail`.`dbmail_datefield`, CONSTRAINT
> > > `dbmail_datefield_ibfk_1` FOREIGN KEY (`physmessage_id`)
> > > REFERENCES
> > > `dbmail_physmessage` (`id`))] [DELETE FROM dbmail_physmessage
> > > WHERE id
> > > = 2774]
> > >
> > >
> > > Error error deleting message
> > >
> > >
> > > Failed. An error occured. Please check log.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Repairing DBMAIL for rfcsize field...
> > >
> > >
> > > Ok. Found [0] missing rfcsize values.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Repairing DBMAIL for incorrect is_header flags...
> > >
> > >
> > > Ok. Found [0] incorrect is_header flags.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Repairing DBMAIL for cached envelopes...
> > >
> > >
> > > Ok. Found [0] missing envelope values.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Repairing DBMAIL for cached header values...
> > >
> > >
> > > Ok. Found [0] un-cached physmessages.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Setting DELETE status for deleted messages...
> > >
> > >
> > > Ok. [236] messages set for deletion.
> > >
> > >
> > > Re-calculating used quota for all users...
> > >
> > >
> > > Ok. Used quota updated for all users.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Vacuuming and optimizing database...
> > >
> > >
> > > Ok. Database cleaned up.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Maintenance done. No errors found.
> > >
> > >
> > >
> > >
> > >
> > >
> > > What’s happening?
> > >
> > >
> > >
> > >
> > >
> > >
> > > How can I fix this?
> > >
> > >
> > >
> > >
> > >
> > >
> > > Thanks ;)
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > _______________________________________________
> > > DBmail mailing list
> > > [email protected]
> > > https://mailman.fastxs.nl/mailman/listinfo/dbmail
> >
> >
> > _______________________________________________
> > DBmail mailing list
> > [email protected]
> > https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
> Andrea Brancatelli
> [EMAIL PROTECTED]
>
>
>
>
>
> _______________________________________________
> DBmail mailing list
> [email protected]
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
#
# Copyright (c) 2004-2006, NFG Net Facilities Group BV, [EMAIL PROTECTED]
# Copyright (c) 2006 Aaron Stone, [EMAIL PROTECTED]
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later
# version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#
# Schema to repair broken foreign key constraints.
# Run thusly:
# mysql -u dbmail_user -p dbmail_pass dbmail_db -f < fix_foreign_keys.mysql
#
# You will probably see a lot of errors. That's OK, because we're not sure what
# the foreign keys are called in your database (since they're broken!) and
# programmed in a couple of guesses to try to get them.
#
# The errors you can ignore will look similar to this one:
# ERROR 1025 (HY000) at line 37: Error on rename of
# './dbmail/dbmail_mailboxes' to './dbmail/#sql2-6487-4c' (errno: 152)
#
# After this script runs, you must run:
# dbmail-util -by
#
# As we have dropped several of the ephemeral tables outright, they have to be
# rebuilt from the message store.
#
SET FOREIGN_KEY_CHECKS=0;
alter table dbmail_mailboxes drop foreign key owner_idnr_fk;
alter table dbmail_mailboxes drop foreign key dbmail_mailboxes_ibfk;
alter table dbmail_mailboxes drop foreign key dbmail_mailboxes_ibfk_1;
alter table dbmail_mailboxes add FOREIGN KEY owner_idnr_fk (owner_idnr)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE;
alter table dbmail_subscription drop foreign key user_id_fk;
alter table dbmail_subscription drop foreign key mailbox_id_fk;
alter table dbmail_subscription drop foreign key dbmail_subscription_ibfk_1;
alter table dbmail_subscription drop foreign key dbmail_subscription_ibfk_2;
alter table dbmail_subscription drop foreign key dbmail_subscription_ibfk_3;
alter table dbmail_subscription add FOREIGN KEY user_id_fk (user_id)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE;
alter table dbmail_subscription add FOREIGN KEY mailbox_id_fk (mailbox_id)
REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE CASCADE ON
UPDATE CASCADE;
alter table dbmail_acl drop foreign key user_id_fk;
alter table dbmail_acl drop foreign key mailbox_id_fk;
alter table dbmail_acl drop foreign key dbmail_acl_ibfk_1;
alter table dbmail_acl drop foreign key dbmail_acl_ibfk_2;
alter table dbmail_acl drop foreign key dbmail_acl_ibfk_3;
alter table dbmail_acl add FOREIGN KEY user_id_fk (user_id)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE;
alter table dbmail_acl add FOREIGN KEY mailbox_id_fk (mailbox_id)
REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE CASCADE ON
UPDATE CASCADE;
alter table dbmail_messages drop foreign key physmessage_id_fk;
alter table dbmail_messages drop foreign key mailbox_idnr_fk;
alter table dbmail_messages drop foreign key dbmail_messages_ibfk_1;
alter table dbmail_messages drop foreign key dbmail_messages_ibfk_2;
alter table dbmail_messages drop foreign key dbmail_messages_ibfk_3;
alter table dbmail_messages add FOREIGN KEY physmessage_id_fk (physmessage_id)
REFERENCES dbmail_physmessage (id) ON DELETE CASCADE ON UPDATE
CASCADE;
alter table dbmail_messages add FOREIGN KEY mailbox_idnr_fk (mailbox_idnr)
REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE CASCADE ON
UPDATE CASCADE;
alter table dbmail_messageblks drop foreign key physmessage_id_fk;
alter table dbmail_messageblks drop foreign key dbmail_messageblks_ibfk_1;
alter table dbmail_messageblks drop foreign key dbmail_messageblks_ibfk_2;
alter table dbmail_messageblks add FOREIGN KEY physmessage_id_fk
(physmessage_id)
REFERENCES dbmail_physmessage (id) ON DELETE CASCADE ON UPDATE
CASCADE;
alter table dbmail_auto_notifications drop foreign key physmessage_id_fk;
alter table dbmail_auto_notifications drop foreign key
dbmail_auto_notifications_ibfk_1;
alter table dbmail_auto_notifications drop foreign key
dbmail_auto_notifications_ibfk_2;
alter table dbmail_auto_notifications add FOREIGN KEY user_idnr_fk (user_idnr)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE;
alter table dbmail_auto_replies drop foreign key physmessage_id_fk;
alter table dbmail_auto_replies drop foreign key dbmail_auto_replies_ibfk_1;
alter table dbmail_auto_replies drop foreign key dbmail_auto_replies_ibfk_2;
alter table dbmail_auto_replies add FOREIGN KEY user_idnr_fk (user_idnr)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE;
alter table dbmail_sievescripts drop foreign key physmessage_id_fk;
alter table dbmail_sievescripts drop foreign key dbmail_sievescripts_ibfk_1;
alter table dbmail_sievescripts drop foreign key dbmail_sievescripts_ibfk_2;
alter table dbmail_sievescripts add FOREIGN KEY owner_idnr_fk (owner_idnr)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE;
DROP TABLE IF EXISTS dbmail_headervalue;
CREATE TABLE dbmail_headervalue (
headername_id BIGINT NOT NULL,
physmessage_id BIGINT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
headervalue TEXT NOT NULL,
PRIMARY KEY (id),
UNIQUE (physmessage_id, id),
UNIQUE (physmessage_id, headername_id, headervalue(255)),
INDEX (headername_id),
INDEX (physmessage_id),
FOREIGN KEY (headername_id)
REFERENCES dbmail_headername(id) ON UPDATE CASCADE ON DELETE
CASCADE,
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE
CASCADE
) ENGINE=InnoDB ;
# Provide separate storage of commonly used headers
# These fields will typically be preparsed as specified by
# http://www.ietf.org/internet-drafts/draft-ietf-imapext-sort-17.txt
# Threading
# support fast threading by breaking out In-Reply-To/References headers
# these fields contain zero or more Message-Id values that determine the message
# threading
DROP TABLE IF EXISTS dbmail_subjectfield;
CREATE TABLE dbmail_subjectfield (
physmessage_id BIGINT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
subjectfield VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE (physmessage_id, subjectfield),
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;
DROP TABLE IF EXISTS dbmail_datefield;
CREATE TABLE dbmail_datefield (
physmessage_id BIGINT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
datefield DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
PRIMARY KEY (id),
UNIQUE (physmessage_id, datefield),
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;
DROP TABLE IF EXISTS dbmail_referencesfield;
CREATE TABLE dbmail_referencesfield (
physmessage_id BIGINT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
referencesfield VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE (physmessage_id, referencesfield),
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;
# Searching and Sorting
# support fast sorting by breaking out and preparsing the fields most commonly
used
# in searching and sorting: Subject, From, To, Reply-To, Cc.
DROP TABLE IF EXISTS dbmail_fromfield;
CREATE TABLE dbmail_fromfield (
physmessage_id BIGINT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
fromname VARCHAR(100) NOT NULL DEFAULT '',
fromaddr VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE (physmessage_id, id),
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;
DROP TABLE IF EXISTS dbmail_tofield;
CREATE TABLE dbmail_tofield (
physmessage_id BIGINT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
toname VARCHAR(100) NOT NULL DEFAULT '',
toaddr VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE (physmessage_id, id),
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;
DROP TABLE IF EXISTS dbmail_replytofield;
CREATE TABLE dbmail_replytofield (
physmessage_id BIGINT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
replytoname VARCHAR(100) NOT NULL DEFAULT '',
replytoaddr VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE (physmessage_id, id),
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;
DROP TABLE IF EXISTS dbmail_ccfield;
CREATE TABLE dbmail_ccfield (
physmessage_id BIGINT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
ccname VARCHAR(100) NOT NULL DEFAULT '',
ccaddr VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE (physmessage_id, id),
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;
COMMIT;
DROP TABLE IF EXISTS dbmail_envelope;
CREATE TABLE dbmail_envelope (
physmessage_id BIGINT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
envelope TEXT NOT NULL,
PRIMARY KEY (id),
UNIQUE (physmessage_id, id),
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS=1;
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
