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.
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.