1.版本
1)操作系统
cat /etc/issue
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Kernel \r on an \m
cat /proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014
2)mysql数据库版本
mysql --version
mysql Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using EditLine wrapper
2.问题描述
使用source导入某个库的导出文件时报如下错误:
- <span style="color:#ff0000;">ERROR 2006 (HY000): MySQL server has gone away
- No connection. Trying to reconnect...
- Connection id: 125987
- Current database: xwiki
- ...........
- ERROR 2006 (HY000): MySQL server has gone away
- No connection. Trying to reconnect...
- Connection id: 125988
- Current database: xwiki</span><span style="color:#333333;">
- ...........
- ERROR 1231 (42000): Variable 'time_zone' can't be set to the value of 'NULL'
- ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NULL'
- ERROR 1231 (42000): Variable 'foreign_key_checks' can't be set to the value of 'NULL'
- ERROR 1231 (42000): Variable 'unique_checks' can't be set to the value of 'NULL'
- ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL'
- Query OK, 0 rows affected (0.00 sec)</span>
3.解决方案
后来我通过修改max_allowed_packet参数,解决了该问题。
1)查看修改之前max_allowed_packet参数
- mysql> show variables like 'max_allowed%';
- +--------------------+---------+
- | Variable_name | Value |
- +--------------------+---------+
- | max_allowed_packet | 4194304 |
- +--------------------+---------+
- mysql> set global max_allowed_packet=16777218;
- Query OK, 0 rows affected, 1 warning (0.00 sec)
- mysql> show warnings;
- +---------+------+----------------------------------------------------------+
- | Level | Code | Message |
- +---------+------+----------------------------------------------------------+
- | Warning | 1292 | Truncated incorrect max_allowed_packet value: '16777218' |
- +---------+------+----------------------------------------------------------+
- 1 row in set (0.00 sec)
The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple
3)重新连接数据库并执行source
导入成功
4.官方文档中关于max_allowed_packet说明
1)
17.4.1.21 Replication and max_allowed_packet
max_allowed_packet
sets an upper limit on the size of any single message between the MySQL server and clients, including replication slaves. If you are replicating large column values (such as might be found in TEXT
or BLOB
columns) and max_allowed_packet
is too small on the master, the master fails with an error, and the slave shuts down the I/O thread. If max_allowed_packet
is too small on the slave, this also causes the slave to stop the I/O thread.
Row-based replication currently sends all columns and column values for updated rows from the master to the slave, including values of columns that were not actually changed by the update. This means that, when you are replicating large column values using row-based replication, you must take care to setmax_allowed_packet
large enough to accommodate the largest row in any table to be replicated, even if you are replicating updates only, or you are inserting only relatively small values.
2)
-
Command-Line Format --max_allowed_packet=#
System Variable Name max_allowed_packet
Variable Scope Global Dynamic Variable Yes Permitted Values (<= 5.6.5) Type integer
Default 1048576
Min Value 1024
Max Value 1073741824
Permitted Values (>= 5.6.6) Type integer
Default 4194304
Min Value 1024
Max Value 1073741824
The maximum size of one packet or any generated/intermediate string, or any parameter sent by the
mysql_stmt_send_long_data()
C API function. The default is 4MB as of MySQL 5.6.6, 1MB before that.The packet message buffer is initialized to
net_buffer_length
bytes, but can grow up tomax_allowed_packet
bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.You must increase this value if you are using large
BLOB
columns or long strings. It should be as big as the largestBLOB
you want to use. The protocol limit formax_allowed_packet
is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.When you change the message buffer size by changing the value of the
max_allowed_packet
variable, you should also change the buffer size on the client side if your client program permits it. The defaultmax_allowed_packet
value built in to the client library is 1GB, but individual client programs might override this. For example, mysql and mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by settingmax_allowed_packet
on the command line or in an option file.The session value of this variable is read only.