Bug #56793 MySQL crashed - mysqld got signal 11 with myisam-use-mmap=1
Submitted: 15 Sep 2010 12:06 Modified: 30 Sep 2010 5:07
Reporter: Daniel Gerzo Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.1.49, 5.5.5 OS:Linux (Debian 5.0, FC13)
Assigned to: CPU Architecture:Any
Tags: crash, mmap

[15 Sep 2010 12:06] Daniel Gerzo
Description:
Today, we have experienced a crash on one of our mysql servers. We didn't do anything unusual during that time. I could find only information below:

100915  0:42:58 - 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=3221225472
read_buffer_size=131072
max_used_connections=52
max_threads=250
threads_connected=45
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3188306 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x7f5498240c10
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...
stack_bottom = 0x7f54edf31e88 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x7f55f4f384d9]
/usr/sbin/mysqld(handle_segfault+0x404) [0x7f55f4c428a4]
/lib/libpthread.so.0(+0xef60) [0x7f55f44a4f60]
/lib/libc.so.6(memcpy+0xd2) [0x7f55f2f95862]
/usr/sbin/mysqld(mi_mmap_pread+0x9c) [0x7f55f4ef74bc]
/usr/sbin/mysqld(_mi_read_dynamic_record+0x22c) [0x7f55f4ef661c]
/usr/sbin/mysqld(mi_rnext_same+0x1a1) [0x7f55f4ee8511]
/usr/sbin/mysqld(ha_myisam::index_next_same(unsigned char*, unsigned char const*, unsigned int)+0x2f) [0x7f55f4edeb9f]
/usr/sbin/mysqld(+0x3df73e) [0x7f55f4cb173e]
/usr/sbin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x81) [0x7f55f4cae801]
/usr/sbin/mysqld(+0x3dd82d) [0x7f55f4caf82d]
/usr/sbin/mysqld(JOIN::exec()+0xba5) [0x7f55f4cc4635]
/usr/sbin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x142) [0x7f55f4cc0362]
/usr/sbin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x174) [0x7f55f4cc5c64]
/usr/sbin/mysqld(+0x37eb8a) [0x7f55f4c50b8a]
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x516) [0x7f55f4c54b86]
/usr/sbin/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x3fb) [0x7f55f4c5a09b]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xb34) [0x7f55f4c5abe4]
/usr/sbin/mysqld(do_command(THD*)+0xea) [0x7f55f4c5baca]
/usr/sbin/mysqld(handle_one_connection+0x235) [0x7f55f4c4d7b5]
/lib/libpthread.so.0(+0x68ba) [0x7f55f449c8ba]
/lib/libc.so.6(clone+0x6d) [0x7f55f2fe501d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x7f549828ebd0 is an invalid pointer
thd->thread_id=729887
thd->killed=NOT_KILLED
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.
100915 00:42:58 mysqld_safe Number of processes running now: 0
100915 00:42:58 mysqld_safe mysqld restarted
100915  0:42:58 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
100915  0:42:58 [Warning] option 'sort_buffer_size': unsigned value 1024 adjusted to 32776
100915  0:42:58 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
100915  0:42:58 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Log scan progressed past the checkpoint lsn 3 4160156343
100915  0:42:58  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 3 4165399040
InnoDB: Doing recovery: scanned up to log sequence number 3 4170641920
InnoDB: Doing recovery: scanned up to log sequence number 3 4171647976
100915  0:42:59  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
100915  0:43:04  InnoDB: Started; log sequence number 3 4171647976
100915  0:43:04 [Warning] 'user' entry 'root@db1.hockeyarena.net' ignored in --skip-name-resolve mode.
100915  0:43:04 [Note] Event Scheduler: Loaded 0 events
100915  0:43:04 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.49-1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)

How to repeat:
N/A

Suggested fix:
N/A
[15 Sep 2010 12:13] Valeriy Kravchuk
Looks similar to bug #51868. Do you use any partitioned MyISAM tables? Please, send your my.cnf file content also.
[15 Sep 2010 12:28] Daniel Gerzo
Hi, we do not use partitioning, most of the tables are myisam, some of them are innodb (very few). my.cnf has the following contents:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
skip-name-resolve
skip-locking

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1
#
# * Fine Tuning
#
#key_buffer		= 1G
key_buffer_size		= 3G
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 64
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
thread_concurrency	= 16
#
# * Query Cache Configuration
#
query_cache_limit	= 3M
query_cache_size	= 50M #1G
query_cache_type	= 1

# per thread
sort_buffer_size	= 1K
read_buffer_size	= 128K
read_rnd_buffer_size	= 2M
join_buffer_size	= 5M

# global
myisam_sort_buffer_size = 128M
max_heap_table_size	= 256M
tmp_table_size		= 256M
max_connections		= 250
table_cache		= 20K
table_definition_cache	= 300
wait_timeout		= 60
interactive_timeout	= 120 
back_log		= 10000
delay_key_write		= all
concurrent_insert	= 2
low_priority_updates	= 1
ft_min_word_len		= 3

innodb_buffer_pool_size	= 500M
innodb_log_file_size	= 50M
myisam_use_mmap		= 1

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
log-error		= /var/log/mysql/mysql-error.log
log_slow_queries	= /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
[15 Sep 2010 12:34] Valeriy Kravchuk
I see this setting:

myisam_use_mmap		= 1

Can you try to check if crashes of this kind are repeatable if you comment it out?

Also, please, send the results of

free
uname -a

Linux commands.
[15 Sep 2010 12:52] Daniel Gerzo
I can try disabling this option for the time being...

Please note that the myisam_use_mmap is enabled for quite some time now (weeks) and this is the first time I noticed the mysql server crashed (it happened during some task which runs every night thus the task was interrupted). However I do not claim that it didn't happen before (we just may have not noticed, because the mysqld restarted itself automatically in this case).

Here is the current output of the requested commands:

db1:/home/backup# free
             total       used       free     shared    buffers     cached
Mem:       8190840    8114768      76072          0      65944    6438308
-/+ buffers/cache:    1610516    6580324
Swap:      8388600       3536    8385064

db1:/home/backup# uname -a
Linux db1 2.6.32-5-amd64 #1 SMP Sat Jul 24 01:47:24 UTC 2010 x86_64 GNU/Linux

If you need any more input, please do not hesitate to contact me.
[15 Sep 2010 13:49] Valeriy Kravchuk
Do you know (from some application level logs maybe) what exact statement was executed when server crashed?
[15 Sep 2010 13:58] Daniel Gerzo
This is where the nighlty task has been interrupted:

00:42:58 - SQL error in script 
<br>Error executing the ohm_query:<br>
DELETE dn FROM di_new dn
LEFT JOIN commentary c
    ON dn.last_seen=c.id
WHERE
    c.date<'2010-08-15'
    OR ISNULL(c.id)
<br><br><br>Error number: 2013<br>Error type:   Lost connection to MySQL server during query

I suppose there were no other parallel queries running on the database.

This is the output of SHOW CREATE TABLE for di_new and commentary tables:

CREATE TABLE `di_new` (
  `manager_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `di_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `last_seen` int(10) unsigned NOT NULL DEFAULT '0',
  `di_type` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`manager_id`,`di_id`),
  KEY `di_type` (`di_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `commentary` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nick` varchar(15) CHARACTER SET utf8 COLLATE utf8_slovak_ci NOT NULL DEFAULT '',
  `text` text CHARACTER SET utf8 COLLATE utf8_slovak_ci NOT NULL,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `table_id` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `manager_id` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `settings` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `date` (`date`),
  KEY `table_id` (`table_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7291268 DEFAULT CHARSET=utf8
[15 Sep 2010 14:49] Valeriy Kravchuk
Please, send the results of:

explain select * FROM di_new dn
LEFT JOIN commentary c
    ON dn.last_seen=c.id
WHERE
    c.date<'2010-08-15'
    OR ISNULL(c.id);
[15 Sep 2010 18:38] Daniel Gerzo
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                      | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-------------+
|  1 | SIMPLE      | dn    | ALL    | NULL          | NULL    | NULL    | NULL                     | 60543 |             |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | hockeyarena.dn.last_seen |     1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-------------+
2 rows in set (0.03 sec)
[17 Sep 2010 17:37] MySQL Verification Team
there was another previously unsolved case..  bug #48726
[20 Sep 2010 13:53] MySQL Verification Team
testcase, crashes my 5.1.50 in seconds

Attachment: bug56793.c (text/x-csrc), 7.33 KiB.

[21 Sep 2010 18:58] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior. Please try with current version 5.1.50 and if problem still exists please check if bug is repeatable always when you run this particular DELETE query. Please also turn InnoDB Monitor and send us its output in time when server crashes.
[29 Sep 2010 8:28] MySQL Verification Team
testcase. import with --force option of client. crashed reliably on FC13, 5.1.50

Attachment: tables.sql (application/octet-stream, text), 36.47 KiB.

[30 Sep 2010 5:07] MySQL Verification Team
this is a duplicate of bug #48726
[29 Jan 2013 8:20] Abhijit Buchake
We encountered this error too. Our server is unable to start after this.
We have a mix of Innodb and MyISAM tables with majority of InnoDB tables.
We don't use myisam_use_mmap option.
Eagerly waiting for further updates.