| Bug #41609 | Crash recovery does not work for InnoDB temporary tables | ||
|---|---|---|---|
| Submitted: | 18 Dec 2008 23:00 | Modified: | 18 Dec 2010 19:45 |
| Reporter: | Mark Callaghan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.67,5.1.30 | OS: | Any |
| Assigned to: | Satya B | CPU Architecture: | Any |
| Tags: | create, ibbackup, innodb, recovery, table, temporary | ||
[18 Dec 2008 23:31]
Gary Pendergast
Crash recovery problem confirmed on: 5.0.74 5.1.30
[19 Dec 2008 0:06]
Mark Callaghan
Actually,the workaround is to use skip_innodb_file_per_table
[19 Dec 2008 16:13]
Mikhail Izioumtchenko
Assigning to Inaam tentatively. Note that the hotbackup issues is known and I think there's a bug number for that. The workaround for HotBackup should be creating the directory with the long strange looking name, before the restore, e.g. /var/lib/mysql/export/hda3/tmp status 'Need Feedback': could you provide us with the exact steps to reproduce this without involving HotBackup? My impression is that if we just restart the server everything should work since the directories names are all the same.
[19 Dec 2008 17:11]
Mark Callaghan
To reproduce: * configure server with innodb_file_per_table and datadir set * execute sql 'create temporary table tt(i int) engine=innodb; commit' * kill mysqld * look at error log When innodb_file_per_table is set there will be error messages in the error log. When it is not set there will not be error messages. But the more serious problem is to construct a case where the MLOG_FILE_CREATE log entry is replayed during crash recovery. If you can construct that case, then I think the server is dead as it will crash on an assert failure in file_op_log_parse_or_replay() when file_create_new_single_table_tablespace() fails. Alas, I was not able to construct that case using the test above. Creating missing directories does not work as the bogus names have an extra '/' /var/lib/mysql//export/hda3/...
[19 Dec 2008 18:44]
Mikhail Izioumtchenko
How does an extra // hurt? It's supposed to be treated as a single / by anything that work with files.
[19 Dec 2008 18:56]
Mark Callaghan
It doesn't hurt. That was a mistake by me. Your workaround works.
[22 Dec 2008 10:04]
Marko Mäkelä
The double // or \\ could have a special meaning on Microsoft Windows. On Unix-like systems, multiple successive / should not hurt.
[5 Mar 2009 13:07]
Heikki Tuuri
Pekka, ibbackup should skip .ibd files it does not find. --Heikki
[5 Mar 2009 13:08]
Heikki Tuuri
...and InnoDB itself should skip .ibd files it does not find.
[5 Mar 2009 13:09]
Heikki Tuuri
Workaround: use MyISAM tables as temp tables.
[5 Mar 2009 13:17]
Heikki Tuuri
Marko wants to take care of this.
[28 Apr 2009 11:42]
Marko Mäkelä
It turns out that we can pass the is_temp flag in the page_no parameter to mlog_write_initial_log_record_for_file_op(). Luckily, it looks like the page_no has been passed as 0 ever since MySQL 4.1. Old versions of InnoDB would ignore the page_no parameter, and new ones would assign a bit of page_no to the is_temp flag.
[29 Apr 2009 11:27]
Marko Mäkelä
As far as I can tell, the redo log entries for file operations are never replayed in InnoDB crash recovery. They are replayed in InnoDB Hot Backup, and in ibbackup --apply-log, it makes sense to skip the replaying when the is_temp flag is set. The error messages about lost *.ibd files for temporary tables are a different matter. I haven't investigated the fix yet. One possibility would be to store the is_temp flag in the InnoDB data dictionary, if we can find a good way of doing it. At least for ROW_FORMAT=COMPACT (the default format), it should be doable. Old versions of InnoDB didn't initialize unused fields to 0, and fields that could contain random garbage cannot be used for new flags.
[14 Jul 2009 15:29]
Heikki Tuuri
The reason why this works in most MySQL installations may be that the temp dir is normally /tmp. Then InnoDB replays temp table operations in a 'tmp' subdir that it creates under the MySQL datadir. That is also a workaround: set /tmp as your temp dir for mysqld. The root cause of these temp table recovery problems is that MySQL puts temp tables to a temp dir of the computer, not to a database directory. Backup programs have trouble finding the temp tables from the temp dir of the computer.
[3 Sep 2009 13:42]
Marko Mäkelä
The fix that I described on [28 Apr 13:42] is included in InnoDB Plugin 1.0.4. Part of this bug remains unfixed. Quoting my comment from [29 Apr 13:27]: The error messages about lost *.ibd files for temporary tables are a different matter. I haven't investigated the fix yet. One possibility would be to store the is_temp flag in the InnoDB data dictionary, if we can find a good way of doing it. At least for ROW_FORMAT=COMPACT (the default format), it should be doable. Old versions of InnoDB didn't initialize unused fields to 0, and fields that could contain random garbage cannot be used for new flags.
[30 Nov 2009 10:51]
Marko Mäkelä
A patch to suppress warnings about non-found temporary tables was committed to the InnoDB Plugin source repository after the InnoDB Plugin 1.0.6 release. The actual crash recovery bug was fixed earlier, last April.
[15 Dec 2009 13:26]
Marko Mäkelä
On a closer thought, InnoDB should drop temporary tables at server startup, no matter if the *.ibd or *.frm files exist. Temporary tables should only be accessible to the connection where they were created, and the connections cannot survive a restart. Depending on how the server was shut down or crashed, MySQL server or the operating environment can delete *.frm and *.ibd files for temporary tables, and information about such unaccessible tables can remain orphaned in the InnoDB data dictionary.
[16 Dec 2009 14:05]
Marko Mäkelä
At startup, MySQL seems to ask InnoDB to drop each temporary table for which a .frm file exists. However, if the .frm files are discarded, for example, by the boot scripts of the operating system distribution, orphan #sql* tables will remain in the InnoDB data dictionary. I developed a further patch that drops temporary tables from the InnoDB data dictionary at startup.
[21 Dec 2009 10:21]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/95188 3291 Satya B 2009-12-21 Applying InnoDB snapshot 5.1-ss6344, part 2. Fixes BUG#41609 but does not address the printouts issue Detailed revision comments: r6310 | marko | 2009-12-15 15:23:54 +0200 (Tue, 15 Dec 2009) | 30 lines branches/5.1: Merge r4922 from branches/zip. This the fix for the first part of Bug #41609 from InnoDB Plugin to the built-in InnoDB in MySQL 5.1. This allows InnoDB Hot Backup to back up a database while the built-in InnoDB in MySQL 5.1 is creating temporary tables. (This fix does not address the printouts about missing .ibd files for temporary tables at InnoDB startup, which was committed to branches/zip in r6252.) rb://219 approved by Sunny Bains. branches/zip: Distinguish temporary tables in MLOG_FILE_CREATE. This addresses Mantis Issue #23 in InnoDB Hot Backup and some of MySQL Bug #41609. In MLOG_FILE_CREATE, we need to distinguish temporary tables, so that InnoDB Hot Backup can work correctly. It turns out that we can do this easily, by using a bit of the previously unused parameter for page number. (The page number parameter of MLOG_FILE_CREATE has been written as 0 ever since MySQL 4.1, which introduced MLOG_FILE_CREATE.) MLOG_FILE_FLAG_TEMP: A flag for indicating a temporary table in the page number parameter of MLOG_FILE_ operations. fil_op_write_log(): Add the parameter log_flags. fil_op_log_parse_or_replay(): Add the parameter log_flags. Do not replay MLOG_FILE_CREATE when MLOG_FILE_FLAG_TEMP is set in log_flags. This only affects ibbackup --apply-log. InnoDB itself never replays file operations.
[23 Dec 2009 7:26]
Satya B
patch queued to mysql-5.1-bugteam and NULL MERGED to mysql-pe
[15 Jan 2010 8:58]
Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:satya.bn@sun.com-20091221102032-5b7qpa9l7av79bhf) (merge vers: 5.1.42) (pib:16)
[16 Jan 2010 1:35]
Paul DuBois
Noted in 5.1.43 changelog. Crash recovery did not work for InnoDB temporary tables.
[5 Feb 2010 11:45]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100119163614-172adculixyu26j5) (pib:16)
[5 Feb 2010 11:52]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)
[5 Feb 2010 11:57]
Bugs System
Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alexey.kopytov@sun.com-20091225091516-mbk3c41dhl1vunpo) (merge vers: 5.5.1-m2) (pib:16)
[12 Mar 2010 14:17]
Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:33]
Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:49]
Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[1 Apr 2010 10:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/104782 3438 Sergey Vojtovich 2010-04-01 Applying InnoDB snapshot, fixes BUG#41609. Detailed revision comments: r6252 | marko | 2009-11-30 12:50:11 +0200 (Mon, 30 Nov 2009) | 23 lines branches/zip: Suppress errors about non-found temporary tables. Write the is_temp flag to SYS_TABLES.MIX_LEN. dict_table_t::flags: Add a flag for is_temporary, DICT_TF2_TEMPORARY. Unlike other flags, this will not be written to the tablespace flags or SYS_TABLES.TYPE, but only to SYS_TABLES.MIX_LEN. dict_build_table_def_step(): Only pass DICT_TF_BITS to tablespaces. dict_check_tablespaces_and_store_max_id(), dict_load_table(): Suppress errors about temporary tables not being found. dict_create_sys_tables_tuple(): Write the DICT_TF2_TEMPORARY flag to SYS_TABLES.MIX_LEN. fil_space_create(), fil_create_new_single_table_tablespace(): Add assertions about space->flags. row_drop_table_for_mysql(): Do not complain about non-found temporary tables. rb://160 approved by Heikki Tuuri. This addresses the second part of Bug #41609 Crash recovery does not work for InnoDB temporary tables.
[1 Apr 2010 11:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/104820 3468 Sergey Vojtovich 2010-04-01 Applying InnoDB snapshot, fixes BUG#41609. Detailed revision comments: r6521 | marko | 2010-01-27 10:49:01 +0200 (Wed, 27 Jan 2010) | 17 lines branches/zip: Drop temporary tables at startup. This addresses the third aspect of Bug #41609. row_mysql_drop_temp_tables(): New function, to drop all temporary tables. These can be distinguished by the least significant bit of MIX_LEN. However, we will skip ROW_FORMAT=REDUNDANT tables, because in the records for those tables, that bit may be garbage. recv_recovery_from_checkpoint_finish(): Invoke row_mysql_drop_temp_tables(). Normally, if the .frm files for the temporary tables exist at startup, MySQL will ask InnoDB to drop the temporary tables. However, if the files are deleted, for instance, by the boot scripts of the operating system, the tables would remain in the InnoDB data dictionary unless someone digs them up by innodb_table_monitor and creates .frm files for dropping the tables. rb://221 approved by Sunny Bains.
[6 Apr 2010 8:00]
Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100401151005-c6re90vdvutln15d) (merge vers: 5.1.46) (pib:16)
[6 Apr 2010 15:08]
Paul DuBois
Fixed in earlier 5.1.x.
[5 May 2010 15:21]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 16:58]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:51]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:20]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:48]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 15:35]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[15 Jun 2010 8:18]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:35]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:51]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:29]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:16]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[8 Jul 2010 18:13]
Susan Lundstrom
I am using MySQL 5.1.46 and I am still having problems when recovering a database using InnoDB Hot Backup. I posted a note about it on the InnoDB forum. When I recover a database that had temporary tables at the time of backup I get errors: 100707 12:29:51 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 100707 12:29:51 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './tmp/#sql168e_2ef_4.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a temporary table #sql..., InnoDB: and MySQL removed the .ibd file for this. I do not have binary logging enabled on my server. Does anyone know if these errors mean the database I have recovered is now corrupt? Is there a way to get rid of these errors? I've restarted my server a few times and the errors persist.
Description: I don't think that InnoDB crash recovery code doesn't work for temporary tables when innodb_file_per_table and datadir are set. I think that ibbackup has the same problem, but I don't expect you to fix that. We noticed the problem when ibbackup restores failed with error messages like: InnoDB: Doing recovery: scanned up to log sequence number 77 2578521600 081218 8:11:09 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: File name /var/lib/mysql//export/hda3/tmp/#sql14c7_a9fa97_0.ibd InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. The pathname is odd: /var/lib/mysql//export/hda3/tmp/#sql14c7_a9fa97_0.ibd '/var/lib/mysql/' == datadir from my.cnf '/export/hda3/tmp/#sql14c7_a9fa97_0.ibd' is the pathname for the temp table. The problem appears to be this code in fil_op_log_parse_or_replay that is run to replay file creation operations. The third argument in the call to fil_create_new_single_table_tablespace is FALSE. That is the 'is_temp' arg, so recovery is always done as if the file is for a non-temporary table. Unfortunately, fil_create_new_single_table_tablespace calls fil_make_ibd_name and passes on the value of 'is_temp' } else { ut_a(type == MLOG_FILE_CREATE); if (fil_tablespace_exists_in_mem(space_id)) { /* Do nothing */ } else if (fil_get_space_id_for_table(name) != ULINT_UNDEFINED) { /* Do nothing */ } else { /* Create the database directory for name, if it does not exist yet */ fil_create_directory_for_tablename(name); ut_a(space_id != 0); ut_a(DB_SUCCESS == fil_create_new_single_table_tablespace( &space_id, name, FALSE, FIL_IBD_FILE_INITIAL_SIZE)); And file_make_ibd_name has this code that will prepend the value of datadir to the pathname written to the log entry which explains the pathname I listed above. I think this is also a problem for ibbackup and the storage engine. The default value of fil_path_to_mysql_datadir is '.' and can be changed by datadir. For http://bugs.mysql.com/bug.php?id=20867 the bad pathname starts with './tmp/' if (is_temp) { memcpy(filename, name, namelen); memcpy(filename + namelen, ".ibd", sizeof ".ibd"); } else { memcpy(filename, fil_path_to_mysql_datadir, dirlen); filename[dirlen] = '/'; memcpy(filename + dirlen + 1, name, namelen); memcpy(filename + dirlen + namelen + 1, ".ibd", sizeof ".ibd"); } Some of this is evident in http://bugs.mysql.com/bug.php?id=20867. Look at the pathname for the tmp table. It starts with '.': InnoDB: open the tablespace file './tmp/@0023sql14dc_1607_0.ibd How to repeat: It is easy to reproduce this with ibbackup. While ibbackup is running, create InnoDB temporary tables. It is harder to reproduce without ibbackup. I create an InnoDB temp table and then kill mysqld. During crash recovery an error message is printed with the incorrect pathname for the temp table (starts with '.') datadir and innodb_file_per_table should be set to reproduce this. But I think that someone from InnoDB can save you some time because the problem looks obvious to me from reading the code. Suggested fix: Crash recovery code must determine whether the MLOG_FILE_CREATE log entry is for a temporary or non-temporary table.