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.
GRANTstatement 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: Read the rest of this entry »