MySQL 5.5.8

MySQL 5.x

When we use GUI tools like HeidiSQL with MySQL Windows binaries - setup from MySQL.com might be creating issues like these. Because recently faced errors #1364 and #1133 both became visible while I did clean install of MySQL by downloading Windows Installer for MySQL 5.5.8 from MySQL.com and not while using it with MySQL as part of some WAMP package.

It must be because the way some parameters come as defaults while we use MySQL installer or might be the way we configure the MySQL Instance using MySQL instance configuration wizard.

SQL Error (1133): Can't find any matching row in the user table

Now we will get above error message due to SQL_MODE variable value set to NO_AUTO_CREATE_USER, you can check value for your server's sql-mode by using following SQL:

SHOW VARIABLES LIKE 'SQL_MODE'
 

Output can be something like STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION as we can specify multiple values separated by comma. NO_AUTO_CREATE_USER won't let you create users with empty passwords.

Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified. (Added in MySQL 5.0.2)

If you're using HeidiSQL 6 or earlier you won't be able to create users even if you specify password due to the way HeidiSQL constructs the SQL to create user. See the screen-shot:

MySQL Error 1133: While Creating New User

MySQL Error 1133: While Creating New User

It seems like this won't let you create user anyway, unless you do it manually or download HediSQL 6.0.0.3606 which has this bug fixed.

The problem is not this SQL_MODE value but it's the message which is confusing and misleads user (programmer) about actual problem. I have seen bug reported for this error on MySQL bug list 2 years back and looks like it is unassigned. I know it's minor but can lead to confusion.

Solution: There are 3 ways to fix this.

A) Download latest version if you're using HeidiSQL and facing this problem with HeidiSQL.

B) Change SQL_MODE if you can't change your tool or SQL statement syntax somehow. You can either set it for SESSION if you want to fix it for a while and get away with it for now.

C) Use SQL similar to below:

CREATE USER 'digitss'@'localhost' IDENTIFIED BY 'digitss';
GRANT EXECUTE, PROCESS, SELECT, SHOW DATABASES, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, CREATE USER, FILE, LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN, SUPER ON *.* TO 'digitss'@'localhost' WITH GRANT OPTION;
 

Basically earlier version of HeidiSQL had different order for above SQL command.

Similar Posts: