problem with altering a table

[Alex]

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;