mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-14 09:46:14 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/versioning/t/foreign.test
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

627 lines
19 KiB
Text
Raw Permalink Blame History

--source suite/versioning/key_type.inc
--source suite/versioning/common.inc
--echo #################
--echo # Test RESTRICT #
--echo #################
--replace_result "$KEY_TYPE" KEY_TYPE
eval create table parent(
id int,
$KEY_TYPE (id)
) engine innodb;
--replace_result $sys_datatype_expl SYS_DATATYPE
eval create table child(
parent_id int,
sys_start $sys_datatype_expl as row start invisible,
sys_end $sys_datatype_expl as row end invisible,
period for system_time(sys_start, sys_end),
foreign key(parent_id) references parent(id)
on delete restrict
on update restrict
) engine innodb with system versioning;
insert into parent values(1);
insert into child values(1);
-- error ER_ROW_IS_REFERENCED_2
delete from parent where id = 1;
delete from child where parent_id = 1;
delete from parent where id = 1;
insert into parent values(1);
insert into child values(1);
-- error ER_ROW_IS_REFERENCED_2
update parent set id=id+1;
delete from child;
update parent set id=id+1;
select * from child for system_time all;
drop table child;
drop table parent;
--echo ##############################################
--echo # Test when clustered index is a foreign key #
--echo ##############################################
--replace_result "$KEY_TYPE" KEY_TYPE
eval create table parent(
id int(10) unsigned,
$KEY_TYPE (id)
) engine innodb;
--replace_result $sys_datatype_expl SYS_DATATYPE
eval create table child(
parent_id int(10) unsigned primary key,
sys_start $sys_datatype_expl as row start invisible,
sys_end $sys_datatype_expl as row end invisible,
period for system_time(sys_start, sys_end),
foreign key(parent_id) references parent(id)
) engine innodb with system versioning;
insert into parent values(1);
insert into child values(1);
-- error ER_ROW_IS_REFERENCED_2
delete from parent where id = 1;
drop table child;
drop table parent;
--echo ################
--echo # Test CASCADE #
--echo ################
--replace_result "$KEY_TYPE" KEY_TYPE
eval create table parent(
id int,
$KEY_TYPE (id)
) engine innodb;
--replace_result $sys_datatype_expl SYS_DATATYPE
eval create table child(
parent_id int,
sys_start $sys_datatype_expl as row start invisible,
sys_end $sys_datatype_expl as row end invisible,
period for system_time(sys_start, sys_end),
foreign key(parent_id) references parent(id)
on delete cascade
on update cascade
) engine innodb with system versioning;
insert into parent values(1);
insert into child values(1);
delete from parent where id = 1;
select * from child;
select * from child for system_time all;
insert into parent values(1);
insert into child values(1);
update parent set id = id + 1;
select * from child;
select * from child for system_time all;
drop table child;
drop table parent;
--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE
eval create or replace table parent (
id int,
$KEY_TYPE(id),
sys_start $sys_datatype_expl as row start invisible,
sys_end $sys_datatype_expl as row end invisible,
period for system_time(sys_start, sys_end)
) with system versioning
engine innodb;
create or replace table child (
x int,
parent_id int not null,
constraint `parent-fk`
foreign key (parent_id) references parent (id)
on delete cascade
on update restrict
)
engine innodb;
insert into parent (id) values (2);
insert into child (x, parent_id) values (2, 2);
delete from parent;
select * from child;
drop table child;
drop table parent;
--replace_result "$KEY_TYPE" KEY_TYPE
eval create or replace table parent (
id int,
$KEY_TYPE(id)
)
engine innodb;
--replace_result $sys_datatype_expl SYS_DATATYPE
eval create or replace table child (
id int primary key,
parent_id int not null,
row_start $sys_datatype_expl as row start invisible,
row_end $sys_datatype_expl as row end invisible,
period for system_time(row_start, row_end),
constraint `parent-fk`
foreign key (parent_id) references parent (id)
on delete cascade
on update restrict
) with system versioning
engine innodb;
insert into parent (id) values (3);
insert into child (id, parent_id) values (3, 3);
delete from parent;
select * from child;
select *, check_row(row_start, row_end) from child for system_time all;
drop table child;
drop table parent;
--echo #################
--echo # Test SET NULL #
--echo #################
--replace_result "$KEY_TYPE" KEY_TYPE
eval create table parent(
id int,
$KEY_TYPE (id)
) engine innodb;
--replace_result $sys_datatype_expl SYS_DATATYPE
eval create or replace table child(
parent_id int,
sys_start $sys_datatype_expl as row start invisible,
sys_end $sys_datatype_expl as row end invisible,
period for system_time(sys_start, sys_end),
foreign key(parent_id) references parent(id)
on delete set null
on update set null
) engine innodb with system versioning;
insert into parent values(1);
insert into child values(1);
delete from child;
insert into child values(1);
delete from parent where id = 1;
select * from child;
select *, current_row(sys_end) as current_row from child for system_time all order by sys_end;
delete from child;
insert into parent values(1);
insert into child values(1);
update parent set id= id + 1;
select * from child;
select *, current_row(sys_end) as current_row from child for system_time all order by sys_end;
drop table child;
drop table parent;
--echo ###########################
--echo # Parent table is foreign #
--echo ###########################
--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE
eval create or replace table parent(
id int,
$KEY_TYPE (id),
sys_start $sys_datatype_expl as row start invisible,
sys_end $sys_datatype_expl as row end invisible,
period for system_time(sys_start, sys_end)
) engine innodb with system versioning;
create or replace table child(
parent_id int,
foreign key(parent_id) references parent(id)
) engine innodb;
insert into parent values(1);
insert into child values(1);
-- error ER_ROW_IS_REFERENCED_2
delete from parent;
-- error ER_ROW_IS_REFERENCED_2
update parent set id=2;
delete from child;
delete from parent;
-- error ER_NO_REFERENCED_ROW_2
insert into child values(1);
insert into parent values(1);
insert into child values(1);
-- error ER_ROW_IS_REFERENCED_2
delete from parent;
-- error ER_ROW_IS_REFERENCED_2
update parent set id=2;
drop table child;
drop table parent;
--echo ###################
--echo # crash on DELETE #
--echo ###################
--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE
eval create or replace table a (
cola int(10),
$KEY_TYPE (cola),
v_cola int(10) as (cola mod 10) virtual,
sys_start $sys_datatype_expl as row start invisible,
sys_end $sys_datatype_expl as row end invisible,
period for system_time(sys_start, sys_end)
) engine=innodb with system versioning;
create index v_cola on a (v_cola);
--replace_result $sys_datatype_expl SYS_DATATYPE
eval create or replace table b(
cola int(10),
v_cola int(10),
sys_start $sys_datatype_expl as row start invisible,
sys_end $sys_datatype_expl as row end invisible,
period for system_time(sys_start, sys_end)
) engine=innodb with system versioning;
alter table b add constraint `v_cola_fk`
foreign key (v_cola) references a (v_cola);
insert into a(cola) values (12);
insert into b(cola, v_cola) values (10,2);
--error ER_ROW_IS_REFERENCED_2
delete from a;
drop table b, a;
--echo ###############################################
--echo # CASCADE UPDATE foreign not system versioned #
--echo ###############################################
create or replace table parent (
id smallint unsigned not null auto_increment,
value int unsigned not null,
primary key (id, value)
) engine = innodb;
--replace_result $sys_datatype_expl SYS_DATATYPE
eval create or replace table child (
id mediumint unsigned not null auto_increment primary key,
parent_id smallint unsigned not null,
parent_value int unsigned not null,
sys_start $sys_datatype_expl as row start invisible,
sys_end $sys_datatype_expl as row end invisible,
period for system_time(sys_start, sys_end),
constraint `fk_child_parent`
foreign key (parent_id, parent_value) references parent (id, value)
on delete cascade
on update cascade
) engine = innodb with system versioning;
create or replace table subchild (
id int not null auto_increment primary key,
parent_id smallint unsigned not null,
parent_value int unsigned not null,
constraint `fk_subchild_child_parent`
foreign key (parent_id, parent_value) references child (parent_id, parent_value)
on delete cascade
on update cascade
) engine=innodb;
insert into parent (value) values (23);
--disable_cursor_protocol
--enable_prepare_warnings
select id, value from parent into @id, @value;
--disable_prepare_warnings
--enable_cursor_protocol
insert into child values (default, @id, @value);
insert into subchild values (default, @id, @value);
select parent_id from subchild;
update parent set id = 11, value = value + 1;
select parent_id from subchild;
select * from child;
delete from parent;
select count(*) from child;
select * from child for system_time all;
select count(*) from subchild;
drop table subchild, child, parent;
--echo #
--echo # MDEV-18057 Assertion `(node->state == 5) || (node->state == 6)' failed in row_upd_sec_step upon DELETE after UPDATE failed due to FK violation
--echo #
create or replace table t1 (f1 int, key(f1)) engine=innodb;
create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning;
set foreign_key_checks= off;
insert ignore into t2 values (1);
set foreign_key_checks= on;
--error ER_NO_REFERENCED_ROW_2
update t2 set f2= 2;
delete from t2;
drop table t2, t1;
--echo #
--echo # MDEV-18879 Corrupted record inserted by FOREIGN KEY operation
--echo #
SET timestamp = 1;
SET time_zone='+02:00';
SELECT now();
CREATE TABLE t1 (
pk INT UNSIGNED PRIMARY KEY,
f1 varchar(255) CHARACTER SET ucs2,
f2 longtext CHARACTER SET ucs2,
f3 varchar(255),
f4 char(255),
f5 longtext CHARACTER SET ucs2,
f6 INT UNSIGNED,
f7 INT UNSIGNED,
f8 INT UNSIGNED,
f9 INT UNSIGNED,
f10 INT UNSIGNED,
f11 INT UNSIGNED,
f12 varchar(255) CHARACTER SET ucs2,
f13 char(255) CHARACTER SET ucs2,
f14 char(255) CHARACTER SET ucs2,
f15 varchar(255),
f16 longtext,
f17 char(255)
) ENGINE=InnoDB WITH SYSTEM VERSIONING;
INSERT INTO t1 VALUES
(1, 'a', 'e', 'f', 'a', 'generate', 1, 2, 3, 4, 5, 6, 'main', 'against', 'b', 'u', 'explode', 'tomorrow'),
(2, REPEAT('a',127), 'f', 'k', 'game', 'g', 2, 3, 4, 5, 6, 7, REPEAT('o',222), 'oven', 'flower', REPEAT('r',120), 'l', 'g'),
(3, 'weekly', 'x', 'v', 'r', 'c', 3, 4, 5, 6, 7, 8, 'validity', 'y', 'h', 'oxygen', 'venture', 'uncertainty'),
(4, 'r', 't', REPEAT('b',153), 'modern', 'h', 4, 5, 6, 7, 8, 9, REPEAT('g',128), 'a', 'w', 'f', 'b', 'b'),
(5, 'h', 'y', REPEAT('v',107), 'knife', 'profession', 5, 6, 7, 8, 9, 0, 'infection', 'u', 'likelihood', REPEAT('n',149), 'folk', 'd'),
(6, 'g', 'violent', REPEAT('o',28), 'capital', 'p', 6, 7, 8, 9, 0, 1, 'w', 'patron', 'd', 'y', 'originally', 'k'),
(7, 'k', 'uncomfortable', REPEAT('v',248), 'y', 'link', 7, 8, 9, 0, 1, 2, REPEAT('j',204), 'j', 'statute', 'emphasis', 'u', 'water'),
(8, 'preparation', 'water', 'suck', 'silver', 'a', 8, 9, 0, 1, 2, 3, 'h', 'q', 'o', 't', 'k', 'y'),
(9, 'y', 'f', 'e', 'a', 'dawn', 9, 0, 1, 2, 3, 4, 'peak', 'parking', 'b', 't', 'timber', 'c'),
(10, REPEAT('h',78), 'apologize', 'direct', 'u', 'frankly', 0, 1, 2, 3, 4, 5, 'h', 'exhibit', 'f', 'd', 'effective', 'c'),
(11, 'i', 'h', 'a', 'y', 'u', 1, 2, 3, 4, 5, 6, 'l', 'b', 'm', 'respond', 'ideological', 'credibility');
CREATE TABLE t2 (
pk int primary key,
f char(255) CHARACTER SET ucs2,
key(f)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,'against'),(2,'q');
SET SQL_MODE= '';
SET timestamp = 2;
SELECT * INTO OUTFILE 't1.data' FROM t1;
SET timestamp = 3;
UPDATE t1 SET f13 = 'q';
SET timestamp = 4;
LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
SELECT * INTO OUTFILE 't1.data.2' FROM t1;
SET timestamp = 5;
LOAD DATA INFILE 't1.data.2' REPLACE INTO TABLE t1;
SELECT * INTO OUTFILE 't2.data' FROM t2;
SET timestamp = 6;
LOAD DATA INFILE 't2.data' REPLACE INTO TABLE t2;
SET FOREIGN_KEY_CHECKS = OFF;
ALTER TABLE t1 ADD FOREIGN KEY (f13) REFERENCES t2 (f) ON DELETE SET NULL;
SET timestamp = 7;
LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
SET FOREIGN_KEY_CHECKS = ON;
SET SESSION SQL_MODE= 'NO_BACKSLASH_ESCAPES';
SET timestamp = 8;
LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
SET timestamp = 9;
REPLACE INTO t2 SELECT * FROM t2;
# Cleanup
DROP TABLE t1, t2;
set timestamp= default;
set time_zone='+00:00';
--let $datadir= `select @@datadir`
--remove_file $datadir/test/t1.data
--remove_file $datadir/test/t1.data.2
--remove_file $datadir/test/t2.data
--echo #
--echo # MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation
--echo #
create or replace table t1 (a int, key(a)) engine innodb with system versioning;
create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb;
insert into t1 values (1),(2);
insert into t2 values (1);
--echo # DELETE from referenced table is not allowed
--error ER_ROW_IS_REFERENCED_2
delete from t1 where a = 1;
drop tables t2, t1;
--echo #
--echo # MDEV-20812 Unexpected ER_ROW_IS_REFERENCED_2 or server crash in row_ins_foreign_report_err upon DELETE from versioned table with FK
--echo #
create or replace table t1 (x int primary key) engine innodb;
create or replace table t2 (x int, foreign key (x) references t1(x)) engine innodb with system versioning;
set foreign_key_checks= off;
insert into t2 values (1), (1);
set foreign_key_checks= on;
--echo # DELETE from foreign table is allowed
delete from t2;
drop tables t2, t1;
create or replace table t1 (a int, key(a)) engine innodb;
insert into t1 values (1);
create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb with system versioning;
insert into t2 values (1), (1);
--echo # DELETE from foreign table is allowed
delete from t2;
drop tables t2, t1;
--echo #
--echo # MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table
--echo #
create table t1 (pk int primary key, f1 int,f2 int, f3 text,
key(f1), fulltext(f3), key(f3(10)),
foreign key (f2) references t1 (f1) on delete set null
) engine=innodb with system versioning;
insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071));
delete from t1;
select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk;
# cleanup
drop table t1;
--echo # Shorter case for clustered index (MDEV-25004)
create table t1 (
y int primary key, r int, f int, key (r),
foreign key (f) references t1 (r) on delete set null)
with system versioning engine innodb;
insert into t1 values (1, 6, 6), (2, 6, 6);
delete from t1;
select *, check_row_ts(row_start, row_end) from t1 for system_time all;
drop tables t1;
--echo # Secondary unique index
create table t1 (
y int unique null, r int, f int, key (r),
foreign key (f) references t1 (r) on delete set null)
with system versioning engine innodb;
insert into t1 values (1, 6, 6), (2, 6, 6);
delete from t1;
select *, check_row_ts(row_start, row_end) from t1 for system_time all;
drop tables t1;
--echo # Non-unique index cannot be fixed because it does not trigger duplicate error
create table t1 (
y int, r int, f int, key (y), key (r),
foreign key (f) references t1 (r) on delete set null)
with system versioning engine innodb;
insert into t1 values (1, 6, 6), (2, 6, 6);
delete from t1;
select *, check_row_ts(row_start, row_end) from t1 for system_time all;
drop tables t1;
--echo #
--echo # MDEV-21555 Assertion secondary index is out of sync on delete from versioned table
--echo #
create table t1 (a int, b int as (a + 1) virtual, key(a)) engine=innodb with system versioning;
set foreign_key_checks= off;
insert into t1 (a) values (1), (2);
alter table t1 add foreign key (b) references t1 (a), algorithm=copy;
update t1 set a= null where a = 1;
delete from t1 where a is null;
set foreign_key_checks= on;
delete history from t1;
delete from t1;
# cleanup
drop table t1;
--echo #
--echo # MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT
--echo # constraint
--echo #
create table t0 (pk integer primary key) with system versioning engine=innodb;
create table t1 (pk integer primary key,
foreign key(pk) references t0(pk)
on delete restrict on update cascade) engine=innodb;
create table t2 (pk integer);
insert into t0 (pk) values (1);
insert into t1 (pk) values (1);
insert into t2 (pk) values (1);
--error ER_ROW_IS_REFERENCED_2
delete from t0;
--error ER_ROW_IS_REFERENCED_2
replace t0 values (1);
select * into outfile 'load_t0' from t0 ;
--error ER_ROW_IS_REFERENCED_2
load data infile 'load_t0' replace into table t0;
--error ER_ROW_IS_REFERENCED_2
delete t0, t2 from t0 join t2;
select pk from t0;
--echo # Cleanup
drop table t1, t0, t2;
--let $datadir= `select @@datadir`
--remove_file $datadir/test/load_t0
--echo # create_select for a temporary table didn't set up pos_in_locked_tables.
create table t (a int unique) engine=innodb
replace select 1 as a, 2 as b union select 1 as a, 3 as c;
select * from t;
drop table t;
create temporary table t (a int unique) engine=innodb
replace select 1 as a, 2 as b union select 1 as a, 3 as c;
select * from t;
drop table t;
--echo #
--echo # MDEV-20729 Fix REFERENCES constraint in column definition
--echo #
--replace_result $sys_datatype_expl SYS_DATATYPE
eval create or replace table t1(
id $sys_datatype_expl
);
--echo # system fields can't be foreign keys:
--replace_result $sys_datatype_expl SYS_DATATYPE
--error ER_PARSE_ERROR,ER_PARSE_ERROR
eval create or replace table t2(
x int,
sys_start $sys_datatype_expl as row start references t1(id),
sys_end $sys_datatype_expl as row end,
period for system_time(sys_start, sys_end)
) engine innodb with system versioning;
--replace_result $sys_datatype_expl SYS_DATATYPE
--error ER_PARSE_ERROR,ER_PARSE_ERROR
eval create or replace table t2(
x int,
sys_start $sys_datatype_expl as row start,
sys_end $sys_datatype_expl as row end references t1(id),
period for system_time(sys_start, sys_end)
) engine innodb with system versioning;
--replace_result $sys_datatype_expl SYS_DATATYPE
--error ER_FK_NO_INDEX_PARENT
eval create or replace table t2(
x int,
sys_start $sys_datatype_expl as row start,
sys_end $sys_datatype_expl as row end,
period for system_time(sys_start, sys_end),
foreign key (sys_start) references t1(id)
) engine innodb with system versioning;
--replace_result $sys_datatype_expl SYS_DATATYPE
--error ER_FK_NO_INDEX_PARENT
eval create or replace table t2(
x int,
sys_start $sys_datatype_expl as row start,
sys_end $sys_datatype_expl as row end,
period for system_time(sys_start, sys_end),
foreign key (sys_end) references t1(id)
) engine innodb with system versioning;
drop table t1;
--echo # End of 10.5 tests
--source suite/versioning/common_finish.inc
Reference in a new issue View git blame Copy permalink