Hi,
I'm using dbmail - a program that holds mails in a database, for example
mysql. I'm in the process of migrating an old installation to the
current one. dbmail does provide a migration script, but I've
encountered a problem. It definitely is mysql specific and I haven't yet
gotten a response from the dbmail list.
I've narrowed the problem down and have a script ready, which reproduces
the problem.
I'm running mysql 5.0.22 on SLES9, using the mysql.com appropriate rpm.
This bugreport might be relevant: http://bugs.mysql.com/bug.php?id=13778
This is part of what happens during the migration process. I first load
a dump of the old version and then launch the migration sql script.
I get this output:
mysql mail2 < test.sql
ERROR 1025 (HY000) at line 34: Error on rename of './mail2/#sql-16e4_93'
to './mail2/dbmail_messageblks' (errno: 150)
Appreciate any help,
Alex
Here's the script, launch it on a db without dbmail_messageblks nor
messageblks table:
### this part is from the dbmail 1.1 dump with mysql Ver 12.18 Distrib
4.0.12, for pc-linux (i686)
## first I disable foreign key cheks, otherwise I wouldn't be able to
create the table
SET FOREIGN_KEY_CHECKS=0;
## creating the table as loading the dump does. all successful
CREATE TABLE messageblks (
messageblk_idnr bigint(21) NOT NULL auto_increment,
message_idnr bigint(21) NOT NULL default '0',
messageblk longtext NOT NULL,
blocksize bigint(21) NOT NULL default '0',
PRIMARY KEY (messageblk_idnr),
UNIQUE KEY messageblk_idnr_2 (messageblk_idnr),
KEY messageblk_idnr (messageblk_idnr),
KEY msg_index (message_idnr),
FOREIGN KEY (`message_idnr`) REFERENCES `messages` (`message_idnr`)
ON DELETE CASCADE
) TYPE=InnoDB;
#### from the migration script migrate_from_1.x_to_2.0_innodb.mysql.
SET FOREIGN_KEY_CHECKS=0;
SET SQL_LOG_OFF=1;
SET SQL_LOG_UPDATE=0;
# start a transaction.
BEGIN WORK;
# alter messageblks table
RENAME TABLE messageblks TO dbmail_messageblks;
###### this is the part that fails
ALTER TABLE dbmail_messageblks
DROP INDEX messageblk_idnr,
DROP INDEX messageblk_idnr_2,
DROP INDEX msg_index,
CHANGE message_idnr physmessage_id bigint(21) NOT NULL DEFAULT '0',
ADD COLUMN is_header tinyint(1) DEFAULT '0' NOT NULL,
ADD INDEX physmessage_id_index (physmessage_id),
ADD INDEX physmessage_id_is_header_index (physmessage_id,
is_header),
ADD FOREIGN KEY physmessage_id_fk (physmessage_id)
REFERENCES dbmail_physmessage (id)
ON DELETE CASCADE ON UPDATE CASCADE;
UPDATE dbmail_messageblks SET is_header = '0';
COMMIT;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
