| 1 |
--source include/have_ndb.inc |
| 2 |
--source suite/ndb/include/backup_restore_setup.inc |
| 3 |
|
| 4 |
# |
| 5 |
# Basic testing for the metadata check. This test covers the |
| 6 |
# metadata check in the following scenarios: |
| 7 |
# |
| 8 |
# 1. CREATE TABLE |
| 9 |
# 2. ALTER TABLE (COPY) |
| 10 |
# 3. ALTER TABLE (INPLACE) |
| 11 |
# 4. TRUNCATE TABLE |
| 12 |
# 5. Table discovery |
| 13 |
# |
| 14 |
# Additional coverage is provided by pretty much every NDB test |
| 15 |
# |
| 16 |
|
| 17 |
# Table with various data types |
| 18 |
CREATE TABLE t1 ( |
| 19 |
c1 INT PRIMARY KEY AUTO_INCREMENT, |
| 20 |
c2 INT UNSIGNED NOT NULL DEFAULT 0, |
| 21 |
c3 FLOAT DEFAULT 1.5, |
| 22 |
c4 VARCHAR(255) NOT NULL DEFAULT '', |
| 23 |
c5 TIME, |
| 24 |
c6 DATE, |
| 25 |
c7 DATETIME, |
| 26 |
c8 BLOB, |
| 27 |
c9 CHAR(30) DEFAULT 'a', |
| 28 |
c10 VARBINARY(255), |
| 29 |
c11 DECIMAL(5,2), |
| 30 |
c12 DATETIME(6), |
| 31 |
c13 TIMESTAMP(2), |
| 32 |
c14 TIMESTAMP, |
| 33 |
c15 YEAR, |
| 34 |
c16 INT NOT NULL, |
| 35 |
UNIQUE KEY xc2(c2), |
| 36 |
UNIQUE KEY uk(c16) USING HASH, |
| 37 |
INDEX idx(c16) |
| 38 |
) ENGINE NDB; |
| 39 |
|
| 40 |
ALTER TABLE t1 DROP INDEX uk; |
| 41 |
|
| 42 |
INSERT INTO t1 VALUES(1,1,2.5,'a','23:59:59','2018-01-30','2018-01-30 13:46:00','b','c','d',78.3,'2020-06-01 16:36:04.297','2020-06-01 16:36:04.297','2020-06-01 16:36:04','1918',35); |
| 43 |
|
| 44 |
# Table with a virtual generated column |
| 45 |
CREATE TABLE t2 ( |
| 46 |
c1 INT PRIMARY KEY, |
| 47 |
c2 INT GENERATED ALWAYS AS (c1*c1) VIRTUAL |
| 48 |
) ENGINE NDB; |
| 49 |
|
| 50 |
INSERT INTO t2(c1) VALUES(2); |
| 51 |
|
| 52 |
CREATE LOGFILE GROUP lg1 |
| 53 |
ADD UNDOFILE 'undofile.dat' |
| 54 |
INITIAL_SIZE 1M |
| 55 |
UNDO_BUFFER_SIZE = 1M |
| 56 |
ENGINE=NDB; |
| 57 |
|
| 58 |
CREATE TABLESPACE ts1 |
| 59 |
ADD DATAFILE 'datafile.dat' |
| 60 |
USE LOGFILE GROUP lg1 |
| 61 |
INITIAL_SIZE 8M |
| 62 |
EXTENT_SIZE 256k |
| 63 |
ENGINE NDB; |
| 64 |
|
| 65 |
# Table with explicit column with disk storage |
| 66 |
CREATE TABLE t3 ( |
| 67 |
a INT STORAGE DISK, |
| 68 |
b INT COLUMN_FORMAT DYNAMIC, |
| 69 |
c BIT(8) NOT NULL |
| 70 |
) TABLESPACE ts1 ENGINE NDB; |
| 71 |
|
| 72 |
INSERT INTO t3 VALUES (10000,325,b'1'); |
| 73 |
|
| 74 |
TRUNCATE TABLE t3; |
| 75 |
|
| 76 |
# Some more CREATE statements |
| 77 |
CREATE TABLE t4 ( |
| 78 |
a INT PRIMARY KEY |
| 79 |
) STORAGE DISK TABLESPACE ts1 ENGINE NDB; |
| 80 |
|
| 81 |
INSERT INTO t4 VALUES (10001); |
| 82 |
|
| 83 |
CREATE TABLE t5 ( |
| 84 |
a INT PRIMARY KEY |
| 85 |
) ENGINE NDB; |
| 86 |
ALTER TABLE t5 STORAGE DISK TABLESPACE ts1; |
| 87 |
|
| 88 |
INSERT INTO t5 VALUES (15980); |
| 89 |
|
| 90 |
CREATE TABLE t6 ( |
| 91 |
a INT, |
| 92 |
b INT COLUMN_FORMAT FIXED, |
| 93 |
PRIMARY KEY pk USING HASH(a) |
| 94 |
) ENGINE NDB; |
| 95 |
|
| 96 |
CREATE TABLE t7 ( |
| 97 |
a INT PRIMARY KEY, |
| 98 |
b BIT(3) DEFAULT b'111' |
| 99 |
) ENGINE NDB; |
| 100 |
|
| 101 |
CREATE TABLE t8 ( |
| 102 |
a INT PRIMARY KEY |
| 103 |
) ENGINE NDB; |
| 104 |
|
| 105 |
ALTER TABLE t8 ALGORITHM=INPLACE, |
| 106 |
ADD COLUMN b INT COLUMN_FORMAT DYNAMIC; |
| 107 |
|
| 108 |
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8; |
| 109 |
|
| 110 |
ALTER TABLESPACE ts1 |
| 111 |
DROP DATAFILE 'datafile.dat'; |
| 112 |
|
| 113 |
DROP TABLESPACE ts1; |
| 114 |
|
| 115 |
DROP LOGFILE GROUP lg1 |
| 116 |
ENGINE NDB; |
| 117 |
|
| 118 |
# Foreign key related checks |
| 119 |
CREATE TABLE parent ( |
| 120 |
col1 INT PRIMARY KEY, |
| 121 |
col2 INT, |
| 122 |
UNIQUE p_ukc2(col2) |
| 123 |
) ENGINE NDB; |
| 124 |
|
| 125 |
CREATE TABLE child ( |
| 126 |
c1 INT, |
| 127 |
c2 INT, |
| 128 |
INDEX c_xc1 (c1), |
| 129 |
INDEX c_xc2 (c2), |
| 130 |
CONSTRAINT c_fk1 |
| 131 |
FOREIGN KEY (c1) |
| 132 |
REFERENCES parent(col1), |
| 133 |
CONSTRAINT c_fk2 |
| 134 |
FOREIGN KEY (c2) |
| 135 |
REFERENCES parent(col2) |
| 136 |
) ENGINE NDB; |
| 137 |
|
| 138 |
INSERT INTO parent VALUES (1,1); |
| 139 |
|
| 140 |
DROP TABLE child,parent; |
| 141 |
|
| 142 |
# Self referential foreign key |
| 143 |
CREATE TABLE parent ( |
| 144 |
pk INT PRIMARY KEY, |
| 145 |
id1 INT UNIQUE, |
| 146 |
id2 INT UNIQUE, |
| 147 |
id3 INT UNIQUE, |
| 148 |
ref1 INT, |
| 149 |
ref2 INT, |
| 150 |
ref3 INT |
| 151 |
) ENGINE NDB; |
| 152 |
|
| 153 |
CREATE TABLE child ( |
| 154 |
pk INT PRIMARY KEY, |
| 155 |
ref1 INT, |
| 156 |
ref2 INT |
| 157 |
) ENGINE NDB; |
| 158 |
|
| 159 |
ALTER TABLE parent |
| 160 |
ADD CONSTRAINT fk_p1 FOREIGN KEY ref1_idx(ref1) REFERENCES parent(id1), |
| 161 |
ALGORITHM = COPY; |
| 162 |
|
| 163 |
DROP TABLE child,parent; |
| 164 |
|
| 165 |
# Table discovery |
| 166 |
CREATE TABLE t1 ( |
| 167 |
c1 INT PRIMARY KEY, |
| 168 |
c2 INT |
| 169 |
) ENGINE NDB; |
| 170 |
|
| 171 |
--source include/ndb_backup.inc |
| 172 |
|
| 173 |
DROP TABLE t1; |
| 174 |
|
| 175 |
--exec $NDB_RESTORE -b $the_backup_id -n 1 -m $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT |
| 176 |
|
| 177 |
# Discover the table |
| 178 |
SHOW CREATE TABLE t1; |
| 179 |
|
| 180 |
DROP TABLE t1; |
| 181 |
--source suite/ndb/include/backup_restore_cleanup.inc |
| 182 |
--remove_file $NDB_TOOLS_OUTPUT |