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
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).
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.
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.
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:
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.