I see many questions on StackOverflow about database issues. This post aims to provide a checklist to help diagnose common issues. While this post contains PHP and MySQL code samples, this debugging checklist applies to other database platforms.

First, debugging is hard. Especially debugging database issues. Often times the best approach is to systematically rule out what cannot be the problem. This checklist adopts such an approach from a low to high level.

Can you connect to the database outside your application?

Verify you can connect to your database by logging into MySQL from the command line.

mysql -u dbuser -p -h localhost database

If you have a specific database user for your application, be sure to verify their credentials as well.

If you do not have command line access, you can use another database administration tool (e.g. PHPMyAdmin).

If you cannot connect to the database, you need to start at the beginning: Ensure MySQL is running, your database exists, and your credentials are correct.

Can you connect to the database inside your application?

Verify you can connect to the database from PHP. Test with a separate script to also rule out bugs in your codebase:

1$link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
2 
3if (!$link) {
4    die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
5}
6 
7echo 'Connected... ' . mysqli_get_host_info($link) . "\n";

If this code connects, but your application code does not, debug your application code.

If this code does not connect use the output for clues. You can also check your PHP error logs. It's likely your MySQL module is misconfigured. Use phpinfo() to review your MySQL configuration.

Does your query run successfully?

More often than not the query is the problem. Especially if the query is generated dynamically. The best way to verify your query it to output and run it yourself.

1$sql = 'SELECT column FROM table WHERE column = $bad_var';
2echo $sql;
3if (!$mysqli->query($sql)) {
4    echo 'Error: ', $mysqli->error;
5}

In this case, we'd see that $bad_var is not set. As such, the query becomes:

SELECT column FROM table WHERE column =

Note: This code above is a contrived example of a dynamic query. If you do not see what else is wrong with this query, please read about SQL injection.

You can debug in any order. Top down or bottom up. Just remember this is by no means exhaustive. Nonetheless, following this debugging checklist will help diagnose a majority of your database issues. Please share other common database debugging you use.

Find this interesting? Let's continue the conversation on Twitter.