Bug #72497 LOAD DATA INFILE with MERGE tables containing compressed MyISAM tables
Submitted: 1 May 2014 0:53 Modified: 17 Jul 2014 22:06
Reporter: Manuel Villasante Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:5.6.10, 5.6.21 OS:Linux
Assigned to: CPU Architecture:Any
Tags: LOAD DATA, merge, myisampack
Triage: Needs Triage: D2 (Serious)

[1 May 2014 0:53] Manuel Villasante
Description:
I have done a thorough search and have not seen this issue reported anywhere, much to my surprise.

I wanted to archive some large tables, so I decided to use the procedure:

1) Created identical MyISAM tables table_1, table_2, table_current.
2) Compressed with myisampack the first two tables but not table_current.
3) Created a merge table test_merge with identical structure and using

ENGINE=MERGE UNION=(table_1,table_2,table_current) INSERT_METHOD=LAST;

4) Made sure that the merge table is properly created by querying it and using "show create table"

5) Tried to add data using 

LOAD DATA LOCAL INFILE 'filepathname' INTO TABLE test_merge

and got the following error:

ERROR 1036 (HY000): Table 'table_1' is read only

I expected the records to be added to table_current and, thus, to test_merge.  Table table_current is not compressed and not "read only".

In fact doing 

LOAD DATA LOCAL INFILE 'filepathname' INTO TABLE table_current 

DOES WORK.

If you skip step 2) above (table compression), there is no problem and you will see at the end that the "LOAD ... INTO TABLE test_merge" command puts records in table_current and not the other tables.  Thus the above error is not caused because this command tries to put records in the other (initially compressed) tables, but rather the mere fact that they are compressed "bothers it".

If one uses INSERT_METHOD=FIRST and puts the uncompressed table first in the list, the problem remains the same, as expected.

How to repeat:
To reproduce one can follow a simplified version of the above:

I) Create the tables using a convenient existing table:
CREATE TABLE test_1 LIKE SomeTable;
CREATE TABLE test_2 LIKE SomeTable;
CREATE TABLE test_merge LIKE SomeTable;
ALTER TABLE test_merge ENGINE=MERGE UNION=(test_1,test_2) INSERT_METHOD=LAST;

(if there are unique indices, one would have to create test_merge in a more pedestrian way, of course)

II) Insert records into tables test_1 and test_2 (one needs to insert enough records in test_1 so that myisampack will not refuse to compress it):
INSERT INTO test_1 SELECT * FROM SomeTable LIMIT 1000;
INSERT INTO test_2 SELECT * FROM SomeTable LIMIT 1;

III) Run myisampack test_1

IV) Create a file for import:
SELECT * INTO OUTFILE '/tmp/test_data' FROM SomeTable LIMIT 1,3;

V) Load the file:

LOAD DATA LOCAL INFILE '/tmp/test_data' INTO TABLE test_merge;

VI) Enjoy the error.
[17 Jul 2014 22:06] Sveta Smirnova
Thank you for the report.

Verified as described.
[22 Oct 2016 10:09] Birger .
This used to work as expected in Debian Squeeze (MySQL version 5.1.72) and is broken as described from at least Debian Wheezy (MySQL version 5.5.49).