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

Asked 13/10, 2012 at 22:8 Answered 26/3, 2021 at 19:27

Solved mysql phpmyadmin mysql-error-1064

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.

Sender answered 13/10, 2012 at 22:8 Comment(0)

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.

Sender answered 14/10, 2012 at 19:18 Comment(1)

This is not the correct solution. Please view pdg137's answer, which OP should rather mark as accepted. – Gadhelic 16/9, 2015 at 9:21

135

I encountered this error using MySQL in a different context (not within phpMyAdmin). GRANT and SET PASSWORD commands failed on a particular existing user, who was listed in the mysql.user table. In my case, it was fixed by running

FLUSH PRIVILEGES;

The documentation for this command says

Reloads the privileges from the grant tables in the mysql database.

The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.

Apparently the user table cache had reached an inconsistent state, causing this weird error message. More information is available here.

Tangleberry answered 2/7, 2013 at 22:23 Comment(2)

I had this problem after restarting the Database, the flush command worked. Thanks! – Birdsong 3/12, 2013 at 13:50

It is important if user in mysql/user table has server one of these: localhost, 127.0.0.1, ::1, % - you need to use the same. – Binni 17/1, 2022 at 11:42

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 myuser@'xxx.xxx.xxx.xxx';

This is because, with 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 myuser@'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;

Benil answered 20/11, 2017 at 11:28 Comment(3)

I would emphasize this sentence: 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. – Crummy 27/9, 2018 at 14:41

Your second line solved my problem. But I still don't get what is the issue here: the target user and host were present in the user table but GRANT ALL ON db.* TO 'me'@'192.168.1.0/255.225.255.0' didn't work. With the password it did. – Centromere 30/12, 2019 at 10:7

When you say it works we are left wondering what was your expected result. I ban the work word from my vocabulary in software. No offense. Did you expect your grant statement to add rights to an existing user ? Or to create a new user ? – Benil 30/12, 2019 at 18:25

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:

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

DONE!! It worked for me.

Ahola answered 21/8, 2013 at 21:14 Comment(1)

It was the "FLUSH PRIVILEGES" command that worked for me. Thanks. – Trademark 13/2, 2015 at 12:59

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.

Sender answered 14/10, 2012 at 19:18 Comment(1)

This is not the correct solution. Please view pdg137's answer, which OP should rather mark as accepted. – Gadhelic 16/9, 2015 at 9:21

To expound on Stephane's answer.

I got this error when I tried to grant remote connections privileges of a particular database to a root user on MySQL server by running the command:

USE database_name;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

This gave an error:

ERROR 1133 (42000): Can't find any matching row in the user table

Here's how I fixed it:

First, confirm that your MySQL server allows for remote connections. Use your preferred text editor to open the MySQL server configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Scroll down to the bind-address line and ensure that is either commented out or replaced with 0.0.0.0 (to allow all remote connections) or replaced with Ip-Addresses that you want remote connections from.

Once you make the necessary changes, save and exit the configuration file. Apply the changes made to the MySQL config file by restarting the MySQL service:

sudo systemctl restart mysql

Next, log into the MySQL server console on the server it was installed:

mysql -u root -p

Enter your mysql user password

Check the hosts that the user you want has access to already. In my case the user is root:

SELECT host FROM mysql.user WHERE user = "root";

This gave me this output:

+-----------+
| host      |
+-----------+
| localhost |
+-----------+

Next, I ran the command below which is similar to the previous one that was throwing errors, but notice that I added a password to it this time:

USE database_name;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my-password';

Note: % grants a user remote access from all hosts on a network. You can specify the Ip-Address of the individual hosts that you want to grant the user access from using the command - GRANT ALL PRIVILEGES ON *.* TO 'root'@'Ip-Address' IDENTIFIED BY 'my-password';

Afterwhich I checked the hosts that the user now has access to. In my case the user is root:

SELECT host FROM mysql.user WHERE user = "root";

This gave me this output:

+-----------+
| host      |
+-----------+
| %         |
| localhost |
+-----------+

Finally, you can try connecting to the MySQL server from another server using the command:

mysql -u username -h mysql-server-ip-address -p

Where u represents user, h represents mysql-server-ip-address and p represents password. So in my case it was:

mysql -u root -h 34.69.261.158 -p

Enter your mysql user password

You should get this output depending on your MySQL server version:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Resources: How to Allow Remote Connections to MySQL

That's all.

I hope this helps

Whittier answered 5/10, 2020 at 21:23 Comment(0)

grant all on newdb.* to newuser@localhost identified by 'password';

Si answered 28/8, 2017 at 4:23 Comment(1)

Yes, been there – Carducci 10/12, 2017 at 15:9

For someone who is doing a mistake like me.

I was using command

SET PASSWORD FOR root = 'elephant7'

to update the password for the root user and I was getting the same error. I tried every thing mention above in all the answer but I got no success then after searching a bit I found out the ans,

MySQL account names consist of a user name and a host name. This enables creation of accounts for users with the same name who can connect from different hosts.

on https://dba.stackexchange.com/

and then I used the command

SET PASSWORD FOR 'root'@'localhost'=PASSWORD('mynewpasword');

here 'root'@'localhost' did the trick and the command worked.

Hope this be beneficial for someone.

Thurber answered 26/3, 2021 at 19:27 Comment(0)

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.

Patristic answered 15/8, 2013 at 17:38 Comment(0)

-1

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.

Reactant answered 24/3, 2017 at 9:22 Comment(0)

-1

I think the answer is here now : https://bugs.mysql.com/bug.php?id=83822

So, you should write :

GRANT ALL PRIVILEGES ON mydb.* to myuser@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'mypassword';

And i think that could be work :

SET PASSWORD FOR myuser@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'old_password' = PASSWORD('new_password');

Quinquennial answered 31/1, 2018 at 10:51 Comment(2)

When answering you are encouraged attempt to explain what you are doing and why the solution works, especially when it comes to 5 year-old questions. – Cymograph 31/1, 2018 at 11:19

doesn't explain whether x should be placeholder or not, and if it isn't a placeholder, it doesn't generally solve the problem for dynamic ip accesses. – Tripos 25/3, 2018 at 0:21

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags