Debian Codegraph @

mysql-test/suite/innodb/r/portability_basic.result

raw
1 ## create tables and tablespaces without absolute paths
2 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
3 CREATE TABLE tab1(c1 int, c2 varchar(10)) TABLESPACE=ts1;
4 INSERT INTO tab1 VALUES(1, 'VISH');
5 CREATE TABLE tab2(c1 int, c2 varchar(10)) Engine=InnoDB;
6 INSERT INTO tab2 VALUES(2, 'VISH');
7 CREATE INDEX ix1 ON tab1(c2) USING BTREE;
8 CREATE INDEX ix1 ON tab2(c2) ;
9 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
10 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
11 SELECT file_name,file_type,tablespace_name FROM
12 INFORMATION_SCHEMA.FILES
13 WHERE FILE_TYPE LIKE '%undo%';
14 FILE_NAME FILE_TYPE TABLESPACE_NAME
15 ./undo_001 UNDO LOG innodb_undo_001
16 ./undo_002 UNDO LOG innodb_undo_002
17 ./undo_003.ibu UNDO LOG undo_003
18 ./undo_004.ibu UNDO LOG undo_004
19 ## Stop DB server
20 # Copy whole --datadir files into new location
21 ## Start with new --datadir
22 ## Check datadir started with new one
23 SELECT @@datadir;
24 @@datadir
25 NEW_DATADIR/
26 ## Check with SHOW
27 SHOW CREATE TABLE tab1;
28 Table Create Table
29 tab1 CREATE TABLE `tab1` (
30 `c1` int DEFAULT NULL,
31 `c2` varchar(10) DEFAULT NULL,
32 KEY `ix1` (`c2`) USING BTREE
33 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
34 SHOW CREATE TABLE tab2;
35 Table Create Table
36 tab2 CREATE TABLE `tab2` (
37 `c1` int DEFAULT NULL,
38 `c2` varchar(10) DEFAULT NULL,
39 KEY `ix1` (`c2`)
40 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
41 ## Check with DML and DDL
42 SELECT * FROM tab1;
43 c1 c2
44 1 VISH
45 SELECT * FROM tab2;
46 c1 c2
47 2 VISH
48 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
49 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
50 SELECT name,space_type,state
51 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
52 WHERE space_type = 'Undo';
53 name space_type state
54 innodb_undo_001 Undo empty
55 innodb_undo_002 Undo empty
56 undo_003 Undo active
57 undo_004 Undo active
58 DELETE FROM tab1;
59 DELETE FROM tab2;
60 ALTER TABLE tab1 add column c3 varchar(10);
61 INSERT INTO tab1 VALUES(1, 'VISH','NATH');
62 INSERT INTO tab2 VALUES(2, 'VISH');
63 SELECT * FROM tab1;
64 c1 c2 c3
65 1 VISH NATH
66 SELECT * FROM tab2;
67 c1 c2
68 2 VISH
69 SELECT name,space_type,state
70 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
71 WHERE space_type = 'Undo';
72 name space_type state
73 innodb_undo_001 Undo empty
74 innodb_undo_002 Undo empty
75 undo_003 Undo active
76 undo_004 Undo active
77 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
78 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
79 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
80 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
81 SELECT name,space_type,state
82 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
83 WHERE space_type = 'Undo';
84 name space_type state
85 innodb_undo_001 Undo active
86 innodb_undo_002 Undo active
87 undo_003 Undo empty
88 undo_004 Undo empty
89 # Clean up new datadir
90 DROP TABLE tab1;
91 DROP TABLE tab2;
92 DROP TABLESPACE ts1;
93 DROP UNDO TABLESPACE undo_003;
94 DROP UNDO TABLESPACE undo_004;
95 # Stop DB server with new datadir
96 # Re-start with old --datadir
97 # restart: --datadir=OLD_DATADIR
98 # Check --datadir started with old
99 SELECT @@datadir;
100 @@datadir
101 OLD_DATADIR
102 DROP TABLE tab1;
103 DROP TABLE tab2;
104 DROP TABLESPACE ts1;
105 SELECT name,space_type,state
106 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
107 WHERE space_type = 'Undo';
108 name space_type state
109 innodb_undo_001 Undo active
110 innodb_undo_002 Undo active
111 undo_003 Undo active
112 undo_004 Undo active
113 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
114 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
115 SELECT name,space_type,state
116 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
117 WHERE space_type = 'Undo';
118 name space_type state
119 innodb_undo_001 Undo active
120 innodb_undo_002 Undo active
121 undo_003 Undo empty
122 undo_004 Undo empty
123 DROP UNDO TABLESPACE undo_003;
124 DROP UNDO TABLESPACE undo_004;