Adding a user to MySQL with 'name'@'%' fails with ERROR 1396

I just tried to add a new user to MySQL using

CREATE USER 'name'@'%' IDENTIFIED BY '...'

However, it fails with the following error:

ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'

Adding the user just for localhost works fine:

CREATE USER 'name'@'localhost' IDENTIFIED BY '...'

I have no clue what could be the problem. I'd be grateful for any ideas.

(I'm using mysql Ver 14.14 Distrib 5.1.66.)

According to the docs if you ommit the @'hostname' (that is CREATE USER 'name') MySQL will interpret it as it had a @'%'. The error message you provided suggests that there is already a user 'name'@'%' in the system:

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'
mysql> CREATE USER 'name' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'

If you delete the user and still get the message, try running FLUSH PRIVILEGES.

Also see this SO question for additional info.

StackExchange Database Administrators Q#34940, answer score: 14