Bug #12620 UTF-8 indexing on remote volume causes ER_NOT_KEYFILE error
Submitted: 17 Aug 2005 11:56 Modified: 4 Oct 2006 1:45
Reporter: Name Withheld Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Embedded Library ( libmysqld ) Severity:S1 (Critical)
Version:5.0, 4.1 OS:Mac OS X (Mac OS X v10.4)
Assigned to: Alexey Botchkov CPU Architecture:Any

[17 Aug 2005 11:56] Name Withheld
Description:
The embedded server (libmysqld) returns an ER_NOT_KEYFILE error when inserting records into an empty MyISAM table under the following circumstances:

    a) Running on MacOS 10.4 ("Tiger").
    b) Database is hosted on a Windows (SMB) server, via a symlink.
    c) Table uses UTF-8 charset.
    d) Table uses text indexes.

The problem disappears after making any of these changes:

    a) Run on MacOS 10.3 ("Panther").
    b) Host the database on a local volume (still via a symlink).
    c) Change table to use UCS-2 charset.
    d) Remove text indexes from table.

We've tried this on several different machines, always with the same results.

How to repeat:
Use the attached C API file, "main.c", linking it against the libmysqld library.  There are additional instructions at the head of this sourcefile.
[2 Sep 2005 20:45] Jorge del Conde
Hi!

Can you please attatch your smb.conf file to this bug report ?

Thanks a lot!
[9 Sep 2005 18:26] Name Withheld
This issue is more serious than I thought.  Apparently it can corrupt databases.

I've attached another C API file, "main2.c", which demonstrates the corruption.  Please see the head of that sourcefile for additional instructions.

Given this new information, maybe items c) and d) from my original description are irrelevant.  What "main.c" and "main2.c" both demonstrate is an embedded server problem for ISAM tables which are hosted on a Windows (SMB) machine and accessed from Tiger.  There's no problem if the database is local, or if it's accessed from Panther.

This is not just a problem with my local configuration.  We've reproduced these same symptoms at several different facilities, using servers and clients which were configured by unrelated organizations.
[29 Oct 2005 0:47] Jorge del Conde
Hi!

Thanks for your bug report.  I was able to reproduce this bug using the following:

OSX 10.4
MySQL 5.0.15-standard
samba 3.0.10

Thanks !
[29 Oct 2005 1:26] Jorge del Conde
I was unable to reproduce this bug under Linux:

mysql> INSERT INTO jterms (list_id, term) select 1, term from term_dedupe;
Query OK, 80 rows affected, 3 warnings (0.02 sec)
Records: 80  Duplicates: 0  Warnings: 0

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1364 | Field 'abbr1' doesn't have a default value |
| Warning | 1364 | Field 'abbr2' doesn't have a default value |
| Warning | 1364 | Field 'abbr3' doesn't have a default value |
+---------+------+--------------------------------------------+
3 rows in set (0.00 sec)

mysql>
[25 Jul 2006 7:40] Sveta Smirnova
Could you please indicate accurate version of Windows and Windows filesystem (FAT, NTFS, etc.) you are using? Thank you.
[25 Jul 2006 17:59] Name Withheld
I've only seen the problem when using NTFS.  I don't know if it would occur with FAT; I haven't tried.

I'm running Spanish Windows XP SP2, build 2600, with all the latest patches.  I don't think the exact build matters though; we've seen this on a wide variety of unrelated systems.

We've discovered that the bug isn't really in MySQL.  In Mac OS 10.4, the standard read() and write() routines are unexpectedly returning -1 instead of zero when invoked with a length count of zero, if the file is hosted via Samba.  And MySQL is failing when it receives these strange return values from the operating system.

This still occurs with the latest version, Mac OS 10.4.7.

If you want to protect MySQL from this problem, you should avoid invoking read() and write() with a length count of zero.
[28 Jul 2006 8:38] Domas Mituzas
zero-length read indeed returns -1 on smbfs, 

Darwin 8.7.1, all patches installed
[7 Aug 2006 14:48] 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/10123

ChangeSet@1.2530, 2006-08-07 20:12:15+05:00, holyfoot@mysql.com +2 -0
  bug #12620 (UTF-8 indexing on a remote volume causes ER_NO_KEYFILE error)
  
  The problem is that 'read' and 'write' functions on MacOSX can
  return -1 instead of expected 0 being called with 0 bytes to
  read/write.
  So i added code to not to call these at all in this case.
[4 Sep 2006 14:21] 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/11358

ChangeSet@1.2563, 2006-09-04 19:47:24+05:00, holyfoot@mysql.com +2 -0
  bug #12620 (UTF-8 indexing causes ER_NO_KEYFILE error)
  
  The problem is that on some Mac OS X-es the file writing/reading
  call with zero bytes to read/write returns error.
  So here i try to eliminate that kinds of calls.
[15 Sep 2006 6:05] Alexander Barkov
The patch "bk commit into 4.1 tree (holyfoot:1.2563) BUG#12620" 
looks ok to push.
[1 Oct 2006 8:55] Georgi Kodinov
Pushed in 4.1.22/5.0.26/5.1.12
[4 Oct 2006 1:45] Paul Dubois
Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.

On Mac OS X, zero-byte read() or write() calls to an SMB-mounted
filesystem could return a non-standard return value, leading to data 
corruption. Now such calls are avoided.