[MDEV-13175] Adding a new enum value at the end of a list triggers a table rebuild - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.22, 10.0.23, 10.0.28, 10.0.30, 10.0.31
Fix Version/s: 10.0.32
Component/s: Data Definition - Alter Table
Labels:
- alter
Environment:
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

Ascending order - Click to sort in descending order

Manuel Arostegui created issue - 2017-06-26 13:29

Manuel Arostegui made changes - 2017-06-26 13:30

Field	Original Value	New Value
Description	As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data : {code} 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 [code] However that looks broken at least on: 10.0.22 10.0.31 Table definition {code} 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 {code} ALTER to run: {code} ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL; {code} We can see that on 10.0.31 and 10.0.22 it forces a table rebuild, even when forcing it: {code} 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. {code} Without forcing it, it goes thru, but rebuilds the table {code} 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 {code} 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 {code} Without forcing it, it also goes thru finely.	As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data : {code} 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 {code} However that looks broken at least on: 10.0.22 10.0.31 Table definition {code} 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 {code} ALTER to run: {code} ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL; {code} We can see that on 10.0.31 and 10.0.22 it forces a table rebuild, even when forcing it: {code} 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. {code} Without forcing it, it goes thru, but rebuilds the table {code} 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 {code} 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 {code} Without forcing it, it also goes thru finely.

Manuel Arostegui made changes - 2017-06-26 13:30

Description

As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data :
{code}
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
{code}

However that looks broken at least on:
10.0.22
10.0.31

Table definition
{code}
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
{code}

ALTER to run:
{code}
ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL;
{code}

We can see that on 10.0.31 and 10.0.22 it forces a table rebuild, even when forcing it:
{code}
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.
{code}

Without forcing it, it goes thru, but rebuilds the table
{code}
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
{code}

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
{code}

Without forcing it, it also goes thru finely.

As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data :
{code}
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
{code}

However that looks broken at least on:
10.0.22
10.0.31

Table definition
{code}
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
{code}

ALTER to run:
{code}
ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL;
{code}

We can see that on 10.0.31 and 10.0.22 it forces a table rebuild, even when forcing it:
{code}
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.
{code}

Without forcing it, it goes thru, but rebuilds the table
{code}
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
{code}

Same thing does not happens on 10.1.24 and works as expected, without rebuilding the table:
{code}
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
{code}

Without forcing it, it also goes thru finely.

Manuel Arostegui made changes - 2017-06-26 13:31

Description

As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data :
{code}
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
{code}

However that looks broken at least on:
10.0.22
10.0.31

Table definition
{code}
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
{code}

ALTER to run:
{code}
ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL;
{code}

We can see that on 10.0.31 and 10.0.22 it forces a table rebuild, even when forcing it:
{code}
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.
{code}

Without forcing it, it goes thru, but rebuilds the table
{code}
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
{code}

Same thing does not happens on 10.1.24 and works as expected, without rebuilding the table:
{code}
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
{code}

Without forcing it, it also goes thru finely.

As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data :
{code}
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
{code}

However that looks broken at least on:
*10.0.22*
*10.0.31*

Table definition
{code}
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
{code}

ALTER to run:
{code}
ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL;
{code}

We can see that on 10.0.31 and 10.0.22 it forces a table rebuild, even when forcing it:
{code}
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.
{code}

Without forcing it, it goes thru, but rebuilds the table
{code}
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
{code}

Same thing does not happens on 10.1.24 and works as expected, without rebuilding the table:
{code}
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
{code}

Without forcing it, it also goes thru finely.

Manuel Arostegui made changes - 2017-06-26 13:31

Summary

Adding an enum triggers a table rebuild

Adding an enum value at the end of a list triggers a table rebuild

Manuel Arostegui made changes - 2017-06-26 13:31

Summary

Adding an enum value at the end of a list triggers a table rebuild

Adding a new enum value at the end of a list triggers a table rebuild

Manuel Arostegui made changes - 2017-06-27 08:03

Affects Version/s		10.0.31 [ 22501 ]
Affects Version/s		10.0.28 [ 22107 ]
Affects Version/s		10.0.23 [ 20401 ]
Description	As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data : {code} 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 {code} However that looks broken at least on: 10.0.22 10.0.31 Table definition {code} 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 {code} ALTER to run: {code} ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL; {code} We can see that on 10.0.31 and 10.0.22 it forces a table rebuild, even when forcing it: {code} 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. {code} Without forcing it, it goes thru, but rebuilds the table {code} 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 {code} Same thing does not happens on 10.1.24 and works as expected, without rebuilding the table: {code} 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 {code} Without forcing it, it also goes thru finely.	As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data : {code} 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 {code} However that looks broken at least on: 10.0.22 10.0.23 10.0.28 10.0.30 10.0.31 Table definition {code} 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 {code} ALTER to run: {code} ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL; {code} We can see that on 10.0.31 and 10.0.22 it forces a table rebuild, even when forcing it: {code} 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. {code} Without forcing it, it goes thru, but rebuilds the table {code} 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 {code} Same thing does not happens on 10.1.24 and works as expected, without rebuilding the table: {code} 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 {code} Without forcing it, it also goes thru finely.

Alice Sherepa added a comment - 2017-06-27 09:41

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.

Alice Sherepa added a comment - 2017-06-27 09:41 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.

Manuel Arostegui made changes - 2017-06-27 14:00

Description

As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data :
{code}
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
{code}

However that looks broken at least on:
*10.0.22*
*10.0.23*
*10.0.28*
*10.0.30*
*10.0.31*

Table definition
{code}
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
{code}

ALTER to run:
{code}
ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL;
{code}

We can see that on 10.0.31 and 10.0.22 it forces a table rebuild, even when forcing it:
{code}
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.
{code}

Without forcing it, it goes thru, but rebuilds the table
{code}
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
{code}

Same thing does not happens on 10.1.24 and works as expected, without rebuilding the table:
{code}
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
{code}

Without forcing it, it also goes thru finely.

As per: https://mariadb.com/kb/en/mariadb/alter-table/#when-does-alter-table-copy-all-data :
{code}
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
{code}

However that looks broken at least on:
*10.0.22*
*10.0.23*
*10.0.28*
*10.0.30*
*10.0.31*

Table definition
{code}
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
{code}

ALTER to run:
{code}
ALTER TABLE image MODIFY img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL;
{code}

We can see that on 10.0.31 and 10.0.22 it fails when forcing it to do it INPLACE:
{code}
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.
{code}

Without forcing it, it goes thru, but rebuilds the table
{code}
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
{code}

Same thing does not happens on 10.1.24 and works as expected, without rebuilding the table:
{code}
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
{code}

Without forcing it, it also goes thru finely.

Manuel Arostegui added a comment - 2017-06-28 09:36 - edited

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.

Manuel Arostegui added a comment - 2017-06-28 09:36 - edited 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.

Manuel Arostegui made changes - 2017-06-28 09:36

Priority

Major [ 3 ]

Critical [ 2 ]

Elena Stepanova added a comment - 2017-06-28 10:14

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?

Elena Stepanova added a comment - 2017-06-28 10:14 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?

Elena Stepanova made changes - 2017-06-28 10:15

Labels

alter

alter need_feedback

Manuel Arostegui added a comment - 2017-06-28 12:04

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.

Manuel Arostegui added a comment - 2017-06-28 12:04 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.

Elena Stepanova made changes - 2017-06-28 13:24

Labels

alter need_feedback

alter

Elena Stepanova added a comment - 2017-06-28 13:25

Assigning to serg to decide whether it should indeed be backported. Also ATTN bar.

Elena Stepanova added a comment - 2017-06-28 13:25 Assigning to serg to decide whether it should indeed be backported. Also ATTN bar .

Elena Stepanova made changes - 2017-06-28 13:25

Fix Version/s		10.0 [ 16000 ]
Assignee		Sergei Golubchik [ serg ]

Elena Stepanova made changes - 2017-06-28 13:25

Status

Open [ 1 ]

Confirmed [ 10101 ]

Sergei Golubchik made changes - 2017-07-20 13:48

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2017-07-20 13:48

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik added a comment - 2017-07-20 18:16

backported

Sergei Golubchik added a comment - 2017-07-20 18:16 backported

Sergei Golubchik made changes - 2017-07-20 18:16

Fix Version/s		10.0.32 [ 22504 ]
Fix Version/s	10.0 [ 16000 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 81373 ]

MariaDB v4 [ 152383 ]

People

Assignee:: Sergei Golubchik

Reporter:: Manuel Arostegui

Votes:: 1 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-06-26 13:29

Updated:: 2017-07-20 18:16

Resolved:: 2017-07-20 18:16

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration