Bug #5791 Server side prepared statements not working

• Submitted: 28 Sep 2004 17:30
• Modified: 31 Mar 2014 7:48
• Reporter: [ name withheld ]
• Status: Can't repeat
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 4.1.3
• OS: Solaris (Solaris 5.8)
• Assigned to: Alexander Soklakov
• CPU Architecture: Any

[28 Sep 2004 17:30] [ name withheld ]

Description:
I'm using the Connector/J API and server-side prepared statements to access MySQL. It seems that on occasion, the server drops conditions in the original SQL sent to the server.

We print out the query the PreparedStatement thinks it is executing, and it's correct. However, when I call PreparedStatement.executeQuery() from within the application, I get the wrong number of rows returned. In fact, the number of rows returned is the same if one of the conditions in the where clause in the original SQL is dropped. If I use the same
PreparedStatement within a loop that calls executeQuery() 55 times
(after binding variables each time), 3 of the 55 calls return the
incorrect number of rows; the rest return the correct number. So it's an
intermittant, but definitely reproducible bug.

The PreparedStatement has 2 parameters in the where clause, both of which are ints.

How to repeat:
It may be hard to reproduce this without our schema and associated data. Unfortunately, we cannot release this proprietary data. 

Suggested fix:
Maybe there is a buffer on the server where the SQL is cached that is getting stepped on somehow.

[18 Oct 2004 20:08] Mark Matthews

Please check this bug with version 4.1.6 of the server, it appears some fixes to prepared statements and constant propagation with the optimizer have been fixed.

[20 Oct 2004 15:46] [ name withheld ]

I saw that with 4.1.6 a semaphore issue had been addressed. we'll try an upgrade.

thanks very much

jeff mathis

[21 Dec 2004 0:31] Mark Matthews

Is this fixed, then?

[21 Dec 2004 16:31] [ name withheld ]

We still have not been able to upgrade our mysql server version  :(

InnoDB 4.1.8 has just come out, which is apparently the most stable release in the 4.1 series. Heikki thinks we should watch the boards for a couple weeks to make sure nothing unexpected crops up, and then do the upgrade, which will then allow us to test this fix.

Another issue is that we are 4.1.3. In order to upgrade to 4.1.8, we will have to dump and reload the entire database because of a timestamp storage error in 4.1.3. This is obviously a bummer, but we'll get over it.

So, thanks for the continued follow up. I would hope that by the end of January we'll be up to date.

Jeff

[14 Feb 2005 22:54] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[14 Feb 2005 22:59] [ name withheld ]

We still have not had a chance to test this. We are waiting for Heikki to say the latest InnoDB release, 4.1.9 I believe, is stable. But, in case i did not mention this in previous communications, we all believe this is an InnoDB issue, and not a Connector/J problem. The server was definitely mangling cached statements.

When we can check this, we will. Thanks for following up.

Jeff

[25 Apr 2005 21:09] [ name withheld ]

We have just completed a migration to the 4.1.11 InnoDB release. I'm now checking on this. Report back soon.

Jeff

[31 Mar 2014 7:48] Alexander Soklakov

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.