mysql重连接

[rickypc]

想请教一个问题：
为什么我每次执行一个sql（时间间隔不超过10秒钟）语句的时候都会显示数据库断开需要重新连接呢？如：

mysql> show databases;
error 2006 <HY000>: MySQL server has gone away
no connection. Trying to reconnect...
connection id : 9
current database : mysql

+---------------------------+
| DataBase |
+---------------------------+
| information_schema |

...............

请教高手帮忙。。。。

[rucypli]

直接命令行连接的吗

[zuoxingyu]

很明显楼主是用命令行连接的。
我没有遇到这样的问题。你打印出这个看看，比较下是否有什么差别。
mysql> show variables like '%time%';
+----------------------------+-------------------+
| Variable_name | Value |
+----------------------------+-------------------+
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| flush_time | 1800 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lc_time_names | en_US |
| long_query_time | 10.000000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | |
| table_lock_wait_timeout | 50 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1276506247 |
| wait_timeout | 28800 |
+----------------------------+-------------------+
20 rows in set, 1 warning (0.00 sec)

[rickypc]

我的显示是：
mysql> show variables like '%time%';
error 2006 <HY000>: MySQL server has gone away
no connection. Trying to reconnect...
connection id : 19
current database : ***NONE***

+----------------------------+-------------------+
| Variable_name | Value |
+----------------------------+-------------------+
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| flush_time | 1800 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 7776000 |
| lc_time_names | en_US |
| long_query_time | 10.000000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | |
| table_lock_wait_timeout | 50 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1276506668 |
| wait_timeout | 7776000 |
+----------------------------+-------------------+
20 rows in set, 1 warning (0.20 sec)


不知道为什么一直需要重连接。。。

[zuoxingyu]

打开错误日志看看。是什么原因导致的连接断开
贴出来后面50行。

[rickypc]

100613 15:06:07 InnoDB: Starting shutdown...
100613 15:06:08 InnoDB: Shutdown completed; log sequence number 0 44253
100613 15:06:08 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Shutdown complete

100613 15:06:16 [Note] Plugin 'FEDERATED' is disabled.
100613 15:06:16 InnoDB: Started; log sequence number 0 44253
100613 15:06:16 [Note] Event Scheduler: Loaded 0 events
100613 15:06:16 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.41-community' socket: '' port: 3306 MySQL Community Server (GPL)
100614 15:35:46 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Normal shutdown

100614 15:35:46 [Note] Event Scheduler: Purging the queue. 0 events
100614 15:35:48 [Warning] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Forcing close of thread 130 user: 'Test'

100614 15:35:48 [Warning] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Forcing close of thread 129 user: 'Test'

100614 15:35:48 [Warning] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Forcing close of thread 128 user: 'Test'

100614 15:35:48 InnoDB: Starting shutdown...
100614 15:35:49 InnoDB: Shutdown completed; log sequence number 0 44253
100614 15:35:49 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Shutdown complete

100614 15:35:56 [Note] Plugin 'FEDERATED' is disabled.
100614 15:35:56 InnoDB: Started; log sequence number 0 44253
100614 15:35:56 [Note] Event Scheduler: Loaded 0 events
100614 15:35:56 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.41-community' socket: '' port: 3306 MySQL Community Server (GPL)
100614 16:57:18 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Normal shutdown

100614 16:57:18 [Note] Event Scheduler: Purging the queue. 0 events
100614 16:57:18 InnoDB: Starting shutdown...
100614 16:57:19 InnoDB: Shutdown completed; log sequence number 0 44253
100614 16:57:19 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Shutdown complete

100614 16:58:01 [Note] Plugin 'FEDERATED' is disabled.
100614 16:58:02 InnoDB: Started; log sequence number 0 44253
100614 16:58:02 [Note] Event Scheduler: Loaded 0 events
100614 16:58:02 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.41-community' socket: '' port: 3306 MySQL Community Server (GPL)


高人帮忙看看。。

[iihero_]

[Quote=引用楼主 rickypc 的回复:]
想请教一个问题：
为什么我每次执行一个sql（时间间隔不超过10秒钟）语句的时候都会显示数据库断开需要重新连接呢？如：

mysql> show databases;
error 2006 <HY000>: MySQL server has gone away
no connection. Trying to reconnect...
connection id : 9
curre……
[/Quote]

从你后边贴出的timeout变量来看，应该不是timeout引起的问题。
反而可能跟你当前执行的sql语句之前，可能执行过很长的sql语句造成的。
你把max_allowed_packet配置变量调大一些，比如调成64M试试。
具体方法：
在my.ini的[mysqld]下:
max_allowed_packge=64M

[xilaianzxsc]

真的很复杂...

[rickypc]

有设置：max_allowed_packge=16M 还是出问题， 没有办法我只能把mysql重新安装了。 目前好像没有什么问题。

[乱世狂刀]

真复杂...

[zuoxingyu]

[Quote=引用 9 楼 rickypc 的回复:]

100613 15:06:16 [Note] Plugin 'FEDERATED' is disabled.
100613 15:06:16 InnoDB: Started; log sequence number 0 44253
100613 15:06:16 [Note] Event Scheduler: Loaded 0 events
100613 15:06:16 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.41-community' socket: '' port: 3306 MySQL Community Server (GPL)
100614 15:35:46 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Normal shutdown。
[/Quote]

这里能反应出什么问题吗？？
是不是在安装过程中的某些参数选择得不对？

[jiahehao]

100614 15:35:48 [Warning] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Forcing close of thread 130 user: 'Test'

......

100613 15:06:16 [Note] Plugin 'FEDERATED' is disabled.
***********************************

第一条表示你的连接是被用户Test强制关闭的。但没有指出原因。查了第二条，网上有人说是mysql自己的一个bug，在某种机器配置下会出现：

终于将Mysql升级到5.1，但是发现安装中老是出现莫名错误，反复琢磨查证，最后证明为MySQL的错。

版本：mysql 5.1.37 win32.msi 二进制版本
错误症状：Could not start the service=MySQL：error 0 (运行config wizard 后启动服务显示)
安装环境：XP
Log分析：可以在data文件夹中找到***.err的mysql日志文件，出现的启动错误描述日志记录：

090803 8:51:54 [Note] Plugin 'FEDERATED' is disabled.
090803 8:51:55 InnoDB: Error: unable to create temporary file; errno: 2
090803 8:51:55 [ERROR] Plugin 'InnoDB' init function returned error.
090803 8:51:55 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
090803 8:51:55 [ERROR] Unknown/unsupported table type: INNODB
090803 8:51:55 [ERROR] Aborting

解决原因：MySQL的config wizard向导生成my.ini漏掉一条配置tmpdir的目录
解决办法：在my.ini中增加一行
tmpdir="D:/Program Files/MySQL/MySQL Server 5.1/DataFiles/Data/"
(目录可以随意)

原文地址：
http://hi.baidu.com/vanjor/blog/item/eaa676a693bb589cd0435871.html/cmtid/30bb8201875daa8ae850cd24