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:
None 
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
Description:
The backup_datatypes test is failing again on Mac OS X. Note: This is *not* the same error as before (BUG#46934, BUG#43220). Results from PB:

backup.backup_datatypes                  [ fail ]
        Test ended at 2009-11-19 18:56:44

CURRENT_TEST: backup.backup_datatypes
mysqltest: At line 112: query 'RESTORE FROM 'bup_datatype.bak' OVERWRITE' failed: 1100: Table '§Æ2' was not locked with LOCK TABLES

The result from queries just before the failure was:
< snip >
summary	longtext	YES		NULL	
data	blob	YES		NULL	
details	mediumblob	YES		NULL	
queries	tinytext	YES		NULL	
query2	tinyblob	YES		NULL	
extract	longblob	YES		NULL	
paras	mediumtext	YES		NULL	
DESCRIBE `§¶œ`;
Field	Type	Null	Key	Default	Extra
b1	binary(3)	YES		NULL	
b2	varbinary(2)	YES		NULL	
bitvalue	bit(64)	YES		NULL	
** Backup data **

How to repeat:
./mysql-test-run.pl backup_datatypes

Suggested fix:
Possible locking issue.

Msy be related to BUG#48151.
[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.