Smirnova - MySQL troubleshooting: what to do when queries dont work

Copyright 2012 Sveta Smirnova

OReilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (.

Nutshell Handbook, the Nutshell Handbook logo, and the OReilly logo are registered trademarks of OReilly Media, Inc. MySQL Troubleshooting , the image of a Malayan badger, and related trade dress are trademarks of OReilly Media, Inc.

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and OReilly Media, Inc., was aware of a trademark claim, the designations have been printed in caps or initial caps.

While every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.

Solving a system problem can be one of the most frustrating experiences a systems expert can encounter. Repair of the problem or the execution of the solution is typically the easy part. Diagnosing the cause of the problem is the real challenge.

Experienced administrators have learnedsome by doing and others by trial and error that the best way to solve a problem is to use a standardized process for defining the problem, forming a list of possible causes, and then testing each until the solution is found. This may sound nave, but it generally works (although it is not sufficient for specialized systems).

MySQL is a specialized, complex, mature, and powerful database system capable of meeting the needs of a vast number of organizations. MySQL is also very easy to install and configure. Indeed, most default installations do not need to be configured or tuned at all. However, MySQL is also a system with many layers of functionality that can sometimes go awry and produce a warning or error.

Sometimes the warning or error is specific enough (or has been seen and documented enough) that a solution can be implemented immediately. Other times, and thankfully infrequently, a problem is encountered that does not have a known solution or is specific to your application, database, or environment. Finding a solution for such a warning, error, or other problem with MySQL can be a daunting task.

When encountering such an issue, database professionals typically search various resources looking for clues or at least documentation that describes a similar problem and solution. Most will find that there are simply too many references to problems that are somewhat similar or that contain suggested solutions that simply dont work or dont apply to your situation.

A fine example of this is searching the Internet using the error message as search criteria. More often than not, you will find all manner of hits, varying from archived email logs to blogs and similar commentary that may or may not refer to the error message. This often leads to a lot of wasted time and frustration. What is needed is a reference guide for how to solve problems with MySQL.

Not only does this book fulfill that need, it also establishes a protocol for solving problems that can be applied to almost any system. The methods presented are well structured, thorough, and repeatable. Combined with real-world examples, the text becomes a watershed work that defines the proper way to diagnose and repair MySQL.

Sveta uses her firsthand experiences and in-depth knowledge of MySQL and diagnostic skills to teach the reader fundamental skills to diagnose and repair almost any problem you may encounter with MySQLmaking this book a must have for any MySQL professional.

I consider myself a MySQL expert, and while my skills are backed by much experience, I wont claim to know everything there is to know about MySQL. After reading this book, I can say that Ive broadened my skills even further. If a seasoned professional like myself can benefit from reading this book, every MySQL user should read this book. More to the point, it should be considered required reading for all MySQL database administrators, consultants, and database developers.

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 heres 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 dont know why your problem is occurring, youll 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.