C # MySqlCommand Multiple Drop Foreign Key problem
I hope someone can help me with this problem.
I have a small application to "fix" our database as we need to increase functionality and fix bugs. It reads in patches, which are just files with SQL statements, and does some internal work, querying the database and applying patches that were not applied. Anyway, this is the solution we have, good or bad, and it works really well ... so far.
I found it necessary to drop the existing foreign key constraints on multiple tables and replace them with the ON DELETE CASCADE clause instead of the ON ON DELETE RESTRICT they currently have. The idea is that deleting PK on a table that references other tables will clear all 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 related
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 line (SQL statements found in the patch file) like this:
MySqlCommand myCommand = new MySqlCommand(thePatch);
myCommand.Connection = connection;
myCommand.ExecuteNonQuery();
The following error message appears:
Error while renaming '. \ Mydb \ table2' to '. \ Mydb # sql2-6a8-3f' (errno: 152)
I only get this when I have two of these DROP FOREIGN KEY lines in a row.
I've made sure the keys listed on the DROP lines are keys, not column names, and everything else appears (at least for me) to be okay.
I have included mulitple SQL statements in one MySqlCommand and had no problem, so I'm a little frustrated.
I can take the same file and pipe it to mysql.exe from the command line and it works great, so it seems like the difference is in how the statements are handled.
Does anyone have any ideas on this?
Thanks, Matt
source to share
I donβt know the complete answer, but it might go a little deeper into the problem. When changing a table, MySQL uses the following steps: 1) CREATE TABLE newTableName with new specifications 2) SELECT * FROM oldTableName INTO newTableName 3) Rename oldTableName to some intermediate table name 4) rename newTableName to OldTableName 5) DROP TABLE middleTableName
The error you are talking about looks like it might be bad step 3 here. You can check the file permissions. Apart from this, I will postpone those with more knowledge of MySQL internals.
source to share