1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
SET GLOBAL innodb_file_per_table=1;
CREATE TABLE tr(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
CREATE TABLE tc(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=COMPACT;
CREATE TABLE td(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
CREATE TABLE tz(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=1;
CREATE TABLE tp(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC
PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=9;
SYS_TABLES clustered index root page (8):
N_RECS=9; LEVEL=0; INDEX_ID=0x0000000000000001
header=0x01000003016e (NAME=0x696e66696d756d00)
header=0x00002815008d (NAME='SYS_DATAFILES',
DB_TRX_ID=0x000000000301,
DB_ROLL_PTR=0x81000001320194,
ID=0x000000000000000e,
N_COLS=0x00000002,
TYPE=0x00000001,
MIX_ID=0x0000000000000000,
MIX_LEN=0x00000040,
CLUSTER_NAME=NULL(0 bytes),
SPACE=0x00000000)
header=0x0000101500d5 (NAME='SYS_FOREIGN',
DB_TRX_ID=0x000000000300,
DB_ROLL_PTR=0x800000012d0110,
ID=0x000000000000000b,
N_COLS=0x00000004,
TYPE=0x00000001,
MIX_ID=0x0000000000000000,
MIX_LEN=0x00000040,
CLUSTER_NAME=NULL(0 bytes),
SPACE=0x00000000)
header=0x000018150122 (NAME='SYS_FOREIGN_COLS',
DB_TRX_ID=0x000000000300,
DB_ROLL_PTR=0x800000012d0201,
ID=0x000000000000000c,
N_COLS=0x00000004,
TYPE=0x00000001,
MIX_ID=0x0000000000000000,
MIX_LEN=0x00000040,
CLUSTER_NAME=NULL(0 bytes),
SPACE=0x00000000)
header=0x0400201501fc (NAME='SYS_TABLESPACES',
DB_TRX_ID=0x000000000301,
DB_ROLL_PTR=0x81000001320110,
ID=0x000000000000000d,
N_COLS=0x00000003,
TYPE=0x00000001,
MIX_ID=0x0000000000000000,
MIX_LEN=0x00000040,
CLUSTER_NAME=NULL(0 bytes),
SPACE=0x00000000)
header=0x000038150240 (NAME='test/tc',
DB_TRX_ID=0x000000000303,
DB_ROLL_PTR=0x83000001360110,
ID=0x0000000000000010,
N_COLS=0x80000001,
TYPE=0x00000001,
MIX_ID=0x0000000000000000,
MIX_LEN=0x00000050,
CLUSTER_NAME=NULL(0 bytes),
SPACE=0x00000002)
header=0x0000401502c8 (NAME='test/td',
DB_TRX_ID=0x000000000304,
DB_ROLL_PTR=0x84000001370110,
ID=0x0000000000000011,
N_COLS=0x80000001,
TYPE=0x00000021,
MIX_ID=0x0000000000000000,
MIX_LEN=0x00000050,
CLUSTER_NAME=NULL(0 bytes),
SPACE=0x00000003)
header=0x0000501501b8 (NAME='test/tp',
DB_TRX_ID=0x000000000306,
DB_ROLL_PTR=0x86000001390110,
ID=0x0000000000000013,
N_COLS=0x80000001,
TYPE=0x000009a1,
MIX_ID=0x0000000000000000,
MIX_LEN=0x00000050,
CLUSTER_NAME=NULL(0 bytes),
SPACE=0x00000005)
header=0x000030150284 (NAME='test/tr',
DB_TRX_ID=0x000000000302,
DB_ROLL_PTR=0x82000001350110,
ID=0x000000000000000f,
N_COLS=0x00000001,
TYPE=0x00000001,
MIX_ID=0x0000000000000000,
MIX_LEN=0x00000050,
CLUSTER_NAME=NULL(0 bytes),
SPACE=0x00000001)
header=0x000048150074 (NAME='test/tz',
DB_TRX_ID=0x000000000305,
DB_ROLL_PTR=0x85000001380110,
ID=0x0000000000000012,
N_COLS=0x80000001,
TYPE=0x00000023,
MIX_ID=0x0000000000000000,
MIX_LEN=0x00000050,
CLUSTER_NAME=NULL(0 bytes),
SPACE=0x00000004)
header=0x060008030000 (NAME=0x73757072656d756d00)
SHOW CREATE TABLE tr;
Table Create Table
tr CREATE TABLE `tr` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
INSERT INTO tr VALUES(42);
SHOW CREATE TABLE tc;
ERROR 42S02: Table 'test.tc' doesn't exist in engine
SHOW CREATE TABLE td;
ERROR 42S02: Table 'test.td' doesn't exist in engine
SHOW CREATE TABLE tz;
ERROR 42S02: Table 'test.tz' doesn't exist in engine
SHOW CREATE TABLE tp;
ERROR 42S02: Table 'test.tp' doesn't exist in engine
FOUND /InnoDB: Table '.test.\..t[cdzp].' in InnoDB data dictionary has unknown type (81|f21|121|302[13])\b/ in mysqld.1.err
FOUND /InnoDB: Cannot open table test/t[cdzp] from the internal data dictionary of InnoDB/ in mysqld.1.err
Restoring SYS_TABLES clustered index root page (8)
SHOW CREATE TABLE tr;
Table Create Table
tr CREATE TABLE `tr` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
SHOW CREATE TABLE tc;
Table Create Table
tc CREATE TABLE `tc` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
SHOW CREATE TABLE td;
Table Create Table
td CREATE TABLE `td` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
SHOW CREATE TABLE tz;
Table Create Table
tz CREATE TABLE `tz` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1
SHOW CREATE TABLE tp;
Table Create Table
tp CREATE TABLE `tp` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC `PAGE_COMPRESSED`=1 `PAGE_COMPRESSION_LEVEL`=9
BEGIN;
INSERT INTO tr VALUES(1);
INSERT INTO tc VALUES(1);
INSERT INTO td VALUES(1);
INSERT INTO tz VALUES(1);
INSERT INTO tp VALUES(1);
ROLLBACK;
SELECT * FROM tr;
a
42
SELECT * FROM tc;
a
SELECT * FROM td;
a
SELECT * FROM tz;
a
SELECT * FROM tp;
a
DROP TABLE tr,tc,td,tz,tp;
ib_logfile0
ib_logfile1
ibdata1
sys_tables.bin
|