Bug #41364 CHECK TABLE does not work with --external-locking on Windows
Submitted: 10 Dec 2008 17:26
Reporter: Ingo Strüwing Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:6.0 and probably earlier OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[10 Dec 2008 17:26] Ingo Strüwing
Description:
This does not happen on UNIX, because we do not use mandatory file locking there. 

Mandatory file locking allows write operations only if the writer has a write lock on the file. It allows read operations only if there is a read or write lock on the file.

Advisory file locking does not constrain reads or writes. It just synchronizes file locking methods. Well behaving applications do not write without acquiring a write lock first. They dont read without acquiring a read or write lock first. But they are not hindered by the operating system to misbehave and read or write without having an appropriate lock.

On Windows we use locking calls, that do mandatory locking. So our programs are forced to use proper locks.

The problem is that CHECK TABLE uses a read lock on the table, so that other threads can continue to read the table. After checking a MyISAM table, it updates the state info. That is, it writes to the begin of the index file though it does only have a read lock on the table.

This is a clear misbehavior. UNIX tolerates it as it does not enforce proper locks. But Windows refuses the write and reports an error.

Fortunately the impact is relatively low. The server executes file locking only with the --external-locking option. This is used to synchronize two or more servers that work on the same tables. Pretty uncommon, especially on Windows. Another use case is to allow the use of myisamchk in parallel to an active server. With proper administrative procedures, parallel work on the same tables can be excluded and the option is not required.

How to repeat:
Add --external-locking to mysql-test/t/myisamlog-master.opt and run the myisamlog test case. You will see:

main.myisamlog                 [ fail ]

--- .../mysql-test/r/myisamlog.result        2008-12-08 09:12:25.859656000 +0300
+++ ...\mysql-test\r\myisamlog.reject        2008-12-10 20:12:15.948931200 +0300
@@ -44,5 +44,6 @@
 5      A knife
 check table t1 extended;
 Table  Op      Msg_type        Msg_text
-mysqltest.t1   check   status  OK
+mysqltest.t1   check   error   13 when updating keyfile
+mysqltest.t1   check   error   Corrupt
 drop database mysqltest;

mysqltest: Result length mismatch

Suggested fix:
This may be tricky. But perhaps it could be possible to start with TL_WRITE_ALLOW_READ and upgrade it later to TL_WRITE. This should not risk deadlocks as we do only lock one table. If this approach is accepted, we need to check if this changes the lock type on MyISAM level. Otherwise we may need to change it explicitly.
[10 Dec 2008 18:19] Ingo Strüwing
Suggested triage values:
Defect class medium. It makes the CHECK TABLE statement unusable.
Workaround partial. One can use myisamchk, but it requires administrative procedures to prevent access to the table by the server during the check.
Impact minimal. Who will ever want to use --external-locking on Windows?