Search

Database Troubleshooting 101

Ever set up a site and got the following error?

Database Error: Unable to connect to the database:Could not connect to MySQL

Well there are a few common things you can do to test this and find the cause of the problem. If you are using a CMS(or Content Management System) or any sort of site that uses a database to store data, you will typically have a database configuration file of some kind.

For an example we will use the Wordpress CMS, however you should be able to follow these steps using other CMS software including (but not limited too)drupal, joomla, etc.
When using a default wordpress install you will have a file in your site's root directory(what is a site root directory?) called wp-config.php(please refer to wordpress documentation for updated info on the location of this file). You will need to open this file to view and edit your database connection strings (for other CMS's you may want to look in a directory usually called"includes" for your configuration files). To do this you can use an FTP client and a text editor, or SSH if you have access to do that and are comfortable with Linux command line, or cPanel's "file manager" if you don't have any thing else.

Once you open the file it should looks similar to this:

        <?php
        /**
         * The base configurations of the WordPress.
         *
         * This file has the following configurations: MySQL settings, Table Prefix,
         * Secret Keys, WordPress Language, and ABSPATH. You can find more information
         * by visiting {@link http://codex.wordpress.org/Editing_wp-config.php Editing
         * wp-config.php} Codex page. You can get the MySQL settings from your web host.
         *
         * This file is used by the wp-config.php creation script during the
         * installation. You don't have to use the web site, you can just copy this file
         * to "wp-config.php" and fill in the values.
         *
         * @package WordPress
         */

        // ** MySQL settings - You can get this info from your web host ** //
        /** The name of the database for WordPress */
        define('DB_NAME', 'cpanelusername_wdp1');

        /** MySQL database username */
        define('DB_USER', 'cpanelusername_wdp1');

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

        /** MySQL hostname */
        define('DB_HOST', 'localhost');

        /** 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', '');
        ...


The only lines we will need to worry about are the following:

        /** The name of the database for WordPress */
        define('DB_NAME', 'cpanelusername_wdp1');

        /** MySQL database username */
        define('DB_USER', 'cpanelusername_wdp1');

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

        /** MySQL hostname */
        define('DB_HOST', 'localhost');

If you are at all familiar with setting up databases in cpanel these should be pretty self explanatory, however I'll give a brief explanation.

define('DB_NAME', 'cpanelusername_wdp1');
DB_NAME is referring to the name of the database that was created for this site.

define('DB_USER', 'cpanelusername_wdp1');
DB_USER is referring to the user that was created to access and make changes to the database. Typically you will have to create this database user in cpanel, however if you use softaculous to install wordpress this is already done for you.

define('DB_PASSWORD', 'w38S5cze6P');
This is the password assigned to the database user.

define('DB_HOST', 'localhost');
and this is the host, or location of the mysql server you are connecting too. Midphase, and ANHosting servers host mysql locally(as in the same server as your site is hosted) therefore this should be set to localhost if you plan to use a database hosted on this server.

Ah, wow! Now that we've got most of the explaining out of the way let's get back to troubleshooting. If you end up with an error like this on your site:

Database Error: Unable to connect to the database:Could not connect to MySQL

The first thing you will want to do is make sure that your database username and password are set up correctly. This is easy with cpanel's "MySQL databases" tool.

Login to cPanel > MySQL Databases

Once there you should notice two sections. The top section starts with the "Created/Existing" databases for the account and will show you which database users are assigned to which database. And the bottom section is where you "Create DB users, add users to a database, and Remove Users".
The easiest way to find out if your database password is incorrect is by attempting to reset the password. If you select the database user from the list on the bottom, you will see a form to create a new password. Here you will be require to also put in the old password. This is where the wp-config.php file comes in handy, just use the password from there as the old password and attempt to change it a new one. If it's wrong then this tool will tell you, if in fact it is incorrect you can simply delete the user by selecting the red x to the right of the database user in the list of users. Then create a new user with that password or a new one if you wish. Finally, you will need to assign the newly created user to the database and grant all privileges(privileges may vary depending on the CMS you use check your CMS's documentation for further information).

Once you've done that you should be ready to start creating content, and manage it using your newly installed system.

Final ordered recap:
1. Find database configuration settings for your site.
2. Verify your user is both assigned to the database, and that your Password is correct.
3. Change your database password and update your configuration file.
4. Refresh, and always be blogging!
Last Updated: 5th of August, 2011     Article ID: 985

Continue
 
301 Moved Permanently

Moved Permanently

The document has moved here.


Apache Server at www.midphase.com Port 80