Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 36432
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: CAPTTOFU [...] cpan.org
Requestors: merijn [...] web2all.nl
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: mysql_multi_statements causing DBD::mysql::db do failed: Commands out of sync
Date: Wed, 4 Jun 2008 12:41:42 +0200
To: <bug-DBD-mysql [...] rt.cpan.org>
From: "Merijn van den Kroonenberg" <merijn [...] web2all.nl>
Hi, I am running into a problem when using the mysql_multi_statements=1 setting. It happens when I do: $dbh->do($dbstructure); # the $dbstructure is a string with table create statements. # and then $dbh->do($somequery); # where $somequery is a string with a query (eg. SELECT NOW();) This second query will result in the following warning: DBD::mysql::db do failed: Commands out of sync; you can't run this command now I can work around this by doing a (dummy) prepared statement between the two do's my $sth = $dbh->prepare('SELECT NOW();'); $sth->execute(); $sth->finish(); Just to be sure, I also tried with the mysql_server_prepare=0 option and with sleeps between all statements, but this made no difference. DBI::mysql version: 4.005 perl, v5.8.8 built for i486-linux If this is not clear, don't hesitate to contact me. Greetings, Merijn van den Kroonenberg Web2All B.V. Telefoon: +31 475 775511 Fax: +31 475 338290 Email: merijn@web2all.nl Website: http://www.web2all.nl Country: Netherlands
From: Chris Heath <chris [...] heathens.co.nz>
Download (untitled) / with headers
text/plain 389b
I ran into the same problem. I found that you could work around the problem by putting an unused prepare() before and after the do(): $dbh->prepare("SELECT 1"); $dbh->do($my_multiple_queries); $dbh->prepare("SELECT 1"); But I also attached a patch that fixes this and other mysql_multi_statements problems. Patch is against 4.007. Only tested on one machine, so use at your own peril!
Download DBD-mysql.patch
text/x-diff 5.8k
--- dbdimp.c.orig 2008-04-28 19:36:17.000000000 -0400 +++ dbdimp.c 2008-07-24 00:17:33.000000000 -0400 @@ -2668,7 +2668,7 @@ /* No more pending result set(s)*/ if (DBIc_TRACE_LEVEL(imp_xxh) >= 2) PerlIO_printf(DBILOGFP, - "\n <- dbs_st_more_rows no more results\n"); + "\n <- dbs_st_more_results no more results\n"); return 0; } @@ -2699,6 +2699,8 @@ next_result_return_code= mysql_next_result(svsock); + imp_sth->warning_count = mysql_warning_count(imp_dbh->pmysql); + /* mysql_next_result returns 0 if there are more results @@ -2721,10 +2723,13 @@ do_error(sth, mysql_errno(svsock), mysql_error(svsock), mysql_sqlstate(svsock)); + imp_sth->row_num= mysql_affected_rows(imp_dbh->pmysql); + if (imp_sth->result == NULL) { /* No "real" rowset*/ - return 0; + imp_sth->insertid= mysql_insert_id(imp_dbh->pmysql); + return 1; } else { @@ -3137,7 +3142,13 @@ if (imp_sth->row_num+1 != (my_ulonglong)-1) { if (!imp_sth->result) + { imp_sth->insertid= mysql_insert_id(imp_dbh->pmysql); +#if MYSQL_VERSION_ID >= MULTIPLE_RESULT_SET_VERSION + if (mysql_more_results(imp_dbh->pmysql)) + DBIc_ACTIVE_on(imp_sth); +#endif + } else { /** Store the result in the current statement handle */ @@ -3674,7 +3685,7 @@ We don't close the cursor till DESTROY. The application may re execute it. */ - if (imp_sth && imp_sth->result) + if (imp_sth && DBIc_ACTIVE(imp_sth)) { /* Clean-up previous result set(s) for sth to prevent --- mysql.xs.orig 2008-07-24 00:44:01.000000000 -0400 +++ mysql.xs 2008-07-24 02:17:53.000000000 -0400 @@ -234,6 +234,9 @@ int retval; struct imp_sth_ph_st* params= NULL; MYSQL_RES* result= NULL; +#if MYSQL_VERSION_ID >= MULTIPLE_RESULT_SET_VERSION + int next_result_rc; +#endif #if MYSQL_VERSION_ID >= SERVER_PREPARE_VERSION STRLEN slen; char *str_ptr, *statement_ptr, *buffer; @@ -247,7 +250,16 @@ MYSQL_STMT *stmt= NULL; MYSQL_BIND *bind= NULL; imp_sth_phb_t *fbind= NULL; - +#endif +#if MYSQL_VERSION_ID >= MULTIPLE_RESULT_SET_VERSION + while (mysql_next_result(imp_dbh->pmysql)==0) + { + MYSQL_RES* r = mysql_use_result(imp_dbh->pmysql); + if (r) + mysql_free_result(r); + } +#endif +#if MYSQL_VERSION_ID >= SERVER_PREPARE_VERSION /* * Globaly enabled using of server side prepared statement * for dbh->do() statements. It is possible to force driver @@ -492,6 +504,28 @@ mysql_free_result(result); result= 0; } +#if MYSQL_VERSION_ID >= MULTIPLE_RESULT_SET_VERSION + if (retval != -2) /* -2 means error */ + { + /* more results? -1 = no, >0 = error, 0 = yes (keep looping) */ + while ((next_result_rc= mysql_next_result(imp_dbh->pmysql)) == 0) + { + result = mysql_use_result(imp_dbh->pmysql); + if (result) + mysql_free_result(result); + } + if (next_result_rc > 0) + { + if (dbis->debug >= 2) + PerlIO_printf(DBILOGFP, "\t<- do() ERROR: %s\n", + mysql_error(imp_dbh->pmysql)); + + do_error(dbh, mysql_errno(imp_dbh->pmysql), + mysql_error(imp_dbh->pmysql), mysql_sqlstate(imp_dbh->pmysql)); + retval= -2; + } + } +#endif /* remember that dbd_st_execute must return <= -2 for error */ if (retval == 0) /* ok with no rows affected */ XST_mPV(0, "0E0"); /* (true but zero) */ --- t/76multi_statement.t.orig 2008-05-11 10:56:13.000000000 -0400 +++ t/76multi_statement.t 2008-07-24 02:10:11.000000000 -0400 @@ -19,21 +19,55 @@ if ($@) { plan skip_all => "ERROR: $@. Can't continue test"; } -plan tests => 7; +plan tests => 27; ok (defined $dbh, "Connected to database with multi statement support"); $dbh->{mysql_server_prepare}= 0; SKIP: { - skip "Server doesn't support multi statements", 6 + skip "Server doesn't support multi statements", 26 if $dbh->get_info($GetInfoType{SQL_DBMS_VER}) lt "4.1"; ok($dbh->do("DROP TABLE IF EXISTS $table"), "clean up"); ok($dbh->do("CREATE TABLE $table (a INT)"), "create table"); - ok($dbh->do("INSERT INTO $table VALUES (1); INSERT INTO $table VALUES (2);")); + ok($dbh->do("INSERT INTO $table VALUES (1); INSERT INTO $table VALUES (2);"), "2 inserts"); + + # Check that a second do() doesn't fail with an 'Out of sync' error + ok($dbh->do("INSERT INTO $table VALUES (3); INSERT INTO $table VALUES (4);"), "2 more inserts"); + + # Check that more_results works for non-SELECT results too + my $sth; + ok($sth = $dbh->prepare("UPDATE $table SET a=5 WHERE a=1; UPDATE $table SET a='6-' WHERE a<4")); + ok($sth->execute(), "Execute updates"); + is($sth->rows, 1, "First update affected 1 row"); + is($sth->{mysql_warning_count}, 0, "First update had no warnings"); + ok($sth->{Active}, "Statement handle is Active"); + ok($sth->more_results()); + is($sth->rows, 2, "Second update affected 2 rows"); + is($sth->{mysql_warning_count}, 2, "Second update had 2 warnings"); + ok(not $sth->more_results()); + ok($sth->finish()); + + # Now run it again without calling more_results(). + ok($sth->execute(), "Execute updates again"); + ok($sth->finish()); + + # Check that do() doesn't fail with an 'Out of sync' error + is($dbh->do("DELETE FROM $table"), 4, "Delete all rows"); + + # Test that do() reports errors from all result sets + $dbh->{RaiseError} = $dbh->{PrintError} = 0; + ok(!$dbh->do("INSERT INTO $table VALUES (1); INSERT INTO bad_$table VALUES (2);"), "do() reports errors"); + + # Test that execute() reports errors from only the first result set + ok($sth = $dbh->prepare("UPDATE $table SET a=2; UPDATE bad_$table SET a=3")); + ok($sth->execute(), "Execute updates"); + ok(!$sth->err(), "Err was not set after execute"); + ok(!$sth->more_results()); + ok($sth->err(), "Err was set after more_results"); $dbh->disconnect();
Thank you very much for the patch! After testing, I will add it in for an upcoming 4.008 release.
I've added this to the impending 4.008 code - and given credit! Thank you!


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.