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: | |
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
[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>