Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-960

Syntax error RENAME TABLE

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Do
    • 3.0.4, 3.0.5
    • N/A
    • 2.7 compatibility
    • None
    • 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).

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          diego dupin Diego Dupin added a comment -

          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.

          diego dupin Diego Dupin added a comment - 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.
          diego dupin Diego Dupin added a comment -

          any news ?

          diego dupin Diego Dupin added a comment - any news ?
          pneyer Patrick Neyer added a comment -

          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

          pneyer Patrick Neyer added a comment - 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
          diego dupin Diego Dupin added a comment -

          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)

          diego dupin Diego Dupin added a comment - 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)
          pneyer Patrick Neyer added a comment -

          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.

          pneyer Patrick Neyer added a comment - 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.
          diego dupin Diego Dupin added a comment -

          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.

          diego dupin Diego Dupin added a comment - 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.
          pneyer Patrick Neyer added a comment -

          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

          pneyer Patrick Neyer added a comment - 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

          People

            diego dupin Diego Dupin
            pneyer Patrick Neyer
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.