Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
Description
When MySQL 5.6 introduced the ALGORITHM attribute to ALTER TABLE, only two possible values were introduced in addition to the implied value ALGORITHM=DEFAULT: ALGORITHM=COPY to refer to the old-style ALTER TABLE that basically consists of CREATE TABLE, INSERT…SELECT, RENAME TABLE and DROP TABLE, and ALGORITHM=INPLACE to refer to the "fast" ALTER TABLE whose first incarnation was implemented in the InnoDB Plugin for MySQL 5.1.
This choice of keywords is unfortunate, because the supposedly "fast" algorithm would sometimes copy the table. Already the InnoDB Plugin would do it when executing ADD PRIMARY KEY. In MySQL 5.6, several ALGORITHM=INPLACE operations would actually copy the table. Most notably, these would be ADD COLUMN, DROP COLUMN, reordering columns, and changing ROW_FORMAT, and naturally the FORCE attribute that forces a rebuild.
The following was originally written in MDEV-11369, which would make certain cases of ADD COLUMN an instantaneous operation:
It would be nice to introduce new syntax to prevent nasty surprises. When an operation is expected to be quick, it could be better to return an error than to perform a disruptive (resource-intensive and time-consuming) operation.
ALGORITHM=INSTANT will refuse any operation that must modify any data in files. (Updates to metadata are possible.)
ALGORITHM=NOCOPY will refuse any operation that would rebuild the clustered index (and the whole table).
With respect to the allowed operations, ALGORITHM=INSTANT is a subset of ALGORITHM=NOCOPY which is a subset of ALGORITHM=INPLACE which is a subset of ALGORITHM=COPY.
Example:
# No data file change (MDEV-11369 instant ADD COLUMN)
|
ALTER TABLE t ADD COLUMN b INT, ALGORITHM=INSTANT;
|
# The following will change data files (ADD INDEX), but not rebuild the table:
|
ALTER TABLE t ADD COLUMN c INT, ADD INDEX(c), ALGORITHM=NOCOPY;
|
# The following are changing data files (not instant operation)
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
ALTER TABLE t DROP INDEX c, ALGORITHM=INSTANT;
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
ALTER TABLE t ADD COLUMN d INT, ADD INDEX(d), ALGORITHM=INSTANT;
|
# All of the above should be allowed with any other ALGORITHM.
|
# With ALGORITHM=DEFAULT or with no ALGORITHM, the most efficient
|
# available algorithm will be used.
|
Attachments
Issue Links
- blocks
-
MDEV-16099 Use alter algorithm=nocopy or algorithm=instant for non-InnoDB engine
-
- Closed
-
- causes
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Stalled
-
-
-
- Confirmed
-
-
MDEV-18845 Introduce alter_lock to allow refusing non-online ALTER TABLE
-
- Open
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
As part of this change, I think that we should strongly consider optimizing away non-changes to ENGINE or ROW_FORMAT or possibly other attributes.
Currently, the following two ALTER TABLE will cause a full table rebuild:
CREATE TABLE t(a INT) ENGINE=InnoDB ROW_FORMAT=COMPACT; |
ALTER TABLE t ENGINE=InnoDB; |
ALTER TABLE t ROW_FORMAT=COMPACT; |
If a rebuild is desired, the FORCE keyword can be specified:
ALTER TABLE t FORCE; |
| Link |
This issue relates to |
| Assignee | Thirunarayanan B [ thiru ] |
| Link |
This issue relates to |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Thirunarayanan B [ thiru ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
This looks OK from the InnoDB point of view, after addressing my review comments.
| Assignee | Marko Mäkelä [ marko ] | Thirunarayanan B [ thiru ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Thirunarayanan B [ thiru ] | Sergei Golubchik [ serg ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
I posted some more review comments. The only thing that is missing from the InnoDB side is the removal of the warning InnoDB rebuilding table to add column FTS_DOC_ID and the testing of ADD FULLTEXT INDEX…ALGORITHM=NOCOPY.
The non-change of ENGINE or ROW_FORMAT will continue to rebuild the table, for now. And those operations will be refused by ALGORITHM=NOCOPY or ALGORITHM=INSTANT.
| NRE Approved | Yes [ 10304 ] |
| Link |
This issue relates to |
https://github.com/MariaDB/server/commit/45be7b5ff7abc61c48e7b79877b8ed0bb430e5f3
This is the latest commit.
The latest looks OK from the InnoDB perspective; the only problem is a misleading error message for refusing ADD FULLTEXT INDEX…ALGORITHM=NOCOPY when a hidden FTS_DOC_ID column would be created.
I think that we should consider extending the existing Boolean parameter old_alter_table to integer as follows:
- old_alter_table=0 maps to ALGORITHM=DEFAULT (choose the best available algorithm)
- old_alter_table=1 maps to ALGORITHM=COPY
- old_alter_table=2 maps to ALGORITHM=INPLACE
- old_alter_table=3 maps to ALGORITHM=NOCOPY
- old_alter_table=4 maps to ALGORITHM=INSTANT
The values 0 and 1 would correspond to the current behaviour. Values 1 to 4 would limit the choice of ALTER TABLE operations. If one wants to avoid performance surprises on the server, one could configure old_alter_table=4 or old_alter_table=3. Then, any ALTER TABLE would be refused, unless it is instantaneous or avoids a table rebuild. Users could explicitly specify ALGORITHM if they want a more expensive operation.
The name old_alter_table is not very intuitive. Perhaps an alias alter_algorithm should be defined, with the default value alter_algorithm=default (0).
| Link |
This issue is duplicated by |
| Link |
This issue blocks |
| Assignee | Sergei Golubchik [ serg ] | Thirunarayanan B [ thiru ] |
Introduced new alter algorithm type called NOCOPY & INSTANT for inplace alter operation.
NOCOPY - Algorithm refuses any alter operation that would rebuild the clustered index.
It is a subset of INPLACE algorithm.
INSTANT - Algorithm allow any alter operation that would modify only meta data. It is a
subset of NOCOPY algorithm.
Introduce new variable called alter_algorithm. The values are DEFAULT(0), COPY(1), INPLACE(2), NOCOPY(3), INSTANT(4)
Message to deprecate old_alter_table variable and make it alias for alter_algorithm variable.
alter_algorithm variable for slave is always set to default value.
| Fix Version/s | 10.3.7 [ 23005 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
Removed the warning for InnoDB table building to add FTS_DOC_ID as well.
10.3 commit c5b28e55f6ff2a77bf67a2052cc4f4ddd73bc151 compiled with debug
Asserts observed up till now
egrep -i 'Assert.* failed' last_rmr_workdir/*.log | sed -e 's/^.*10\.3\.A//g' | sort -u
/storage/innobase/handler/handler0alter.cc:9238: virtual bool ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool): Assertion `ctx->need_rebuild()' failed.
/storage/innobase/row/row0ins.cc:3115: dberr_t row_ins_sec_index_entry_low(ulint, ulint, dict_index_t*, mem_heap_t*, mem_heap_t*, dtuple_t*, trx_id_t, que_thr_t*, bool): Assertion `thr_get_trx(thr)->error_state == DB_SUCCESS' failed.
/storage/innobase/trx/trx0rec.cc:1915: dberr_t trx_undo_report_rename(trx_t*, const dict_table_t*): Assertion `++loop_count < 2' failed.
/storage/innobase/row/row0trunc.cc:1894: dberr_t row_truncate_table_for_mysql(dict_table_t*, trx_t*): Assertion `logger->debug()' failed.
| Link | This issue relates to MDEV-16282 [ MDEV-16282 ] |
| Link |
This issue causes |
| Link |
This issue relates to |
| NRE Approved | Yes [ 10304 ] |
| Description |
When MySQL 5.6 introduced the ALGORITHM attribute to ALTER TABLE, only two possible values were introduced in addition to the implied value ALGORITHM=DEFAULT: ALGORITHM=COPY to refer to the old-style ALTER TABLE that basically consists of CREATE TABLE, INSERT…SELECT, RENAME TABLE and DROP TABLE, and ALGORITHM=INPLACE to refer to the "fast" ALTER TABLE whose first incarnation was implemented in the InnoDB Plugin for MySQL 5.1.
This choice of keywords is unfortunate, because the supposedly "fast" algorithm would sometimes copy the table. Already the InnoDB Plugin would do it when executing ADD PRIMARY KEY. In MySQL 5.6, several ALGORITHM=INPLACE operations would actually copy the table. Most notably, these would be ADD COLUMN, DROP COLUMN, reordering columns, and changing ROW_FORMAT, and naturally the FORCE attribute that forces a rebuild. The following was originally written in It would be nice to introduce new syntax to prevent nasty surprises. When an operation is expected to be quick, it could be better to return an error than to perform a disruptive (resource-intensive and time-consuming) operation. ALGORITHM=INSTANT will refuse any operation that must modify any data files. ALGORITHM=NOCOPY will refuse any operation that would rebuild the clustered index (and the whole table). With respect to the allowed operations, ALGORITHM=INSTANT is a subset of ALGORITHM=NOCOPY which is a subset of ALGORITHM=INPLACE which is a subset of ALGORITHM=COPY. Example: {code:SQL} # No data file change ( ALTER TABLE t ADD COLUMN b INT, ALGORITHM=INSTANT; # The following will change data files (ADD INDEX), but not rebuild the table: ALTER TABLE t ADD COLUMN c INT, ADD INDEX(c), ALGORITHM=NOCOPY; # The following are changing data files (not instant operation) --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t DROP INDEX c, ALGORITHM=INSTANT; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t ADD COLUMN d INT, ADD INDEX(d), ALGORITHM=INSTANT; # All of the above should be allowed with any other ALGORITHM. # With ALGORITHM=DEFAULT or with no ALGORITHM, the most efficient # available algorithm will be used. {code} |
When MySQL 5.6 introduced the ALGORITHM attribute to ALTER TABLE, only two possible values were introduced in addition to the implied value ALGORITHM=DEFAULT: ALGORITHM=COPY to refer to the old-style ALTER TABLE that basically consists of CREATE TABLE, INSERT…SELECT, RENAME TABLE and DROP TABLE, and ALGORITHM=INPLACE to refer to the "fast" ALTER TABLE whose first incarnation was implemented in the InnoDB Plugin for MySQL 5.1.
This choice of keywords is unfortunate, because the supposedly "fast" algorithm would sometimes copy the table. Already the InnoDB Plugin would do it when executing ADD PRIMARY KEY. In MySQL 5.6, several ALGORITHM=INPLACE operations would actually copy the table. Most notably, these would be ADD COLUMN, DROP COLUMN, reordering columns, and changing ROW_FORMAT, and naturally the FORCE attribute that forces a rebuild. The following was originally written in It would be nice to introduce new syntax to prevent nasty surprises. When an operation is expected to be quick, it could be better to return an error than to perform a disruptive (resource-intensive and time-consuming) operation. ALGORITHM=INSTANT will refuse any operation that must modify any data in files. (Updates to metadata are possible.) ALGORITHM=NOCOPY will refuse any operation that would rebuild the clustered index (and the whole table). With respect to the allowed operations, ALGORITHM=INSTANT is a subset of ALGORITHM=NOCOPY which is a subset of ALGORITHM=INPLACE which is a subset of ALGORITHM=COPY. Example: {code:SQL} # No data file change ( ALTER TABLE t ADD COLUMN b INT, ALGORITHM=INSTANT; # The following will change data files (ADD INDEX), but not rebuild the table: ALTER TABLE t ADD COLUMN c INT, ADD INDEX(c), ALGORITHM=NOCOPY; # The following are changing data files (not instant operation) --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t DROP INDEX c, ALGORITHM=INSTANT; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t ADD COLUMN d INT, ADD INDEX(d), ALGORITHM=INSTANT; # All of the above should be allowed with any other ALGORITHM. # With ALGORITHM=DEFAULT or with no ALGORITHM, the most efficient # available algorithm will be used. {code} |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-18570 [ MDEV-18570 ] |
| Link | This issue relates to MDEV-18845 [ MDEV-18845 ] |
| Link | This issue relates to MDEV-20419 [ MDEV-20419 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 81308 ] | MariaDB v4 [ 133296 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
As discussed with marko, it might be good in the scope of this task to get rid of this rather pointless warning:
ALTER TABLE t1 ADD FULLTEXT KEY ftidx (c);Warnings:Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID