MySQL Bugs: #35906: Myisampack --join (merge) option doesn't work completely

Bug #35906	Myisampack --join (merge) option doesn't work completely
Submitted:	8 Apr 2008 16:05	Modified:	7 May 2008 17:02
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	merge, myisampack

Description:
The merge option for myisampack does not work completely.

Trying to merge existing MyISAM tables t1 and t2 as follows (where t1 and t2 are identical structure --data type, column names, etc:

myisampack --join=t3 t1 t2

Results in t3.MYD and t3.MYI being created, but no t3.FRM file.  As a result, table t3 is not recognized.

Trying to pre-create t3 prior to the merge (CREATE'ing it the same as t1 and t2) results in myisampack returning an error.

The same can be said of:
1)  Trying to use t1 or t2 as the --join value: myisampack --join=t1 t1 t2
2)  Including a non-existent t3 in the list of arguments:  myisampack --join=t3 t1 t2 t3
3)  Including a pre-existing t3 in the list of arguments

If I copy the .FRM file from t1 or t2 as t3.FRM, everything is fine -- 
t3 is read only
t3 contains the data from t1 and t2

How to repeat:
Run the attached ./mysql-test-run files with the --record option.

The fail_1 file will illustrate how a new merge table will not have a .FRM file

The fail_2 file will show how using one of the existing tables as the merge table will fail

The fail_3 file will show a failure when pre-creating the target table before the merge.

The pass file will show how copying the .FRM file of a merged table will result in the merge being successful.

*Will attach after creation of Bug Report.

Suggested fix:
Ensure the merge process works thoroughly
1)  The creation of a new resultant merge table will be 100% (.FRM produced)

2)  Should you be able to use an existing table as a merge target?  myisampack --join=t1 t1 t2

3)  Documentation should reflect all the possibilities of a merge -- must make a new table, can merge into an existing table (don't need to create a new table), etc.

4)  Need to document that using myisampack --join=<target> <source> <source>.... does NOT result in packing of the <source> tables.  (verified behavior -- see pass file)

Passing test case w/ hack to complete merge

Attachment: myisampack_pass.test (, text), 1.05 KiB.

First failure example

Attachment: myisampack_fail_1.test (, text), 944 bytes.

Second Failure example

Attachment: myisampack_fail_2.test (, text), 643 bytes.

Third Failure example

Attachment: myisampack_fail_3.test (, text), 733 bytes.

As a workaround, you can just copy a .FRM file from one of the source tables to <merged_table_name>.FRM to be able to use the new table.

This is a documentation issue.

myisampack behaves as expected.  As this isn't a part of MySQL (feature of the MyISAM storage engine instead), it will not create .FRM files.

For a user to access the merged tables via MySQL, they must make a copy of one of the source tables' .FRM files as <merge_table_name>.FRM.

Documentation should probably note:
1)  Must make a copy of .FRM file for merged table from a source tables' .FRM
2)  Will not compress the source tables: myisampack -join=t3 t1 t2 will only make t3 compressed / read-only
3)  Cannot use a pre-existing table for the merged table
      myisampack --join=t1 t1 t2
      myisampack --join=t3 t1 t2 
Both will fail if t3 is already created before invoking myisampack

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.

Added:

big_tbl_name must not exist prior to the join operation. All source
tables named on the command line to be merged into big_tbl_name must
exist. The source tables are read for the join operation but not
modified. The join operation does not create a .frm file for
big_tbl_name, so after the join operation finishes, copy the .frm
file from one of the source tables and name it big_tbl_name.frm.

I have filed Bug#36573 as a feature request for myisampack to create the destination table .frm file by copying it from one of the source tables.