[CONJ-960] Syntax error RENAME TABLE Created: 2022-04-12  Updated: 2022-06-02  Resolved: 2022-06-02

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

Type: Bug Priority: Major
Reporter: Patrick Neyer Assignee: Diego Dupin
Resolution: Won't Do Votes: 0
Labels: None
Environment:

MariaDB 10.4.18 Docker Image


 Description   

Using MariaDB Java Client version 3.0.4 (and latest Spring Data JPA/Hibernate), we get following exception:

java.sql.SQLSyntaxErrorException: (conn=8) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RENAME TABLE x_partial TO x' at line 1
 
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:270)
 
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:358)
 
	at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:133)
 
	at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:815)
 
	at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:754)
 
	at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:673)
 
	at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:616)
 
	at org.mariadb.jdbc.Statement.executeInternal(Statement.java:906)
 
	at org.mariadb.jdbc.Statement.execute(Statement.java:1031)
 
	at org.mariadb.jdbc.Statement.execute(Statement.java:441)
 
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
 
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
 
	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
 
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)

Was working with MariaDB Java Client 2.7.5 (and all older 2.x versions).

 Comments   
Comment by Diego Dupin [ 2022-04-14 ]

This error 'RENAME TABLE x_partial TO x' error is just a message returned from server, it doesn't help much to identify the issue.

Since it this was working with 2.7.5, either hibernate doesn't send the same kind of query, either server has changed too.
If you can reproduced the issue, can you set "TRACE" logging level to "org.mariadb.jdbc" logger ? this would permit to log exchanges with server to have a more detailed view of the problem.

Comment by Diego Dupin [ 2022-05-13 ]

any news ?

Comment by Patrick Neyer [ 2022-05-31 ]

Sorry for the late reply.
The actual issue is a different one than the one reported: we execute two commands in one query using JdbcTemplate (from Spring JDBC). The complete statement was:

jdbcTemplate.execute("DROP TABLE xyz; RENAME TABLE xyz_partial TO xyz;");

This statement fails with the error message above. As a workaround, when we execute the these two commands as separate Queries, the code runs perfectly.

So the real issue is: Regression-Bug: You cannot execute two sql queries in the same statement anymore using JdbcTemplate

Comment by Diego Dupin [ 2022-06-02 ]

By default this is disable. But there is an option to permit that : `allowMultiQueries` (see options https://mariadb.com/kb/en/about-mariadb-connector-j/#infrequently-used-parameters).
(This option is disable by default)

Comment by Patrick Neyer [ 2022-06-02 ]

We do not use the parameter "allowMultiQueries" anywhere.
Also these two sql-commands worked with version 2.7.5 but did not work with version 3.0.4/3.0.5 anymore, without altering any jdbc connection properties and/or code.

Comment by Diego Dupin [ 2022-06-02 ]

allright, found the problem: multiple queries in 2.7 are enabled either by enabling options `allowMultiQueries` or `rewriteBatchedStatements`.
The option `rewriteBatchedStatements` doesn't exists anymore in 3.0, replaced by a more performant solution (bulk).

In order to still allow multiqueries in 3.0, the option `allowMultiQueries` must explicitly be enable.

Comment by Patrick Neyer [ 2022-06-02 ]

Ok, we set "rewriteBatchedStatements=true" in jdbc connection string, that explains the behavior, you've nailed it
Thanks for clarification!
I think this ticket can be closed

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