Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY''

[size=small]-實際遇到的問題： 

last_sql_error: error 'duplicate entry '1' for key 'primary'' on query. default database: 'antiy_bbs'. 

query: 'insert into bbs_common_searchindex set `srchmod`='2' , `keywords`='apk版本' , `searchstring`='forum|title|yxbr54mi5pys|0||\\\'65\\\',\\\'55\\\',\\\'70\\\',\\\ 

'69\\\',\\\'71\\\',\\\'72\\\',\\\'73\\\',\\\'74\\\',\\\'2\\\',\\\'48\\\',\\\'38\\\',\\\'67\\\',\\\'47\\\',\\\'79\\\',\\\'56\\\',\\\'58\\\',\\\'59\\\',\\\'66\\\',\\\' 

51\\\',\\\'60\\\',\\\'53\\\',\\\'68\\\',\\\'77\\\',\\\'75\\\',\\\'76\\\',\\\'78\\\',\\\'63\\\'|0|0|all|||0' , `useip`='192.168.10.241' , `uid`='54' , 

`dateline`='1420450904' , `expiration`='1420454504' , `num`='0' , `ids`='0'' 

從報錯來看，應該是插入的值違反了主鍵的限制，但看表結構，主鍵是自增類型，就不曉得哪裡出了問題 

主庫可看到插入了兩條記錄，後做了truncate 

[root@newmysql5 mysql]# mysqlbinlog mysql-bin.000008|grep bbs_common_searchindex 

insert into bbs_common_searchindex set `srchmod`='2' , `keywords`='apk版本' , `searchstring`='forum|title|yxbr54mi5pys|0||\\\'65\\\',\\\'55\\\',\\\'70\\\',\\\'69\\\',\\\'71\\\',\\\'72\\\',\\\'73\\\',\\\'74\\\',\\\'2\\\',\\\'48\\\',\\\'38\\\',\\\'67\\\',\\\'47\\\',\\\'79\\\',\\\'56\\\',\\\'58\\\',\\\'59\\\',\\\'66\\\',\\\'51\\\',\\\'60\\\',\\\'53\\\',\\\'68\\\',\\\'77\\\',\\\'75\\\',\\\'76\\\',\\\'78\\\',\\\'63\\\'|0|0|all|||0' , `useip`='192.168.10.241' , `uid`='54' , `dateline`='1420450904' , `expiration`='1420454504' , `num`='0' , `ids`='0' 

insert into bbs_common_searchindex set `srchmod`='2' , `keywords`='liqintao' , `searchstring`='forum|title|bglxaw50yw8=|0||\\\'65\\\',\\\'55\\\',\\\'70\\\',\\\'69\\\',\\\'71\\\',\\\'72\\\',\\\'73\\\',\\\'74\\\',\\\'2\\\',\\\'48\\\',\\\'38\\\',\\\'67\\\',\\\'47\\\',\\\'79\\\',\\\'56\\\',\\\'58\\\',\\\'59\\\',\\\'66\\\',\\\'51\\\',\\\'60\\\',\\\'53\\\',\\\'68\\\',\\\'77\\\',\\\'75\\\',\\\'76\\\',\\\'78\\\',\\\'63\\\'|0|0|all|||0' , `useip`='192.168.10.241' , `uid`='54' , `dateline`='1420451052' , `expiration`='1420454652' , `num`='0' , `ids`='0' 

truncate bbs_common_searchindex 

而從庫隻看到insert into，而沒有truncate，是以查詢記錄還有2條，應該是插入的時候卡住了就沒往下走。 

mysql> show create table bbs_common_searchindex; 

+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 

| table                  | create table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | 

| bbs_common_searchindex | create table `bbs_common_searchindex` ( 

  `searchid` int(10) unsigned not null auto_increment, 

  `srchmod` tinyint(3) unsigned not null, 

  `keywords` varchar(255) not null default '', 

  `searchstring` text not null, 

  `useip` varchar(15) not null default '', 

  `uid` mediumint(10) unsigned not null default '0', 

  `dateline` int(10) unsigned not null default '0', 

  `expiration` int(10) unsigned not null default '0', 

  `threadsortid` smallint(6) unsigned not null default '0', 

  `num` smallint(6) unsigned not null default '0', 

  `ids` text not null, 

  primary key (`searchid`), 

  key `srchmod` (`srchmod`) using btree 

) engine=myisam default charset=utf8 | 

1 row in set (0.00 sec) 

mysql> show index from bbs_common_searchindex; 

+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 

| table                  | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment | 

| bbs_common_searchindex |          0 | primary  |            1 | searchid    | a         |           0 |     null | null   |      | btree      |         |               | 

| bbs_common_searchindex |          1 | srchmod  |            1 | srchmod     | a         |        null |     null | null   |      | btree      |         |               | 

2 rows in set (0.00 sec) 

用網上介紹的方法跳過之後， 

stop slave; 

set global sql_slave_skip_counter=1; 

start slave; 

mysql> show slave status\g 

*************************** 1. row *************************** 

               slave_io_state: waiting for master to send event 

                  master_host: 192.168.12.101 

                  master_user: repli 

                  master_port: 3306 

                connect_retry: 60 

              master_log_file: mysql-bin.000008 

          read_master_log_pos: 48320168 

               relay_log_file: mysql-relay-bin.000006 

                relay_log_pos: 4840169 

        relay_master_log_file: mysql-bin.000008 

             slave_io_running: yes 

            slave_sql_running: yes 

              replicate_do_db: 

          replicate_ignore_db: 

           replicate_do_table: 

       replicate_ignore_table: 

      replicate_wild_do_table: 

  replicate_wild_ignore_table: 

                   last_errno: 0 

                   last_error: 

                 skip_counter: 0 

          exec_master_log_pos: 4841595 

              relay_log_space: 48321003 

              until_condition: none 

               until_log_file: 

                until_log_pos: 0 

           master_ssl_allowed: no 

           master_ssl_ca_file: 

           master_ssl_ca_path: 

              master_ssl_cert: 

            master_ssl_cipher: 

               master_ssl_key: 

        seconds_behind_master: 34552   ---這裡卻在持續增大 

master_ssl_verify_server_cert: no 

                last_io_errno: 0 

                last_io_error: 

               last_sql_errno: 0 

               last_sql_error: 

  replicate_ignore_server_ids: 

             master_server_id: 100 

                  master_uuid: 14bfd13a-1920-11e4-ac07-000c296c30fd 

             master_info_file: /var/lib/mysql/master.info 

                    sql_delay: 0 

          sql_remaining_delay: null 

      slave_sql_running_state: altering table   ---顯示正在變更表 

           master_retry_count: 86400 

                  master_bind: 

      last_io_error_timestamp: 

     last_sql_error_timestamp: 

               master_ssl_crl: 

           master_ssl_crlpath: 

           retrieved_gtid_set: 

            executed_gtid_set: 

                auto_position: 0 

mysql> use antiy_bbs 

database changed 

mysql> select count(*) from bbs_common_searchindex;  --從庫看到還有2條記錄，而主庫是0 

+----------+ 

| count(*) | 

|        2 | 

mysql> select * from bbs_common_searchindex; 

empty set (0.00 sec) 

mysql> select count(*) from bbs_common_searchindex;   ---等待了一會之後，記錄消失，同步恢複。 

|        0 | 

          read_master_log_pos: 49058519 

               relay_log_file: mysql-relay-bin.000007 

                relay_log_pos: 10539748 

          exec_master_log_pos: 49058519 

              relay_log_space: 49057429 

        seconds_behind_master: 0 

      slave_sql_running_state: slave has read all relay log; waiting for the slave i/o thread to update it 

mysql> show variables like 'sql_slave_skip_counter';  --退出會話後，自動變回原來的0，是以不用修改。 

+------------------------+-------+ 

| variable_name          | value | 

| sql_slave_skip_counter | 0     | 

1 row in set (0.00 sec)[/size]