MySQL Bugs: #109108: mysqldump creating packets too large for restore in 5.7 and 8.0. 5.6 works

Bug #109108	mysqldump creating packets too large for restore in 5.7 and 8.0. 5.6 works
Submitted:	16 Nov 2022 9:20	Modified:	27 Nov 2022 0:57
Reporter:	Doug C	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.7 and 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have found a problem when dumping a table with a large blob object starting with MySQL 5.7 and still active in MySQL 8.0.

I am in the process of migrating from MySQL 5.6 to MySQL 8.0, and as part of that project I have a bridge slave/2nd master server running MySQL 5.7.

As part of this testing I periodically create new dumps from my slave servers to import into MySQL 8.0.   When dumping from a 5.6 mysqldump, I had no problem restoring the dump.   However when I created a the 5.7 bridge server and started using it for my dumps I ran into an issue with a table that has an element close to the size of max_allowed_packet/

How to repeat:
Table structure is as follows:

CREATE TABLE `adv_psd` (
  `aid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `auid` bigint(20) unsigned NOT NULL,
  `psd` longblob,
  UNIQUE KEY `aid` (`aid`),
  KEY `auid` (`auid`),
  KEY `psdlib` (`aid`,`auid`,`psd`(1))
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The problem row's relevant statistics:
+------------------+
| max(length(psd)) |
+------------------+
|         32188364 |
+------------------+

There are a variety of other row entries in this table mostly in MB sizes.  Smallest is as follows:

+------------------+
| min(length(psd)) |
+------------------+
|            32719 |
+------------------+

All servers configured with this limiting [mysqld] variable:

max_allowed_packet = 32M

which translates to this size:
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 33554432 |
+--------------------+----------+

The my.cnf has the same mysqldump settings in MySQL 5.6 and MySQL 5.7.   MySQL 8.0 has slightly different settings:

5.6.47 & 5.7
[mysqldump]
quick
max_allowed_packet = 32M

8.0.31
[mysqldump]
quick
max_allowed_packet = 32M
column-statistics=0

Also tried this in 5.7 as a possible fix, but no luck:
max_allowed_packet = 32767K

Dump runs fine using MySQL 5.6, 5.7 and 8.0.   MYSQLD is 5.7 in most of my trials, but I think I also tried dumping from 5.6 MYSQLD with the 5.7 client to the same effect.  Dumped results from 5.7 and 8.0 client both fail to reload.  

Dumping with MySQL 5.6 client works against 5.6 and 5.7 MYSQLD and reloads in all MYSQLD versions.

Dumping with 5.7 and 8.0 client causes this error:

ERROR 1153 (08S01) at line 33152: Got a packet bigger than 'max_allowed_packet' bytes

And this INSERT line in the dump file includes multiple rows and is larger than 32M.

Dump command line is the same across all clients:

mysqldump -u root -p --no-create-info --databases  xxx

mysqldump --help for all clients included in attached files.

mysqldump 8.0 --help

Attachment: 8.0.txt (text/plain), 23.64 KiB.

Forgot to mention, same error occurred when dumping only this table.   The table is large so I hope you can generate test blobs given only the sizes/description mentioned above.

Hi Mr. C,

Thank you for your bug report.

However, it is not a bug .....

We have analysed your uploaded files and concluded that you have to change defaults for your servers. Do note that default settings for CHAR/VARCHAR/TEXT and some other settings have changed from 5.7 to 8.0. Version 5.6 is not maintained any longer, so we do not accept reports on that version.

You definitely have to increase max_allowed_packet on all of your 5.7 and 8.0 servers.

Not a bug.

Respectfully, I believe this is an issue with the mysqldump not honoring the parameter max-allowed-packet.  From mysqldump help:

  --max-allowed-packet=#
                      The maximum packet length to send to or receive from
                      server.

Both the mysql server and mysqldump client are configured with max-allowed-packet=32M

From mysqldump --help:
max-allowed-packet 33554432

Dumping from a MysQL 5.7 server with mysqldump 5.7 or 8.0 produces packets that are larger than max-allowed-packet (the bug) even though both the server and mysqldump are configured with a lower limit.  I mention mysqldump 5.6 only because this is the only mysqldump version that is honoring the configured limit correctly.

The large row in the database does not exceed the 32M limit by itself, but mysqldump 5.7 is not breaking the INSERTs properly so that the combined INSERT does not exceed the configured limit which is what caused the error when trying to restore the dumped data.  I can probably work around this by forcing one row per INSERT using --skip-extended-insert, but this will result in huge dumps.

I can create a dump of the table and extract the row to show you what is happening.  This is a large dump, so trying to avoid uploading the entire table.

Hi,

There are entities for whom 32 MBytes is too low a value for that variable.

Ok, I did more detailed research on this issue and was able to successfully dump and reload the table using the 8.0 mysqldump client by using the following command:

mysqldump --default-character-set=latin1 

The issue I encountered appears to be related to changes in the default character set used for output.   The documentation here:

https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html

still indicates that latin1 is the default output format:

--------
The mysql, mysqladmin, mysqlcheck, mysqlimport, and mysqlshow client programs determine the default character set to use as follows:

    In the absence of other information, each client uses the compiled-in default character set, usually latin1. "
--------

However, it appears that mysqldump is using utf8mb4 as the default for all dumps instead of latin1 which made the dump output exceed the max allowed packet.  I think I saw something to this effect in the 8.0 release notes, so maybe this documentation needs to be updated to match.

Going forward I will add that variable to [mysqldump] in the my.cnf file.  From the documentation, it also looks like the same variable should to be added under [mysql] to make sure the C client API uses the latin1 default as well so it doesn't break large inserts/updates that worked previously.

Thanks.

Not sure if this was/is a bug, but did some testing in 5.6, and looks like mysqldump in 5.6 defaults to utf8 which maps to ut8mb3 I believe, which produced a packet too large when I tested it in 8.0.  Which means something changed the character set in 5.6 even though latin1 was not specified?

Should mysqldump be using the character set of the table being dumped?

Looks like it did in mysql 5.6.

No, this is not a bug ......

Simply, defaults changed from version to version .....