Bug #59663 Error 11 - can't unlock file when using external_locking
Submitted: 21 Jan 2011 17:05 Modified: 24 Jan 2011 23:28
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.1.54 OS:Windows (7/64 - 64 bit server)
Assigned to: CPU Architecture:Any
Tags: external_locking, qc

[21 Jan 2011 17:05] Peter Laursen
Description:
Test case from here: http://bugs.mysql.com/59449 . That one was about ORDER BY with COALESCE() - this is something quite different. (A) valid statement(s) return

-- Error Code : 11
-- Can't unlock file (Errcode: 11)

How to repeat:
CREATE TABLE LANGUAGE (
  LanguageID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  LanguageCode CHAR(10) NOT NULL,
  LanguageName VARCHAR(40) NOT NULL,
  LanguageNameTextID INTEGER UNSIGNED NOT NULL,
  LatestChangeStamp TIMESTAMP NOT NULL,
  PRIMARY KEY (LanguageID),
  KEY LanguageCode (LanguageCode),
  KEY LanguageNameTextID (LanguageNameTextID),
  KEY LanguageName (LanguageName) )
ENGINE=INNODB
DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci;

INSERT LANGUAGE SELECT 1, 'E', 'English', 1, NULL;
INSERT LANGUAGE SELECT 2, 'S', 'Spanish', 2, NULL;
INSERT LANGUAGE SELECT 3, 'ASL', 'American Sign Language', 3, NULL;
INSERT LANGUAGE SELECT 4, 'LSA', 'Argentinian Sign Language', 4, NULL;

CREATE TABLE TextMaster (
  TextMasterID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  MasterText TEXT NOT NULL,
  LatestChangeStamp TIMESTAMP NOT NULL,
  PRIMARY KEY (TextMasterID) )
ENGINE=INNODB
DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci;

INSERT TextMaster SELECT 1, 'English', NULL;
INSERT TextMaster SELECT 2, 'Spanish', NULL;
INSERT TextMaster SELECT 3, 'American Sign Language', NULL;
INSERT TextMaster SELECT 4, 'Argentinian Sign Language', NULL;

CREATE TABLE TextTranslation (
  TextTranslationID INTEGER UNSIGNED AUTO_INCREMENT,
  TextMasterID INTEGER UNSIGNED NOT NULL,
  LanguageID INTEGER UNSIGNED NOT NULL,
  TranslatedText TEXT NOT NULL,
  LatestChangeStamp TIMESTAMP NOT NULL,
  PRIMARY KEY (TextTranslationID),
  KEY TextMasterID_LanguageID (TextMasterID, LanguageID) )
ENGINE=INNODB
DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci;

INSERT TextTranslation SELECT NULL, 1, 1, 'English', NULL;
INSERT TextTranslation SELECT NULL, 2, 1, 'Spanish', NULL;
INSERT TextTranslation SELECT NULL, 3, 1, 'American Sign Language',
NULL;
INSERT TextTranslation SELECT NULL, 4, 1, 'Argentinian Sign Language',
NULL;
INSERT TextTranslation SELECT NULL, 1, 2, 'Inglés', NULL;
INSERT TextTranslation SELECT NULL, 2, 2, 'Español', NULL;
INSERT TextTranslation SELECT NULL, 3, 2, 'Lenguaje de señas
americano', NULL;
INSERT TextTranslation SELECT NULL, 4, 2, 'Lengua de señas argentina',
NULL;

-- Here are the queries:
SELECT l.LanguageID,
      l.LanguageCode,
      l.LanguageNameTextID,
      COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
      l.LatestChangeStamp
 FROM LANGUAGE l
 INNER JOIN TextMaster tm1
   ON tm1.TextMasterID = l.LanguageNameTextID
 LEFT OUTER JOIN TextTranslation t1
   ON t1.TextMasterID = l.LanguageNameTextID
  AND t1.LanguageID = 2
 WHERE l.LanguageName LIKE '%Sign Language%'
 ORDER BY LanguageName COLLATE utf8_spanish_ci;
-- Error Code : 11
-- Can't unlock file (Errcode: 11)

SELECT l.LanguageID,
      l.LanguageCode,
      l.LanguageNameTextID,
      COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
      l.LatestChangeStamp
 FROM LANGUAGE l
 INNER JOIN TextMaster tm1
   ON tm1.TextMasterID = l.LanguageNameTextID
 LEFT OUTER JOIN TextTranslation t1
   ON t1.TextMasterID = l.LanguageNameTextID
  AND t1.LanguageID = 2
 WHERE l.LanguageName LIKE '%Sign Language%'
 ORDER BY LanguageName;
-- Error Code : 11
-- Can't unlock file (Errcode: 11)

Suggested fix:
I have no idea what happens here. But: 

1) Happens with MySQL 5.1.54 (64 bit Windows build) - and not 5.1.45 (tested by a colleague on this machine) and 5.5.8 (on my machine).
2) If all tables are InnoDB and not MyISAM the 1st SELECT succeeds, but the 2nd SELECT still fails with same error.

(when copying the script above from the webpage you may face the problem that it is copied as UTF8 - and some INSERTS may fails in command line on Windows.  So I executed with SQLyog.  But executing the SELECTS in command line return same error. It is not client-related.

Errors on SELECTS also happen after a fresh restart of the (MySQL) server.
[21 Jan 2011 21:47] Peter Laursen
I realize that it may be hard to understand what I was trying to convey as regards 5.5.8.

So let me clarify that the error does not occur on 5.58.  The SELECT-queries both succeed and returns two rows.
[22 Jan 2011 9:16] Peter Laursen
Also rebooting the system did not remove this error. I have no idea what else I could do.

I have tried so many times now that I think I can exclude the possibility that it is blocked by an AV scanner and similar. It would be nice if the file name was listed with this and similar 'low level file system errors'. 

Simple queries like

 SELECT COALESCE(NULL,LanguageID) a,
      LanguageCode b,
      LanguageNameTextID c,
      LatestChangeStamp d
 FROM LANGUAGE;

.. have no problem with either of the tables.
[22 Jan 2011 9:53] Valeriy Kravchuk
I can not repeat this with current mysql-5.1 on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.56-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE LANGUAGE (
    ->   LanguageID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   LanguageCode CHAR(10) NOT NULL,
    ->   LanguageName VARCHAR(40) NOT NULL,
    ->   LanguageNameTextID INTEGER UNSIGNED NOT NULL,
    ->   LatestChangeStamp TIMESTAMP NOT NULL,
    ->   PRIMARY KEY (LanguageID),
    ->   KEY LanguageCode (LanguageCode),
    ->   KEY LanguageNameTextID (LanguageNameTextID),
    ->   KEY LanguageName (LanguageName) )
    -> ENGINE=INNODB
    -> DEFAULT CHARSET=utf8
    -> COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT LANGUAGE SELECT 1, 'E', 'English', 1, NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT LANGUAGE SELECT 2, 'S', 'Spanish', 2, NULL;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT LANGUAGE SELECT 3, 'ASL', 'American Sign Language', 3, NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT LANGUAGE SELECT 4, 'LSA', 'Argentinian Sign Language', 4, NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE TextMaster (
    ->   TextMasterID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   MasterText TEXT NOT NULL,
    ->   LatestChangeStamp TIMESTAMP NOT NULL,
    ->   PRIMARY KEY (TextMasterID) )
    -> ENGINE=INNODB
    -> DEFAULT CHARSET=utf8
    -> COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT TextMaster SELECT 1, 'English', NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextMaster SELECT 2, 'Spanish', NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextMaster SELECT 3, 'American Sign Language', NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextMaster SELECT 4, 'Argentinian Sign Language', NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE TextTranslation (
    ->   TextTranslationID INTEGER UNSIGNED AUTO_INCREMENT,
    ->   TextMasterID INTEGER UNSIGNED NOT NULL,
    ->   LanguageID INTEGER UNSIGNED NOT NULL,
    ->   TranslatedText TEXT NOT NULL,
    ->   LatestChangeStamp TIMESTAMP NOT NULL,
    ->   PRIMARY KEY (TextTranslationID),
    ->   KEY TextMasterID_LanguageID (TextMasterID, LanguageID) )
    -> ENGINE=INNODB
    -> DEFAULT CHARSET=utf8
    -> COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT TextTranslation SELECT NULL, 1, 1, 'English', NULL;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 2, 1, 'Spanish', NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 3, 1, 'American Sign Language',
    -> NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 4, 1, 'Argentinian Sign Language',
    -> NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 1, 2, 'Inglés', NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 2, 2, 'Español', NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 3, 2, 'Lenguaje de señas
    '> americano', NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 4, 2, 'Lengua de señas argentina',
    -> NULL;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  FROM LANGUAGE l
    ->  INNER JOIN TextMaster tm1
    ->    ON tm1.TextMasterID = l.LanguageNameTextID
    ->  LEFT OUTER JOIN TextTranslation t1
    ->    ON t1.TextMasterID = l.LanguageNameTextID
    ->   AND t1.LanguageID = 2
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName COLLATE utf8_spanish_ci;
+------------+--------------+--------------------+------------------------------+---------------------+
| LanguageID | LanguageCode | LanguageNameTextID | LanguageName                 | LatestChangeStamp   |
+------------+--------------+--------------------+------------------------------+---------------------+
|          3 | ASL          |                  3 | Lenguaje de señas
americano | 2011-01-22 11:50:18 |
|          4 | LSA          |                  4 | Lengua de señas argentina   | 2011-01-22 11:50:18 |
+------------+--------------+--------------------+------------------------------+---------------------+
2 rows in set (0.01 sec)

mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  FROM LANGUAGE l
    ->  INNER JOIN TextMaster tm1
    ->    ON tm1.TextMasterID = l.LanguageNameTextID
    ->  LEFT OUTER JOIN TextTranslation t1
    ->    ON t1.TextMasterID = l.LanguageNameTextID
    ->   AND t1.LanguageID = 2
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName;
+------------+--------------+--------------------+------------------------------+---------------------+
| LanguageID | LanguageCode | LanguageNameTextID | LanguageName                 | LatestChangeStamp   |
+------------+--------------+--------------------+------------------------------+---------------------+
|          4 | LSA          |                  4 | Lengua de señas argentina   | 2011-01-22 11:50:18 |
|          3 | ASL          |                  3 | Lenguaje de señas
americano | 2011-01-22 11:50:18 |
+------------+--------------+--------------------+------------------------------+---------------------+
2 rows in set (0.01 sec)
[22 Jan 2011 10:04] Peter Laursen
I believe that this should be verified on Windows. In the report from where I took the test case it also worked with 5.1.54 on Linux.

I do not exclude that it is something specific for my system. But I found no information in docs about this type of error and how to resolve it. My guess is that it is the multiple JOINs that create temporary file(s) and the error is related to such temporary file.

(and that is why it would be helpful to have the file name in the error message. Without it is hard/impossible to debug). There are a lot of reports here, but I did not find a solution:
http://www.google.dk/search?client=opera&rls=da&q=mysql+%22can't+unlock+file%22+%22error+1...
[22 Jan 2011 10:21] Peter Laursen
emptying /tempdir did not help either.
[22 Jan 2011 10:24] Peter Laursen
add: emptying tempdir did not help either and after executing the SELECTs (with error) /tempdir is still empty. Failure to create temporary table?
[22 Jan 2011 12:49] Valeriy Kravchuk
I can not repeat this on 32-bit Windows XP SP3 with official 5.1.54 binaries also:

...
mysql> INSERT TextTranslation SELECT NULL, 2, 2, 'Español', NULL;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 3, 2, 'Lenguaje de señas
    '> americano', NULL;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 4, 2, 'Lengua de señas argentina',
    -> NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  FROM LANGUAGE l
    ->  INNER JOIN TextMaster tm1
    ->    ON tm1.TextMasterID = l.LanguageNameTextID
    ->  LEFT OUTER JOIN TextTranslation t1
    ->    ON t1.TextMasterID = l.LanguageNameTextID
    ->   AND t1.LanguageID = 2
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName COLLATE utf8_spanish_ci;
+------------+--------------+--------------------+-----------------------------+
---------------------+
| LanguageID | LanguageCode | LanguageNameTextID | LanguageName                |
 LatestChangeStamp   |
+------------+--------------+--------------------+-----------------------------+
---------------------+
|          3 | ASL          |                  3 | Lenguaje de senas
americano | 2011-01-22 14:47:33 |
|          4 | LSA          |                  4 | Lengua de senas argentina   |
 2011-01-22 14:47:33 |
+------------+--------------+--------------------+-----------------------------+
---------------------+
2 rows in set (0.11 sec)

mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  FROM LANGUAGE l
    ->  INNER JOIN TextMaster tm1
    ->    ON tm1.TextMasterID = l.LanguageNameTextID
    ->  LEFT OUTER JOIN TextTranslation t1
    ->    ON t1.TextMasterID = l.LanguageNameTextID
    ->   AND t1.LanguageID = 2
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName;
+------------+--------------+--------------------+-----------------------------+
---------------------+
| LanguageID | LanguageCode | LanguageNameTextID | LanguageName                |
 LatestChangeStamp   |
+------------+--------------+--------------------+-----------------------------+
---------------------+
|          4 | LSA          |                  4 | Lengua de senas argentina   |
 2011-01-22 14:47:33 |
|          3 | ASL          |                  3 | Lenguaje de senas
americano | 2011-01-22 14:47:33 |
+------------+--------------+--------------------+-----------------------------+
---------------------+
2 rows in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.54-community |
+------------------+
1 row in set (0.00 sec)
[22 Jan 2011 12:53] Peter Laursen
Who has a clue then? :-(

It is consistently reproducible for me and google searches find lots of reports with 'error 11' and 'error 13' (for instance) file system errors in lots of developer forums.

Could temporary tables be attempted created with characters that are not valid for file names on Windows (the only idea I have)?
[22 Jan 2011 16:50] Peter Laursen
.. and no further information in error log (including Windows Event Viewer) as far as I can see.
[23 Jan 2011 10:42] Peter Laursen
It is I_S problem.   This is consistently reproducible for me too:

SELECT * FROM information_schema.tables;
-- Error Code : 11
-- Can't unlock file (Errcode: 11)

SELECT * FROM information_schema.tables WHERE table_name LIKE '%';
-- Error Code : 11
-- Can't unlock file (Errcode: 11)

SELECT * FROM information_schema.tables WHERE table_name LIKE 'table';
-- succeeds
[23 Jan 2011 10:48] Peter Laursen
Setting to "S2".
[23 Jan 2011 12:01] Peter Laursen
a clarification:

-- 1)

SHOW GRANTS;
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY ... WITH GRANT OPTION

SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.tables WHERE table_name LIKE '%';
SELECT * FROM information_schema.tables WHERE table_name LIKE 'table';
-- they all succeed

-- 2)

SHOW GRANTS;
/*
GRANT USAGE ON *.* TO 'dc'@'localhost'                
GRANT SELECT ON `testing`.`table1` TO 'dc'@'localhost'
*/
 
SELECT * FROM information_schema.tables;
-- Error Code : 11
-- Can't unlock file (Errcode: 11)

SELECT * FROM information_schema.tables WHERE table_name LIKE '%';
-- Error Code : 11
-- Can't unlock file (Errcode: 11)

SELECT * FROM information_schema.tables WHERE table_name LIKE 'table';
-- succeeds

.. maybe this and http://bugs.mysql.com/bug.php?id=59677 are related?
[23 Jan 2011 15:44] Peter Laursen
I double-checked (after server restart , reboot etc.). On 5.1.54 this is consistently reproducible:

SHOW GRANTS;
/*
GRANT USAGE ON *.* TO 'dc'@'localhost'                
GRANT SELECT ON `testing`.`table1` TO 'dc'@'localhost'
*/
 
SELECT * FROM information_schema.tables;
-- Error Code : 11
-- Can't unlock file (Errcode: 11)

SELECT * FROM information_schema.tables WHERE table_name LIKE '%';
-- Error Code : 11
-- Can't unlock file (Errcode: 11)

SELECT * FROM information_schema.tables WHERE table_name LIKE 'table';
-- succeeds

If user is 'root' then all statements succeeds. On 5.5.8 all statements succeed as bot 'root' and 'dc' users.

On 5.1.54 as root: 
SELECT COUNT(*) FROM information_schema.tables -- 129 (correct)
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema LIKE 'information_schema' -- 28

On 5.1.54 as 'dc' user: 
SELECT COUNT(*) FROM information_schema.tables -- 29 (should be 30)
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema LIKE 'information_schema'-- 28

It looks very much like even pretty small temporary tables on Windows easily causes files system errors on recent 5.1 and 5.5 servers. I have no clue why it happens with non-privileged user and not 'root' user.
[23 Jan 2011 15:49] Peter Laursen
I can share my 5.1.54 /datadir if it helps (it is a few hundred KB's only).  I had only one database with private data. I dropped it and it is still reproducible.
[23 Jan 2011 21:25] Santo Leto
Can not repeat this with version 5.1.54 64 bit on Windows 7 64-bit.
Peter, can you please download a new mysql archive and try if you can repeat the problem on a fresh installation?

Thanks

--
mysql> create database bug59663;
Query OK, 1 row affected (0.25 sec)

mysql> use bug59663
Database changed
mysql>
mysql> CREATE TABLE LANGUAGE (
    ->   LanguageID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   LanguageCode CHAR(10) NOT NULL,
    ->   LanguageName VARCHAR(40) NOT NULL,
    ->   LanguageNameTextID INTEGER UNSIGNED NOT NULL,
    ->   LatestChangeStamp TIMESTAMP NOT NULL,
    ->   PRIMARY KEY (LanguageID),
    ->   KEY LanguageCode (LanguageCode),
    ->   KEY LanguageNameTextID (LanguageNameTextID),
    ->   KEY LanguageName (LanguageName) )
    -> ENGINE=INNODB
    -> DEFAULT CHARSET=utf8
    -> COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT LANGUAGE SELECT 1, 'E', 'English', 1, NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT LANGUAGE SELECT 2, 'S', 'Spanish', 2, NULL;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT LANGUAGE SELECT 3, 'ASL', 'American Sign Language', 3, NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT LANGUAGE SELECT 4, 'LSA', 'Argentinian Sign Language', 4, NULL;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE TextMaster (
    ->   TextMasterID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   MasterText TEXT NOT NULL,
    ->   LatestChangeStamp TIMESTAMP NOT NULL,
    ->   PRIMARY KEY (TextMasterID) )
    -> ENGINE=INNODB
    -> DEFAULT CHARSET=utf8
    -> COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> INSERT TextMaster SELECT 1, 'English', NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextMaster SELECT 2, 'Spanish', NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextMaster SELECT 3, 'American Sign Language', NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextMaster SELECT 4, 'Argentinian Sign Language', NULL;
Query OK, 1 row affected (0.24 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE TextTranslation (
    ->   TextTranslationID INTEGER UNSIGNED AUTO_INCREMENT,
    ->   TextMasterID INTEGER UNSIGNED NOT NULL,
    ->   LanguageID INTEGER UNSIGNED NOT NULL,
    ->   TranslatedText TEXT NOT NULL,
    ->   LatestChangeStamp TIMESTAMP NOT NULL,
    ->   PRIMARY KEY (TextTranslationID),
    ->   KEY TextMasterID_LanguageID (TextMasterID, LanguageID) )
    -> ENGINE=INNODB
    -> DEFAULT CHARSET=utf8
    -> COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql> INSERT TextTranslation SELECT NULL, 1, 1, 'English', NULL;
Query OK, 1 row affected (0.42 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 2, 1, 'Spanish', NULL;
Query OK, 1 row affected (0.28 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 3, 1, 'American Sign Language',
    -> NULL;
Query OK, 1 row affected (0.25 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 4, 1, 'Argentinian Sign Language',
    -> NULL;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 1, 2, 'Inglés', NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 2, 2, 'Español', NULL;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 3, 2, 'Lenguaje de señas
    '> americano', NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT TextTranslation SELECT NULL, 4, 2, 'Lengua de señas argentina',
    -> NULL;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> -- Here are the queries:
mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  FROM LANGUAGE l
    ->  INNER JOIN TextMaster tm1
    ->    ON tm1.TextMasterID = l.LanguageNameTextID
    ->  LEFT OUTER JOIN TextTranslation t1
    ->    ON t1.TextMasterID = l.LanguageNameTextID
    ->   AND t1.LanguageID = 2
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName COLLATE utf8_spanish_ci;
+------------+--------------+--------------------+-----------------------------+
---------------------+
| LanguageID | LanguageCode | LanguageNameTextID | LanguageName                |
 LatestChangeStamp   |
+------------+--------------+--------------------+-----------------------------+
---------------------+
|          3 | ASL          |                  3 | Lenguaje de señas
americano | 2011-01-23 22:05:35 |
|          4 | LSA          |                  4 | Lengua de señas argentina   |
 2011-01-23 22:05:35 |
+------------+--------------+--------------------+-----------------------------+
---------------------+
2 rows in set (0.24 sec)

mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  FROM LANGUAGE l
    ->  INNER JOIN TextMaster tm1
    ->    ON tm1.TextMasterID = l.LanguageNameTextID
    ->  LEFT OUTER JOIN TextTranslation t1
    ->    ON t1.TextMasterID = l.LanguageNameTextID
    ->   AND t1.LanguageID = 2
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName;
+------------+--------------+--------------------+-----------------------------+
---------------------+
| LanguageID | LanguageCode | LanguageNameTextID | LanguageName                |
 LatestChangeStamp   |
+------------+--------------+--------------------+-----------------------------+
---------------------+
|          4 | LSA          |                  4 | Lengua de señas argentina   |
 2011-01-23 22:05:35 |
|          3 | ASL          |                  3 | Lenguaje de señas
americano | 2011-01-23 22:05:35 |
+------------+--------------+--------------------+-----------------------------+
---------------------+
2 rows in set (0.26 sec)

mysql> select user(), connection_id(), version();
+----------------+-----------------+----------------------+
| user()         | connection_id() | version()            |
+----------------+-----------------+----------------------+
| root@localhost |               1 | 5.1.54-community-log |
+----------------+-----------------+----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'version_compile_os';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| version_compile_os | Win64 |
+--------------------+-------+
1 row in set (0.00 sec)
[23 Jan 2011 22:11] Peter Laursen
well .. anything you can suggest .. but

1) 'archive'?? I can uninstall as Windows user normally would a do a fresh install

2) no matter what I observe, you should observe the hundreds of similar reports in developer forums. THERE IS A PROBLEM!

Anyway .. let me try to backup my data folder .. uninstall ..  do a fresh install and try it with both an fresh datadir and and my existing datadir. I have a habit of creating very silly table etc. names (for testing our own application) so what is saved in my existing datadir may be the problem.
[24 Jan 2011 9:09] Santo Leto
Okay, thanks.

Yeah, I was suggesting to use the zip archive so that you can set up a second test server without making any uninstallation. I was wondering if on your machine you can reproduce the bug as you reported with antoher new server. Using the archive is simple. You can unzip it on a temporary folder (your desktop, for example), and start it with a command like this:

mysqld.exe --defaults-file="pth-to-your-server\my-small.ini" -P51541

(make sure you use a different port than the port your other server is using, for example 51541).
[24 Jan 2011 10:05] Peter Laursen
OK .. a 'parallel' 5.1.54 is a good idea. I can then try with a fresh and the old datadir.
[24 Jan 2011 10:19] Peter Laursen
.. but it could take a few days before I get time.
[24 Jan 2011 10:31] Valeriy Kravchuk
As for your example with poor dc@localhost user, I can not repeat is also:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -udc -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| dc@localhost   |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1 limit 1;
Empty set (0.02 sec)

mysql> show grants;
+-------------------------------------------------+
| Grants for dc@localhost                         |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'localhost'          |
| GRANT SELECT ON `test`.`t1` TO 'dc'@'localhost' |
+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.tables limit 1\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: information_schema
     TABLE_NAME: CHARACTER_SETS
     TABLE_TYPE: SYSTEM VIEW
         ENGINE: MEMORY
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: 384
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 9437184
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: max_rows=24576
  TABLE_COMMENT:
1 row in set (0.01 sec)

mysql> SELECT * FROM information_schema.tables where table_name like '%' limit 1
\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: information_schema
     TABLE_NAME: CHARACTER_SETS
     TABLE_TYPE: SYSTEM VIEW
         ENGINE: MEMORY
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: 384
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 9437184
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: max_rows=24576
  TABLE_COMMENT:
1 row in set (0.01 sec)

Looks like need your data directory to find out what's the real difference.
[24 Jan 2011 19:46] Peter Laursen
Not reproducible with a unzipped archive started from command-line and with only the tables referenced created as suggested. 

The data folder from the affected instance (5.1.54) has been uploaded as error1data.zip

So undoubtedly some kind of corruption (at least if reproducible with my data). But error message and docs do not provide any information on how to resolve it.
[24 Jan 2011 22:59] MySQL Verification Team
i used the provided datadir and user, but still no error. see attached. maybe i need the my.ini file?

Attachment: bug59663_output_with_datadir_cannot_repeat.txt (text/plain), 13.05 KiB.

[24 Jan 2011 23:02] Peter Laursen
my.ini (actually almost 100% generated from by config wizard).

Attachment: my.ini (application/octet-stream, text), 8.76 KiB.

[24 Jan 2011 23:07] MySQL Verification Team
The problem is with external_locking....
[24 Jan 2011 23:08] Peter Laursen
I do not claim that this is not some issue with my system (only there are hundreds similar in developer forums). I just do not find any information anywhere on how to resolve it. Mostly because the error message does not list the file. I believe it is a TEMP-file (the query from I_S indicates it) but except for this I have no clue at all.

I could dump --all-databases and import to a fresh install, if you think! But please at least provide the file name in the error message for 'error 11', 'error 13' etc.
[24 Jan 2011 23:10] Peter Laursen
ahhh .. Trackdog Shane is getting closer!
[24 Jan 2011 23:17] Peter Laursen
Ok .. I have put 'external-locking' in config. I forgot this.  I added it when checking this: http://bugs.mysql.com/bug.php?id=58348

Thanks. I can remove that and it will undoubtedly be solved for me.
[24 Jan 2011 23:19] MySQL Verification Team
a DBUG snippet of where the first error comes from

Attachment: bug59663_trying_to_unlock_a_tmpfile.txt (text/plain), 6.71 KiB.

[24 Jan 2011 23:26] MySQL Verification Team
easy to repeat on a clean installation.

1. start server with --external_locking
2. login with an under privileged user
3. select * from information_schema.COLUMNS;

e.g.

I:\mysql\5.1\5.1.54\mysql-advanced-gpl-5.1.54-winx64\bin>mysqld --external_locking --console
110125  1:22:14 [Note] Plugin 'FEDERATED' is disabled.
110125  1:22:14  InnoDB: Initializing buffer pool, size = 8.0M
110125  1:22:14  InnoDB: Completed initialization of buffer pool
110125  1:22:15  InnoDB: Started; log sequence number 0 44233
110125  1:22:15 [Note] Event Scheduler: Loaded 0 events
110125  1:22:15 [Note] mysqld: ready for connections.
Version: '5.1.54-enterprise-gpl-advanced'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition (GPL)

mysql> show grants;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.columns;
ERROR 11 (HY000): Can't unlock file (Errcode: 11)
mysql>