| Bug #65096 | MyISAM Table Marked as Crashed During Query From INFORMATION_SCHEMA | ||
|---|---|---|---|
| Submitted: | 25 Apr 2012 1:44 | Modified: | 22 Dec 2012 13:34 |
| Reporter: | Jervin R | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S1 (Critical) |
| Version: | 5.1.62 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | information_schema, marked as crashed, myisam | ||
[25 Apr 2012 1:44]
Jervin R
Forgot to add link to MariaDB bug https://bugs.launchpad.net/maria/+bug/925377
[25 Apr 2012 9:00]
Valeriy Kravchuk
I can not repeat (with test case from MariaDB bug at least) on 5.1.63.
[25 Apr 2012 17:59]
Sveta Smirnova
Thank you for the report. I can not repeat it with test case provided too. Have you tested this issue with our binaries available from dev.mysql.com/downloads or only used MariaDB?
[25 Apr 2012 22:46]
Jervin R
Sveta, Yes - 5.1.54, 5.1.57 and 5.1.62 (i.e. the tarbal packages mysql-5.1.62-linux-x86_64-glibc23) - do you need anything else to reproduce? I just ran it again on 5.1.62 with clean datadir and here is my log: [revin@railsvm ~]$ cat /var/log/mysqld.log 120425 18:44:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 120425 18:44:22 [Note] Plugin 'FEDERATED' is disabled. 120425 18:44:22 InnoDB: Initializing buffer pool, size = 276.0M 120425 18:44:22 InnoDB: Completed initialization of buffer pool InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 120425 18:44:22 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 120425 18:44:22 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 120425 18:44:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 120425 18:44:22 InnoDB: Started; log sequence number 0 0 120425 18:44:22 [Note] Event Scheduler: Loaded 0 events 120425 18:44:22 [Note] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: ready for connections. Version: '5.1.62' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 120425 18:44:45 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:45 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:44:46 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:44:47 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:48 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:48 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:50 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:44:50 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:51 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:51 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:44:51 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:51 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:44:52 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:54 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:54 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:44:55 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:56 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:44:56 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:57 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:59 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:44:59 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:45:00 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:45:00 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:45:01 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:45:03 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:45:04 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:45:05 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer0' is marked as crashed and should be repaired 120425 18:45:05 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:45:05 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:45:06 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired 120425 18:45:07 [ERROR] /home/revin/Downloads/mysql-5.1.62-linux-x86_64-glibc23/bin/mysqld: Table './test/customer1' is marked as crashed and should be repaired
[26 Apr 2012 13:00]
Jervin R
Build 5.1.57 with --with-debug=full and here is a relevant trace.
Attachment: 65096-full-debug.txt (text/plain), 17.07 KiB.
[30 Apr 2012 14:08]
MySQL Verification Team
I got a segfault on 5.1.62. Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0x7fffed46c700 (LWP 23413)] __strcmp_sse2 () at ../sysdeps/x86_64/strcmp.S:213 213 movlpd (%rdi), %xmm1 (gdb) bt #0 __strcmp_sse2 () at ../sysdeps/x86_64/strcmp.S:213 #1 in check_table_is_closed at mi_dbug.c:183 #2 in mi_delete_table at mi_delete_table.c:68 #3 in ha_myisam::delete_table at ha_myisam.cc:1903 #4 in handler::drop_table at handler.cc:3105 #5 in handler::ha_drop_table at handler.cc:3420 #6 in free_tmp_table at sql_select.cc:10937 #7 in close_thread_tables at sql_base.cc:1281 #8 in dispatch_command at sql_parse.cc:1699 #9 in do_command at sql_parse.cc:893 #10 in handle_one_connection at sql_connect.cc:1454 #11 in start_thread at pthread_create.c:301 #12 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115 My testcase is a bit more nasty though. 30 threads running over 255 tables: i|1|set global table_open_cache=400 i|1|set global sync_frm=0 i|1000|drop table if exists `t?`|tinyint i|1000|create table if not exists `t?`(a int key,b int,key (a),key(b))engine=myisam|tinyint i|4000|insert ignore into `t?` values (?,?)|tinyint,int,int n|1|alter table `t?` disable keys|tinyint n|1|alter table `t?` enable keys|tinyint n|1..5|SELECT table_schema "name",sum( data_length + index_length ) "size" FROM information_schema.TABLES GROUP BY table_schema; n|1..5|select * from information_schema.statistics where table_schema='test'; n|1..5|select * from information_schema.columns where table_schema='test'; n|1|show table status
[13 May 2012 13:18]
MySQL Verification Team
The crashing is not repeatable for me on 5.5.24. I suppose MDL locking had some good impact here. Given this, the chance of fixing such problems in 5.1 becomes far less imho.
[23 May 2012 20:46]
Sveta Smirnova
Jervin, please check version 5.5 in your environment to be sure Shane's guess is correct and his test case repeats your problem. Thanks in advance.
[29 May 2012 1:04]
Jervin R
Yes, I was not able to repeat this on 5.5.24 - does that mean there is very little chance of getting this fixed on 5.1?
[29 May 2012 18:47]
Sveta Smirnova
Thank you for the feedback. Not, MDL locking is completely new feature and would not be backported into 5.1 See http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html for details.
[22 Dec 2012 13:34]
MySQL Verification Team
already fixed in 5.5+
Description: Marked as crashed error is logged in error log when simultaneous ALTER/DROP/Query to I_S which is possibly false. How to repeat: Use my.cnf: [mysqld] basedir=/home/revin/Downloads/ mysql-5.1.62-linux-x86_64-glibc23/ datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=revin # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 collation_server = utf8_unicode_ci character_set_server = utf8 skip-external-locking #fast_index_creation = false # 10M ramfs, can be ignored tmpdir = /tmpfs key_buffer = 276M innodb_buffer_pool_size = 276M max_allowed_packet = 16M thread_stack = 256K thread_cache_size = 128 thread_concurrency = 8 max_connections = 512 table_cache = 2048 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 128M expire_logs_days = 10 max_binlog_size = 100M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Execute ruby script below: #!/usr/bin/env ruby ENV["BUNDLE_GEMFILE"] ||= File.expand_path("../Gemfile", __FILE__) require "rubygems" require "bundler/setup" require 'mysql2' def create_client client = Mysql2::Client.new(:host => 'localhost',:socket => '/var/lib/mysql/mysql.sock', :username => 'root', :port => 3306, :password => '', :database => 'test') client end # Calculate database size. This SQL triggers the crash def dbs_size client = create_client while true do puts "Query db sizes." sizes = client.query( 'SELECT table_schema "name", sum( data_length + index_length ) "size" FROM information_schema.TABLES GROUP BY table_schema') end end def run(index) puts "enter run #{index}" client = create_client client.query("CREATE TABLE `customer#{index}` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customerName` char(150) NOT NULL DEFAULT '', `notes` text, `vendorId` int(11) NOT NULL, `accountId` varchar(150) DEFAULT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, `primaryLocationId` int(11) DEFAULT NULL, `customerContact` varchar(150) DEFAULT NULL, `logoPath` varchar(250) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK27FBE3FED24BC9CB#{index}` (`customerContact`), KEY `FK27FBE3FE219A6745#{index}` (`primaryLocationId`), KEY `FK24217FDEAF781563#{index}` (`primaryLocationId`), KEY `FK24217FDEE9410B47#{index}` (`vendorId`) ) ENGINE=MyISAM CHARSET=latin1") client.query("alter table `customer#{index}` disable keys") client.query("alter table `customer#{index}` enable keys") puts "#{index} finished" rescue => e puts e ensure client.query("drop table `customer#{index}`") client.close end Thread.new do dbs_size end 2.times do |idx| Thread.new do run(idx) while true end end sleep 300 while true Suggested fix: Possibly partly fixed in MariaDB?