![]() InnoDB supports modifying a column to not allow NULL values with ALGORITHM set to INPLACE. InnoDB supports modifying a column to allow NULL values with ALGORITHM set to INPLACE.įor example: CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar ( 50 ), c varchar ( 50 ) NOT NULL ) SET SESSION alter_algorithm = 'INPLACE' ALTER TABLE tab MODIFY COLUMN c varchar ( 50 ) NULL Query OK, 0 rows affected ( 0. Try ALGORITHM = COPY Changing a Column to NULL Reason : Cannot change column type INPLACE. ![]() 005 sec )īut this fails in MariaDB 10.2.2 and later, because the original length of the column is less than 256 bytes, and the new length is greater than 256 bytes: CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar ( 50 ), c varchar ( 255 ) ) CHARACTER SET = latin1 SET SESSION alter_algorithm = 'INPLACE' ALTER TABLE tab MODIFY COLUMN c varchar ( 256 ) ERROR 1846 ( 0 A000 ): ALGORITHM = INPLACE is not supported. Try ALGORITHM = COPYīut this succeeds in MariaDB 10.2.2 and later, because the original length of the column is less than 256 bytes, and the new length is still less than 256 bytes: CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar ( 50 ), c varchar ( 50 ) ) CHARACTER SET = latin1 SET SESSION alter_algorithm = 'INPLACE' ALTER TABLE tab MODIFY COLUMN c varchar ( 100 ) Query OK, 0 rows affected ( 0. See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Changing the Data Type of a Column for more information.įor example, this fails: CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar ( 50 ), c varchar ( 50 ) ) SET SESSION alter_algorithm = 'INPLACE' ALTER TABLE tab MODIFY COLUMN c int ERROR 1846 ( 0 A000 ): ALGORITHM = INPLACE is not supported. In MariaDB 10.4.3 and later, InnoDB supports increasing the length of VARCHAR columns with ALGORITHM set to INPLACE in the cases where the operation supports having the ALGORITHM clause set to INSTANT.This means that the length of a column cannot be increased with ALGORITHM set to INPLACE if the original length was less than 256 bytes, and the new length is 256 bytes or more. A VARCHAR column that is between 0 and 255 bytes in size requires 1 byte to represent its length, while a VARCHAR column that is 256 bytes or longer requires 2 bytes to represent its length. In MariaDB 10.2.2 and later, InnoDB supports increasing the length of VARCHAR columns with ALGORITHM set to INPLACE, unless it would require changing the number of bytes requires to represent the column's length.InnoDB does not support modifying a column's data type with ALGORITHM set to INPLACE in most cases. 022 sec ) Changing the Data Type of a Column InnoDB supports reordering columns within a table with ALGORITHM set to INPLACE.įor example: CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar ( 50 ), c varchar ( 50 ) ) SET SESSION alter_algorithm = 'INPLACE' ALTER TABLE tab MODIFY COLUMN c varchar ( 50 ) AFTER a Query OK, 0 rows affected ( 0. When this strategy is used, all concurrent DML is permitted.įor example: CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar ( 50 ), c varchar ( 50 ) ) SET SESSION alter_algorithm = 'INPLACE' ALTER TABLE tab DROP COLUMN c Query OK, 0 rows affected ( 0. This strategy can be explicitly chosen by setting the LOCK clause to NONE. ![]() This operation supports the non-locking strategy. InnoDB supports dropping columns from a table with ALGORITHM set to INPLACE. When this strategy is used, all concurrent DML is permitted.įor example: CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar ( 50 ) ) SET SESSION alter_algorithm = 'INPLACE' ALTER TABLE tab ADD COLUMN c varchar ( 50 ) Query OK, 0 rows affected ( 0. ![]() With the exception of adding an auto-increment column, this operation supports the non-locking strategy. As a result, the operation is quite expensive. The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. InnoDB supports adding columns to a table with ALGORITHM set to INPLACE. InnoDB Online DDL Operations with ALGORITHM=INSTANTĬolumn Operations ALTER TABLE.InnoDB Online DDL Operations with ALGORITHM=NOCOPY.See the following additional pages for more information about these supported operations: Therefore, when the ALGORITHM clause is set to INPLACE, some operations are supported by inheritance. Similarly, when the ALGORITHM clause is set to NOCOPY, the supported operations are a superset of the operations that are supported when the ALGORITHM clause is set to INSTANT. When the ALGORITHM clause is set to INPLACE, the supported operations are a superset of the operations that are supported when the ALGORITHM clause is set to NOCOPY. Limitations Related to Generated (Virtual and Persistent/Stored) Columns.Limitations Related to Fulltext Indexes.Removing System Versioning from a Column. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |