处理MySQL的典型问题

2018-11-09 12:01:50

MySQL系统参数设置问题

有如下递归函数，用来查询某型装备下的所有子装备，并将结果以字符串形式返回，具体函数如下：

图1 查看参数

经过反复测试，该函数始终无法得到所有子装备，怀疑是该函数内部某参数长度或系统函数返回结果的长度无法满足要求。

经反复检查，初步怀疑是group_concat函数返回值长度受限。

如图1所示，查看系统有关参数后发现group_concat函数的最大长度为1024，无法满足需求。故使用“set group_concat_max_len =10000”将该参数设置为10000。再次运行递归函数，结果正常。

本以为问题已经得到解决，但是在客户端断开与MySQL服务器连接后，再次连接MySQL服务器时，问题仍然出现。

此时查看参数” group_concat_max_len”，发现之前做的更改失效了。

后经研究发现”set group_concat_max_len”相当于”set session group_concat_max_len”，该设置只对单次连接有效，因此当连接断开后，再次进行连接时，上次所做更改失效。

使用”set global group_concat_max_len”修改参数后，断开连接后再次连接时，发现所做更改仍然有效。

但是当重启MySQL服务后，问题再次出现，再看参数，发现之前所做更改再次失效。

此时，修改配置文件 my.ini，加入一行” group_concat_max_len=10000”，重启 MySQL服务，再次查看对应参数，发现所做更改有效。

至此，问题完全解决。得出结论：my.ini文件中的配置永久有效；使用”set global xxx”设置参数时，在MySQL服务不重启的情况下，一直有效；使用“set xxx”设置参数时，仅对当前连接有效。

由于需要批量处理数据，因此必须用到游标。在使用游标的过程中，也出现了一些问题。

1.游标的声明。游标声明必须在变量或条件声明后。

2.多一次循环的问题。有一个存储过程，其中使用到了游标。代码如下：

图2 查看数据库情况

图3 查看相关参数

当调用该存储过程时，发现实际的循环次数总是比所希望的循环次数多一次。

经研究发现，当游标指到最后一条数据时，done的值仍为0，满足循环条件，因此又进入下一次循环，fetch后面的代码继续执行，游标继续向后移动，此时无数据，将done置为1，不满足循环条件，因此实际循环次数比理论循环次数多了一次。

在将代码进行如下修改后，程序正常执行。

……

fetch getEqu into equID,equNum,equPrice;if(not done) then

……

end if;

笔者在一次向后台服务器提交数据库处理请求时，遭遇了严重的超时问题。具体情况如下：

系统采用的是B/S架构,在前台向服务器提交请求后，前台页面一直处于等待状态。

使用”show processlist”反复查看数据库运行状态，结果如图2所示。

结果数据库一直在执行语句”delete……”,直到50秒后，进入sleep状态，此时前台的等待状态结束。怀疑该问题与数据库锁有关。此时使用”show variables like ‘%innodb_lock%’” 查看相关参数，结果如图3所示。

我们可以看到innodb_lock_wait_timeout的值与之前等待的时间相同，再查看后台软件，有提示表示锁等待超时。

为了查明具体的原因，再次向服务器提交相同的请求。

此时，查看information_schema数据库，如图 4、图 5。

通过以上结果可以初步判断，此次请求造成了数据库的锁等待，并且能够精准定位具体产生锁等待的表为ck.sysmsg。

此时，再去检查后台响应请求的代码，

图4 information_schema数据库

图5 information_schema数据库

此处，首先建立了一个数据库连接，并且执行了相应的数据库操作，该操作会向ck.sysmsg表中新增一条数据,但是由于将autocommit设置为0，该新增操作并不会立即提交。

然后，sqlExecute函数又新建了一个数据库连接，用来更新application表，同时会触发对ck.sysmsg表的delete操作。

此时，之前向ck.sysmsg新增数据的操作正在等待提交。因而就会产生锁等待的现象，delete的操作一直在等待ck.sysmsg表中锁的释放。

为了解决这一问题，对后台代码做如下的修改：

至此，问题完全解决。

在利用MySQL进行开发的时候会遇到各种各样的问题，在遇到问题时，一定要思路清晰，多利用MySQL自带的命令语句查找问题。希望本文能为大家提供一个思路。