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.
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.