MySQL server has gone away

One day I suddenly got the ”MySQL server has gone away” error message in my web-application. No queries were running. The webpage (cron update) that generates the error is a heavy one with approx. 10 000 queries. I had never seen this error message before and the solutions to fix it seems to be different in each case. The way I solved the error was the following.

1) I ran ”mysqlcheck” in repair mode and it fixed problems with clients that were using or hadn’t closed tables properly.

2) I found this workaround.

3) Then I checked the mysql servers system variable wait_timeout with:
mysqladmin -u username -p variables

The value was 10 which were pretty low considering the default value which is 28800.

wait_timeout is the number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.

4) I searched and found the ”wait_timeout” value in the mysql config file /etc/my.cnf. I had set this value to 10 when I did some mysql performance optimization in the past. Then I raised this value to a higher number.

5) Restarted mysql and ran the webpage. Everything worked fine.

I still don’t know why this error occured that day. The server configuration worked good before the accident and I haven’t made any changes lately. Very strange!

Convert the character set of a mysql database

This php-script is for converting content of all tables of a mysql database to another character set. In this case i convert everything (latin 8859-1) to utf8_swedish_ci. The same action can be made from the shell with mysqldump and iconv, but the programmer in me prefers the-php-way. As always, tweaks and comments are very helpful.

<?

set_time_limit(0);

$link = mysql_connect(‘localhost’, ‘mysql_user’, ‘mysql_password’);

mysql_select_db(‘db’);

$sql = "SHOW TABLES FROM db";
$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
{
$sql = "ALTER TABLE ".$row[0]." CONVERT TO CHARACTER SET utf8 COLLATE utf8_swedish_ci";
mysql_query($sql);
echo ‘Converting table: ‘.$row[0].’<br>’;
}

?>

MySQL – replace – sök och ersätt

En smidig funktionalitet i MySQL för att göra enkla sökningar-ersättningar (find-replace) i texter är att använda sig av strängfunktionen replace. Den har visat sig väldigt användbar då jag av misstag fått in en massa junktext i databasen och måste rätta detta.

Syntax:
REPLACE(sträng,från_sträng,till_sträng)

Exempel:
mysql> SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);
Resultat> ‘WwWwWw.mysql.com’