MySQL 4.x.x + Query.setMaxResults(n) = Error

matan_a · **Joined:** Thu Jun 03, 2004 2:10 am **Posts:** 19

Hibernate version: 2.1.7c

Name and version of the database you are using: MySQL 4.1.7

When the setMaxResults is set for a Query on the MySQL database, it causes an SQL parsing exception. This is because Hibernate tries to add the " limit ?" string to the SQL statement. This used to work with old versions of MySQL, but the newer versions - which use server side prepared statements, don't accept limit with parameters. What this means it that we can't use this feature by default :(

I was wondering if Hibernate could be updated not to try to add the "limit ?" text. Instead, it could add the text with the in-line number: "limit 10".

A fallback could be just to use the PreparedStatement.setMaxRows method - that works as well.

The only work around i found without changing the Hibernate code is to disable server-side prepared statements for MySQL through the JDBC driver parameter (but who want's to do that?).

Anyone have a solution for this?

christian · **Posted:** Tue Dec 07, 2004 4:26 pm

Complain to MySQL.

mileshq · **Posted:** Tue Dec 07, 2004 8:06 pm

I had a similar problem with Informix and LIMIT syntax ("first N" in Informix). I fixed it by adjusting the InformixDialect. All the dialects are in package:

Code:

net.sf.hibernate.dialect

See this post for an idea of changes made:
http://forum.hibernate.org/viewtopic.php?t=936331&highlight=

matan_a · **Joined:** Thu Jun 03, 2004 2:10 am **Posts:** 19

Thanks mileshq,

I did change the MySQL dialect to hard code the limit parameter instead of adding it to the prepared statement, but i was wondering if the Hibernate team was interested in adjusting their dialects to be compatible with the changes in the DBs....

tcollins · **Posted:** Thu Jan 06, 2005 2:45 pm

Could you post your code for the MySQLDialect fix? How did you get the limit values?

Code:

public String getLimitString(String querySelect, boolean hasOffset, int limit)

This will pass you the max limit, but what about the current page. So you can have a limit like this.... limit 21, 40

tcollins · **Posted:** Thu Jan 06, 2005 3:10 pm

I found another hack/workaround/solution to this mySQL LIMIT problem.

Check out this link, this is a known MySQL bug. http://bugs.mysql.com/bug.php?id=4633

Add the following property to you JDBC Connection URL
useServerPrepStmts=false

So your connection string should look something like.....
jdbc:mysql://localhost:3306/your_database?useServerPrepStmts=false

Jonathan Feinberg · **Joined:** Sun Feb 13, 2005 5:00 pm **Posts:** 5

matan_a wrote:

Name and version of the database you are using: MySQL 4.1.7

When the setMaxResults is set for a Query on the MySQL database, it causes an SQL parsing exception. This is because Hibernate tries to add the " limit ?" string to the SQL statement.

In case anyone is still watching this thread, I'm happy to report that the recent nightly builds of the mysql-connector-3.1.x drivers fix this bug. You no longer need to prevent the use of prepared statements.

See http://downloads.mysql.com/snapshots.php at the 3.1 Development header.

bigdaddydsp · **Joined:** Mon Feb 28, 2005 3:22 pm **Posts:** 1

The problem appears in MySQL jdbc driver 3.1.6 GA but is fixed in 3.1.7 GA.

lukasbradley · **Posted:** Tue Mar 15, 2005 6:43 pm

I do not believe this has been fixed, and I don't necessarily believe the problem is with MySQL.

I'm using Hibernate 3.0. The following query runs within the Hibernate Tools Eclipse plugin against MySQL 4.1.8 using MySQL Connector 3.1.7.

I'm interested to see what others get from the following queries. The first works, while the second does not.

Code:

from MyTable as mine order by mine.id limit 3 ;

from MyTable as mine limit 3 ;

If the limit clause is "buffered" by the order by clause, the limit seems to work. I have tried this with both the "?useServerPrepStmts=false" condition and without it. Both produce the same results.

checksum · **Joined:** Wed Jun 29, 2005 1:07 pm **Posts:** 1

tcollins wrote:

I found another hack/workaround/solution to this mySQL LIMIT problem.

Check out this link, this is a known MySQL bug. http://bugs.mysql.com/bug.php?id=4633

Add the following property to you JDBC Connection URL
useServerPrepStmts=false

So your connection string should look something like.....
jdbc:mysql://localhost:3306/your_database?useServerPrepStmts=false

With hibernate 3.0.5 and mysql-connector-java-3.1.10 i still needed the above solution to get my problem fixed when using setMaxResults()

prophecy · **Posted:** Thu Oct 06, 2005 3:07 pm

Has this been fixed in hibernate or the mysql driver ? I just want some clarification, there seem to be some conflicting results in this thread.

Thanks.

Alt3 · **Joined:** Tue Feb 17, 2004 11:34 am **Posts:** 8

bigdaddydsp wrote:

The problem appears in MySQL jdbc driver 3.1.6 GA but is fixed in 3.1.7 GA.

I use mysql connector 3.2 alpha but problem not fixed. Is it problem of MySQL ?

Alt3 · **Joined:** Tue Feb 17, 2004 11:34 am **Posts:** 8

Alt3 wrote:

bigdaddydsp wrote:

The problem appears in MySQL jdbc driver 3.1.6 GA but is fixed in 3.1.7 GA.

I use mysql connector 3.2 alpha but problem not fixed. Is it problem of MySQL ?

It is problem of mysql but for why reasons their 3.2 drivers again contains it bug.
If I use 3.1 drivers I stand with bugs with blobs if use 3.2 then bug with limit.. I think I will switch to 3.0 or other DB .

trevor · **Posted:** Thu May 04, 2006 12:11 am

A big thank you to everyone who posted the solution in this forum!

As of this date, popular Java hosting service Kattare.com is still using Mysql-Connector-java 3.1.6, so anyone moving their project to their services as I did has to either use the "useServerPrepStmts=false" solution or avoid using setMaxResults().

- Trevor