MySQL Bugs: #2319: Library mysqlclient

Bug #2319	Library mysqlclient
Submitted:	8 Jan 2004 7:00	Modified:	29 Mar 2004 13:38
Reporter:	Dominique DAMBRAIN	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1 & 5.0	OS:	Linux (Linux kernel 2.4.21)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Hi,

I'm trying to develop an interface in C language, using mysql libraries

My modules generates 'on the fly' the needed SQL statements, and uses :
   - mysql_prepare(  )
   - mysql_bind_result ( ) to associate buffers to resulting select list
   - mysql_bind_param (   ) to bind the WHERE clause criterias ( each ? )
   - mysql_execute(  )
   - mysql_stmt_store_result(  )
   - loop on mysql_fetch(  ) to transfer each row into buffer

Second time the same kind of statement must be submitted, I don't execute prepare & bind_result ( statement and structures have been kept )
I only use bind_param, execute, stmt_store_result & fetch

This technic works perfectly if my statement only contains 1 parameter in WHERE clause : I can reuse a lot of times the same prepared statement.
But , if I have many parameters in my WHERE clause ( many '?' in the statement), the execute of second sequence generates an error 2013 ( crash of server instance )
On Windows, the process mysqld-nt.exe disappears, without error.

At this moment , I'm forced to re-execute the full 1st sequence ( prepare, bin_result, bind_param, execute ) even I have an already prepared statement.
Consequence : 50% performances less !

The problem is the same with versions 4.1.1 or 5.0
I cannot test with versions less than 4.1, because those statements aren't available.

How to repeat:
On demand, I can develop a small example, extracted from my solution ( too complex to be provided totally in source !)

1- prepare a statement using 2 parameters in WHERE clause
2- bind_result for select list
3_ bind_param for the 2 criterias values
4- execute ( it's OK )
    eventually fetch

5- bind_param for 2 new criterias values ( same statement)
6- execute ( crash : 2013 error )

A small code sample which demonstrates the problem would be most helpful to ensure that we test your exact situation.  Attach it here via the Files tab.

Thank you, Dean, for so prompt reaction.

I'll start NOW development of a small sample program reproducing problem.
I'll notify you soon

Script to compile sample program

Attachment: compile (text/plain), 61 bytes.

Test program generating crash

Attachment: test.c (text/plain), 6.12 KiB.

Hi, Dean

I've attached two files to the debug report :
    compile is the script used to compile the program
    test.c is the test program itself

Note : You'll have to adapt (manually) one line in the source :
    Database on which to connect
    user name
    password

The program will create a 'test_table' , with 3 columns
It'll insert a row into it.
It'll prompt you to obtain number of criterias to use :
    answer 1 or 2
it'll execute test sequence

1- With 1 criteria : all is OK ( SELECT statement is reused )
2- With 2 citerias, that fails ( idem, but error 2013 under Linux, or crash server under Windows)

Verified with 4.1.2.

The problem occurs when re-executing a prepared SELECT statement.

I tested a seperate program with pstest (a int, b int, c int) and the statement: "SELECT a FROM pstest WHERE b = ?".  I can re-execute this without issue, but if anything at all is added to the WHERE clause re-execution crashes mysqld.  Examples:

"SELECT a FROM pstest WHERE 1 = 1 AND b = ?"
"SELECT a FROM pstest WHERE b = ? AND 1 = 1"
"SELECT a FROM pstest WHERE b = ? AND c = 1"
"SELECT a FROM pstest WHERE b = ? AND c = ?"

Attaching some stack traces.

Hi, Dmitri & Dean

If I've correctly interpreted last events about this bug report, Dean has prooved that problem occurs, also with version 4.1.2

What can I do, now, about this problem ?

Can I help you more, or only wait a new notification from you ?

Kindly yours,

Hi, Dominique!

I can't repeat this behavior with latest 4.1 tree (but it is easily repeatable for 4.1.1). Since there were fixes in code related to prepared statements and exactly related to consequnces of constant propagation in prepared statements recently I believe that you bug is fixed.

Please look if you can repeat it with latest bk version or with 4.1.2 when it comes out (look into http://www.mysql.com/doc/en/Installing_source.html for more info about building MySQL from public development source tree)

Thank you for your interest in MySQL!

Hi, Dmitri
As suggested, we've tested version 4.1.2 on Windows & Linux
The complex WHERE clause seems to work fine, now : Bug fixed !

But we still have problem ( not fully identified ) with prepared statements reused many times : After some use, we receive error 2014.

To be continued.....

Thanks for help.