前些天公司論壇的一個(gè)從數(shù)據(jù)庫(kù)報(bào)了一次
Duplicate entry(1062)錯(cuò)誤，現(xiàn)在的論壇都搭建了雙從庫(kù)，而登錄另一臺(tái)從庫(kù)看到是同步正常的，這就有些詭異了。起初以為是mysql版本問題導(dǎo)致，但是檢查后發(fā)現(xiàn)同步正常的那臺(tái)機(jī)器的mysql版本跟主庫(kù)不一致，而恰恰是出問題的機(jī)器與主庫(kù)的版本是一致的。出錯(cuò)的語句是一個(gè)合并帖子的UPDATE操作，由此引出了這個(gè)問題，問題最初是順安發(fā)現(xiàn)的。
1 問題描述
論壇從Discuz 7.2升級(jí)到X3.2，并基于Discuz X3.2版本做了許多的定制和插件開發(fā)，用到的MySQL版本為5.1，存儲(chǔ)引擎為MyISAM，binlog_format為STATEMENT。這個(gè)問題簡(jiǎn)單描述就是：論壇里面有個(gè)帖子表，在合并帖子的時(shí)候會(huì)對(duì)帖子的位置信息進(jìn)行更新，在執(zhí)行位置更新語句的時(shí)候主庫(kù)和另一個(gè)從庫(kù)沒有報(bào)錯(cuò)，而其中一個(gè)從庫(kù)報(bào)錯(cuò)了。為了方便描述，這里先創(chuàng)建一個(gè)測(cè)試表post，
CREATE TABLE `post` (
`i` int(11) DEFAULT NULL,
`p` int(11) NOT NULL,
PRIMARY KEY (`p`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
然后在里面插入幾條數(shù)據(jù) insert into post values (1,1), (2,3), (3,4)。在論壇里面合并帖子的時(shí)候會(huì)執(zhí)行一個(gè)帖子位置更新的操作 update post set p=p+1;，這樣我們看到這個(gè)操作就報(bào)錯(cuò)了。
mysql> insert into post values(1,1), (2,3),(3,4);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> update post set p=p+1;
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
mysql> select * from post;
+------+---+
| i | p |
+------+---+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
+------+---+
3 rows in set (0.00 sec)
2 解決方案
看到這里相比大家都發(fā)現(xiàn)了，這是因?yàn)閁PDATE的順序問題導(dǎo)致出現(xiàn)了鍵值沖突，因?yàn)槲覀儾]有指定UPDATE的順序，而恰好我們看到的順序是按照p=1, 3, 4的順序來執(zhí)行，因此，第一條記錄(1,1)成功更新成了(1,2)，而執(zhí)行到p=3的時(shí)候，因?yàn)閜+1=4與已有的值4沖突報(bào)錯(cuò)。要解決這個(gè)問題的方式也很簡(jiǎn)單，在更新位置信息時(shí)指定順序?yàn)?code>DESC，這樣從大到小執(zhí)行更新就不會(huì)出錯(cuò)了，update post set p=p+1 order by p desc(ps：之前很少在UPDATE語句中用ORDER BY 語句，還以為不支持，查了下手冊(cè)確認(rèn)了下是OK的)。MySQL手冊(cè)里面還特意提到過這個(gè)問題，可能Discuz開發(fā)人員沒有注意到這一頁(yè)，當(dāng)然Discuz里面數(shù)據(jù)庫(kù)操作有許多有性能問題的SQL語句，我們已經(jīng)修過很多BUG了。
If an UPDATE statement includes an ORDER BY clause, the rows are updated in the
order specified by the clause. This can be useful in certain situations that might
otherwise result in an error. Suppose that a table t contains a column id that has
a unique index. The following statement could fail with a duplicate-key error,
depending on the order in which rows are updated:
UPDATE t SET id = id + 1;
For example, if the table contains 1 and 2 in the id column and 1 is updated to 2
before 2 is updated to 3, an error occurs. To avoid this problem, add an ORDER BY
clause to cause the rows with larger id values to be updated before those with
smaller values:
UPDATE t SET id = id + 1 ORDER BY id DESC;
3 幾個(gè)問題
-
1）MySQL的SELECT和UPDATE語句的默認(rèn)順序是怎么樣的？
這個(gè)問題mysql手冊(cè)中也有提到過，SELECT/UPDATE語句的默認(rèn)順序跟數(shù)據(jù)文件以及存儲(chǔ)引擎等相關(guān)，在寫SQL語句的時(shí)候千萬不要依賴默認(rèn)順序，如有需要，請(qǐng)加上ORDER BY.
-
2）為什么版本一致，但是主庫(kù)沒有報(bào)錯(cuò)而從庫(kù)報(bào)錯(cuò)？
這就是第一個(gè)問題里面說的，雖然主庫(kù)和從庫(kù)版本相同，但是由于數(shù)據(jù)組織不同導(dǎo)致主庫(kù)UPDATE的順序跟從庫(kù)不同，巧合的避過了重復(fù)鍵的錯(cuò)誤。如果本身主庫(kù)和從庫(kù)的UPDATE順序一致，那么主庫(kù)本身就會(huì)只執(zhí)行部分更新并報(bào)錯(cuò)，從之前的那篇 MySQL binlog格式解析可以知道，這種情況下binlog里面是會(huì)記錄一個(gè)錯(cuò)誤碼的，那么從庫(kù)在執(zhí)行的時(shí)候雖然也會(huì)有一個(gè)
Duplicate entry(1062)錯(cuò)誤，但是對(duì)比binlog的錯(cuò)誤碼是一致的，所以同步并不會(huì)出錯(cuò)。
