MySQL Bugs: #14180: max_allowed_packet set for session ignored (for PreparedStatement)

Bug #14180	max_allowed_packet set for session ignored (for PreparedStatement)
Submitted:	20 Oct 2005 13:44	Modified:	15 Nov 2005 13:58
Reporter:	Gustav Oesgaard	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.10	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Note: max_allowed_packet is set to 1M in my.ini.

Overriding max_allowed_packet set in my.ini will not work. The value I´m setting is greater than the value in my.ini but the docs I´ve found says the session variable will override it so I think it should work.

I´ve tried to assemble the relevant code here:
...
Connection connection = DriverManager.getConnection(url, username, password);
Statement stmt = connection.createStatement();
stmt.executeQuery("SET SESSION max_allowed_packet=16777216");  //16M
ResultSet rs = stmt.executeQuery("SHOW variables LIKE 'max_allowed_packet'");
rs.next();
System.out.println (rs.getString(1) + ":" + rs.getInt(2)); // prints max_allowed_packet:16777216
String sql = "INSERT INTO some_table (NAME, DATA) VALUES('aName', ?)";
PreparedStatement ps = dbConnection.prepareStatement(sql);
ps.setBytes(1, largeByteArr7Megabytes);
ps.execute();  // PacketTooBigException thrown "Packet for query is too large (6957205 > 1047552). You can change this value on the server by setting the max_allowed_packet' variable."
...

I´ve tried to use setMaxFieldSize() on the PreparedStatement object but that throws "SQLException: Can not set max field size > max allowed packet of 1 047 552 bytes" which I also think is wrong since the message refers to the value set in my.ini instead of the variable I´ve set for the session.
 

How to repeat:
Obvious (see code sample in description).

Suggested fix:
Use max_allowed_packet set for session instead of the global variable.

Could you please say me version of mysql ?

Sorry, version of server is "mysql  Ver 14.7 Distrib 4.1.12a, for Win32 (ia32)"

Thanks for the bug report. I was able to reproduce the bug. My test case is attached.

Test case

Attachment: Bug14180.java (text/java), 1.32 KiB.

Great that you were able to reproduce the bug.

Do you know any "workaround" for this issue (a way to let me insert the large amount of data without modifying my.ini)?

You must use the "sessionVariables=..." to set this session value for max_allowed_packet. These variables are sent before the driver reads the values for itself during connection initialization.

You can _not_ issue any "set system_variable" queries from JDBC directly, this is not supported, as the driver reads the max_allowed_packet value _once_ on connection initialization. The server doesn't provide information to tell clients when these values have changed, so there's no efficient way to poll for changes to this value.

The driver must enforce max_allowed_packet on the client side, as older versions of the server would clobber the connection with no useful error message when the packet exceeded max_allowed_packet. This can also be considered a denial-of-service attack, as it would keep the server busy reading all kinds of large packets.