Amazon AWS database connection error with valid settings

Question

I'm running both a wordpress site as well as a mediawiki off of the same web servers. The mediawiki site works great! The wordpress site, meh. Not so much. I keep getting the common database connection error:

Error establishing a database connection

And as far as I can tell the settings between the mediawiki site and the wordpress site are nearly identical.

Here's the media wiki config first since that one's working:

## Database settings
$wgLBFactoryConf['class'] = 'LBFactorySimple';
$wgDBtype = "mysql";
$wgDBservers = '';
$wgDBserver = "db.example.com";
$wgDBssl    =  true;
$wgDBname = "jfwiki";
$wgDBuser = "admin_ssl";
$wgDBpassword = "secret";

And here's what the wordpress database connection settings look like since they are not:

/** MySQL database username */
define('DB_NAME', 'jokefire');

define('DB_USER', 'admin_ssl');

/** MySQL database password */
define('DB_PASSWORD', 'secret');

/** MySQL hostname */
define('DB_HOST', 'db.example.com');

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');

/** Contact the database over a secure connection */
define('DB_SSL', true);

I realize that they're not exactly the same. But I think you can make an easy correlation between the mediawiki settings and the settings for wordpress. And they look similar enough to think that wordpress should be working. Right?

The only real other difference is the name of the database each site is using, which I guess makes sense.

But the fact that medawiki works fine tells me that the user and password set for both sites has access to the database.

Just for laughs I use the account settings from the wordpress config to demonstrate that I can connect to the DB on the command line. Again, it's the same account info that I have in the wik site:

#mysql -uadmin_ssl -p -h db.example.com -D jokefire  -e "show tables" | head -5
Enter password:
Tables_in_jokefire
wp_bp_activity
wp_bp_activity_meta
wp_bp_chat_channel_users
wp_bp_chat_channels

Also, I created a basic php script to see if it could connect to the database

<?php
$link = mysql_connect('db.example.com', 'admin_ssl', 'secret');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

And to my surprise it can't connect!

php testconnect.php
Could not connect: Access denied for user 'admin_ssl'@'ec2-54-86-143-49.compute-1.amazonaws.com' (using password: YES)

Why am I surprised that it can't? Because again 1) the wiki can connect to the database no problem. And 2) I can connect to the db on the command line using the same credentials.

My API MySQL Client version is:

Client API version  mysqlnd 5.0.10 - 20111026 - $Id: c85105d7c6f7d70d609bb4c000257868a40840ab $

Hope this helps narrow down the problem. Thanks!

Answer 1

The host name could easily be the issue.

Try select user,host from mysql.user where user='admin_ssl' order by host;

As is often the case on AWS servers, what works on localhost or 127.0.0.1 as a host is quite different than a host coming in from elsewhere.

From your PHP sample, the mysql server cannot authenticate the user and host. My bet it is the ec2-54-86-143-49.compute-1.amazonaws.com part of it.

A quick test would be to go on a mysql prompt on the server in question, and perform a create user and grant for such a user. Other people would recommend creating a 'admin_ssl'@'%' user so it works regardless of hostname coming in. I would be careful with that approach.

In order to lock down security, I am willing to create a user on AWS such as create user 'dbAdmin'@'mc83.newyork.comcastbusiness.com' IDENTIFIED BY ... to ensure I don't user wildcards like '%'.

Don't forget the grants. But that is getting ahead of the issue at hand, which is a connect.

good luck.