溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
有記錄進行插入時，自增列產生的值就有可能與已有的記錄主鍵沖突，導致出錯。首先想辦法解決問題，通過人工調大自增列的值，保證大于表內已有的主鍵即可，調整后，導數據正常
問題發生的前置條件：
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=InnoDB AUTO_INCREMENT=6 DEFAULT 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=InnoDB AUTO_INCREMENT=6 DEFAULT 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 test_autoinc\G; | 此時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 |
| 9 | 手工調大自增主鍵 Show create table test_autoinc\G; | 手工調大自增id alter table test_autoinc auto_increment=12; | Show create table test_autoinc\G; | alter table test_autoinc auto_increment=12; Show create table test_autoinc\G; 發現master和slave的自增id一致 |
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
