Bug #68445 '%' feature for 'CREATE USER' statement behaves unexpectedly

• Submitted: 20 Feb 2013 21:49
• Modified: 21 Feb 2013 22:49
• Reporter: Yevgeniy Gorbatenko
• Status: Closed
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.6.10
• OS: Linux
• CPU Architecture: Any
• Tags: 5.6, create, user

[20 Feb 2013 21:49] Yevgeniy Gorbatenko

Description:
create user 'test'@'192.168.101.%' identified by 'test'; throws ERROR 1396 (OPERATION FAILED)

create user 'test'@'192.168.101%' identified by 'test'; works as expected

How to repeat:
create user 'test'@'192.168.101.%' identified by 'test';

Suggested fix:
use a work around from above

[21 Feb 2013 5:36] MySQL Verification Team

Hello Yevgeniy,

Thanks for your report, I can't reproduce your problem on reported version.

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

mysql>

mysql> create user 'test'@'192.168.101.%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user,host,password FROM mysql.user where user='test';
+------+---------------+-------------------------------------------+
| user | host          | password                                  |
+------+---------------+-------------------------------------------+
| test | 192.168.101.% | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+---------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> create user 'test'@'192.168.101%' identified by 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,password from mysql.user where user='test';
+------+---------------+-------------------------------------------+
| user | host          | password                                  |
+------+---------------+-------------------------------------------+
| test | 192.168.101.% | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| test | 192.168.101%  | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+---------------+-------------------------------------------+
2 rows in set (0.00 sec)

And if you try to create the same user again, it would fail as

mysql> create user 'test'@'192.168.101%' identified by 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'test'@'192.168.101%'

mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)

Please could you check if the user already exists?