Detailed explanation of the error prompting table primary key conflict when innodb_index

Detailed explanation of the error prompting table primary key conflict when innodb_index_stats imports data

Y2J

Release： 2017-05-24 13:40:43

Original

2819 people have browsed it

The following editor will bring you an article on how to solve the problem of table primary key conflict when innodb_index_stats imports backup data. The editor thinks it’s pretty good, so I’ll share it with you now and give it as a reference. Let's follow the editor to take a look.

Fault description

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

Cause of failure

After checking, this primary key should be under the MySQL system library The system table innodb_index_stats

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=&#39;hoc_log99&#39; and table_name=&#39;item_log_27&#39; and stat_name=&#39;n_diff_pfx01&#39; and index_name=&#39;PRIMARY&#39;;
+---------------+-------------+------------+---------------------+--------------+------------+-------------+------------------+
| 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)

Copy after login

then checked the records of my backup file sql at that time, and found that the table will be rebuilt before importing this table again, which excludes the operation records of the item_log_27 table before importing this table. Possibility of innodb_index_stats.

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

Copy after login

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

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

Copy after login

Conclusion

##Mysql 5.6 bug, other colleagues have also encountered the same error

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

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

[Related recommendations]

Mysql Free Video Tutorial

Detailed examples of adding new user permissions in MySQL

Detailed examples of changing passwords and access restrictions in MySQL

Details of examples of using regular expressions to replace content in the database Solution

Detailed explanation of examples of php storing images in mysql

The above is the detailed content of Detailed explanation of the error prompting table primary key conflict when innodb_index_stats imports data. For more information, please follow other related articles on the PHP Chinese website!