MySQL Bugs: #28541: Temporary tables may fail on case insensitive HFS

Bug #28541	Temporary tables may fail on case insensitive HFS
Submitted:	20 May 2007 17:15	Modified:	31 Dec 2007 14:04
Reporter:	Antony Curtis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.4	OS:	MacOS (10.4.9 PPC/Intel)
Assigned to:	Hakan Küçükyılmaz	CPU Architecture:	Any

Description:
Falcon fails and is unable to access a newly created temporary table when using case-insensitive HFS+ filesystem and there are capital letters in the directory path for the data directory.

How to repeat:
Build/Install MySQL in a directory path which contains upper case letters on a case sensitive HFS+ filesystem.

Relevent lines follow:

./mysql-test-run.pl --do-test=falcon

falcon_bug_22089               [ fail ]

Errors are (from /Users/antony/Work/falcon-macos-ppc.b/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 6: query 'CREATE TEMPORARY TABLE t1 (a int)' failed: 1030: Got error 157 from storage engine

The same test when run on the same filesystem where there are no uppercase letters in the directory path succeeds.

While debugging, it is observed that after the table was created, the open attempt ends up using the directory path with all lowercase letters. The hash value used for StorageTable.cpp is correct (hash func is case insensitive) but the subsequent comparison to verify that the correct table was found is case sensitive - and so table open fails.

Suggested fix:
Trim off the directory path prefix from the hash table or if using a case insensitive filesystem, always uppercase or lowercase all characters before comparing

Verified on Mac/Intel running OS X 10.4.9. Following temporary table related tests are failing:
  - falcon_bug_22089
  - falcon_bug_22143
  - falcon_temporary_alter

Best regards,

Hakan

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

ChangeSet@1.2530, 2007-05-24 00:39:50+02:00, hakank@au0012.local +4 -0
  Changes to get Falcon compiled on Mac OS X/Intel.
  
  Following tests do fail on Mac OS X/Intel
    - falcon_bug_22089, temporary table problem (Bug#28541)
    - falcon_bug_22143, temporary table problem (Bug#28541)    
    - falcon_bug_23663, temporary decimal change to get DBT2 running    
    - falcon_bug_24079, temporary table problem (Bug#28541)    
    - falcon_bug_27962, temporary decimal change to get DBT2 running    
    - falcon_temporary_alter, temporary table problem (Bug#28541)    
    - index_merge_falcon, fails also on Linux
    - partition, fails also on Linux    
    - symlink, fails also on Linux
  
  Thanks to Antony for providing the patches.

The last patch was not a bug fix, it fixed compile issues on Intel/Mac.

Hakan,

   I think this was tested before Jim put all temporary 
tables into a temporary tablespace.  That change may have
fixed the problem.  Could you retest?

Thanks,

Ann

Depends on Bug#32201, which has to be fixed first.

Fixed. Tested on Mac/Intel.

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

falcon.falcon_bug_22089        [ pass ]            323
falcon.falcon_bug_22143        [ pass ]             39
falcon.falcon_temporary_alter  [ pass ]            156
-------------------------------------------------------
Stopping All Servers
All 3 tests were successful.
The servers were restarted 1 times
Spent 0.518 of 8 seconds executing testcases

A note has been added to the 6.0.4 changelog:

Using a temporary table within Falcon that is created in a directory where the path contains a mixture of upper and lower letters would fail.