mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
1028 lines
30 KiB
Text
1028 lines
30 KiB
Text
set default_storage_engine=innodb;
|
|
#
|
|
# Bug#22469130: FOREIGN KEY ON DELETE CASCADE NOT ALLOWED
|
|
# WHEN A VIRTUAL INDEX EXISTS.
|
|
# UPDATE CASCADE
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# UPDATE SET NULL
|
|
CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY,
|
|
KEY(fld1));
|
|
CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE SET NULL);
|
|
INSERT INTO t1 VALUES(1, 2);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
NULL
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
NULL NULL
|
|
DROP TABLE t2, t1;
|
|
# DELETE CASCADE
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE);
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t1 VALUES(2);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
INSERT INTO t2 VALUES(2, DEFAULT);
|
|
DELETE FROM t1 WHERE fld1= 1;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# DELETE SET NULL
|
|
CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY, KEY(fld1));
|
|
CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE SET NULL);
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
INSERT INTO t1 VALUES(2, 2);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
INSERT INTO t2 VALUES(2, DEFAULT);
|
|
DELETE FROM t1 WHERE fld1= 1;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
NULL
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
NULL NULL
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# VIRTUAL INDEX CONTAINS FK CONSTRAINT COLUMN
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT, fld3 INT AS (fld2) VIRTUAL,
|
|
KEY(fld3, fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
|
|
INSERT INTO t1(fld1) VALUES(1);
|
|
INSERT INTO t2(fld1, fld2) VALUES(1, 3);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld3, fld1 FROM t2;
|
|
fld3 fld1
|
|
3 2
|
|
SELECT * FROM t2;
|
|
fld1 fld2 fld3
|
|
2 3 3
|
|
DROP TABLE t2, t1;
|
|
# Multiple level of VIRTUAL columns.
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
|
|
fld3 INT AS (fld2) VIRTUAL, KEY(fld3), KEY(fld2),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
|
|
INSERT INTO t1(fld1) VALUES(1);
|
|
INSERT INTO t2(fld1) VALUES(1);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
2
|
|
SELECT fld3 FROM t2;
|
|
fld3
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2 fld3
|
|
2 2 2
|
|
DROP TABLE t2, t1;
|
|
# Drop the VIRTUAL INDEX using alter copy ALGORITHM
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY vk(fld2),
|
|
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1(fld1) VALUES(1);
|
|
INSERT INTO t2(fld1) VALUES(1);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
2 2
|
|
ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
|
|
UPDATE t1 SET fld1= 3;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
3 3
|
|
DROP TABLE t2, t1;
|
|
# Drop the VIRTUAL INDEX using INPLACE alter ALGORITHM
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
|
|
KEY vk(fld2), KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1(fld1) VALUES(1);
|
|
INSERT INTO t2(fld1) VALUES(1);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
2 2
|
|
ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
|
|
UPDATE t1 SET fld1= 3;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
3 3
|
|
DROP TABLE t2, t1;
|
|
# Add the VIRTUAL INDEX using COPY alter ALGORITHM
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
|
|
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1(fld1) VALUES(1);
|
|
INSERT INTO t2(fld1) VALUES(1);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
2 2
|
|
ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= COPY;
|
|
UPDATE t1 SET fld1= 3;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
3 3
|
|
DROP TABLE t2, t1;
|
|
# Add the VIRTUAL INDEX using INPLACE alter ALGORITHM
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,fld2 INT AS (fld1) VIRTUAL,
|
|
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1(fld1) VALUES(1);
|
|
INSERT INTO t2(fld1) VALUES(1);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
2 2
|
|
ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= INPLACE;
|
|
UPDATE t1 SET fld1= 3;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
3 3
|
|
DROP TABLE t2, t1;
|
|
# Drop the VIRTUAL INDEX contains fk constraint column
|
|
# using alter copy ALGORITHM
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
|
|
fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1),
|
|
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1(fld1) VALUES(1);
|
|
INSERT INTO t2(fld1, fld2) VALUES(1, 2);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld3, fld1 FROM t2;
|
|
fld3 fld1
|
|
2 2
|
|
ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
|
|
UPDATE t1 SET fld1= 3;
|
|
SELECT fld3, fld1 FROM t2;
|
|
fld3 fld1
|
|
2 3
|
|
DROP TABLE t2, t1;
|
|
# Drop the VIRTUAL INDEX which contains fk constraint column
|
|
# using INPLACE alter operation
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
|
|
fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1),
|
|
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1(fld1) VALUES(1);
|
|
INSERT INTO t2(fld1, fld2) VALUES(1, 2);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld3, fld1 FROM t2;
|
|
fld3 fld1
|
|
2 2
|
|
alter TABLE t2 DROP INDEX vk, ALGORITHM= INPLACE;
|
|
UPDATE t1 SET fld1= 3;
|
|
SELECT fld3, fld1 FROM t2;
|
|
fld3 fld1
|
|
2 3
|
|
DROP TABLE t2, t1;
|
|
# Add the VIRTUAL INDEX contains fk constraint column
|
|
# using copy alter operatiON
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
|
|
fld3 INT AS (fld2) VIRTUAL, KEY(fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
|
|
INSERT INTO t1(fld1) VALUES(1);
|
|
INSERT INTO t2(fld1, fld2) VALUES(1, 2);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld3, fld1 FROM t2;
|
|
fld3 fld1
|
|
2 2
|
|
alter TABLE t2 ADD INDEX vk(fld3, fld1), ALGORITHM= COPY;
|
|
UPDATE t1 SET fld1= 3;
|
|
SELECT fld3, fld1 FROM t2;
|
|
fld3 fld1
|
|
2 3
|
|
DROP TABLE t2, t1;
|
|
# Cascading UPDATEs and DELETEs for the multiple
|
|
# fk dependent TABLEs
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
|
|
KEY(fld1), KEY(fld2, fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
|
|
CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
|
|
KEY(fld2, fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1), (2);
|
|
INSERT INTO t2(fld1) VALUES(1), (2);
|
|
INSERT INTO t3(fld1) VALUES(1), (2);
|
|
UPDATE t1 SET fld1= 4 WHERE fld1= 1;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
2 2
|
|
4 4
|
|
SELECT fld2, fld1 FROM t3;
|
|
fld2 fld1
|
|
2 2
|
|
4 4
|
|
DROP TABLE t3, t2, t1;
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
|
|
fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1), KEY(fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
|
|
CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL,
|
|
fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1), (2);
|
|
INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default);
|
|
INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default);
|
|
UPDATE t1 SET fld1= 4 WHERE fld1= 1;
|
|
SELECT fld3, fld1 FROM t2;
|
|
fld3 fld1
|
|
1 4
|
|
2 2
|
|
SELECT fld3, fld1 FROM t3;
|
|
fld3 fld1
|
|
1 4
|
|
2 2
|
|
DROP TABLE t3, t2, t1;
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
|
|
KEY(fld1), KEY(fld2, fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE);
|
|
CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
|
|
KEY(fld2, fld1), FOREIGN KEY(fld1) REFERENCES t2(fld1)
|
|
ON DELETE CASCADE);
|
|
INSERT INTO t1 VALUES(1), (2);
|
|
INSERT INTO t2(fld1) VALUES(1), (2);
|
|
INSERT INTO t3(fld1) VALUES(1), (2);
|
|
DELETE FROM t1 WHERE fld1= 1;
|
|
SELECT fld2, fld1 FROM t2;
|
|
fld2 fld1
|
|
2 2
|
|
SELECT fld2, fld1 FROM t3;
|
|
fld2 fld1
|
|
2 2
|
|
DROP TABLE t3, t2, t1;
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
|
|
fld3 INT AS (fld2) VIRTUAL,
|
|
KEY(fld3, fld1), KEY(fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON DELETE CASCADE);
|
|
CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL,
|
|
fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t2(fld1)
|
|
ON DELETE CASCADE);
|
|
INSERT INTO t1 VALUES(1), (2);
|
|
INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default);
|
|
INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default);
|
|
DELETE FROM t1 WHERE fld1= 1;
|
|
SELECT fld3, fld1 FROM t2;
|
|
fld3 fld1
|
|
2 2
|
|
SELECT fld3, fld1 FROM t3;
|
|
fld3 fld1
|
|
2 2
|
|
DROP TABLE t3, t2, t1;
|
|
# RENAME TABLE
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL,
|
|
KEY(fld2, fld1),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON DELETE CASCADE);
|
|
INSERT INTO t1 VALUES(1), (2);
|
|
INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
|
|
RENAME TABLE t2 to t3;
|
|
DELETE FROM t1 WHERE fld1= 1;
|
|
SELECT fld2, fld1 FROM t3;
|
|
fld2 fld1
|
|
2 2
|
|
DROP TABLE t3, t1;
|
|
# FOREIGN_KEY_CHECKS disabled DURING INPLACE ALTER
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL,
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1), (2);
|
|
INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM=INPLACE;
|
|
SET foreign_key_checks = 1;
|
|
UPDATE t1 SET fld1= 3 WHERE fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
1
|
|
3
|
|
DROP TABLE t2, t1;
|
|
# GENERATED COLUMN COMPUTATION FAILS when SQL_MODE
|
|
# is set to ERROR_FOR_DIVISION_BY_ZERO
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (100/fld1) VIRTUAL,
|
|
KEY(fld2),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1), (2);
|
|
INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
|
|
UPDATE IGNORE t1 SET fld1= 0 WHERE fld1= 2;
|
|
Warnings:
|
|
Warning 1365 Division by 0
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
NULL
|
|
100
|
|
DROP TABLE t2, t1;
|
|
# CHANGE SQL_MODE and try the ERROR_FOR_DIVISION_BY_ZERO
|
|
SET sql_mode = STRICT_ALL_TABLES;
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (100/fld1) VIRTUAL,
|
|
KEY(fld2),
|
|
FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1), (2);
|
|
INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
|
|
UPDATE t1 SET fld1= 0 WHERE fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
NULL
|
|
100
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
1 100
|
|
0 NULL
|
|
DROP TABLE t2, t1;
|
|
SET sql_mode = default;
|
|
# ADD FOREIGN CONSTRAINT USING COPY
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL, KEY(fld2));
|
|
ALTER TABLE t2 ADD FOREIGN KEY (fld1)
|
|
REFERENCES t1(fld1) ON UPDATE CASCADE,
|
|
ALGORITHM=copy;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# ADD FOREIGN CONSTRAINT USING INPLACE
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL, KEY(fld2));
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE t2 ADD FOREIGN KEY (fld1)
|
|
REFERENCES t1(fld1) ON UPDATE CASCADE,
|
|
ALGORITHM=inplace;
|
|
SET foreign_key_checks = 1;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# DROP FOREIGN CONSTRAINT USING COPY
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
|
|
CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=COPY;
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
1
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
1 1
|
|
DROP TABLE t2, t1;
|
|
# DROP FOREIGN CONSTRAINT USING INPLACE
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
|
|
CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=INPLACE;
|
|
SET foreign_key_checks = 1;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
1
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
1 1
|
|
DROP TABLE t2, t1;
|
|
# ADD VC INDEX and ADD FK IN SAME COPY ALTER
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL);
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE, ALGORITHM=copy;
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# ADD VC INDEX and ADD FK IN SAME INPLACE ALTER
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL);
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE, ALGORITHM=inplace;
|
|
SET foreign_key_checks = 1;
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# ADD VC INDEX and DROP FK IN SAME COPY ALTER
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL,
|
|
CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=copy;
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
1
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
1 1
|
|
DROP TABLE t2, t1;
|
|
# ADD VC INDEX and DROP FK IN SAME INPLACE ALTER
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL,
|
|
CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=inplace;
|
|
SET foreign_key_checks = 1;
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
1
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
1 1
|
|
DROP TABLE t2, t1;
|
|
# DROP VC INDEX and ADD FK IN SAME COPY ALTER
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL,
|
|
KEY idx(fld2));
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE, ALGORITHM=COPY;
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# DROP VC INDEX and ADD FK IN SAME INPLACE ALTER
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL,
|
|
KEY idx(fld2));
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE, ALGORITHM=INPLACE;
|
|
SET foreign_key_checks = 1;
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
2
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# DROP VC INDEX and DROP FK IN SAME COPY ALTER
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL,
|
|
KEY idx(fld2),
|
|
CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=COPY;
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
1
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
1 1
|
|
DROP TABLE t2, t1;
|
|
# DROP VC INDEX and DROP FK IN SAME INPLACE ALTER
|
|
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t2(fld1 INT NOT NULL,
|
|
fld2 INT AS (fld1) VIRTUAL,
|
|
KEY idx(fld2),
|
|
CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
|
|
ON UPDATE CASCADE);
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, DEFAULT);
|
|
SET foreign_key_checks = 0;
|
|
ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=INPLACE;
|
|
SET foreign_key_checks = 1;
|
|
UPDATE t1 SET fld1= 2;
|
|
SELECT fld2 FROM t2;
|
|
fld2
|
|
1
|
|
SELECT * FROM t2;
|
|
fld1 fld2
|
|
1 1
|
|
DROP TABLE t2, t1;
|
|
CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
|
|
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
|
|
KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2(f1) VALUES(1);
|
|
EXPLAIN SELECT f1, f2 FROM t2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index NULL f1 9 NULL 1 Using index
|
|
SELECT f1, f2 FROM t2;
|
|
f1 f2
|
|
1 1
|
|
INSERT INTO t2(f1) VALUES(2);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`))
|
|
DROP TABLE t2, t1;
|
|
CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
|
|
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
|
|
KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1)
|
|
ON UPDATE CASCADE)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2(f1) VALUES(1);
|
|
EXPLAIN SELECT f1, f2 FROM t2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index NULL f1 9 NULL 1 Using index
|
|
SELECT f1, f2 FROM t2;
|
|
f1 f2
|
|
1 1
|
|
UPDATE t1 SET f1 = 2 WHERE f1 = 1;
|
|
EXPLAIN SELECT f1, f2 FROM t2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index NULL f1 9 NULL 1 Using index
|
|
SELECT f1, f2 FROM t2;
|
|
f1 f2
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
|
|
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
|
|
KEY (f1, f2))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2(f1) VALUES(1);
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1)
|
|
ON UPDATE CASCADE, ALGORITHM=INPLACE;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
UPDATE t1 SET f1 = 3;
|
|
EXPLAIN SELECT f1, f2 FROM t2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index NULL f1 9 NULL 1 Using index
|
|
SELECT f1, f2 FROM t2;
|
|
f1 f2
|
|
3 3
|
|
DROP TABLE t2, t1;
|
|
CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
|
|
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
|
|
KEY (f1, f2))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2(f1) VALUES(1);
|
|
ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1)
|
|
ON UPDATE CASCADE, ALGORITHM=COPY;
|
|
UPDATE t1 SET f1 = 3;
|
|
EXPLAIN SELECT f1, f2 FROM t2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index NULL f1 9 NULL 1 Using index
|
|
SELECT f1, f2 FROM t2;
|
|
f1 f2
|
|
3 3
|
|
DROP TABLE t2, t1;
|
|
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL,
|
|
f3 INT AS (2) VIRTUAL,
|
|
FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE,
|
|
KEY idx1 (f2, f1, f3))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2(f1) VALUES(1);
|
|
ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=INPLACE;
|
|
UPDATE t1 SET f1 = 3;
|
|
EXPLAIN SELECT f1, f3 FROM t2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index NULL idx1 9 NULL 1 Using index
|
|
SELECT f1, f3 FROM t2;
|
|
f1 f3
|
|
3 2
|
|
DROP TABLE t2, t1;
|
|
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL,
|
|
f3 INT AS (2) VIRTUAL,
|
|
FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE,
|
|
KEY idx1 (f2, f1, f3))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2(f1) VALUES(1);
|
|
ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=COPY;
|
|
UPDATE t1 SET f1 = 3;
|
|
EXPLAIN SELECT f1, f3 FROM t2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index NULL idx1 9 NULL 1 Using index
|
|
SELECT f1, f3 FROM t2;
|
|
f1 f3
|
|
3 2
|
|
DROP TABLE t2, t1;
|
|
#
|
|
# MDEV-15553 Assertion failed in dict_table_get_col_name
|
|
#
|
|
CREATE TABLE t1 (
|
|
c1 TIMESTAMP,
|
|
c2 YEAR,
|
|
c3 TIME,
|
|
c4 CHAR(10),
|
|
v1 TIMESTAMP AS (c1) VIRTUAL,
|
|
v2 YEAR AS (c2) VIRTUAL,
|
|
v3 TIME AS (c3) VIRTUAL,
|
|
v4 CHAR(10) AS (c4) VIRTUAL
|
|
) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
|
|
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
SET foreign_key_checks=0;
|
|
ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk' in the foreign table 't1'
|
|
ALTER TABLE t1 ADD INDEX(v4);
|
|
ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
|
|
SET foreign_key_checks=1;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` timestamp NULL DEFAULT NULL,
|
|
`c2` year(4) DEFAULT NULL,
|
|
`c3` time DEFAULT NULL,
|
|
`c4` char(10) DEFAULT NULL,
|
|
`v1` timestamp GENERATED ALWAYS AS (`c1`) VIRTUAL,
|
|
`v2` year(4) GENERATED ALWAYS AS (`c2`) VIRTUAL,
|
|
`v3` time GENERATED ALWAYS AS (`c3`) VIRTUAL,
|
|
`v4` char(10) GENERATED ALWAYS AS (`c4`) VIRTUAL,
|
|
KEY `v4` (`v4`),
|
|
CONSTRAINT `fk` FOREIGN KEY (`v4`) REFERENCES `nosuch` (`col`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
ALTER TABLE t1 DROP FOREIGN KEY fk;
|
|
ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
|
|
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` timestamp NULL DEFAULT NULL,
|
|
`c2` year(4) DEFAULT NULL,
|
|
`c3` time DEFAULT NULL,
|
|
`c4` char(10) DEFAULT NULL,
|
|
`v1` timestamp GENERATED ALWAYS AS (`c1`) VIRTUAL,
|
|
`v2` year(4) GENERATED ALWAYS AS (`c2`) VIRTUAL,
|
|
`v3` time GENERATED ALWAYS AS (`c3`) VIRTUAL,
|
|
`v4` char(10) GENERATED ALWAYS AS (`c4`) VIRTUAL,
|
|
KEY `v4` (`v4`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-20396 Server crashes after DELETE with SEL NULL Foreign key and a
|
|
# virtual column in index
|
|
#
|
|
CREATE TABLE parent
|
|
(
|
|
ID int unsigned NOT NULL,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
CREATE TABLE child
|
|
(
|
|
ID int unsigned NOT NULL,
|
|
ParentID int unsigned NULL,
|
|
Value int unsigned NOT NULL DEFAULT 0,
|
|
Flag int unsigned AS (Value) VIRTUAL,
|
|
PRIMARY KEY (ID),
|
|
KEY (ParentID, Flag),
|
|
FOREIGN KEY (ParentID) REFERENCES parent (ID) ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO parent (ID) VALUES (100);
|
|
INSERT INTO child (ID,ParentID,Value) VALUES (123123,100,1);
|
|
DELETE FROM parent WHERE ID=100;
|
|
select * from child;
|
|
ID ParentID Value Flag
|
|
123123 NULL 1 1
|
|
INSERT INTO parent (ID) VALUES (100);
|
|
UPDATE child SET ParentID=100 WHERE ID=123123;
|
|
DROP TABLE child, parent;
|
|
#
|
|
# MDEV-23387 dict_load_foreign() fails to load the table during alter
|
|
#
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL,
|
|
f3 INT AS (f1) VIRTUAL,
|
|
INDEX(f1), INDEX(f2))ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD CONSTRAINT r FOREIGN KEY(f2) REFERENCES t1(f1), LOCK=NONE;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(11) NOT NULL,
|
|
`f2` int(11) NOT NULL,
|
|
`f3` int(11) GENERATED ALWAYS AS (`f1`) VIRTUAL,
|
|
KEY `f1` (`f1`),
|
|
KEY `f2` (`f2`),
|
|
CONSTRAINT `r` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
ALTER TABLE t1 DROP INDEX f1;
|
|
ALTER TABLE t1 DROP f3;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-24041 Generated column DELETE with FOREIGN KEY crash InnoDB
|
|
#
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
CREATE DATABASE `a-b`;
|
|
USE `a-b`;
|
|
CREATE TABLE emails (
|
|
id int,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE email_stats (
|
|
id int,
|
|
email_id int,
|
|
date_sent char(4),
|
|
generated_email_id int as (email_id),
|
|
#generated_sent_date DATE GENERATED ALWAYS AS (date_sent),
|
|
PRIMARY KEY (id),
|
|
KEY mautic_generated_sent_date_email_id (generated_email_id),
|
|
FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE emails_metadata (
|
|
email_id int,
|
|
PRIMARY KEY (email_id),
|
|
CONSTRAINT FK FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO emails VALUES (1);
|
|
INSERT INTO email_stats (id, email_id, date_sent) VALUES (1,1,'Jan');
|
|
INSERT INTO emails_metadata VALUES (1);
|
|
UPDATE emails SET id=2;
|
|
DELETE FROM emails;
|
|
DROP TABLE email_stats;
|
|
DROP TABLE emails_metadata;
|
|
DROP TABLE emails;
|
|
DROP DATABASE `a-b`;
|
|
USE test;
|
|
#
|
|
# Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE
|
|
#
|
|
# Test-Case 1
|
|
CREATE TABLE emails (
|
|
id int unsigned NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE email_stats (
|
|
id bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
email_id int unsigned DEFAULT NULL,
|
|
date_sent datetime NOT NULL,
|
|
generated_sent_date date GENERATED ALWAYS AS
|
|
(concat(year(date_sent), '-', lpad(month(date_sent), 2, '0'),
|
|
'-', lpad(dayofmonth(date_sent), 2, '0'))),
|
|
PRIMARY KEY (id),
|
|
KEY IDX_ES1 (email_id),
|
|
KEY mautic_generated_sent_date_email_id(generated_sent_date, email_id),
|
|
FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
|
|
) ENGINE = InnoDB;
|
|
INSERT INTO emails VALUES (1);
|
|
INSERT INTO email_stats (id, email_id, date_sent)
|
|
VALUES (1, 1, '2020-10-22 13:32:41');
|
|
SELECT * FROM email_stats;
|
|
id email_id date_sent generated_sent_date
|
|
1 1 2020-10-22 13:32:41 2020-10-22
|
|
DELETE FROM emails;
|
|
DELETE FROM email_stats;
|
|
# Clean up.
|
|
DROP TABLE email_stats;
|
|
DROP TABLE emails;
|
|
# Test-Case 2
|
|
CREATE TABLE emails (
|
|
id int unsigned NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4
|
|
COLLATE = utf8mb4_unicode_ci
|
|
ROW_FORMAT = DYNAMIC;
|
|
CREATE TABLE email_stats (
|
|
id bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
email_id int unsigned DEFAULT NULL,
|
|
date_sent datetime NOT NULL,
|
|
generated_sent_date date GENERATED ALWAYS AS
|
|
(concat(year(date_sent), '-', lpad(month(date_sent), 2, '0'),
|
|
'-', lpad(dayofmonth(date_sent), 2, '0'))),
|
|
PRIMARY KEY (id),
|
|
KEY IDX_ES1 (email_id),
|
|
KEY mautic_generated_sent_date_email_id(generated_sent_date, email_id),
|
|
FOREIGN KEY (email_id) REFERENCES emails (id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE SET NULL
|
|
) ENGINE = InnoDB;
|
|
INSERT INTO emails VALUES (1);
|
|
INSERT INTO email_stats (id, email_id, date_sent)
|
|
VALUES (1, 1, '2020-10-22 13:32:41');
|
|
UPDATE emails SET id = 2 where id = 1;
|
|
SELECT id FROM email_stats WHERE generated_sent_date IS NULL;
|
|
id
|
|
SELECT * FROM email_stats;
|
|
id email_id date_sent generated_sent_date
|
|
1 NULL 2020-10-22 13:32:41 2020-10-22
|
|
UPDATE email_stats
|
|
SET email_id=2
|
|
WHERE DATE(generated_sent_date) = '2020-10-22';
|
|
SELECT * FROM email_stats;
|
|
id email_id date_sent generated_sent_date
|
|
1 2 2020-10-22 13:32:41 2020-10-22
|
|
# Clean up.
|
|
DROP TABLE email_stats;
|
|
DROP TABLE emails;
|
|
# Test-case 3
|
|
CREATE TABLE emails (
|
|
id int unsigned NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE = INNODB
|
|
DEFAULT CHARSET = utf8mb4
|
|
COLLATE = utf8mb4_unicode_ci
|
|
ROW_FORMAT = DYNAMIC;
|
|
CREATE TABLE email_stats (
|
|
id bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
email_id int unsigned DEFAULT NULL,
|
|
date_sent datetime NOT NULL,
|
|
generated_sent_email varchar(20) GENERATED ALWAYS AS
|
|
(CONCAT(YEAR(date_sent), '-', COALESCE(email_id, '$'))),
|
|
PRIMARY KEY (id),
|
|
KEY idx_es1 (email_id),
|
|
KEY mautic_generated_sent_date_email(generated_sent_email, email_id),
|
|
FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
|
|
) ENGINE = INNODB;
|
|
INSERT INTO emails VALUES (1);
|
|
INSERT INTO email_stats (id, email_id, date_sent)
|
|
VALUES (1, 1, '2020-10-22 13:32:41');
|
|
SELECT * FROM email_stats;
|
|
id email_id date_sent generated_sent_email
|
|
1 1 2020-10-22 13:32:41 2020-1
|
|
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1';
|
|
date_sent
|
|
2020-10-22 13:32:41
|
|
DELETE FROM emails;
|
|
SELECT * FROM email_stats;
|
|
id email_id date_sent generated_sent_email
|
|
1 NULL 2020-10-22 13:32:41 2020-$
|
|
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$';
|
|
date_sent
|
|
2020-10-22 13:32:41
|
|
# Clean up.
|
|
DROP TABLE email_stats;
|
|
DROP TABLE emails;
|
|
# Test-case 4
|
|
CREATE TABLE emails (
|
|
id int unsigned NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE = INNODB;
|
|
CREATE TABLE email_stats (
|
|
id bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
email_id int unsigned DEFAULT NULL,
|
|
date_sent datetime NOT NULL,
|
|
generated_sent_email varchar(20) GENERATED ALWAYS AS
|
|
(CONCAT(YEAR(date_sent), '-', COALESCE(email_id, '$'))),
|
|
PRIMARY KEY (id),
|
|
KEY idx_es1 (email_id),
|
|
KEY mautic_generated_sent_date_email(generated_sent_email, email_id),
|
|
FOREIGN KEY (email_id) REFERENCES emails (id) ON UPDATE SET NULL
|
|
) ENGINE = INNODB;
|
|
INSERT INTO emails VALUES (1);
|
|
INSERT INTO email_stats (id, email_id, date_sent)
|
|
VALUES (1, 1, '2020-10-22 13:32:41');
|
|
SELECT * FROM email_stats;
|
|
id email_id date_sent generated_sent_email
|
|
1 1 2020-10-22 13:32:41 2020-1
|
|
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1';
|
|
date_sent
|
|
2020-10-22 13:32:41
|
|
UPDATE emails SET id = 2 WHERE id = 1;
|
|
SELECT * FROM email_stats;
|
|
id email_id date_sent generated_sent_email
|
|
1 NULL 2020-10-22 13:32:41 2020-$
|
|
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$';
|
|
date_sent
|
|
2020-10-22 13:32:41
|
|
DROP TABLE email_stats;
|
|
DROP TABLE emails;
|
|
CREATE TABLE emails (breaker int unsigned,
|
|
KEY (breaker),
|
|
id int unsigned NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=INNODB;
|
|
CREATE TABLE email_stats (
|
|
id bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
email_id int unsigned DEFAULT NULL,
|
|
date_sent datetime NOT NULL,
|
|
generated_sent_email varchar(20) GENERATED ALWAYS AS
|
|
(CONCAT(YEAR(date_sent),
|
|
'-',
|
|
COALESCE(email_id, '$'))),
|
|
PRIMARY KEY (id),
|
|
KEY idx_es1 (email_id),
|
|
KEY mautic_generated_sent_date_email (generated_sent_email, email_id),
|
|
FOREIGN KEY fk_ea1 (email_id) REFERENCES emails (breaker)
|
|
ON DELETE SET NULL
|
|
) ENGINE=INNODB;
|
|
show create table email_stats;
|
|
Table Create Table
|
|
email_stats CREATE TABLE `email_stats` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`email_id` int(10) unsigned DEFAULT NULL,
|
|
`date_sent` datetime NOT NULL,
|
|
`generated_sent_email` varchar(20) GENERATED ALWAYS AS (concat(year(`date_sent`),'-',coalesce(`email_id`,'$'))) VIRTUAL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_es1` (`email_id`),
|
|
KEY `mautic_generated_sent_date_email` (`generated_sent_email`,`email_id`),
|
|
CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES `emails` (`breaker`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO emails VALUES (1,1);
|
|
INSERT INTO email_stats(id, email_id, date_sent)
|
|
VALUES (1, 1, '2020-10-22 13:32:41');
|
|
SELECT * FROM email_stats;
|
|
id email_id date_sent generated_sent_email
|
|
1 1 2020-10-22 13:32:41 2020-1
|
|
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1';
|
|
date_sent
|
|
2020-10-22 13:32:41
|
|
DELETE FROM emails;
|
|
SELECT * FROM email_stats;
|
|
id email_id date_sent generated_sent_email
|
|
1 NULL 2020-10-22 13:32:41 2020-$
|
|
SELECT date_sent
|
|
FROM email_stats force index (mautic_generated_sent_date_email)
|
|
WHERE generated_sent_email = '2020-$';
|
|
date_sent
|
|
2020-10-22 13:32:41
|
|
SELECT date_sent
|
|
FROM email_stats force index (idx_es1)
|
|
WHERE generated_sent_email = '2020-$';
|
|
date_sent
|
|
2020-10-22 13:32:41
|
|
DROP TABLE email_stats;
|
|
DROP TABLE emails;
|