[MDEV-13175] Adding a new enum value at the end of a list triggers a table rebuild Created: 2017-06-26  Updated: 2017-07-20  Resolved: 2017-07-20

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.0.22, 10.0.23, 10.0.28, 10.0.30, 10.0.31
Fix Version/s: 10.0.32

Type: Bug Priority: Critical
Reporter: Manuel Arostegui Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
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.

 Comments   
Comment by Alice Sherepa [ 2017-06-27 ]

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.

Comment by Manuel Arostegui [ 2017-06-28 ]

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.

Comment by Elena Stepanova [ 2017-06-28 ]

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?

Comment by Manuel Arostegui [ 2017-06-28 ]

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.

Comment by Elena Stepanova [ 2017-06-28 ]

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

Comment by Sergei Golubchik [ 2017-07-20 ]

backported

Generated at Thu Feb 08 08:03:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.