mirror of
https://github.com/MariaDB/server.git
synced 2025-07-22 03:08:18 +02:00
cffbb17480
Before MySQL 4.0.18, user-specified constraint names were ignored. Starting with MySQL 4.0.18, the specified constraint name was prepended with the schema name and '/'. Now we are transforming into a format where the constraint name is prepended with the dict_table_t::name and the impossible UTF-8 sequence 0xff. Generated constraint names will be ASCII decimal numbers. On upgrade, old FOREIGN KEY constraint names will be displayed without any schema name prefix. They will be updated to the new format on DDL operations. dict_foreign_t::sql_id(): Return the SQL constraint name without any schemaname/tablename\377 or schemaname/ prefix. row_rename_table_for_mysql(), dict_table_rename_in_cache(): Simplify the logic: Just rename constraints to the new format. dict_table_get_foreign_id(): Replaces dict_table_get_highest_foreign_id(). innobase_get_foreign_key_info(): Let my_error() refer to erroneous anonymous constraints as "(null)". row_delete_constraint(): Try to drop all 3 constraint name variants. Reviewed by: Thirunarayanan Balathandayuthapani Tested by: Matthias Leich
324 lines
12 KiB
Text
324 lines
12 KiB
Text
set @save_default_engine= @@default_storage_engine;
|
|
#
|
|
# MDEV-25803 Inplace ALTER breaks MyISAM/Aria tables when order of keys is changed
|
|
#
|
|
create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)) engine myisam;
|
|
alter table t1 change x xx int, algorithm=inplace;
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x));
|
|
alter table t1 change x xx int, algorithm=inplace;
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
drop table t1;
|
|
#
|
|
# End of 10.3 tests
|
|
#
|
|
#
|
|
# MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax
|
|
#
|
|
CREATE TABLE t1(a INT, b VARCHAR(30), c FLOAT);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` varchar(30) DEFAULT NULL,
|
|
`c` float DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
INSERT INTO t1 VALUES(1,'abcd',1.234);
|
|
CREATE TABLE t2(a INT, b VARCHAR(30), c FLOAT) ENGINE=MyIsam;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` varchar(30) DEFAULT NULL,
|
|
`c` float DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
INSERT INTO t2 VALUES(1,'abcd',1.234);
|
|
ALTER TABLE t1 RENAME COLUMN a TO a;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` varchar(30) DEFAULT NULL,
|
|
`c` float DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 RENAME COLUMN a TO m;
|
|
ALTER TABLE t1 RENAME COLUMN a TO m;
|
|
ERROR 42S22: Unknown column 'a' in 't1'
|
|
ALTER TABLE t1 RENAME COLUMN IF EXISTS a TO m;
|
|
Warnings:
|
|
Note 1054 Unknown column 'a' in 't1'
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`m` int(11) DEFAULT NULL,
|
|
`b` varchar(30) DEFAULT NULL,
|
|
`c` float DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM t1;
|
|
m b c
|
|
1 abcd 1.234
|
|
ALTER TABLE t1 RENAME COLUMN m TO x,
|
|
RENAME COLUMN b TO y,
|
|
RENAME COLUMN c TO z;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`x` int(11) DEFAULT NULL,
|
|
`y` varchar(30) DEFAULT NULL,
|
|
`z` float DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM t1;
|
|
x y z
|
|
1 abcd 1.234
|
|
ALTER TABLE t2 RENAME COLUMN a TO d, RENAME COLUMN b TO e, RENAME COLUMN c to f;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`d` int(11) DEFAULT NULL,
|
|
`e` varchar(30) DEFAULT NULL,
|
|
`f` float DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM t2;
|
|
d e f
|
|
1 abcd 1.234
|
|
ALTER TABLE t1 CHANGE COLUMN x a INT, RENAME COLUMN y TO b;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` varchar(30) DEFAULT NULL,
|
|
`z` float DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 CHANGE COLUMN z c DOUBLE, RENAME COLUMN b to b;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` varchar(30) DEFAULT NULL,
|
|
`c` double DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 CHANGE COLUMN a b int, RENAME COLUMN b TO c, CHANGE COLUMN c d FLOAT;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` varchar(30) DEFAULT NULL,
|
|
`d` float DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 ADD COLUMN zz INT, RENAME COLUMN d TO f;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` varchar(30) DEFAULT NULL,
|
|
`f` float DEFAULT NULL,
|
|
`zz` int(11) DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 DROP COLUMN zz, RENAME COLUMN c TO zz;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`b` int(11) DEFAULT NULL,
|
|
`zz` varchar(30) DEFAULT NULL,
|
|
`f` float DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 RENAME COLUMN zz to c, DROP COLUMN f;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` varchar(30) DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 ADD COLUMN d INT DEFAULT 5, RENAME COLUMN c TO b, DROP COLUMN b;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`b` varchar(30) DEFAULT NULL,
|
|
`d` int(11) DEFAULT 5
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 RENAME COLUMN b TO d, RENAME COLUMN d TO b;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`d` varchar(30) DEFAULT NULL,
|
|
`b` int(11) DEFAULT 5
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 ADD KEY(b);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`d` varchar(30) DEFAULT NULL,
|
|
`b` int(11) DEFAULT 5,
|
|
KEY `b` (`b`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 RENAME COLUMN b TO bb;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`d` varchar(30) DEFAULT NULL,
|
|
`bb` int(11) DEFAULT 5,
|
|
KEY `b` (`bb`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM t1;
|
|
d bb
|
|
abcd 5
|
|
CREATE TABLE t3(a int, b int, KEY(b));
|
|
ALTER TABLE t3 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES t1(bb);
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
KEY `b` (`b`),
|
|
CONSTRAINT `1` FOREIGN KEY (`b`) REFERENCES `t1` (`bb`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 RENAME COLUMN bb TO b;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`d` varchar(30) DEFAULT NULL,
|
|
`b` int(11) DEFAULT 5,
|
|
KEY `b` (`b`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t3 RENAME COLUMN b TO c;
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
KEY `b` (`c`),
|
|
CONSTRAINT `1` FOREIGN KEY (`c`) REFERENCES `t1` (`b`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
CREATE TABLE t4(a int);
|
|
ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE;
|
|
SHOW CREATE TABLE t4;
|
|
Table Create Table
|
|
t4 CREATE TABLE `t4` (
|
|
`aa` int(11) DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t4 RENAME COLUMN aa TO a, ALGORITHM = COPY;
|
|
SHOW CREATE TABLE t4;
|
|
Table Create Table
|
|
t4 CREATE TABLE `t4` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
DROP TABLE t4;
|
|
CREATE VIEW v1 AS SELECT d,e,f FROM t2;
|
|
CREATE TRIGGER trg1 BEFORE UPDATE on t2 FOR EACH ROW SET NEW.d=OLD.d + 10;
|
|
CREATE PROCEDURE sp1() INSERT INTO t2(d) VALUES(10);
|
|
ALTER TABLE t2 RENAME COLUMN d TO g;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`g` int(11) DEFAULT NULL,
|
|
`e` varchar(30) DEFAULT NULL,
|
|
`f` float DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SHOW CREATE VIEW v1;
|
|
View Create View character_set_client collation_connection
|
|
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`f` AS `f` from `t2` latin1 latin1_swedish_ci
|
|
Warnings:
|
|
Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
SELECT * FROM v1;
|
|
ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
UPDATE t2 SET f = f + 10;
|
|
ERROR 42S22: Unknown column 'd' in 'NEW'
|
|
CALL sp1();
|
|
ERROR 42S22: Unknown column 'd' in 'INSERT INTO'
|
|
DROP TRIGGER trg1;
|
|
DROP PROCEDURE sp1;
|
|
CREATE TABLE t_gen(a INT, b DOUBLE GENERATED ALWAYS AS (SQRT(a)));
|
|
INSERT INTO t_gen(a) VALUES(4);
|
|
SELECT * FROM t_gen;
|
|
a b
|
|
4 2
|
|
SHOW CREATE TABLE t_gen;
|
|
Table Create Table
|
|
t_gen CREATE TABLE `t_gen` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` double GENERATED ALWAYS AS (sqrt(`a`)) VIRTUAL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t_gen RENAME COLUMN a TO c, CHANGE COLUMN b b DOUBLE GENERATED ALWAYS AS (SQRT(c));
|
|
SELECT * FROM t_gen;
|
|
c b
|
|
4 2
|
|
SHOW CREATE TABLE t_gen;
|
|
Table Create Table
|
|
t_gen CREATE TABLE `t_gen` (
|
|
`c` int(11) DEFAULT NULL,
|
|
`b` double GENERATED ALWAYS AS (sqrt(`c`)) VIRTUAL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t_gen CHANGE COLUMN c x INT;
|
|
show create table t_gen;
|
|
Table Create Table
|
|
t_gen CREATE TABLE `t_gen` (
|
|
`x` int(11) DEFAULT NULL,
|
|
`b` double GENERATED ALWAYS AS (sqrt(`x`)) VIRTUAL
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t_gen RENAME COLUMN x TO a;
|
|
DROP TABLE t_gen;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`d` varchar(30) DEFAULT NULL,
|
|
`b` int(11) DEFAULT 5,
|
|
KEY `b` (`b`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 RENAME COLUMN b z;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'z' at line 1
|
|
ALTER TABLE t1 RENAME COLUMN FROM b TO z;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM b TO z' at line 1
|
|
ALTER TABLE t1 RENAME COLUMN b TO 1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1
|
|
ALTER TABLE t1 RENAME COLUMN b TO e, RENAME COLUMN c TO e;
|
|
ERROR 42S22: Unknown column 'c' in 't1'
|
|
ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN b TO z;
|
|
ERROR 42S21: Duplicate column name 'z'
|
|
ALTER TABLE t1 DROP COLUMN b, RENAME COLUMN b TO z;
|
|
ERROR 42S22: Unknown column 'b' in 't1'
|
|
ALTER TABLE t1 RENAME COLUMN b TO b, RENAME COLUMN b TO b;
|
|
ERROR 42S22: Unknown column 'b' in 't1'
|
|
ALTER TABLE t1 RENAME COLUMN b TO c3, DROP COLUMN c3;
|
|
ERROR 42000: Can't DROP COLUMN `c3`; check that it exists
|
|
ALTER TABLE t1 ADD COLUMN z INT, CHANGE COLUMN z y INT, DROP COLUMN y;
|
|
ERROR 42S22: Unknown column 'z' in 't1'
|
|
ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN z TO y, DROP COLUMN y;
|
|
ERROR 42S22: Unknown column 'z' in 't1'
|
|
ALTER TABLE t1 RENAME COLUMN b TO `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn`;
|
|
ERROR 42000: Incorrect column name 'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn'
|
|
ALTER TABLE t1 CHANGE b `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn` int;
|
|
ERROR 42000: Identifier name 'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn' is too long
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`d` varchar(30) DEFAULT NULL,
|
|
`b` int(11) DEFAULT 5,
|
|
KEY `b` (`b`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM t1;
|
|
d b
|
|
abcd 5
|
|
DROP VIEW v1;
|
|
DROP TABLE t3,t1,t2;
|
|
#
|
|
# MDEV-25803 Inplace ALTER breaks MyISAM/Aria tables when order of keys is changed
|
|
#
|
|
create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)) engine myisam;
|
|
alter table t1 change x xx int, algorithm=inplace;
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x));
|
|
alter table t1 change x xx int, algorithm=inplace;
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
drop table t1;
|
|
#
|
|
# End of 10.5 tests
|
|
#
|
|
set @@default_storage_engine= @save_default_engine;
|