1# tests the basic functionality of rocksdb_strict_collation, 2# rocksdb_strict_collation_exceptions, and my_regex functionality behind 3# rocksdb_strict_collation_exceptions 4--source include/have_rocksdb.inc 5 6call mtr.add_suppression("Invalid pattern"); 7 8# ci non-indexed column is allowed 9CREATE TABLE t1 (id INT PRIMARY KEY, value VARCHAR(50), value2 VARBINARY(50), value3 TEXT) ENGINE=ROCKSDB CHARSET UTF8; 10# ci indexed column is not allowed 11ALTER TABLE t1 ADD INDEX (value); 12DROP TABLE t1; 13 14# ci indexed column is not allowed 15CREATE TABLE t1 (id INT PRIMARY KEY, value VARCHAR(50), value2 VARBINARY(50), value3 TEXT, INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 16DROP TABLE t1; 17CREATE TABLE t1 (id INT PRIMARY KEY, value VARCHAR(50), value2 VARBINARY(50), value3 TEXT, INDEX(value3(50))) ENGINE=ROCKSDB CHARSET UTF8; 18DROP TABLE t1; 19# ci indexed column with rocksdb_strict_collation_check=OFF is allowed. 20SET GLOBAL rocksdb_strict_collation_check=0; 21CREATE TABLE t1 (id INT PRIMARY KEY, value VARCHAR(50), value2 VARBINARY(50), value3 TEXT, INDEX(value3(50))) ENGINE=ROCKSDB CHARSET UTF8; 22DROP TABLE t1; 23SET GLOBAL rocksdb_strict_collation_check=1; 24 25# cs indexed column is allowed 26CREATE TABLE t1 (id INT PRIMARY KEY, value VARCHAR(50), value2 VARBINARY(50), value3 TEXT, INDEX(value2)) ENGINE=ROCKSDB CHARSET UTF8; 27DROP TABLE t1; 28 29# cs latin1_bin is allowed 30CREATE TABLE t1 (id VARCHAR(20), value VARCHAR(50), value2 VARCHAR(50), value3 TEXT, PRIMARY KEY (id), INDEX(value, value2)) ENGINE=ROCKSDB CHARSET LATIN1 COLLATE LATIN1_BIN; 31# THIS SHOULD FAIL BUT IT DOES NOT 32ALTER TABLE t1 collate=latin1_general_ci; 33DROP TABLE t1; 34 35# cs utf8_bin is allowed 36CREATE TABLE t1 (id VARCHAR(20), value VARCHAR(50), value2 VARCHAR(50), value3 TEXT, PRIMARY KEY (id), INDEX(value, value2)) ENGINE=ROCKSDB CHARSET UTF8 COLLATE UTF8_BIN; 37DROP TABLE t1; 38 39# cs mixed latin1_bin and utf8_bin is allowed 40CREATE TABLE t1 (id VARCHAR(20) COLLATE LATIN1_BIN, value VARCHAR(50) COLLATE UTF8_BIN, value2 VARCHAR(50) COLLATE LATIN1_BIN, value3 TEXT, PRIMARY KEY (id), INDEX(value, value2)) ENGINE=ROCKSDB CHARSET UTF8; 41DROP TABLE t1; 42 43# ci indexed column is not allowed unless table name is in exception list 44SET GLOBAL rocksdb_strict_collation_exceptions=t1; 45CREATE TABLE t1 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 46DROP TABLE t1; 47CREATE TABLE t2 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 48DROP TABLE t2; 49 50# test regex for exception list 51SET GLOBAL rocksdb_strict_collation_exceptions="t.*"; 52CREATE TABLE t123 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 53DROP TABLE t123; 54CREATE TABLE s123 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 55DROP TABLE s123; 56 57SET GLOBAL rocksdb_strict_collation_exceptions=".t.*"; 58CREATE TABLE xt123 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 59DROP TABLE xt123; 60CREATE TABLE t123 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 61DROP TABLE t123; 62 63# test multiple entries in the list with vertical bar 64SET GLOBAL rocksdb_strict_collation_exceptions="s.*|t.*"; 65CREATE TABLE s1 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 66DROP TABLE s1; 67CREATE TABLE t1 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 68DROP TABLE t1; 69CREATE TABLE u1 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 70DROP TABLE u1; 71 72# test allowing alters to create temporary tables 73SET GLOBAL rocksdb_strict_collation_exceptions='t1'; 74CREATE TABLE t1 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 75ALTER TABLE t1 AUTO_INCREMENT=1; 76DROP TABLE t1; 77CREATE TABLE t2 (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 78DROP TABLE t2; 79CREATE TABLE t2 (id INT PRIMARY KEY, value VARCHAR(50)) ENGINE=ROCKSDB CHARSET UTF8; 80ALTER TABLE t2 ADD INDEX(value); 81DROP TABLE t2; 82 83--let SEARCH_FILE=$MYSQLTEST_VARDIR/tmp/rocksdb.collation.err 84--let $restart_parameters=restart:--log-error=$SEARCH_FILE 85--replace_result $SEARCH_FILE LOG_FILE 86--source include/restart_mysqld.inc 87 88# test invalid regex (missing end bracket) 89SET GLOBAL rocksdb_strict_collation_exceptions="[a-b"; 90--let SEARCH_PATTERN=RocksDB: Invalid pattern in strict_collation_exceptions: \[a-b 91--source include/search_pattern_in_file.inc 92 93CREATE TABLE a (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 94DROP TABLE a; 95SET GLOBAL rocksdb_strict_collation_exceptions="[a-b]"; 96CREATE TABLE a (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 97CREATE TABLE b (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 98CREATE TABLE c (id INT PRIMARY KEY, value VARCHAR(50), INDEX(value)) ENGINE=ROCKSDB CHARSET UTF8; 99DROP TABLE a, b, c; 100 101# test invalid regex (trailing escape) 102SET GLOBAL rocksdb_strict_collation_exceptions="abc\\"; 103--let SEARCH_PATTERN=Invalid pattern in strict_collation_exceptions: abc\\\\ 104--source include/search_pattern_in_file.inc 105 106# test bad regex (null caused a crash) - Issue 493 107SET GLOBAL rocksdb_strict_collation_exceptions=null; 108 109# test for errors instead of warnings 110--let $restart_parameters=restart:--rocksdb_error_on_suboptimal_collation=1 111--source include/restart_mysqld.inc 112 113SET GLOBAL rocksdb_strict_collation_check=1; 114 115# ci indexed column is not optimal, should emit a warning 116--error ER_UNSUPPORTED_COLLATION 117CREATE TABLE t1 (id INT primary key, value varchar(50), value2 varbinary(50), value3 text, index(value)) engine=rocksdb charset utf8; 118 119# ci non-indexed column is allowed 120CREATE TABLE t1 (id INT primary key, value varchar(50), value2 varbinary(50), value3 text) engine=rocksdb charset utf8; 121# ci indexed column is not allowed, should emit an error 122--error ER_UNSUPPORTED_COLLATION 123ALTER TABLE t1 ADD INDEX (value); 124DROP TABLE t1; 125 126# cs latin1_bin is allowed 127CREATE TABLE t1 (id varchar(20), value varchar(50), value2 varchar(50), value3 text, primary key (id), index(value, value2)) engine=rocksdb charset latin1 collate latin1_bin; 128# THIS SHOULD ERROR BUT IT DOES NOT 129ALTER TABLE t1 collate=latin1_general_ci; 130DROP TABLE t1; 131 132# cleanup 133--let $restart_parameters= 134--source include/restart_mysqld.inc 135--remove_file $SEARCH_FILE 136