MySQL Troubleshooting (2012)

Preface

I have worked since May 2006 as a principal technical support engineer in the Bugs Verification Group of the MySQL Support Group for MySQL AB, then Sun, and finally Oracle. During my daily job, I often see users who are stuck with a problem and have no idea what to do next. Well-verified methods exist to find the cause of the problem and fix it effectively, but they are hard to cull from the numerous information sources. Hundreds of great books, blog posts, and web pages describe different parts of the MySQL server in detail. But here’s where I see the difficulty: this information is organized in such a way as to explain how the MySQL server normally works, leaving out methods that can identify failures and ill-posed behavior.

When combined, these information sources explain each and every aspect of MySQL operation. But if you don’t know why your problem is occurring, you’ll probably miss the cause among dozens of possibilities suggested by the documentation. Even if you ask an expert what could be causing your problem, she can enumerate many suspects, but you still need to find the right one. Otherwise, any changes you make could just mask the real problem temporarily, or even make it worse.

It is very important to know the source of a problem, even when a change to an SQL statement or configuration option can make it go away. Knowledge of the cause or failure will arm you to overcome it permanently and prevent it from popping up again in the future.

I wrote this book to give you the methods I use constantly to identify what caused an error in an SQL application or a MySQL configuration and how to fix it.

Audience

This book is written for people who have some knowledge about MySQL. I tried to include information useful for both beginners and advanced users. You need to know SQL and have some idea of how the MySQL server works, at least from a user manual or beginner’s guide. It’s better yet if you have real experience with the server or have already encountered problems that were hard to solve.

I don’t want to repeat what is in other information sources; rather, I want to fill those gaps that I explained at the beginning of this Preface. So you’ll find guidance in this book for fixing an application, but not the details of application and server behavior. For details, consult the MySQL Reference Manual (http://dev.mysql.com/doc/refman/5.5/en/index.html).

How to Solve a Problem

This book is shaped around the goal of pursuing problems and finding causes. I step through what I would do to uncover the problem, without showing dozens of distracting details or fancy methods.

NOTE

It is very important to identify what the problem is.

For example, when saying that a MySQL installation is slow, you need to identify where it is slow: is only part of the application affected, or do all queries sent to the MySQL server run slowly? It’s also good to know whether the same installation was “slow” in the past and whether this problem is consistent or repeatable only periodically.

Another example is wrong behavior. You need to know what behaved wrongly, what results you have, and what you expected.

I have been very disciplined in presenting troubleshooting methods. Most problems can be solved in different ways, and the best solution depends on the application and the user’s needs. If I described how to go off in every direction, this book would be 10 times bigger and you would miss the fix that works for you. My purpose is to put you on the right path from the start so that you can deal quickly with each type of problem. Details about fixing the issue can be found in other information sources, many of which I cite and point you to in the course of our journey.

How This Book Is Organized

This book has seven chapters and an appendix.

Chapter 1, Basics, describes basic troubleshooting techniques that you’ll use in nearly any situation. This chapter covers only single-threaded problems, i.e., problems that are repeatable with a single connection in isolation. I start with this isolated and somewhat unrealistic setting because you will need these techniques to isolate a problem in a multithreaded application.

Chapter 2, You Are Not Alone: Concurrency Issues, describes problems that come up when applications run in multiple threads or interfere with transactions in other applications.

Chapter 3, Effects of Server Options, consists of two parts. The first is a guide to debugging and fixing a problem caused by a configuration option. The second is a reference to important options and is meant to be consulted as needed instead of being read straight through. The second part also contains recommendations on how to solve problems caused by particular options and information about how to test whether you have solved the problem. I tried to include techniques not described in other references, and to consolidate in one place all the common problems with configuration options. I also grouped them by the kind of problems, so you can easily search for the cause of your symptom.

Chapter 4, MySQL’s Environment, is about hardware and other aspects of the environment in which the server runs. This is a huge topic, but most of the necessary information is specific to operating systems and often can be solved only by the system administrator. So I list some points a MySQL database administrator (DBA) must look into. After you read this short chapter, you will know when to blame your environment and how to explain the problem to your system administrator.

Chapter 5, Troubleshooting Replication, is about problems that come up specifically in replication scenarios. I actually discuss replication issues throughout this book, but other chapters discuss the relationship between replication and other problems. This chapter is for issues that are specific to replication.

Chapter 6, Troubleshooting Techniques and Tools, describes extra techniques and tools that I skipped over or failed to discuss in detail during earlier guidelines to troubleshooting. The purpose of this chapter is to close all the gaps left in earlier chapters. You can use it as a reference if you like. I show principles first, then mention available tools. I can’t write about tools I don’t work with, so I explain the ones I personally use every day, which consequently leads to a focus on tools written by the MySQL Team and now belonging to Oracle. I do include third-party tools that help me deal with bugs and support tickets every day.

Chapter 7, Best Practices, describes good habits and behaviors for safe and effective troubleshooting. It does not describe all the best practices for designing MySQL applications, which are covered in many other sources, but instead concentrates on practices that help with problem hunting—or help prevent problems.

The Appendix A, contains a list of information sources that I use in my daily job and that can help in troubleshooting situations. Of course, some of them influenced this book, and I refer to them where appropriate.

Some Choices Made in This Book

In the past few years, many forks of MySQL were born. The most important are Percona server and MariaDB. I skipped them completely in this book because I work mostly with MySQL and simply cannot describe servers I don’t work with daily. However, because they are forks, you can use most of the methods described here. Only if you are dealing with a particular feature added in the fork will you need information specific to that product.

To conserve space and avoid introducing a whole new domain of knowledge with a lot of its own concerns, I left out MySQL Cluster problems. If you use MySQL Cluster and run into an SQL or application issue, you can troubleshoot it in much the same way as any other storage engine issue. Therefore, this book is applicable to such issues on clusters. But issues that are specific to MySQL Cluster need separate MySQL Cluster knowledge that I don’t describe here.

But I do devote a lot of space to MyISAM- and InnoDB-specific problems. This was done because they are by far the most popular storage engines, and their installation base is huge. Both also were and are default storage engines: MyISAM before version 5.5 and InnoDB since version 5.5.

A few words about examples. They were all created either specially for this book or for conferences where I have spoken about troubleshooting. Although some of the examples are based on real support cases and bug reports, all the code is new and cannot be associated with any confidential data. In a few places, I describe customer “tickets.” These are not real either. At the same time, the problems described here are real and have been seen many times, just with different code, names, and circumstances.

I tried to keep all examples as simple, understandable, and universal as possible. Therefore, I use the MySQL command-line client in most of the examples. You always have this client in the MySQL installation.

This decision also explains why I don’t describe all complications specific to particular kinds of installations; it is just impossible to cover them all in single book. Instead, I tried to give starting points that you can extend.

I have decided to use the C API to illustrate the functions discussed in this book. The choice wasn’t easy, because there are a lot of programming APIs for MySQL in various languages. I couldn’t possibly cover them all, and didn’t want to guess which ones would be popular. I realized that many of them look like the C API (many are even wrappers around the C API), so I decided that would be the best choice. Even if you are using an API with a very different syntax, such as ODBC, this section still can be useful because you will know what to look for.

A few examples use PHP. I did so because I use PHP and therefore could show real examples based on my own code. Real examples are always good to show because they reflect real-life problems that readers are likely to encounter. In addition, the MySQL API in PHP is based on the C API and uses the very same names, so readers should be able to compare it easily to C functions discussed in this book.^[1^]

I omitted JDBC and ODBC examples because these APIs are very specific. At the same time, their debugging techniques are very similar, if not always the same. Mostly the syntax is different. I decided that adding details about these two connectors might confuse readers without offering any new information about troubleshooting.^[2^]