同步故障Last_Errno:1061

xishuai236發表於2015-06-24
AST
1051處理完又出現好多1061的錯誤。
  1. mysql>show slave status\G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 10.128.45.8
  5.                   Master_User: slave
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000036
  9.           Read_Master_Log_Pos: 259947402
  10.                Relay_Log_File: mysqld-relay-bin.000039
  11.                 Relay_Log_Pos: 795208907
  12.         Relay_Master_Log_File: mysql-bin.000016
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: No
  15.                    Last_Errno: 1061
                       Last_Error: Error 'Duplicate key name 'Index_Feed_Record_Time'' on query. Default database: 'storefeedback500'. Query: 'ALTER TABLE feedback_record ADD INDEX  `Index_Feed_Record_Time`  USING BTREE(`feedback_time`)'
error log報錯如下：
  1. 2015-06-24 08:44:09 2804 [ERROR] Slave SQL: Error 'Duplicate key name 'Index_Feed_Record_person'' on query. Default database: 'storefeedback500'. Query: 'ALTER TABLE feedback_record ADD INDEX  `Index_Feed_Record_person`  USING BTREE(`feedback_person_id`)', Error_code: 1061
    2015-06-24 08:44:09 2804 [Warning] Slave: Duplicate key name 'Index_Feed_Record_person' Error_code: 1061
    2015-06-24 08:44:09 2804 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000016' position 795212720
    2015-06-24 08:48:32 2804 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
    2015-06-24 08:48:32 2804 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000016' at position 795212720, relay log './mysqld-relay-bin.000039' position: 795212883
從庫中已經存在相關索引，主庫再新增則報錯。原因還沒弄清楚。
檢視從庫
  1. mysql> show index from feedback_record;
  2. +--------------+---------+---------------+-----------+------------+----------+------------+---------+-------+------+---------+------+-----+
  3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  4. +--------------+---------+---------------+-----------+------------+----------+------------+---------+-------+------+---------+------+-----+
  5. | feedback_record | 0 | PRIMARY                  | 1 | id                 | A | 1424178 | NULL | NULL |     | BTREE | | |
  6. | feedback_record | 1 | FK_Reference_10          | 1 | feedback_id        | A | 41      | NULL | NULL | YES | BTREE | | |
  7. | feedback_record | 1 | Index_Feed_Record_person | 1 | feedback_person_id | A | 20      | NULL | NULL | YES | BTREE | | |
  8. | feedback_record | 1 | Index_Feed_Record_Time   | 1 | feedback_time      | A | 27388   | NULL | NULL |     | BTREE | | |
  9. +--------------+---------+---------------+-----------+------------+----------+------------+---------+-------+------+---------+------+-----+
可以手動刪除報錯的索引，解決如下：
  1. mysql> use storefeedback500
  2. mysql> drop index feedback_index_sponsor_id on feedback;
  3. mysql> start slave
參考：
http://blog.itpub.net/15456724/viewspace-682681/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26831783/viewspace-1710005/，如需轉載，請註明出處，否則將追究法律責任。

相關文章