How to set the mysql maximum packet size from a JDBC mysql client application.
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Matt_Busche
Posted On:   Tuesday, December 4, 2007 02:14 PM

I'm not sure this is the right forum to be asking this mysql specific question, but here goes: 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: MySQL servers have a configuration setting known as the maximum packet size. You can set this in /etc/my.cnf 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 upda   More>>

I'm not sure this is the right forum to be asking this mysql specific question, but here goes:


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:


MySQL servers have a configuration setting known as the maximum packet size. You can set this in /etc/my.cnf 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 '
'
(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. Why the mysql dev team designed it's replication engine to fail if your updates exceed some configurable parameter is beyond me. And why the load data infile command would by default use packets of this same size seems a mistake.


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=


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, including possibly pointers to other forums that might have experts better able to answer my question.


Matt Busche

Lakewood, CO

   <<Less

Re: How to set the mysql maximum packet size from a JDBC mysql client application.

Posted By:   Robert_Lybarger  
Posted On:   Tuesday, December 4, 2007 07:14 PM

Looks like you got an answer over here on another forum.
About | Sitemap | Contact