首页 > MySQL > update更新数字主键的时候可能导致主从(master/slave)复制中断

update更新数字主键的时候可能导致主从(master/slave)复制中断

– 准备工作
CREATE TABLE `t_ivan_innodb` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `f_c` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT charset=latin1;

CREATE TABLE `t_ivan_myisam` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `f_c` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=Myisam DEFAULT charset=latin1;

INSERT INTO t_ivan_innodb(f_c) VALUES();
INSERT INTO t_ivan_innodb(f_c) VALUES();
INSERT INTO t_ivan_innodb(f_c) VALUES();
DELETE FROM t_ivan_innodb WHERE id=1;

INSERT INTO t_ivan_myisam(f_c) VALUES();
INSERT INTO t_ivan_myisam(f_c) VALUES();
INSERT INTO t_ivan_myisam(f_c) VALUES();
DELETE FROM t_ivan_myisam WHERE id=1;

– ——————————————-

root:ivan> UPDATE t_ivan_innodb SET id=id-1 WHERE id>1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

root:ivan> UPDATE t_ivan_myisam SET id=id-1 WHERE id>1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

– 可见默认在物理记录数序没有打乱的时候,默认是asc的,更新都正常
– ——————————————–

DROP TABLE t_ivan_innodb;
DROP TABLE t_ivan_myisam;
– 重新创建和插入记录

root:ivan> UPDATE t_ivan_innodb SET id=id-1 WHERE id>1 ORDER BY id DESC;
ERROR 1062 (23000): Duplicate entry ’2′ FOR KEY ‘PRIMARY’

root:ivan> UPDATE t_ivan_myisam SET id=id-1 WHERE id>1 ORDER BY id DESC;
ERROR 1062 (23000): Duplicate entry ’2′ FOR KEY ‘PRIMARY’

– desc排序的时候肯定错误
– ———————————————

– 下面插入数据故意打乱

INSERT INTO t_ivan_innodb(id) VALUES(2);
INSERT INTO t_ivan_innodb(id) VALUES(3);
INSERT INTO t_ivan_innodb(id) VALUES(4);
INSERT INTO t_ivan_innodb(id) VALUES(7);
INSERT INTO t_ivan_innodb(id) VALUES(6);
INSERT INTO t_ivan_innodb(id) VALUES(5);

INSERT INTO t_ivan_myisam(id) VALUES(2);
INSERT INTO t_ivan_myisam(id) VALUES(3);
INSERT INTO t_ivan_myisam(id) VALUES(4);
INSERT INTO t_ivan_myisam(id) VALUES(7);
INSERT INTO t_ivan_myisam(id) VALUES(6);
INSERT INTO t_ivan_myisam(id) VALUES(5);

root:ivan> UPDATE t_ivan_innodb SET id=id-1 WHERE id>1;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

root:ivan> UPDATE t_ivan_myisam SET id=id-1 WHERE id>1;
ERROR 1062 (23000): Duplicate entry ’6′ FOR KEY ‘PRIMARY’
 


小结:更新数字主键+ – 的时候需要注意逻辑顺序,如果物理上的数据跟逻辑不匹配会导致sql语句出错
这样如果master是innodb,而slave是myisam引擎的时候,会导致复制中断。

Related posts:

  1. 事务隔离级别导致锁级别的不同
  2. Innodb如何查看剩余表空间?
  3. MySQL 5.5 表分区功能增强
  4. 发布自己的DbSession类(for php)
  5. MySQL分区表没有全局索引,只有分区索引
分类: MySQL 标签:
  1. digital猪头
    2009年7月28日16:10 | #1

    学习一下

  1. 本文目前尚无任何 trackbacks 和 pingbacks.