MariaDB – max_allowed_packet size

測試環境為 CentOS 8 x86_64

遇到一個奇怪問題, LONGBLOB , LONGTEXT 字元上限為 4 GB (2^32 -1),使用 4 bytes 紀錄前面字元使用長度.但是實際使用只能到 16777216 (約 16M),下面來看一下是怎麼了.

先建立一個資料庫 (testdb) , 與資料表 (longtext1) 資料格式為 T1 (LONGTEXT).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB 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 [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [testdb]> CREATE TABLE longtext1 (T1 LONGTEXT);
Query OK, 0 rows affected (0.003 sec)

塞入一個 4G 大小的資料到 T1 LongText 欄位

MariaDB [testdb]> INSERT INTO longtext1 (T1) VALUES (RPAD('', 4294967295, 'x'));
ERROR 1301 (HY000): Result of rpad() was larger than max_allowed_packet (16777216) - truncated

以上範例透過 RPAD 函數(在字串後填入指定大小的指定字元).

SELECT RPAD('hello',10,'.');
+----------------------+
| RPAD('hello',10,'.') |
+----------------------+
| hello.....           |
+----------------------+

SELECT RPAD('hello',2,'.');
+---------------------+
| RPAD('hello',2,'.') |
+---------------------+
| he                  |
+---------------------+

Insert 失敗的原因是因為 max_allowed_packet 預設為 16M(16777216).

MariaDB [testdb]> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.001 sec)

不過該變數最大也只能到 1G(1073741824 數值須為 1024 的倍數).

MariaDB [testdb]> SET GLOBAL max_allowed_packet=1073741824;
Query OK, 0 rows affected (0.000 sec)

MariaDB [testdb]> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.001 sec)

需登出再登入才能看出改變.

MariaDB [testdb]> exit
Bye
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1668
Server version: 10.3.28-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 [(none)]> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.001 sec)

SET GLOBAL 在 MariaDB 重新啟動之後就會恢復為 16M,可以透過修改設定檔 /etc/my.cnf.d/mariadb-server.cnf 的方式.

[root@localhost ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
...
max_allowed_packet=1073741824

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-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 [(none)]> SHOW VARIABLES LIKE 'max_allowed_packet' ;
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.001 sec)

終於可以輸入 16M 大小的資料了.

MariaDB [(none)]> USE testdb;
Database changed

MariaDB [testdb]> INSERT INTO longtext1 (T1) VALUES (RPAD('', 16777216, 'x'));
Query OK, 1 row affected (0.145 sec)

不過比較奇怪的是超過一個範圍之後又不行

MariaDB [testdb]> INSERT INTO longtext1 (T1) VALUES (RPAD('', 326777216, 'x'));
Query OK, 1 row affected (6.729 sec)

MariaDB [testdb]> INSERT INTO longtext1 (T1) VALUES (RPAD('', 426777216, 'x'));
ERROR 1301 (HY000): Result of rpad() was larger than max_allowed_packet (1073741824) - truncated

這應該是跟 Package – https://mariadb.com/kb/en/0-packet/ 算法有關

常見的錯誤

1. Got a packet bigger than ‘max_allowed_packet’ bytes
   這個是在匯入資料時會產生的錯誤訊息,解決方式跟上面一樣 須加大 max_allowed_packet 的設定值.
2. Truncated incorrect max_allowed_packet value
   這是因為 max_allowed_packet 的值須為 1024 的倍數.

   MariaDB [(none)]> SET GLOBAL max_allowed_packet=20000000;
   Query OK, 0 rows affected, 1 warning (0.000 sec)
   

   MariaDB [(none)]> show warnings;
   +---------+------+----------------------------------------------------------+
   | Level   | Code | Message                                                  |
   +---------+------+----------------------------------------------------------+
   | Warning | 1292 | Truncated incorrect max_allowed_packet value: '20000000' |
   +---------+------+----------------------------------------------------------+
   1 row in set (0.000 sec)
   

   重新設定 為 16777216 (16*1024*1024) = 16M 就可以了,還是需要重新登入值才會顯示.

   MariaDB [(none)]> SHOW VARIABLES LIKE 'max_allowed_packet';
   +--------------------+----------+
   | Variable_name      | Value    |
   +--------------------+----------+
   | max_allowed_packet | 16777216 |
   +--------------------+----------+
   1 row in set (0.001 sec)