Bug #58348 skip_external_locking is not considered by CSV
Submitted: 20 Nov 2010 15:13 Modified: 13 Jan 2011 15:46
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.53 - any, I guess OS:Any (MS Windows, all)
Assigned to: Paul Dubois CPU Architecture:Any
Tags: qc

[20 Nov 2010 15:13] Peter Laursen
Description:
skip_external_locking is not considered by CSV

How to repeat:
SHOW VARIABLES LIKE 'skip_external_locking'; -- ON

CREATE TABLE `csvdata` (
  `a` INT(11) NOT NULL,
  `b` INT(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8

-- now open the data file csvdata.CSV in another program (a spreadsheet, a text editor)

INSERT INTO csvdata VALUES (1,2); -- success; 
-- MySQL does not respect that the file is open in another application.

Suggested fix:
I think the major reason that people would want to use CSV is that want to read files in the above mentioned types of programs.  There is no risk of index corruption of course, but still a risk of data corruption, I think.

Documentation at:
http://dev.mysql.com/doc/refman/5.1/en/external-locking.html
.. only tells about MyISAM tables. If the option only works with MyISAM it should at least be documented explicitly that "this option only has effect with MyISAM tables"

and
http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html:
"This format can be read, and even written, by spreadsheet applications such as Microsoft Excel or StarOffice Calc."
.. yes but it is risky (on Windows at least as any user may modify the file with the file system privileges normally used by by the MySQL server on Windows.
[20 Nov 2010 15:17] Peter Laursen
pasting error in the original post. It should be OFF not OFF of course:

SHOW VARIABLES LIKE 'skip_external_locking';
/*
Variable_name          Value 
---------------------  ------
skip_external_locking  OFF 
*/
[20 Nov 2010 16:11] Peter Laursen
It should be OFF .. not ON .. hopefully I get it right this time ;-)
[21 Nov 2010 10:43] Peter Laursen
One more consideration: Except for CSV there may be 3rd party engines that are accessed by other programs in parallel and will require 'skip_external_locking' handled. It could also be interesting with BDB (where data files can be read by other programs), with 'NoSQL' implementations making use of files in the MySQL data folder and running in parallel with the MySQL server. 

But I do not know if same problem exists with other engines than CSV.
[22 Nov 2010 23:29] Sveta Smirnova
Thank you for the report.

Verified as described. Thought this is more documentation issue as MyISAM is only engine who uses this feature and manual not clearly says about it.
[24 Nov 2010 16:35] Peter Laursen
Maybe this option only applies to MyISAM currently.  But I think the SE API should have an option for enable it for a custom engine.  It is risky to have two applications writing to the same file at the very same moment (what is possible with multi-core machines). Not sure about other OS's but on Windows/NTFS it may lead to total file corruption. For same reason I think CSV should honour the option.
[13 Jan 2011 15:46] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

"Documentation at:
http://dev.mysql.com/doc/refman/5.1/en/external-locking.html
.. only tells about MyISAM tables. If the option only works with MyISAM it should at
least be documented explicitly that "this option only has effect with MyISAM tables"

I have modified such references to note this applies only to MyISAM.

"http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html:
"This format can be read, and even written, by spreadsheet applications such as Microsoft
Excel or StarOffice Calc."
.. yes but it is risky (on Windows at least as any user may modify the file with the file
system privileges normally used by by the MySQL server on Windows."

I didn't make any change here. Users should not *modify* files corresponding to MySQL database tables; this is dangerous even if you take precautions to make sure the server is stopped.