Bug #48923 | The backup_datatypes and backup_dbname_lctns fail in PB2. Possible lock issue. | ||
---|---|---|---|
Submitted: | 19 Nov 2009 21:36 | Modified: | 7 Mar 2010 19:36 |
Reporter: | Chuck Bell | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Backup | Severity: | S3 (Non-critical) |
Version: | 6.0.14 | OS: | Any |
Assigned to: | Rafal Somla | CPU Architecture: | Any |
[19 Nov 2009 21:36]
Chuck Bell
[19 Nov 2009 21:40]
Chuck Bell
Also, backup_dbname_lctn2 fails with same error: mysqltest: In included file "./suite/backup/t/backup_dbname_lctn1.test": At line 111: query 'RESTORE FROM 'upup.bup'' failed: 1100: Table 'ucase' was not locked with LOCK TABLES The result from queries just before the failure was: < snip > RESTORE FROM 'lowlow.bup'; backup_id # SHOW DATABASES WHERE `Database` LIKE 'lower'; Database lower SHOW TABLES IN lower; Tables_in_lower lcase SELECT * FROM lower.lcase ORDER BY b; a b L00 1000 L41 1300 L41 1301 L41 1305 L01 1453 # # RESTORE upper case database and verify content # More results from queries before failure can be found in /Users/cbell/source/bzr/mysql-6.0-review/mysql-test/var/log/backup_dbname_lctn2.log Warnings from just before the error: Error 1100 Table 'ucase' was not locked with LOCK TABLES Error 1689 Open and lock tables failed in RESTORE - saving '/Users/cbell/source/bzr/mysql-6.0-review/mysql-test/var/log/backup.backup_dbname_lctn2/' to '/Users/cbell/source/bzr/mysql-6.0-review/mysql-test/var/log/backup.backup_dbname_lctn2/'
[19 Nov 2009 21:42]
Chuck Bell
Not just for Macs...same errors showing up on windows for backup_dbname_lctn2.
[19 Nov 2009 21:57]
Chuck Bell
Error is thrown at line#2607 in sql_base.cc in the mysql-6.0-backup tree. I suspect this is not a backup bug but rather a locking issue. Rassigning to runtime team for evaluation. Stack trace is as follows (from Windows): mysqld.exe!open_table(THD * thd=0x00d52ec0, TABLE_LIST * table_list=0x00d64f28, st_mem_root * mem_root=0x02fad074, Open_table_context * ot_ctx=0x02fad0a0, unsigned int flags=256) Line 2607 C++ mysqld.exe!open_and_process_table(THD * thd=0x00d52ec0, LEX * lex=0x00d53d78, TABLE_LIST * tables=0x00d64f28, unsigned int * counter=0x02fad120, unsigned int flags=256, Prelocking_strategy * prelocking_strategy=0x02fad14c, bool has_prelocking_list=false, Open_table_context * ot_ctx=0x02fad0a0, st_mem_root * new_frm_mem=0x02fad074) Line 3941 + 0x19 bytes C++ mysqld.exe!open_tables(THD * thd=0x00d52ec0, TABLE_LIST * * start=0x02fad134, unsigned int * counter=0x02fad120, unsigned int flags=256, Prelocking_strategy * prelocking_strategy=0x02fad14c) Line 4179 + 0x2d bytes C++ mysqld.exe!open_and_lock_tables_derived(THD * thd=0x00d52ec0, TABLE_LIST * tables=0x00d64f28, bool derived=false, unsigned int flags=256, Prelocking_strategy * prelocking_strategy=0x02fad14c) Line 4764 + 0x19 bytes C++ mysqld.exe!open_and_lock_tables_derived(THD * thd=0x00d52ec0, TABLE_LIST * tables=0x00d64f28, bool derived=false, unsigned int flags=256) Line 1518 + 0x1a bytes C++ mysqld.exe!Backup_restore_ctx::lock_tables_for_restore() Line 1144 + 0x1a bytes C++ mysqld.exe!Backup_restore_ctx::do_restore(bool overwrite=false) Line 1634 + 0x8 bytes C++ mysqld.exe!execute_backup_command(THD * thd=0x00d52ec0, LEX * lex=0x00d53d78, String * backupdir=0x02fae740, bool overwrite=false, bool skip_gap_event=false) Line 296 + 0xd bytes C++ mysqld.exe!mysql_execute_command(THD * thd=0x00d52ec0) Line 2480 + 0x24 bytes C++ mysqld.exe!mysql_parse(THD * thd=0x00d52ec0, const char * inBuf=0x00d64d90, unsigned int length=23, const char * * found_semicolon=0x02faf5e4) Line 5983 + 0x9 bytes C++ mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x00d52ec0, char * packet=0x00d5ccf1, unsigned int packet_length=23) Line 1078 + 0x22 bytes C++ mysqld.exe!do_command(THD * thd=0x00d52ec0) Line 760 + 0x1b bytes C++ mysqld.exe!handle_one_connection(void * arg=0x00d52ec0) Line 1164 + 0x9 bytes C++ mysqld.exe!pthread_start(void * p=0x00d07e60) Line 61 + 0x9 bytes C mysqld.exe!_callthreadstartex() Line 348 + 0xf bytes C mysqld.exe!_threadstartex(void * ptd=0x00d0a0e8) Line 331 C kernel32.dll!76fb1174() [Frames below may be incorrect and/or missing, no symbols loaded for kernel32.dll] ntdll.dll!76e7b3f5() ntdll.dll!76e7b3c8() mysqld.exe!Item_func::print_args(String * str=, unsigned int from=, enum_query_type query_type=) Line 418 C++
[20 Nov 2009 4:28]
Valeriy Kravchuk
With latest mysql-6.0-codebase from bzr on Mac OS X 10.5.x I've got BUG#46934 and result content mismatch, but none of the problems described here.
[20 Nov 2009 7:23]
Rafal Somla
Just a wild guess, but this can be a naming issue on case insensitive systems. Perhaps wrong objects are locked, whose names differ only in upper/lower case.
[20 Nov 2009 9:57]
Sveta Smirnova
Thank you for the report. Verified as described using mysql-6.0-backup tree on my personal Mac OSX 10.4
[23 Nov 2009 22:38]
Chuck Bell
The cause of the problem is centered around a change made to kernel.cc regarding locking tables for restore. The change below introduced a problem on Mac and Windows that causes the open_table[s]() code to fail to open a table with case name issues (mixed case on lctn1 or lctn2). If one applies this diff to remove these changes and recompiles, the lock issue goes away. === modified file 'sql/backup/kernel.cc' --- sql/backup/kernel.cc 2009-11-17 20:18:50 +0000 +++ sql/backup/kernel.cc 2009-10-29 20:02:06 +0000 @@ -1107,26 +1097,14 @@ int Backup_restore_ctx::lock_tables_for_ } } - ret= obs::lock_tables_for_write(m_thd, m_backup_tables); - if (ret) - goto err1; - DEBUG_SYNC(m_thd, "after_restore_lock_tables_for_write"); - - /* - Truncate tables. Some INSERT might have found the freshly - created table. - */ - ret= obs::truncate_tables(m_thd, m_backup_tables); - if (ret) - goto err; - DEBUG_SYNC(m_thd, "after_restore_truncate_tables"); - - /* - Mimic error in opening tables. Cannot be done by setting ret=1 - after open_and_lock_tables_derived becase that method is - supposed to release the lock before returning error. - */ - DBUG_EXECUTE_IF("restore_lock_tables_for_restore", goto err;); + DBUG_EXECUTE_IF("restore_lock_tables_for_restore", + /* + Mimic error in opening tables. Cannot be done by setting ret=1 + after open_and_lock_tables_derived becase that method is + supposed to release the lock before returning error. + */ + return fatal_error(report_error(ER_BACKUP_OPEN_TABLES,"RESTORE")); + ); /* Open and lock the tables. @@ -1143,15 +1121,10 @@ int Backup_restore_ctx::lock_tables_for_ /* Do not open tmp tables. */ ); if (ret || is_killed()) - goto err; + return fatal_error(report_error(ER_BACKUP_OPEN_TABLES,"RESTORE")); m_tables_locked= TRUE; return 0; - - err: - (void) obs::unlock_tables(m_thd); - err1: - return fatal_error(report_error(ER_BACKUP_OPEN_TABLES, "RESTORE")); } @@ -1166,26 +1139,26 @@ void Backup_restore_ctx::unlock_tables() return; DBUG_PRINT("restore",("unlocking tables")); - close_thread_tables(m_thd); // Never errors. - m_tables_locked= FALSE; /* Refresh tables that have been restored. Some restore drivers might restore a table layout that differs from the version created by - materialize(). We need to force a final close after restore. + materialize(). We need to force a final close after restore with + close_cached_tables(). Note that we do this before we unlock the + tables. Otherwise other threads could use the still open tables + before we refresh them. For information about a concrete problem, see the comment in myisam_backup_engine.cc:Table_restore::close(). Use the restore table list as created by lock_tables_for_restore(). - - We ignore the status from FLUSH and UNLOCK as there is no way - to deal with such problems here. */ - DEBUG_SYNC(m_thd, "before_restore_flush_tables"); - (void) obs::flush_tables(m_thd, m_backup_tables); - DEBUG_SYNC(m_thd, "before_restore_unlock_tables"); - (void) obs::unlock_tables(m_thd); + if (m_backup_tables) + close_cached_tables(m_thd, m_backup_tables, FALSE, FALSE); + + close_thread_tables(m_thd); // Never errors. + m_tables_locked= FALSE; + return; }
[24 Nov 2009 17:46]
Rafal Somla
REFINED PROBLEM DESCRIPTION --------------------------- To simplify description, let us consider restoring table 't1' in database 'X' on a case-insensitive server with lctn=2. On such server both 'X' and 'x' refer to the same database, but server preserves the original case when referring to the database. During restore, si_objects.cc lock_tables_for_write() function is called which executes SQL statement "LOCK TABLES `X`.`t1` WRITE". This works fine as `X` correctly refers to the database. But later, open_and_lock_tables() is called, where the table is again described by 'X' and 't1'. This is however wrong, because open_and_lock_tables() expects database name to be converted to lowercase if lctn > 0. Note: lctn = the value of --lower-case-table-names option.
[27 Nov 2009 18:19]
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/91979 2899 Rafal Somla 2009-11-27 Bug #48923 - The backup_datatypes and backup_dbname_lctns fail in PB2. Possible lock issue. Problem: On lctn=2 system, table names preserve case but internally are represented in lower case. In particular, open_and_lock_tables() expects that all table and database names in TABLE_LIST are in lower case. However, backup system passed names in original case in TABLE_LIST. This lead to problems. Solution: Utility function backup::build_table_list() is changed so that on lctn=2 system it converts names to lower case when storing them in TABLE_LIST structure. In all places where we need TABLE_LIST parameter, we create it using this function ensuring that name case is correct. @ sql/backup/backup_aux.h - Make it possible to explicitly set character set when constructing a String from a blob. - Do lowercase conversions (if lctn=2) when TABLE_LIST structure is filled. - Add MEM_ROOT parameter to build_table_list() function. @ sql/backup/be_default.cc - Use build_table_list() to initialize TABLE_LIST list. - Initialize memory root for build_table_list. - In get_data() use linked list to access consecutive members. @ sql/backup/be_default.h Declare mem_root for build_table_list(). @ sql/backup/kernel.cc - Specify correct character set when creating String out of object name read from the image. - In build_table_list() use provided MEM_ROOT to allocate objects. - Ensure that list created by build_table_list has the same order as the one passed to the function. @ storage/myisam/myisam_backup_engine.cc Specify MEM_ROOT for build_table_list() function.
[28 Nov 2009 22:05]
Ingo Strüwing
Approved. See minor comments in email.
[1 Dec 2009 18:42]
Chuck Bell
Approved pending changes
[2 Dec 2009 8:02]
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/92390 2900 Rafal Somla 2009-12-02 Bug #48923 - The backup_datatypes and backup_dbname_lctns fail in PB2. Possible lock issue. Problem: On lctn=2 system, table names preserve case but internally are represented in lower case. In particular, open_and_lock_tables() expects that all table and database names in TABLE_LIST are in lower case. However, backup system passed names in original case in TABLE_LIST. This lead to problems. Solution: Utility function backup::build_table_list() is changed so that on lctn=2 system it converts names to lower case when storing them in TABLE_LIST structure. In all places where we need TABLE_LIST parameter, we create it using this function ensuring that name case is correct. @ sql/backup/backup_aux.h - Make it possible to explicitly set character set when constructing a String from a blob. - Rename mk_table_list() -> mk_table_list_element() to emphasize that it creates a single TABLE_LIST structure. - Do lower case conversions (if lctn=2) when TABLE_LIST structure is filled. - Add MEM_ROOT parameter to build_table_list() function. @ sql/backup/be_default.cc - Use build_table_list() to initialize TABLE_LIST list. - In get_data() use linked list to access consecutive members. @ sql/backup/kernel.cc - Rename mk_table_list() -> mk_table_list_element(). - Specify correct character set when creating String out of object name read from the image. - In build_table_list() use provided MEM_ROOT to allocate objects. - Ensure that list created by build_table_list has the same order as the one passed to the function. @ storage/myisam/myisam_backup_engine.cc Specify MEM_ROOT for build_table_list() function.
[2 Dec 2009 9:58]
Rafal Somla
Pushed to mysql-6.0-backup tree. revid:rafal.somla@sun.com-20091202080143-ctxu49d6cvi1aj45
[20 Feb 2010 9:19]
Bugs System
Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100119103538-wtp5alpz4p2jayl5) (merge vers: 6.0.14-alpha) (pib:16)
[7 Mar 2010 19:36]
Paul DuBois
Noted in 6.0.14 changelog. A BACKUP DATABASE and RESTORE cycle could fail to handle database and table lettercase on systems with lower_case_table_names set to 2.