[libdbi-devel] [patch] Fixed bug in mysql driver when using multiple statement queries

[Markus H. <mar...@mh...>]

Hi,

thanks for bringing this to our attention. While your patch may cure
the symptoms, I don't think it's the proper way to handle this
problem. After all, your approach simply tosses all results of
multiple statement queries except the first one. Writing your app in a
way that prevents multiple statement queries will provide the same
results without unwanted side effects. I think it is more honest to
admit that libdbi does not yet support multiple statement queries at
this time and add this to the documentation.

Thanks anyway for looking into this and for suggesting a patch.

regards,
Markus

Nicolas Morey-Chaisemartin writes:
 > (sent to the libdbi-dev mailing list but forgot to put you in copy)
 > 
 > Hello everyone,
 > 
 > I am using libdbi for one of my application. It was working perfectly
 > fine with Postgre but I had some troubles with MySQL.
 > After looking a bit around, i found out I had to install the last libdbi
 > drivers to be able to use multiple statements with MySQL.
 > I checked the sources and libdbi-drivers-0.8.3-1 takes the mysql multi
 > statement option and pass it to MySQL when connecting.
 > 
 > The problem is when you are doing a multi statement query, mysql is
 > generating multiple results. Even if you do a couple of INSERT.
 > As long as all these results have not been read, MySQL will return a
 > 2014 (out of sync) error to any query.
 > 
 > I've quickly patch the driver so it'll eat all the results and return
 > the first one.
 > It will manage the case where statements are INSERT or UPDATES but some
 > data will be lost when several SELECT are done in a single query.
 > 
 > I've tested it and it solves my problem.
 > I guess a more generic solution should be developped but I really don't
 > have time for this right now.
 > 
 > Regards
 > 
 > Nicolas Morey-Chaisemartin
 > 
 > 
 > *** drivers/mysql/dbd_mysql.c.orig	2008-01-15 01:27:29.000000000 +0100
 > --- drivers/mysql/dbd_mysql.c	2008-12-29 18:04:26.000000000 +0100
 > ***************
 > *** 506,511 ****
 > --- 506,513 ----
 >   	
 >   	res = mysql_store_result((MYSQL *)conn->connection);
 >   	
 > + 	while(mysql_next_result((MYSQL *)conn->connection) == 0)
 > + 		mysql_free_result(mysql_store_result((MYSQL *)conn->connection));
 >   	/* if res is null, the query was something that doesn't return rows (like an INSERT) */
 >   	result = _dbd_result_create(conn, (void *)res, (res ? mysql_num_rows(res) : 0), 
 >   								mysql_affected_rows((MYSQL *)conn->connection));
 > ***************
 > *** 528,533 ****
 > --- 530,537 ----
 >   	
 >   	res = mysql_store_result((MYSQL *)conn->connection);
 >   	
 > + 	while(mysql_next_result((MYSQL *)conn->connection) == 0)
 > + 		mysql_free_result(mysql_store_result((MYSQL *)conn->connection));
 >   	/* if res is null, the query was something that doesn't return rows (like an INSERT) */
 >   	result = _dbd_result_create(conn, (void *)res, (res ? mysql_num_rows(res) : 0), 
 >   								mysql_affected_rows((MYSQL *)conn->connection));
 > 
 > 

-- 
Markus Hoenicka
mar...@ca...
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de