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
Today, I encountered a problem: the content in the...
This article uses examples to illustrate the sear...
First, you need to download and install Navicat f...
Table of contents What is the Linux system that w...
1. Clarify the design direction <br />First,...
Let's make a simple 3D Rubik's Cube today...
1. Check Linux disk status df -lh The lsblk comma...
I encountered this problem when I was making the ...
Mongodb has a db.serverStatus() command, which ca...
The project scaffolding built with vue-cli has al...
Nginx's shared memory is one of the main reas...
In the horizontal direction, you can set the alig...
I used the label tag when I was doing something re...
MySQL green version setting code, and 1067 error ...
view What is a view? What is the role of a view? ...