| Bug #65199 | InnoDB tablespace | ||
|---|---|---|---|
| Submitted: | 4 May 2012 5:24 | Modified: | 27 Aug 2013 19:24 |
| Reporter: | Claudiu Cc | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 5.5.23 | OS: | Linux (Fedora 16) |
| Assigned to: | CPU Architecture: | Any | |
[4 May 2012 5:24]
Claudiu Cc
[4 May 2012 5:40]
Claudiu Cc
I tried to drop that database 'example1', but DBD::mysql::db do failed: Lost connection to MySQL server during query from logs: 120504 0:30:12 InnoDB: Assertion failure in thread 140177226143488 in file fil0fil.c line 709 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 05:30:12 UTC - mysqld got signal 6 ; 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=268435456 read_buffer_size=2097152 max_used_connections=1 max_threads=250 thread_count=1 connection_count=1 It is possible that mysqld could use up to. key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1800991 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x50f3960 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 = 7f7d8dc50da8 thread_stack 0x40000 /usr/libexec/mysqld(my_print_stacktrace+0x29)[0x783f49] /usr/libexec/mysqld(handle_fatal_signal+0x473)[0x66ee83] /lib64/libpthread.so.0(+0xf500)[0x7f7d8d8dc500] /lib64/libc.so.6(gsignal+0x35)[0x7f7d8bd5c285] /lib64/libc.so.6(abort+0x17b)[0x7f7d8bd5db9b] /usr/libexec/mysqld[0x87716d] /usr/libexec/mysqld[0x877209] /usr/libexec/mysqld[0x881269] /usr/libexec/mysqld[0x8812b9] /usr/libexec/mysqld[0x86100b] /usr/libexec/mysqld[0x805172] /usr/libexec/mysqld[0x805a4d] /usr/libexec/mysqld[0x8cefa8] /usr/libexec/mysqld[0x8cf517] /usr/libexec/mysqld[0x7f5566] /usr/libexec/mysqld[0x7f7755] /usr/libexec/mysqld[0x7e106a] /usr/libexec/mysqld[0x66eeff] /usr/libexec/mysqld(_Z24plugin_foreach_with_maskP3THDPFcS0_P13st_plugin_intPvEijS3_+0x229)[0x59a309] /usr/libexec/mysqld(_Z11mysql_rm_dbP3THDPcbb+0x5d8)[0x573d38] /usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x204a)[0x58f29a] /usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1b4)[0x593a14] /usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x137f)[0x594dbf] /usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x122)[0x61fe82] /usr/libexec/mysqld(handle_one_connection+0x50)[0x61ff30] /lib64/libpthread.so.0(+0x7d90)[0x7f7d8d8d4d90] /lib64/libc.so.6(clone+0x6d)[0x7f7d8be16f5d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f7d28561290): DROP DATABASE IF EXISTS `example1` Connection ID (thread ID): 8 Status: 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. 120504 00:30:12 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 120504 0:30:12 [Note] Plugin 'FEDERATED' is disabled. 120504 0:30:12 InnoDB: The InnoDB memory heap is disabled 120504 0:30:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins 120504 0:30:12 InnoDB: Compressed tables use zlib 1.2.5 120504 0:30:12 InnoDB: Using Linux native AIO 120504 0:30:12 InnoDB: Initializing buffer pool, size = 512.0M 120504 0:30:12 InnoDB: Completed initialization of buffer pool 120504 0:30:12 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 13905351610 120504 0:30:12 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 13905358906 120504 0:33:26 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 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 InnoDB: Apply batch completed 120504 0:33:27 InnoDB: Error: table 'example1/name1' InnoDB: in InnoDB data dictionary has tablespace id 98481, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 120504 0:33:27 InnoDB: Error: table 'example1/name2' InnoDB: in InnoDB data dictionary has tablespace id 129550, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 120504 0:33:27 InnoDB: Error: table 'example1/name3' InnoDB: in InnoDB data dictionary has tablespace id 129556, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 120504 0:33:27 InnoDB: Error: table 'example1/name4' InnoDB: in InnoDB data dictionary has tablespace id 129552, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 120504 0:33:28 InnoDB: Waiting for the background threads to start 120504 0:33:29 InnoDB: 1.1.8 started; log sequence number 13905358906 120504 0:33:29 [ERROR] /usr/libexec/mysqld: Table './mysql/db' is marked as crashed and should be repaired 120504 0:33:29 [Warning] Checking table: './mysql/db' 120504 0:33:29 [ERROR] 1 client is using or hasn't closed the table properly 120504 0:33:29 [Note] Event Scheduler: Loaded 0 events 120504 0:33:29 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.23' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) This is related to my other bug 64704, reported later.
[4 May 2012 17:40]
Sveta Smirnova
Thank you for the report. My guess is shared tablespace could be re-created at upgrade time. Please, send us full error log file compressed as Valeriy already requested in another bug.
[4 May 2012 18:50]
Claudiu Cc
I already did a full copy/paste from the logs. There is nothing more.
[4 May 2012 19:19]
Sveta Smirnova
Thank you for the feedback. But you did not copy-pasted full error log file. You just pasted not linked parts of it.
[5 May 2012 6:03]
Claudiu Cc
I have autorepair for myisam enabled and I've only omitted notification lines when a database is repaired. It's even different database than the one involved in crash.
[5 May 2012 12:46]
Sveta Smirnova
Thank you for the feedback. I think old bug contains explanation of the problem: > [24 Mar 12:16] Claudiu Cc ... > Deleting mysql dir by hand and doing again the drop will solve the problem. This is not supported action for InnoDB, because deleting mysql dir by hand you don't delete entries in shared InnoDB tablespace. This is why you see errors. So this one is technically not a bug. You need to dump your databases, drop all InnoDB files and re-create it from scratch.
[6 May 2012 18:35]
Claudiu Cc
I can't remember exactly the message, but mysql say something about failed rm of database dir and encourage user to delete himself the dir manually. Something like that, more or less. At this point I'm going to delete the dir, not before drop then database is dropped correctly without any error in logs. Trying to drop again the database without to rm the dir, after it previously failed, is causing the same error. after a failed drop, ls /var/lib/mysql/database is showing something like: -rw-rw---- 1 mysql mysql 95792 May 6 00:41 table.ibd not anymore db.opt or .frm file.
[8 May 2012 15:23]
Sveta Smirnova
Thank you for the feedback. > after a failed drop, ls /var/lib/mysql/database is showing something like: > > -rw-rw---- 1 mysql mysql 95792 May 6 00:41 table.ibd > > not anymore db.opt or .frm file. This was worst possible case: InnoDB have not deleted InnoDB data file and, as latter error shows, have not deleted table id from tablespace. So this looks like result of bug #64704 To solve this problem you should've be done something like solution in comment "[6 Feb 22:07] John Russell" for bug #45976: ----<q>---- The workaround for the problem was to create a similar temporary table again, copy its .frm file to tmpdir under the name mentioned in the error message (for example, #sql123.frm) and restart mysqld with tmpdir set to its normal value without a trailing slash, for example /var/tmp. On startup, Mysql would see the .frm file and issue DROP TABLE for the orphaned temporary table. ----</q>----- So in your case you've need to create a table with same structure like a table which left in the database, then copy *.frm file into database directory, then issue "DROP TABLE" statement again. So messages themselves are not a bug, but circumstances which lead to this problem are, probably, a bug.
[8 May 2012 19:18]
Claudiu Cc
>> So in your case you've need to create a table with same structure like a table which left in the database, then copy *.frm file into database directory, then issue "DROP TABLE" statement again. I already run a similar script, but with many databases on the server errors like this one are very frequent and once a day I must check the logs for errors and solve the issue by hand. I hope you will find a solution.
[18 Jul 2012 12:51]
Van Stokes
We are having a similar issue: OS: Linux: Rehad E5, Ubuntu 12.04 LTS MySQL: 5.0.95 (rh), 5.5.25a (Ubuntu) We have a daily script that runs on the Ubuntu server using 5.5.25a. It performs a mysqldump (ver 5.5.25a) from the 5.0.95 (rh) mysql server and then imports the dump using mysql (ver 5.5.25a). Here is the error log message: --- Begin of log 120717 19:38:42 InnoDB: Warning: trying to init to the tablespace memory cache InnoDB: a tablespace 275019 of name './oti_lw_prod/apinvoice_charges.ibd', InnoDB: but a tablespace 272383 of the same name InnoDB: already exists in the tablespace memory cache! InnoDB: We assume that InnoDB did a crash recovery, and you had InnoDB: an .ibd file for which the table did not exist in the InnoDB: InnoDB internal data dictionary in the ibdata files. InnoDB: We assume that you later removed the .ibd and .frm files, InnoDB: and are now trying to recreate the table. We now remove the InnoDB: conflicting tablespace object from the memory cache and try InnoDB: the init again. --- End of log The dump does have the drop table statement and then the create table statement: --- Begin dump -- -- Table structure for table `apinvoice_charges` -- DROP TABLE IF EXISTS `apinvoice_charges`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `apinvoice_charges` ( `id` int(11) NOT NULL auto_increment, `apinvoice_id` int(11) default NULL, `charges_code` varchar(4) default NULL, `amount` decimal(12,2) default NULL, `quantity` varchar(20) default NULL, `description` varchar(50) default NULL, `gl_account` varchar(20) default NULL, `total` int(11) default NULL, PRIMARY KEY (`id`), KEY `apinvoice_foreignkey` (`apinvoice_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `apinvoice_charges` -- LOCK TABLES `apinvoice_charges` WRITE; /*!40000 ALTER TABLE `apinvoice_charges` DISABLE KEYS */; INSERT INTO `apinvoice_charges` VALUES (2,46,NULL,200.00,NULL,'testing invoice','02-2200-01',NULL); /*!40000 ALTER TABLE `apinvoice_charges` ENABLE KEYS */; UNLOCK TABLES; --- End dump The result is the table is dropped however the apinvoice_charges.ibd file remains. The fix this problem manually, we do this: USE `oti_lw_prod`; SET FOREIGN_KEY_CHECKS=0; DROP TABLE `apinvoice_charges`; which results in this error message (expected): Query: drop table `apinvoice_charges` Error Code: 1051 Unknown table 'apinvoice_charges' The we drop to the shell prompt and remove the apinvoice_charges.ibd file and then restart the import process which successfully works. However, the next day, when the automated process starts again, we have the same exact problem.
[4 Oct 2012 14:34]
Vojtech Kurka
Crash when trying to drop a table/database on 5.5.23-55-log: Thread pointer: 0x6c5fe90 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 = 7f84e80e8e58 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7a4b85] /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x680454] /lib64/libpthread.so.0[0x3b5a40f500] /lib64/libc.so.6(gsignal+0x35)[0x3b59c328a5] /lib64/libc.so.6(abort+0x175)[0x3b59c34085] /usr/sbin/mysqld[0x8179a1] /usr/sbin/mysqld[0x7ffc96] /usr/sbin/mysqld[0x68050f] /usr/sbin/mysqld(_Z24plugin_foreach_with_maskP3THDPFcS0_P13st_plugin_intPvEijS3_+0x212)[0x592e52] /usr/sbin/mysqld(_Z11mysql_rm_dbP3THDPcbb+0x8b3)[0x56abb3] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x280c)[0x588b3c] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x333)[0x58ae43] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x15b2)[0x58c4a2] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xd7)[0x624607] /usr/sbin/mysqld(handle_one_connection+0x51)[0x624741] /lib64/libpthread.so.0[0x3b5a407851] /lib64/libc.so.6(clone+0x6d)[0x3b59ce76dd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f8430009510): is an invalid pointer Connection ID (thread ID): 225 Status: NOT_KILLED In fact the database directory doesn't exist any more, only the entry in InnoDB dictionary. This happens to use every few weeks. It's a testing machine, where we create and drop thousands of databases per day, each DB with about 10-100 InnoDB tables. I was not able to reproduce it. I have tried the to remove the table from InnoDB dictionary as described here: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html, I created the right FRM file, but I always end up with a server crash when trying to drop the table.
[23 Jan 2013 14:29]
Matthew Lord
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of an obvious bug. The errors reported by InnoDB are merely filesystem errors (OS error 2 is "No such directory or file"). If you are able to come up with a clear test case that shows the problem, then please let me know and I'll be happy to test it. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
[12 Aug 2013 23:49]
Jonathan Nicol
"I created the right FRM file, but I always end up with a server crash when trying to drop the table." "Your problem is not the result of an obvious bug" Did I misunderstand something? Sounds like he was doing exactly as I am, following the recovery instructions. # ls my_schema/bad_table* my_schema/bad_table.frm # /etc/init.d/mysql start Starting MySQL. [ OK ] # mysql my_schema mysql> drop table bad_table; *SERVER CRASHES*
[27 Aug 2013 19:24]
Sveta Smirnova
Yes, you seems to forgot to specify one option.
