Unable to log into mysql database

[doonfoot]

I have a had a MySQL database set up and working fine on X10 for 6 months.
I access it using mysqli from a php script.
4 days ago it stopped working out of the blue.
The error is 'unable to connect to MySQL. Access denied for user xxxxx'

Nothing has changed my end. I have checked the username and password for the database and they are still correct, and tried creating a new user and logging in with that, but the error persists.
I can still connect to and query the database through phpMyAdmin.

The only thing I can see on the MySQL page is that the MySQL server started up 4 days ago, which coincides with the start of my problems.

Any ideas?

Martin

 

[doonfoot]

Ok, I'm still not getting anywhere with this.
I'm sure something must have changed at the X10 end (an SQL server restart?) as nothing has changed at my end and this has been working flawlessly for months up to now.

Anyway, I have tried creating a new database with a new user to see if I can access that from mysqli, but no, same error.
Here is the code I am running to access the database. It is held in a file in my root directory on the X10 server and invoked from a remote browser request:

<?php
$mysqli = new mysqli('127.0.0.1', 'user_name', 'user_password', 'database_name');

if ($mysqli->connect_errno) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
die('Error: Failed to connect to the database server');
}

$mysqli->close();
?>

This always returns:
Error: Unable to connect to MySQL. Debugging errno: 1045 Debugging error: Access denied for user 'user_name'@'127.0.0.1' (using password: YES) Error: Failed to connect to the database server

Suggestions on a postcard please...

Martin

 

[bdistler]

Suggestions on a postcard please...

the following settings should be used:

Host/Server Name - [ localhost ]

Port - [ 3306 ] (not necessary)

Username --> Your cPanel username followed by an underscore and the MySQL username you configured in cPanel

Password --> The password that you used when creating the MySQL user

Database --> Your cPanel username followed by an underscore and the name of the database that you created in cPanel

NOTE: Free hosting accounts do not have remote MySQL access.

 

[doonfoot]

Thanks for the suggestions, but those are the settings I have been using successfully for the last 6 months.
It stopped working without warning 5 days ago.

Martin

 

[essellar]

The system is normally set up so that the literal string "localhost" is the only allowed connection address (excluding even the loopback IP address 127.0.0.1). Occasionally, there are short periods where the config/restriction set on a given server is off, and people find things working that shouldn't be working - what is actually a bug looks an awful lot like a feature, so you build stuff around it.

 

[doonfoot]

Thanks for the suggestion.

I have now tried switching from '127.0.0.1' to 'localhost', but it still won't authorise:
"Debugging errno: 1045 Debugging error: Access denied for user 'xxx_xxx'@'localhost' (using password: YES)"

Also, I believe that my php script is communicating with SQL server, but is not being authenticated. If I change from 127.0.0.1 or localhost to an incorrect address (e.g. 125.1.1.1) I get a different error: "Debugging errno: 2002 Debugging error: Connection timed out".

Martin

 

[doonfoot]

Also, is it a coincidence that my instance of SQL server, as shown on the Status page of phpMyAdmin, has been running for 4 days 23 hours, which is almost exactly when I became unable to log into the server?

Martin

 

[doonfoot]

Ok guys, still pulling my hair out over this one, and now I'm starting to talk to myself it seems. Not a good sign

So, I created a new user for my database. Still didn't work.
Then I created a new database. Still didn't work.
Then I created a whole new free X10 account, created a database in it, and BINGO! I can connect to this database using exactly the same php script that doesn't work on my original account.

So I'm going to stick my neck out here, and say that SQL server was reconfigured and restarted on my account 5 days ago, and it's been broken since. My original (not working) account is on the x10.mx domain, and the new (working) account is on x10host.com, if that makes any difference.

Can someone from x10 look into this and find the problem? Or do I just have to copy everything to my new account?

Martin

 

[Dead-i]

Hi,

I took a look at this, and the MySQL user definitely exists, but the password your PHP script is using is incorrect.

Please try re-creating your MySQL user (not a database) from within cPanel (and be sure a Password Manager isn't interfering or anything like that), then change the password in your PHP script accordingly.

I can confirm there are no known issues with MySQL on xo3 at this time.

 

[doonfoot]

Thanks.
I reset the password for my MySQL user to the same one my script was using, but that had no effect.
I removed and then re-added the user to the database, but that had no effect.
Then I did as you suggested, and completely deleted and then recreated the user with the same password as before, and that worked.
I don't know what was going on, but it's fixed now.
Thanks again for your help.

Martin