MySQL Troubleshooting (2012)

Chapter 6. Troubleshooting Techniques and Tools

I have discussed many troubleshooting techniques and tools earlier in this book. Some of them were explained thoroughly, and in other cases I only touched on aspects of their use. This chapter adds details left out of previous chapters. I have tried to avoid repetition. Many techniques and tools depend on each other, so they are combined in this chapter.

There are too many tools to describe them all, so here I stick to those that I find absolutely necessary. They are mostly command-line tools that either come with the MySQL distribution or are provided as separate packages. I include a few descriptions of third-party tools, again command-line utilities. I did this not because I don’t like third-party tools, but to let you know about the great tools that come with MySQL and that you therefore always have. One of the advantages of MySQL tools is that they are always available, which is very important for our customers. Some companies have rules that prevent their employees from downloading a third-party tool. Therefore, when doing customer support, we always prefer tools that come with the MySQL distribution.

For similar reasons, I don’t describe graphical tools here. The command-line tools pose no special requirements, such as the X Window System or a particular operating system, whereas graphical tools are more demanding.

Finally, I know MySQL’s tools much better than their analogs in other packages. If you find more powerful third-party tools, use them. But knowing what can be done with simple tools is always useful.

The Query

In Chapter 1 we learned how a single query can affect performance across the whole server and how to find which one is misbehaving. Here I’ll add a few words about this type of problem.

A problematic query fired by an application can be found by checking the error logs, through the use of output functions in the application that automatically logs queries sent to the MySQL server, using a library written for such a purpose in the application, and in the general query logfile. Here I want to discuss how such logging can be tuned.

The general query log is missing some useful information that can help you debug your query, such as query execution time, information about errors and warnings, and the result set. Information about query execution time can be used to find slow queries. Of course, you can log all this special data using a custom library or by simply adding output functions everywhere in your application. But before you start tuning the application, you can still use a built-in resource: the slow query log.

Slow Query Log

The slow query log contains queries that run longer than long_query_time seconds. The default value of this variable is 10, but you can decrease it. In fact, by setting the value to zero, you can log all queries. Since version 5.1, you can turn logging to the slow log on and off dynamically as needed, just as for the general query log. You can also redirect output to a table, so it can be queried like any other.

For performance tuning, find the slowest queries, check them one by one in isolation, and then change the query statement or make other necessary changes, such as to indexes. You can start from the default long_query_time, then decrease it bit by bit to zero to find more and more queries. This method reveals the slowest queries first.

By default, this option does not log administrative statements and fast queries that don’t use indexes, but you can log such queries by setting the log-slow-admin-statements and log_queries_not_using_indexes options, respectively.

One disadvantage of the slow query log is that you can’t omit queries that you think don’t need to be optimized. Writing the log to a table can help you screen out what you don’t need to see because you can use WHERE clauses, grouping, and sorting to focus down on the queries that you think are important.

MYSQLDUMPSLOW

The mysqldumpslow utility prints the contents of the slow query log in a kind of summary format. It groups queries, so if two queries are literally the same but use different parameters, they are printed once, together with the number of execution times. This means the utility treats queries such as SELECT * FROM t2 WHERE f1=1 and SELECT * FROM t2 WHERE f1=2 as the same because the actual value of the parameter f1 usually does not affect query execution time. This is especially convenient if you want to find slow queries that use a similar pattern in an application that runs thousands of them.

$mysqldumpslow /Users/apple/Applications/mysql-5.1/data512/mysqld512-apple-slow.log

Reading mysql slow query log from

/Users/apple/Applications/mysql-5.1/data512/mysqld512-apple-slow.log

Count: 3 Time=0.03s (0s) Lock=0.03s (0s) Rows=0.7 (2), root[root]@localhost

SELECT * FROM t2 WHERE f1=N

Count: 1 Time=0.03s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost

select @@version_comment limit N

Count: 1 Time=0.02s (0s) Lock=0.03s (0s) Rows=3.0 (3), root[root]@localhost

SELECT * FROM t2

Count: 3 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.3 (1), root[root]@localhost

select TEXT from test where ID=N

Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=3.0 (3), root[root]@localhost

select * from t2

Note that although the utility is smart enough to group similar queries using different parameters, it treats queries that differ syntactically only in insignificant ways—such as using a different letter case or having different whitespace—as different queries.

Tools That Can Be Customized

Usually, just finding slow queries is not enough. You’ll want to know such things as which error or warning was returned and how many rows were updated or selected. There are three ways to get this information: through your application, by writing a plug-in, or by using a proxy.

An application can use the methods described in Getting Information About a Query to receive and log information; I won’t offer details or examples, because they depend so much on your programming language and other context. If you are interested in query execution time, just measure it in the application before and after a mysql_query or mysql_real_query call. The advantage of this method is that it is very tunable. The disadvantage is that you need to modify the application, which isn’t possible for people using third-party software.

If you want to write a MySQL server plug-in for auditing purposes, refer to the section “Writing Audit Plugins” in the MySQL Reference Manual. Once installed, a MySQL server plug-in becomes part of the MySQL server and can be accessed through SQL queries. Besides this advantage, the solution is completely independent from the application, does not need any changes to existing code, and can be used by multiple applications. The disadvantage is that it must be compiled for a specific version of the MySQL server and installed, which is not good if you distribute your plug-in to a wide audience.

The third solution is to use a scriptable proxy. A proxy is a daemon that sits between the server and client and can be configured independently from both the server and the client. Because it gets all traffic, you can do whatever you want with it. The advantages of this method are that you are completely independent from both the server and client, so you don’t need to change anything that you have inherited from other people. The disadvantage is that the proxy adds an additional layer of processing, so it will slow down the application and create a new single point of failure between the client and server.

MYSQL PROXY

MySQL Proxy is a scriptable daemon that supports the MySQL protocol and sits between the MySQL server and the application. The application should be configured in such a way that all queries go through the proxy. This generally just means setting the proper hostname and port.

MySQL Proxy supports the Lua scripting language. It allows query and result set rewriting, logging, load balancing, and much more. Here I’ll discuss only logging because that’s what will help you debug slow queries.

For auditing purposes, you need to write a Lua script that saves the necessary information. A sample script that can imitate general query log behavior and, in addition, save query execution time can look like:

function read_query( packet )

if packet:byte() == proxy.COM_QUERY then

print(os.date("%d%m%y %H:%M:%S") .. "\t"

.. proxy.connection.server.thread_id

.."\tQuery\t" .. packet:sub(2))

proxy.queries:append(1, packet )

return proxy.PROXY_SEND_QUERY

end

end

function read_query_result(inj)

print("Query execution time: " .. (inj.query_time / 1000) .. "ms,\t"

.. "Response time: " .. (inj.response_time / 1000) .. "ms,\t"

.. "Total time: " .. ((inj.query_time + inj.response_time) / 1000) .. "ms")

end

Call the script as follows:

$mysql-proxy --admin-username=admin --admin-password=foo \

--admin-lua-script=./lib/mysql-proxy/lua/admin.lua \

--proxy-address=127.0.0.1:4040 --proxy-backend-addresses=127.0.0.1:3355 \

--proxy-lua-script=`pwd`/general_log.lua

The results will look like:

$mysql-proxy --admin-username=admin --admin-password=foo \

--admin-lua-script=./lib/mysql-proxy/lua/admin.lua \

--proxy-address=127.0.0.1:4040 --proxy-backend-addresses=127.0.0.1:3355 \

--proxy-lua-script=`pwd`/general_log.lua

031111 01:51:11 20 Query show tables

Query execution time: 376.57ms, Response time: 376.612ms, Total time: 753.182ms

031111 01:51:19 20 Query select * from t1

Query execution time: 246.849ms, Response time: 246.875ms, Total time: 493.724ms

031111 01:51:27 20 Query select * from t3

Query execution time: 689.772ms, Response time: 689.801ms, Total time: 1379.573ms

031111 01:51:39 20 Query select count(*) from t4

Query execution time: 280.751ms, Response time: 280.777ms, Total time: 561.528ms

You can adapt this script to your needs. MySQL Proxy has access to the query and result set both before and after its execution, which allows you to save much more information than a simple general logfile: errors, warnings, number of affected rows, query execution time, and even the full result set.

A lot of useful scripts can also be found at MySQL Forge.

The MySQL Command-Line Interface

MySQL command-line client, also known as MySQL CLI, is the first tool you should use for testing most situations.

When queries do not work properly, the first suspect is a bug in the application. But every query can be affected by numerous issues, especially client and server options. So if you think you’re sending the correct query but getting wrong results, test it in the MySQL CLI. This is the easiest and fastest way to confirm your guess.

When members of MySQL Support Bugs Verification Group suspect client bugs or (most often) misconfigurations, we always ask for queries that we can test in the MySQL CLI. Here I’ll explain briefly why this is important and why other tools do not suit the first round of troubleshooting so well.

NOTE

Besides the regular client options, which affect every MySQL client application, Connector/J and Connector/ODBC have their own APIs and configurations. Queries can also be affected by conversion rules carried out by these interfaces. Therefore, if you use one of them, it becomes critical to test a problematic query in the MySQL CLI.

To test an application that gets results that are different from what you expect, start the MySQL CLI with the --column-type-info option, which prints information about data types:

$mysql --column-type-info test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 415

Server version: 5.1.60-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM t1;

Field 1: `f1`

Catalog: `def`

Database: `test`

Table: `t1`

Org_table: `t1`

Type: LONG

Collation: binary (63)

Length: 11

Max_length: 2

Decimals: 0

Flags: NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

Field 2: `f2`

Catalog: `def`

Database: `test`

Table: `t1`

Org_table: `t1`

Type: BLOB

Collation: latin1_swedish_ci (8)

Length: 65535

Max_length: 32

Decimals: 0

Flags: BLOB

+----+----------------------------------+

| f1 | f2 |

+----+----------------------------------+

| 1 | f9f760a2dc91dfaf1cbc95046b249a3b |

| 2 | e81077a403cc27525fdbb587451e7935 |

| 3 | a003a1256c0178e0c4d37a063ad1786b |

| 4 | 2447565c49917f2daeaac192614eabe8 |

| 6 | 6bfb21c57cc3a8de22dc4dbf635fdc77 |

| 7 | 2d9f5350ba5b914a8f4abf31b5ae975c |

| 8 | 57e87a3c55d053b5ab428f5da7f6ba28 |

| 9 | ad2ede5e02ce1da95dcd8f71426d5e7b |

| 13 | 65400ab09cdc725ec5bafe2ac4f5045d |

| 14 | 48f1b1e99041365a74444f75a6689d64 |

| 15 | 1f6c558fe2c492f1da2ebfb42d9f53dc |

| 16 | ff931f7ac8c8035a929dc35fee444332 |

| 17 | f26f6b6e8d16ae5603cf8c02409f4bb5 |

| 18 | 239ca93bf7b5fd82a53e731004cba761 |

| 19 | 1f985d1fe9efa14453a964e2c4657ab5 |

| 20 | 1d599460b91f2d892c024fe5a64f7d6d |

+----+----------------------------------+

16 rows in set (0.09 sec)

This output tells you basic meta-information about the field, such as its data type and collation—information that may be changed by configuration options or application settings. By running it in the MySQL CLI, you can see what the query would normally do on the server, and you can guess there’s a problem if the information received by the server from the application is different. Let’s examine this output line by line.

mysql> SELECT `name` FROM `t1`;

Field 1: `name`

The preceding output shows the name of a field.

Catalog: `def`

The catalog name, which is always def.

Database: `test`

The database currently being used.

Table: `t1`

The table name, but the output shows the alias of the table when you use a syntax such as select field_name from table_name as alias_name.

Org_table: `t1`

The original table name, which is useful to know if the previous line showed an alias.

Type: VAR_STRING

The preceding line shows the field type.

Collation: latin1_swedish_ci (8)

The collation.

Length: 255

The field length as defined in the table’s definition.

Max_length: 5

The length of the largest value in the field, in the result set that was returned.

Decimals: 0

The number of decimals in the field, if it is an integer type.

Flags:

Field flags, if any. For instance, a primary key field will have the flags PRI_KEY and AUTO_INCREMENT.

+-------+

| name |

+-------+

| sveta |

+-------+

1 row in set (0.00 sec)

The result set of the query.

What if your favorite MySQL client is something other than the MySQL CLI? You can test there, but remember that it may introduce side effects. This is particularly true of GUI clients. For example, if the client uses JDBC, it is affected by its configuration, which wouldn’t affect the MySQL CLI. Other clients have preconfigured character sets, such as MySQL Workbench, which supports only UTF-8. Such a setup prevents you from testing another character set. Some clients (Workbench again) disconnect and reconnect after each query. Others can be affected by small thread buffers, which is common for web-based clients. Sometimes you can reconfigure the client, but when in doubt, it’s much easier to switch to the command line and try the query in the MySQL CLI.

One of the strengths of the MySQL CLI is that it’s very transparent in regard to options: you can always see and tune its configuration. Like every piece of software, admittedly, the MySQL CLI could have a bug. But the tool is in regular, heavy use by millions of users and actively used internally at Oracle, so the chances that you’ll be affected by a bug in it is very low.

NOTE

All of the connectors in their unconfigured state, with the exception of Connector/PHP, start with character_set_client set to UTF8 and character_set_results set to NULL. This is actually a “debugging” mode regarding charsets and is not recommended in a command-line client.

The reason behind this behavior is to let the driver logic determine the best way of displaying and storing results to or from the client and to avoid “double-conversion” bugs, which are rather common, by preventing the server from converting textual results into charset results. However, this trick does not work on ad-hoc queries, such as SHOW CREATE TABLE, where it should treat BINARYas UTF8, or SELECT varbinary_col FROM some_table, where it really should be binary, or with SELECT CONCAT(char_field1, 1) AS a where a will have BINARY flag set.

Thus, all of the connectors have some sort of workaround in their connection options that tells the driver to treat function results as UTF8 instead of BINARY strings. Also, even though each connector has its own default encoding, they issue SET NAMES UTF8. This is mostly to avoid the default behavior of the libmysqlclient library, which sets all character-set-related variables to latin1.

§ If you think a query should run fine but it is giving you unexpected results, before you consider the possibility of a bug in the MySQL server code, try the query in the MySQL CLI.

NOTE

I love automation. When I create tests for bug reports, I use a script that runs MySQL Test Framework tests (see MySQL Test Framework) in a bunch of MySQL server distributions. This helps me to test a problem in many versions with single a command.

But once this habit played a bad joke on me. I tested one of the bug reports and could not repeat it. I spent a lot of time communicating with the reporter and tried many options without any luck. I relied entirely on our test suite and didn’t suspect that the client could be introducing side effects. Then my colleague tried the test case in the MySQL CLI and got the absolute same results as the original reporter. The bug was confirmed and fixed.

This experience shows how dangerous it is to ignore possible client differences and how important it is to try the MySQL CLI before anything else.

Effects of the Environment

I already discussed some effects of environments such as concurrent threads, the operating system, hardware, concurrently running software, and the MySQL server and client options in this book. But a query, even if it is running in a single client connected to a dedicated MySQL server, can also be affected by the context in which it is run.

When you call a query from a stored procedure, function, trigger, or event, these contexts can override current session options with their own defaults. Therefore, if you encounter a problem you can’t explain, try the same query outside of the routine. If the results are different, check the routine’s character set and SQL mode. Examine the body of the routine to check whether all necessary objects exist and whether a variable that can affect the query was set. Another environment variable that matters is time_zone, which affects the results of time functions such as NOW()and CURDATE().

§ If a query does not work properly, check the environment in which it was called.

Sandboxes

A sandbox is an isolated environment for running an application, where it cannot affect anything outside of that environment. Throughout this book, I’ve been encouraging you to “try” various configuration options and changes to databases. But some such “tries” can slow down the application or even crash the application or the database. This is not what most users want. Instead, you can use a sandbox to isolate the system you’re testing in its own environment, where anything you do wrong doesn’t matter.

In the MySQL world, Giuseppe Maxia introduced this term by creating a tool named the MySQL Sandbox. I will describe the MySQL Sandbox and how it can be helpful a bit later, but here I want to briefly show some variants of sandboxes.

The simplest way to safely test queries on a table is to make a copy, so that the original table is secured and can be used by the application as usual while you are be experimenting with the copy. You also won’t have to worry about reverting changes that you inadvertently make:

CREATE TABLE test_problem LIKE problem;

INSERT INTO test_problem SELECT * FROM problem;

One good thing with this solution is that you can copy just part of the data, using WHERE to limit the number of rows. For example, suppose you are testing a complex query and are sure that it is correctly executing one of its WHERE clauses. You can limit your test table to items meeting that condition when you create it, and then have a smaller table on which to test the query:

INSERT INTO test_problem SELECT FROM problem WHERE condition]

You can then simplify the query as well by removing that condition. This can save a lot of time when the original table is huge. This technique is also useful when the WHERE clause worked properly but a GROUP BY grouping or ORDER BY sort is wrong.

If a query accesses more than one table or you just want to test queries on different tables, it makes sense to create a whole separate database:

CREATE DATABASE sandbox;

USE sandbox;

CREATE TABLE problem LIKE production.problem;

INSERT INTO problem SELECT * FROM production.problem [WHERE ...]

In this case, you will have an environment absolutely reproducing your production database, but you won’t harm anything, even if you damage rows in the copy.

These two methods are good for query rewriting and similar problems. But if the server crashes or uses a lot of resources, it’s best not to test anything on it. Instead, set up a development server just for testing purposes and copy the data from the production server. This also can help if you are planning an upgrade or want to check whether a particular bug is fixed in a newer version of MySQL.

When you create an application in the first place, you can just upgrade the MySQL server on your development machine. But if the application has been running for a long time and you need to test how a particular MySQL version affects actual data, such an upgrade in a sandbox can be hard to create manually. In this case, the MySQL Sandbox is the best choice.

To create the installation in the first place, you need to have a MySQL package without an installer (such as those that end with tar.gz for Linux), of the desired version and a copy of MySQL Sandbox, available for download from https://launchpad.net/mysql-sandbox. Create the sandbox from the MySQL package with a command such as the following:

$make_sandbox mysql-5.4.2-beta-linux-x86_64-glibc23.tar.gz

unpacking /mysql-5.4.2-beta-linux-x86_64-glibc23.tar.gz

...

The MySQL Sandbox, version 3.0.05

(C) 2006,2007,2008,2009 Giuseppe Maxia

installing with the following parameters:

upper_directory = /users/ssmirnova/sandboxes

...

........ sandbox server started

Your sandbox server was installed in

$HOME/sandboxes/msb_5_4_2

Once installed, you should stop the server and change the configuration file so it corresponds to your production configuration, then restart it and load a backup of your production databases. Now you are ready to test safely. This method is very useful when you need to quickly check an application on several versions of MySQL, for example, to determine whether a bug is fixed.

You can have as many sandboxes as you want and test different aspects of MySQL and your databases without additional effort. You can even create a replication sandbox—a sandbox that contains a master server along with as many slave servers as you choose:

$make_replication_sandbox mysql-5.1.51-osx10.4-i686.tar.gz

installing and starting master

installing slave 1

installing slave 2

starting slave 1

... sandbox server started

starting slave 2

....... sandbox server started

initializing slave 1

initializing slave 2

replication directory installed in $HOME/sandboxes/rsandbox_5_1_51

$cd $HOME/sandboxes/rsandbox_5_1_51

$./m

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master [localhost] {msandbox} ((none)) > \q

Bye

$./s1

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave1 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: rsandbox

Master_Port: 26366

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1690

Relay_Log_File: mysql_sandbox26367-relay-bin.000002

Relay_Log_Pos: 1835

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1690

Relay_Log_Space: 2003

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > \q

Bye

$./stop_all

executing "stop" on slave 1

executing "stop" on slave 2

executing "stop" on master

Once the sandbox is running, experiment with its options.

One gorgeous advantage of using the MySQL Sandbox for a single server is when you need to compare many environments. If you are working with only one version of software on one type of system, you can just load production data from a backup of MySQL onto your development machine. With replication, this would not work, because you will need at least two MySQL instances. And a replication sandbox can dramatically save time, even if you don’t care about versions or custom environments, because it takes only a couple of minutes to install and set up as many MySQL instances as you need.

NOTE

Tools from Workbench Utilities set can help to create a sandbox copy of your production database.

mysqldbcopy

Copies a database, either creating a new database on the same server under a different name or placing the database on a different server with the same name or a different one

mysqlreplicate

Configures and starts replication among two servers

mysqlserverclone

Starts a new instance of a running server

Errors and Logs

Another important troubleshooting technique sounds simple: read and analyze information from the server. This is a very important step. In Chapter 1, I discussed tools that can help you get and analyze information, along with examples. Here I want to add details I skipped before.

Error Information, Again

Error messages are key and should never be ignored. You can find information about errors in the MySQL Reference Manual at http://dev.mysql.com/doc/refman/5.5/en/error-handling.html. This page lists client and server error messages, but omits messages specific to a storage engine. Nor does it explain errors that come from the operating system. Strings of information describing operating system errors can be derived through the perror utility (see Retrieving Error Strings Through perror).

Another very important tool is the mysqld error logfile, which contains information about table corruption, server crashes, replication errors, and much more. Always have it turned on, and analyze it when you encounter a problem. A log from an application cannot always replace the MySQL server error log, because the latter can contain problems and details not visible to the application.

Crashes

I discussed crashes and a general troubleshooting sequence applicable to them in When the Server Does Not Answer. Start by using the techniques described in the previous section: look in the error logfile, and analyze its content. This works in most cases, but this section discusses what to do if the error log does not contain enough information to help you troubleshoot the crash.

The latest versions of the MySQL server can print a backtrace, even in release builds. Therefore, if the error logfile does not contain a backtrace, check whether the mysqld binary is stripped.^[18^] (On Unix-style systems, the file command will report whether an executable file is stripped.) If it is, replace the mysqld binary with the one that came with your MySQL distribution. If you built it yourself, compile a version with symbols for use in testing.

§ Check whether the mysqld binary contains symbols, e.g., is not stripped.

In some cases you may need to run a debug binary. This is a file named mysqld-debug, located in the bin directory under the MySQL installation root.

The debug binary contains assertions that can help to catch the problem at an earlier stage. In this case, you probably will get a better error message because the error will be caught when the server does something wrong, rather than when a memory leak occurs. Using the release binary, you don’t get an error message until the memory leak actually leads to a crash.

The price for using debug binary is a performance decrease.

If the error logfile does not have enough information about the crash to help you find the source of the problem, try the two methods that follow. In any case, always work from evidence, as I did when discussing the backtrace from the error logfile in When the Server Does Not Answer. Don’t just make intuitive guesses, because if you try to solve a problem using a wrong guess, you can introduce even more problems.

§ Always test. Any guess can be wrong.

Core file

Core files contain the memory image of a process and are created (if the operating system is configured to do so) when a process terminates abnormally. You can obtain a core file by starting the MySQL server with the core option, but first you should make sure the operating system allows the file to be created.

To debug using a core file, you need to be acquainted with the MySQL source code. The “MySQL Internals” page on MySQL Forge is good start. I also recommend the book Expert MySQL by Dr. Charles A. Bell (Apress). You can also find useful information in the books Understanding MySQL Internals by Sasha Pachev (O’Reilly) and MySQL 5.1 Plugin Development by Andrew Hutchings and Sergei Golubchik (Packt). At some point, of course, you have to dive into the MySQL source code itself.

I won’t describe how to deal with core files here in detail, because that would require a whole book about the MySQL source code, but I will show a small example.

To enable the creation of core files, start mysqld with the core option and adjust the operating system to allow core files of unlimited size to be created. Different operating systems use different tools to control the creation of core files. For example, Solaris uses coreadm, whereas on my Mac OS X Tiger box I have to edit /etc/hostconfig. On Windows, you should have debugging symbols for both mysqld and the operating system. On Unix-style systems, the simplest method is the ulimit -c command, which should be set to unlimited, but consult your OS manual to find out if you need a configuration change somewhere else too.

After the core file is created, you can access its content using a debugger. I use gdb here, but this is not required; use your favorite debugger.

$gdb ../libexec/mysqld var/log/main.bugXXXXX/mysqld.1/data/core.21965

The command line contains the name of the gdb command followed by the path to the mysqld executable file and the path to core file itself.

GNU gdb (GDB) 7.3.1

Copyright (C) 2011 Free Software Foundation, Inc.

License GPLv3+: GNU GPL version 3 or later

<http://gnu.org/licenses/gpl.html7gt;

This is free software: you are free to change and redistribute it.

There is NO WARRANTY, to the extent permitted by law. Type "show copying"

and "show warranty" for details.

This GDB was configured as "i686-pc-linux-gnu".

For bug reporting instructions, please see:

<http://www.gnu.org/software/gdb/bugs/7gt;...

Reading symbols from /users/ssmirnova/build/mysql-5.1/libexec/mysqld...done.

[New LWP 21984]

[New LWP 21970]

[New LWP 21972]

[New LWP 21974]

[New LWP 21965]

[New LWP 21973]

[New LWP 21967]

[New LWP 21971]

[New LWP 21968]

[New LWP 21969]

warning: Can't read pathname for load map: Input/output error.

[Thread debugging using libthread_db enabled]

Core was generated by `/users/ssmirnova/build/mysql-5.1/libexec/mysqld

--defaults-group-suffix=.1 --de'.

Program terminated with signal 11, Segmentation fault.

#0 0x00832416 in __kernel_vsyscall ()

(gdb)

The backtrace is the first thing we need:

(gdb) bt

#0 0x00832416 in __kernel_vsyscall ()

#1 0x008ce023 in pthread_kill () from /lib/libpthread.so.0

#2 0x085aa6ad in my_write_core (sig=11) at stacktrace.c:310

#3 0x0824f412 in handle_segfault (sig=11) at mysqld.cc:2537

#4 7lt;signal handler called>

#5 0x084bce68 in mach_read_from_2 (b=0xfffffffe 7lt;Address 0xfffffffe out of

bounds>) at ../../storage/innobase/include/mach0data.ic:68

#6 0x084cfdd6 in rec_get_next_offs (rec=0x0, comp=1) at

../../storage/innobase/include/rem0rec.ic:278

#7 0x084e32c9 in row_search_for_mysql (buf=0xb281d7b0 "\371\001", mode=2,

prebuilt=0xb732de68, match_mode=1, direction=0) at row/row0sel.c:3727

#8 0x08476177 in ha_innobase::index_read (this=0xb281d660, buf=0xb281d7b0

"\371\001", key_ptr=0xb2822198 "", key_len=0, find_flag=HA_READ_KEY_EXACT) at

handler/ha_innodb.cc:4443

#9 0x0838f13c in handler::index_read_map (this=0xb281d660, buf=0xb281d7b0

"\371\001", key=0xb2822198 "", keypart_map=0, find_flag=HA_READ_KEY_EXACT) at

handler.h:1390

#10 0x082dd38f in join_read_always_key (tab=0xb28219e8) at sql_select.cc:11691

#11 0x082da39f in sub_select (join=0xb2822468, join_tab=0xb28219e8,

end_of_records=false) at sql_select.cc:11141

#12 0x082da79f in do_select (join=0xb2822468, fields=0xb2834954, table=0x0,

procedure=0x0) at sql_select.cc:10898

#13 0x082f1bef in JOIN::exec (this=0xb2822468) at sql_select.cc:2199

#14 0x082090db in subselect_single_select_engine::exec (this=0xb28358a0) at

item_subselect.cc:1958

<skipped>

From this output, you already have a bit of information. If you want to learn more about using core files, turn to man core, debugger documentation, the MySQL internals manual, the books I mentioned, and the source code.

General log file

Another way to catch what is going on is to use the two solutions I mentioned in Tools That Can Be Customized: the general logfile and the use of a proxy solution. As the concept is similar here, I’ll show how to catch errors with the general query log and let you deduce proxy solutions on your own if you decide to use one. I’ll use the example from When the Server Does Not Answer again, but in this case I’ll run it on my MacBook. The error log contains:

091002 16:49:48 - mysqld got signal 10 ;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help diagnose

the problem, but since we have already crashed, something is definitely wrong

and this may fail.

key_buffer_size=8384512

read_buffer_size=131072

max_used_connections=1

max_connections=100

threads_connected=1

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225784

K

This build does not print backtrace information. If I’m in a situation where I can’t use the debug version of MySQL server, how can I know what is going on?

Here is the place where the general query log can help again. MySQL writes each query to this log before executing it. Therefore, we can find information about a crash in this log. First, set up logging:

mysql> SET GLOBAL general_log=1;

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL log_output='table';

Query OK, 0 rows affected (0.00 sec)

Wait until the crash happens again, and then check the contents of the general log:

mysql> SELECT argument FROM mysql.general_log ORDER BY event_time

desc \G

*************************** 1. row ***************************

argument: Access denied for user 'MySQL_Instance_Manager'@'localhost'

(using password: YES)

*************************** 2. row ***************************

argument: select 1 from `t1` where `c0` <> (SELECT geometrycollectionfromwkb(`c3`)

FROM `t1`)

The second row in this output is the query that crashed the server.

§ Use the general query log if the error log does not contain enough information about the server crash.

The only situation in which this technique would not help is when the crash happens while the MySQL server is writing into the general query log, or even before it. You can try logging to a file instead of a table if this happens. Proxy and application-side solutions are not affected by thisissue.