Setting allowMultiQueries leads to different results about aws-mysql-jdbc HOT 4 CLOSED

Hi @dwenking,

Thanks for reaching out and raising this issue.

We'll take a look at this and keep you updated as we investigate.

Thank you for your patience!

from aws-mysql-jdbc.

Hi @dwenking,

After some investigation, this behaviour seems to be a result of how the allowMultiQueries property behaves with batch statements and may not be a bug.

When allowMultiQueries is set to false, each statement in the batch is executed separately. Even if an error occurs on one statement, the driver will continue executing the other statements in the batch and will throw the error after all statements have been executed. In your test case, the select * will result in 1.670697762E9 (from the first insert statement) and 697762 (from the batch insert statement).

When allowMultiQueries is set to true, the statements in the batch are executed as a single query. When one of the statements results in an error, the execution is stopped and any statements following the error-causing statement will not be completed. In your test case, the first statement in the batch to insert 1670697762 fails, and the second statement to insert 697762 is not reached. This means select * will only result in 1670697762 from the first insert statement. Note that if you swap the order of the batch insert statements as seen below, the select * will result in 1670697762 (from the first insert statement) and 697762 (from the batch insert statement).

    bstmt.addBatch("INSERT INTO t0 VALUES (697762)");
    bstmt.addBatch("INSERT INTO t0 VALUES (1670697762)");

from aws-mysql-jdbc.

Thanks for your quick response! According to JDBC document:

If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands.

In my opinion, changing the value of allowMultiQueries should not affect the driver's behavior. Additionally, I have observed that the MariaDB and Postgres JDBC connectors do not exhibit this issue. Therefore, I think this issue may be a bug.

from aws-mysql-jdbc.

Hi @dwenking,

After some consideration, we have decided to close this ticket as we will not be issuing a fix.

There are a few reasons for this:

• There is some ambiguity in the documentation. It does say that a JDBC driver should be consistent and either always process remaining commands or never continue to process remaining commands after failing to execute a command in a batch update. However, whether the command must be executed as part of being processed is unclear. The driver is consistent with the specification that it returns an array with a size equal to the number of commands when the method BatchUpdateException.getUpdateCounts is called, which may or may not be enough to meet the specification of processing commands. Additionally, regardless of what the allowMultiQueries property is set to, the resulting getUpdateCounts will be the same. Therefore, it is a bit difficult to determine if this is a bug.
• The AWS JDBC Driver for MySQL is based on the MySQL Connector/J driver, and we would like to remain as consistent with it as possible. Making changes for this issue will alter the effects of the allowMultiQueries property and we are a bit hesitant to make changes of that magnitude. Have you considered reaching out and creating a bug report for the MySQL Connector/J driver?

Thank you for raising this issue, please let us know if you notice any other problems or inconsistencies.

from aws-mysql-jdbc.