Devart Forums

Hi.

When I'm dropping 2 foreign keys I get an error

I'm using.
Borland C++ Builder 6
MyDAC version 4.40.0.26
MySQL server 5.0.67 (also tried on 5.0.41)

Below the code to reproduce the error:
(please correct server information in code)

******************** CODE START ********************

TMyConnection *MyConnection;
TMyCommand *MyCommand;

MyConnection = new TMyConnection(NULL);
MyConnection->Server = "127.0.0.1"; //Correct
MyConnection->Port = 3306; //Correct
MyConnection->Database = "test"; //Correct
MyConnection->Username = "root"; //Correct
MyConnection->Password = "password"; //Correct
MyConnection->LoginPrompt = false;

MyCommand = new TMyCommand(this);
MyCommand->Connection = MyConnection;

try
{
MyCommand->SQL->Clear();
MyCommand->SQL->Add("DROP TABLE IF EXISTS `c`;");
MyCommand->SQL->Add("DROP TABLE IF EXISTS `b`;");
MyCommand->SQL->Add("DROP TABLE IF EXISTS `a`;");
MyCommand->SQL->Add("CREATE TABLE `a` (");
MyCommand->SQL->Add(" `A_Id` int(10) unsigned NOT NULL auto_increment,");
MyCommand->SQL->Add(" `Name` varchar(45) NOT NULL,");
MyCommand->SQL->Add(" PRIMARY KEY USING BTREE (`A_Id`)");
MyCommand->SQL->Add(") ENGINE=InnoDB DEFAULT CHARSET=latin1;");
MyCommand->SQL->Add("");
MyCommand->SQL->Add("CREATE TABLE `b` (");
MyCommand->SQL->Add(" `B_Id` int(10) unsigned NOT NULL auto_increment,");
MyCommand->SQL->Add(" `CarName` varchar(45) NOT NULL,");
MyCommand->SQL->Add(" `A_Id` int(10) unsigned NOT NULL,");
MyCommand->SQL->Add(" PRIMARY KEY (`B_Id`),");
MyCommand->SQL->Add(" KEY `FK_b_a` (`A_Id`),");
MyCommand->SQL->Add(" CONSTRAINT `FK_b_a` FOREIGN KEY (`A_Id`) REFERENCES `a` (`A_Id`)");
MyCommand->SQL->Add(") ENGINE=InnoDB DEFAULT CHARSET=latin1;");
MyCommand->SQL->Add("");
MyCommand->SQL->Add("CREATE TABLE `c` (");
MyCommand->SQL->Add(" `C_Id` int(10) unsigned NOT NULL auto_increment,");
MyCommand->SQL->Add(" `HouseAddress` varchar(45) NOT NULL,");
MyCommand->SQL->Add(" `A_Id` int(10) unsigned NOT NULL,");
MyCommand->SQL->Add(" PRIMARY KEY (`C_Id`),");
MyCommand->SQL->Add(" KEY `FK_c_a` USING BTREE (`A_Id`),");
MyCommand->SQL->Add(" CONSTRAINT `FK_c_a` FOREIGN KEY (`A_Id`) REFERENCES `a` (`A_Id`)");
MyCommand->SQL->Add(") ENGINE=InnoDB DEFAULT CHARSET=latin1;");
MyCommand->Execute(0);

//WHEN DROPPING 1 FOREIGN KEY THERE IS NO PROBLEM
//BUT WHEN DROPPING 2 FOREIGN KEYS AN EXCEPTION OCCUR
MyCommand->SQL->Clear();
MyCommand->SQL->Add("ALTER TABLE `b` DROP FOREIGN KEY `FK_b_a`;");
MyCommand->SQL->Add("ALTER TABLE `b` DROP INDEX `FK_b_a`;");
MyCommand->SQL->Add("ALTER TABLE `c` DROP FOREIGN KEY `FK_c_a`;");
MyCommand->SQL->Add("ALTER TABLE `c` DROP INDEX `FK_c_a`;");
MyCommand->Execute(0);
}
catch (const Exception& e)
{
//Error occured from database
MessageBox(NULL, AnsiString("Error occured from database\n" + e.Message).c_str(), "Database", MB_OK | MB_ICONERROR | MB_TASKMODAL);
}

delete MyCommand;
delete MyConnection;

******************** CODE END ********************

Error: "HY000Error on rename of '.\test\b' to '.\test\#sql2-61c-a82' (errorno: 152)'
According to MySql forums this error occurs when not using the constraint name when deleting a foreign key. But that's not the problem here, since I am using the constraint name.
The wierd part is if I only drop one foreign key there is no problem, but when dropping two foreign keys the problem occurs.

The same query can run without errors in mysql.exe

Hope there is an explanation about this problem or a fix for it.

Best regards,
Thomas Jensen

The point is that TMyCommand executes SQL statements as one query, but MySQL client executes them as several separate queries.
When one SQL query drops several foreign keys MySQL server raises an error. To solve this problem you should divide the SQL query in two queries, like this: Also you can use the TMyScript component to execute several SQL statements one by one.

Ok thanks for the answer.

I just don't understand why MySQL server raises an error when dropping 2 foreign keys. While all other queries work just fine. Ex. dropping 2 tables works fine.

Are there other similar queries that will fail - like dropping 2 foreign keys?

Best regards, Thomas

We do not know about similar queries that will fail. It's a specifity of MySQL Server and is not connected with MyDAC.