?
Solved

PHP 5.4.0 can't connect to MySQL 5.0.77

Posted on 2012-03-12
2
Medium Priority
?
1,465 Views
Last Modified: 2012-03-12
We have a shared hosting account which hosts a website's files, and a VPS which hosts the database for that website. This setup was working for a while until we decided to upgrade the shared account's version of php to version 5.4.0 (the hosting provider allows you to select which version to use). On the VPS the MySQL server version is 5.0.77 as shown below:
~$ mysql --version
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

Open in new window

As mentioned this worked fine when we were using php version 5.2.x on the shared hosting environment. But now that we have upgraded, our joomla site fails to connect to the database. Since joomla doesn't actually give you the mysql error I wrote a short php script to print out the error it's encountering and found this:
Failed mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file
 

Open in new window

this occurs on the call to mysql_connect ('server', 'user', 'pass'). So taking advice from the error message I went ahead and created a new user for the database (making sure to specify the correct host) in the fashion:
create user 'xxx'@'xxx' identified by password 'xxxhash';
grant all on xxx.* to 'xxx'@'xxx' identified by password 'xxxhash';

Open in new window

Where 'xxxhash' was replaced with the hash returned by "select password ('xxx');". Then I replaced the values in my test script on the shared hosting environment, and tried to connect again. To no avail, the same exact problem. I also just tried resetting the user's password using:
set password for 'xxx'@'xxx' = password ('xxx');

Open in new window

Also to no avail, and the same problem again. I'm really kinda stumped as to what is causing this. Can anyone shed some light?
0
Comment
Question by:ruavol2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 8

Accepted Solution

by:
fundacionrts earned 2000 total points
ID: 37712048
You need to set in my.cnf (Be aware with compatibility http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_old-passwords):
...
old_passwords=0
...

and then restart MySQL.

To detect if the password assigned to user is in old format or new format use the next sql statement:

SELECT User, Host, Length(Password) FROM mysql.user

Old Password = 16
New password = 41
Empty pasword = 0

If you detect some Ol password format, set the password to this user:

SET PASSWORD FOR User@Host=PASSWORD('userpassword');
FLUSH Privileges
0
 

Author Closing Comment

by:ruavol2
ID: 37712195
That was exactly what was needed. Thanks a ton
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question