Bug #12795 Non-locking ALTER TABLE for updating UNION of MERGEs
Submitted: 25 Aug 2005 2:23 Modified: 22 Feb 2006 11:42
Reporter: Lachlan Mulcahy Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S4 (Feature request)
Version:4.1.12, 5.0.x OS:Any (Any)
Assigned to: CPU Architecture:Any

[25 Aug 2005 2:23] Lachlan Mulcahy
Description:
Currently an ALTER TABLE that changes the UNION information for an existing MERGE table will block if there are any locks on the MERGE table itself or any of it's subtables. 

It would be great if the blocking that occurs could be relaxed greatly so that the UNION information for active MERGE tables could be updated more easily.

How to repeat:
N/A

Suggested fix:
Here are some examples that illustrate how the locking could be relaxed.

Eg 1.

1. A long running UPDATE is occurring on the 'logs_20050101' table, which is one of many in the UNION that makes up the 'logs' table.
2. An ALTER is issued to add a new underlying table to the 'logs' MERGE.
 
If there are any queries acting on the underlying tables, this should not effect the ability to ALTER the MERGE table itself.

Eg 2.

1. A long running UPDATE is occurring on the 'logs' table, which is a MERGE of many log tables.
2. An ALTER is issued to add a new underlying table to the 'logs' MERGE.

The UPDATE should continue running using the pre-ALTER definition of the MERGE, the ALTER should complete and any new queries will see the newly defined UNION.
[22 Feb 2006 11:42] Valeriy Kravchuk
Thank you for a reasonable feature request.