MySQL数据库连接超时(wait_timeout)问题的处理

想必大家在用MySQL时都会遇到连接超时的问题，如下图所示：

    就是这个异常(com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure Last packet sent to the server was X ms ago)，是由于MySQL服务在长时间不连接之后断开了，断开之后的首次请求会抛出这个异常。那么既然是连接超时的问题，就要去MySQL中探究一下连接时间是怎么控制的。打开MySQL的控制台，运行:show variables like ‘%timeout%’，查看和连接时间有关的MySQL系统变量，得到如下结果：

    其中wait_timeout就是负责超时控制的变量，其时间为长度为28800s，就是8个小时，那么就是说MySQL的服务会在操作间隔8小时后断开，需要再次重连。也有用户在URL中使用jdbc.url=jdbc:mysql://localhost:3306/nd?autoReconnect=true来使得连接自动恢复，当然了，这是可以的，不过是MySQL4及其以下版本适用。MySQL5中已经无效了，必须调整系统变量来控制了。MySQL5手册中对两个变量有如下的说明：
    interactive_timeout：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。又见wait_timeout
    wait_timeout:服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)，又见interactive_timeout
    如此看来，两个变量是共同控制的，那么都必须对他们进行修改了。继续深入这两个变量wait_timeout的取值范围是1-2147483(Windows)，1-31536000(linux)，interactive_time取值随wait_timeout变动，它们的默认值都是28800。
    MySQL的系统变量由配置文件控制，当配置文件中不配置时，系统使用默认值，这个28800就是默认值。要修改就只能在配置文件里修改。Windows下在%MySQL HOME%/bin下有mysql.ini配置文件，打开后在如下位置添加两个变量，赋值。（这里修改为388000）
    保存退出，重启mysql服务，一定是重启系统服务。便可看到修改结果：

    Linux系统下的配置文件为/etc/my.cnf。需要多说一点的是：windows下的配置文件具体是哪个需要从windows系统服务中找到mysql，打开属性，看“可执行文件路径”里面的参数值，因为它可能是my.cnf而不是my.ini，这是由于安装时的设置，我们可能会忽略它。

    至此，修改完成，那么连接超时的问题从数据库上就解决了。当然在编程时也可以使用连接池设置超时时间，配置相对简单。但修改数据库更为本质一些。
    个人见解。希望对使用者有用。

===========================================================

 

你可能会想到在tomcat的数据源配置中有没有办法解决？的确，在jdbc连接url的配置中，你可以附上“autoReconnect=true”，但这仅对mysql5以前的版本起作用。增加“validation query”似乎也无济于事。

　　本人觉得最简单的办法，就是对症下药：既然问题是由mysql5的全局变量wait_timeout的缺省值太小引起的，我们将其改大就好了。

查看mysql5的手册，发现对wait_timeout的最大值分别是24天/365天(windows/linux)。以windows为 例，假设我们要将其设为21天，我们只要修改mysql5的配置文件“my.ini”(mysql5 installation dir)，增加一行：wait_timeout=1814400.
4
　　需要重新启动mysql5。

　　linux系统配置文件：/etc/my.cnf.

 

 

=================================================================

 

 

Hql中比较时间时拼hql语句，可以【cast "2010-07-03 10:26:46" as date 】但不能【cast "2010-07-03 10:26:46" as datetime 】，因为hibernate不支持，见如下：

Java代码   

1. String beginDate = "2010-07-03 10:26:46";  
2. String hql = "flowDefinition.createTime > " + "cast('" + beginDate + "' as datetime)" ;  

但是如果必须比较datetime,可以这样比较：

Java代码   

1. String beginDate = "2010-07-03 10:26:46";  
2. String hql = "flowDefinition.createTime > " + "'"+beginDate+"'" ;  

如果使用sql,mysql同时支持【cast "2010-07-03 10:26:46" as date 】和【cast "2010-07-03 10:26:46" as datetime 】。

===================================================================

MySQL数据修复方法：

[ERROR] /usr/local/mysql/bin/mysqld: Table './vodlite3/t_report_asset_play' is marked as crashed and should be repaired

预防措施：
1、一定要备份一次数据库，起码保留了表结构，有些可有可无的数据，可以直接覆盖。
2、重要的数据要经常注意备份，一般一个月左右备份一次。
3、出现此类错误，一般能够解决，经jb51.net测试下面的方法是比较可行的。

复制代码代码如下:

解决办法：首先需要定位到你的mysql的bin目录，里面包含myisamchk.exe文件的目录 
./myisamchk -c -r 数据库表MYI文件的路径(例如：/home/mysql/var/crawlerfeedsky/aaaa.MYI) 
如果还不行，就-f 强制修复 

windows服务器下mysql的修复方法：
F:\sqldata\mysql\bin>cd .. 返回上级目录 
F:\sqldata\mysql>cd data 进入数据库所在目录（mysql的数据库文件都是放在data这个目录里面的） 
F:\sqldata\mysql\data>cd jb51 进入数据库，windows服务器中数据库就是一个文件夹（这里以jb51为例子） 

F:\sqldata\mysql\data\jb51>myisamchk -r jb51_soft 

- recovering (with sort) MyISAM-table 'jb51_soft' 
Data records: 7216 
- Fixing index 1 
- Fixing index 2 
- Fixing index 3 

F:\sqldata\mysql\data\jb51> 

jb51_soft为数据库的表名，提示哪个表错误，就修复哪个表。

为了以后方便，我们可以写成一个 批处理 ,将这个文件保存为fixit.bat放到数据库所在的目录即可。

复制代码代码如下:

myisamchk -r jb51_soft

下面是网上收集的多种方法，大家可以测试下。
（一） 
昨晚浏览自己的Blog的时候，突然发现所有页面都无法显示，到后台查看的时候，发现一个”Table ‘xxx' is marked as crashed and should be repaired” 的错误。连忙上网搜索，原来修改这个严重的错误很简单： 
1. 进入管理mysql的phpmyadmin 
2. 在左则选中自己的数据库 
3. 在右则勾选中错误信息中的那个'xxx'表 
4. 滚动屏幕到下面，有个下拉菜单(With selected:)，选择”Repair table” 
修改之后，并没有发现有什么数据损失，连忙备份了一份数据库到邮件里面，备份还是很重要的！ 
（二） 
现象： 
mysql> use yourealcn 
Database changed 
mysql> select user,company from biz_user where commend='1' order by regtime desc,checked desc limit 0,5; 
ERROR 145 (HY000): Table './yourealcn/biz_user' is marked as crashed and should be repaired 
mysql> exit 
使用命令myisamchk修复数据库的MYI文件即可 
# /usr/local/mysql5/bin/myisamchk -c -r /bak/lib/mysql/yourealcn/biz_user.* 
myisamchk: error: '/bak/lib/mysql/yourealcn/biz_user.frm' is not a MyISAM-table 
--------- 
myisamchk: error: '/bak/lib/mysql/yourealcn/biz_user.MYD' is not a MyISAM-table 
--------- 
- recovering (with sort) MyISAM-table '/bak/lib/mysql/yourealcn/biz_user.MYI' 
Data records: 20414 
- Fixing index 1 
- Fixing index 2 
- Fixing index 3 
- Fixing index 4 
（三） 
Caused by: java.sql.SQLException: Table '表名' is marked as crashed and should be repaired 
解决办法： 
./myisamchk -c -r 数据库表MYI文件的路径(例如：/home/mysql/var/crawlerfeedsky/aaaa.MYI) 
如果还不行，就-f 强制修复 
（四） 
今天上服务器一看，发现网页错误，无法连接数据库服务器。mysql服务自己down掉了，然后重新启动服务器，发现网页无法打开，提示： [mysql]Table tblName is marked as crashed and should be repaired 
Mysql提示tblName表格已损坏，需要修复，解决方法： 
进入到对应的数据库目录： 
cd /var/lib/mysql/dbname 
使用myisamchk修复： 
shell> myisamchk -r tblName 
（五） 
我用的修复命令是：myisamchk -r bbsthreads 
其中bbsthreads是我出问题的表名，当然使用这个命令还得进入mysql你所出问题的数据库的表的存放路径，具体更详细的命令可以看帮助：myisamchk --help； 
如果用以上命令你不能解决问题请看后面，后面的内容是我转载的。 
我的网站出问题了，访问一看，果然全屏报错，检查mysql日志，错误信息为： 
Table '.\dedecmsv4\dede_archives' is marked as crashed and should be repaired 
提示说cms的文章表dede_archives被标记有问题，需要修复。于是赶快恢复历史数据，上网查找原因。最终将问题解决。解决方法如下： 
找到mysql的安装目录的bin/myisamchk工具，在命令行中输入： 
myisamchk -c -r ../data/dedecmsv4/dede_archives.MYI 
然后myisamchk 工具会帮助你恢复数据表的索引。重新启动mysql，问题解决。 
问题分析： 
1、错误产生原因，有网友说是频繁查询和更新dede_archives表造成的索引错误，因为我的页面没有静态生成，而是动态页面，因此比较同意这种说法。还有说法为是MYSQL数据库因为某种原因而受到了损坏，如：数据库服务器突发性的断电、在提在数据库表提供服务时对表的原文件进行某种操作都有可能导致MYSQL数据库表被损坏而无法读取数据。总之就是因为某些不可测的问题造成表的损坏。 
问题的编号为145 
2、问题解决办法。 
当你试图修复一个被破坏的表的问题时，有三种修复类型。如果你得到一个错误信息指出一个临时文件不能建立，删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的。 
这三种修复方法如下所示： 
% myisamchk --recover --quick /path/to/tblName 
% myisamchk --recover /path/to/tblName 
% myisamchk --safe-recover /path/to/tblName 
第一种是最快的，用来修复最普通的问题；而最后一种是最慢的，用来修复一些其它方法所不能修复的问题。 
检查和修复MySQL数据文件 
如果上面的方法无法修复一个被损坏的表，在你放弃之前，你还可以试试下面这两个技巧： 
如果你怀疑表的索引文件(*.MYI)发生了不可修复的错误，甚至是丢失了这个文件，你可以使用数据文件(*.MYD)和数据格式文件(*.frm)重新生成它。首先制作一个数据文件(tblName.MYD)的拷贝。重启你的MySQL服务并连接到这个服务上，使用下面的命令删除表的内容： 
mysql> DELETE FROM tblName; 
在删除表的内容的同时，会建立一个新的索引文件。退出登录并重新关闭服务，然后用你刚才保存的数据文件(tblName.MYD)覆盖新的(空)数据文件。最后，使用myisamchk执行标准的修复(上面的第二种方法)，根据表的数据的内容和表的格式文件重新生成索引数据。 
如果你的表的格式文件(tblName.frm)丢失了或者是发生了不可修复的错误，但是你清楚如何使用相应的CREATE TABLE语句来重新生成这张表，你可以重新生成一个新的.frm文件并和你的数据文件和索引文件(如果索引文件有问题，使用上面的方法重建一个新的)一起使用。首先制作一个数据和索引文件的拷贝，然后删除原来的文件(删除数据目录下有关这个表的所有记录)。 
启动MySQL服务并使用当初的CREATE TABLE文件建立一个新的表。新的.frm文件应该可以正常工作了，但是最好你还是执行一下标准的修复(上面的第二种方法)。 
3、myisamchk工具介绍（见mysql的官方手册） 
可以使用myisamchk实用程序来获得有关数据库表的信息或检查、修复、优化他们。myisamchk适用MyISAM表(对应.MYI和.MYD文件的表)。 
调用myisamchk的方法： 
shell> myisamchk [options] tbl_name ... 
options指定你想让myisamchk做什么。在后面描述它们。还可以通过调用myisamchk --help得到选项列表。 
tbl_name是你想要检查或修复的数据库表。如果你不在数据库目录的某处运行myisamchk，你必须指定数据库目录的路径，因为myisamchk不知道你的数据库位于哪儿。实际上，myisamchk不在乎你正在操作的文件是否位于一个数据库目录；你可以将对应于数据库表的文件拷贝到别处并且在那里执行恢复操作。 
如果你愿意，可以用myisamchk命令行命名几个表。还可以通过命名索引文件(用“ .MYI”后缀)来指定一个表。它允许你通过使用模式“*.MYI”指定在一个目录所有的表。例如，如果你在数据库目录，可以这样在目录下检查所有的MyISAM表： 
shell> myisamchk *.MYI 
如果你不在数据库目录下，可通过指定到目录的路径检查所有在那里的表： 
shell> myisamchk /path/to/database_dir/*.MYI 
你甚至可以通过为MySQL数据目录的路径指定一个通配符来检查所有的数据库中的所有表： 
shell> myisamchk /path/to/datadir/*/*.MYI 
推荐的快速检查所有MyISAM表的方式是： 
shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI 
如果你想要检查所有MyISAM表并修复任何破坏的表，可以使用下面的命令： 
shell> myisamchk --silent --force --fast --update-state \ 
-O key_buffer=64M -O sort_buffer=64M \ 
-O read_buffer=1M -O write_buffer=1M \ 
/path/to/datadir/*/*.MYI 
该命令假定你有大于64MB的自由内存。关于用myisamchk分配内存的详细信息，参见5.9.5.5节，“myisamchk内存使用”。 
当你运行myisamchk时，必须确保其它程序不使用表。否则，当你运行myisamchk时，会显示下面的错误消息： 
warning: clients are using or haven't closed the table properly 
这说明你正尝试检查正被另一个还没有关闭文件或已经终止而没有正确地关闭文件的程序(例如mysqld服务器)更新的表。 
如果mysqld正在运行，你必须通过FLUSH TABLES强制清空仍然在内存中的任何表修改。当你运行myisamchk时，必须确保其它程序不使用表。避免该问题的最容易的方法是使用CHECK TABLE而不用myisamchk来检查表。