Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 5.5.36, 10.0.10
-
Fix Version/s: 10.0.12
-
Component/s: None
-
Labels:None
-
Environment:32-Bit Debian 7
Description
When adding or removing a non-materialized virtual column to/from a MyISAM table with ALTER TABLE, MariaDB recreates the whole table although only the metadata should be affected:
MariaDB [test]> ALTER TABLE test ADD COLUMN testcolumn INT AS (othercolumn+1) VIRTUAL; Stage: 1 of 2 'copy to tmp table' 0.001% of stage done
This is especially annoying for multi-gigabyte tables, where this operation takes a long time.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Sergei Golubchik, please review fix for this bug.
ok to push!
Fixed in 10.0.12:
revno: 4218
revision-id: svoj@mariadb.org-20140603125729-k5htqiobnba0wqsj
parent: psergey@askmonty.org-20140603202627-ior1c69y85kz1ggd
committer: Sergey Vojtovich <svoj@mariadb.org>
branch nick: 10.0-mdev6103
timestamp: Tue 2014-06-03 16:57:29 +0400
message:
MDEV-6103 - Adding/removing non-materialized virtual column triggers
table recreation
Relaxed InnoDB/XtraDB checks to allow online add/drop of
non-materialized virtual columns.
Shouldn't this bug be split? As I understand, this issue is now fixed for InnoDB. I saw this issue with MyISAM, which is still affected.
See MDEV-6303.
It came to be that MyISAM (and most probably Aria) reserves extra bits for virtual columns (NULL flag) in a physical record (the one that is written to disk). There should be no good reason to do so.
E.g.:
create table t1 (b int, d int); # Uses 2nd and 3rd bits for NULL flags
create table t2 (a int as (-b), b int, c int as (-b), d int); # Uses 3rd and 5th bits for NULL flags
All in all it means physical record format changes when adding/dropping virtual columns and just relaxing table comparison function is not enough to fix this bug.
I will attach partial fix for this bug shortly. InnoDB doesn't seem to be affected.