Server IP : 172.67.216.182 / Your IP : 172.70.143.189 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/innodb/t/ |
Upload File : |
-- source include/have_innodb.inc --echo # --echo # Bug#22469130: FOREIGN KEY ON DELETE CASCADE NOT ALLOWED --echo # WHEN A VIRTUAL INDEX EXISTS. --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t2; --enable_warnings SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; SELECT fld3 FROM t2; --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3 FROM t2; --enable_warnings SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t2; --enable_warnings ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY; UPDATE t1 SET fld1= 3; SELECT fld2, fld1 FROM t2; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t2; --enable_warnings DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t2; --enable_warnings ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY; UPDATE t1 SET fld1= 3; SELECT fld2, fld1 FROM t2; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t2; --enable_warnings DROP TABLE t2, t1; --echo # 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; ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= COPY; UPDATE t1 SET fld1= 3; SELECT fld2, fld1 FROM t2; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t2; --enable_warnings DROP TABLE t2, t1; --echo # 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; ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= INPLACE; UPDATE t1 SET fld1= 3; SELECT fld2, fld1 FROM t2; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t2; --enable_warnings DROP TABLE t2, t1; --echo # Drop the VIRTUAL INDEX contains fk constraint column --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t2; --enable_warnings SELECT fld3, fld1 FROM t2; ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY; UPDATE t1 SET fld1= 3; SELECT fld3, fld1 FROM t2; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t2; --enable_warnings DROP TABLE t2, t1; --echo # Drop the VIRTUAL INDEX which contains fk constraint column --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t2; --enable_warnings SELECT fld3, fld1 FROM t2; alter TABLE t2 DROP INDEX vk, ALGORITHM= INPLACE; UPDATE t1 SET fld1= 3; SELECT fld3, fld1 FROM t2; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t2; --enable_warnings DROP TABLE t2, t1; --echo # Add the VIRTUAL INDEX contains fk constraint column --echo # 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; alter TABLE t2 ADD INDEX vk(fld3, fld1), ALGORITHM= COPY; UPDATE t1 SET fld1= 3; SELECT fld3, fld1 FROM t2; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t2; --enable_warnings DROP TABLE t2, t1; --echo # Cascading UPDATEs and DELETEs for the multiple --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t2; SELECT fld2, fld1 FROM t2; --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t3; --enable_warnings SELECT fld2, fld1 FROM t3; 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t2; SELECT fld3, fld1 FROM t2; --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t3; --enable_warnings SELECT fld3, fld1 FROM t3; 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t2; SELECT fld2, fld1 FROM t2; --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t3; --enable_warnings SELECT fld2, fld1 FROM t3; 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t2; SELECT fld3, fld1 FROM t2; --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld3, fld1 FROM t3; --enable_warnings SELECT fld3, fld1 FROM t3; DROP TABLE t3, t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2, fld1 FROM t3; --enable_warnings SELECT fld2, fld1 FROM t3; DROP TABLE t3, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings DROP TABLE t2, t1; --echo # GENERATED COLUMN COMPUTATION FAILS when SQL_MODE --echo # 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); --error ER_DIVISION_BY_ZERO UPDATE t1 SET fld1= 0 WHERE fld1= 2; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; SELECT fld2 FROM t2; --enable_warnings DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; SELECT fld2 FROM t2; SELECT * FROM t2; --enable_warnings DROP TABLE t2, t1; SET sql_mode = default; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; --echo # 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; --disable_warnings --replace_column 1 x 2 x 4 x 6 x 8 x 9 x 10 x 11 x 12 x EXPLAIN SELECT fld2 FROM t2; --enable_warnings SELECT fld2 FROM t2; SELECT * FROM t2; DROP TABLE t2, t1; # Foreign key constraint references to virtual index 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; SELECT f1, f2 FROM t2; --error ER_NO_REFERENCED_ROW_2 INSERT INTO t2(f1) VALUES(2); DROP TABLE t2, t1; # Update foreign key constraint references to virtual index 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; SELECT f1, f2 FROM t2; UPDATE t1 SET f1 = 2 WHERE f1 = 1; EXPLAIN SELECT f1, f2 FROM t2; SELECT f1, f2 FROM t2; DROP TABLE t2, t1; # Add foreign key constraint via inplace alter references to virtual index 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; SELECT f1, f2 FROM t2; DROP TABLE t2, t1; # Add foreign key constraint via copy alter references to virtual index 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; SELECT f1, f2 FROM t2; DROP TABLE t2, t1; # Drop column via inplace alter which triggers to remove the FK index idx 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; SELECT f1, f3 FROM t2; DROP TABLE t2, t1; # Drop column via copy alter which triggers to remove the FK index idx 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; SELECT f1, f3 FROM t2; DROP TABLE t2, t1; --echo # --echo # Bug#32124113 MYSQL CRASH FOR GENERATED COLUMN DELETE WITH FOREIGN KEY --echo # CREATE TABLE `emails` ( `id` int(10) 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(20) unsigned NOT NULL AUTO_INCREMENT, `email_id` int(10) 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'))) VIRTUAL, PRIMARY KEY (`id`), KEY `IDX_ES1` (`email_id`), KEY `mautic_generated_sent_date_email_id` (`generated_sent_date`,`email_id`), CONSTRAINT `FK_ES1` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC; CREATE TABLE `emails_metadata` ( `email_id` int(10) unsigned NOT NULL, PRIMARY KEY (`email_id`), CONSTRAINT `FK_EM1` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC; INSERT INTO `emails` VALUES (1); INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES (1,1,'2020-10-22 13:32:41'); INSERT INTO `emails_metadata` VALUES (1); DELETE FROM `emails`; #cleanup DROP TABLE `email_stats`; DROP TABLE `emails_metadata`; DROP TABLE `emails`;