What if root can’t CREATE or DROP a user?

The following situation is a bit confusing:

MariaDB [(none)]> CREATE USER 'obj_stk_sequence'@'localhost';
ERROR 1396 (HY000): Operation CREATE USER failed for 'obj_stk_sequence'@'localhost'
MariaDB [(none)]> drop user obj_stk_sequence;
ERROR 1396 (HY000): Operation DROP USER failed for 'obj_stk_sequence'@'%'
MariaDB [(none)]> select current_user;
+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Sure, if the user already exists you are unable to CREATE him, even if you’re root. And if he doesn’t exists, you can’t DROP him. But, WTF, it’s impossible that he EXISTS and NOT EXISTS at the same time!

Uhm. Maybe it’s possible… if some system tables are damaged. In fact, I’ve CHECKed all tables in `mysql` and retried, and everything was ok:

MariaDB [(none)]> create user obj_stk_sequence;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> drop user obj_stk_sequence;
Query OK, 0 rows affected (0.00 sec)

I’ve noticed that if the `mysql`.`proc` system table is corrupted, a clear error informs you about the problem. However, if you can’t CREATE and DROP something, and you’re sure that you have all the privileges you need, I suggest that you CHECK all the system tables.

To compose the query you need to CHECK all the tables, just run this:

SELECT CONCAT('CHECK TABLE ', GROUP_CONCAT(CONCAT('`mysql`.`', `TABLE_NAME`, '`') SEPARATOR ', '), ' EXTENDED;')
		FROM `information_schema`.`TABLES`
		WHERE `TABLE_SCHEMA` = 'mysql';

Then, just copy and paste the result.

Please note that you can’t do this automagically with a Stored Procedure, because the CHECK command is not supported for Prepared Statements.

Enjoy!

Advertisements

3 thoughts on “What if root can’t CREATE or DROP a user?

  1. I also noticed something like this when you have renamed the `root` user and in `mysql`.`proc` system table you have something defined by `root`… …hell will break loose 🙂

    • But I doubt that you can rename root and solve the problem with a CHECK 🙂
      In such a case, you have to rename root again. If you can’t, you need to restart the server with –skip-grant-tables option

      • You’re right… CHECK solves nothing here.
        I readded root user with rights only from localhost. That did it.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s