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

[Nicolas Morey-C. <de...@mo...>]

Markus Hoenicka a écrit :
> 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. 

The thing is for INSERT or UPDATE queries, there is actually no results
returned. It just a bunch of NULL pointers, so my workaround doesn't
lose any data.
The only problem is, I agree, with multiple SELECT.
With Postgres, i'm running multiple INSERT/UPDATE without any problem. I
haven't tried to run multiple SELECT but i guess there are some prioblem
too as the libdbi API seems not to be fitted for multiple SELECT.

In my opinion, handling multiple query for query without any result send
back is acceptable as long as it is well documented.
> Writing your app in a
> way that prevents multiple statement queries will provide the same
> results without unwanted side effects. 
The problem is my app is a performance plugin and gets about 10000
updates to do in the database, and on Postgres it's much faster when
using multiple queries.

> 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.
>
>   
I agree, but as I said supporting only multiple queries without "real"
result (only NULL pointers) seems to me like an acceptable behaviour.
> 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));
>  > 
>  > 
>
>