Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY''-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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 
1 row in set (0.00 sec) 

mysql> use antiy_bbs 
Database changed 
mysql> select count(*) from bbs_common_searchindex;  --从库看到还有2条记录,而主库是0 
+----------+ 
| count(*) | 
+----------+ 
|        2 | 
+----------+ 
1 row in set (0.00 sec) 

mysql> select * from bbs_common_searchindex; 
Empty set (0.00 sec) 

mysql> select count(*) from bbs_common_searchindex;   ---等待了一会之后,记录消失,同步恢复。 
+----------+ 
| count(*) | 
+----------+ 
|        0 | 
+----------+ 
1 row in set (0.00 sec) 

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: 49058519 
               Relay_Log_File: mysql-relay-bin.000007 
                Relay_Log_Pos: 10539748 
        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: 49058519 
              Relay_Log_Space: 49057429 
              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: 0 
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: Slave has read all relay log; waiting for the slave I/O thread to update it 
           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 
1 row in set (0.00 sec) 


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]

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: