mirror of
https://github.com/MariaDB/server.git
synced 2025-01-30 02:30:06 +01:00
f5df4482e0
Problem: ====== - InnoDB fail to do instant operation while adding the variable length column. Problem is that InnoDB wrongly assumes that variable character length can never part of externally stored page. Solution: ======== instant_alter_column_possible(): Variable length character field can be stored as externally stored page.
331 lines
9.3 KiB
Text
331 lines
9.3 KiB
Text
#
|
||
# MDEV-15563: Instant ROW_FORMAT=REDUNDANT column type change&extension
|
||
# (reverted in MDEV-18627)
|
||
#
|
||
create database best;
|
||
use best;
|
||
set default_storage_engine=innodb;
|
||
set @bigval= repeat('0123456789', 30);
|
||
create procedure check_table(table_name varchar(255))
|
||
begin
|
||
select table_id into @table_id
|
||
from information_schema.innodb_sys_tables
|
||
where name = concat('best/', table_name);
|
||
select name, mtype, hex(prtype) as prtype, len
|
||
from information_schema.innodb_sys_columns
|
||
where table_id = @table_id;
|
||
end~~
|
||
# VARCHAR -> CHAR, VARBINARY -> BINARY conversion
|
||
set @bigval= repeat('0123456789', 20);
|
||
create table t (a varchar(300));
|
||
alter table t modify a char(255), algorithm=instant;
|
||
ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
|
||
alter table t modify a char(255), algorithm=copy;
|
||
create or replace table t (a varchar(200));
|
||
insert into t values (@bigval);
|
||
insert into t values ('z');
|
||
alter table t modify a char(200);
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
select count(a) from t where a = @bigval;
|
||
count(a)
|
||
1
|
||
select a, length(a) from t where a = 'z';
|
||
a length(a)
|
||
z 1
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
a 2 800FE 200
|
||
# CHAR enlargement
|
||
alter table t modify a char(220);
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
select count(a) from t where a = @bigval;
|
||
count(a)
|
||
1
|
||
select a, length(a) from t where a = 'z';
|
||
a length(a)
|
||
z 1
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
a 2 800FE 220
|
||
ALTER TABLE t CHANGE COLUMN a a CHAR(230) BINARY;
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
ALTER TABLE t ADD COLUMN b INT FIRST;
|
||
affected rows: 0
|
||
info: Records: 0 Duplicates: 0 Warnings: 0
|
||
ALTER TABLE t DROP b;
|
||
affected rows: 0
|
||
info: Records: 0 Duplicates: 0 Warnings: 0
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
a 13 2F00FE 230
|
||
# Convert from VARCHAR to a bigger CHAR
|
||
alter table t modify a varchar(200);
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
alter table t modify a char(255);
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
select count(a) from t where a = @bigval;
|
||
count(a)
|
||
1
|
||
select a, length(a) from t where a = 'z';
|
||
a length(a)
|
||
z 1
|
||
select * from t;
|
||
a
|
||
01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
|
||
z
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
a 2 800FE 255
|
||
# BINARY/VARBINARY test
|
||
create or replace table t (a varbinary(300));
|
||
insert into t values(NULL);
|
||
alter table t modify a binary(255);
|
||
affected rows: 1
|
||
info: Records: 1 Duplicates: 0 Warnings: 0
|
||
create or replace table t (a varbinary(200));
|
||
insert into t values (@bigval);
|
||
insert into t values ('z');
|
||
alter table t modify a binary(200);
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
select count(a) from t where a = @bigval;
|
||
count(a)
|
||
1
|
||
select length(a) from t where left(a, 1) = 'z';
|
||
length(a)
|
||
200
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
a 3 3F04FE 200
|
||
# BINARY enlargement
|
||
alter table t modify a binary(220);
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
a 3 3F04FE 220
|
||
# Convert from VARBINARY to a bigger BINARY
|
||
alter table t modify a varbinary(220);
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
alter table t modify a binary(255);
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
select count(a) from t where a = @bigval;
|
||
count(a)
|
||
0
|
||
select a, length(a) from t where a = 'z';
|
||
a length(a)
|
||
select * from t;
|
||
a
|
||
01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
|
||
z
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
a 3 3F04FE 255
|
||
# Integer conversions
|
||
create or replace table t (x tinyint);
|
||
insert into t values (127);
|
||
alter table t modify x smallint;
|
||
affected rows: 1
|
||
info: Records: 1 Duplicates: 0 Warnings: 0
|
||
select * from t;
|
||
x
|
||
127
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
x 6 402 2
|
||
update t set x= 32767;
|
||
alter table t modify x mediumint;
|
||
affected rows: 1
|
||
info: Records: 1 Duplicates: 0 Warnings: 0
|
||
select * from t;
|
||
x
|
||
32767
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
x 6 409 3
|
||
update t set x= 8388607;
|
||
alter table t modify x int;
|
||
affected rows: 1
|
||
info: Records: 1 Duplicates: 0 Warnings: 0
|
||
select * from t;
|
||
x
|
||
8388607
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
x 6 403 4
|
||
update t set x= 2147483647;
|
||
alter table t modify x bigint;
|
||
affected rows: 1
|
||
info: Records: 1 Duplicates: 0 Warnings: 0
|
||
select * from t;
|
||
x
|
||
2147483647
|
||
check table t extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t check status OK
|
||
call check_table('t');
|
||
name mtype prtype len
|
||
x 6 408 8
|
||
# Check IMPORT TABLESPACE
|
||
create or replace table t2 (x int);
|
||
alter table t2 discard tablespace;
|
||
create or replace table t1 (x tinyint);
|
||
insert into t1 set x= 42;
|
||
alter table t1 modify x int;
|
||
flush tables t1 for export;
|
||
unlock tables;
|
||
alter table t2 import tablespace;
|
||
select * from t2;
|
||
x
|
||
42
|
||
check table t2 extended;
|
||
Table Op Msg_type Msg_text
|
||
best.t2 check status OK
|
||
call check_table('t2');
|
||
name mtype prtype len
|
||
x 6 403 4
|
||
# Check innobase_col_to_mysql() len < flen
|
||
create or replace table t1 (x mediumint);
|
||
insert into t1 values (1);
|
||
insert into t1 values (1);
|
||
alter table t1 add column y int first, modify x int;
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 0
|
||
alter table t1 add column z int first, add primary key (x);
|
||
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
||
# Check assertion in wrong instant operation
|
||
create or replace table t1 (a varchar(26) not null) default character set utf8mb4;
|
||
insert into t1 values ('abcdef'), (repeat('x',26));
|
||
alter ignore table t1 modify a varchar(25) not null;
|
||
affected rows: 2
|
||
info: Records: 2 Duplicates: 0 Warnings: 1
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 2
|
||
select * from t1;
|
||
a
|
||
abcdef
|
||
xxxxxxxxxxxxxxxxxxxxxxxxx
|
||
# Check row_mysql_store_col_in_innobase_format()
|
||
create or replace table t1(x int primary key, a varchar(20));
|
||
insert into t1 (x) values (1);
|
||
update t1 set a= 'foo' where x = 2;
|
||
#
|
||
# MDEV-18124 PK on inplace-enlarged type fails
|
||
#
|
||
create or replace table t1 (x int, y int);
|
||
insert into t1 (x, y) values (11, 22);
|
||
alter table t1 modify x bigint;
|
||
affected rows: 1
|
||
info: Records: 1 Duplicates: 0 Warnings: 0
|
||
alter table t1 add primary key (x);
|
||
affected rows: 0
|
||
info: Records: 0 Duplicates: 0 Warnings: 0
|
||
select * from t1;
|
||
x y
|
||
11 22
|
||
check table t1;
|
||
Table Op Msg_type Msg_text
|
||
best.t1 check status OK
|
||
create or replace table t1 (a varchar(10), y int);
|
||
insert into t1 (a, y) values ("0123456789", 33);
|
||
alter table t1 modify a char(15);
|
||
affected rows: 1
|
||
info: Records: 1 Duplicates: 0 Warnings: 0
|
||
alter table t1 add primary key (a);
|
||
affected rows: 0
|
||
info: Records: 0 Duplicates: 0 Warnings: 0
|
||
select * from t1;
|
||
a y
|
||
0123456789 33
|
||
check table t1;
|
||
Table Op Msg_type Msg_text
|
||
best.t1 check status OK
|
||
create or replace table t1 (x int primary key, y int);
|
||
insert into t1 (x, y) values (44, 55);
|
||
alter table t1 modify x bigint;
|
||
affected rows: 1
|
||
info: Records: 1 Duplicates: 0 Warnings: 0
|
||
select * from t1;
|
||
x y
|
||
44 55
|
||
check table t1;
|
||
Table Op Msg_type Msg_text
|
||
best.t1 check status OK
|
||
create or replace table t1 (x int primary key, y int);
|
||
insert into t1 values (66, 77);
|
||
alter table t1 add column z int;
|
||
affected rows: 0
|
||
info: Records: 0 Duplicates: 0 Warnings: 0
|
||
alter table t1 drop column y;
|
||
affected rows: 0
|
||
info: Records: 0 Duplicates: 0 Warnings: 0
|
||
select * from t1;
|
||
x z
|
||
66 NULL
|
||
check table t1;
|
||
Table Op Msg_type Msg_text
|
||
best.t1 check status OK
|
||
create or replace table t1 (x integer, a varchar(20));
|
||
insert into t1 (x, a) values (73, 'a');
|
||
affected rows: 1
|
||
alter table t1 add index idx3 (a);
|
||
affected rows: 0
|
||
info: Records: 0 Duplicates: 0 Warnings: 0
|
||
alter table t1 modify a char(20);
|
||
affected rows: 1
|
||
info: Records: 1 Duplicates: 0 Warnings: 0
|
||
select * from t1;
|
||
x a
|
||
73 a
|
||
check table t1;
|
||
Table Op Msg_type Msg_text
|
||
best.t1 check status OK
|
||
drop database best;
|
||
#
|
||
# MDEV-33214 Table is getting rebuild with
|
||
# ALTER TABLE ADD COLUMN
|
||
#
|
||
use test;
|
||
CREATE TABLE t1(f1 INT, f2 VARCHAR(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
|
||
INSERT INTO t1 VALUES(1,'abc'),(2,'def');
|
||
ALTER TABLE t1 ADD (f3 VARCHAR(5000), f4 VARCHAR(20)), ALGORITHM=instant;
|
||
ALTER TABLE t1 ADD f5 TEXT, ALGORITHM=INSTANT;
|
||
DROP TABLE t1;
|
||
# End of 10.4 tests
|