er_password_no_match MySQL Error#1133-Can't find any matching row in the user table




mysql show users (7)

In my case I had just renamed the Mysql user which was going to change his password on a gui based db tool (DbVisualizer). The terminal in which I tried to 'SET PASSWORD' did not work(MySQL Error #1133).

However this answer worked for me, even after changing the password the 'SET PASSWORD' command did not work yet.

After closing the terminal and opening new one the command worked very well.

Unable to set password for a user using 3.5.2.2 - phpMyAdmin for 5.5.27 - MySQL. When trying to set the password while logged onto phpMyAdmin as the user, it pops up the following error:

#1133 - Can't find any matching row in the user table

When logged on as root, following password set successfully message pops up.

SET PASSWORD FOR 'user'@'%' = PASSWORD( '***' )

In either case, password does not set and stays as it currently is, blank.



Answer #2

If you're using PHPMyAdmin you have to be logged in as root to be able to change root password. in user put root than leave password blank than change your password.


Answer #3

I encountered this issue, but in my case the password for the 'phpmyadmin' user did not match the contents of /etc/phpmyadmin/config-db.php

Once I updated the password for the 'phpmyadmin' user the error went away.

These are the steps I took:

  1. Log in to mysql as root: mysql -uroot -pYOUR_ROOT_PASS
  2. Change to the 'mysql' db: use mysql;
  3. Update the password for the 'phpmyadmin' user: UPDATE mysql.user SET Password=PASSWORD('YOUR_PASS_HERE') WHERE User='phpmyadmin' AND Host='localhost';
  4. Flush privileges: FLUSH PRIVILEGES;

DONE!! It worked for me.



Answer #5

It turns out, the error is very vague indeed!

1) Password was setting while logged on as root, as it was updating the user/password field in the users table under MySql.

2) When logged on as user, password was in fact not changing and even though there was one specified in the users table in MySql, config.inc.php file allowed authentication without password.

Solution:

Change following value to false in the config.inc.php.

$cfg['Servers'][$i]['AllowNoPassword'] = true;

So that it reads

$cfg['Servers'][$i]['AllowNoPassword'] = false;

Change user's host from Any or % to localhost in MySql users table. This could easily be achieved via phpMyAdmin console.

These two changes allowed me to authenticate as user with it's password and disallowed authentication without password.

It also allowed user to change its password while logged on as user.

Seems all permissions and the rest was fixed with these two changes.


Answer #6

This error can occur if trying to grant privileges for a non existing user.

It is not clear to me what MySQL considers a non existing user. But I suspect MySQL considers a user to exist if it can be found by a name (column User) and a host (column Host) in the user table.

If trying to grant privileges to a user that can be found with his name (column User) but not by his name and host (columns User and Host), and not provide a password, then the error occurs.

For example, the following statement triggers the error:

grant all privileges on mydb.* to [email protected]'xxx.xxx.xxx.xxx';

This is because, no password being specified, MySQL cannot create a new user, and thus tries to find an existing user. But no user with the name myuser and the host xxx.xxx.xxx.xxx can be found in the user table.

Whereas providing a password, allows the statement to be executed successfully:

grant all privileges on mydb.* to [email protected]'xxx.xxx.xxx.xxx' identified by 'mypassword';

Make sure to reuse the same password of that user you consider exists, if that new "MySQL user" is the same "application user".

Complete the operation by flushing the privileges:

flush privileges;




phpmyadmin