1--source include/have_rocksdb.inc 2# MariaDB doesn't have server variables to check for GCC version, so the 3# following check is commented out: 4# --source include/have_fullregex.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 11--error ER_UNSUPPORTED_COLLATION 12ALTER TABLE t1 ADD INDEX (value); 13DROP TABLE t1; 14 15# ci indexed column is not allowed 16--error ER_UNSUPPORTED_COLLATION 17CREATE TABLE t1 (id INT primary key, value varchar(50), value2 varbinary(50), value3 text, index(value)) engine=rocksdb charset utf8; 18--error ER_UNSUPPORTED_COLLATION 19CREATE TABLE t1 (id INT primary key, value varchar(50), value2 varbinary(50), value3 text, index(value3(50))) engine=rocksdb charset utf8; 20# ci indexed column with rocksdb_strict_collation_check=OFF is allowed. 21SET GLOBAL rocksdb_strict_collation_check=0; 22CREATE TABLE t1 (id INT primary key, value varchar(50), value2 varbinary(50), value3 text, index(value3(50))) engine=rocksdb charset utf8; 23DROP TABLE t1; 24SET GLOBAL rocksdb_strict_collation_check=1; 25 26# cs indexed column is allowed 27CREATE TABLE t1 (id INT primary key, value varchar(50), value2 varbinary(50), value3 text, index(value2)) engine=rocksdb charset utf8; 28DROP TABLE t1; 29 30# cs latin1_bin is allowed 31CREATE 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; 32# THIS SHOULD FAIL BUT IT DOES NOT 33ALTER TABLE t1 collate=latin1_general_ci; 34DROP TABLE t1; 35 36# cs utf8_bin is allowed 37CREATE 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; 38DROP TABLE t1; 39 40# cs mixed latin1_bin and utf8_bin is allowed 41CREATE 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; 42DROP TABLE t1; 43 44# ci indexed column is not allowed unless table name is in exception list 45SET GLOBAL rocksdb_strict_collation_exceptions=t1; 46CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 47DROP TABLE t1; 48--error ER_UNSUPPORTED_COLLATION 49CREATE TABLE t2 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 50 51# test regex for exception list 52SET GLOBAL rocksdb_strict_collation_exceptions="t.*"; 53CREATE TABLE t123 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 54DROP TABLE t123; 55--error ER_UNSUPPORTED_COLLATION 56CREATE TABLE s123 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 57 58SET GLOBAL rocksdb_strict_collation_exceptions=".t.*"; 59CREATE TABLE xt123 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 60DROP TABLE xt123; 61--error ER_UNSUPPORTED_COLLATION 62CREATE TABLE t123 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 63 64# test multiple entries in the list with commas 65SET GLOBAL rocksdb_strict_collation_exceptions="s.*,t.*"; 66CREATE TABLE s1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 67DROP TABLE s1; 68CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 69DROP TABLE t1; 70--error ER_UNSUPPORTED_COLLATION 71CREATE TABLE u1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 72 73# test multiple entries in the list with vertical bar 74SET GLOBAL rocksdb_strict_collation_exceptions="s.*|t.*"; 75CREATE TABLE s1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 76DROP TABLE s1; 77CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 78DROP TABLE t1; 79--error ER_UNSUPPORTED_COLLATION 80CREATE TABLE u1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 81 82# test multiple entries in the list and extra comma at the front 83SET GLOBAL rocksdb_strict_collation_exceptions=",s.*,t.*"; 84CREATE TABLE s1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 85DROP TABLE s1; 86CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 87DROP TABLE t1; 88--error ER_UNSUPPORTED_COLLATION 89CREATE TABLE u1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 90 91# test multiple entries in the list and extra vertical bar at the front 92SET GLOBAL rocksdb_strict_collation_exceptions="|s.*|t.*"; 93CREATE TABLE s1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 94DROP TABLE s1; 95CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 96DROP TABLE t1; 97--error ER_UNSUPPORTED_COLLATION 98CREATE TABLE u1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 99 100# test multiple entries in the list and extra comma in the middle 101SET GLOBAL rocksdb_strict_collation_exceptions="s.*,,t.*"; 102CREATE TABLE s1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 103DROP TABLE s1; 104CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 105DROP TABLE t1; 106--error ER_UNSUPPORTED_COLLATION 107CREATE TABLE u1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 108 109# test multiple entries in the list and extra vertical bar in the middle 110SET GLOBAL rocksdb_strict_collation_exceptions="s.*||t.*"; 111CREATE TABLE s1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 112DROP TABLE s1; 113CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 114DROP TABLE t1; 115--error ER_UNSUPPORTED_COLLATION 116CREATE TABLE u1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 117 118# test multiple entries in the list and extra comma at the end 119SET GLOBAL rocksdb_strict_collation_exceptions="s.*,t.*,"; 120CREATE TABLE s1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 121DROP TABLE s1; 122CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 123DROP TABLE t1; 124--error ER_UNSUPPORTED_COLLATION 125CREATE TABLE u1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 126 127# test multiple entries in the list and extra vertical bar at the end 128SET GLOBAL rocksdb_strict_collation_exceptions="s.*|t.*|"; 129CREATE TABLE s1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 130DROP TABLE s1; 131CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 132DROP TABLE t1; 133--error ER_UNSUPPORTED_COLLATION 134CREATE TABLE u1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 135 136# test multiple entries in the list and tons of commas and vertical bars just for the fun of it 137SET GLOBAL rocksdb_strict_collation_exceptions="||||,,,,s.*,,|,,||,t.*,,|||,,,"; 138CREATE TABLE s1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 139DROP TABLE s1; 140CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 141DROP TABLE t1; 142--error ER_UNSUPPORTED_COLLATION 143CREATE TABLE u1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb charset utf8; 144 145# test allowing alters to create temporary tables 146SET GLOBAL rocksdb_strict_collation_exceptions='t1'; 147CREATE TABLE t1 (id INT primary key, value varchar(50), index(value)) engine=rocksdb; 148ALTER TABLE t1 AUTO_INCREMENT=1; 149DROP TABLE t1; 150--error ER_UNSUPPORTED_COLLATION 151CREATE TABLE t2 (id INT primary key, value varchar(50), index(value)) engine=rocksdb; 152CREATE TABLE t2 (id INT primary key, value varchar(50)) engine=rocksdb; 153--error ER_UNSUPPORTED_COLLATION 154ALTER TABLE t2 ADD INDEX(value); 155DROP TABLE t2; 156 157 158# test invalid regex (missing end bracket) 159SET GLOBAL rocksdb_strict_collation_exceptions="[a-b"; 160let SEARCH_FILE=$MYSQLTEST_VARDIR/log/mysqld.1.err; 161let SEARCH_PATTERN=Invalid pattern in strict_collation_exceptions: \[a-b; 162source include/search_pattern_in_file.inc; 163CREATE TABLE a (id INT PRIMARY KEY, value varchar(50), index(value)) engine=rocksdb charset utf8; 164SET GLOBAL rocksdb_strict_collation_exceptions="[a-b]"; 165CREATE TABLE a (id INT PRIMARY KEY, value varchar(50), index(value)) engine=rocksdb charset utf8; 166CREATE TABLE b (id INT PRIMARY KEY, value varchar(50), index(value)) engine=rocksdb charset utf8; 167--error ER_UNSUPPORTED_COLLATION 168CREATE TABLE c (id INT PRIMARY KEY, value varchar(50), index(value)) engine=rocksdb charset utf8; 169DROP TABLE a, b; 170 171call mtr.add_suppression("Invalid pattern in strict_collation_exceptions:"); 172# test invalid regex (trailing escape) 173SET GLOBAL rocksdb_strict_collation_exceptions="abc\\"; 174let SEARCH_PATTERN=Invalid pattern in strict_collation_exceptions: abc; 175source include/search_pattern_in_file.inc; 176--error ER_UNSUPPORTED_COLLATION 177CREATE TABLE abc (id INT PRIMARY KEY, value varchar(50), index(value)) engine=rocksdb charset utf8; 178SET GLOBAL rocksdb_strict_collation_exceptions="abc"; 179CREATE TABLE abc (id INT PRIMARY KEY, value varchar(50), index(value)) engine=rocksdb charset utf8; 180--error ER_UNSUPPORTED_COLLATION 181CREATE TABLE abcd (id INT PRIMARY KEY, value varchar(50), index(value)) engine=rocksdb charset utf8; 182DROP TABLE abc; 183 184# test bad regex (null caused a crash) - Issue 493 185SET GLOBAL rocksdb_strict_collation_exceptions=null; 186 187# test for warnings instead of errors 188--let $_mysqld_option=--rocksdb_error_on_suboptimal_collation=0 189--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 190--source include/restart_mysqld_with_option.inc 191 192SET GLOBAL rocksdb_strict_collation_check=1; 193 194# ci indexed column is not optimal, should emit a warning 195CREATE TABLE t1 (id INT primary key, value varchar(50), value2 varbinary(50), value3 text, index(value)) engine=rocksdb charset utf8; 196DROP TABLE t1; 197 198# ci non-indexed column is allowed 199CREATE TABLE t1 (id INT primary key, value varchar(50), value2 varbinary(50), value3 text) engine=rocksdb charset utf8; 200# ci indexed column is not allowed, should emit a warning 201ALTER TABLE t1 ADD INDEX (value); 202DROP TABLE t1; 203 204# cs latin1_bin is allowed 205CREATE 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; 206# THIS SHOULD WARN BUT IT DOES NOT 207ALTER TABLE t1 collate=latin1_general_ci; 208DROP TABLE t1; 209 210# cleanup 211--source include/restart_mysqld.inc 212