Unable to select Database Foo using Ec2 and RDS

Question

I have set up an RDS Database Instance with a security group where I use my EC2 Elastic IP as my CIDR/IP. I have also associated the security group with my EC2.

My security group on the EC2 Instance looks like this. I associated one of the 3306 ports with my Elastic IP.

I have created a database and a table in phpMyAdmin and am trying to test it out by printing out all the values by using the code below:

<?php


    // set database server access variables:
    $host = "XXXXXXX.XXXXXXXXXX.eu-west-1.rds.amazonaws.com";
    $user = "XXXXXXX";
    $pass = "XXXXXXXX";
    $db = "XXXXXXX";    
    $con = mysql_connect($host, $user, $pass, $db);

// Check connection
if (mysql_connect_error())
  {
  echo "Failed to connect to MySQL: " . mysql_connect_error();
  }else { echo "You have connected successfully!";
}


 $result = mysql_query($con, "SELECT * FROM `XXXXX` LIMIT 0, 30 ");

echo "<p>starting again..</p>";

while($row = mysql_fetch_assoc($result)){
    //iterate over all the fields
    foreach($row as $key => $val){
        //generate output
        echo $key . ": " . $val . "<BR />";
    }
}
    mysql_close($connection);

?>

The error that I am getting is Unknown database 'XXXX'. Any ideas?

EDIT 1

I have just changed all the mysqli statements to mysql. But the connection is still not successful i.e. the database cannot be found.

EDIT 2

Here is a screenshot of my mysql privileges.

Answer 1

Unknown database 'XXXX' means the database could not be accessed. This could happen because :

1. Server is connecting successfully, but database does not exist. Try connecting without giving the database name as a parameter. Then run query show databases;. It will show all the databases there.

2. You are connecting to wrong server (you added database somewhere else database exists on other server). Checking the databases present can help you identify which server is it.

It seems that you are able to connect to the Amazon RDS server. But database is not there. Similar error would show if we try to connect to fresh installation of mysql. It has zero databases (other than information_schema and mysql).

Answer 2

First off, you should not (never) use the mysql_* extension and its function anymore. They are outdated, deprecated and will be removed from php in a near version. Use mysqli_, or PDO. See the huge red warning at the top of the mysql_connect page in the php docs for more info.

• Check that you connect to the right server

Connect to your webserver in ssh, and use the local mysql client to connect to the database server as root (you will be asked the password for the root user):

$ mysql -uroot -p -hXXXXXXX.XXXXXXXXXX.eu-west-1.rds.amazonaws.com
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 100530
Server version: 5.1.66-0+squeeze1 (Debian)

[...]

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If this work, you should end up at a mysql prompt. If this didn't work, your issue lies in your network configuration, your webserver cannot connect to the mysql server (check ports openings).

• Make sure the database in question exists on the server

  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | [... db names ...] |
  +--------------------+
  X rows in set (0.00 sec)
  
  mysql>
  

If you cannot see your database name in the list, this means the server your see in phpmyadmin is not the correct one; check your phpmyadmin settings.

• Check if you can connect with this user using the cli mysql client. Note that I do not specify a database on the connection line.

  // close the root connection
  mysql> quit
  Bye
  // connect using your specific user, replace USERNAME with your db user (and type its password when asked)
  $ mysql -uUSERNAME -p
  Enter password:
  
  [...]
  
  mysql> 
  

If this fails and you do not get to a prompt, this can be either because the permission does not exists, or because this user cannot connect from the host you specified. Look at the error message mysql will display; something like!

ERROR 1045 (28000): Access denied for user 'USERNAME'@'HOST' (using password: NO)

What is in the HOST is important and you have to make sure it is allowed in your privileges. If everything seems ok and it is still not working, try editing the privileges for that user and set the HOST to '%' (means anything is allowed). If it works with that, it means the HOST is wrongly configured in mysql's privileges.

• Check if the user can see the database in question. When you connect with a specific user, "show databases" will only list databases this user has privileges to see.

  mysql > show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | [... db names ...] |
  +--------------------+
  X rows in set (0.00 sec)
  
  mysql>
  

If you cannot see your database in the list, it means the privileges for the user are wrong / not set for this database.

If every steps here works fine, it probably means you have an error in your php code, double check the host, password, user name, ...

• If you really can't figure out what parts of the privileges are wrong, try creating a dummy user will all permissions from any host:

  $ mysql -uroot -p -hXXXXXXX.XXXXXXXXXX.eu-west-1.rds.amazonaws.com
  Enter password:
  mysql > GRANT ALL ON yourdbname.* to 'testuser'@'%' IDENTIFIED BY 'dummypassword';
  mysql > quit
  Bye
  // note that I give the password in the prompt directly, to make sure this isn't an input error just in case
  $ mysql -utestuser -pdummypassword
  mysql >
  

Once you made this user and can connect to it, try to connect to it using your php code. Once this work, delete the user (using DROP USER 'testuser'), create it again with a more limited HOST privilege and try again, until you replicated the permissions you want.

Remember to delete that user and you're done.

Answer 3

Introduction

The error Unknown database 'XXXX' means the data base does not exist and has nothing to do with your mysql privileges.

phpMyAdmin on RDS

If you created your Database via phpMyAdmin are you sure it was configured to connect to remote Amazon RDS instance or you are sill working on your localhost

Please check your config.inc.php to make sure its properly configured. You can add your remote server by calling

$i++; // This would add the new servers
$cfg['Servers'][$i]['host'] = 'XXXXXXX.XXXXXXXXXX.eu-west-1.rds.amazonaws.com';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['compress'] = TRUE;
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'XXXXXXX';
$cfg['Servers'][$i]['password'] = 'XXXXXXX';

Better DB Management

You can also use MySQL workbench which would not just allow you to create the and mange your database but you can also monitor real time. You can connect directly or use SSH

• How to create a MySQL Workbench connection to Amazon EC2 server
• Monitoring and Managing Amazon RDS Databases using MySQL Workbench

Finally

I have just changed all the mysqli statements to mysql. But the connection is still not successful i.e. the database cannot be found.

There is be no reason whatsoever you should change mysqli to mysql because it Officially Depreciated at 5.5.0

• Why shouldn't I use mysql_* functions in PHP?
• mysqli or PDO - what are the pros and cons?