Server IP : 172.67.216.182 / Your IP : 162.158.88.102 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 --source include/not_embedded.inc --echo # --echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW --echo # DICT_CREATE_FOREIGN_CONSTR --echo # create table t1 (f1 int primary key) engine=InnoDB; --error ER_CANNOT_ADD_FOREIGN create table t2 (f1 int primary key, constraint c1 foreign key (f1) references t1(f1), constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; create table t2 (f1 int primary key, constraint c1 foreign key (f1) references t1(f1)) engine=innodb; --replace_regex /#sql-[0-9a-f_]*'/#sql-temporary'/ --error ER_DUP_KEY alter table t2 add constraint c1 foreign key (f1) references t1(f1); set foreign_key_checks = 0; --error ER_FK_DUP_NAME alter table t2 add constraint c1 foreign key (f1) references t1(f1); drop table t2, t1; --echo # --echo # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN --echo # NULL/NOT NULL MISMATCH --echo # set foreign_key_checks = 1; show variables like 'foreign_key_checks'; CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, INDEX idx(a)) ENGINE=InnoDB; CREATE TABLE t2 (a INT KEY, b INT, INDEX ind(b), FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB; show create table t1; show create table t2; INSERT INTO t1 VALUES (1, 80); INSERT INTO t1 VALUES (2, 81); INSERT INTO t1 VALUES (3, 82); INSERT INTO t1 VALUES (4, 83); INSERT INTO t1 VALUES (5, 84); INSERT INTO t2 VALUES (51, 1); INSERT INTO t2 VALUES (52, 2); INSERT INTO t2 VALUES (53, 3); INSERT INTO t2 VALUES (54, 4); INSERT INTO t2 VALUES (55, 5); SELECT a, b FROM t1 ORDER BY a; SELECT a, b FROM t2 ORDER BY a; --error ER_NO_REFERENCED_ROW_2 INSERT INTO t2 VALUES (56, 6); ALTER TABLE t1 CHANGE a id INT; SELECT id, b FROM t1 ORDER BY id; SELECT a, b FROM t2 ORDER BY a; --echo # Operations on child table --error ER_NO_REFERENCED_ROW_2 INSERT INTO t2 VALUES (56, 6); --error ER_NO_REFERENCED_ROW_2 UPDATE t2 SET b = 99 WHERE a = 51; DELETE FROM t2 WHERE a = 53; SELECT id, b FROM t1 ORDER BY id; SELECT a, b FROM t2 ORDER BY a; --echo # Operations on parent table DELETE FROM t1 WHERE id = 1; UPDATE t1 SET id = 50 WHERE id = 5; SELECT id, b FROM t1 ORDER BY id; SELECT a, b FROM t2 ORDER BY a; DROP TABLE t2, t1; --echo # --echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART --echo # base bug#24818604 [GR] --echo # CREATE TABLE t1 (c1 INT PRIMARY KEY); CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1)); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); SELECT unique_constraint_name FROM information_schema.referential_constraints WHERE table_name = 't2'; --source include/restart_mysqld.inc SELECT unique_constraint_name FROM information_schema.referential_constraints WHERE table_name = 't2'; SELECT * FROM t1; SELECT unique_constraint_name FROM information_schema.referential_constraints WHERE table_name = 't2'; DROP TABLE t2; DROP TABLE t1; --echo # --echo # Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE --echo # --echo #Test-Case 1 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'))) VIRTUAL, PRIMARY KEY (`id`), KEY `IDX_ES1` (`email_id`), KEY `mautic_generated_sent_date_email_id` (`generated_sent_date`,`email_id`), CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`) ON DELETE SET NULL ) 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'); SELECT * FROM `email_stats`; DELETE FROM `emails`; DELETE FROM `email_stats`; #clean up. DROP TABLE `email_stats`; DROP TABLE `emails`; --echo # 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'))) VIRTUAL, PRIMARY KEY (`id`), KEY `IDX_ES1` (`email_id`), KEY `mautic_generated_sent_date_email_id` (`generated_sent_date`,`email_id`), CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`) ON DELETE SET NULL ON UPDATE SET NULL ) 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'); UPDATE `emails` SET `id` = 2 where `id` = 1; SELECT id FROM `email_stats` WHERE `generated_sent_date` IS NULL; SELECT * FROM `email_stats`; UPDATE `email_stats` SET `email_id`=2 WHERE DATE(`generated_sent_date`) = '2020-10-22'; SELECT * FROM `email_stats`; #clean up. DROP TABLE `email_stats`; DROP TABLE `emails`; --echo # 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`, ' '))) 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` (`id`) ON DELETE SET NULL ) 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'); SELECT * FROM `email_stats`; SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1'; DELETE FROM `emails`; SELECT * FROM `email_stats`; SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-'; #clean up. DROP TABLE `email_stats`; DROP TABLE `emails`; --echo # test-case 4 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`, ' '))) 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` (`id`) ON UPDATE SET NULL ) 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'); SELECT * FROM `email_stats`; SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1'; UPDATE `emails` SET `id` = 2 WHERE `id` = 1; SELECT * FROM `email_stats`; SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-'; #clean up. DROP TABLE `email_stats`; DROP TABLE `emails`;