深入探寻mysql自增列导致主键重复问题的原因,mysql主键
废话少说,进入正题。
拿到问题后,首先查看现场,发现问题表的中记录的最大值比自增列的值要大,那么很明显,当有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错。首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常。问题是解决了,接下来要搞清楚问题原因,什么操作导致了这种现象的发生呢?
这里有一种可能,即业务逻辑包含更新自增主键的代码,由于mysql的update动作不会同时更新自增列值,若更新主键值比自增列大,也会导致上述现象:记录最大值比自增主键值大。但开发反馈说这张表仅仅存在load data infile操作,不会进行更新主键操作,所以这个解释行不通。继续分析,表中含有唯一约束,会不会和唯一约束有关,线下实验模拟没有重现。后来想想会不会和主备切换有关系,因为前两天做过一次主备切换。于是乎,配合主备环境作了测试,果然和主备切换有关系,一切问题的来源都清晰了。
问题发生的前置条件:
1.mysql复制基于row模式
2.innodb表
3.表含有自增主键,并且含有唯一约束
4.load data infile 采用replace into语法插入数据【遇到重复唯一约束,直接覆盖】
问题发生的原理:
1.主库遇到重复unique约束时,进行replace操作;
2.replace在主库上面实际变化为delete+insert,但binlog记录的是update;
3.备库重做update动作,更新主键,但由于update动作不会更新自增列值,导致更新后记录值大于自增列值
问题重现实验:
准备工作 |
Create table test_autoinc(id int auto_increment, c1 int,c2 varchar(100),primary key(id),unique key(c1)); insert into test_autoinc(c1,c2) values(1,'abc'); insert into test_autoinc(c1,c2) values(2,'abc'); insert into test_autoinc(c1,c2) values(3,'abcdd'); insert into test_autoinc(c1,c2) values(4,'abcdd'); insert into test_autoinc(c1,c2) values(5,'abcdd'); |
|||
1 |
操作 |
备注 |
Master |
slave |
2 |
查看自增列值 Show create table test_autoinc\G |
插入5条记录后,自增列值变为6 |
CREATE TABLE `test_autoinc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c1` (`c1`) ) ENGINE=InnoDBAUTO_INCREMENT=6DEFAULT CHARSET=utf8 |
CREATE TABLE `test_autoinc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c1` (`c1`) ) ENGINE=InnoDBAUTO_INCREMENT=6DEFAULT CHARSET=utf8
|
3 |
查看表数据 |
|
id | c1 | c2 ---+------+------ 1 | 1 | abc 2 | 2 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd |
id | c1 | c2 ---+------+------ 1 | 1 | abc 2 | 2 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd |
4 |
查看binlog位置 show master status\G |
记录当前binlog位点, 后续可以查看replace动作产生的binlog事件 |
mysql-bin.000038 59242888 |
|
5 |
replace操作 replace into test_autoinc(c1,c2) values(2,'eeee'); |
影响两条记录,主库replace= delete+insert |
Query OK, 2 rows affected (0.00 sec) |
|
6 |
查看表数据 |
|
id | c1 | c2 ---+------+------- 1 | 1 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd 6 | 2 | eeee |
id | c1 | c2 ---+------+------- 1 | 1 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd 6 | 2 | eeee |
7 |
查看binlog事件 show binlog events in 'mysql-bin.000038' from 59242888; |
也可以通过mysqlbinlog工具分析日志,查询从库执行的update语句 |
Pos | Event_type ---------+--------------- 59242888 | Query 59242957 | Table_map 59243013 |Update_rows_v1 59243072 | Xid |
|
8 |
查看自增列值 Show create table |
此时master的自增列为7,而slave的自增列为6,与表内最大值相同 |
CREATE TABLE `test_autoinc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c1` (`c1`) ) ENGINE=InnoDBAUTO_INCREMENT=7 |
CREATE TABLE `test_autoinc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c1` (`c1`) ) ENGINE=InnoDBAUTO_INCREMENT=6 |
经过第8步操作后,若发生主备切换,slave提供服务,此时通过自增列插入主键6的记录,就会发生主键冲突。
如何解决这个bug?对于replace操作,生成binlog时也生成delete和insert两个事件而非一个update事件;或者在执行update更新主键的同时也更新自增列值。当然了,这个只是纯原理分析,具体采用什么方法解这个问题,要根据mysql内部的实现,避免引入新的问题。这个bug我同事已经提交到社区,http://bugs.mysql.com/73563 ,大家可以看看。
数据库修改mysql的字符集:在安装mysql5.0时可以设置好mysql的字符集,一般使用utf8的字符集1查看建立表“tablename”的sql语句Show create table tablename;//查看建立表“tablename”的sql语句| maos_mail_batch | CREATE TABLE `maos_mail_batch` (`ID` varchar(32) character set latin1 NOT NULL,`BATCH_TIME` varchar(30) character set latin1 NOT NULL,`BATCH_INTRODUCE` varchar(2000) character set latin1 default NULL,`BATCH_SUM` int(11) default NULL,`BATCH_STATE` varchar(1) character set latin1 default NULL,PRIMARY KEY (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 |2修改数据库和表的字符集alter database maildb default character set utf8;//修改数据库的字符集alter table mailtable default character set utf8;//修改表的字符集如果您想要把表默认的字符集和所有字符列(CHAR, VARCHAR, TEXT)改为新的字符集,应使用如下语句:ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;警告:前面的操作转换了字符集之间的列类型。如果您有一列使用一种字符集(如latin1),但是存储的值实际上使用了其它的字符集(如utf8),这种情况不是您想要的。此时,您必须对这样的列进行以下操作。ALTER TABLE t1 CHANGE c1 c1 BLOB;ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;这种方法能够实现此功能的原因是,当您转换到BLOB列或从BLOB列转换过来时,并没有发生转换。3 mysql的主键问题:Mysql的两种主键。Primary key 和not null auto_incriment在建立mysql表时,给一个字段添加了主键primary key 在insert数据时可以不用insert主键,mysql会自动添加0,但是在第二次insert时没有填写值mysql数据库还是默认添加0,会导致有重复的主键,这是不可以的。所有在定义了primary key时,在insert数据时要给主键填写值。在建立mysql表时,给一个字段添加了主键not null auto_increment=1;这也是一个主键。时自增长的以1为开始。这个字段是可以不用填写值的,mysql数据库会自动给填写值,不会出现primary key的状况。Alter table tb add primary key(id);Alter table tb change id id int(10) not null auto_increment=1;4 删除自增长的主键id先删除自增长在删除主键Alter table tb change id id int(10);//删除自增长A......余下全文>>
可以#24右键点击要修改的表,点design table ->完了就是最右面那有个Allow null后面的点一下,出来个钥匙的形状,下面auto increment点上勾,完事,自己看图吧