2
DROP TABLE IF EXISTS t1;
3
CREATE TABLE t1(a INT NOT NULL, PRIMARY KEY(a));
4
Check transaction_log_entries
5
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
9
Check transaction_log_transactions
10
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
13
Check transaction log contents
14
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
15
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
26
create_table_statement {
55
has_user_set_auto_increment_value: false
56
collation: "utf8_general_ci"
74
SET GLOBAL transaction_log_truncate_debug= true;
76
Testing multi-column CREATE
77
DROP TABLE IF EXISTS t1;
78
CREATE TABLE t1(a INT NOT NULL, b CHAR, c BLOB, d TEXT, e DATE NOT NULL, f BLOB NOT NULL, PRIMARY KEY(a));
79
Check transaction_log_entries
80
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
84
Check transaction_log_transactions
85
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
88
Check transaction log contents
89
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
90
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
101
create_table_statement {
123
collation: "utf8_general_ci"
145
collation: "utf8_general_ci"
182
has_user_set_auto_increment_value: false
183
collation: "utf8_general_ci"
201
SET GLOBAL transaction_log_truncate_debug= true;
203
Testing CREATE with DEFAULT VALUES
204
DROP TABLE IF EXISTS t1;
205
CREATE TABLE t1(a INT NOT NULL, b CHAR(25) NOT NULL DEFAULT 'this_is_a_default_value', PRIMARY KEY(a));
206
Check transaction_log_entries
207
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
211
Check transaction_log_transactions
212
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
215
Check transaction log contents
216
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
217
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
218
transaction_context {
228
create_table_statement {
245
default_value: "this_is_a_default_value"
253
collation: "utf8_general_ci"
272
has_user_set_auto_increment_value: false
273
collation: "utf8_general_ci"
291
SET GLOBAL transaction_log_truncate_debug= true;
293
Testing CREATE with multi-part key
294
DROP TABLE IF EXISTS t1;
295
CREATE TABLE t1(a INT NOT NULL, b CHAR(25) NOT NULL DEFAULT 'this_is_a_default_value', c DATE, d TIMESTAMP, PRIMARY KEY(a,b,d));
296
Check transaction_log_entries
297
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
301
Check transaction_log_transactions
302
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
305
Check transaction log contents
306
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
307
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
308
transaction_context {
318
create_table_statement {
335
default_value: "this_is_a_default_value"
343
collation: "utf8_general_ci"
379
binary_pack_key: true
386
has_user_set_auto_increment_value: false
387
collation: "utf8_general_ci"
405
SET GLOBAL transaction_log_truncate_debug= true;
407
Testing CREATE with partial key
408
DROP TABLE IF EXISTS t1;
409
CREATE TABLE t1(a INT NOT NULL, b CHAR(25) NOT NULL DEFAULT 'this_is_a_default_value', c DATE, d TIMESTAMP, PRIMARY KEY(b(5)));
410
Check transaction_log_entries
411
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
415
Check transaction_log_transactions
416
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
419
Check transaction log contents
420
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
421
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
422
transaction_context {
432
create_table_statement {
449
default_value: "this_is_a_default_value"
457
collation: "utf8_general_ci"
485
binary_pack_key: true
487
has_partial_segments: true
493
has_user_set_auto_increment_value: false
494
collation: "utf8_general_ci"
512
SET GLOBAL transaction_log_truncate_debug= true;
514
Testing CREATE with multiple keys
515
DROP TABLE IF EXISTS t1;
516
CREATE TABLE t1(a INT NOT NULL, b CHAR(25) NOT NULL DEFAULT 'this_is_a_default_value', c DATE, d TIMESTAMP, PRIMARY KEY(b(5)), KEY composite_key(a,b(10),c));
517
Check transaction_log_entries
518
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
522
Check transaction_log_transactions
523
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
526
Check transaction log contents
527
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
528
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
529
transaction_context {
539
create_table_statement {
556
default_value: "this_is_a_default_value"
564
collation: "utf8_general_ci"
592
binary_pack_key: true
594
has_partial_segments: true
598
name: "composite_key"
616
binary_pack_key: true
619
has_partial_segments: true
625
has_user_set_auto_increment_value: false
626
collation: "utf8_general_ci"
644
SET GLOBAL transaction_log_truncate_debug= true;
646
Testing CREATE with UNIQUE key
647
DROP TABLE IF EXISTS t1;
648
CREATE TABLE t1(a INT NOT NULL, b CHAR(25) NOT NULL DEFAULT 'this_is_a_default_value', c DATE, d TIMESTAMP, PRIMARY KEY(a), UNIQUE KEY unique_key(a,b(20),c));
649
Check transaction_log_entries
650
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
654
Check transaction_log_transactions
655
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
658
Check transaction log contents
659
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
660
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
661
transaction_context {
671
create_table_statement {
688
default_value: "this_is_a_default_value"
696
collation: "utf8_general_ci"
745
binary_pack_key: true
748
has_partial_segments: true
754
has_user_set_auto_increment_value: false
755
collation: "utf8_general_ci"
773
SET GLOBAL transaction_log_truncate_debug= true;
775
Testing CREATE IF NOT EXISTS
776
DROP TABLE IF EXISTS t1;
777
positive test (should pass)
778
CREATE TABLE IF NOT EXISTS t1(a INT NOT NULL, PRIMARY KEY(a));
779
Check transaction_log_entries
780
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
784
Check transaction_log_transactions
785
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
788
Check transaction log contents
789
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
790
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
791
transaction_context {
801
create_table_statement {
830
has_user_set_auto_increment_value: false
831
collation: "utf8_general_ci"
848
negative test (should not be created / logged)
849
CREATE TABLE IF NOT EXISTS t1(b CHAR(100) NOT NULL, PRIMARY KEY(b));
851
Note 1050 Table 't1' already exists
852
Check transaction_log_entries
853
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
857
Check transaction_log_transactions
858
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
861
Check transaction log contents
862
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
863
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
864
transaction_context {
874
create_table_statement {
903
has_user_set_auto_increment_value: false
904
collation: "utf8_general_ci"
922
SET GLOBAL transaction_log_truncate_debug= true;
924
Testing Foreign Key constraints
925
DROP TABLE IF EXISTS t1, t2;
926
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
927
Check transaction_log_entries
928
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
932
Check transaction_log_transactions
933
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
936
Check transaction log contents
937
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
938
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
939
transaction_context {
949
create_table_statement {
998
has_user_set_auto_increment_value: false
999
collation: "utf8_general_ci"
1016
CREATE TABLE t2(a INT NOT NULL, b INT , PRIMARY KEY(a), KEY b_key (b),
1017
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE SET NULL ON UPDATE CASCADE);
1018
Check transaction_log_entries
1019
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
1023
Check transaction_log_transactions
1024
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
1027
Check transaction log contents
1028
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
1029
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
1030
transaction_context {
1040
create_table_statement {
1090
name: "fk_constraint_t2"
1092
references_table_name: "t1"
1093
references_columns: "b"
1094
match: MATCH_UNDEFINED
1095
update_option: OPTION_CASCADE
1096
delete_option: OPTION_SET_NULL
1099
has_user_set_auto_increment_value: false
1100
collation: "utf8_general_ci"
1119
SET GLOBAL transaction_log_truncate_debug= true;
1121
Testing CREATE TABLE...LIKE
1122
DROP TABLE IF EXISTS t1, t2;
1123
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
1124
Check transaction_log_entries
1125
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
1129
Check transaction_log_transactions
1130
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
1133
Check transaction log contents
1134
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
1135
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
1136
transaction_context {
1146
create_table_statement {
1195
has_user_set_auto_increment_value: false
1196
collation: "utf8_general_ci"
1213
CREATE TABLE t2 LIKE t1;
1214
Check transaction_log_entries
1215
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
1219
Check transaction_log_transactions
1220
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
1223
Check transaction log contents
1224
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
1225
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
1226
transaction_context {
1236
create_table_statement {
1285
has_user_set_auto_increment_value: false
1286
collation: "utf8_general_ci"
1305
SET GLOBAL transaction_log_truncate_debug= true;
1307
Testing collations - column
1308
DROP TABLE IF EXISTS t1, t2;
1309
CREATE TABLE t1(a INT NOT NULL, b CHAR(1000) NOT NULL COLLATE utf8_turkish_ci, c CHAR(10), PRIMARY KEY(a), KEY b_key1 (b));
1311
Warning 1071 Specified key was too long; max key length is 1023 bytes
1312
Check transaction_log_entries
1313
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
1317
Check transaction_log_transactions
1318
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
1321
Check transaction log contents
1322
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
1323
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
1324
transaction_context {
1334
create_table_statement {
1356
collation: "utf8_turkish_ci"
1368
collation: "utf8_general_ci"
1395
binary_pack_key: true
1396
var_length_key: true
1397
has_partial_segments: true
1403
has_user_set_auto_increment_value: false
1404
collation: "utf8_general_ci"
1422
SET GLOBAL transaction_log_truncate_debug= true;
1424
Testing collations - table
1425
DROP TABLE IF EXISTS t1, t2;
1426
CREATE TABLE t1(a INT NOT NULL, b CHAR(1000) NOT NULL , c CHAR(1) COLLATE utf8_bin, PRIMARY KEY(a)) COLLATE utf8_turkish_ci;
1427
Check transaction_log_entries
1428
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
1432
Check transaction_log_transactions
1433
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
1436
Check transaction log contents
1437
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
1438
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
1439
transaction_context {
1449
create_table_statement {
1471
collation: "utf8_turkish_ci"
1483
collation: "utf8_bin"
1502
has_user_set_auto_increment_value: false
1503
collation: "utf8_turkish_ci"
1521
SET GLOBAL transaction_log_truncate_debug= true;
1523
Testing charset - column
1524
DROP TABLE IF EXISTS t1, t2;
1525
CREATE TABLE t1(a INT NOT NULL, b BLOB NOT NULL, c CHAR(1) DEFAULT '0', PRIMARY KEY(a));
1526
Check transaction_log_entries
1527
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
1531
Check transaction_log_transactions
1532
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
1535
Check transaction log contents
1536
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
1537
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
1538
transaction_context {
1548
create_table_statement {
1581
collation: "utf8_general_ci"
1600
has_user_set_auto_increment_value: false
1601
collation: "utf8_general_ci"
1619
SET GLOBAL transaction_log_truncate_debug= true;
1621
Testing CREATE data-type / fields
1622
DROP TABLE IF EXISTS t1;
1624
autoinc_int_field INT NOT NULL AUTO_INCREMENT
1625
, null_int_field INT NULL
1626
, not_null_bigint_field BIGINT NOT NULL
1627
, null_bigint_field BIGINT NULL
1628
, not_null_int_field INT NOT NULL
1629
, null_varchar_field VARCHAR(100) NULL
1630
, not_null_varchar_field VARCHAR(100) NOT NULL
1631
, null_enum_field ENUM ('val1', 'val2') NULL
1632
, not_null_enum_field ENUM ('val1', 'val2') NOT NULL
1633
, null_date_field DATE NULL
1634
, not_null_date_field DATE NOT NULL
1635
, null_datetime_field DATETIME NULL
1636
, not_null_datetime_field DATETIME NOT NULL
1637
, null_blob_field BLOB NULL
1638
, not_null_blob_field BLOB NOT NULL
1639
, null_text_field TEXT NULL
1640
, not_null_text_field TEXT NOT NULL
1641
, null_timestamp_field TIMESTAMP NULL
1642
, not_null_timestamp_field TIMESTAMP NOT NULL
1643
, null_double_field DOUBLE NULL
1644
, not_null_double_field DOUBLE NOT NULL
1645
, null_decimal_field DECIMAL(10,2) NULL
1646
, not_null_decimal_field DECIMAL(10,2) NOT NULL
1647
, PRIMARY KEY (autoinc_int_field)
1649
Check transaction_log_entries
1650
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
1654
Check transaction_log_transactions
1655
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
1658
Check transaction log contents
1659
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
1660
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
1661
transaction_context {
1671
create_table_statement {
1678
name: "autoinc_int_field"
1684
is_autoincrement: true
1688
name: "null_int_field"
1695
name: "not_null_bigint_field"
1702
name: "null_bigint_field"
1709
name: "not_null_int_field"
1716
name: "null_varchar_field"
1724
collation: "utf8_general_ci"
1728
name: "not_null_varchar_field"
1736
collation: "utf8_general_ci"
1740
name: "null_enum_field"
1745
enumeration_values {
1747
collation: "utf8_general_ci"
1753
name: "not_null_enum_field"
1758
enumeration_values {
1760
collation: "utf8_general_ci"
1766
name: "null_date_field"
1773
name: "not_null_date_field"
1780
name: "null_datetime_field"
1787
name: "not_null_datetime_field"
1794
name: "null_blob_field"
1805
name: "not_null_blob_field"
1816
name: "null_text_field"
1823
collation: "utf8_general_ci"
1827
name: "not_null_text_field"
1834
collation: "utf8_general_ci"
1838
name: "null_timestamp_field"
1845
name: "not_null_timestamp_field"
1852
name: "null_double_field"
1859
name: "not_null_double_field"
1866
name: "null_decimal_field"
1877
name: "not_null_decimal_field"
1903
has_user_set_auto_increment_value: false
1904
collation: "utf8_general_ci"
1922
SET GLOBAL transaction_log_truncate_debug= true;
1924
Testing index generation
1925
DROP TABLE IF EXISTS t1;
1927
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
1928
, key1 VARCHAR(10) NOT NULL
1929
, key2 DOUBLE NOT NULL
1930
, key3 BLOB NOT NULL
1932
, KEY named_key (key2)
1933
, KEY partial_key (key3(30))
1935
Check transaction_log_entries
1936
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
1940
Check transaction_log_transactions
1941
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
1944
Check transaction log contents
1945
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
1946
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
1947
transaction_context {
1957
create_table_statement {
1970
is_autoincrement: true
1982
collation: "utf8_general_ci"
2027
binary_pack_key: true
2028
var_length_key: true
2055
binary_pack_key: true
2056
var_length_key: true
2057
has_partial_segments: true
2063
has_user_set_auto_increment_value: false
2064
collation: "utf8_general_ci"
2082
SET GLOBAL transaction_log_truncate_debug= true;