MySql – Is there a way to reverse to use old password

MySQL

I downloaded the latest MySQL connector for .Net and I got this error "Authentication with old password no longer supported, use 4.1 style passwords."

I found a StackOverflow post on how to fix it so I stupidly logged in as root with Sqlyog and run these two quires:

SET SESSION old_passwords=FALSE;
SET PASSWORD = PASSWORD('[your password]');

Now I can't log in as root. Is there a way to reverse it back to use old_password? The root password length is 41 from 16. I can't upgrade every users password because there are other users using it and I don't want to break. What's my option here? Can I use instance config wizard to change root password?

EDIT: My server is Win2003 Standard, IIS6 and I can have admin access to the server.

Best Answer

The documentation explains how to reset the root password. The summary is to create a file containing SQL statements to set the password:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Then stop mysqld, and manually start it again, but telling it to execute this SQL file:

sudo -u mysqld mysqld_safe --init-file=/home/me/mysql-init

Related Solutions

Mysql – Upgrading PHP, MySQL old-passwords issue

Fixed. For Gods only know what reason the mysql.user table's 'password' column was a char(16). Set it to char(41), dropped the hash generated from the other DB into there and it was happy.

MySQL, is there a way to tell when was database last changed/modified

Only works for MyISAM tables

You can run a MySQL query against the information_schema table:

Example (replace dbname with your database name):

SELECT UNIX_TIMESTAMP(MAX(UPDATE_TIME)) as last_update 
FROM information_schema.tables 
WHERE TABLE_SCHEMA='dbname' 
GROUP BY TABLE_SCHEMA;

Best Answer

Related Solutions

Mysql – Upgrading PHP, MySQL old-passwords issue

MySQL, is there a way to tell when was database last changed/modified

Related Topic