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
Have you ever had the need to compute a very larg...
[LeetCode] 176. Second Highest Salary Write a SQL...
Table of contents environment Virtual Machine Ver...
Docker provides multiple networks such as bridge,...
Click here to return to the 123WORDPRESS.COM HTML ...
Table of contents 1 What is function currying? 2 ...
Table of contents 1. Retrieve via --skip-grant-ta...
After installing VMware Tools, ① text can be copi...
Table of contents Packaging, launching and optimi...
Let me share with you a creative opening realized...
The background color of the table can be set thro...
Table of contents Preface 1. The process of using...
mysql master-slave configuration 1. Preparation H...
1. MySQL master-slave asynchrony 1.1 Network Dela...
Effect Preview Press the "Click to Preview&q...