Mysql Query Cache not working

abredon · **Joined:** Fri Jun 23, 2006 6:33 am **Posts:** 1

Hi All,
We have a mysql instance running with query caching turned on. When we run for example a select statement from the mysql console the query gets cached, however none of our hibernate queries get cached.

I have turned hibernate.show_sql on and taken the output from hibernate, run that from the mysql console and that get's cached. So can someone please explain why it won't cache my queries through my application? I don't want to use the hibernate query cache (ehcache etc), I want to use the native mysql query cache. Please help.

Thanks,
Andrew

mm-mysql · **Joined:** Thu Feb 23, 2006 12:12 am **Posts:** 3

abredon wrote:

Hi All,
We have a mysql instance running with query caching turned on. When we run for example a select statement from the mysql console the query gets cached, however none of our hibernate queries get cached.

I have turned hibernate.show_sql on and taken the output from hibernate, run that from the mysql console and that get's cached. So can someone please explain why it won't cache my queries through my application? I don't want to use the hibernate query cache (ehcache etc), I want to use the native mysql query cache. Please help.

Thanks,
Andrew

Hi Andrew,

Hibernate uses prepared statements. If you're using server-side prepared statements with MySQL (the default for MySQL > 4.1), these don't use the query cache. You can disable server-side prepared statements usage by the JDBC driver by including the JDBC configuration property "useServerPrepStmts=false" in your datasource declaration or URL.

-Mark

mrtz · **Joined:** Mon Aug 01, 2005 3:10 pm **Posts:** 22

A related question: If I use the mysql command "show global status" and observe the "Com_stmt_prepare", "Com_stmt_execute" and "Com_stmt_close" counts, they all keep going up equally even if I run the same hibernate queries many times via my application. Why does the "Com_stmt_prepare" count not remain constant?

My connection pool setting in JBoss looks like this:

Code:

   <local-tx-datasource>
      <jndi-name>jdbc/nbqa_ds</jndi-name>
      <connection-url>jdbc:mysql://localhost/nbqa</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>

      <connection-property name="cachePrepStmts">true</connection-property>
      <connection-property name="prepStmtCacheSize">100</connection-property>
      <connection-property name="useServerPrepStmts">true</connection-property>
      <share-prepared-statements>true</share-prepared-statements>      

      <user-name>abcd</user-name>
      <password>abcd</password>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
      <min-pool-size>5</min-pool-size>
      <max-pool-size>200</max-pool-size>
      <idle-timeout-minutes>10</idle-timeout-minutes>
      <track-statements>false</track-statements>
      <!--<prepared-statement-cache-size>100</prepared-statement-cache-size>-->
      <application-managed-security/>
      <metadata>
         <type-mapping>mySQL</type-mapping>
      </metadata>
   </local-tx-datasource> 

If I set useServerPrepStmts=false, then I don't see any count going up at all.

Is is correct to assume that if the prepared statements are working as expected, then the "Com_stmt_execute" should keep increasing and the "Com_stmt_prepare" count should remain mostly constant when running the same hibernate queries over and over again?

narenderreddy · **Joined:** Tue Dec 12, 2006 7:52 am **Posts:** 20

Hi
You open this below link and read it
it helpful to you
http://hibernate.org/213.html

glynnp · **Posted:** Mon Apr 30, 2007 8:21 am

mm-mysql wrote:

abredon wrote:

Hi All,
We have a mysql instance running with query caching turned on. When we run for example a select statement from the mysql console the query gets cached, however none of our hibernate queries get cached.

I have turned hibernate.show_sql on and taken the output from hibernate, run that from the mysql console and that get's cached. So can someone please explain why it won't cache my queries through my application? I don't want to use the hibernate query cache (ehcache etc), I want to use the native mysql query cache. Please help.

Thanks,
Andrew

Hi Andrew,

Hibernate uses prepared statements. If you're using server-side prepared statements with MySQL (the default for MySQL > 4.1), these don't use the query cache. You can disable server-side prepared statements usage by the JDBC driver by including the JDBC configuration property "useServerPrepStmts=false" in your datasource declaration or URL.

-Mark

Hi... We had this problem and worked around it in exactly the same way... Unfortunately, now we are no longer using server side prepared statements we are logging lots of Data truncation errors which go away if we switch back to prepared statements.

I dug up plenty of information regarding floating point values, jdbc and data truncation errors... but we are experiencing our problems on text columns, with no apparent cause.

Im under the impression that the values would be truncated but inserted without errors..

Has anyone else experienced this...have any ideas?

thanks

gp

rackoon · **Joined:** Thu Jun 07, 2007 5:19 am **Posts:** 1

glynnp wrote:

Hi... We had this problem and worked around it in exactly the same way... Unfortunately, now we are no longer using server side prepared statements we are logging lots of Data truncation errors which go away if we switch back to prepared statements.

I dug up plenty of information regarding floating point values, jdbc and data truncation errors... but we are experiencing our problems on text columns, with no apparent cause.

Im under the impression that the values would be truncated but inserted without errors..

Has anyone else experienced this...have any ideas?

We also got data truncation errors (on MySQL 5). It turned out that the error messages were misleading: It was an encoding problem. Without prepared statements text columns where not converted correctly from UTF-8 to Latin-1 when inserting into the DB. After changing the columns' collation to UTF-8 everything worked.

-Caspar