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.
