I am using JPA\Hibernate with MySql. I am executing a native query with SQL_CALC_FOUND_ROWS and a limit along with a "SELECT FOUND_ROWS()" query.

There are certain times where the found_rows query is returning the wrong result. I looked in the MySql query logs and saw that a query "SHOW CHARACTER SET" is being executed between the SQL_CALC_FOUND_ROWS query and the FOUND_ROWS query which messes up the FOUND_ROWS result.

Is the "SHOW CHARACTER SET" query being run by hibernate? If so, how can I prevent this?

Regards

I know it's probably too late, anyway I got the same problem and looking into mysql-connector source code
i found you can set the following flag in the jdbc connection string :
useDynamicCharsetInfo=false

This will prevent the execution of SHOW CHARACTER SET query.

Bye,
D.

Looks like the problem affects only MySQL 5.x.
Here is the bug listing on MySQL tracking site (2007, mind you):

http://bugs.mysql.com/bug.php?id=25978

Anyway, your workaround is still valid from 5.0.7 on.

Bye,
M.