[SQL] 產生唯一的交易編號 – Generating unique transaction IDs

Intro 在交易的應用場景下,可能需求為透過資料庫符合 ACID 快速產生唯一的交易編號作為 Primary key 或是 Unique key。 MySQL 實作 假設定義一組16碼的交易編號,格式為YYYYMMDD(8) + 流水號(8)。 這前提下代表一天內不建立超過8位數的 record,就可以利用 MySQL auto_increment 搭配 last_insert_id() 來實現: START TRANSACTION; INSERT INTO transactions (transaction_date, amount) VALUES (NOW(), 100); SET @last_id = LAST_INSERT_ID(); SET @fixed_length = 8; SET […]

[並行控制] Concurrency control 指南 – 樂觀鎖|悲觀鎖

Intro Concurrency control – 並行控制 分類上目前可分為三種: 樂觀鎖(Optimistic Locking) 悲觀鎖(Pessimistic Locking) 半樂觀鎖 (Semi-optimistic Locking) * Database – 資料庫管理系統 樂觀鎖 – OCC 樂觀並行控制 – Optimistic Concurrency Control,縮寫「OCC」 不利用內建鎖機制,而是利用應用層邏輯解決衝突 可透過讀取資料時分派一個版本或時間戳,通過校驗後寫入否則中斷(實作上可利用寫入連帶驗證版本達成) 適合交易之間資料競爭(data race)概率較小的應用情境 MySQL 實現範例 update DB.table set quantity = quantity -1 , version […]

[Web] 資料過濾與驗證指南 – Data Validation & Filter (XSS, nl2br)

Intro 防範XSS、SQL Injection以及驗證資料已經是基本開發流程,但要如何標準化的去做團隊開發就是一門學問。 資料驗證 顧名思義,就是在寫入Datebase之前處理Input資料驗證。 以MVC架構來說,Validator最佳的實踐流程點是在Model/Entity上,也就是Model已定義好驗證規則,所以所有經過此Model寫入的資料都會是經過經過規則驗證通過的,提供絕佳的共用性與嚴謹度。 Yii 2 – Validating Input (Yii 2 透過Model定義Validator實踐開發) CodeIgniter/Model – Validation (CI擴充框架中,一樣透過Model定義整合Validator實踐開發) Filter前處理or後處理 以XSS來說,到底要前處理還是後處理? 兩個都沒錯,這全看你後端/資料庫那個欄位到底是定義成Plain Text還是HTML Content。 平均開發上,只要一開始寫資料庫沒特別定義,不外乎都是Plain Text,意即做後處理比較直覺(畢竟過往資料都寫進去了)。 後處理缺點 每次在前端(HTML)顯示時都須謹記後處理,漏一個沒處理就中了廣義XSS 前處理缺點 因為資料預設就是HTML,所以應付前端不須處理;相反的在需要Plain Text(例如Excel Plain Text)時就需要在後處理反轉回來 無法保障資料驗證,因為輸入過濾將影響驗證一致性,例如最大字數通過過濾的不確定性造成不精準 遇到textarea給值時需要Decode回來 (歸類input,正規給值方式value或jQuery val(),用html不標準) 綜合以上,優缺點是一體兩面的,取決於需求。 例如80/20法則,前端顯示開發較多、Plain Text顯示開發較少,那選前處理絕對較省成本,且較嚴謹(畢竟反轉與否不影響安全性),所以依照專案與團隊自行評估即可。 我個人對於Pattern來說會選擇後處理為主,因為儲存資料定義為原始輸入資料(Plain […]

[SQL] 單一連線多資料庫設計模式 – (Multiple Catalogs Databases)

Intro 一個專案會有遇到需要操作多個資料庫的情境,然而多資料庫分離的Type一般分兩類: 同一Application分主資料庫和附屬資料庫,如分myapp與myapp_log兩個DB。 異質系統,若非API串接,也是有可能直接SQL Connection跨過去連線異質資料庫。 異質系統資料庫連線 異質系統連線沒甚麼設計模式,基本上必定需要另外設定一個連線,以PHP Yii2 Framework連線異質MySQL為例: return [ ‘components’ => [ ‘db’ => [ ‘class’ => ‘yii\db\Connection’, ‘dsn’ => ‘mysql:host=localhost;dbname=myapp’, ‘username’ => ‘user’, ‘password’ => ‘pass’, ‘charset’ => ‘utf8’, ], ‘dbOtherApp’ => [ ‘class’ => ‘yii\db\Connection’, ‘dsn’ […]

[MySQL] MySQL效能測試指南(連線|運行) – MySQL Benchmark

Intro MySQL Benchmark方法指南。 效能測試 取得PROFILE資料檢測Query: SET profiling=1;<query>;SHOW PROFILE; 從mysqlDB測測CPU: SELECT DISTINCT Transition_type_id FROM `time_zone_transition`; 連線測試 MySQL目前有個有趣的連線Latency問題,可參考:[MySQL] 如何佈署Database最佳連線路由 – phpMyAdmin建議走本機或內網 這邊提供測試方法(透過mysql client): 預裝好的選mysqldatabase來測試: use mysql; 找最大的time_zone_transition來測: SELECT * FROM `time_zone_transition`; 結果範例最後的資訊帶有總體來回時間: 120208 rows in set (0.08 sec) 交叉測試便可知越遠的client connection時間越久。

[MySQL] InnoDB Transaction – 交易指南 (SELECT … FOR UPDATE)

鎖資料列(可讀/不可讀) MySQL Document 14.5.2.4 Locking Reads 主要分為sharedLock與lockForUpdate: sharedLock locks only for write, lockForUpdate also prevents them from being selected Pessimistic Locking (SELECT … FOR UPDATE) 談到pessimistic concurrency control(Lock row for read),實作上再Transaction內使用: SELECT … FOR UPDATE 則結果列將會被鎖定且不可讀,應用上可做不重複流水號等等。

[MySQL] 資料庫密碼修改處理 – MySQL Password Change

修改密碼指令 MySQL 5.7.5 and earlier: SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘MyNewPass’); MySQL 5.7.6 and later: ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’; MariaDB設定使用者失效Bug MariaDB版本與PHPMyAdmin版本存在使用者密碼操作問題,如有遇到則以語法自行對照操作即可修復。 Ex. 5.5.46 SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘MyNewPass’); 另外如果設定完成但登入後又失效,可以確認mysql->users該用戶的plugin欄位是否有mysql_native_password值,將其刪除再設定密碼即可。 重設密碼 – Root密碼遺失強制修改 How to Reset the Root […]

[MySQL] Master / Slave – MySQL Replica 指南

引言 MySQL Replica 相關指令及指南 安裝 MySQL官方文件:Chapter 16 Replication 安裝筆記可以參考:https://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/ Slave 狀態檢查 狀態查詢SQL SHOW slave STATUS; 建議使用command直式較好查看:SHOW slave STATUS \G; 忽略錯誤 SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1; 可一次略過大量SQL數,建議逐筆Error Skip才不會漏 重新跟Master (錯誤後會停止) START SLAVE; 狀態識別 Key Description Master_Log_File IO Thread正在讀取Master的binlog Slave_IO_Running 是否在同步,正常為Yes Slave_SQL_Running 是否在同步,正常為Yes […]

[MySQL] 計算全部資料庫容量 – Count size for all databases

使用情境 總會要計算全部資料庫的總容量吧!竟然沒這SQL語法。 解決方法 下SQL語句直接從Data length計算在群組,以下為個別列出資料庫容量: SELECT table_schema AS “Database name”, SUM( data_length + index_length ) /1024 /1024 /1024 AS “Size (GB)” FROM information_schema.TABLES GROUP BY table_schema 要做加總的話,就Group SUM欄: GROUP BY “Size (GB)”