Bug #101332 mysqldump from 8.0 to 5.7 does not work due to trying to read COLUMN_STATISTICS
Submitted: 27 Oct 2020 7:35 Modified: 27 Oct 2020 10:27
Reporter: Simon Mudd (OCA) Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:8.0.21 OS:Any
Assigned to: CPU Architecture:Any

[27 Oct 2020 7:35] Simon Mudd
Running mysqldump (8.0.21) against a MySQL 5.7 server gives this error:

mysqldump --user=some_user --password=some_password --host=some-host --databases some-database --single-transaction --set-gtid-purged=OFF

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'some-database' AND TABLE_NAME = 'some-table';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

This appears to be because I_S.COLUMN_STATISTICS is only present on 8.0 and not earlier versions.

I see there is a --column-statistics to generate column statistics on the server, but this is disabled, but even disabling this option does not seem to prevent trying to read from this table on the server being dumped even if the information is unlikely to be used.

This seems to break cross-version dump abilities.

How to repeat:
See above.

Seen on mysqldump from mysql-community-client-8.0.21-1.el8.x86_64

- I may be missing some option to resolve this but do not see one.
- using --force works around this but --force is just that brute-force and should not really be needed for a dump from the previous major version I think.

Suggested fix:
I think that the mysqldump code should:
* be aware of the mysql server version it is talking to and only query this information if present (and needed).
* not query the information if you are going to generate the data when loading the dump, the option implied by --column-statistics
* perhaps provide an explicit --no-column-statistics option to force mysqldump to not attempt to read the statistics from the server

Also improve documentation:
* related to the fact this is required
* to grants that may be required to read this table
* to show how a 5.7 to 8.0 dump can be done "safely" and mention any incompatibilities that users should be aware of.
[27 Oct 2020 7:43] MySQL Verification Team
Hello Simon,

Thank you for the report and feedback.
Imho this is duplicate of Bug #89825.

[27 Oct 2020 7:44] MySQL Verification Team
Also, Bug #100686
[27 Oct 2020 10:27] Simon Mudd
Sorry for the duplicate.

I'll add my suggested fixes to the original bug report so they're in one place.