MySQL Bugs: #46934: backup.backup_datatypes fails on Mac OS X

Bug #46934	backup.backup_datatypes fails on Mac OS X
Submitted:	26 Aug 2009 9:23	Modified:	25 Feb 2010 1:43
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	mysql-next	OS:	MacOS
Assigned to:	Chuck Bell	CPU Architecture:	Any
Tags:	experimental, pb2, test failure

Description:
backup.backup_datatypes fails on Mac OS X:

---------------------------------------------------------------------
backup.backup_datatypes                  [ fail ]
        Test ended at 2009-08-25 11:26:23

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

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 **

BACKUP DATABASE `¥ü` to 'bup_datatype.bak';
backup_id
#
** dropping  database**
DROP DATABASE `¥ü`;
**Restore**
---------------------------------------------------------------------

The test case has been marked experimental due to this bug.

How to repeat:
XRef2: http://pb2.norway.sun.com/web.py?template=mysql_show_test_failure&test_failure_id=2287793&...

This bug appears in the mysql-next tree, but not the mysql-6.0-backup tree. Once the next merge is done from the backup tree to mysql-next, this problem should go away.

STATUS: Waiting for next merge of backup to main.

Yesterday I merged mysql-6.0-backup and mysql-next. The failure is still present.

See http://pb2.norway.sun.com/web.py?branch=mysql-6.0-backup-merge&template=show_pushes

and http://pb2.norway.sun.com/web.py?action=archive_download&archive_id=735980&pretty=please

At first glance, it appears to be a problem with MyISAM specifically. The error is way deep into the create_frm() methods. These fail to allocate the .frm file. However, if you change the table's storage engine to ARCHIVE (and comment out the update), the test passes. Similarly, if you use MEMORY (and comment out the blobs), the test also passes.

However, changing to the InnoDB storage engine results in another issue:

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

Executing the statements from the backup image in a normal client work fine. 

This test executes correctly on Windows and Linux so it is clearly a problem on the Mac OS X platform.

If you remove the special characters for the database name (and not the tables), the test passes.

I must conclude that there is something funny going on deep in the code for the execute_direct() and related methods related to special characters and the Mac OS X. I recommend someone from either the runtime team and/or charsets take a look at the problem. It does not appear to be a backup issue.

After further investigation, this appears to be an issue with the Æ character and closing of the tables.

If I substitute the Æ character in the two table names with something else, everything works.

If I do not change the character but put a FLUSH TABLES after the backup, everything works.

PROPOSED SOLUTION
-----------------
Remove the offending character from the table names. 
Remove the test from experimental status.
Add a test case to show how the special character will fail on restore.
Create a new bug with a minimal test case and assign to charset team for resolution.

Minor correction. The test case cannot be added because it does not fail on all Mac OS X systems.

Alternative Solution
--------------------
Augment test so that it works on Mac OS X systems with FLUSH TABLES.
Open new bug report to report problem with special character.

Example test change is shown below. This will be placed after the BACKUP DATABASE command on line #94.

#
# BUG#NNNNNN : Character 'Æ' in table name results in failed
# close table call in backup for Mac OS X systems.
#
if (`select @@version_compile_os like 'apple-darwin%'`)
{
  --disable_query_log
  FLUSH TABLES;
  --enable_query_log
}

Both proposed solutions want to work around the problem in the existing test case and report a new bug for the problem. In this sense I can approve both.

In case of the alternative solution, I'd prefer to do the FLUSH TABLES in all cases (not spend the effort to limit it to a specific platform) and formulate the comment so that it shall be removed by the patch for the fix. Of cousre this must be mentioned in the bug report.

For now I would like to know, if FLUSH TABLES x [,y ...] is sufficient or if indeed all tables must be flushed. Also I'd like to know, if a FLUSH TABLES before BACKUP does also help (that is, if the problem is related to tables, not included in the backup).

Apart of that I am curious about the outcome of the analyze of the new bug. It is pretty unbelievable that a certain character code in a table name produces a problem that can be worked around by FLUSH TABLES.

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/87357

Approved. No comments. test case change only. No second review required.

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/88009

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/88182

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)

Changes to test case. No changelog entry needed.