Bug #53071 MySQL crashes with various errors when executing CREATE TABLE ... AS SELECT
Submitted: 22 Apr 2010 16:34 Modified: 15 Jul 2010 8:13
Reporter: Sebastian Nohn Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:> 5.0.51a(5.0.90, 5.1.45) OS:Linux (CentOS 5, Ubuntu 9.10)
Assigned to:

[22 Apr 2010 16:34] Sebastian Nohn
Description:
When executing certain CREATE TABLE ... AS SELECT, MySQL > 5.0.51a crashes with message like these:

100422 18:03:52 - 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=67108864
read_buffer_size=131072
max_used_connections=2
max_connections=3000
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 6593512 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xf538790
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=0x45b20fb0, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0x45b20fb0, stack_bottom=0x45b20000, thread_stack=262144, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xf542920 = CREATE TABLE aggregation_cuc 
(
    ...
thd->thread_id=11
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Signals are 11 or 6. This happens with the following Storage Engines for the created table: MEMORY, MYISAM, INNODB (others weren't tested). This happens with Replication Running and Stopped (Server is Slave).

Sometimes, I'm also able to catch stuff on the Shell, the MySQL was started on (see attachment mysql-backtrace-mmap.txt).

The Server is a 1 Processor Intel(R) Xeon(R) CPU  X5550 @ 2.67GHz, 4 Physical Core, 8 HT Cores, 24 GB RAM.

While in Crash Scenario, the machine has definitly enough Free RAM:

# vmstat -n 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0 3958404 204536 19838580    0    0     1   256   11   17  0  0 99  0  0
 0  0      0 3709276 204544 19838576    0    0     0   129 1023  185  1  0 99  0  0
 0  0      0 3708664 204544 19838588    0    0     0    24 1011   74  0  0 100  0  0
 1  0      0 3494632 204544 19838588    0    0     0    66 1020  328  2  0 98  0  0
 1  0      0 3389492 204684 19852656    0    0     0  6610 1140  999 11  2 87  0  0
 1  0      0 3148384 204692 19856924    0    0     0  4863 1036  210 12  0 87  0  0
 0  0      0 3455908 204852 19842976    0    0     0  2466 1265 1663  8  2 90  0  0
 0  0      0 3434844 204852 19842976    0    0     0     9 1016  101  0  0 100  0  0

When running successful, for example on 5.0.51a, vmstat it looks like this:

vmstat -n 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 3425356 204876 19843212    0    0     1   256   12   17  0  0 99  0  0
 1  0      0 3008212 204884 19847172    0    0     0  4106 1035  268 12  1 87  0  0
 1  0      0 2902564 204900 19851180    0    0     0  6754 1038 4114 15  0 84  0  0
 1  0      0 2845896 204908 19855264    0    0     0  3958 1036  194 12  0 87  0  0
 1  0      0 2795312 204920 19855332    0    0     0  2050 1026 1453 15  1 84  0  0
 1  0      0 2736652 204944 19859448    0    0     0 10076 1050 4992 17  1 82  0  0
 1  0      0 2674280 204952 19863552    0    0     0  3988 1033  226 12  0 87  0  0
 1  1      0 2621828 204952 19863572    0    0     0 41262 1286  852 14  1 84  1  0
 1  0      0 2581164 204968 19867804    0    0     0 40790 1265 3833 15  1 84  1  0
 1  0      0 2529572 204992 19871792    0    0     0  6171 1041  203 12  0 87  0  0
 1  0      0 2491380 205012 19875900    0    0     0 13574 1075 4238 18  1 81  0  0
 1  0      0 2441904 205024 19880016    0    0     0  8565 1048 1205 13  1 86  0  0
 1  0      0 2393924 205044 19882656    0    0     0  4610 1042  248 12  0 87  0  0
 0  1      0 2358080 205048 19884148    0    0     0 36367 1224 4059 19  1 80  1  0

The my.cnf looks like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Ligatus Default Settings
innodb_file_per_table
innodb_data_file_path=ibdata1:500M:autoextend
set-variable = innodb_buffer_pool_size=1450M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_file_size=100M
set-variable = innodb_log_buffer_size=32M
collation_server=latin1_german2_ci
max_allowed_packet=200M
set-variable=key_buffer=64M
set-variable=query_cache_size=256M
set-variable=wait_timeout=600
set-variable=interactive_timeout=259200
set-variable=max_connections=3000
set-variable=thread_cache_size=100
lower_case_table_names=1
skip-name-resolve
slave-skip-errors=1062
connect_timeout=60
log-slow-queries=/var/lib/mysql/slow-query.log
long-query-time=2
log-slow-admin-statements
div_precision_increment=20
replicate_wild_ignore_table=xxxx.xxxx
replicate_wild_ignore_table=xxxx.xxxx
replicate_wild_ignore_table=xxxx.xxxx_%
replicate_wild_ignore_table=xxxx.xxxx_%
log-warnings
server-id = 2005
binlog_ignore_db=mysql
binlog_do_db=xxxxx
replicate-wild-do-table=xxxxx.%
master-host=xxxxx
master-user=xxxxx
master-password=xxxxx
log-bin=mysqld-bin
relay-log=mysqld-relay-bin
skip-slave-start

set-variable    = auto_increment_increment=2
set-variable    = auto_increment_offset=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
prompt=\u@\h [\d]>\_
default-character-set=utf8

To summarize:

It was tested working with

# mysql-standard-5.0.18-linux-x86_64-glibc23 on CentOS 5, x64
# mysql-5.0.45-linux-x86_64-glibc23 on CentOS 5, x64
# mysql-5.0.51a-linux-x86_64-glibc23 on CentOS 5, x64

It crashes with 

# mysql-5.0.67-linux-x86_64-glibc23 on CentOS 5, x64
# MySQL 5.0.77 x64 shipped with CentOS 5 on CentOS 5, x64
# mysql-5.0.90-linux-x86_64-glibc23 on CentOS 5, x64
# MySQL 5.1.36 x64 shipped with Ubuntu 9.10 on Ubuntu 9.10 x64
# MySQL-server-community-5.1.45-1.rhel5.x86_64.rpm on CentOS 5, x64

On the non-crashing machines it works with InnoDB, MyISAM, Memory as the created Table
On the crashing machines it crashes with InnoDB, MyISAM, Memory as the created Table

We use the same mechanism also in other SQL Scripts that work successfully on the Machines, also on Versions crashing with this particular SQL

How to repeat:
Unfortunately some queries work, a particular one doesn't work, so reproducing instructions are hard to tell, we are still working on some small reproduction instructions.
[22 Apr 2010 16:36] Sebastian Nohn
Backtrace / Memory Map on Shell

Attachment: mysql-backtrace-mmap.txt (text/plain), 9.44 KiB.

[22 Apr 2010 16:39] Valeriy Kravchuk
Please, send the results of:

uname -a

from all systems affected.
[22 Apr 2010 17:14] Sebastian Nohn
Linux hostname 2.6.18-164.15.1.el5 #1 SMP Wed Mar 17 11:30:06 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

We only installed the ubuntu machine to check if this has an influence. It didn't, so we killed that machine again.

We btw. also checked this on another machine (machine has exactly the same parameters in terms of hardware and software installed, the problem also occurs there)
[26 Apr 2010 6:33] Sveta Smirnova
Thank you for the feedback.

Please also send full CREATE TABLE ... AS SELECT statement and dump/output of SHOW CREATE TABLE and SHOW TABLE STATUS for all underlying tables.
[14 Jun 2010 15:32] Sebastian Nohn
Do you need any further information?
[15 Jun 2010 8:13] Sveta Smirnova
Thank you for the update.

Looks like I missed your previous comment while I was on vacation.

I was able to repeat crash with version 5.1.47, but version 5.1.48 does not crash. So this looks like bug fixed by one of another fixes. Please upgrade to version 5.1.48 when it become available and try it on your side so we can be absolutely sure this problem does not exist anymore.
[15 Jul 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".