ClearDB id 連續遞增問題

本文為 Lidemy [BE201] 部署第十七週的抽獎網站到 Heroku 及使用 ClearDB，新增獎項時 id 連續遞增問題，以下記錄試圖解決的過程。

結論：auto increment id 只保證嚴格遞增，沒有保證連續，就算遞增時調整成都是 1 也一樣。這是 auto increment id 的限制，ClearDB 的做法是使用遞增但是間隔不是連續的方式，如果要使用的話，就接受它。

一、使用背景：

使用 express + sequelize 做了一個抽獎網站，部署前在本地端測試沒有此問題，但部署到 heroku 後，在後台新增獎項時，id 沒有連續遞增。

使用的介面是 Sequel Pro

二、問題描述：

預期 id 自動增加的順序及間隔為 1 → 2 → 3 → 4，實際情況是 4 → 14 → 24 → 34。

三、關鍵字：

auto_increment_offset
auto_increment_increment
MySQL
lampp/etc/my.cnf

四、嘗試過程：

嘗試 1：刪掉重新部署一次

刪掉重新再部署一次，並在資料庫的 Table Info reset AUTO_INCREMENT 但沒有效果。

推測應該是資料庫的設定有問題，id 應該從 1 開始，但實際從 4 開始，而且 offset 不應該增加為 10，可是我找不到在哪設定。

嘗試 2:

在 Prizes 這個表格裡面，使用 query show variables like '%auto_increment%'; 果然出現了奇怪的設定

註：當我理解原理後，就不會說這是「奇怪的設定了」...

// 每次 auto_increment 增加的數值，這裡應為每次增加 1
auto_increment_increment  10

// auto_increment 的初始值，這裡應為 1 開始
auto_increment_offset     4

在表格的 query 裡面下指令：

show variables like 'auto_increment%'

// 會顯示以下兩個資訊
auto_increment_increment  10
auto_increment_offset     4

// 修改 auto_increment_offset 為一
set auto_increment_offset=1

// 修改 auto_increment_increment 為ㄧ
set auto_increment_increment=1

不過資料庫關掉以後，又變回原來的樣子

找到了這個討論串，看起來 bug 已經修好了，似乎跟我的問題無關？

Reset AUTO_INCREMENT does not always work

嘗試 3:

在 Sequel Pro 的 help 裡面搜尋 variables，可以發現有這兩項數值存在，按 save as 會存成 ServerVariables.cnf；估狗以後，應該是要找出下面其中一個檔案，把數值改好

搜尋電腦裡面的 my.ini or my.cnf 檔案：在 lampp/etc/my.cnf

裡面沒有 auto_increment_offset、auto_increment_increment 相關的設定，自己手動新增上去以後，情況也沒有改善。

五、ClearDB 官方說明：

這個 issue 在 ClearDB 官方 Q&A When I use auto_increment keys (or sequences) in my database, they increment by 10 with varying offsets. Why? 有說明，以下為原文：

ClearDB uses circular replication to provide master-master MySQL support. As such, certain things such as auto_increment keys (or sequences) must be configured in order for one master not to use the same key as the other, in all cases. We do this by configuring MySQL to skip certain keys, and by enforcing MySQL to use a specific offset for each key used. The reason why we use a value of 10 instead of 2 is for future development.

六、最佳解答

這是老師找到的連結，Auto increment by 1 in MySQL with clearDB，裡面說明了修改過程以及 ClearDB 的規則，不得不說老師關鍵字下得很準啊，讚讚，虎年 Huli 虎虎虎虎生風。

七、學到的事

MySQL重大Bug!自增主键竟然不是连续递增

1. auto increment id 嚴格遞增的原因：

a. 為了保持 id 的唯一性

b. 降低 query 的成本

如果要新增一筆資料，資料庫執行時需要「先去找出哪個 id 沒有被用過，然後再新增」，這樣大大增加了 query 的成本，要知道 query 資料表負擔是很大的。

2. auto increment id 不保證連續

a. 避免衝突

如果「要保證連續」，假設在同時刪除跟新增的情況下，會有衝突情況發生。

比如說現在最大的 id 是 10，使用者刪除 id 為 10 的資料，另一個使用者新增一筆資料，如果要「保證連續」，很有可能會有衝突的情況發生。

3. 有學過不代表記得，通常是遇到問題後，才會印象深刻

id 遞增的規則，似乎有聽老師說過，但有印象歸有印象，要等到真正遇到事情時才會深刻體會

4. 藉由搜尋關鍵字反向思考，自己其實並不真正理解問題根源

老師做了一個示範，他的關鍵字是「cleardb set variable global」，我記得我當時下的關鍵字可能是「MySQL id 增加問題」、「Heroku 部署 id 增加問題」之類的，實際上跟這個沒有太大關聯，真正的問題在於 cleardb 與 variable 上面。

5. 釐清軟體們之間的關係

a. Sequel Pro

Sequel Pro 是一款在 Mac 上運行 MySQL 資料庫的圖像化軟體

Sequel Pro is a fast, easy-to-use Mac database management application for working with MySQL databases.

b. phpMyAdmin

phpMyAdmin 是一個以 PHP 為基礎，以 Web-Base 方式架構在網站主機上的 MySQL 的資料庫管理工具，讓管理者可用 Web 介面管理 MySQL 資料庫。

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. phpMyAdmin supports a wide range of operations on MySQL and MariaDB. Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, etc) can be performed via the user interface, while you still have the ability to directly execute any SQL statement.