Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Multiline create procedure script causes UnableToExecuteStatementException #2535

Closed
IrinaTerlizhenko opened this issue Nov 16, 2023 · 2 comments · Fixed by #2547
Closed

Multiline create procedure script causes UnableToExecuteStatementException #2535

IrinaTerlizhenko opened this issue Nov 16, 2023 · 2 comments · Fixed by #2547
Assignees
@hgschmie
Labels
bug

Comments

@IrinaTerlizhenko
Copy link

IrinaTerlizhenko commented Nov 16, 2023 •
edited

Starting from version org.jdbi:jdbi3-core:3.31.0 I have UnableToExecuteStatementException when running a multiline script to create a procedure in MySQL via JDBI. Versions prior to 3.30.0 (including) don't have this issue. It is still reproduced in the latest 3.41.3 release.

I suspect it might be related to #2021, but not sure. Some other statements, like for example DELETE FROM tablename WHERE ...;, successfully pass even when multiline.

JDBI versions: 3.31.0+
Java version: 11
MySQL version: 8.0.32
JDBC Driver: software.aws.rds:aws-mysql-jdbc:1.1.11

Test to reproduce:

plugins {
    id 'java'
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.jdbi:jdbi3-core:3.31.0'
    runtimeOnly 'software.aws.rds:aws-mysql-jdbc:1.1.11'
    testImplementation 'org.junit.jupiter:junit-jupiter-api:5.8.1'
    testRuntimeOnly 'org.junit.jupiter:junit-jupiter-engine:5.8.1'
}

test {
    useJUnitPlatform()
}
import org.jdbi.v3.core.Jdbi;
import org.junit.jupiter.api.Test;

public class QweTest {
    private static final Jdbi jdbi = Jdbi.create("jdbc:mysql://localhost:3306/dbname");

    @Test
    public void passesSingleLine() {
        String sqlScript = "CREATE PROCEDURE QWE() " +
                "BEGIN " +
                "END; " +
                "DROP PROCEDURE IF EXISTS QWE;";
        jdbi.withHandle(h -> h.createScript(sqlScript).execute());
    }

    @Test
    public void failsMultiLine() {
        String sqlScript = "CREATE PROCEDURE QWE()\n" +
                "BEGIN\n" +
                "END;\n" +
                "DROP PROCEDURE IF EXISTS QWE;\n";
        jdbi.withHandle(h -> h.createScript(sqlScript).execute());
    }
}

Output from failsMultiLine:

java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP PROCEDURE IF EXISTS QWE' at line 5 [statement:"null", arguments:{positional:{}, named:{}, finder:[]}]
org.jdbi.v3.core.statement.UnableToExecuteStatementException: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP PROCEDURE IF EXISTS QWE' at line 5 [statement:"null", arguments:{positional:{}, named:{}, finder:[]}]
	at app//org.jdbi.v3.core.statement.Batch.execute(Batch.java:89)
	at app//org.jdbi.v3.core.statement.Script.execute(Script.java:43)
	at app//QweTest.lambda$failsMultiLine$1(QweTest.java:24)
	at app//org.jdbi.v3.core.Jdbi.withHandle(Jdbi.java:357)
	at app//QweTest.failsMultiLine(QweTest.java:24)
	at java.base@11.0.18/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base@11.0.18/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base@11.0.18/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base@11.0.18/java.lang.reflect.Method.invoke(Method.java:566)
	at app//org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
	at app//org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at app//org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
	at app//org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
	at app//org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
	at app//org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
	at app//org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
	at app//org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
	at app//org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
	at app//org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
	at app//org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
	at app//org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
	at app//org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
	at app//org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
	at app//org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
	at app//org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at app//org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)
	at app//org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
	at app//org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
	at app//org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
	at app//org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
	at app//org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
	at java.base@11.0.18/java.util.ArrayList.forEach(ArrayList.java:1541)
	at app//org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
	at app//org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
	at app//org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
	at app//org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
	at java.base@11.0.18/java.util.ArrayList.forEach(ArrayList.java:1541)
	at app//org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
	at app//org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
	at app//org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
	at app//org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
	at app//org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
	at app//org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
	at app//org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
	at app//org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
	at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
	at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
	at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor$CollectAllTestClassesExecutor.processAllTestClasses(JUnitPlatformTestClassProcessor.java:99)
	at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor$CollectAllTestClassesExecutor.access$000(JUnitPlatformTestClassProcessor.java:79)
	at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor.stop(JUnitPlatformTestClassProcessor.java:75)
	at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.stop(SuiteTestClassProcessor.java:61)
	at java.base@11.0.18/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base@11.0.18/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base@11.0.18/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base@11.0.18/java.lang.reflect.Method.invoke(Method.java:566)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:36)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
	at org.gradle.internal.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:33)
	at org.gradle.internal.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:94)
	at com.sun.proxy.$Proxy2.stop(Unknown Source)
	at org.gradle.api.internal.tasks.testing.worker.TestWorker$3.run(TestWorker.java:193)
	at org.gradle.api.internal.tasks.testing.worker.TestWorker.executeAndMaintainThreadName(TestWorker.java:129)
	at org.gradle.api.internal.tasks.testing.worker.TestWorker.execute(TestWorker.java:100)
	at org.gradle.api.internal.tasks.testing.worker.TestWorker.execute(TestWorker.java:60)
	at org.gradle.process.internal.worker.child.ActionExecutionWorker.execute(ActionExecutionWorker.java:56)
	at org.gradle.process.internal.worker.child.SystemApplicationClassLoaderWorker.call(SystemApplicationClassLoaderWorker.java:133)
	at org.gradle.process.internal.worker.child.SystemApplicationClassLoaderWorker.call(SystemApplicationClassLoaderWorker.java:71)
	at app//worker.org.gradle.process.internal.worker.GradleWorkerMain.run(GradleWorkerMain.java:69)
	at app//worker.org.gradle.process.internal.worker.GradleWorkerMain.main(GradleWorkerMain.java:74)
Caused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP PROCEDURE IF EXISTS QWE' at line 5
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.util.Util.handleNewInstance(Util.java:193)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.util.Util.getInstance(Util.java:168)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.util.Util.getInstance(Util.java:175)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:893)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:797)
	at org.jdbi.v3.core.statement.SqlLoggerUtil.wrap(SqlLoggerUtil.java:31)
	at org.jdbi.v3.core.statement.Batch.execute(Batch.java:87)
	... 89 more
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP PROCEDURE IF EXISTS QWE' at line 5
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1339)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:859)
	... 92 more
@hgschmie
Copy link
Contributor

Hi @IrinaTerlizhenko,

thank you for filing a bug report with Jdbi. I could reproduce this.

@hgschmie hgschmie self-assigned this Nov 23, 2023
@hgschmie hgschmie mentioned this issue Nov 24, 2023
Merged
hgschmie added a commit to hgschmie/jdbi that referenced this issue Nov 24, 2023
@hgschmie
Fix script parsing for END with semicolon
d5d13de 
Script parsing raises its ugly head again. Again. Allow for `END;`
as it is used within MySQL scripts.

Fixes jdbi#2535
hgschmie added a commit to hgschmie/jdbi that referenced this issue Nov 24, 2023
@hgschmie
Fix script parsing for END with semicolon
ba9fcc1 
Script parsing raises its ugly head again. Again. Allow for `END;`
as it is used within MySQL scripts.

Fixes jdbi#2535
hgschmie added a commit to hgschmie/jdbi that referenced this issue Nov 24, 2023
@hgschmie
Fix script parsing for END with semicolon
205d077 
Script parsing raises its ugly head again. Again. Allow for `END;`
as it is used within MySQL scripts.

Fixes jdbi#2535
hgschmie added a commit to hgschmie/jdbi that referenced this issue Nov 24, 2023
@hgschmie
Fix script parsing for END with semicolon
8debeaf 
Script parsing raises its ugly head again. Again. Allow for `END;`
as it is used within MySQL scripts.

Fixes jdbi#2535
@hgschmie
Copy link
Contributor

#2547 fixes this. Will be released as part of the next release (3.42.0).

@IrinaTerlizhenko IrinaTerlizhenko mentioned this issue Dec 1, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@hgschmie hgschmie

Labels
bug
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix script parsing for END with semicolon hgschmie/jdbi
2 participants
@hgschmie @IrinaTerlizhenko