Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.27, 10.1.41, 10.3.18, 10.4.8
-
None
Description
mysqldump ignores some internal tables by default, such as:
- mysql.apply_status
- mysql.schema
- mysql.general_log
- mysql.slow_log
- mysql.transaction_registry
See here:
https://github.com/MariaDB/server/blob/mariadb-10.4.8/client/mysqldump.c#L1001
I think the following tables should also be ignored by default:
- mysql.innodb_index_stats
- mysql.innodb_table_stats
The reason is that if someone tries to import a dump created by mysqldump, then a race condition between the import and the InnoDB persistent statistics calculation exists, and the user can see errors like this:
Duplicate 'PRIMARY-n_diff_pfx01' for key 'primary' for database 'xxxx98'.
|
This was reported long ago in the following upstream bug report:
https://bugs.mysql.com/bug.php?id=71814
The workaround mentioned in that bug report is to disable InnoDB persistent statistics prior to the import:
SET GLOBAL innodb_stats_auto_recalc=0;
|
SET GLOBAL innodb_stats_persistent=0;
|
However, it would be more ideal to make this workaround unnecessary by fixing the problem.
Attachments
Issue Links
- relates to
-
MDEV-22037 Add ability to skip content of some tables (work around for MDEV-20939)
-
- Closed
-
- links to
Activity
Was there a reason the conditional on !(opt_ignore || opt_replace_into) wasn't acceptable?
rucha174 on your patch.
- As your adding to ignore_data unconditionally, leave the system=stats to add into ignore_table. Otherwise myqldump --system=stat mysql with have CREATE TABLES which isn't desired.
- use --skip-comments in your test otherwise you have a mysqldump server version (and debug/release) in the results file that isn't mergeable or even passes release and debug test versoins
- I do want the dump_all_stats and dump_all_timezones part of https://github.com/MariaDB/server/commit/0f35033de9a to resolve this issue with mysqldump --system=
{all,stats,timezones}
and the corresponding mysqldump-system test result change.
Should be covered:
man/mysqldump.1 update to reflect this change
at top of client/mysqldump.c - increment DUMP_VERSION
serg
Patch after latest fix: https://github.com/MariaDB/server/commit/96702de22923728b255293d4c38ab07a56deb782
Also, danblack has pushed fix about dump_all_stats() and dump_all_timezones() to 10.2 related to this issue.
With --insert-ignore (opt_ignore) or --replace (opt_replace_into) there isn't an issue with dumped sql.
Because these innodb tables are meant to support a persistent stats (https://mariadb.com/kb/en/innodb-persistent-statistics/) I think it should be still possible to dump them with --system=stats (or a normal dump if --replace or --insert-ignore) is specified.
In the proposal below I've changed the dump_all_stats, and dump_all_timezones (the two that use inserts directly), to use `REPLACE INTO` in the event --insert-ignore isn't specified in order to prevent PK/unique key conflicts. I've also changed to use the ignore_data table for the base case.
bb-10.2-danielblack-
MDEV-20939-innodb_stats_mysqldump_conflicts (https://github.com/MariaDB/server/commit/0f35033de9a)updated commit, includes test. Also unsure why general_log/slow_log aren't excluded in test.
Acceptable?
TODO man/mysqldump.1 updates