| Bug #69644 | DROP USER fails with error 1396 | ||
|---|---|---|---|
| Submitted: | 2 Jul 2013 2:37 | Modified: | 15 Jul 2013 14:31 |
| Reporter: | Jay Pipes | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S2 (Serious) |
| Version: | 5.5.31-0ubuntu0.12.04.2-log | OS: | Linux (Ubuntu 12.04) |
| Assigned to: | CPU Architecture: | Any | |
[2 Jul 2013 2:37]
Jay Pipes
[2 Jul 2013 2:47]
Jay Pipes
Some more information that might be useful... here is the error log:
root@ubuntu1204-BpOyGFi2g7WO:~# cat /var/log/mysql/error.log
130702 2:43:38 [Note] Plugin 'FEDERATED' is disabled.
130702 2:43:38 InnoDB: The InnoDB memory heap is disabled
130702 2:43:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130702 2:43:38 InnoDB: Compressed tables use zlib 1.2.3.4
130702 2:43:38 InnoDB: Initializing buffer pool, size = 128.0M
130702 2:43:38 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
130702 2:43:38 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
130702 2:43:38 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
130702 2:43:38 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
130702 2:43:38 InnoDB: Waiting for the background threads to start
130702 2:43:39 InnoDB: 5.5.31 started; log sequence number 0
130702 2:43:39 InnoDB: Starting shutdown...
130702 2:43:39 InnoDB: Shutdown completed; log sequence number 1595675
130702 2:43:39 [Note] Plugin 'FEDERATED' is disabled.
130702 2:43:39 InnoDB: The InnoDB memory heap is disabled
130702 2:43:39 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130702 2:43:39 InnoDB: Compressed tables use zlib 1.2.3.4
130702 2:43:39 InnoDB: Initializing buffer pool, size = 128.0M
130702 2:43:39 InnoDB: Completed initialization of buffer pool
130702 2:43:40 InnoDB: highest supported file format is Barracuda.
130702 2:43:40 InnoDB: Waiting for the background threads to start
130702 2:43:41 InnoDB: 5.5.31 started; log sequence number 1595675
130702 2:43:41 InnoDB: Starting shutdown...
130702 2:43:41 InnoDB: Shutdown completed; log sequence number 1595675
130702 2:43:41 [Note] Plugin 'FEDERATED' is disabled.
130702 2:43:41 InnoDB: The InnoDB memory heap is disabled
130702 2:43:41 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130702 2:43:41 InnoDB: Compressed tables use zlib 1.2.3.4
130702 2:43:41 InnoDB: Initializing buffer pool, size = 128.0M
130702 2:43:41 InnoDB: Completed initialization of buffer pool
130702 2:43:41 InnoDB: highest supported file format is Barracuda.
130702 2:43:41 InnoDB: Waiting for the background threads to start
130702 2:43:42 InnoDB: 5.5.31 started; log sequence number 1595675
ERROR: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE user ADD column Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT ' at line 1
130702 2:43:42 [ERROR] Aborting
130702 2:43:42 InnoDB: Starting shutdown...
130702 2:43:42 InnoDB: Shutdown completed; log sequence number 1595675
130702 2:43:42 [Note] /usr/sbin/mysqld: Shutdown complete
130702 2:43:42 [Note] Plugin 'FEDERATED' is disabled.
130702 2:43:42 InnoDB: The InnoDB memory heap is disabled
130702 2:43:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130702 2:43:42 InnoDB: Compressed tables use zlib 1.2.3.4
130702 2:43:42 InnoDB: Initializing buffer pool, size = 128.0M
130702 2:43:42 InnoDB: Completed initialization of buffer pool
130702 2:43:42 InnoDB: highest supported file format is Barracuda.
130702 2:43:42 InnoDB: Waiting for the background threads to start
130702 2:43:43 InnoDB: 5.5.31 started; log sequence number 1595675
130702 2:43:43 InnoDB: Starting shutdown...
130702 2:43:44 InnoDB: Shutdown completed; log sequence number 1595675
130702 2:43:44 [Note] Plugin 'FEDERATED' is disabled.
130702 2:43:44 InnoDB: The InnoDB memory heap is disabled
130702 2:43:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130702 2:43:44 InnoDB: Compressed tables use zlib 1.2.3.4
130702 2:43:44 InnoDB: Initializing buffer pool, size = 128.0M
130702 2:43:44 InnoDB: Completed initialization of buffer pool
130702 2:43:44 InnoDB: highest supported file format is Barracuda.
130702 2:43:44 InnoDB: Waiting for the background threads to start
130702 2:43:45 InnoDB: 5.5.31 started; log sequence number 1595675
ERROR: 1050 Table 'plugin' already exists
130702 2:43:45 [ERROR] Aborting
130702 2:43:45 InnoDB: Starting shutdown...
130702 2:43:45 InnoDB: Shutdown completed; log sequence number 1595675
130702 2:43:45 [Note] /usr/sbin/mysqld: Shutdown complete
130702 2:43:45 [Note] Plugin 'FEDERATED' is disabled.
130702 2:43:45 InnoDB: The InnoDB memory heap is disabled
130702 2:43:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130702 2:43:45 InnoDB: Compressed tables use zlib 1.2.3.4
130702 2:43:45 InnoDB: Initializing buffer pool, size = 128.0M
130702 2:43:45 InnoDB: Completed initialization of buffer pool
130702 2:43:45 InnoDB: highest supported file format is Barracuda.
130702 2:43:45 InnoDB: Waiting for the background threads to start
130702 2:43:46 InnoDB: 5.5.31 started; log sequence number 1595675
130702 2:43:46 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130702 2:43:46 [Note] - '127.0.0.1' resolves to '127.0.0.1';
130702 2:43:46 [Note] Server socket created on IP: '127.0.0.1'.
130702 2:43:46 [Note] Event Scheduler: Loaded 0 events
130702 2:43:46 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.31-0ubuntu0.12.04.2' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
130702 2:43:48 [Note] /usr/sbin/mysqld: Normal shutdown
130702 2:43:48 [Note] Event Scheduler: Purging the queue. 0 events
130702 2:43:48 InnoDB: Starting shutdown...
130702 2:43:49 InnoDB: Shutdown completed; log sequence number 1595685
130702 2:43:49 [Note] /usr/sbin/mysqld: Shutdown complete
Note the error in there around 'ALTER TABLE user ADD column Show_view_priv'...
[2 Jul 2013 12:26]
MySQL Verification Team
Hello Jay Pipes,
Thank you for the report.
I can not repeat described behavior with Oracle builds 5.5.31/5.5.32 and 5.6.12.
In my test case this is reproducible only when I try to drop a non-existing user..
Looking at the provided user table details, user exists and still it doesn't allow you to drop, so it looks like something is wrong with privilege tables.
From the error log contents this looks a fresh installation but would like to confirm with you - Is this a fresh installation? If upgrade, did you run mysql_upgrade during upgrade? Please let us know
>>ERROR: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE user ADD column Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT ' at line 1
I suspect the above error is either coming from --bootstrap thing that mysql_install_db does or may be init-file.
Could you please send us below results:
CHECK TABLE mysql.user;
//
bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'ushastry'"
bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
#### 5.5.31
// Works perfectly - with matching User/Host columns
[root@ushastry mysql-5.5.31]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | ushastry | |
+------+-----------+----------+
[root@ushastry mysql-5.5.31]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'ushastry'"
[root@ushastry mysql-5.5.31]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
+------+-----------+----------+
[root@ushastry mysql-5.5.31]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT version()"
+--------------+
| version() |
+--------------+
| 5.5.31-debug |
+--------------+
//
[root@ushastry mysql-5.5.31]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'localhost'"
[root@ushastry mysql-5.5.31]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
+------+-----------+----------+
// Now, if I try to delete non-existing/non-matching User/Host
[root@ushastry mysql-5.5.31]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'ushastry'"
ERROR 1396 (HY000) at line 1: Operation DROP USER failed for ''@'ushastry'
[root@ushastry mysql-5.5.31]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'localhost'"
ERROR 1396 (HY000) at line 1: Operation DROP USER failed for ''@'localhost'
#### 5.5.32
// Works perfectly - with matching User/Host columns
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | ushastry | |
+------+-----------+----------+
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'ushastry'"
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
+------+-----------+----------+
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT version()"
+------------------+
| version() |
+------------------+
| 5.5.32-debug-log |
+------------------+
//
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'localhost'"
[root@ushastry mysql-5.5.32]#
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
+------+-----------+----------+
// Now, if I try to delete non-existing/non-matching User/Host
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'ushastry'"
ERROR 1396 (HY000) at line 1: Operation DROP USER failed for ''@'ushastry'
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'localhost'"
ERROR 1396 (HY000) at line 1: Operation DROP USER failed for ''@'localhost'
//
mysql> GRANT ALL ON *.* TO ''@'ubuntu1204-Rtdho9QgrYFR';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
[root@ushastry mysql-5.5.32]#
[root@ushastry mysql-5.5.32]#
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------+-------------------------+----------+
| User | Host | Password |
+------+-------------------------+----------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| | ubuntu1204-rtdho9qgryfr | |
+------+-------------------------+----------+
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'ubuntu1204-Rtdho9QgrYFR'"
[root@ushastry mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
+------+-----------+----------+
Thanks,
Umesh
[2 Jul 2013 12:41]
Jay Pipes
shell session showing errors
Attachment: tmp.txt (text/plain), 11.86 KiB.
[2 Jul 2013 12:41]
Jay Pipes
Hi again, The attached file shows the information you've asked me to gather. Tell me, are you testing using the official Ubuntu packages or are you testing with a debug build of the MySQL 5.5.31 server? I also suspect this has something to do with the failed upgrade to the mysql.user table, but as you can see, mysql_upgrade shows no sign of a problem, and at a minimum, it is highly disturbing that: a) Doing a FLUSH PRIVILEGES before DROP USER causes the 1396 error to disappear. b) That a failure in a system schema upgrade script does not cause the server to fail fast and not start. Best, -jay
[2 Jul 2013 13:04]
MySQL Verification Team
Hi Jay, Thank you for the feedback. I'm using official binary distributions(Generic Binaries) of MySQL, and also tried on compiled debug builds. Let me give a try on official Ubuntu packages and comeback to you. Thanks, Umesh
[2 Jul 2013 17:36]
MySQL Verification Team
Hi Jay,
I can't repeat with official Ubuntu(ubuntu0.12.04.2) packages and confirmed that it allows to drop user. Could you please check with official Oracle's MySQL packages? I want to ensure this is Oracle MySQL bug and not packaging issue.
ushastry@ushastry:~$ uname -an
Linux ushastry 3.5.0-23-generic #35~precise1-Ubuntu SMP Fri Jan 25 17:13:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
ushastry@ushastry:~$
ushastry@ushastry:~$ dpkg -l | grep mysql
ii libdbd-mysql-perl 4.020-1build2 Perl5 database interface to the MySQL database
ii libmysqlclient18 5.5.31-0ubuntu0.12.04.2 MySQL database client library
ii mysql-client-5.5 5.5.31-0ubuntu0.12.04.2 MySQL database client binaries
ii mysql-client-core-5.5 5.5.31-0ubuntu0.12.04.2 MySQL database core client binaries
ii mysql-common 5.5.31-0ubuntu0.12.04.2 MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 5.5.31-0ubuntu0.12.04.2 MySQL database server (metapackage depending on the latest version)
ii mysql-server-5.5 5.5.31-0ubuntu0.12.04.2 MySQL database server binaries and system database setup
ii mysql-server-core-5.5 5.5.31-0ubuntu0.12.04.2 MySQL database server binaries
ushastry@ushastry:~$
ushastry@ushastry:~$ mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------------------+-----------+-------------------------------------------+
| User | Host | Password |
+------------------+-----------+-------------------------------------------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | ushastry | |
| debian-sys-maint | localhost | *162F3C2A52506B8EE8ABE160537D7FB0B467D75A |
+------------------+-----------+-------------------------------------------+
ushastry@ushastry:~$
ushastry@ushastry:~$ mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'ushastry'"
ushastry@ushastry:~$
ushastry@ushastry:~$ mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------------------+-----------+-------------------------------------------+
| User | Host | Password |
+------------------+-----------+-------------------------------------------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| debian-sys-maint | localhost | *162F3C2A52506B8EE8ABE160537D7FB0B467D75A |
+------------------+-----------+-------------------------------------------+
ushastry@ushastry:~$ mysql -u root --port=3306 --protocol=tcp -e "SELECT version()"
+-------------------------+
| version() |
+-------------------------+
| 5.5.31-0ubuntu0.12.04.2 |
+-------------------------+
//
mysql> GRANT ALL ON *.* TO ''@'ubuntu1204-Rtdho9QgrYFR';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
ushastry@ushastry:~$ mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------------------+-------------------------+-------------------------------------------+
| User | Host | Password |
+------------------+-------------------------+-------------------------------------------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | ubuntu1204-rtdho9qgryfr | |
| debian-sys-maint | localhost | *162F3C2A52506B8EE8ABE160537D7FB0B467D75A |
+------------------+-------------------------+-------------------------------------------+
ushastry@ushastry:~$ mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'ubuntu1204-rtdho9qgryfr'"
ushastry@ushastry:~$ mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user"
+------------------+-----------+-------------------------------------------+
| User | Host | Password |
+------------------+-----------+-------------------------------------------+
| root | localhost | |
| root | ushastry | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| debian-sys-maint | localhost | *162F3C2A52506B8EE8ABE160537D7FB0B467D75A |
+------------------+-----------+-------------------------------------------+
ushastry@ushastry:~$
// But noticed in the error log that it has ERROR: 1064
130702 21:59:29 InnoDB: 5.5.31 started; log sequence number 1595675
ERROR: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTE
R TABLE user ADD column Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT ' at line 1
130702 21:59:29 [ERROR] Aborting
ERROR: 1050 Table 'plugin' already exists
130702 21:59:32 [ERROR] Aborting
Imho - above are the result of mysql_upgrade script..
ushastry@ushastry:~$ more /var/lib/mysql/mysql_upgrade_info
ushastry@ushastry:~$ sudo more /var/lib/mysql/mysql_upgrade_info
5.5.31
Thanks,
Umesh
[2 Jul 2013 18:11]
Jay Pipes
Hi Umesh, I am installing MySQL server using the opscode mysql cookbook [1]. In that cookbook's server recipe [2], the installation process uses a debian preseed file to set the root user password [3] (since installation obviously needs to be a non-interactive thing), and then calls mysql_install_db [4]. Other than that, it's a standard Debian package install. So, bottom line, I'm not sure what to say. I don't know if this is an issue with the Chef cookbook or with MySQL, or with the packaging. I've filed a ticket with Opscode about this matter [5] in case it is an issue with the cookbook. I don't know what else I can give you, but I promise this is a repeatable thing... Best, -jay [1] https://github.com/opscode-cookbooks/mysql [2] https://github.com/opscode-cookbooks/mysql/blob/master/recipes/server.rb [3] https://github.com/opscode-cookbooks/mysql/blob/master/recipes/server.rb#L54 [4] https://github.com/opscode-cookbooks/mysql/blob/master/recipes/server.rb#L148 [5] http://tickets.opscode.com/browse/COOK-3270
[2 Jul 2013 18:37]
MySQL Verification Team
Hello Jay, Could you please try official Oracle packages? I want to know if it also fails for you. Thanks, Umesh
[2 Jul 2013 19:14]
Jay Pipes
Umesh, I can try to do that in a little bit, sure.
[3 Jul 2013 11:55]
MySQL Verification Team
Hi Jay, I suspect that you are hitting the defect reported in Bug #69658, Bug #62254 and Bug #62255. I just tried to give a try(hostname with mixed char case) on this and indeed it still repeatable but the only difference being it is not throwing the error. When I change hostname from MySQL5532-ClusterRepo121 to ushastry then it drops the user. Could you please check if lowercase hostname helps to see if this is the known issue? // [root@MySQL5532-ClusterRepo121 mysql-5.5.32]# scripts/mysql_install_db --defaults-file=my.cnf --user=mysql // Starting [root@MySQL5532-ClusterRepo121 mysql-5.5.32]# bin/mysqld_safe --defaults-file=my.cnf --user=mysql & [1] 491 [root@MySQL5532-ClusterRepo121 mysql-5.5.32]# 130704 15:00:36 mysqld_safe Logging to '/tmp/master/MySQL5532-ClusterRepo121.err'. 130704 15:00:36 mysqld_safe Starting mysqld daemon with databases from /tmp/master r [root@MySQL5532-ClusterRepo121 mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user" +------+--------------------------+----------+ | User | Host | Password | +------+--------------------------+----------+ | root | localhost | | | root | MySQL5532-ClusterRepo121 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | MySQL5532-ClusterRepo121 | | +------+--------------------------+----------+ [root@MySQL5532-ClusterRepo121 mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'MySQL5532-ClusterRepo121'" [root@MySQL5532-ClusterRepo121 mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user" +------+--------------------------+----------+ | User | Host | Password | +------+--------------------------+----------+ | root | localhost | | | root | MySQL5532-ClusterRepo121 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | MySQL5532-ClusterRepo121 | | +------+--------------------------+----------+ [root@MySQL5532-ClusterRepo121 mysql-5.5.32]# bin/mysql -u root --port=3306 --protocol=tcp -e "SELECT version()" +------------+ | version() | +------------+ | 5.5.32-log | +------------+ Thanks, Umesh
[3 Jul 2013 12:17]
MySQL Verification Team
I tried it on official Ubuntu packages and observed that it is indeed due to the hostname. ushastry@UsHaStry:~$ mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user" +------+-----------+----------+ | User | Host | Password | +------+-----------+----------+ | root | localhost | | | root | UsHaStry | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | UsHaStry | | +------+-----------+----------+ // With the lower case hostname it throws errors ushastry@UsHaStry:~$ mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'ushastry'" ERROR 1396 (HY000) at line 1: Operation DROP USER failed for ''@'ushastry' // with flush privileges - Errors are not visible ushastry@UsHaStry:~$ mysql -u root --port=3306 --protocol=tcp -e "FLUSH PRIVILEGES;DROP USER ''@'ushastry';FLUSH PRIVILEGES;" ushastry@UsHaStry:~$ mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user" +------+-----------+----------+ | User | Host | Password | +------+-----------+----------+ | root | localhost | | | root | UsHaStry | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | UsHaStry | | +------+-----------+----------+ // With the matching hostname "UsHaStry" it doesn't throw any errors ushastry@UsHaStry:~$ mysql -u root --port=3306 --protocol=tcp -e "DROP USER ''@'UsHaStry'" ushastry@UsHaStry:~$ mysql -u root --port=3306 --protocol=tcp -e "SELECT User, Host, Password FROM mysql.user" +------+-----------+----------+ | User | Host | Password | +------+-----------+----------+ | root | localhost | | | root | UsHaStry | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | UsHaStry | | +------+-----------+----------+ ushastry@UsHaStry:~$ mysql -u root --port=3306 --protocol=tcp -e "SELECT VERSION()" +-----------------------------+ | VERSION() | +-----------------------------+ | 5.5.31-0ubuntu0.12.04.2-log | +-----------------------------+ ushastry@UsHaStry:~$ dpkg -l | grep mysql ii libdbd-mysql-perl 4.020-1build2 Perl5 database interface to the MySQL database ii libmysqlclient18 5.5.31-0ubuntu0.12.04.2 MySQL database client library ii mysql-client-5.5 5.5.31-0ubuntu0.12.04.2 MySQL database client binaries ii mysql-client-core-5.5 5.5.31-0ubuntu0.12.04.2 MySQL database core client binaries ii mysql-common 5.5.31-0ubuntu0.12.04.2 MySQL database common files, e.g. /etc/mysql/my.cnf ii mysql-server 5.5.31-0ubuntu0.12.04.2 MySQL database server (metapackage depending on the latest version) ii mysql-server-5.5 5.5.31-0ubuntu0.12.04.2 MySQL database server binaries and system database setup ii mysql-server-core-5.5 5.5.31-0ubuntu0.12.04.2 MySQL database server binaries
[3 Jul 2013 16:01]
Jay Pipes
Hi again, Umesh, OK, so I believe we can narrow down the description of the bug report to the following: 1) If the hostname uses mixed-case, then attempting to DROP USER when NOT specifying the hostname using exact casing, a 1396 is thrown. 2) If the hostname uses mixed-case, then attempting to DROP USER when specifying the hostname using exact casing, no error is thrown, but the specified user is not actually dropped. 3) If the hostname uses mixed-case, then attempting to DROP USER when NOT specifying the hostname using exact casing, a 1396 is NOT thrown when a FLUSH PRIVILEGES statement precedes the DROP USER statement. Please confirm if the above matches your observed behaviour. Let me know if you'd prefer me to break the above 3 things into separate bugs, or stick with this one bug report for all three (depends, I suppose, on whether all three behaviours are related to the same root cause in the code... Best, -jay
[4 Jul 2013 9:00]
MySQL Verification Team
Hi Jay, Yes, your noted points matches observed behavior. IMO existing bugs(#62255, #69658 and #62254) are related but not exactly the same as none of them reported ERROR 1396. Marking as verified. Thanks, Umesh
[9 Jul 2013 13:06]
Georgi Kodinov
Even if the error is a bit different the root cause is the same as bug #62255. Closing as a duplicate.
[9 Jul 2013 22:29]
Jay Pipes
Thanks Georgi, since the original bug was reported 23 months ago, is there any chance of a fix in, say, the next year or so?
[15 Jul 2013 12:31]
Georgi Kodinov
I see this as more of an upgrade issue, since I don't see how once can reproduce the problem using user manipulation commands (CREATE USER/GRANT in specific). Obviously there's a great workaround on file too.
[15 Jul 2013 14:31]
Jay Pipes
Hi Georgi, What is the workaround on file you mentioned? Like I said in the original bug report, using manual DELETE FROM mysql.user statements won't work on systems like Galera which do not pick up row-based replication events from MyISAM tables, and only receive CREATE/DROP USER statements. I can understand that this is partly an upgrade issue, in that the Debian installation scripts seem to automatically create a user in the mysql.user table with a User of '' and a Host matching the name of the host. However, I believe it is a serious bug in the MySQL server when: a) The DROP USER statement doesn't work for users with mixed-case hostnames -- MySQL returns a 1396 error b) Issuing a FLUSH PRIVILEGES call immediately before executing a DROP USER statement for a user with mixed-case hostnames results in no error 1396 being returned by MySQL -- but the user still is not removed. In my mind, this is a data corruption bug -- since the data in the MyISAM users table cannot be altered with the standard DROP USER command -- and therefore deserves serious attention. Best, jay
