Re: problem with altering a table
Alex wrote: I'm running mysql 5.0.22 on SLES9, using the mysql.com appropriate rpm. I've tried other versions of mysql 5, including 5.0.6, 5.0.17, 5.0.18 and 5.0.21. The result is always the same. This leads me to believe, that there are new requirements for mysql 5 and that's the reason ALTER fails. I can't figure it out myself, please help. Here's again the create table + alter table which fails: SET FOREIGN_KEY_CHECKS=0; CREATE TABLE dbmail_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; SET FOREIGN_KEY_CHECKS=0; SET SQL_LOG_OFF=1; SET SQL_LOG_UPDATE=0; 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; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with altering a table
When creating the InnoDB table the InnoDB engine asigns to the FOREIGN KEY you defined a symbol. On my server it generated dbmail_messageblks_ibfk_1... and if in the ALTER statement I entered: DROP FOREIGN KEY dbmail_messageblks_ibfk_1 Then the ALTER table worked fine... If you want to continue with this you should add a symbol name manually like this in the create table statement: CONSTRAINT `fk_message_idnr_manually_set` FOREIGN KEY (`message_idnr`) REFERENCES `messages` (`message_idnr`) Now in the ALTER statement you will have to write before you change the name of the column: DROP FOREIGN KEY `fk_message_idnr_manually_set` If you DROP an index a FOREIGN KEY based on that index will not be dropped automaticaly... Hope this helps ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SOLVED] Re: problem with altering a table
Gabriel PREDA wrote: Hope this helps ! Thanks a bunch, that was it. Problem solved. I'll tell about it on the dbmail list as well. Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with altering a table
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]
