MySQL Bugs: #68043: MySQL keeps dropping connection, even locally.

Bug #68043	MySQL keeps dropping connection, even locally.
Submitted:	6 Jan 2013 16:57	Modified:	12 Jan 2013 6:25
Reporter:	Eos Parish	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.6.9-rc	OS:	Linux (CentOS release 5.8 (Final))
Assigned to:		CPU Architecture:	Any

Description:
For some reason I'm having to reconnect to this version every query. It's been like this since upgrading from 5.6.6-rc despite no identifiable change to the my.cnf I can find. 

mysql> show variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1484633
Current database: *** NONE ***

+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 90       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
11 rows in set (0.01 sec)

mysql> show variables like '%version%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1484972
Current database: *** NONE ***

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.2.9                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.9-rc                     |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

Server is predominantly innodb and using the RC because it needed the FTS capabilities, but this error even occurs on memory based tables. Available RAM shows a full 1 GB free still and nothing is showing up in the error logs.

How to repeat:
Run any query.
Size does not appear to matter, neither does whether it's local or remote.

Auto reconnect is turned on otherwise it'd be crippling. Even with that I had to resort to mysql_ping/reconnect on certain scripts that are critical and only run periodically.

Please upload the compressed mysql error log.  Perhaps server is restarting frequently?  Or else, check if somebody kills connections:

SHOW GLOBAL STATUS LIKE '%kill%';    

/var/log/messages  can show any OOM killings, if they happened.

No one has access to the server but myself so it's not being killed, and it's not restarting when this occurs. This is the entirety of the error log;

InnoDB: Progress in percents: 12012-12-28 06:36:03 18047 [Note] InnoDB: 1.2.9 started; log sequence number 12811971891
2012-12-28 06:36:03 18047 [Note] Server hostname (bind-address): '*'; port: 3306
2012-12-28 06:36:03 18047 [Note] IPv6 is available.
2012-12-28 06:36:03 18047 [Note]   - '::' resolves to '::';
2012-12-28 06:36:03 18047 [Note] Server socket created on IP: '::'.
2012-12-28 06:36:03 18047 [Warning] 'user' entry 'root@vps36175235.vm.ramnoc.net' ignored in --skip-name-resolve mode.
2012-12-28 06:36:03 18047 [Warning] 'proxies_priv' entry '@ root@vps36175235.vm.ramnoc.net' ignored in --skip-name-resolve mode.
2012-12-28 06:36:03 18047 [Note] Event Scheduler: Loaded 0 events
2012-12-28 06:36:03 18047 [Note] /usr/sbin/mysqld: ready for connections.

Whole lot of nothing since startup.

mysql> SHOW GLOBAL STATUS LIKE '%kill%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1500393
Current database: *** NONE ***

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 0     |
+---------------+-------+
1 row in set (0.03 sec)

And for good measure;

| Uptime                                        | 800494       |
| Uptime_since_flush_status                     | 800494       |

Just another example of how ridiculous this is;

mysql> select from_unixtime(unix_timestamp());
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1550133
Current database: *** NONE ***

+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2013-01-06 19:38:23             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(unix_timestamp());
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1550136
Current database: *** NONE ***

+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2013-01-06 19:38:24             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(unix_timestamp());
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2013-01-06 19:38:25             |
+---------------------------------+
1 row in set (0.00 sec)

It has an exact 1 second wait before it's closing the connection. As long as it receives an uninterrupted stream of commands it seems to be fine, but if there's a second pause in between, or any command takes more than a second, the connection drops.

Are you connecting locally with root user, via tcp/ip 127.0.0.1 or socket?

The results are the same either way.

Doesn't matter whether I connect locally via socket or TCP, or remotely via TCP. The examples posted are all via socket, since it's default for local connections.

TCP:

[05:42:08][root@vps36175235:/proc/18047]$ mysql -h127.0.0.1 -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1612557
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

mysql> show variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1612582
Current database: *** NONE ***

+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 90       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
11 rows in set (0.00 sec)

mysql> quit;
Bye
[05:42:49][root@vps36175235:/proc/18047]$

Socket:

[05:47:21][root@vps36175235:/proc/18047]$ mysql --socket=/var/lib/mysql/mysql.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1612988
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1612995
Current database: *** NONE ***

+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 90       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
11 rows in set (0.01 sec)

mysql> quit;
Bye

Replaced with percona.