[MDEV-12092] mysql.gtid_slave_pos should be declared with STATS_PERSISTENT=0 Created: 2017-02-20  Updated: 2018-09-10

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.1
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: Marko Mäkelä Assignee: Andrei Elkin
Resolution: Unresolved Votes: 0
Labels: bootstrap, replication, statistics


 Description   

A user on IRC reported getting the following error in when trying to replicate all databases from one host to another (10.1.17 to 10.1.20):

ERROR 1062 (23000) at line 839574: Duplicate entry 'mysql-gtid_slave_pos-PRIMARY-n_diff_pfx01' for key 'PRIMARY'

The message is clearly originated from InnoDB persistent statistics.

I think that scripts/mysql_system_tables.sql should specify STATS_PERSISTENT=0 for all small tables when they are defined with ENGINE=InnoDB, and especially for all tables that have something to do with GTID or replication state.

 Comments   
Comment by Marko Mäkelä [ 2017-02-20 ]

Note: MySQL 5.7 can create the table mysql.gtid_executed in ENGINE=InnoDB without specifying STATS_PERSISTENT=0.
Maybe there could be a real issue with the MariaDB implementation that setting STATS_PERSISTENT=0 would merely be hiding.

Comment by Elena Stepanova [ 2017-02-24 ]

I have no objections regarding creating the system table with the option, although I would very much prefer the user's problem to be investigated, as it probably might happen to any table. However, if we don't have any other information about the circumstances of the failure, probably there isn't much we can do.

Comment by Marko Mäkelä [ 2018-09-10 ]

Elkin, could the solution simply be that replication should skip the InnoDB persistent statistics tables?
The tables mysql.innodb_index_stats and mysql.innodb_table_stats are special, because they are often written directly by InnoDB at unpredictable intervals, without holding any metadata lock (MDL) or writing any binlog.

Could the issue with restoring a logical backup be similar? Perhaps when the table mysql.gtid_slave_pos was created, InnoDB would have already inserted entries for it in the statistics tables, and then it would report a duplicate key error on an INSERT operation to the statistics tables.

Comment by Andrei Elkin [ 2018-09-10 ]

marko > could the solution simply be that replication should skip the InnoDB persistent statistics tables?

Indeed. We don't do this to P_S tables, and its pattern clearly applies.

Generated at Thu Feb 08 07:55:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.