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