setting max packet size from the client side
Posted by: Matthew Busche
Date: December 04, 2007 02:04PM

How can I configure the "client-side" maximum packet size for a mysql connection from JDBC (or via a C3P0 ComboPooledDataSource which is what I'm using to allocate connections)?

BACKGROUND:

I know you can set the maximum packet size on the server side via /etc/my.cnf something like this:

[mysqld]
max_allowed_packet=16M

Our mysql server administrators have recently been increasing this setting to huge values to resolve failures in mysql replication. (Apparently the maximum packet size has to be big enough to handle a single atomic update during replication. I'm frankly not sure what this means -- perhaps it means the packet has to be big enough to handle an entire transaction? Not sure, but in any case, they've currently set this to 1GB and our server replication errors have nicely gone away -- at least for now.)

But immediately after their most recent bump of this configuration paramter, my JDBC client application started failing. Specifically, I'm getting this error:

java.lang.Exception: Error while executing SQL: load data local infile '/home/ebill/work/load/UID-11230-ATT-20071014-1.idf' ignore
into table EbillStage fields terminated by '~' escaped by '\\' lines terminated by '\n'
(accountNum, serviceNum, recType, recSeq, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16)
at com.raveu.ebill.jobs.EbillLoader.loadBill(EbillLoader.java:182)
at com.raveu.ebill.jobs.EbillLoader.processFiles(EbillLoader.java:65)
at com.raveu.common.jobs.FileProcessor.process(FileProcessor.java:177)
at com.raveu.common.jobs.Processor.execute(Processor.java:19)
at com.raveu.common.jobs.Job.executeInternal(Job.java:31)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:66)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)
Caused by: java.sql.SQLException: Unable to allocate packet of size '1073740792' for LOAD DATA LOCAL INFILE. Either increase heap space available to your JVM, or adjust the MySQL server variable 'max_allowed_packet'
at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:1827)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1318)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1225)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2225)
at com.mysql.jdbc.Statement.execute(Statement.java:906)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
at com.raveu.ebill.jobs.EbillLoader.loadBill(EbillLoader.java:153)
... 7 more

As you can see, it's trying to allocate a 1GB packet. Annoyingly, the file it's trying to load via the "load data infile" command is only about 2MB. Apparently it's trying to allocate a maximum sized packet without knowing how much it really needs.

But the hardware I'm on has less than 400MB of RAM+SWAP, so I'm never going to be able to allocate a packet that large no matter what I do with the java -Xmx option.

This all seems rather silly. Assuming I'm not misinformed, it seems pretty bad that the mysql replication service fails if your updates exceed some configurable parameter. And given this to be the case, it seems a bad idea that the the load data infile command would by default use packets of this same size.

Now here's the interesting thing. In reading through the man pages for the "mysql" client program you find this command line option:

--max_allowed_packet=<size>

The maximum packet length to send to or receive from the server. (Default value is 16MB.)

So as a client, it is apparently possible to pick your own setting for the maximum allowed packet size you will send or accept from the server. Indeed, I can actually run the same troublesome "load data infile" via mysql successfully. Surely there must be a way to set this same client-side configuration item via JDBC or via my C3P0 DataSource, but I have been unable to find any on-line reference that would tell me how to do this.

My only other option is to start adding huge quantities of otherwise unneeded RAM/SWAP to the linux server running my application.

Thanks much for any help you can provide.

Matt Busche
Lakewood, CO

Options: ReplyQuote


Subject
Written By
Posted
setting max packet size from the client side
December 04, 2007 02:04PM


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.