Description:
Multi statement execution fails in 5.1
How to repeat:
#include <stdio.h>
#include <mysql.h>
int main ()
{
MYSQL *mysql = mysql_init(NULL);
MYSQL_RES *res;
int i, status;
char *sql[] = {
"SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO",
"DROP TABLE IF EXISTS `bank_transactions`",
"CREATE TABLE `bank_transactions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`client_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `client` (`client_id`))
ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
"DROP TABLE IF EXISTS `clients`",
"CREATE TABLE `clients` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY
(`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
"ALTER TABLE `bank_transactions` ADD CONSTRAINT `bank_transactions_ibfk_1` FOREIGN KEY
(`client_id`) REFERENCES `clients` (`id`) ON UPDATE CASCADE"};
char multi_statement[10000];
mysql_real_connect(mysql, "localhost", "root", "", "test", 0, NULL,
CLIENT_MULTI_STATEMENTS);
printf("Client: %s\nServer: %s\n", mysql_get_client_info(),
mysql_get_server_info(mysql));
mysql_query(mysql, "SET AUTOCOMMIT=0");
/* process single statements */
for (i=0; i < 6; i++) {
if (mysql_query(mysql, sql[i]))
printf("Single statement error: %s\n", mysql_error(mysql));
}
/* process multi statement */
sprintf((char *)&multi_statement, "%s;%s;%s;%s;%s;%s", sql[0], sql[1], sql[2], sql[3],
sql[4], sql[5]);
status = mysql_query(mysql, multi_statement);
i= 0;
do {
if ((res = mysql_store_result(mysql))) {
mysql_free_result(res);
}
status = mysql_next_result(mysql);
i++;
} while (status == 0);
if (mysql_error(mysql))
printf("multi statement error: %s\n", mysql_error(mysql));
}
Output:
georg@bing:~/work/php/test> ./test
Client: 5.1.30
Server: 5.1.30-debug-log
multi statement error: Cannot delete or update a parent row: a foreign key constraint
fails
Description: Multi statement execution fails in 5.1 How to repeat: #include <stdio.h> #include <mysql.h> int main () { MYSQL *mysql = mysql_init(NULL); MYSQL_RES *res; int i, status; char *sql[] = { "SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO", "DROP TABLE IF EXISTS `bank_transactions`", "CREATE TABLE `bank_transactions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `client_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `client` (`client_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC", "DROP TABLE IF EXISTS `clients`", "CREATE TABLE `clients` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC", "ALTER TABLE `bank_transactions` ADD CONSTRAINT `bank_transactions_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON UPDATE CASCADE"}; char multi_statement[10000]; mysql_real_connect(mysql, "localhost", "root", "", "test", 0, NULL, CLIENT_MULTI_STATEMENTS); printf("Client: %s\nServer: %s\n", mysql_get_client_info(), mysql_get_server_info(mysql)); mysql_query(mysql, "SET AUTOCOMMIT=0"); /* process single statements */ for (i=0; i < 6; i++) { if (mysql_query(mysql, sql[i])) printf("Single statement error: %s\n", mysql_error(mysql)); } /* process multi statement */ sprintf((char *)&multi_statement, "%s;%s;%s;%s;%s;%s", sql[0], sql[1], sql[2], sql[3], sql[4], sql[5]); status = mysql_query(mysql, multi_statement); i= 0; do { if ((res = mysql_store_result(mysql))) { mysql_free_result(res); } status = mysql_next_result(mysql); i++; } while (status == 0); if (mysql_error(mysql)) printf("multi statement error: %s\n", mysql_error(mysql)); } Output: georg@bing:~/work/php/test> ./test Client: 5.1.30 Server: 5.1.30-debug-log multi statement error: Cannot delete or update a parent row: a foreign key constraint fails