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: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 4.1.0 | OS: | Windows (Win2K SP2) |
Assigned to: | CPU Architecture: | Any |
[5 Jun 2003 6:28]
Stefan Hinz
[6 Jun 2003 13:11]
MySQL Verification Team
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.
[6 Jun 2003 15:29]
Stefan Hinz
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.
[6 Jun 2003 15:37]
Stefan Hinz
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.
[6 Jun 2003 18:16]
MySQL Verification Team
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.
[6 Jun 2003 18:41]
MySQL Verification Team
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.
[7 Jun 2003 0:10]
Stefan Hinz
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 ;-)
[7 Jun 2003 5:33]
MySQL Verification Team
Stefan thank you for the feedback. I was be able for to repeat the behavior for you reported.
[21 Jul 2003 8:15]
Sergei Golubchik
let's wait for 4.1.1 PASSWORD() as of 4.1.0 will not be supported
[12 Aug 2005 14:28]
Burak Ueda
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 :)
[6 May 2006 21:37]
Juan Manuel Trejo Sánchez
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.
[30 Jul 2008 17:33]
S Singh
Dear friend, You solved my problem even after replying after 3 years :-) As it is said "Good Work Always Pays" Thanks once again
[27 Sep 2008 8:07]
Konstantin Osipov
Needs a 4.0 and earlier client to repeat, and 4.0 is active support has ended.