| Bug #40877 | multi statement execution fails in 5.1.30 | ||
|---|---|---|---|
| Submitted: | 20 Nov 2008 10:35 | Modified: | 11 Nov 18:03 |
| Reporter: | Georg Richter | ||
| Status: | Need Doc Info | ||
| Category: | Server | Severity: | S1 (Critical) |
| Version: | 5.0/5.1/6.0 | OS: | Linux (x64) |
| Assigned to: | Georgi Kodinov | Target Version: | 5.1+ |
| Triage: | Triaged: D2 (Serious) | ||
[20 Nov 2008 12:46]
Miguel Solorzano
Thank you for the bug report. Verified on 6.0/5.0 too. C:\temp>bug40877 Client: 5.1.31 Server: 5.1.31-nt-debug-log multi statement error: Cannot delete or update a parent row: a foreign key constraint fails C:\temp>bug60.exe Client: 6.0.9-alpha Server: 6.0.9-alpha-nt-debug-log multi statement error: Cannot delete or update a parent row: a foreign key constraint fails C:\temp>bug50.exe Client: 5.0.74 Server: 5.0.74-nt-log multi statement error:
[13 Oct 17:15]
Georgi Kodinov
There's problems on two levels that prevent multi statements working as they should with this example : 1. The Innodb storage engine assumes the string returned by thd_query() (THD::query actually) is null-terminated. This is true with single statements. But with multi statements it's not true. Innodb should be extended to use char pointer/length instead of null-terminated strings. But it (currently) has no way getting this information out of the server. Which brings us to problem #2 2. The MySQL server has a THD::query_length that contains the length of THD::query. We need to expose this to the Innodb storage engine. This is best done by a new function that will return a LEX_STRING instead of char *. And while at it we may just as well wrap the string pointer and the length into a LEX_STRING internally and provide accessor methods for easy code update. I will use this bug report to track the solution of problem #2. #2 is a prerequisite for the real fix (#1). I will open a new bug for problem #1.
[13 Oct 17:36]
Georgi Kodinov
Problem #2 reported as bug #48024.
[13 Oct 18:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/86705 3156 Georgi Kodinov 2009-10-13 Bug #40877: multi statement execution fails in 5.1.30 Implemented the server infrastructure for the fix: 1. Added a function LEX_STRING *thd_query_string(THD) to return a LEX_STRING structure instead of char *. This is the function that must be called in innodb instead of thd_query() 2. Did some encapsulation in THD : aggregated thd_query and thd_query_length into a LEX_STRING and made accessor and mutator methods for easy code updating. 3. Updated the server code to use the new methods where applicable.
[16 Oct 12:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/87103 3177 Georgi Kodinov 2009-10-16 Bug #40877: multi statement execution fails in 5.1.30 Implemented the server infrastructure for the fix: 1. Added a function LEX_STRING *thd_query_string(THD) to return a LEX_STRING structure instead of char *. This is the function that must be called in innodb instead of thd_query() 2. Did some encapsulation in THD : aggregated thd_query and thd_query_length into a LEX_STRING and made accessor and mutator methods for easy code updating. 3. Updated the server code to use the new methods where applicable.
[16 Oct 15:31]
Georgi Kodinov
Pushed in 5.1-bugteam and merged to mysql-pe
[22 Oct 8:36]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 18:03]
Paul DuBois
Noted in 6.0.14 changelog. Multiple-statement execution could fail. Setting report to NDI pending push into 5.1.x, 5.5.x.
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