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.
Attachments
Activity
I have just tried the same thing on MySQL 5.6 and it works fine.
mysql> select @@version;
|
+-----------+
|
| @@version |
|
+-----------+
|
| 5.6.36 |
|
+-----------+
|
1 row in set (0.00 sec)
|
|
|
mysql> 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;
|
Query OK, 0 rows affected (0.02 sec)
|
|
|
mysql> 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.00 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
mysql> show create table image\G
|
*************************** 1. row ***************************
|
Table: image
|
Create Table: 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','3D') 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
|
1 row in set (0.00 sec)
|
|
|
|
I have changed the priority to Critical as this triggers unexpected rebuilds and it can cause serious outages if not expected and ran on big tables, specially if using replication.
marostegui, alice, thanks for the report and provided information.
The problem was fixed in 10.1 long time ago: MDEV-8948 (ALTER ... INPLACE does work for BINARY, BLOB)
Given that there are two GA versions above 10.0, I don't think it makes sense to backport such a change.
marostegui, do you have any plans to upgrade to 10.1 or 10.2 any time soon?
I do believe it should be backported, it is critical enough to be done or at least specified in the documentation. This can cause serious outages.
We have around 200 mariadb servers so migrating to 10.1 will not happen anytime soon, specially not on the masters as they require downtime to be switched over.
I repeated on 10.0.31 and got error (ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.)
There is no error if character set is not binary, e.g. utf8 or latin7.