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

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  quot MySQL user quot  is the same  quot application user quot   Complete the operation by flushing the privileges  flush privileges

User · Answer

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

User · Answer

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    quot root quot    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    quot root quot    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 gt    Resources  How to Allow Remote Connections to MySQL That s all  I hope this helps

User · Answer

grant all on newdb   to newuser localhost  identified by  password

User · Answer

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

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

Examples related to mysql

Examples related to phpmyadmin

Examples related to mysql-error-1064