[CONJ-1074] Unable to execute OPTIMIZE TABLE Created: 2023-05-03  Updated: 2023-05-11  Resolved: 2023-05-11

Status: Closed
Project: MariaDB Connector/J
Component/s: 2.7 compatibility
Affects Version/s: 3.1.2, 3.1.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Rick Tuk Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

org.hibernate.orm.hibernate-core 6.1.7.Final
org.hibernate.orm.hibernate-hikaricp 6.1.7.Final
com.zaxxer.HikariCP version 5.0.1
org.mariadb.jdbc.mariadb-java-client 3.1.2 / 3.1.4


 Description   

When trying to execute an OPTIMIZE TABLE query:
entityManager.createNativeQuery("OPTIMIZE TABLE `nonce`;").executeUpdate();
an exception is thrown:
jakarta.persistence.PersistenceException: Converting `org.hibernate.exception.GenericJDBCException` to JPA `PersistenceException` : JDBC exception executing SQL [OPTIMIZE TABLE `nonce`;]
Caused by: java.sql.SQLException: (conn=929) the given SQL statement produces an unexpected ResultSet object

WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: -1, SQLState: HY000
ERROR o.h.e.jdbc.spi.SqlExceptionHelper - (conn=929) the given SQL statement produces an unexpected ResultSet object

This query used to work perfectly on the 2.x.x version of the connector

I have tested with connector version 3.1.2 and 3.1.4

 Comments   
Comment by Diego Dupin [ 2023-05-03 ]

3.x version of driver now respect specification for executeUpdate : see https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

in your case, replacing 

executeUpdate()

by 

executeQuery()

like 

entityManager.createNativeQuery("OPTIMIZE TABLE `nonce`;").executeQuery();

will solve this issue

Comment by Rick Tuk [ 2023-05-05 ]

entityManager,createNativeQuery() returns a Query object which only has the executeUpdate() method:
https://jakarta.ee/specifications/persistence/3.0/apidocs/jakarta.persistence/jakarta/persistence/query

I don't see how to get this to work

Comment by Diego Dupin [ 2023-05-05 ]

ah yes, must be changed to a command that will call executeQuery, so that must be a jakarta.persistence.Query.getResultList() to execute under the hood org.mariadb.jdbc.Statement.executeQuery

Comment by Rick Tuk [ 2023-05-05 ]

Thank you for your help, getResultList() fixes my problem!

Generated at Thu Feb 08 03:20:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.