During batchUpdate() Single line comments are causing error "Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets."

[benkanlu]

Describe the bug

Driver does not properly deal with single-line quotes when using batchUpdate with multiple statements that have various combinations of single-line quotes using '-- ' or '\n-- '. This bug appears to have been introduced in version 1.0.0

Usage of comments in SQL is very common and batchUpdate should be able to handle this correctly in various combinations.

Expected Behavior

Similar to MariaDB/MySQL JDBC drivers, all comments (single-line, multi-line) should be properly ignored by the driver and not result in an confusing '... cannot issue statements that produce results sets.' error.

Current Behavior

2023-09-25T13:45:29.423-06:00 INFO 5952 --- [ main] c.e.B.BatchUpdateIssueApplication : Starting BatchUpdateIssueApplication using Java 17.0.6 with PID 5952 (C:\xxxxxx\BatchUpdateIssue\BatchUpdateIssue\target\classes started by xxxxxxin C:\xxxxxx\BatchUpdateIssue\BatchUpdateIssue)
2023-09-25T13:45:29.425-06:00 INFO 5952 --- [ main] c.e.B.BatchUpdateIssueApplication : No active profile set, falling back to 1 default profile: "default"
2023-09-25T13:45:29.772-06:00 INFO 5952 --- [ main] c.e.B.BatchUpdateIssueApplication : Started BatchUpdateIssueApplication in 0.525 seconds (process running for 0.882)
2023-09-25T13:45:29.775-06:00 INFO 5952 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2023-09-25T13:45:30.731-06:00 INFO 5952 --- [ main] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Added connection software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ConnectionImpl@508a65bf
2023-09-25T13:45:30.732-06:00 INFO 5952 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2023-09-25T13:45:31.381-06:00 INFO 5952 --- [ main] .s.b.a.l.ConditionEvaluationReportLogger :

Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled.
2023-09-25T13:45:31.392-06:00 ERROR 5952 --- [ main] o.s.boot.SpringApplication : Application run failed

java.lang.IllegalStateException: Failed to execute CommandLineRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:772) ~[spring-boot-3.0.3.jar:3.0.3]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:753) ~[spring-boot-3.0.3.jar:3.0.3]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:317) ~[spring-boot-3.0.3.jar:3.0.3]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1304) ~[spring-boot-3.0.3.jar:3.0.3]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1293) ~[spring-boot-3.0.3.jar:3.0.3]
at com.example.BatchUpdateIssue.BatchUpdateIssueApplication.main(BatchUpdateIssueApplication.java:16) ~[classes/:na]
Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [
-- ]; Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104) ~[spring-jdbc-5.3.5.jar:5.3.5]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-5.3.5.jar:5.3.5]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[spring-jdbc-5.3.5.jar:5.3.5]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[spring-jdbc-5.3.5.jar:5.3.5]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541) ~[spring-jdbc-5.3.5.jar:5.3.5]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393) ~[spring-jdbc-5.3.5.jar:5.3.5]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:625) ~[spring-jdbc-5.3.5.jar:5.3.5]
at com.example.BatchUpdateIssue.BatchUpdateIssueApplication.run(BatchUpdateIssueApplication.java:39) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-6.0.5.jar:6.0.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.0.5.jar:6.0.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.0.5.jar:6.0.5]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-6.0.5.jar:6.0.5]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.0.5.jar:6.0.5]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:390) ~[spring-tx-6.0.5.jar:6.0.5]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.0.5.jar:6.0.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.5.jar:6.0.5]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-6.0.5.jar:6.0.5]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) ~[spring-aop-6.0.5.jar:6.0.5]
at com.example.BatchUpdateIssue.BatchUpdateIssueApplication$$SpringCGLIB$$1.run() ~[classes/:na]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:769) ~[spring-boot-3.0.3.jar:3.0.3]
... 5 common frames omitted
Caused by: java.sql.BatchUpdateException: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets.
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) ~[na:na]
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) ~[na:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.util.Util.handleNewInstance(Util.java:193) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.util.Util.getInstance(Util.java:168) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.util.Util.getInstance(Util.java:175) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:891) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:795) ~[aws-mysql-jdbc-1.1.9.jar:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.ConnectionProxy$JdbcInterfaceProxy.lambda$invoke$0(ConnectionProxy.java:355) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.plugins.DefaultConnectionPlugin.execute(DefaultConnectionPlugin.java:80) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.plugins.NodeMonitoringConnectionPlugin.execute(NodeMonitoringConnectionPlugin.java:249) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.plugins.failover.FailoverConnectionPlugin.execute(FailoverConnectionPlugin.java:279) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.plugins.ConnectionPluginManager.execute(ConnectionPluginManager.java:138) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.ConnectionProxy$JdbcInterfaceProxy.invoke(ConnectionProxy.java:352) ~[aws-mysql-jdbc-1.1.9.jar:na]
at jdk.proxy2/jdk.proxy2.$Proxy48.executeBatch(Unknown Source) ~[na:na]
at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.HikariProxyStatement.executeBatch(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar:na]
at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallback.doInStatement(JdbcTemplate.java:585) ~[spring-jdbc-5.3.5.jar:5.3.5]
at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallback.doInStatement(JdbcTemplate.java:571) ~[spring-jdbc-5.3.5.jar:5.3.5]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ~[spring-jdbc-5.3.5.jar:5.3.5]
... 23 common frames omitted
Caused by: java.sql.SQLException: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets.
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1258) ~[aws-mysql-jdbc-1.1.9.jar:na]
at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:857) ~[aws-mysql-jdbc-1.1.9.jar:na]
... 40 common frames omitted

2023-09-25T13:45:31.396-06:00 INFO 5952 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2023-09-25T13:45:31.504-06:00 INFO 5952 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.

Process finished with exit code 1

Reproduction Steps

See the following repository to recreate error:
https://github.com/benkanlu/AWSJDBC_BatchUpdateIssue

These repo has 3 examples of when the singe-line comment errors out.
It also includes commented out MariaDB drivers...you can switch to these to verify it works fine with MariaDB.

Possible Solution

It appears the handling of the single_line quotes is in the StringInspector.java class (line 397) around the SearchMode.SKIP_LINE_COMMENTS section.

Additional Information/Context

No response

The AWS JDBC Driver for MySQL version used

Any version greater than 0.4.0 causes this issue.

JDK version used

OpenJDK Runtime Environment Corretto-17.0.6.10.1 (build 17.0.6+10-LTS)

Operating System and version

Windows 10 / Ubunutu 18.04

[crystall-bitquill]

Hi @benkanlu,

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!

[crystall-bitquill]

Hi @benkanlu,

We've recently merged a fix (PR #470) for this issue. Could you kindly checkout our snapshot build and let us know if the issue persists? Our latest build version is 1.1.11.

Thank you!

[crystall-bitquill]

Hi @benkanlu,

I wanted to check in and see if you were able to verify whether the fix resolved the issue. The fix for this issue was also included in our latest release of the driver, version 1.1.11.

If there are no further updates on this ticket in the next few days, it will be closed. If you have other questions or concerns, please feel free to reach out again.

Thank you!

[benkanlu]

I thought I had responded to this, but it didn't take/post, apparently. Yes, that version (1.1.11) now handles the comments without error. Thanks!