search

jOOQ / jOOQ

jOOQ is the best way to write SQL in Java
https://www.jooq.org
Other
6.16k stars 1.21k forks source link

Packet for query is too large (4,000,066 > 1,048,576) when inserting binary data into a BLOB column in MySQL #12963

Open lukaseder opened 2 years ago

lukaseder commented 2 years ago

The test introduced for #12956 fails on a few other dialects, including:

List to be amended.

It fails with:

org.jooq.exception.DataAccessException: SQL [insert into `test`.`t_725_lob_test` (`ID`, `LOB`) values (?, ?)]; Packet for query is too large (4,000,066 > 1,048,576). You can change this value on the server by setting the 'max_allowed_packet' variable.
    at org.jooq_3.17.0-SNAPSHOT.MYSQL.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:3089)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:670)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:354)
    at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:115)
    at org.jooq.test.all.testcases.DataTypeTests.testDataType_LONGBLOB(DataTypeTests.java:1958)
    at org.jooq.test.jOOQAbstractTest.testDataType_LONGBLOB(jOOQAbstractTest.java:4045)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
    at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
    at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.testcontainers.containers.FailureDetectingExternalResource$1.evaluate(FailureDetectingExternalResource.java:30)
    at org.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
    at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:365)
    at org.apache.maven.surefire.junit4.JUnit4Provider.executeWithRerun(JUnit4Provider.java:273)
    at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:238)
    at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:159)
    at org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:384)
    at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:345)
    at org.apache.maven.surefire.booter.ForkedBooter.execute(ForkedBooter.java:126)
    at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:418)
Caused by: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (4,000,066 > 1,048,576). You can change this value on the server by setting the 'max_allowed_packet' variable.
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:107)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:419)
    at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:961)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:340)
    ... 38 more

I guess we could fix this in various dialects by defaulting to binding binary data via the JDBC Clob API instead of setting byte[] values. Curiously, this has never been an issue with users (at least not that I'm aware of) for many years.

lukaseder commented 2 years ago

While this here is a regression for H2 only, given that H2 has changed their default behaviour, for other RDBMS, the fix might introduce other problems (binding everything as Blob instead of byte[] has other impacts), so I won't backport the fix for this issue here.

lukaseder commented 2 years ago

For example, our R2DBC <-> JDBC bridge does not yet support binding Blob types...

lukaseder commented 2 years ago

The R2DBC issue will be fixed first, here: https://github.com/jOOQ/jOOQ/issues/12964

lukaseder commented 2 years ago

Fixed for jOOQ 3.17.0. As further integration tests might fail eventually, I'll update the issue discription with the affected dialects.

lukaseder commented 2 years ago

Binding a Blob doesn't seem to fix the issue for MySQL