先說結論 Ref: wsrep_max_ws_size 官方文件寫得死死了 ; 看到 permitted the maximum to be set beyond 2GB, which was rejected by Galera.
就又再更心死了一次
2147483647 (2GB, >= MariaDB Galera 10.0.27, MariaDB 10.1.17)
1073741824 (1GB, <= MariaDB Galera 10.0.26, MariaDB 10.1.16)
為什麼這次要研究這個問題 ; 因為我們需要討論的是一大包 Transactions 的合理性，
改不改得動是商務邏輯的事，但從架構面能不能解決 ERROR 1180 看起來還是有點辦法的
MariaDB [test]> COMMIT;
ERROR 1180 (HY000): Got error 5 "Input/output error" during COMMIT
基於 Percona 對 MySQL 做的測試在這可以參考，我們這次的測試標的基本資訊如下
[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@localhost ~]# mysql -e "\s" | grep -i "Server version"
Server version: 10.4.15-MariaDB-log MariaDB Server
[root@localhost ~]# mysql -e "show status like '%wsrep%'" | grep 'wsrep_local_state_comment\|wsrep_cluster_status\|wsrep_ready'
wsrep_local_state_comment Synced
wsrep_cluster_status Primary
wsrep_ready ON
[root@localhost ~]# mysql -e "select @@wsrep_max_ws_size" # 因為需要容易觸發的條件，所以就把設定最小值帶上
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
| 1024 |
+---------------------+
[root@localhost ~]# mysql -e "select @@binlog_row_image"
+--------------------+
| @@binlog_row_image |
+--------------------+
| FULL |
+--------------------+
測試經過語法 & schema 如下
CREATE TABLE t (
a bigint primary key DEFAULT UUID_SHORT(),
b text,
d text default "123",
e text default "123",
f text default "123",
g text default "123"
);
SET autocommit=0;
START TRANSACTION;
-- binlog_row_image = FULL 的情況下 b 僅能指定 300 個字元，再上去就 ERROR 1180
UPDATE t SET b='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
COMMIT;
-- 錯誤訊息就不另贅述了
MariaDB [test]> COMMIT;
ERROR 1180 (HY000): Got error 5 "Input/output error" during COMMIT
此時 COMMIT 成功的 binlog 長這樣，不相關的欄位也被一起帶進來了，所以合理推測 Transactions 內容也會把其他 column 帶入，如果是 blob / text 欄位的話
那踩到 wsrep_max_ws_size 上限也就合理了
#220220 20:19:55 server id 1 end_log_pos 4434 CRC32 0x68307979 Update_rows: table id 25 flags: STMT_END_F
BINLOG '
6zESYhMBAAAANgAAANAOAAAAABkAAAAAAAEABHRlc3QAAXQABgj8/Pz8/AUCAgICAj7xstL3
6zESYhgBAAAAggIAAFIRAAAAABkAAAAAAAEABv//wAEAANAREmIB9gB4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHgDADEyMwMAMTIzAwAxMjMDADEyM8ABAADQERJiASwBeHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4AwAxMjMDADEy
MwMAMTIzAwAxMjN5eTBo
'/*!*/;
### UPDATE `test`.`t`
### WHERE
### @1=99662009468977153
### @2='123'
### @3='123'
### @4='123'
### @5='123'
### @6='123'
### SET
### @1=99662009468977153
### @2='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
### @3='123'
### @4='123'
### @5='123'
### @6='123'
# Number of rows: 1
# at 4434
#220220 20:19:55 server id 1 end_log_pos 4465 CRC32 0x6dd7ab45 Xid = 11
COMMIT/*!*/;
# at 4465
#220220 20:22:56 server id 1 end_log_pos 4488 CRC32 0x8b4370c7 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
所以應該用 binlog_row_image=minimal 解
[root@localhost ~]# mysql -e "select @@wsrep_max_ws_size"
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
| 1024 |
+---------------------+
[root@localhost ~]# mysql -e "select @@binlog_row_image"
+--------------------+
| @@binlog_row_image |
+--------------------+
| MINIMAL |
+--------------------+
[root@localhost ~]# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.4.15-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [test]> SET autocommit=0;
Query OK, 0 rows affected (0.001 sec)
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
-- binlog_row_image = MINIMAL 的情況下 b 僅能指定 448 個字元，再上去就 ERROR 1180
MariaDB [test]> UPDATE t SET b='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1 Changed: 0 Warnings: 0
MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.000 sec)
此時 binlog 狀態長這樣
#220220 20:49:28 server id 1 end_log_pos 2106 CRC32 0x118f42b9 Update_rows: table id 25 flags: STMT_END_F
BINLOG '
2DgSYhMBAAAANgAAAEwGAAAAABkAAAAAAAEABHRlc3QAAXQABgj8/Pz8/AUCAgICAj5x9VP7
2DgSYhgBAAAA7gEAADoIAAAAABkAAAAAAAEABgEC/gAAAMM3EmIB/sABeHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eLlCjxE=
'/*!*/;
### UPDATE `test`.`t`
### WHERE
### @1=99662172459630592
### SET
### @2='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
# Number of rows: 1
# at 2106
#220220 20:49:28 server id 1 end_log_pos 2137 CRC32 0x3ffe0ba5 Xid = 27
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Transactions 內容是多了點沒錯，但原則上還是應急用考量
畢竟 binlog_row_image 不在 FULL 條件下的 Point-in-time recovery (PITR) 也不好實作
畢竟一個 2GB 的 Transactions 種種考量下要 roll back 也不容易。
所以還是建議拆細 Transactions 粒度才是最佳解。