[CONJ-1052] regression in 3.x.y: parameterized batch in multiquery mode "SET @name := ?; INSERT INTO products( name ) VALUES ( @name )" fails Created: 2023-02-07  Updated: 2023-03-01

Status: Open
Project: MariaDB Connector/J
Component/s: 2.7 compatibility
Affects Version/s: 3.1.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Dmitriy Pichugin Assignee: Diego Dupin
Resolution: Unresolved Votes: 1
Labels: None
Environment:

win10pro[64bit], 10.5.4-MariaDB, java8

Attachments: Java Source File MariaDB_Regression3.java    

 Description   

regression.
2.7.x connector works, 3.x.y connector fails to execute parameterized batch of statements in multiquery mode [in connection url: allowMultiQueries=true]
"SET @name := ?; INSERT INTO products( name ) VALUES ( @name )".

OUTPUT when fails with connector driver ver. 3.1.2:

 running:
 
  SELECT version() as serverVersion
 
 Server version: [{serverVersion=10.5.4-MariaDB}]
 
 Driver version: 3.1.2
 
 running:
 
  DROP TABLE IF EXISTS products
 
 running:
 
  CREATE TABLE IF NOT EXISTS products (
 
    id       int unsigned AUTO_INCREMENT NOT NULL,
 
    name     varchar( 100 ) NOT NULL,
 
    created  datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
 
    PRIMARY KEY ( id )
 
  ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci
 
 running batch:
 
  SET @name := ?;
 
  INSERT INTO products( name ) VALUES ( @name )
 
[ WARN] (main) Error: 1064-42000: 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 'INSERT INTO products( name ) VALUES ( @name )' at line 2
 
[ WARN] (main) Error: 1243-HY000: Unknown prepared statement handler (4294967295) given to mysqld_stmt_execute
 
Exception in thread "main" java.sql.BatchUpdateException: (conn=1356) 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 'INSERT INTO products( name ) VALUES ( @name )' at line 2
 
	at org.mariadb.jdbc.export.ExceptionFactory.createBatchUpdate(ExceptionFactory.java:181)
 
	at org.mariadb.jdbc.ClientPreparedStatement.executeBatchBulk(ClientPreparedStatement.java:179)
 
	at org.mariadb.jdbc.ClientPreparedStatement.executeInternalPreparedBatch(ClientPreparedStatement.java:115)
 
	at org.mariadb.jdbc.ClientPreparedStatement.executeBatch(ClientPreparedStatement.java:466)
 
	at test.MariaDB_Regression3.runBatch(MariaDB_Regression3.java:88)
 
	at test.MariaDB_Regression3.main(MariaDB_Regression3.java:108)
 
Caused by: java.sql.BatchUpdateException: (conn=1356) 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 'INSERT INTO products( name ) VALUES ( @name )' at line 2
 
	at org.mariadb.jdbc.export.ExceptionFactory.createBatchUpdate(ExceptionFactory.java:215)
 
	at org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:620)
 
	at org.mariadb.jdbc.ClientPreparedStatement.executeBatchBulk(ClientPreparedStatement.java:148)
 
	... 4 more
 
Caused by: java.sql.SQLSyntaxErrorException: (conn=1356) 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 'INSERT INTO products( name ) VALUES ( @name )' at line 2
 
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:282)
 
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:370)
 
	at org.mariadb.jdbc.message.client.PreparePacket.readPacket(PreparePacket.java:74)
 
	at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:855)
 
	at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:794)
 
	at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:713)
 
	at org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:571)
 
	... 5 more



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

This is a known problem, resulting from a clear choice in favor of performance for version 3.

Now, when setting option `useServerPrepStmts`, prepared statement really only use PREPARE + EXECUTE.
This permits many optimization, like pipelining PREPARE + EXECUTE, saving network exchanges latency.
The problem is that PREPARE doesn't permit multiple statement execution.

in 2.x behaviour was this one :
standard prepared query work this way.
on Connection.prepareStatement :

  • send PREPARE
  • read PREPARE result
    then on execute:
  • send EXECUTE
  • read EXECUTE.

And as a workaround for command that cannot be prepared, when prepare fails, use client-side prepare statement in place of server prepare statement. This was the case for you example when using 2.x driver: connector did send a PREPARE command that result in an error, then use TEXT protocol. This was slow, but working in every cases.

with the 3 version, this works differently:
on execute:

  • if not already prepared,
    • send PREPARE + EXECUTE
    • read PREPARE response and EXECUTE response
  • if prepared, only send EXECUTE, then read EXECUTE response
    this save one "ping-pong" exchange to server, improving performance a lot.

Problem is connector now throw an exception for command that cannot be PREPAREd server side. There has been lots of work done server side in order to permit commands to be PREPAREd (like MDEV-16708), but MySQL and MariaDB doesn't permit multiple statement.

This choice improve prepare statement so much, i don't think it's a good idea to revert that change.

One thing that can be done is adding hints:
like beginning command with hint '/text/' , like using "/text/SET @name := ?; INSERT INTO products( name ) VALUES ( @name )"
this would permit connector to know that for this specific command client-side prepared statement is required.

This is not perfect, but can permit to solve some use-cases.

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