MySQL Bugs: #10859: PacketTooBigException using PreparedStatement.setBinaryStream

Bug #10859	PacketTooBigException using PreparedStatement.setBinaryStream
Submitted:	25 May 2005 13:06	Modified:	25 May 2005 13:46
Reporter:	James Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.8a	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When attempting to set the content of a BLOB using PreparedStatement.setBinaryStream I get a PacketTooBigException executing the statement when trying to stream content greater than 1MB.

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1048587 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2561)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2547)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1512)
	at com.mysql.jdbc.ServerPreparedStatement.storeStream(ServerPreparedStatement.java:1899)
	at com.mysql.jdbc.ServerPreparedStatement.serverLongData(ServerPreparedStatement.java:1469)
	at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1289)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:952)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1511)
	at Test.main(Test.java:75)

max_allowed_packet and blobSendChunkSize are both at their default 1024*1024 sizes.

Workaround:
Add the property blobSendChunkSize =1000000 to the JDBC URL

How to repeat:
create table blob_table(content LONGBLOB);

public class Test {
  public static void main(String[] args) { 
     try {
       File file = new File("C:\\LARGEFILE.zip");
       FileInputStream in = new FileInputStream(file);
       DriverManager.registerDriver(new com.mysql.jdbc.Driver());
       Connection con = DriverManager.getConnection
              ("jdbc:mysql://localhost:3306/test", "admin", "password");
       String query = "insert into blob_table values(?)";
       PreparedStatement pstmt = con.prepareStatement(query);
       pstmt.setBinaryStream(1, (InputStream) in, (int) file.length());
       pstmt.execute();
       System.out.println("OK");
     }
     catch (Exception e) { 
       e.printStackTrace();
     }
   }
}

Suggested fix:
ServerPreparedStatement.storeStream appears not to send a command packet until it has exceeded the blob chunk size so by default this will also exceed the maximum packet size. storeStream should send command packets before the smaller of max_allowed_packet and blobSendChunkSize is exceeded.

This is already fixed in Connector/J 3.1.9 (to be released later this week).

See the nightly downloads of 3.1.x at http://downloads.mysql.com/snapshots.php

(there's now a "blobSendChunkSize" configuration property one can use, the default is 1mb.)

This is bug occurs using mySQL 5.0 on Windows 2000 using the Connector/J 5.1.5 using exactly the same test code.  It does not appear that setting the blobSendChunkSize property works.  Setting the server property max_allowed_packet greater than the blob size works.

"blobSendChunkSize" only applies to server-side prepared statements. They're not enabled by default in Connector/J 5.0.x or 5.1.x because of issues with the server-side implementation of them.

Client-side prepared statements can not "stream" blobs to the server.