Paul J Stevens wrote:
Alex, I'm guessing here but given the error code it's most likely the
foreign key restraint that's causing the failure.
You mean removing the
" ADD FOREIGN KEY physmessage_id_fk (physmessage_id)
REFERENCES dbmail_physmessage (id)
ON DELETE CASCADE ON UPDATE CASCADE;
"
bit? No difference at all, ALTER fails with 150.
Also, have you checked
the type of your current tables? Are they all innodb already?
Yes, I've tried recreating all the tables like the dump does (1.1
version) and applying the migration script. The same error. And yes,
they already are all InnoDB after loading the dump. SHOW CREATE TABLE
tells me that.
Now I've tried removing as many lines as possible. The freaky thing,
even this 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;
"
Only the DROP INDEX'es are left. When I comment the "DROP INDEX
msg_index;" line, then ALTER succeeds. To test, that msg_index is to
blame I only left the one DROP.
As expected this fails:
"
ALTER TABLE dbmail_messageblks
# DROP INDEX messageblk_idnr,
# DROP INDEX messageblk_idnr_2,
DROP INDEX msg_index;
# ...
"
But I can't really blame it on that line alone, that just seems to
fishy. And here's the proof:
This one fails, although there's no DROPping:
"
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;
"
By fails I mean errno 150 with ALTER, no syntax errors or anything like
that.
I test the migrating stuff like this:
$ mysql mail2 < recreate_dump.sql && mysql mail2 <
migrate_from_1.x_to_2.0_innodb.mysql
ERROR 1025 (HY000) at line 145: Error on rename of
'./mail2/#sql-299a_70' to './mail2/dbmail_messageblks' (errno: 150)
recreate_dump.sql is a script which drops all the tables if they exist
and recreates new tables like the dump from version 1.1 did. All InnoDB.
Hope you could follow this.
I'm really lost here,
Alex
