max_allowed_packet being ignored

JanKrohn

• January 01, 2019 17:58

Hi, In database backup of a WP site, I get the following error: LOCK TABLES `_aiowps_global_meta` WRITE; /*!40000 ALTER TABLE `_aiowps_global_meta` DISABLE KEYS */; mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' when dumping table `_aiowps_global_meta` at row: 0
Here is my full my.cnf: [mysqld] log-error=/var/lib/mysql/.err sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" local-infile = 0 performance-schema = 0 max_allowed_packet = 256M default-storage-engine = InnoDB table_open_cache=4485 max_connections = 999 max_user_connections = 999 thread_cache_size = 1000 query_cache_type = 1 query_cache_size = 32M sort_buffer_size = 2M tmp_table_size = 64M max_user_connections = 999 thread_cache_size = 1000 query_cache_type = 1 query_cache_size = 32M sort_buffer_size = 2M tmp_table_size = 64M read_buffer_size = 128k read_rnd_buffer_size = 256k join_buffer_size = 128k innodb_buffer_pool_size=134217728 innodb_file_per_table=1
According to "show variables", the configuration was accepted. +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | max_allowed_packet | 268435456 | | slave_max_allowed_packet | 1073741824 | +--------------------------+------------+ So I don't understand why the error message occurs. Any ideas? Thanks! Jan

• 

  JanKrohn

  • January 01, 2019 22:59

  By the way, the table has one record with a size of 29.5 MiB.

  0
• 

  cPanelLauren

  • January 04, 2019 16:44

  The default for max_allowed_packet is: +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | max_allowed_packet | 268435456 | | slave_max_allowed_packet | 1073741824 | +--------------------------+------------+
  Which translates to about 268.4 MB You're inputting 256MB as the max_allowed_packet which would translate to roughly 256120000 (give or take a few) bytes So it did not accept the manual value you've put in place the manual value is also smaller than the default value. I'd update it to a higher value then save the configuration and restart MySQL

  0
• 

  JanKrohn

  • January 04, 2019 18:05

  Thanks for the reply! Hm, I think I had posted the incorrect output last time, from one of my tries when I commented out the line in my.cnf. I've set the value to 512M, and the output is now as expected. +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | max_allowed_packet | 536870912 | | slave_max_allowed_packet | 1073741824 | +--------------------------+------------+ The database backup, however, still fails with the same message.

  0
• 

  cPanelLauren

  • January 04, 2019 18:10

  That makes more sense! What happens if you set this to 1GB?

  0
• 

  JanKrohn

  • January 04, 2019 18:22

  The config is accepted. +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | max_allowed_packet | 1073741824 | | slave_max_allowed_packet | 1073741824 | +--------------------------+------------+ The db backup still exists with the same message.

  0
• 

  cPanelLauren

  • January 04, 2019 18:59

  Can you please open a ticket using the link in my signature? Once open please reply with the Ticket ID here so that we can update this thread with the resolution once the ticket is resolved. Thanks!

  0
• 

  JanKrohn

  • January 04, 2019 21:20

  Thanks! Here's the ticket number: 11122469

  0
• 

  cPanelLauren

  • January 07, 2019 13:59

  Hi @JanKrohn I just checked in on that ticket and it looks like the analyst found that the max_allowed_packet needed to be added to the mysqldump portion of the my.cnf as it's not automatically changed for mysqldump. Once that modification was made the issue stopped occurring. Thanks!

  0
• 

  JanKrohn

  • January 08, 2019 18:21

  Confirmed. No more issues.

  0

Please sign in to leave a comment.