Not the answer you need?
Register and ask your own question!

mysql.innodb_index_stats "Duplication Entry" error on restore

chrschchrsch EntrantInactive User Role Beginner
Hej there,

since MySQL 5.6 there are at least two new tables located in the mysql database:

- innodb_index_stats
- innodb_table_stats

Now my problem. I tried to recover from a full database dump, including the mysql database. All databases were created and rows inserted successfully, except the mysql database.

At this point i always get:

ERROR 1062 (23000) at line 9476752: Duplicate entry 'XXXXXXXXXXXX-PRIMARY-n_diff_pfx01' for key

I think i know why this is happening. And I fixed this issue by editing the dump file and changed “insert into” to “replace into”. But I don’t know how to act right to avoid further problems like this.

I’d think the following way is a good solution. What do you think? Is there an alternative?

on dump ignore `mysql.innodb_index_stats' and `mysql.innodb_table_stats'
and an replication also ignore these tables

What is best practice for this matter?

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.