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

Navicat for MySQL tutorial

First, you need to download and install Navicat f...

Detailed explanation of the relationship between Linux and GNU systems

Table of contents What is the Linux system that w...

Web page experience: planning and design

1. Clarify the design direction <br />First,...

Example code for implementing 3D Rubik's Cube with CSS

Let's make a simple 3D Rubik's Cube today...

Introduction to root directory expansion under Linux system

1. Check Linux disk status df -lh The lsblk comma...

Zabbix3.4 method to monitor mongodb database status

Mongodb has a db.serverStatus() command, which ca...

Detailed explanation of nginx shared memory mechanism

Nginx's shared memory is one of the main reas...

HTML table tag tutorial (11): horizontal alignment attribute ALIGN

In the horizontal direction, you can set the alig...

Analysis and solutions to problems encountered in the use of label tags

I used the label tag when I was doing something re...

MySQL green version setting code and 1067 error details

MySQL green version setting code, and 1067 error ...

A brief discussion on MySql views, triggers and stored procedures

view What is a view? What is the role of a view? ...