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)

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/ 算法有關

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料