Solution to primary key conflict when innodb_index_stats reports an error when importing backup data

Solution to primary key conflict when innodb_index_stats reports an error when importing backup data

Fault description

percona5.6, mysqldump full backup, error Duplicate entry 'hoc_log99-item_log_27-PRIMARY-n_diff_pfx01' for key 'PRIMARY' when importing backup data

Cause

Checked and found that this primary key should be the system table innodb_index_stats under the MySQL system library

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)

I checked the sql records in my backup file at that time and found that the table would be rebuilt before importing it. This ruled out the possibility that the operation records of the item_log_27 table were entered into innodb_index_stats before importing the table.

-- 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 */;

So I checked the recent binlog records again and found that there was indeed an operation to rebuild this table

DROP TABLE IF EXISTS `innodb_index_stats` /* generated by server */
CREATE TABLE `innodb_index_stats` (
/*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */

in conclusion

MySQL 5.6 bug, other colleagues have encountered the same error

https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/31971-mysql-innodb_index_stats-duplication-entry-error-on-restore

https://bugs.mysql.com/bug.PHP?id=71814

Solution

1 mysqldump adds parameters to ignore the backup of this table

2 Change the insert of this table in the backup file to replace

3 mysql -f forced import

The above article on how to solve the primary key conflict of the error table when innodb_index_stats imports backup data is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

<<:  JavaScript canvas implements moving the ball following the mouse

>>:  Ubuntu terminal multi-window split screen Terminator

Recommend

How to use multi-core CPU to speed up your Linux commands (GNU Parallel)

Have you ever had the need to compute a very larg...

SQL implementation LeetCode (176. Second highest salary)

[LeetCode] 176. Second Highest Salary Write a SQL...

Navicat connection MySQL error description analysis

Table of contents environment Virtual Machine Ver...

Markup Language - Image Replacement

Click here to return to the 123WORDPRESS.COM HTML ...

JavaScript Function Currying

Table of contents 1 What is function currying? 2 ...

Vue project packaging and optimization implementation steps

Table of contents Packaging, launching and optimi...

Creative opening effect achieved by combining CSS 3.0 with video

Let me share with you a creative opening realized...

HTML table tag tutorial (7): background color attribute BGCOLOR

The background color of the table can be set thro...

Avoid abusing this to read data in data in Vue

Table of contents Preface 1. The process of using...

Record the whole process of MySQL master-slave configuration based on Linux

mysql master-slave configuration 1. Preparation H...

Detailed explanation of MySQL master-slave inconsistency and solutions

1. MySQL master-slave asynchrony 1.1 Network Dela...

Use pure CSS to create a pulsating loader effect source code

Effect Preview Press the "Click to Preview&q...