| Bug #47097 | ALTER <existing table> ENGINE = MERGE/BLACKHOLE ... is allowed + data loss | ||
|---|---|---|---|
| Submitted: | 3 Sep 2009 10:55 | Modified: | 17 Oct 2009 6:37 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.0+ | OS: | Any |
| Assigned to: | Jon Stephens | CPU Architecture: | Any |
| Tags: | blackhole, merge | ||
[10 Sep 2009 14:31]
Georgi Kodinov
The fix for bug #10952 that introduced this behavior was reverted. But the addition to the docs stayed. Can we please update the docs so they document the current reality ? I don't think re-introducing the behavior described in bug #10952 is a good idea because it was unconditionally preventing an action that some of our users may actually perform intentionally.
[16 Sep 2009 9:20]
Jon Stephens
Assigning this one to myself.
[17 Sep 2009 6:37]
Jon Stephens
Was this change ever actually introduced in 5.1 or later? I find no evidence that BUG#10952 was ever merged upward beyond 5.0. Is this change was merged up, when was it done, and when was it reverted? (We need three-part version numbers for the Manual.) If it was never merged up, we just need this confirmed, otherwise we need to know which trees and releases were affected by this. Thanks!
[17 Oct 2009 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: The MySQL manuals 5.0 till 6.0 say: To prevent inadvertent loss of data, ALTER TABLE cannot be used to change the storage engine of a table to MERGE or BLACKHOLE. The 5.0 manual adds "As of MySQL 5.0.23". My script: ---------- --disable_warnings DROP TABLE IF EXISTS t1,t2; --enable_warnings CREATE TABLE t1 (f1 varchar(20)); INSERT INTO t1 VALUES('This is table t1'); CREATE TABLE t2 LIKE t1 ; INSERT INTO t2 VALUES('This is table t2'); ALTER TABLE t2 ENGINE = MERGE UNION (t1); SELECT * FROM t2; ALTER TABLE t1 ENGINE = BLACKHOLE; SELECT * FROM t1; # Cleanup DROP TABLE t1,t2; The outcome: ------------ CREATE TABLE t1 (f1 varchar(20)); INSERT INTO t1 VALUES('This is table t1'); CREATE TABLE t2 LIKE t1 ; INSERT INTO t2 VALUES('This is table t2'); ALTER TABLE t2 ENGINE = MERGE UNION (t1); SELECT * FROM t2; f1 This is table t1 # Total loss of old content # of t2. ALTER TABLE t1 ENGINE = BLACKHOLE; SELECT * FROM t1; f1 # Total loss of content of t1 My environment: --------------- - mysql-5.0-bugteam late August 2009 - mysql-5.1-bugteam late August 2009 - mysql-6.0-bugteam 2009-09-03 - ./BUILD/compile-pentium64-debug-max - Linux OpenSuSE 11.0 (64 Bit) - Intel Core2Duo BTW: Bug#10635 Loss of data when converting from InnoDB to MERGE closed with "Not a Bug" does not apply. There we had MySQL 4.1.11 and most probably different statements within the manual. How to repeat: See above Suggested fix: Please disallow ALTER TABLE <existing table> ENGINE = MERGE or BLACKHOLE if the existing table is not empty. Why not disallow ALTER ... ENGINE = MERGE or BLACKHOLE in general like the manual says? It is quite convenient to do actions like CREATE TABLE t1 ... ENGINE = MyISAM; CREATE TABLE t2 LIKE t1; CREATE TABLE t3 LIKE t1; By using "LIKE" t1 - t3 have all the same structure which is required for merge tables. ALTER TABLE t3 ENGINE = MERGE UNION(t1,t2);