Bug #48151 Tables not closed on Mac OS X using UTF-8 uppercase characters
Submitted: 19 Oct 2009 14:44 Modified: 19 Oct 2009 15:13
Reporter: Chuck Bell Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:6.0.14 OS:MacOS (10.6.1)
Assigned to: CPU Architecture:Any

[19 Oct 2009 14:44] Chuck Bell
Description:
Tables are not being closed properly on the Mac OS X 10.6.1 platform when running backup. This has been traced to a problem with comparing uppercase letters in table names that have UTF-8 symbols.

If you substitute the Æ for U or Ü, the test fails.
If you substitute the Æ for any lower case letter, the test passes.
If you set lctn=0,1 the test passes with the Æ character.
If you set lctn=2, the test fails with the Æ character.

The problem is displayed when a restore is followed by a backup with tables that have upper-case UTF-8 names. The backup is working but the tables are not being closed so that on restore, the table cannot be created. 

In examining the problem during restore, the error is manifested when the test_if_reopen() method is called from mi_create.c.

  if (test_if_reopen(filename))
  {
    my_printf_error(0, "MyISAM table '%s' is in use "
                    "(most likely by a MERGE table). Try FLUSH TABLES.",
                    MYF(0), name + dirname_length(name));
    my_errno= HA_ERR_TABLE_EXIST;
    goto err;
  }

Error 1105 MyISAM table '@00a7U' is in use (most likely by a MERGE table). Try FLUSH TABLES.

NOTE: If you substitute InnoDB, the problem still occurs albeit with a slightly different error message:

mysqltest: At line 95: query 'BACKUP DATABASE `¥ü` to 'bup_datatype.bak'' failed: 1146: Table '¥ü.§Æ' doesn't exist

Clearly, there are differences WRT how MyISAM and InnoDB decipher table names on Mac OS X with uppercase UTF-8 characters.

NOTE: the test in question is the backup_datatypes test. A basic test case extracted from that test is shown below.

How to repeat:
Run this test case:

--source include/not_embedded.inc

--disable_warnings
DROP DATABASE IF EXISTS `¥ü`;
--enable_warnings

SET NAMES utf8;
CREATE DATABASE `¥ü`;
USE `¥ü`;

CREATE TABLE `§Æ`(a int);

# Note: This command should fail for InnoDB.
--replace_column 1 #
BACKUP DATABASE `¥ü` to 'bup_datatype.bak';

# Note: This command should fail for MyISAM.
--replace_column 1 #
RESTORE FROM 'bup_datatype.bak' OVERWRITE;

Suggested fix:
A workaround shall be implemented that issues a FLUSH TABLES after the backup. This should be removed from backup_datatypes.test when this bug is fixed.

#
# BUG#NNNNNN : Table names with uppercase characters and not being
# closed completely for Mac OS X systems.
#
if (`select @@version_compile_os like 'apple-darwin%'`)
{
  --disable_query_log
  FLUSH TABLES;
  --enable_query_log
}

When this bug is fixed, this section of the test should be removed.
[19 Oct 2009 15:13] Valeriy Kravchuk
Thank you for the problem report. Verified just as described with recent mysql-6.0-codebase from bzr on Mac OS X 10.5.6:

77-52-242-160:mysql-test openxs$ ./mtr --mysqld="--mysql-backup" bug48151
Logging: ./mtr  --mysqld=--mysql-backup bug48151
091019 18:11:50 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/XfQ4424XEU/ is case insensitive
091019 18:11:50 [Note] Plugin 'FEDERATED' is disabled.
091019 18:11:50 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 6.0.14
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /Users/openxs/dbs/6.0-codebase/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/6.0-codebase/mysql-test/var'...
Installing system database...
Using server port 53474

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug48151                            [ fail ]
        Test ended at 2009-10-19 18:11:55

CURRENT_TEST: main.bug48151
mysqltest: At line 19: query 'RESTORE FROM 'bup_datatype.bak' OVERWRITE' failed: 1700: Could not restore table `¥ü`.`§Æ`

The result from queries just before the failure was:
DROP DATABASE IF EXISTS `¥ü`;
SET NAMES utf8;
CREATE DATABASE `¥ü`;
USE `¥ü`;
CREATE TABLE `§Æ`(a int);
BACKUP DATABASE `¥ü` to 'bup_datatype.bak';
backup_id
#

Warnings from just before the error:
Error 1105 MyISAM table '@00a7@0M' is in use (most likely by a MERGE table). Try FLUSH TABLES. 
Error 1005 Can't create table '¥ü.§æ' (errno: 156) 
Error 1700 Could not restore table `¥ü`.`§Æ`

 - saving '/Users/openxs/dbs/6.0-codebase/mysql-test/var/log/main.bug48151/' to '/Users/openxs/dbs/6.0-codebase/mysql-test/var/log/main.bug48151/'
------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 5 seconds executing testcases

Failed 1/1 tests, 0.00% were successful.

Failing test(s): main.bug48151
[20 Oct 2009 6:46] Ingo Strüwing
See Bug#46934 (backup.backup_datatypes fails on Mac OS X) for the workaround that needs to be removed with the fix for this bug.
[18 Nov 2009 13:55] Chuck Bell
May be related to BUG#48850.
[19 Nov 2009 21:37] Chuck Bell
May be cause of BUG#48923.
[20 Feb 2010 9:17] 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)