MySQL Bugs: #30068: Online backup might crash server

Bug #30068	Online backup might crash server
Submitted:	26 Jul 2007 14:13	Modified:	22 Aug 2007 22:28
Reporter:	Jonas Sundin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.2.4	OS:	Linux (CentOS 5)
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
Online backup query crashes and sometimes take the server with it when used with mixed tables.

[root@drdb-test1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.2.4-alpha-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT count(engine),engine FROM tables WHERE table_schema LIKE 'mediawiki' GROUP BY engine;
+---------------+--------+
| count(engine) | engine |
+---------------+--------+
|            47 | InnoDB |
|             2 | MEMORY |
|             5 | MyISAM |
+---------------+--------+
3 rows in set (0.20 sec)

mysql> BACKUP DATABASE mediawiki TO 'foobar';
ERROR 2013 (HY000): Lost connection to MySQL server during query

From /var/log/mysqld.log when the server crashes I get:

070726 15:00:31 [Note] Backup: Starting backup process
070726 15:00:31 [Note] Backup: Backing up selected databases
070726 15:00:31 - mysqld got signal 11;
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=8388600
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337607 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xa371e18
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb4b07638, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81ef3b7
0x85217fb
0x8521184
0x8520b25
0x851ffa0
0x81ff506
0x8200e45
0x81f853f
0x81f809e
0x81f72aa
0x4ea3c2db
0x4e99612e
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/refman/5.1/en/resolve-stack-dump.html
and follow instructions on how to resolve the stack trace.
Resolved stack trace is much more helpful in diagnosing the
problem, so please do resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa3944e8 = BACKUP DATABASE mediawiki TO 'foobar'
thd->thread_id=3
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
070726 15:00:31 mysqld_safe mysqld from pid file /data/mysql/mysql/drdb-test1.mysql.com.pid ended

How to repeat:
Query always crashes with:

ERROR 2013 (HY000): Lost connection to MySQL server during query

But the  server doesn't always crash.

Could you please try with latest source server. I was not able to repeat with
mixed tables database. Thanks in advance.

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.2.5-alpha-debug   |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | suse-linux-gnu      |
+-------------------------+---------------------+
5 rows in set (0.01 sec)

mysql>  BACKUP DATABASE mediawiki TO 'foobar';
+--------------------------------+
| Backup Summary                 |
+--------------------------------+
|  header     =       29 bytes   |
|  meta-data  =      350 bytes   |
|  data       = 1065353216 bytes |
|               --------------   |
|  total        1065353595 bytes |
+--------------------------------+
5 rows in set (1 min 22.85 sec)

mysql>

I tried to upgrade it and use the same compile options as we use for our release builds. so now I have:

mysql> SHOW variables LIKE "%version%";
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| protocol_version        | 10                                   |
| version                 | 5.2.5-alpha-bk20070730-debug-log     |
| version_comment         | MySQL Community Server - Debug (GPL) |
| version_compile_machine | i686                                 |
| version_compile_os      | pc-linux-gnu                         |
+-------------------------+--------------------------------------+
5 rows in set (1.48 sec)

But I still get:

mysql> BACKUP DATABASE mediawiki TO 'foobar';
ERROR 2013 (HY000): Lost connection to MySQL server during query

Backtrace looks almost the same:

thd: 0xa5f23c8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb4afd3c8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x823174e
0x867089d
0x867110a
0x867197d
0x8672043
0x8240b14
0x8247f52
0x8248e36
0x8249e89
0x8239e5c
0x4ea3c2db
0x4e99612e
New value of fp=(nil) failed sanity check, terminating stack trace!

I will try to compile it on another machine to rule out HW problems.

Maybe we are using different source tree, see your version output and the
one I printed?. Thanks.

I used the 'mysql-5.2' tree with the release build options slightly edited to:

./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --libexecdir=/usr/local/mysql/bin --with-comment="MySQL Community Server - Debug (GPL)" --with-server-suffix="-bk20070730-debug" --enable-thread-safe-client --enable-local-infile --enable-assembler --with-pic --with-fast-mutexes --with-client-ldflags="-static" --with-mysqld-ldflags="-static" --with-zlib-dir=bundled --with-debug --without-ndb-debug --with-big-tables --with-ssl --with-readline --with-embedded-server --with-archive-storage-engine --with-blackhole-storage-engine --with-ndbcluster --with-csv-storage-engine --with-example-storage-engine --with-federated-storage-engine --with-partition --with-innodb --with-extra-charsets=all

I should perhaps have removed '--with-comment=' and '--with-server-suffix=' but I wanted to keep it as close as possible to the original ones.

Thank you for the feedback. My first test I applied the BUILD/ script and now
using your configure option I am not able to repeat the crash ( I changed
just the install directories):

miguel@luar:~/dbs/5.2> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.2.5-alpha-bk20070730-debug MySQL Community Server - Debug (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "%version%";
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| protocol_version        | 10                                   |
| version                 | 5.2.5-alpha-bk20070730-debug         |
| version_comment         | MySQL Community Server - Debug (GPL) |
| version_compile_machine | x86_64                               |
| version_compile_os      | suse-linux-gnu                       |
+-------------------------+--------------------------------------+
5 rows in set (0.01 sec)

mysql>  BACKUP DATABASE mediawiki TO 'foobar';
+--------------------------------+
| Backup Summary                 |
+--------------------------------+
|  header     =       29 bytes   |
|  meta-data  =      350 bytes   |
|  data       = 1065353216 bytes |
|               --------------   |
|  total        1065353595 bytes |
+--------------------------------+
5 rows in set (2 min 24.35 sec)

mysql>

Still I am not able to repeat.

I'm also unable to repeat with the official 5.2.5-alpha on a second machine. I get an error about an InnoDB table that I need to investigate further, but I would assume this bug was due to faulty HW or similar so please feel free to close.