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:

SHOW VARIABLES

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

SHOW VARAIBLES LIKE 'log%'

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
%.example.com 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.