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]

时间： 2024-10-24 03:03:39

Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY''的相关文章

mysq主从报错Last_SQL_Error: Error 'Duplicate entry

报错信息如下: Last_SQL_Error: Error 'Duplicate entry '2738079' for key 'PRIMARY'' on query. Default database: 'whsir'. Query: 'INSERT INTO dede_uploads(title,url,mediatype,width,height,playtime,filesize,uptime,mid) VALUES ('小图361_170525114411_1_lit.png','/

mysql #1062 –Duplicate entry '1' for key 'PRIMARY'_Mysql

近日一直在折腾vps ,刚刚碰到在搬移wordpress过程中导入数据库的时候.碰到了 #1062 – Duplicate entry '1′ for key 'PRIMARY' 当时那个急啊,原本的数据我已经全部删除了,没办法只有请求万能的百度了.我找了大半天终于给我给我找到了.兴奋ing,马上测试,O(∩_∩)O哈哈~成功了. 现在附上解决办法只要把原来的老数据清空导入就可以了. 原理我不明白,贴上来你们自己看吧.反正达到目的就ok了. "提示#1062 – Duplicate entry

mysql 出现Error: Duplicate entry “xxxx” for key 1解决办法

所出错形式如下: Discuz! info: MySQL Query Error User: 爱我荆门 Time: 2007-3-5 11:00am Script: /bbs/post.php SQL: INSERT INTO cd12sb_threads (fid, readperm, price, iconid, typeid, author, authorid, subject, dateline, lastpost, lastposter, displayorder, digest, b

mysql报错#1062 Duplicate entry ‘2147483647’ for key ‘PRIMARY’

实际上本次故障的素材来自于朋友的朋友,虽然我并不是故障的亲身经历者,但即便只是作为旁观者,依然感觉有所收获,于是乎记录下来以馈读者. 故障的来龙去脉大致是这样的:在一个月黑风高的晚上,苦逼的程序员被一阵急促的报警短信声惊醒,原来是数据库的某个表出问题了,虽然查询操作都正常,但创建操作却都失败了,经过调试,发现原因是表被插入了一行问题数据,其自增字段的值被显式的设置为整型的最大值,导致后续缺省插入的数据不能获取到一个合法的主键值. 我们不妨创建一个测试表说明问题: CREATE TABLE IF

1062 - Duplicate entry 33 for key 1

我们有时在导入数据时会出来如1062 - Duplicate entry '33' for key 1的问题是吧, 那这是什么原因呢,昨天我在导入数据时发现这样的问题. 经过分析原因是,我要导入的数据ID在数据表中己经存在了, 既然知道原因了,解决办法就由你自己定了.

mysql主从复制错误：Last_SQL_Error: Error 'Duplicate entry '327' for key 'PRIMARY'' on query. Default database: 'xxx'. Query:

这个算不算解决,我都不太清楚,因为我感觉网上的说法,只是把错误忽略了,不表示以后用从库时不会出问题!!! 解决的办法是在从库上执行: mysql> slave stop; mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> slave start; 上面的方法可以解决问题,还有一种解决问题的办法是通过修改mysql的配置文件,让从库的同步线程忽略这个错误,方法: 修改mysql配置文件 /etc/my.cnf 在 [mysqld]下加一行

Duplicate entry '0' for key 'PRIMARY'

今天在测试程序的时候,发现一个问题,就是在给用户添加图片的时候,虽然图片已经被上传到服务器的发布目录中,但是eclipse控制台报错显示:Duplicate entry '0' for key 'PRIMARY' 的错误,而且这个错误属于MySQLIntegrityConstraintViolationException,后来经过思考,发现在设计相关表的时候,id的默认被设为了0,详情看图,改正后,测试一切顺利!(详情也可以看图)

Last_Errno: 1062，Last_Error: Error Duplicate entry_Mysql

线上环境我从来没有碰到过1062的问题,测试环境开发环境不停的出现类似问题,以往为了赶时间都是skip或者直接重新做,这会有时间,就好好去查查问题所在 1 从库报错信息: mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event             

mysql error:#1062 Duplicate entry ‘***′ for key 1问题解决方法_Mysql

我将id的int类型改成了bigint就可以了,其实再改回来可能也会好了.可能是数据库备份的时候出现了错误. 开发的网站后台系统在测试过程中出现了这个问题: Invalid Query : Duplicate entry '127′ for key 1 SQL is : INSERT INTO `kq_news` (`Title`,`Author`,`Type`,`Content`,`IsDel`,`Adate`,`Range`,`Lang`) values ('捐款活动','yuanying'