1
Simple CREATE / DROP test
2
Testing simple CREATE SCHEMA
3
CREATE SCHEMA my_test_schema;
4
# check transaction_log
5
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG;
6
FILE_NAME FILE_LENGTH NUM_LOG_ENTRIES NUM_TRANSACTIONS MIN_TRANSACTION_ID MAX_TRANSACTION_ID MIN_END_TIMESTAMP MAX_END_TIMESTAMP INDEX_SIZE_IN_BYTES
7
transaction.log 121 1 1 1 1 START_TIMESTAMP END_TIMESTAMP INDEX_SIZE
9
Check transaction_log_entries
10
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
11
ENTRY_OFFSET ENTRY_TYPE ENTRY_LENGTH
14
Check transaction_log_transactions
15
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
16
ENTRY_OFFSET TRANSACTION_ID SERVER_ID START_TIMESTAMP END_TIMESTAMP NUM_STATEMENTS CHECKSUM
17
0 1 1 START_TIMESTAMP END_TIMESTAMP 1 0
18
Check transaction log contents
19
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
20
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
31
create_schema_statement {
33
name: "my_test_schema"
34
collation: "utf8_general_ci"
46
Testing simple DROP SCHEMA
47
DROP SCHEMA my_test_schema;
48
# check transaction_log
49
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG;
50
FILE_NAME FILE_LENGTH NUM_LOG_ENTRIES NUM_TRANSACTIONS MIN_TRANSACTION_ID MAX_TRANSACTION_ID MIN_END_TIMESTAMP MAX_END_TIMESTAMP INDEX_SIZE_IN_BYTES
51
transaction.log 197 2 2 1 2 START_TIMESTAMP END_TIMESTAMP INDEX_SIZE
53
Check transaction_log_entries
54
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
55
ENTRY_OFFSET ENTRY_TYPE ENTRY_LENGTH
59
Check transaction_log_transactions
60
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
61
ENTRY_OFFSET TRANSACTION_ID SERVER_ID START_TIMESTAMP END_TIMESTAMP NUM_STATEMENTS CHECKSUM
62
0 1 1 START_TIMESTAMP END_TIMESTAMP 1 0
63
121 2 1 START_TIMESTAMP END_TIMESTAMP 1 0
64
Check transaction log contents
65
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
66
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
77
drop_schema_statement {
78
schema_name: "my_test_schema"
84
SET GLOBAL transaction_log_truncate_debug= true;
86
Testing DROP SCHEMA on non-empty schema
87
CREATE SCHEMA my_test_schema;
88
CREATE TABLE my_test_schema.t1 (a int not null, primary key(a));
89
CREATE TABLE my_test_schema.t2 LIKE my_test_schema.t1;
90
CREATE TABLE my_test_schema.t3 LIKE my_test_schema.t2;
91
We truncate the log to simplify test validation
92
we are mainly concerned that we see 4 new entries
93
once we have issued the DROP SCHEMA statement
94
SET GLOBAL transaction_log_truncate_debug= true;
96
DROP SCHEMA my_test_schema;
97
The implied DROP TABLE statements for t1->t3
98
do not come in any deterministic order t1 may or may not be first
99
,therefore we have to satisfy ourselves that we have 4 items in the log
100
after issuing the DROP SCHEMA
101
We do check the first entry (OFFSET=0) as this should be the first of 3 DROP TABLE messages
102
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',0);
103
PRINT_TRANSACTION_MESSAGE('transaction.log',0)
104
transaction_context {
114
drop_table_statement {
116
schema_name: "my_test_schema"
117
table_name: TABLE_NAME
119
if_exists_clause: true
123
# check transaction_log
124
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG;
125
FILE_NAME FILE_LENGTH NUM_LOG_ENTRIES NUM_TRANSACTIONS MIN_TRANSACTION_ID MAX_TRANSACTION_ID MIN_END_TIMESTAMP MAX_END_TIMESTAMP INDEX_SIZE_IN_BYTES
126
transaction.log 331 4 4 1 4 START_TIMESTAMP END_TIMESTAMP INDEX_SIZE
128
Check transaction_log_entries
129
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
130
ENTRY_OFFSET ENTRY_TYPE ENTRY_LENGTH
136
Check transaction_log_transactions
137
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
138
ENTRY_OFFSET TRANSACTION_ID SERVER_ID START_TIMESTAMP END_TIMESTAMP NUM_STATEMENTS CHECKSUM
139
0 1 1 START_TIMESTAMP END_TIMESTAMP 1 0
140
85 2 1 START_TIMESTAMP END_TIMESTAMP 1 0
141
170 3 1 START_TIMESTAMP END_TIMESTAMP 1 0
142
255 4 1 START_TIMESTAMP END_TIMESTAMP 1 0
143
Check transaction log contents
144
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
145
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
146
transaction_context {
156
drop_schema_statement {
157
schema_name: "my_test_schema"
163
SET GLOBAL transaction_log_truncate_debug= true;
165
Testing simple CREATE SCHEMA
166
CREATE SCHEMA my_test_schema;
168
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b CHAR(50), PRIMARY KEY(a));
169
ALTER SCHEMA my_test_schema COLLATE utf8_turkish_ci;
170
SHOW CREATE TABLE t1;
172
t1 CREATE TABLE `t1` (
173
`a` INT NOT NULL AUTO_INCREMENT,
174
`b` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL,
176
) ENGINE=InnoDB COLLATE = utf8_general_ci
177
# check transaction_log
178
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG;
179
FILE_NAME FILE_LENGTH NUM_LOG_ENTRIES NUM_TRANSACTIONS MIN_TRANSACTION_ID MAX_TRANSACTION_ID MIN_END_TIMESTAMP MAX_END_TIMESTAMP INDEX_SIZE_IN_BYTES
180
transaction.log 440 3 3 1 3 START_TIMESTAMP END_TIMESTAMP INDEX_SIZE
182
Check transaction_log_entries
183
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
184
ENTRY_OFFSET ENTRY_TYPE ENTRY_LENGTH
189
Check transaction_log_transactions
190
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
191
ENTRY_OFFSET TRANSACTION_ID SERVER_ID START_TIMESTAMP END_TIMESTAMP NUM_STATEMENTS CHECKSUM
192
0 1 1 START_TIMESTAMP END_TIMESTAMP 1 0
193
121 2 1 START_TIMESTAMP END_TIMESTAMP 1 0
194
329 3 1 START_TIMESTAMP END_TIMESTAMP 1 0
195
Check transaction log contents
196
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
197
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
198
transaction_context {
208
sql: "ALTER SCHEMA my_test_schema COLLATE utf8_turkish_ci"
213
CREATE TABLE t2 LIKE t1;
214
# check transaction_log
215
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG;
216
FILE_NAME FILE_LENGTH NUM_LOG_ENTRIES NUM_TRANSACTIONS MIN_TRANSACTION_ID MAX_TRANSACTION_ID MIN_END_TIMESTAMP MAX_END_TIMESTAMP INDEX_SIZE_IN_BYTES
217
transaction.log 650 4 4 1 4 START_TIMESTAMP END_TIMESTAMP INDEX_SIZE
219
Check transaction_log_entries
220
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
221
ENTRY_OFFSET ENTRY_TYPE ENTRY_LENGTH
227
Check transaction_log_transactions
228
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
229
ENTRY_OFFSET TRANSACTION_ID SERVER_ID START_TIMESTAMP END_TIMESTAMP NUM_STATEMENTS CHECKSUM
230
0 1 1 START_TIMESTAMP END_TIMESTAMP 1 0
231
121 2 1 START_TIMESTAMP END_TIMESTAMP 1 0
232
329 3 1 START_TIMESTAMP END_TIMESTAMP 1 0
233
440 4 1 START_TIMESTAMP END_TIMESTAMP 1 0
234
Check transaction log contents
235
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
236
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
237
transaction_context {
247
create_table_statement {
260
is_autoincrement: true
272
collation: "utf8_general_ci"
289
schema: "my_test_schema"
291
has_user_set_auto_increment_value: false
292
collation: "utf8_general_ci"
304
DROP SCHEMA my_test_schema;
305
SET GLOBAL transaction_log_truncate_debug= true;