'max_allowed_packet' value exceeded

For users or potential users.
Post Reply
ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

'max_allowed_packet' value exceeded

Post by ForrestGumpDDS » Sun Mar 12, 2017 2:56 pm

Hi guys,
I was getting things ready for mygrating to a new server, when I encountered this error while creating a backup from existing database:

Code: Select all

17:34:18 Dumping opendental (all tables)
Running: mysqldump.exe --defaults-file="c:\users\user\appdata\local\temp\tmpowfbmb.cnf"  --set-gtid-purged=OFF --user=opendental --host=192.168.41.3 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events --skip-triggers "opendental"
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `documentmisc` at row: 0

Operation failed with exitcode 3
Adding the option --max_allowed_packet=2G helped. While the default option was set to 1G, it was not sufficient.
Is it normal for that table to generate such large packets?
Thank you

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: 'max_allowed_packet' value exceeded

Post by cmcgehee » Mon Mar 13, 2017 8:01 am

Based on this error message, you are exceeding the max_allowed_packet value when copying the contents of the Open Dental installation directory into the database. This happens during the update process. There are probably some large images or other files in the C:\Program Files\Open Dental folder on the computer you are updating from. Try deleting extraneous files from that folder on the computer that you usually update from. If you are unsure if a file is necessary, you can post its name of this thread. Also, what version of Open Dental are you on?
Chris McGehee
Open Dental Software
http://www.opendental.com

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: 'max_allowed_packet' value exceeded

Post by jsalmon » Mon Mar 13, 2017 12:38 pm

It was normal back in the day until we found out that a lot of users had their MySQL set up in such a way that couldn't handle such large packets. In newer versions of Open Dental we broke up the documentmisc packets into 1MB chunks. The large packet row is kept around for backwards compatibility purposes.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: 'max_allowed_packet' value exceeded

Post by jsalmon » Mon Mar 13, 2017 12:44 pm

If you're at a recent version of Open Dental and don't want the large packet in the table you could set the max_packet_allowed value to something lower (e.g. 40MB) and then click the Recopy button via the Update Setup window.
http://www.opendental.com/manual/updatefullversion.html

This will clear out the entire table and try to recreate the necessary rows in the table. If MySQL will not let the large payload row get created then it will simply skip this step and move on to the 1MB chunk rows.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: 'max_allowed_packet' value exceeded

Post by ForrestGumpDDS » Thu Apr 06, 2017 10:32 am

Thank you.
I was on 16.3.51 at that time.
The error I mentioned, has happened another time when importing. That required me to tweak the mySQL server's settings. The error looked less descriptive

Code: Select all

ERROR 2006 (HY000) at line 2874: MySQL server has gone away
I went ahead and look at the offending entries in the database.
Let me post the files, maybe there's a way to get rid of them entirely?
  • DocMiscNum DateCreated Filename DocMiscType RawBase64
  • 5 2016-05-17 UpdateFiles.zip 0 UEsDBBQ...
  • 189 2017-03-14 0019 1 i+J32w0SmV...
  • 188 2017-03-14 0018 1 MLQewFK...
  • 187 2017-03-14 0017 1 PXAivfwnRZ...
  • 186 2017-03-14 0016 1 VyFLRPQ1kt...
  • 185 2017-03-14 0015 1 qI4B2uWTm16O...
  • 184 2017-03-14 0014 1 wDr/+lmJ/pXjVvv...
  • 183 2017-03-14 0013 1 o6aH2Z0jQeN8...
  • 182 2017-03-14 0012 1 luWMXZC6kbESg...
  • 181 2017-03-14 0011 1 1zto4K9TRyDfk...
  • 180 2017-03-14 0010 1 wK+n4M9RkLCrI...
  • 179 2017-03-14 0009 1 fM4XfMlXfM03f...
  • 178 2017-03-14 0008 1 SFa/82xPiI4gD...
  • 177 2017-03-14 0007 1 9veUzcgePPSos1k...
  • 176 2017-03-14 0006 1 n8ljoKmDDAsFO...
  • 175 2017-03-14 0005 1 eX9mfGs4fBvn4Hx...
  • 174 2017-03-14 0004 1 8YMgCoJqkigDa...
  • 173 2017-03-14 0003 1 XXUJ4w8/lDe...
  • 172 2017-03-14 0002 1 +k3v49yKqeN7EU...
  • 171 2017-03-14 0001 1 UEsDBBQAAAAI...
Image
I was just wondering where do I find those files? Should I get rid of those?

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: 'max_allowed_packet' value exceeded

Post by ForrestGumpDDS » Thu Apr 06, 2017 11:11 am

There was another table (total 2 tables were giving me the error).
Image
Do I want to do anything with them, or just leave them alone?

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: 'max_allowed_packet' value exceeded

Post by jsalmon » Thu Apr 06, 2017 11:51 am

ForrestGumpDDS wrote:The error I mentioned, has happened another time when importing. That required me to tweak the mySQL server's settings. The error looked less descriptive
Importing does a INSERT INTO [new_table] SELECT * FROM [old_table] so I could see why that would be exceeding your limit. We would have to treat that as a bug in our import tool but I'm not sure how we'd solve that because a table could have only one row and that one row could potentially be larger than the max packet allowed.

Do you have a Linux box by chance? If so, it could be that your tables are defaulting to InnoDB and you need to increase your innodb_log_file_size variable as well.
ForrestGumpDDS wrote:There was another table (total 2 tables were giving me the error).
Image
Do I want to do anything with them, or just leave them alone?
The emailmessage table has a DBM tool called "Raw Emails" that might help this one:
http://www.opendental.com/manual/databa ... nance.html
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

ForrestGumpDDS
Posts: 177
Joined: Fri Oct 14, 2011 3:31 pm

Re: 'max_allowed_packet' value exceeded

Post by ForrestGumpDDS » Thu Apr 06, 2017 12:45 pm

jsalmon wrote:Importing does a INSERT INTO [new_table] SELECT * FROM [old_table] so I could see why that would be exceeding your limit. We would have to treat that as a bug in our import tool but I'm not sure how we'd solve that because a table could have only one row and that one row could potentially be larger than the max packet allowed.
Do you have a Linux box by chance? If so, it could be that your tables are defaulting to InnoDB and you need to increase your innodb_log_file_size variable as well.
Great tip, I'll look into that.
I was setting up a new server to replace the old one, and that was the first import into the newly created mysql table that came with CentOS 7 minimal. I'm sure you are correct about defaulting to InnoDB, Jason.
After that I upgraded OD to the latest version, and it was working fine.
I remember about RAW emails tool, but did not use it right before I exported the database for backup.

Everything is working now.

Post Reply