MilesWeb
MilesWeb Hosting Forum

Please login or register.

Login with username, password and session length

 

News:

SMF - Just Installed!


Author Topic: MySQL authentication error  (Read 4896 times)

0 Members and 1 Guest are viewing this topic.

Offline johnc

  • Newbie
  • *
  • Posts: 12
  • Karma: +0/-0
    • View Profile
MySQL authentication error
« on: July 28, 2014, 06:01:45 PM »
Hi Members,

When trying to access the website it shows Error Establishing a Database Connection. No changes were made to the connection string.

On my VPS when I try to connect the database with the MySQL username set in config file, I see the error below.

Quote
ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

Anybody here who can help ?

MilesWeb Hosting Forum

MySQL authentication error
« on: July 28, 2014, 06:01:45 PM »

Offline Administrator

  • Chinmay Dingore
  • Administrator
  • Full Member
  • *****
  • Posts: 135
  • Karma: +1/-0
    • View Profile
    • MilesWeb Internet Services Pvt Ltd
Re: MySQL authentication error
« Reply #1 on: July 28, 2014, 11:41:09 PM »
Hi John,

It looks like MySQL 5.x version is still set to default old password hashes. You need to change this in my.cnf by adding the line below.

Quote
old_passwords = 1

Restart MySQL else it will keep using the old password format, which means that you cannot upgrade the passwords using the builtin PASSWORD() hashing function. Execute the command below at MySQL prompt

Quote
SELECT Length(PASSWORD('xyz'));
Quote
+-------------------------+
| Length(PASSWORD('xyz')) |
+-------------------------+
|                      16 |
+-------------------------+
1 row in set (0.00 sec)

New password hashes are 41 characters where as old are 16 characters.

You will have to change the format of all the passwords in the database to the new format. Below command will show the users with 16 OR 41 characters.

Quote
SELECT user,  Length(`Password`) FROM   `mysql`.`user`;

It will show all the MySQL users. A sample example is as below.

Quote
+----------+--------------------+
| user     | Length(`Password`) |
+----------+--------------------+
| root     |                 41 |
| root     |                 16 |
| abc_xyz    |                 16 |
| lmn_opq    |                 16 |
+----------+--------------------+

Notice here that each user can have multiple rows (one for each different host specification). To update the password for your MySQL user, execute the command at MySQL prompt.

Quote
UPDATE mysql.user SET Password = PASSWORD('MySQL-User-Password') WHERE user = 'abc_xyz';

Simply flush the privileges now & check if you can connect.

Note: Replace MySQL-User-Password with your MySQL username's password in the config file & abc_xyz with your actual MySQL username.

Let me know if the above solution helps.
« Last Edit: July 28, 2014, 11:50:36 PM by Administrator »
MilesWeb.com - Your Hosting, Our Responsibility!
Unlimited Hosting Plans | Managed VPS with 20% Discount

Offline johnc

  • Newbie
  • *
  • Posts: 12
  • Karma: +0/-0
    • View Profile
Re: MySQL authentication error
« Reply #2 on: July 28, 2014, 11:52:40 PM »
Hey Admin,

Thanks mate. I am stuck with flushing the privileges. What is the command to Flush MySQL privileges ?

Cheers. 

Offline Administrator

  • Chinmay Dingore
  • Administrator
  • Full Member
  • *****
  • Posts: 135
  • Karma: +1/-0
    • View Profile
    • MilesWeb Internet Services Pvt Ltd
Re: MySQL authentication error
« Reply #3 on: July 28, 2014, 11:53:58 PM »
Hi John,

Simply execute the command below.

Quote
FLUSH PRIVILEGES;
MilesWeb.com - Your Hosting, Our Responsibility!
Unlimited Hosting Plans | Managed VPS with 20% Discount

Offline johnc

  • Newbie
  • *
  • Posts: 12
  • Karma: +0/-0
    • View Profile
Re: MySQL authentication error
« Reply #4 on: July 29, 2014, 12:12:18 AM »
Bang on target Admin ! I followed the steps you posted. mysql connects & website works.

I must say, I asked for 2 solutions in few days on this forum. Got the solution in time. Bravo admin  8)

I have bookmarked you guys & you will get few signups from me in coming days.