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

MySQL 8.0.11 MacOS 10.13 installation and configuration method graphic tutorial

The process of installing MySQL database and conf...

How to install pyenv under Linux

Prerequisites Need to install git Installation St...

Draw a heart with CSS3

Achieve resultsRequirements/Functionality: How to...

A brief discussion on whether MySQL can have a function similar to Oracle's nvl

Use ifnull instead of isnull isnull is used to de...

Solve the problem of ugly blue border after adding hyperlink to html image img

HTML img produces an ugly blue border after addin...

Font references and transition effects outside the system

Copy code The code is as follows: <span style=...

WeChat applet calculator example

This article shares the specific code of the WeCh...

How to automatically back up the mysql database regularly

We all know that data is priceless. If we don’t b...

Detailed explanation of Javascript basics loop

Table of contents cycle for for-in for-of while d...

Solutions to Mysql index performance optimization problems

The optimization created by MySQL is to add index...

JavaScript to achieve balance digital scrolling effect

Table of contents 1. Implementation Background 2....

Use pure CSS to disable the a tag in HTML without JavaScript

In fact, this problem has already popped up when I...

WeChat applet to obtain mobile phone number step record

Preface Recently, I encountered such a problem wh...