MySQL Bugs: #594: SET PASSWORD causes error 1133

Bug #594	SET PASSWORD causes error 1133
Submitted:	5 Jun 2003 6:28	Modified:	27 Sep 2008 8:07
Reporter:	Stefan Hinz	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.0	OS:	Windows (Win2K SP2)
Assigned to:		CPU Architecture:	Any

Description:
I set up a user "test" with no password in MySQL 4.1.0.
Then I try to connect using a pre-4.1 client (mysql  Ver 13.5 Distrib 4.1.0-alpha, for Win95/Win98 (i32)). I can connect successfully.
However, when I try to change my own password (the password of the connected user "test") using SET PASSWORD, I get error 1133.
The same thing happens when I use a 4.1-client (mysql  Ver 13.5 Distrib 4.1.0-alpha, for Win95/Win98 (i32)).

How to repeat:
- Connect as the test user with your pre-4.1 client.
- Change its password: SET PASSWORD = PASSWORD('a');
That results in:
mysql> SET PASSWORD = PASSWORD('a');
ERROR 1133: Can't find any matching row in the user table
(I wasn't sure if that's a bug, but Sergei confirmed it is, and asked me to report it.)

Stefan I don't understand well, what you meant with pre-client 4.1
== 4.0.13 (for example) connecting with the server 4.1.
Please read the whole thread of the bug #464 and say me it's a similar
case.

Sorry, I mixed up the client versions in my bug report. When talking about a "pre-4.1 client", I meant a client like that one:
mysql  Ver 12.20 Distrib 4.0.13, for Win95/Win98 (i32)
Sorry for the confusion I caused.

I don't think this bug (#594) is somehow related to bug #464. Bug #464 is all about 4.0.13 (client and server from the same distribution), while bug #594 is about a client from the 4.0.13 dist and a server from the 4.1.0 dist. (But, as said, it seems to be an all-4.1.0 bug, anyway.)
The cause of the trouble seems to be the empty password that's assigned to the account ("test"@"localhost") in the first place.

I ran the 4.1.0 server and using a 4.0.13 mysql client:

C:\mysql4.0.13\bin>mysql -uroot mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.0-alpha-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL PRIVILEGES ON *.* TO test@localhost
    -> IDENTIFIED BY 'b' WITH GRANT OPTION;
Query OK, 0 rows affected (0.05 sec)

mysql> exit
Bye

C:\mysql4.0.13\bin>mysql -utest -p
Enter password: *
ERROR 1249: Client does not support authentication protocol requested by server.
 Consider upgrading MySQL client

C:\mysql4.0.13\bin>mysql -utest
ERROR 1045: Access denied for user: 'test@127.0.0.1' (Using password: NO)

The above should be considered correct.

Now setting the user in another way:

C:\mysql4.0.13\bin>mysql -uroot mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.0-alpha-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> insert into user (user,host,password) values ("testb","localhost",
    -> Password("a"));
Query OK, 1 row affected (0.05 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

C:\mysql4.0.13\bin>mysql -utestb
ERROR 1045: Access denied for user: 'testb@127.0.0.1' (Using password: NO)

C:\mysql4.0.13\bin>mysql -utestb -pa
ERROR 1249: Client does not support authentication protocol requested by server.
 Consider upgrading MySQL client

Now I inserted an user without to use the grant command and without to flush
the privileges tables:

C:\mysql4.0.13\bin>mysql -uroot mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.1.0-alpha-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> insert into user (user,host,password) values ("testc","localhost",
    -> Password("a"));
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye

C:\mysql4.0.13\bin>mysql -utestc -pa
ERROR 1045: Access denied for user: 'testc@127.0.0.1' (Using password: YES)

C:\mysql4.0.13\bin>mysql -utestc
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.1.0-alpha-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET PASSWORD = PASSWORD('a');
ERROR 1044: Access denied for user: '@127.0.0.1' to database 'mysql'
mysql> use mysql;
ERROR 1044: Access denied for user: '@127.0.0.1' to database 'mysql'

mysql> exit
Bye

You can verify above that the user testc was able for to log in when
not using password, however was denied the operation, a message different
than for you reported.

Stefan sorry I didn't complete the all options I tried in my last
post, also I tried with a blank password and in this case the 
log in was denied.
Could you please, write the sequence of commands you applied for to
get a different message error.

Miguel (you apparently closed that bug, so I can just comment):

Here is the session protocol of how you can repeat the bug:

mysql> STATUS
--------------
c:\mysql41\bin\mysql.exe  Ver 13.5 Distrib 4.1.0-alpha, for Win95/Win98 (i32)

Connection id:          1
Current database:
Current user:           superuser@localhos
SSL:                    Not in use
Server version:         4.1.0-alpha-max-nt-log
Protocol version:       10
Connection:             localhost via TCP/IP
Client characterset:    latin1_swedish_ci
Server characterset:    latin1_swedish_ci
TCP port:               3306
Uptime:                 2 min 27 sec

Threads: 1  Questions: 6  Slow queries: 2  Opens: 12  Flush tables: 1  Open t
--------------

mysql> SELECT User, Password FROM mysql.user;
+-----------+-----------------------------------------------+
| User      | Password                                      |
+-----------+-----------------------------------------------+
| superuser | *29a7f6411eedb36959d8214967e303e6c1127de1e4a5 |
+-----------+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT ALL ON *.* TO 'test'@'localhost';
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT User, Password FROM mysql.user;
+-----------+-----------------------------------------------+
| User      | Password                                      |
+-----------+-----------------------------------------------+
| superuser | *29a7f6411eedb36959d8214967e303e6c1127de1e4a5 |
| test      |                                               |
+-----------+-----------------------------------------------+
2 rows in set (0.01 sec)

mysql> EXIT
Bye

C:\mysql41\bin>mysql -utest -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.0-alpha-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET PASSWORD = PASSWORD('abc');
ERROR 1133: Can't find any matching row in the user table
mysql>

As you see, this can also happen in a 4.1-only context. Sergei said it's a bug which I take as proof that it's not a feature ;-)

Stefan thank you for the feedback.
I was be able for to repeat the behavior for you reported.

let's wait for 4.1.1
PASSWORD() as of 4.1.0 will not be supported

Hi,
I had the same problem with my MySQL database.
I am not an advanced user, and I am not even sure that my problem is related to this at all. But Error messages and situations are same:
I create a user with all privileges, give a password, and then change the password:
SET PASSWORD FOR newuser@localhost = OLD_PASSWORD("pw");
And I received same error message:
Error 1133 cannot find any matching row in the user table!

I have found a workaround, and wanted to share (probably it is already known?):
I deleted the user from the user table.
And give the all priveleges to a user which doesn't exist yet:
GRANT ALL PRIVILEGES ON *.* to newuser@localhost;
this command automaticly created a the newuser in the table with no password.

and than I operate the command:
SET PASSWORD FOR newuser@localhost = OLD_PASSWORD("pw");
and no error messages...

hope this helps someone :)

May not be a bug: I had the same issue myself. Hope this helps (three years after the post :P)

Make sure your user is attached to the host you are trying to find it in: On MySQL Administrator, on the User Administrator section, select the user, right-click and select 'Add Host from Which the User can connect' 

If for example you want your user to be user@localhost, add localhost to thelist of host the user can login in from. Otherwise, trying to use the SET PASSWORD will throw ERROR 1133, which is correct, since user@localhost doesn' exist until you attach it to localhost.

Dear friend, You solved my problem even after replying after 3 years :-)
As it is said "Good Work Always Pays"
Thanks once again

Needs a 4.0 and earlier client to repeat, and 4.0 is active support has ended.