SolutionBase: Troubleshooting common MySQL problems

Working with databases like MySQL can be difficult enough when things are going well. But what do you do when things *aren't* going well and don't seem to be working the way they're supposed to? Bob Watkins outlines some troubleshooting methodologies you can use to solve common problems.

Troubleshooting is one of the most difficult tasks a database administrator or developer faces. If it's a production system, countless people are relying on you for a quick solution -- and some of them aren't shy about telling you so. Problems in development are no less important, with ambitious project deadlines looming and time slipping away.

In this article we'll cover a methodology for researching database-related issues, as well as some of the tools available in MySQL 5.0 for troubleshooting. Finally, some of the more common issues that you may encounter in working with MySQL will be described with likely causes.

Plan to troubleshoot

The most important part of troubleshooting is having a plan -- in advance. Rehearsing the steps you'll take to investigate a problem, and lining up the resources that you'll need to do so, will save you precious time when a problem does occur.

If you don't already have a separate test environment that mimics your production environment as closely as possible, create one. Without a separate test environment, all your troubleshooting efforts will impact other users of the production system. With one, your efforts are isolated.

Between incidents, test environments are important, too. You can proactively test changes to the production system before applying them, thus heading off problems in the first place. You can also use it to practice how to recover from various types of problems, so you're familiar with procedures such as restoring backups when they're needed.

A test environment for MySQL means a second installation of MySQL at the same version, and configured identically. The environment needn't include all the complexity of production, but it should be close. For example, it can often share part of its infrastructure (DNS servers, email servers, etc.) with the development environment.

Another part of your preparation is to know what tools and resources are available (see below for some ideas).

Why Not Just Copy the Data?

In the past, test environments were created by simply copying some or all of the live data from the production system to another server. But in the more closely regulated environment of Sarbanes-Oxley and HIPAA in the United States, and similar privacy legislation in other countries, doing this may be illegal for some companies. If you don't have tools for creating repeatable test data in a volume that makes for reasonable testing, now is the time to make or buy them. You don't need real data to test with; you just need realistic data.

A troubleshooting methodology

Typical steps in a troubleshooting methodology include: defining the problem, researching the symptoms, testing the hypothesis, installing the solution, and verifying the solution.

Step 1: Define the problem

What is the problem? Slow connection? No connection? An error message? Write down a specific description of the symptoms, including the text of any error messages.

Whether it's a problem you noticed yourself, or one reported by a user of the system, don't be too quick to assign a cause at this point, just document the symptoms. End users tend to describe problems by suggesting causes: they may say the server is down, when what they mean is that they personally can't connect to it.

Also, define what a successful resolution would look like.

Once you've define the problem, track it. Even in small organizations, it's important to log help requests. Over time, this information becomes a valuable knowledge base of the types of problems that tend to happen in your specific organization.

Step 2: Research

Based on the symptoms, try to determine which part of the system is involved. Is it really the MySQL database? Or is the network slow? If it helps, try eliminating things that cannot be the problem, based on the evidence.

Don't overlook search engines: if you're having a problem, chances are someone else has had it before -- and reported it online. Type in a few key words from an error message to see if a ready solution already exists.

Read the appropriate sections of the online MySQL 5.0 Reference Manual. There may be some restriction or version-related change you weren't previously aware of.

Step 3: Test

Your research may have uncovered several things that it could be. Great! Pick the most likely one and isolate that specific reason in a simple test case. Can you reproduce the error?

Try one thing at a time. If you change more than one thing, you won't know which one was really the problem. If your changes meet the criteria you set forth in Step 2 for a successful resolution, you're done with this step. Otherwise, reverse out the changes you made, pick another possible cause, and keep testing.

Step 4: Install

Once you have a solution you feel confident will work, it's time to install it into production, right? Not quite yet. Before making any production change, perform a quick risk analysis. What could go wrong with your proposed solution? Do you have a way to fix that? If everything goes horribly wrong, do you have a plan for reversing out your change completely and restoring the previous condition of the database? How long will that take, and what is the cutoff time at which you must decide to do it?

Once you know your Plan B, you can proceed with the fix. You may want to copy original scripts or damaged files (if any) to an archive directory on disk so that you can replace them later, if your fix doesn't work out.

Step 5: Verify

Run whatever tests you need to verify that the database is now working correctly, and that something else didn't break in the process.

Troubleshooting tools in MySQL 5.0

Some of the tools available in MySQL 5.0 for troubleshooting include logs, health monitors, and status variables.

MySQL logs

One of the first places to check for clues is the logs that MySQL creates as a byproduct of its operation. There are three of them: the error log, query log, and slow query log. Depending on the problem you're trying to resolve, each can provide significant information.

Most MySQL logging is turned off by default: it adds overhead, and one of MySQL's design goals is fast processing. One log, the error log, is always active. It records startups, shutdowns, and server-level errors.

The other two logs are the general query log, and the slow query log. If you're experiencing problems and want to enable them, you'll have to add lines to [mysqld] section of the my.ini file, and restart MySQL.

Once the logs are activated, you can view their contents at the operating system level, or by using the graphical MySQL Administrator program as you can see in Figure A.

Figure A

You can view MySQL Logs to troubleshoot problems.

Health monitors (MySQL Administrator). One of the tools in the graphical MySQL Administrator program is a series of displays on server health. The first tab, Connection Health, contains client connection statistics: the current number of connections, traffic in kilobytes (KB), and number of SQL queries executed. The current, minimum, maximum, and average values of each are shown as well.

Figure B

You can display MySQL server health statistics here.

The second tab, Memory Health, show similar displays that track hit ratios for the query cache (recently executed queries) and key buffer (recently used index pages).

You can add your own tabs (called pages), groups of charts, and charts to this display, and track status variables as either line or bar charts.

The third and fourth tabs in the Health section of MySQL Administrator show the current values of Status Variables and System variables, respectively. Categories are listed on the left, and the variables of the current category are on the right. Status variables show many parts of the system that are important to resolving issues, such as locking and sorting. System Variables show configuration options in effect. (A separate function in MySQL Administrator, Startup Variables, enables you to examine the my.ini file that contains the startup settings of the system variables.) Figure C shows the Status Variables screen.

Figure C

You can view MSQ Logs to troubleshoot problems.

You can interrogate these variables directly in the MySQL Command Line Client or other SQL tool. The command:


will show them all, or you can use a wildcard by adding the word LIKE:


Only the matching variables will be displayed.

Some typical problem scenarios

Two areas in which you may have problems using MySQL are connections and permissions.

Connection issues

One of the most common problem areas in MySQL is connecting to the database. The flexibility that MySQL offers in its host-based authentication, including wildcards, sometimes leads to unexpected results.

For example, assume a home-based employee uses various computers in the office when he comes in for meetings. He reports that he can no longer connect to the MySQL server. You check the mysql.user table and find he is still on file, and is allowed to connect from any IP address in the office because the Host column in his record reads 192.168.51.%. What's wrong?

Although the user table entry was set up to allow this employee to roam within the office, the computer he is trying to use may in fact be on a different subnet. If the network structure was recently modified, it may be that it no longer matches the user table. You could add another user record to cover the new subnet as well, but a better solution would be to switch to host names instead of IP addresses. The wildcard would allow him to connect from any machine in the domain, regardless of its IP address.

Permissions issues

Here are a couple of permission-related situations.

1. You've created a new user account, and then used INSERT statements to add permissions for it into the system tables in the mysql database. Yet, the user isn't able to connect or do work. You query the tables, and find all your entries are present. What's wrong?

For efficiency, MySQL loads the grant tables into memory when it starts up. It doesn't do so again until it is told to do so via the FLUSH PRIVILEGES statement. Using a GRANT, REVOKE, or SET PASSWORD command causes both the tables on disk and the copy in memory to be updated; a direct INSERT, UPDATE, or DELETE command to the tables only updates the tables. Changes made directly to the system tables aren't acted upon until they are reloaded into memory.

2. You're having trouble with a specific schema (often called a "database" in MySQL). You grant one of the developers temporary permission to SELECT from all the tables in the schema, yet she is unable to do so. What's wrong?

Grants on individual tables or other objects typically take effect immediately, but global permissions (access to all objects in a schema) don't take effect until the database is re-entered by the user. The developer needs to issue the USE databasename command to activate her permissions.

In general, admin-level privileges (access to all schemas in the running MySQL instance) take effect when the user reconnects to the server; global database privileges take effect when the user reconnects to that specific schema; and individual permissions take effect immediately. Understanding this hierarchy of permissions and when they take effect can help you diagnose permissions-related issues.

Other troubleshooting resources

MySQL Network is a subscription service offered by MySQL AB. Your subscription level can include a few support incidents per year, or unlimited ones; support during business hours only, or 24 x 7 support; and guaranteed response time from 2 days, down to 30 minutes. For mission critical applications in which the cost of downtime is significant, the subscription can pay for itself with a single support incident.

The MySQL online community is an active one. The official MySQL forums cover a wide range of topics, including installation, migration issues, and using MySQL tools. Because these are company forums, product release announcements are posted here as well. But many independent MySQL-related forums exist, such as MySQLFreaks and DevShed.

As MySQL grows in popularity, the number of books about MySQL has grown too. Coverage ranges all the way from basic tutorials such as PHP and MySQL For Dummies, 2nd Edition by Janet Valade (For Dummies, ISBN: 0764555898) up to detailed performance tuning books like High Performance MySQL by Jeremy D. Zawodny (O'Reilly, ISBN: 0596003064).

Many of these books have excellent troubleshooting resources. Appendix A of the MySQL Administrator's Guide by MySQL AB (Sams, ISBN: 0672326345) contains a number of common issues and things to check. The Definitive Guide to MySQL 5, Third Edition by Michael Kofler (Apress, ISBN: 1590595351) also contains details on pitfalls to avoid.

The Bottom Line

There's no way to avoid all problems with a complex software product, but there are ways to minimize the effects of them. Plan ahead for the inevitable and you can turn panic into productivity.