mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2026-03-05 07:58:40 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/gcol/r/innodb_virtual_fk.result
Aleksey Midenkov a518e1dd42 MDEV-20865 Store foreign key info in TABLE_SHARE 
1. Access foreign keys via TABLE_SHARE::foreign_keys and
   TABLE_SHARE::referenced_keys;

   foreign_keys and referenced_keys are lists in TABLE_SHARE.

2. Remove handler FK interface:

   - get_foreign_key_list()
   - get_parent_foreign_key_list()
   - referenced_by_foreign_key()

3. Invalidate referenced shares on:

   - RENAME TABLE
   - DROP TABLE
   - RENAME COLUMN
   - ADD FOREIGN KEY

   When foreign table is created or altered by the above operations
   all referenced shares are closed. This blocks the operation while
   any referenced shares are used (when at least one its TABLE
   instance is locked).

4. Update referenced shares on:

   - CREATE TABLE

   On CREATE TABLE add items to referenced_keys of referenced
   shares. States of referenced shares are restored in case of errors.

5. Invalidate foreign shares on:

   - RENAME TABLE
   - RENAME COLUMN

   The above-mentioned blocking takes effect.

6. Check foreign/referenced shares consistency on:

   - CHECK TABLE

7. Temporary change until MDEV-21051:

   InnoDB fill foreign key info at handler open().

FOREIGN_KEY_INFO is refactored to FK_info holding Lex_cstring.

On first TABLE open FK_info is loaded from storage engine into
TABLE_SHARE. All referenced shares (if any exists) are closed. This
leads to blocking of first time foreign table open while referenced
tables are used.

MDEV-21311 Converge Foreign_key and supplemental generated Key together

mysql_prepare_create_table() does data validation and such utilities
as automatic name generation. But it does that only for indexes and
ignores Foreign_key objects. Now as Foreign_key data needs to be
stored in FRM files as well this processing must be done for it like
for any other Key objects.

Replace Key::FOREIGN_KEY type with Key::foreign flag of type
Key::MULTIPLE and Key::generated set to true. Construct one object
with Key::foreign == true instead of two objects of type
Key::FOREIGN_KEY and Key::MULTIPLE.

MDEV-21051 datadict refactorings

- Move read_extra2() to datadict.cc
- Refactored extra2_fields to Extra2_info
- build_frm_image() readability

MDEV-21051 build_table_shadow_filename() refactoring

mysql_prepare_alter_table() leaks fixes

MDEV-21051 amend system tables locking restriction

Table mysql.help_relation has foreign key to mysql.help_keyword. On
bootstrap when help_relation is opened, it preopens help_keyword for
READ and fails in lock_tables_check().

If system table is opened for write then fk references are opened for
write.

Related to: Bug#25422, WL#3984
Tests: main.lock

MDEV-21051 Store and read foreign key info into/from FRM files

1. Introduce Foreign_key_io class which creates/parses binary stream
containing foreign key structures. Referenced tables store there only
hints about foreign tables (their db and name), they restore full info
from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in
extra2 section of FRM file.

2. Modify mysql_prepare_create_table() to generate names for foreign
keys. Until InnoDB storage of foreign keys is removed, FK names must
be unique across the database: the FK name must be based on table
name.

3. Keep stored data in sync on DDL changes. Referenced tables update
their foreign hints after following operations on foreign tables:

  - RENAME TABLE
  - DROP TABLE
  - CREATE TABLE
  - ADD FOREIGN KEY
  - DROP FOREIGN KEY

Foreign tables update their foreign info after following operations on
referenced tables:

  - RENAME TABLE
  - RENAME COLUMN

4. To achieve 3. there must be ability to rewrite extra2 section of
FRM file without full reparse. FRM binary is built from primary
structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.

Use shadow write and rename like fast_alter_partition_table()
does. Shadow FRM is new FRM file that replaces the old one.

CREATE TABLE workflow:

  1. Foreign_key is constructed in parser, placed into
     alter_info->key_list;

  2. mysql_prepare_create_table() translates them to FK_info, assigns
     foreign_id if needed;

  3. build_frm_image() writes two FK_info lists into FRM's extra2
     section, for referenced keys it stores only table names (hints);

  4. init_from_binary_frm_image() parses extra2 section and fills
     foreign_keys and referenced_keys of TABLE_SHARE.

     It restores referenced_keys by reading hint list of table names,
     opening corresponding shares and restoring FK_info from their
     foreign_keys. Hints resolution is done only when initializing
     non-temporary shares. Usually temporary share has different
     (temporary) name and it is impossible to resolve foreign keys by
     that name (as we identify them by both foreign and referenced
     table names). Another not unimportant reason is performance: this
     saves spare share acquisitions.

ALTER TABLE workflow:

  1. Foreign_key is constructed in parser, placed into
     alter_info->key_list;

  2. mysql_prepare_alter_table() prepares action lists and share list
     of foreigns/references;

  3. mysql_prepare_alter_table() locks list of foreigns/references by
     MDL_INTENTION_EXCLUSIVE, acquires shares;

  4. prepare_create_table() converts key_list into FK_list, assigns
     foreign_id;

  5. shadow FRM of altered table is created;

  6. data is copied;

  7. altered table is locked by MDL_EXCLUSIVE;

  8. fk_handle_alter() processes action lists, creates FK backups,
     modifies shares, writes shadow FRMs;

  9. altered table is closed;

  10. shadow FRMs are installed;

  11. altered table is renamed, FRM backup deleted;

  12. (TBD in MDEV-21053) shadow FRMs installation log closed, backups
      deleted;

On FK backup system:

In case of failed DDL operation all shares that was modified must be
restored into original state. This is done by FK_ddl_backup (CREATE,
DROP), FK_rename_backup (RENAME), FK_alter_backup (ALTER).

On STL usage:

STL is used for utility not performance-critical algorithms, core
structures hold native List. A wrapper was made to convert STL
exception into bool error status or NULL value.

MDEV-20865 fk_check_consistency() in CHECK TABLE

Self-refs fix

Test table_flags fix: "debug" deviation is now gone.

FIXMEs: +16 -1
2025-09-02 13:24:36 +03:00

1028 lines
30 KiB
Text
Raw Permalink Blame History

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 42S02: Table 'test.nosuch' doesn't exist
SET foreign_key_checks=0;
ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
ERROR HY000: 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 42S02: Table 'test.nosuch' doesn't exist
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;
Reference in a new issue View git blame Copy permalink