下面小編就為大家?guī)硪黄猧nnodb_index_stats導(dǎo)入備份數(shù)據(jù)時報錯表主鍵沖突的解決方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
故障描述
percona5.6，mysqldump全備份，導(dǎo)入備份數(shù)據(jù)時報錯Duplicate entry ‘hoc_log99-item_log_27-PRIMARY-n_diff_pfx01’ for key ‘PRIMARY’
故障原因
查看了下這個主鍵應(yīng)該是MySQL系統(tǒng)庫下的系統(tǒng)表innodb_index_stats
mysql>?show?create?table?innodb_index_statsG ***************************?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)
再查看下我當(dāng)時的備份文件sql的記錄，發(fā)現(xiàn)再導(dǎo)入這個表之前是會重建表的，排除了再導(dǎo)入這個表之前，有item_log_27 表的操作記錄進了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?*/;
于是我又查看了下最近的binlog記錄，發(fā)現(xiàn)確實有重建這個表的操作
DROP?TABLE?IF?EXISTS?`innodb_index_stats`?/*?generated?by?server?*/ CREATE?TABLE?`innodb_index_stats`?( /*!40000?ALTER?TABLE?`innodb_index_stats`?DISABLE?KEYS?*/
結(jié)論
mysql 5.6的bug，也有其他同行遇到了一樣的錯誤
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
解決辦法
1 mysqldump添加參數(shù)忽略這個表的備份
2 將備份文件中的這個表的insert改為replace
3 mysql -f強制導(dǎo)入
【相關(guān)推薦】
1.?Mysql免費視頻教程
2.?Mysql免費視頻教程
3.?Mysql免費視頻教程
4.?Mysql免費視頻教程
5.?Mysql免費視頻教程
