Description:
After bumping the version of mysql-connector-j to 8.0.32 we noticed that batch inserts made by prepared statements took much longer time to execute. After a bit of investigation we found that this only happens on some of our inserts. Namely inserts where we have column names that have the word "value" in them and where the query takes placeholders "?".
This bug seems to have been introduced in 8.0.29.
How to repeat:
Test reproducing the bug:
import static org.junit.jupiter.api.Assertions.assertTrue;
import com.mysql.cj.jdbc.ClientPreparedStatement;
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.jupiter.api.Test;
class RewritableAsMultiValuesTest {
@Test
void rewritableAsMultiValuesBug() throws SQLException {
MysqlDataSource dataSource = new MysqlDataSource();
String url = "DB CONNECTION STRING HERE";
dataSource.setURL(url);
dataSource.setRewriteBatchedStatements(true);
Connection connection = dataSource.getConnection();
//QUERY THAT IS EVALUATED CORRECT
PreparedStatement preparedStatement = connection.prepareStatement(
"INSERT INTO test (a, b) VALUES (?, ?)"
);
ClientPreparedStatement clientPreparedStatement = (ClientPreparedStatement) preparedStatement;
boolean isRewritable = clientPreparedStatement.getQueryInfo()
.isRewritableWithMultiValuesClause();
assertTrue(isRewritable);
connection.close();
//QUERY THAT IS EVALUATED INCORRECTLY
connection = dataSource.getConnection();
PreparedStatement preparedStatementBug = connection.prepareStatement(
"INSERT INTO test (a_value, bvalue) VALUES (?, ?)"
);
ClientPreparedStatement clientPreparedStatementBug = (ClientPreparedStatement) preparedStatementBug;
boolean isRewritableBug = clientPreparedStatementBug.getQueryInfo()
.isRewritableWithMultiValuesClause();
assertTrue(isRewritableBug);
}
}
Suggested fix:
The problem lies in QueryInfo.class. The valuesClauseEnd gets set too early in the query parsing.
© 2023, Oracle Corporation and/or its affiliates
Description: After bumping the version of mysql-connector-j to 8.0.32 we noticed that batch inserts made by prepared statements took much longer time to execute. After a bit of investigation we found that this only happens on some of our inserts. Namely inserts where we have column names that have the word "value" in them and where the query takes placeholders "?". This bug seems to have been introduced in 8.0.29. How to repeat: Test reproducing the bug: import static org.junit.jupiter.api.Assertions.assertTrue; import com.mysql.cj.jdbc.ClientPreparedStatement; import com.mysql.cj.jdbc.MysqlDataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import org.junit.jupiter.api.Test; class RewritableAsMultiValuesTest { @Test void rewritableAsMultiValuesBug() throws SQLException { MysqlDataSource dataSource = new MysqlDataSource(); String url = "DB CONNECTION STRING HERE"; dataSource.setURL(url); dataSource.setRewriteBatchedStatements(true); Connection connection = dataSource.getConnection(); //QUERY THAT IS EVALUATED CORRECT PreparedStatement preparedStatement = connection.prepareStatement( "INSERT INTO test (a, b) VALUES (?, ?)" ); ClientPreparedStatement clientPreparedStatement = (ClientPreparedStatement) preparedStatement; boolean isRewritable = clientPreparedStatement.getQueryInfo() .isRewritableWithMultiValuesClause(); assertTrue(isRewritable); connection.close(); //QUERY THAT IS EVALUATED INCORRECTLY connection = dataSource.getConnection(); PreparedStatement preparedStatementBug = connection.prepareStatement( "INSERT INTO test (a_value, bvalue) VALUES (?, ?)" ); ClientPreparedStatement clientPreparedStatementBug = (ClientPreparedStatement) preparedStatementBug; boolean isRewritableBug = clientPreparedStatementBug.getQueryInfo() .isRewritableWithMultiValuesClause(); assertTrue(isRewritableBug); } } Suggested fix: The problem lies in QueryInfo.class. The valuesClauseEnd gets set too early in the query parsing.