[CONJ-869] Could not send query: query size is >= to max_allowed_packet (16777216) Created: 2021-03-26  Updated: 2021-10-27  Resolved: 2021-10-27

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 2.7.1, 2.7.2
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Maurice Betzel Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: need_feedback
Environment:

Windows 10, 10.5.5-MariaDB, AdoptOpenJDK 8.0.282.8-hotspot, Hibernate 5.2.18.Final, junit4.12


 Description   

Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Could not send query: query size is >= to max_allowed_packet (16777216)

This exception occurs when trying to persist a lob with a size near, but still 8192 bytes smaller as max_allowed_packet size. I do split lager lobs into smaller parts before persisting and most do function as expected. Only the edge cases not.

Stacktrace:

2021-03-26 13:27:50 [main] ERROR SqlExceptionHelper:131 - (conn=6) Could not send query: query size is >= to max_allowed_packet (16777216)
 
javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: could not execute statement
 
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
 
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
 
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)
 
	at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:790)
 
	at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:768)
 
	at eu.abeel.platform.facade.mariadb.blob.repository.persistence.impl.DefaultPlatformBlobRepositoryPersistence.insertBlob(DefaultPlatformBlobRepositoryPersistence.java:85)
 
	at eu.abeel.platform.facade.mariadb.blob.repository.persistence.test.DefaultPlatformBlobRepositoryPersistenceTest.execute(DefaultPlatformBlobRepositoryPersistenceTest.java:63)
 
	at eu.abeel.platform.facade.mariadb.blob.repository.persistence.test.TestBase.executeTest(TestBase.java:43)
 
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 
	at java.lang.reflect.Method.invoke(Method.java:498)
 
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
 
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
 
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
 
	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.runners.ParentRunner.runLeaf(ParentRunner.java:325)
 
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
 
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
 
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
 
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
 
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
 
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
 
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
 
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
 
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
 
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
 
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
 
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)
 
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
 
Caused by: org.hibernate.exception.JDBCConnectionException: could not execute statement
 
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48)
 
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
 
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
 
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
 
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
 
	at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
 
	at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
 
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2933)
 
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3524)
 
	at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
 
	at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:637)
 
	at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
 
	at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
 
	at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
 
	at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:318)
 
	at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:275)
 
	at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:182)
 
	at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:113)
 
	at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWithGeneratedId(JpaPersistEventListener.java:67)
 
	at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:189)
 
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:132)
 
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:58)
 
	at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:783)
 
	... 28 more
 
Caused by: java.sql.SQLTransientConnectionException: (conn=6) Could not send query: query size is >= to max_allowed_packet (16777216)
 
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)
 
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
 
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)
 
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229)
 
	at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149)
 
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:181)
 
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
 
	... 46 more
 
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Could not send query: query size is >= to max_allowed_packet (16777216)
 
	at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
 
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:192)
 
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:175)
 
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321)
 
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220)
 
	... 49 more
 
Caused by: java.sql.SQLTransientConnectionException: Could not send query: query size is >= to max_allowed_packet (16777216)
 
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.handleIoException(AbstractQueryProtocol.java:2049)
 
	... 51 more
 
Caused by: java.net.SocketException: Connection reset by peer: socket write error
 
	at java.net.SocketOutputStream.socketWrite0(Native Method)
 
	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
 
	at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
 
	at org.mariadb.jdbc.internal.io.output.StandardPacketOutputStream.flushBuffer(StandardPacketOutputStream.java:111)
 
	at org.mariadb.jdbc.internal.io.output.AbstractPacketOutputStream.flush(AbstractPacketOutputStream.java:186)
 
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:314)
 
	... 50 more
 
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF-8

MariaDB error log:

InnoDB: using atomic writes.
2021-03-26 13:27:31 0 [Note] InnoDB: Uses event mutexes
2021-03-26 13:27:31 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-03-26 13:27:31 0 [Note] InnoDB: Number of pools: 1
2021-03-26 13:27:31 0 [Note] InnoDB: Using SSE4.2 crc32 instructions
2021-03-26 13:27:31 0 [Note] InnoDB: Initializing buffer pool, total size = 2147483648, chunk size = 134217728
2021-03-26 13:27:31 0 [Note] InnoDB: Completed initialization of buffer pool
2021-03-26 13:27:31 0 [Note] InnoDB: 128 rollback segments are active.
2021-03-26 13:27:31 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-03-26 13:27:31 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-03-26 13:27:31 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2021-03-26 13:27:31 0 [Note] InnoDB: 10.5.5 started; log sequence number 18986157899; transaction id 75841
2021-03-26 13:27:31 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-03-26 13:27:31 0 [Note] InnoDB: Loading buffer pool(s) from C:\Program Files\MariaDB 10.5\data\ib_buffer_pool
2021-03-26 13:27:31 0 [Note] Server socket created on IP: '::'.
2021-03-26 13:27:31 0 [Note] Reading of all Master_info entries succeeded
2021-03-26 13:27:31 0 [Note] Added new Master_info '' to hash table
2021-03-26 13:27:31 0 [Note] C:\Program Files\MariaDB 10.5\bin\mysqld.exe: ready for connections.
Version: '10.5.5-MariaDB' socket: '' port: 3306 mariadb.org binary distribution
2021-03-26 13:27:32 0 [Note] InnoDB: Buffer pool(s) load completed at 210326 13:27:32
2021-03-26 13:27:50 6 [Warning] Aborted connection 6 to db: 'platform_blob_repository' user: 'root' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)

I suspect that the writeBytesEscaped method in AbstractPacketOutpustream does not flush the stream to the MariaDB soon enough resulting in too many bytes, because the checkMaxAllowedLength method does not throw this error but the outputstream write method in the StandardPacketOutputStream class on line 111.

 Comments   
Comment by Diego Dupin [ 2021-06-11 ]

max_allowed_packet correspond to the command maximum size.
Blob can have a size a little less than this size and still have this exception : Query size is send, and blob will be escaped, that might result in additionnal bytes.

If that's normal to have packet this size, the best is to set global max_allowed_packet to a greater value (will only apply for newer connections).
As a workaround, you can use binary prepared statement (option `useServerPrepStmts`) that use a different protocol, sending blobs in separate packet without escaping, so blob can then be really up to max_allowed_packet.

Generated at Thu Feb 08 03:18:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.