max_allowed_packet 测试

一直感觉对 max_allowed_packet 的含义不是很了解，通过今天实验，感觉该值是限制单个列的大小，

下面我们看实验，max_allowed_packet 为1024，

mysql> show variables like 'max_allowed%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_allowed_packet | 1024  |
+--------------------+-------+

mysql> CREATE TABLE t1 (a int not null auto_increment, data1 LONGBLOB,
    ->                  data2 LONGBLOB, PRIMARY KEY(a));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 (data1, data2) VALUES (repeat('a',1000), repeat('a', 1000));
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t1 (data1, data2) VALUES (repeat('a',1024), repeat('a', 1024));
Query OK, 1 row affected (0.03 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (data1, data2) VALUES (repeat('a',1025), repeat('a', 1025));
ERROR 1301 (HY000): Result of repeat() was larger than max_allowed_packet (1024) - truncated
mysql> 

二、另外需要注意是，官网说， If max_allowed_packet is too small on the slave, this also causes the slave to stop the I/O thread.

（如果从库上max_allowed_packet  设置过小，可能导致  I/O thread  停止），但是我的实验结果是，从库  I/O thread  正常，并且能将值

从主库同步过来。

主库：

mysql> show variables like 'max_allowed_%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

mysql> insert into t1(data1,data2) values(repeat('a',1025),repeat('a',1025));
Query OK, 1 row affected (0.02 sec)

备库：

mysql> show variables like 'max_allo%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_allowed_packet | 1024  |
+--------------------+-------+

mysql>  SELECT LENGTH(data1), LENGTH(data2) FROM t1;
+---------------+---------------+
| LENGTH(data1) | LENGTH(data2) |
+---------------+---------------+
|          1000 |          1000 |
|          1024 |          1024 |
|          1025 |          1025 |
+---------------+---------------+