Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.22, 10.0.23, 10.0.28, 10.0.30, 10.0.31
-
debian
Description
As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data :
Over time, more operations have been made possible without a full copy. Here is a list of the actions that can be performed without having to copy the table:
|
|
|
Adding a new enum option last to a list
|
However that looks broken at least on:
10.0.22
10.0.23
10.0.28
10.0.30
10.0.31
Table definition
CREATE TABLE `image` (
|
`img_name` varbinary(255) NOT NULL DEFAULT '',
|
`img_size` int(8) unsigned NOT NULL DEFAULT '0',
|
`img_description` tinyblob NOT NULL,
|
`img_user` int(5) unsigned NOT NULL DEFAULT '0',
|
`img_user_text` varbinary(255) NOT NULL DEFAULT '',
|
`img_timestamp` varbinary(14) NOT NULL DEFAULT '',
|
`img_width` int(5) NOT NULL DEFAULT '0',
|
`img_height` int(5) NOT NULL DEFAULT '0',
|
`img_bits` int(5) NOT NULL DEFAULT '0',
|
`img_metadata` mediumblob NOT NULL,
|
`img_media_type` enum('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE') DEFAULT NULL,
|
`img_major_mime` enum('unknown','application','audio','image','text','video','message','model','multipart') NOT NULL DEFAULT 'unknown',
|
`img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
|
`img_sha1` varbinary(32) NOT NULL DEFAULT '',
|
`img_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
PRIMARY KEY (`img_name`),
|
KEY `img_size` (`img_size`),
|
KEY `img_timestamp` (`img_timestamp`),
|
KEY `img_usertext_timestamp` (`img_user_text`,`img_timestamp`),
|
KEY `img_sha1` (`img_sha1`),
|
KEY `img_media_mime` (`img_media_type`,`img_major_mime`,`img_minor_mime`),
|
KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
|
) ENGINE=InnoDB DEFAULT CHARSET=binary
|
ALTER to run:
ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL;
|
We can see that on 10.0.31 and 10.0.22 it fails when forcing it to do it INPLACE:
root@db2055[enwiki]> select @@version;
|
+-----------------+
|
| @@version |
|
+-----------------+
|
| 10.0.31-MariaDB |
|
+-----------------+
|
1 row in set (0.04 sec)
|
|
|
root@db2055[enwiki]> ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL, ALGORITHM=INPLACE;
|
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
|
Without forcing it, it goes thru, but rebuilds the table
root@db2055[enwiki]> ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL;
|
Stage: 1 of 2 'copy to tmp table' 14.1% of stage done
|
|
|
Query OK, 856490 rows affected (1 min 21.04 sec)
|
Records: 856490 Duplicates: 0 Warnings: 0
|
Same thing does not happens on 10.1.24 and works as expected, without rebuilding the table:
root@db2062[enwiki]> select @@version;
|
+-----------------+
|
| @@version |
|
+-----------------+
|
| 10.1.24-MariaDB |
|
+-----------------+
|
1 row in set (0.04 sec)
|
|
|
root@db2062[enwiki]> ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL, ALGORITHM=INPLACE;
|
Query OK, 0 rows affected (0.04 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
Without forcing it, it also goes thru finely.