Database access denied after moving from localhost to live server

[zkriszti]

Hello,

I've been trying to move a site from localhost to live environment. I have done everything step by step as Ryan suggested here. I get the following error message:

"Exception: SQLSTATE[28000] [1045] Access denied for user" (see attached image).

What I have tried / made sure about:

• my db credentials in site/config.php are OK, I have checked a thousand times
• $config->userAuthSalt untouched
• changed $config->httpHosts value (from array('localhost'); to array('mydomain.hu'); )
• I have deleted then reuploaded all site files
• .htaccess contains only the default ProcessWire directives, nothing else special
• emptied the site/assets/sessions folder
• searched this forum and Google extensively before posting...

...and still getting the error. This was just a regular localhost installation on wamp with no exotic settings like multisite, etc.

Could you please point me in the right direction? Thank you very much in advance. 

 

 

 

[cstevensjr]

14 minutes ago, zkriszti said:

my db credentials in site/config.php are OK, I have checked a thousand times

have you:

1) exported your localhost database?

2) Created a new database on the live or web hosting location?

3) Imported the localhost database into the newly created database?

4) Made changes to your site/config.php to reflect the credentials in the newly created database?

_

[EntitySelf]

Try this: 

Create a PHP file with this script (https://gist.github.com/chales/11359952), enter the right credentials and test it, it is likely not working but now you can be sure it is not PW. Try to find the correct settings for your database at your hostings provider FAQ.

[edit]Working mysqli version for PW, place this file in /site/ and test away!

<?php

# Fill our vars and run on cli
# $ php -f db-connect-test.php

define('PROCESSWIRE', 300);

class DummyConfig {

    private $data = [];

    public function __set($name, $value) {
        $this->data[$name] = $value;
    }

    public function __get($name) {
        return $this->data[$name];
    }
}

$config = new DummyConfig();
include 'config.php';

$dbhost = $config->dbHost;
$dbname = $config->dbName;
$dbuser = $config->dbUser;
$dbpass = $config->dbPass;

$connect = mysqli_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
if (!$connect) {
    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;
    exit;
}

mysqli_select_db($connect, $dbname) or die("Could not open the db '$dbname'");

$test_query = "SHOW TABLES FROM $dbname";
$result = mysqli_query($connect, $test_query);
$tblCnt = 0;

while($tbl = mysqli_fetch_array($result)) {
    $tblCnt++;
    echo $tbl[0]."<br />\n";
}

if (!$tblCnt) {
    echo "There are no tables<br />\n";
} else {
    echo "There are $tblCnt tables<br />\n";
}

 

[zkriszti]

6 minutes ago, EntitySelf said:

Try this: 

Create a PHP file with this script (https://gist.github.com/chales/11359952), enter the right credentials and test it, it is likely not working but now you can be sure it is not PW. Try to find the correct settings for your database at your hostings provider FAQ.

I get this:

" Warning: mysql_connect(): Access denied for user '...'@'[IP]' (using password: YES) in /.../dbtest.php on line 8
Unable to Connect to 'sql' "

- so in this case the problem most probably lies at the histing provider side, ami I right?

[EntitySelf]

Sorry, I made an updated version for you, check my previous post.

Yes, most likely the credentials, host name or port is incorrect.

[edit again]

You are using an IP address now, did you try using a hostname? 

127.0.0.1 = localhost

etc, I remember that this actually does matter when connecting to a DB!

[zkriszti]

6 minutes ago, EntitySelf said:

Sorry, I made an updated version for you, check my previous post.

Yes, most likely the credentials, host or port name are incorrect.

I have tried with the updated version, and I am getting the very same error message as above.

[EntitySelf]

Test yes, the connection settings are wrong, ask your hosting provider for the right ones.

[zkriszti]

14 minutes ago, EntitySelf said:

Sorry, I made an updated version for you, check my previous post.

Yes, most likely the credentials, host name or port is incorrect.

[edit again]

You are using an IP address now, did you try using a hostname? 

127.0.0.1 = localhost

etc, I remember that this actually does matter when connecting to a DB!

OK, I haven't yet seen the third version that you have posted here, but now tried with that one. It says now:

" 404 page not found (no site configuration or install.php available) "

[EntitySelf]

You get a 404 when you run my script or after you changed the IP address and opened the site?

[zkriszti]

2 minutes ago, EntitySelf said:

You get a 404 when you run my script or after you changed the IP address and opened the site?

I received 404 after having run your script (which I have placed into the site directory as you suggested).

I haven't touched IP-addresses.

Edited October 4, 2017 by zkriszti

[EntitySelf]

I am confused, what did you mean with the third version and how did you name the script file?

If you named if "dbtest.php" and upload it to 'mysite.com/site/' and then opened it from the address bar 'mysite.com/site/dbtest.php' you get a 404 error? 

[sadly]I have to go in like 10 minutes, be back +- 7 hours from now.

[abdus]

Ok, after a brief Teamviewer session, it turns out that the problem is beyond PW and @zkriszti, because through neither phpMyAdmin nor adminer could we access the mysql instance. PHP cannot communicate with MySQL server at all. Either the MySQL server does not run on the localhost, but on a different server on the network, or it's not running, which I doubt, because the hosting seems to be shared, so that would mean no service for a lot of sites. Contacting the provider seems to be the best option.

[zkriszti]

@EntitySelf @cstevensjr @abdus thank you all for taking your time trying to help me. I have contacted my hosting provider, and refer back if I get any information of interest.

[zkriszti]

OK, so the host provider told me that I had done something wrong (had chosen the wrong server) when creating the database, then just a minute later they informed me that they have corrected the error. (?) This made it possible to log in to phpMyAdmin, and import my db without any problems, but the frontend was still inaccessible giving me 404 error (as far as I got it, it was some kind of mismatch between index.php and config.php).

No magic helped there, so I deleted all the site files via FTP, then reuploaded them. Of course, here I needed to modify config.php accordingly (as I reuploaded a localhost installed site). After this step, my frontend came alive & working.

Still struggling with some minor issues, but that is for another day.  

[Violet]

I had this exact style of error message also when moving from one server to another, in my case it came from a different issue than the OP (OP's looked to be a problem with the web host). In case anyone else is also experiencing this, I found I had made a simple error: on my new host where I created a new database user, I had forgotten to add the database user as a privileged user of the database. Doing that fixed the problem: in a cPanel host, just go to Databases > MySQL databases and then "Add User to Database".  After making that change, my site was live!

Yes, it's a total facepalm moment on my part, but it IS surprisingly easy to forget after creating a new database and importing the old one into it, then creating a new db user and password, changing the equivalent credentials in site/config.php to match the new info and... it doesn't work! So when creating the database user, remember that it needs to be added as a privileged user of that database.