[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.

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 made changes - 2017-06-28 09:36

Priority

Major [ 3 ]

Critical [ 2 ]

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

Labels

alter

alter need_feedback

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

Labels

alter need_feedback

alter

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 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