C# MySqlCommand Multiple Drop Foreign Key problem

Question

I'm hoping someone can help me with this problem.

I have a small app to 'patch' our DB as we need to augment the functionality and fix bugs. It reads in the patches which are just files with SQL statements and does some internal housekeeping, querying the DB and applying the patches that haven't been applied. Anyhow, this is the solution we have, good or bad, and it has worked pretty well... up until now.

I've found it necessary to drop the existing foreign key constraints on a number of tables and replace them with an ON DELETE CASCADE clause instead of the ON DELETE RESTRICT that they currently have. The idea being that the deletion of the PK on the table that theses other tables reference will result in the cleanup of all the others.

Essentially the patch file has the following in it:

ALTER TABLE `mydb`.`table2` DROP FOREIGN KEY `fk_table2_id`;
ALTER TABLE `mydb`.`table3` DROP FOREIGN KEY `fk_table3_id`;
ALTER TABLE `mydb`.`table4` DROP FOREIGN KEY `fk_table4_id`;

and the associated

ALTER TABLE `mydb`.`table2` ADD CONSTRAINT `fk_table2_id` FOREIGN KEY `fk_table_id` (`fk_the_id`) REFERENCES `mydb` (`id`)
ON DELETE CASCADE
ON UPDATE RESTRICT;

etc...

On the C# side, when I pass this string (the SQL statements found in the patch file) as follows:

MySqlCommand myCommand = new MySqlCommand(thePatch);
myCommand.Connection = connection;
myCommand.ExecuteNonQuery();

I get the following error message:

Error on rename of '.\mydb\table2' to '.\mydb#sql2-6a8-3f' (errno: 152)

I only get this when I've got two of these DROP FOREIGN KEY lines in the string.

I've made sure that the keys listed on the DROP lines are the keys and not the column names and everything else appears (to me at least) to be OK.

I've included mulitple SQL statements in a single MySqlCommand and had no problems, so I'm a bit frustrated.

I can take the same file and pipe it to mysql.exe from the command line and it works fine, so it seems like a difference in how the statements are handled.

Does anyone have any ideas on this one?

Thanks, Matt

Answer 1

I don't know the full answer, but this may help troubleshoot a little deeper. When you alter a table MySQL uses the following steps: 1) CREATE TABLE newTableName with the new specifications 2) SELECT * FROM oldTableName INTO newTableName 3) Rename oldTableName to some intermediateTableName 4) rename newTableName to OldTableName 5) DROP TABLE intermediateTableName

The error you mention looks like it maybe failing step 3 here. You might check file permissions. Beyond that I will defer to those with greater knowledge about MySQL internals.