Why is 'SHOW WARNINGS' query issued here? (JPA/Hibernate/MySQL)

Question

We are refactoring the persistence layer of a Java application from JDBC Template to JPA/Hibernate.

I am profiling the SQL statements being issued to the database and I see "SHOW WARNINGS" is issued many, many times. According to JProfiler the "SHOW WARNINGS" is accounting for a considerable amount of 'inherent time'.

What could cause SHOW WARNINGS to be issued so frequently?

This SHOW WARNINGS was NOT previously issued when using Jdbc Template.

Below is the part of our stack relevant to persistence. The only change here is the introduction of JPA/Hibernate.

• JPA / Hibernate: 4.3.6
• MySQL driver: 5.1.33
• MySQL database: 5.6.20
• JDBC Connection Pool: HikariCP-2.3.2

EDIT: Here's a sample stack trace of when SHOW WARNINGS is issued.

com.mysql.jdbc.StatementImpl.getWarnings()
com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.getWarnings()
org.hibernate.jpa.internal.QueryImpl.getSingleResult()
com.mysema.query.jpa.impl.AbstractJPAQuery.getSingleResult(javax.persistence.Query)
com.mysema.query.jpa.impl.AbstractJPAQuery.uniqueResult()
com.mysema.query.jpa.impl.AbstractJPAQuery.uniqueResult(com.mysema.query.types.Expression)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(java.lang.Object, java.lang.reflect.Method, java.lang.Object[ ])
com.sun.proxy.$Proxy115.findOne(com.mysema.query.types.Predicate)

Answer 1

This is WHY show warnings is being issued: org.hibernate.engine.jdbc.spi.SqlExceptionHelper.handleAndClearWarnings() is calling com.mysql.jdbc.StatementImpl.getWarnings().

A comment in the Hibernate source code says:

"See HHH-9174. Statement#getWarnings can be an expensive call for many JDBC libs. Don't do it unless the log level would actually allow a warning to be logged."

I upped the logging level on logback.xml on "org.hibernate" to ERROR. Profiling shows SHOW WARNINGS query is NO LONGER being issued.

This has made a marginal improvement in performance.

I would appreciate any input as to whether it is actually a good idea to disable SHOW WARNINGS here.

Answer 2

Nothing of this works on payara & log4j, hibernate is still polluting DB with 'show warnings' statements