Bug #42633 | Mysql recreate user error | ||
---|---|---|---|
Submitted: | 5 Feb 2009 22:32 | Modified: | 5 Feb 2009 22:53 |
Reporter: | Marian Lander | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 5.0.67 | OS: | Linux |
Assigned to: | CPU Architecture: | Any |
[5 Feb 2009 22:32]
Marian Lander
[5 Feb 2009 22:53]
MySQL Verification Team
Thank you for the bug report. Please see below: http://dev.mysql.com/doc/refman/5.0/en/privilege-changes.html c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.78-Win x64 bzr revno 2737-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 5.0 > select host, user from mysql.user; +-----------+------+ | host | user | +-----------+------+ | 127.0.0.1 | root | | hegel | | | hegel | root | | localhost | | | localhost | root | +-----------+------+ 5 rows in set (0.00 sec) mysql 5.0 > create user 'marian'; Query OK, 0 rows affected (0.00 sec) mysql 5.0 > select host, user from mysql.user; +-----------+--------+ | host | user | +-----------+--------+ | % | marian | | 127.0.0.1 | root | | hegel | | | hegel | root | | localhost | | | localhost | root | +-----------+--------+ 6 rows in set (0.00 sec) mysql 5.0 > delete from mysql.user where user = 'marian'; Query OK, 1 row affected (0.00 sec) mysql 5.0 > flush privileges; Query OK, 0 rows affected (0.00 sec) mysql 5.0 > create user 'marian'; Query OK, 0 rows affected (0.00 sec) mysql 5.0 > create user 'pepe'; Query OK, 0 rows affected (0.00 sec) mysql 5.0 > delete from mysql.user where user = 'pepe'; Query OK, 1 row affected (0.00 sec) mysql 5.0 > create user 'pepe'; ERROR 1396 (HY000): Operation CREATE USER failed for 'pepe'@'%' mysql 5.0 > flush privileges; Query OK, 0 rows affected (0.00 sec) mysql 5.0 > create user 'pepe'; Query OK, 0 rows affected (0.00 sec) mysql 5.0 >
[28 Feb 2010 1:51]
[ name withheld ]
I run into the described behaviour with 5.0.77 (RHEL5). I created a 'auser'@'somehost' and granted him privileges. Then I granted privileges to 'auser'@'otherhost' without having created the user first. Both times I flushed privileges. So 'auser'@'otherhost' didn't work. I realised that I should have created the user before granting him rights, deleted from user where user = 'auser', and tried to create the user again. Now I got ERROR 1396 (HY000): Operation CREATE USER failed for 'auser'@'otherhost'. Flushing privileges and restarting the server didn't help. At the end it turned out that both delete users were still in the db table. "delete from db where user = 'auser'; flush privileges;" solved the problem and I could re-create the users without problems. The way I run into this is not really a bug. On the other hand, if the reporter used "drop user" instead of just deleting the user the way I did, it is a bug.