mirror of
https://github.com/MariaDB/server.git
synced 2025-10-30 18:36:12 +01:00
7a27db778e
Allow instant changes of columns in ROW_FORMAT=REDUNDANT from NOT NULL to NULL. Later, this may be implemented for ROW_FORMAT=COMPACT or DYNAMIC, but in that case any indexes on the table must be rebuilt. dict_table_t::prepare_instant(): Add some debug assertions, and relax a debug assertion so that the number of fields is allowed not to change. dict_index_t::instant_add_field(): Relax a debug assertion, allowing a column to change from NOT NULL to NULL. dict_table_t::instant_column(): Add debug assertions. instant_alter_column_possible(): Allow ALTER_COLUMN_NULLABLE when applicable. innodb_insert_sys_columns(): Add the parameter bool update=false to run UPDATE instead of INSERT. innobase_instant_add_col(): Remove; let the only caller invoke innodb_insert_sys_columns() directly. innobase_instant_try(): Update the SYS_COLUMNS record if the column is changed. Only convert the table to the instant ALTER TABLE format if necessary. For ALTER_COLUMN_NULLABLE in ROW_FORMAT=REDUNDANT, there is no data format change.
56 lines
2 KiB
Text
56 lines
2 KiB
Text
create table t (a int NOT NULL) engine=innodb row_format= compressed;
|
|
alter table t modify a int NULL, algorithm=instant;
|
|
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
|
|
drop table t;
|
|
create table t (a int NOT NULL) engine=innodb row_format= dynamic;
|
|
alter table t modify a int NULL, algorithm=instant;
|
|
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
|
|
drop table t;
|
|
create table t (a int NOT NULL) engine=innodb row_format= compact;
|
|
alter table t modify a int NULL, algorithm=instant;
|
|
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
|
|
drop table t;
|
|
create table t (
|
|
id int primary key,
|
|
a int NOT NULL default 0,
|
|
b int NOT NULL default 0,
|
|
c int NOT NULL default 0,
|
|
index idx (a,b,c)
|
|
) engine=innodb row_format=redundant;
|
|
insert into t (id, a) values (0, NULL);
|
|
ERROR 23000: Column 'a' cannot be null
|
|
insert into t (id, b) values (0, NULL);
|
|
ERROR 23000: Column 'b' cannot be null
|
|
insert into t (id, c) values (0, NULL);
|
|
ERROR 23000: Column 'c' cannot be null
|
|
insert into t values (1,1,1,1);
|
|
set @id = (select table_id from information_schema.innodb_sys_tables
|
|
where name = 'test/t');
|
|
select * from information_schema.innodb_sys_columns where table_id=@id;
|
|
TABLE_ID NAME POS MTYPE PRTYPE LEN
|
|
TABLE_ID id 0 6 1283 4
|
|
TABLE_ID a 1 6 1283 4
|
|
TABLE_ID b 2 6 1283 4
|
|
TABLE_ID c 3 6 1283 4
|
|
alter table t modify a int NULL, algorithm=instant;
|
|
insert into t values (2, NULL, 2, 2);
|
|
alter table t modify b int NULL, algorithm=nocopy;
|
|
insert into t values (3, NULL, NULL, 3);
|
|
alter table t modify c int NULL, algorithm=inplace;
|
|
insert into t values (4, NULL, NULL, NULL);
|
|
select * from information_schema.innodb_sys_columns where table_id=@id;
|
|
TABLE_ID NAME POS MTYPE PRTYPE LEN
|
|
TABLE_ID id 0 6 1283 4
|
|
TABLE_ID a 1 6 1027 4
|
|
TABLE_ID b 2 6 1027 4
|
|
TABLE_ID c 3 6 1027 4
|
|
select * from t;
|
|
id a b c
|
|
4 NULL NULL NULL
|
|
3 NULL NULL 3
|
|
2 NULL 2 2
|
|
1 1 1 1
|
|
check table t;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
drop table t;
|