Bug #73950 On using the CREATE USER command on mysql shell the cli clent disconnects
Submitted: 17 Sep 2014 12:31 Modified: 29 Sep 2014 7:20
Reporter: Surajit Das Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:Ver14.14Distrib5.6.20,forLinux(x86_64) OS:Linux (CentOS Linux version 2.6.32-358.el6.x86_64)
Assigned to: CPU Architecture:Any
Tags: connection_break, mysql_cli, user_creation

[17 Sep 2014 12:31] Surajit Das 
Description:
On trying to create a new user from mysql command line the command execution fails and the CLI clent loses connection with mysql server.

However on running a usual query again the connection is reestablished and command execution passes.

Note that it happens on some machines and is not repeatable on others, with the same version of mysql server, client and linux.

How to repeat:
[root@VCM-MME-2 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| vcm_udb            |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, host, password from user;
+-------+----------------+-------------------------------------------+
| user  | host           | password                                  |
+-------+----------------+-------------------------------------------+
| root  | localhost      | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| root  | VCM-MME-2      |                                           |
| root  | 127.0.0.1      |                                           |
|       | localhost      |                                           |
|       | VCM-MME-2      |                                           |
| root  | 10.10.0.154    | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| monty | 192.168.10.154 | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| root  | 192.168.10.154 | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| root  | 192.168.10.153 | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
+-------+----------------+-------------------------------------------+
9 rows in set (0.00 sec)

mysql> CREATE USER 'root'@'192.168.10.156' IDENTIFIED BY 'MontyPython';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> CREATE USER 'root'@'192.168.10.156' IDENTIFIED BY 'MontyPython';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: mysql

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select user, host, password from user;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: mysql

+-------+----------------+-------------------------------------------+
| user  | host           | password                                  |
+-------+----------------+-------------------------------------------+
| root  | localhost      | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| root  | VCM-MME-2      |                                           |
| root  | 127.0.0.1      |                                           |
|       | localhost      |                                           |
|       | VCM-MME-2      |                                           |
| root  | 10.10.0.154    | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| monty | 192.168.10.154 | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| root  | 192.168.10.154 | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| root  | 192.168.10.153 | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
+-------+----------------+-------------------------------------------+
9 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> CREATE USER 'root'@'192.168.10.156' IDENTIFIED BY 'MontyPython';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
mysql> 
mysql> 
mysql> 
mysql> CREATE USER 'root'@'192.168.10.156' IDENTIFIED BY 'MontyPython';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: mysql

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
mysql> 
mysql> 
mysql> CREATE USER 'root'@'192.168.10.156' IDENTIFIED BY 'MontyPython';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: mysql

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
mysql> 
mysql> 
mysql> select user, host, password from user;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: mysql

+-------+----------------+-------------------------------------------+
| user  | host           | password                                  |
+-------+----------------+-------------------------------------------+
| root  | localhost      | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| root  | VCM-MME-2      |                                           |
| root  | 127.0.0.1      |                                           |
|       | localhost      |                                           |
|       | VCM-MME-2      |                                           |
| root  | 10.10.0.154    | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| monty | 192.168.10.154 | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| root  | 192.168.10.154 | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
| root  | 192.168.10.153 | *45F82D2D9C67635B6EFFB39D76081A750CE87AC7 |
+-------+----------------+-------------------------------------------+
9 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> use mysql;
Database changed
mysql> quit
Bye
[root@VCM-MME-2 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.6.20, for Linux (x86_64) using  EditLine wrapper
[root@VCM-MME-2 ~]# cat /proc/version 
Linux version 2.6.32-358.el6.x86_64 (mockbuild@c6b8.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-3) (GCC) ) #1 SMP Fri Feb 22 00:31:26 UTC 2013
[root@VCM-MME-2 ~]# 
[root@VCM-MME-2 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UNKNOWN qlen 1000
    link/ether 00:0c:29:9c:9c:bb brd ff:ff:ff:ff:ff:ff
    inet 192.168.10.154/24 brd 192.168.10.255 scope global eth1
    inet 192.168.10.170/24 brd 192.168.10.255 scope global secondary eth1:9
    inet6 fe80::20c:29ff:fe9c:9cbb/64 scope link 
       valid_lft forever preferred_lft forever
3: eth2: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UNKNOWN qlen 1000
    link/ether 00:0c:29:9c:9c:c5 brd ff:ff:ff:ff:ff:ff
    inet 127.0.0.2/32 brd 127.0.0.2 scope host eth2
    inet6 fe80::20c:29ff:fe9c:9cc5/64 scope link 
       valid_lft forever preferred_lft forever
4: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UNKNOWN qlen 1000
    link/ether 00:0c:29:9c:9c:cf brd ff:ff:ff:ff:ff:ff
    inet 10.10.0.154/24 brd 10.10.0.255 scope global eth0
    inet6 fe80::20c:29ff:fe9c:9ccf/64 scope link 
       valid_lft forever preferred_lft forever
[17 Sep 2014 13:04] Surajit Das 
Found a work around.
Reducing the severity.

sudo mysql_upgrade -u root -p

This does the job.

Saw some errors in the mysql error file.
One of the lines there is 
2014-09-18 01:56:42 22151 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure

I am not sure exactly what it means.
I could attach the whole .err file if requested.
[17 Sep 2014 14:19] MySQL Verification Team 
Thank you for the bug report.

1- Your 5.6.20 install was an upgrade?
2- mysql_upgrade fixed the issue?
[17 Sep 2014 14:56] Surajit Das 
1. On one of the machines it was an upgrade from 5.1. On the rest it was a fresh install.

2. Yes mysql_upgrade solved the issue.

But, what is causing the tables in mysql database to have a wrong structure as the .err file suggests?

Eg. 2014-09-18 01:56:42 22151 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
[17 Sep 2014 15:51] MySQL Verification Team 
Thank you for the feedback. I have installed 5.6.20 on Centos 6.5 without that issue are you report. On another hand run mysql_upgrade isn't a workaround how you wrote instead is the correct way when doing a server upgrade:

http://dev.mysql.com/doc/refman/5.6/en/mysql-upgrade.html

So looks to me not a bug. Thanks.
[29 Sep 2014 7:20] Surajit Das 
Closed as the mysql_upgrade solves the issue.