MySQL Bugs: #73296: skip_external

Bug #73296	skip_external_locking not working
Submitted:	15 Jul 2014 16:46	Modified:	19 Aug 2014 15:47
Reporter:	Barry Banhagel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Windows	Severity:	S3 (Non-critical)
Version:	5.5.30	OS:	Windows (2008 R2 Enterprise)
Assigned to:		CPU Architecture:	Any
Tags:	skip_external_locking

Description:
24/7 Database has only MyISAM tables, skip_external_locking = on 
Application temp files begin as soon as robocopy job starts indicating that the table files are seen as being locked.  

How to repeat:
Run a robocopy job using MySQL data folder as the source; 
While the table is being copied; perform a 'load data infile' ; 
I use the /MIR /MT:128 and /NP options in my bat file.

In my understanding 'external locking' (http://dev.mysql.com/doc/refman/5.5/en/external-locking.html) in MySQL has nothing to do with if *FILES* are locked (at OS & file system-level), but rather if *TABLES* are locked (at MySQL server & storage engine level).

MySQL cannot change the file locking that the OS does.

-- Peter
-- not a MySQL/Oracle person.

BTW: I wonder if you are trying to use robocopy for backup?

Also read http://en.wikipedia.org/wiki/Robocopy:

"Robocopy will not copy open files."

Thank you for the report.

What happens in your environment when you "While the table is being copied; perform a 'load data infile' ; "?

Peter, thanks for your comments. Tables using MyISAM engine are nothing more than files.  External locking means that MySQL does in fact look to the OS for file system locks, hence the name 'external' as in: not internal to MySQL.
When MySQL sees a file system lock; it will wait. (unless skip_external_locking=on)   
I'm not having a problem with Robocopy. I'll re-state: I'm seeing reproducible database behavior which is inconsistent with the setting to ignore the file reading.

Hello Sveta, I'd be happy to answer your question, although, robocopy is doing exactly what I expect it to do. IF Robocopy comes across an open table, it adheres to the /R and /W options (it can't copy an open file). What's not doing what I expect is: skip_external_locking=on

Thank you for the feedback.

> Hello Sveta, I'd be happy to answer your question, although, robocopy is doing exactly what I expect it to do. IF Robocopy comes across an open table, it adheres to the /R and /W options (it can't copy an open file). What's not doing what I expect is: skip_external_locking=on

I am sorry, but I don't understand what is the bug than. Do you complain that robocopy waits when MySQL finishes "LOAD DATA INFILE" job?

The Problem is: MySQL appears to be waiting.
MySQL, if configured to skip_external_locking, should NOT be waiting for anything that might be 'reading' one of its files. That is its single purpose. When I say “skip_external_locking is not working”, then it’s not doing the only thing that it’s designed to do. 
Forget Robocopy, it could be anything else reading the file. Robocopy is not the problem, it merely illustrates it.  Thanks

Dear Barry,

Thank you for starting this discussion.

Seems that you have got wrong info on what is external locking. Simply, if external locking is set to ON, then anytime some MyISAM file (.frm, .MYD, .MYI) is to be open on the file system, system locking is applied. By system locking we presume locking by the OS (Operating System). On *nix, it means a call to fcntl() function with F_SETLOCK parameter. On Windows, it is a call to a Win32 function win_lock().

So, when MyISAM table is to be opened by our server, if external locking is NOT disabled, then first all three of the relevant MyISAM table's files are locked by the call to the external OS function.

When skip_external_locking is used, then opening of the MyISAM tables proceeds WITHOUT system locks !!!

And that is all to it. This has NOTHING to do with MySQL server waiting or not waiting for the files locked by some other process.

If our documentation states anywhere that this option has anything to do with locks set by some other processes, please do us a favor and forward us to the chapter that caused this confusion.

Thanks for your reply.
Reference is MySQL documentation:
8.7.4 External Locking
"If the server is run with external locking enabled, you can use myisamchk at any time for read operations such a checking tables. In this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk to finish before it continues"….”With external locking in effect, each process that requires access to a table acquires a file system lock for the table files before proceeding to access the table. If all necessary locks cannot be acquired, the process is blocked from accessing the table until the locks can be obtained (after the process that currently holds the locks releases them).” ….”External locking affects server performance because the server must sometimes wait for other processes before it can access tables”.

The word "wait" is used more than once in the documentation regarding External Locking. The word ‘until’ also implies that MySQL will be waiting until the file system locks are acquired. Immediate file system lock acquisition is not guaranteed, there is a logical conclusion that MySQL engine have to wait to obtain those anytime external locking is enabled.
My interpretation of that reading is: if you enable…server will wait. If I understand your point correctly, you are saying that MySQL will observe file system locks whether external locking is enabled or disabled.

The issue I reported is that MySQL seems to be waiting while Robocopy reads. If External locking is indeed "the use of file system locking to manage contention for MyISAM database tables by multiple processes", how then is it used to manage this contention?

Barry,

Yes, your interpretation of the chapter is largely correct. Also, chapter is correctly written.

When external locking is enabled for the server and it is executed by the OS, then OS will prevent any other program for opening the file(s). Hence, the external locking exists to prevent other programs in opening MyISAM files while MySQL server is accessing them. As long as MySQL server is using the files, all other programs have to wait during the execution of the opening of the files. When server is finished with the operation it releases the locks and other programs can open the files.

When --skip-external-locking is used, then the step of locking the files by OS is skipped.

This feature and option have NOTHING to do with other programs locking the files. When MySQL server asks operating system (OS) to open the file, locked by other program, with open() system call, then OS will make MySQL to wait. Waiting is not happening in MySQL code, but in the server kernel !!! MySQL is issuing a system call and it s not returning. It is held by the OS kernel, which will return the control to MySQL once the other program closes the file. Hence, we can not have the option to force OS to open the locked file to us. Such an option does not exist.

This has nothing to do with