SET SESSION variable has not effect
Posted by: Mathieu B
Date: July 02, 2010 09:56AM

Hi,

I am currently working on a java client using jdbc to insert important amount of data into a mysql table.

My choice is to insert multiple rows at once using multiple-insert syntax : "INSERT INTO table (a,b) VALUES (1,2),(3,4),(5,6)..."

Thus I append to submit to mysql big insert statement which size exceeds mysql's default max_allowed_packet value (which is 1M).

So I tried to modify this value on session-scope basis, before submiting my big insert. But it seems it just has not effect, and the query is still refused by mysql.

Here is my java code :

--------------------------------------------------------------------
// increase max_allowed_packet to 8M
Statement stmtSet = connection.createStatement();
stmtSet.execute("SET SESSION max_allowed_packet = 8 * 1024 * 1024;");

// check if value was changed
Statement stmtShow = connection.createStatement();
ResultSet rs = stmtShow.executeQuery("SHOW SESSION VARIABLES like 'max_allowed_packet'");
rs.next();
System.out.println(rs.getString(1)+"="+rs.getString(2));

// try to execute the big insert
Statement stmtInsert = connection.createStatement();
stmtInsert.executeUpdate(bigInsert));
------------------------------------------------------------------


And what this code prints :
-----------------------------------------------------------
max_allowed_packet=8388608
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4796533 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2655)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1604)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1343)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1260)
-------------------------------------------------------------------


As you can see the max_allowed_packet has indeed been changed to 8M value but I still get PacketTooBigException, although my insert query is only 4M long.

I also add that every statements are executed within the same session.

If someone has an idea of what's happening, I would appreciate a lot.

Thanks !



Edited 1 time(s). Last edit at 07/02/2010 10:04AM by Mathieu B.

Options: ReplyQuote


Subject
Written By
Posted
SET SESSION variable has not effect
July 02, 2010 09:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.