Bug #41280 Lost connection to MySQL server during user's releated queries
Submitted: 6 Dec 2008 18:26 Modified: 11 Jan 2009 7:30
Reporter: Jan Kerski Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1.30 OS:FreeBSD (7.x)
Assigned to: CPU Architecture:Any

[6 Dec 2008 18:26] Jan Kerski
Description:
I had upgraded my MySQL server from 4.1 to 5.1.
Database "mysql" has been dumped to sql file before, then restored , then "flush privileges". Then I ran mysql_upgrade and mysql_fix_privilege_tables. And then I restarter mysql server.

MySQL server is working fine, all of my users could login and query their databases. Users could create databases or tables without problem.

But there is problem releated to MySQL user releated queries.

Examples:

mysql> set password for 'root'@'%'= password('foo');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2712
Current database: mysql

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> create user 'root2' identified by 'foo';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    319
Current database: mysql

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

I connect by local sockets, so that is not the network issue.

To change user's password, delete or add new users I have to sent queries to mysql.user table manually, and then do "flush privileges".

Mysql tools as grant/revoke/create user doesn't work any more.

I suspect there is bug in mysql_upgrade.

Present view of user table:

mysql> describe user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   |     |         |       |
| User                  | char(16)                          | NO   |     |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)

mysql>

mysql> show table status like 'user';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------+----------+----------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation | Checksum | Create_options | Comment                     |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------+----------+----------------+-----------------------------+
| user | MyISAM |      10 | Dynamic    | 2725 |             64 |      176700 | 281474976710655 |         1024 |         0 |           NULL | 2008-12-06 19:09:56 | 2008-12-06 19:15:06 | 2008-12-06 19:15:06 | utf8_bin  |     NULL |                | Users and global privileges |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------+----------+----------------+-----------------------------+
1 row in set (0.00 sec)

How to repeat:
As above.

Suggested fix:
unknown.
[6 Dec 2008 18:30] Jan Kerski
I'm sure that is not related to connect_timeout or sth.
This message:
"ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect..." is fine. 
I have a short timeout, but mysql client correctly reconnects itself.
[7 Dec 2008 17:59] Jan Kerski
I resolved this problem.

I hadn't PRIMARY key's on some columns in 'db' and 'user' tables.
To resolve this problem:

alter ignore table user add primary key (`Host`, `User`);
alter ignore table db add primary key (`Host`, `Db`, `User`);

IMHO - this should be done automagically by mysql_upgrade and mysql error message (Lost connection to MySQL...) should be more clear.
[11 Dec 2008 6:52] Sveta Smirnova
Changed severity as server crash is not feature request.
[11 Dec 2008 7:30] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with tables created after fresh installation of MySQL 4.1. Could you please send us error log file and dump you have problem with (replace user names/passwords with dummy values and/or upload it privately)?
[12 Jan 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".