innodb_index_stats导入备份数据时报错表主键冲突的解决方法_Mysql

innodb_index_stats导入备份数据时报错表主键冲突的解决方法

2020-07-21 16:41MYSQL教程网 Mysql

下面小编就为大家带来一篇innodb_index_stats导入备份数据时报错表主键冲突的解决方法。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧

故障描述

percona5.6，mysqldump全备份，导入备份数据时报错Duplicate entry 'hoc_log99-item_log_27-PRIMARY-n_diff_pfx01' for key 'PRIMARY'

故障原因

查看了下这个主键应该是MySQL系统库下的系统表innodb_index_stats

				?

									mysql> show create table innodb_index_stats\G

									*************************** 1. row ***************************

									    Table: innodb_index_stats

									Create Table: CREATE TABLE `innodb_index_stats` (

									 `database_name` varchar(64) COLLATE utf8_bin NOT NULL,

									 `table_name` varchar(64) COLLATE utf8_bin NOT NULL,

									 `index_name` varchar(64) COLLATE utf8_bin NOT NULL,

									 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

									 `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,

									 `stat_value` bigint(20) unsigned NOT NULL,

									 `sample_size` bigint(20) unsigned DEFAULT NULL,

									 `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,

									 PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)

									) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0

									1 row in set (0.00 sec)

									mysql> select * from innodb_index_stats where database_name='hoc_log99' and table_name='item_log_27' and stat_name='n_diff_pfx01' and index_name='PRIMARY';

									+---------------+-------------+------------+---------------------+--------------+------------+-------------+------------------+

									| database_name | table_name | index_name | last_update     | stat_name  | stat_value | sample_size | stat_description |

									+---------------+-------------+------------+---------------------+--------------+------------+-------------+------------------+

									| hoc_log99   | item_log_27 | PRIMARY  | 2016-10-07 18:44:06 | n_diff_pfx01 |   823672 |     20 | redid      |

									+---------------+-------------+------------+---------------------+--------------+------------+-------------+------------------+

									1 row in set (0.00 sec)

再查看下我当时的备份文件sql的记录，发现再导入这个表之前是会重建表的，排除了再导入这个表之前，有item_log_27 表的操作记录进了innodb_index_stats的可能。

				?

									-- Table structure for table `innodb_index_stats`

									DROP TABLE IF EXISTS `innodb_index_stats`;

									CREATE TABLE `innodb_index_stats` (

									-- Dumping data for table `innodb_index_stats`

									LOCK TABLES `innodb_index_stats` WRITE;

									/*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */;

于是我又查看了下最近的binlog记录，发现确实有重建这个表的操作

				?

									DROP TABLE IF EXISTS `innodb_index_stats` /* generated by server */

									CREATE TABLE `innodb_index_stats` (

									/*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */