Thesql change password in session

MySQLPHP

I changed my password from old hash to a new hash using the following query

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

I found this query on https://stackoverflow.com/questions/1892607/mysql-password-hashing-method-old-vs-new. I replaced [your password] to my own password.

Now I can't log into my db again using the password I set. I was wondering is this password change forever or is the session going to expire and how long till it does.

Best Answer

According to MySQL Documentation on SET PASSWORD

The SET PASSWORD statement assigns a password to an existing MySQL user account. If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the literal text of the password should be given. If the password is specified without using either function, the password should be the already-encrypted password value as returned by PASSWORD().

With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a nonanonymous account can change the password for that account

However, you just been struck by lightning because the same webpage says

Important

SET PASSWORD may be recorded in server logs or in a history file such as ~/.mysql_history, which means that plaintext passwords may be read by anyone having read access to that information. See Section 6.1.2, “Password Security in MySQL”.

Note

If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not use the preceding SET PASSWORD or UPDATE statement without reading Section 6.1.2.3, “Password Hashing in MySQL”, first. The password format changed in MySQL 4.1, and under certain circumstances it is possible that if you change your password, you might not be able to connect to the server afterward.

Your password change is forever, so to speak.

HOW TO FIX IT

Suppose your user is myuser@localhost, you can have someone with ALL PRIVILEGES run this

UPDATE mysql.user SET password=OLD_PASSWORD('[your password]')
WHERE user='myuser' AND host='localhost';
FLUSH PRIVILEGES;

If you have no DBA but can restart mysql, then do this

service mysql restart --skip-networking --skip-grant-tables
mysql -e"UPDATE mysql.user SET password=OLD_PASSWORD('[your password]') WHERE user='myuser' AND host='localhost'"
service mysql restart

RECOMMENDATION #1

Let's presume you have a fair amount of disk space on your cloud server. You create the InnoDB table in stages to watch the effect of diskspace and RAM in slow motion.

Let's say your MyISAM table is called mydb.mytable and you want to convert it to InnoDB.

Try the following:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Play with the table.

If you like its performance, then

DROP TABLE mydb.mytable_old;

If you do not like its performance, then

TRUNCATE TABLE mydb.mytable_old;
INSERT INTO mydb.mytable_old SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_zap;
ALTER TABLE mydb.mytable_old RENAME mydb.mytable;
DROP TABLE mydb.mytable_zap;

RECOMMENDATION #2

You mentioned you are running MySQL 5.1. Unless you install the InnoDB Plugin, InnoDB is single threaded. To get InnoDB to be multithreaded:

Install MySQL 5.6
Install MySQL 5.1's InnoDB Plugin if you want to keep MySQL 5.1 (Applicable to MySQL 5.1.38+)

Once you do either one, please configure InnoDB to engage multiple cores. See my past posts:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

MYSQL – Unable to change ‘old_passwords’ variable

I have 3 additional suggestions (choose one):

Downgrade the PHP drivers (which understand the 16 character encrypted password)
Upgrade to the latest MySQL (where you can set old_password=1 from inception)
Change all the passwords in mysql.user to 41 character encrypted password format

Whatever you do, do not use the MD5 function to make new passwords. Use the PASSWORD function. It is very different from MD5:

The password function PASSWORD function is the equivalent of

SET @my_new_password = 'WhateverIWantAsThePassword';
SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@my_new_password))))) MySQLPWDHASH;

I learned that like 2 years ago from this PalominoDB Blog Post.

You can QA the PASSWORD function with OLD_PASSWORD function. Compare the output of SELECT PASSWORD(@my_new_password); WITH SELECT OLD_PASSWORD(@my_new_password);.

Best Answer

HOW TO FIX IT

Related Solutions

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

RECOMMENDATION #1

RECOMMENDATION #2

MYSQL – Unable to change ‘old_passwords’ variable

Related Question