Home / server / Questions / 243247
Accepted
blunders
Asked: 2011-03-05 07:25:55 +0800 CST

MySQL "max_allowed_packet" error -- how do you profile the max packet size?

  • 772

Keep running into into the "max_allowed_packet" error msg, which to data I've just guessed at what the number should be....

I'd like to stop guessing, and know how to see what the number should be -- and it appears that info might be in the database table info in the "Data_length" column.

Is that correct, and if so, how do I calculate the value to enter in the MySQL config to avoid the error before I get it.

(If it matters, I keep getting these errors on DB restores.)

Questions, feedback, requests -- just comment, thanks!!

Additional info: The database is INNOdb, not MyISAM.

UPDATE (1):

In an effort to find a way to benchmark the "max_packet" I did the following, ran the restore, got the error with the line number causing the error, dumped that line into a text file to measure the file size.

ERROR:

ERROR 1153 (09S01) at line 2742: Got a packet bigger than "max_allowed_packet" bytes

SED code:

sed 'INSERTLINENUMBERq;d' INSERTDATABASEDUMPFILENAME.sql > INSERTLINENUMBER.txt

Problem is... that line is only 8mb, "max_allowed_packet" is set to 100mb.

backup mysql restore

1 Answers

  1. Best Answer

    I'm not 100% sure how to profile a max_allowed_packet situation. But I know I have gotten a ~3 gig database export to import on a max_allowed_packet size of 100mb. What I usually do is just set it arbitrarily high, import the data then set it back down to 1M and restart mysqld.

    • 1