1drop table if exists gap1,gap2,gap3; 2CREATE DATABASE mysqlslap; 3CREATE TABLE gap1 (id1 INT, id2 INT, id3 INT, c1 INT, value INT, 4PRIMARY KEY (id1, id2, id3), 5INDEX i (c1)) ENGINE=rocksdb; 6CREATE TABLE gap2 like gap1; 7CREATE TABLE gap3 (id INT, value INT, 8PRIMARY KEY (id), 9UNIQUE KEY ui(value)) ENGINE=rocksdb; 10insert into gap3 values (1,1), (2,2),(3,3),(4,4),(5,5); 11create table gap4 ( 12pk int primary key, 13a int, 14b int, 15key(a) 16) ENGINE=rocksdb; 17insert into gap4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4); 18create table gap5 like gap4; 19insert into gap5 values (1,1,1), (2,2,2), (3,3,3), (4,4,4); 20set session gap_lock_raise_error=1; 21set session gap_lock_write_log=1; 22set @save_gap_lock_write_log = @@gap_lock_write_log; 23set @save_gap_lock_raise_error = @@gap_lock_raise_error; 24set gap_lock_write_log = 1; 25set gap_lock_raise_error = 0; 26begin; 27update gap4 set a= (select 1+max(a) from gap5 where gap5.pk between 1 and 3 and gap5.b=gap4.b); 281 29update gap4 set a= (select 2+max(a) from gap5 where gap5.pk between 1 and 3 and gap5.b=gap4.b); 30update gap4 set a= (select 3+max(a) from gap5 where gap5.pk between 1 and 3 and gap5.b=gap4.b); 311 321 330 34flush logs; 350 36rollback; 37set gap_lock_write_log = @save_gap_lock_write_log; 38set gap_lock_raise_error = @save_gap_lock_raise_error; 39set global gap_lock_write_log = 1; 40set global gap_lock_write_log = 0; 411000 42set session autocommit=0; 43select * from gap1 limit 1 for update; 44ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 limit 1 for update 45select * from gap1 where value != 100 limit 1 for update; 46ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where value != 100 limit 1 for update 47select * from gap1 where id1=1 for update; 48ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 for update 49select * from gap1 where id1=1 and id2= 1 for update; 50ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 for update 51select * from gap1 where id1=1 and id2= 1 and id3 != 1 for update; 52ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 and id3 != 1 for update 53select * from gap1 where id1=1 and id2= 1 and id3 54between 1 and 3 for update; 55ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 and id3 56between 1 and 3 for update 57select * from gap1 where id1=1 and id2= 1 order by id3 asc 58limit 1 for update; 59ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 order by id3 asc 60limit 1 for update 61select * from gap1 where id1=1 and id2= 1 order by id3 desc 62limit 1 for update; 63ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 order by id3 desc 64limit 1 for update 65select * from gap1 order by id1 asc limit 1 for update; 66ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 order by id1 asc limit 1 for update 67select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 for update; 68ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 for update 69select * from gap1 order by id1 desc limit 1 for update; 70ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 order by id1 desc limit 1 for update 71select * from gap1 order by id1 desc, id2 desc, id3 desc 72limit 1 for update; 73ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 order by id1 desc, id2 desc, id3 desc 74limit 1 for update 75select * from gap1 force index(i) where c1=1 for update; 76ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 force index(i) where c1=1 for update 77select * from gap3 force index(ui) where value=1 for update; 78id value 791 1 80select * from gap1 where id1=1 and id2=1 and id3=1 for update; 81id1 id2 id3 c1 value 82select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) for update; 83id1 id2 id3 c1 value 84select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 85order by c1 for update; 86id1 id2 id3 c1 value 87select * from gap3 where id=1 for update; 88id value 891 1 90set session autocommit=1; 91select * from gap1 limit 1 for update; 92id1 id2 id3 c1 value 930 0 1 1 1 94select * from gap1 where value != 100 limit 1 for update; 95id1 id2 id3 c1 value 960 0 1 1 1 97select * from gap1 where id1=1 for update; 98id1 id2 id3 c1 value 991 0 2 2 2 1001 0 3 3 3 101select * from gap1 where id1=1 and id2= 1 for update; 102id1 id2 id3 c1 value 103select * from gap1 where id1=1 and id2= 1 and id3 != 1 for update; 104id1 id2 id3 c1 value 105select * from gap1 where id1=1 and id2= 1 and id3 106between 1 and 3 for update; 107id1 id2 id3 c1 value 108select * from gap1 where id1=1 and id2= 1 order by id3 asc 109limit 1 for update; 110id1 id2 id3 c1 value 111select * from gap1 where id1=1 and id2= 1 order by id3 desc 112limit 1 for update; 113id1 id2 id3 c1 value 114select * from gap1 order by id1 asc limit 1 for update; 115id1 id2 id3 c1 value 1160 0 1 1 1 117select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 for update; 118id1 id2 id3 c1 value 1190 0 1 1 1 120select * from gap1 order by id1 desc limit 1 for update; 121id1 id2 id3 c1 value 122500 100 1000 1000 1000 123select * from gap1 order by id1 desc, id2 desc, id3 desc 124limit 1 for update; 125id1 id2 id3 c1 value 126500 100 1000 1000 1000 127select * from gap1 force index(i) where c1=1 for update; 128id1 id2 id3 c1 value 1290 0 1 1 1 130select * from gap3 force index(ui) where value=1 for update; 131id value 1321 1 133select * from gap1 where id1=1 and id2=1 and id3=1 for update; 134id1 id2 id3 c1 value 135select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) for update; 136id1 id2 id3 c1 value 137select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 138order by c1 for update; 139id1 id2 id3 c1 value 140select * from gap3 where id=1 for update; 141id value 1421 1 143set session autocommit=0; 144select * from gap1 limit 1 lock in share mode; 145ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 limit 1 lock in share mode 146select * from gap1 where value != 100 limit 1 lock in share mode; 147ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where value != 100 limit 1 lock in share mode 148select * from gap1 where id1=1 lock in share mode; 149ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 lock in share mode 150select * from gap1 where id1=1 and id2= 1 lock in share mode; 151ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 lock in share mode 152select * from gap1 where id1=1 and id2= 1 and id3 != 1 lock in share mode; 153ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 and id3 != 1 lock in share mode 154select * from gap1 where id1=1 and id2= 1 and id3 155between 1 and 3 lock in share mode; 156ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 and id3 157between 1 and 3 lock in share mode 158select * from gap1 where id1=1 and id2= 1 order by id3 asc 159limit 1 lock in share mode; 160ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 order by id3 asc 161limit 1 lock in share mode 162select * from gap1 where id1=1 and id2= 1 order by id3 desc 163limit 1 lock in share mode; 164ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where id1=1 and id2= 1 order by id3 desc 165limit 1 lock in share mode 166select * from gap1 order by id1 asc limit 1 lock in share mode; 167ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 order by id1 asc limit 1 lock in share mode 168select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 lock in share mode; 169ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 lock in share mode 170select * from gap1 order by id1 desc limit 1 lock in share mode; 171ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 order by id1 desc limit 1 lock in share mode 172select * from gap1 order by id1 desc, id2 desc, id3 desc 173limit 1 lock in share mode; 174ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 order by id1 desc, id2 desc, id3 desc 175limit 1 lock in share mode 176select * from gap1 force index(i) where c1=1 lock in share mode; 177ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 force index(i) where c1=1 lock in share mode 178select * from gap3 force index(ui) where value=1 lock in share mode; 179id value 1801 1 181select * from gap1 where id1=1 and id2=1 and id3=1 lock in share mode; 182id1 id2 id3 c1 value 183select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) lock in share mode; 184id1 id2 id3 c1 value 185select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 186order by c1 lock in share mode; 187id1 id2 id3 c1 value 188select * from gap3 where id=1 lock in share mode; 189id value 1901 1 191set session autocommit=1; 192select * from gap1 limit 1 lock in share mode; 193id1 id2 id3 c1 value 1940 0 1 1 1 195select * from gap1 where value != 100 limit 1 lock in share mode; 196id1 id2 id3 c1 value 1970 0 1 1 1 198select * from gap1 where id1=1 lock in share mode; 199id1 id2 id3 c1 value 2001 0 2 2 2 2011 0 3 3 3 202select * from gap1 where id1=1 and id2= 1 lock in share mode; 203id1 id2 id3 c1 value 204select * from gap1 where id1=1 and id2= 1 and id3 != 1 lock in share mode; 205id1 id2 id3 c1 value 206select * from gap1 where id1=1 and id2= 1 and id3 207between 1 and 3 lock in share mode; 208id1 id2 id3 c1 value 209select * from gap1 where id1=1 and id2= 1 order by id3 asc 210limit 1 lock in share mode; 211id1 id2 id3 c1 value 212select * from gap1 where id1=1 and id2= 1 order by id3 desc 213limit 1 lock in share mode; 214id1 id2 id3 c1 value 215select * from gap1 order by id1 asc limit 1 lock in share mode; 216id1 id2 id3 c1 value 2170 0 1 1 1 218select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 lock in share mode; 219id1 id2 id3 c1 value 2200 0 1 1 1 221select * from gap1 order by id1 desc limit 1 lock in share mode; 222id1 id2 id3 c1 value 223500 100 1000 1000 1000 224select * from gap1 order by id1 desc, id2 desc, id3 desc 225limit 1 lock in share mode; 226id1 id2 id3 c1 value 227500 100 1000 1000 1000 228select * from gap1 force index(i) where c1=1 lock in share mode; 229id1 id2 id3 c1 value 2300 0 1 1 1 231select * from gap3 force index(ui) where value=1 lock in share mode; 232id value 2331 1 234select * from gap1 where id1=1 and id2=1 and id3=1 lock in share mode; 235id1 id2 id3 c1 value 236select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) lock in share mode; 237id1 id2 id3 c1 value 238select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 239order by c1 lock in share mode; 240id1 id2 id3 c1 value 241select * from gap3 where id=1 lock in share mode; 242id value 2431 1 244set session autocommit=0; 245select * from gap1 limit 1 ; 246id1 id2 id3 c1 value 2470 0 1 1 1 248select * from gap1 where value != 100 limit 1 ; 249id1 id2 id3 c1 value 2500 0 1 1 1 251select * from gap1 where id1=1 ; 252id1 id2 id3 c1 value 2531 0 2 2 2 2541 0 3 3 3 255select * from gap1 where id1=1 and id2= 1 ; 256id1 id2 id3 c1 value 257select * from gap1 where id1=1 and id2= 1 and id3 != 1 ; 258id1 id2 id3 c1 value 259select * from gap1 where id1=1 and id2= 1 and id3 260between 1 and 3 ; 261id1 id2 id3 c1 value 262select * from gap1 where id1=1 and id2= 1 order by id3 asc 263limit 1 ; 264id1 id2 id3 c1 value 265select * from gap1 where id1=1 and id2= 1 order by id3 desc 266limit 1 ; 267id1 id2 id3 c1 value 268select * from gap1 order by id1 asc limit 1 ; 269id1 id2 id3 c1 value 2700 0 1 1 1 271select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 ; 272id1 id2 id3 c1 value 2730 0 1 1 1 274select * from gap1 order by id1 desc limit 1 ; 275id1 id2 id3 c1 value 276500 100 1000 1000 1000 277select * from gap1 order by id1 desc, id2 desc, id3 desc 278limit 1 ; 279id1 id2 id3 c1 value 280500 100 1000 1000 1000 281select * from gap1 force index(i) where c1=1 ; 282id1 id2 id3 c1 value 2830 0 1 1 1 284select * from gap3 force index(ui) where value=1 ; 285id value 2861 1 287select * from gap1 where id1=1 and id2=1 and id3=1 ; 288id1 id2 id3 c1 value 289select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) ; 290id1 id2 id3 c1 value 291select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 292order by c1 ; 293id1 id2 id3 c1 value 294select * from gap3 where id=1 ; 295id value 2961 1 297set session autocommit=1; 298select * from gap1 limit 1 ; 299id1 id2 id3 c1 value 3000 0 1 1 1 301select * from gap1 where value != 100 limit 1 ; 302id1 id2 id3 c1 value 3030 0 1 1 1 304select * from gap1 where id1=1 ; 305id1 id2 id3 c1 value 3061 0 2 2 2 3071 0 3 3 3 308select * from gap1 where id1=1 and id2= 1 ; 309id1 id2 id3 c1 value 310select * from gap1 where id1=1 and id2= 1 and id3 != 1 ; 311id1 id2 id3 c1 value 312select * from gap1 where id1=1 and id2= 1 and id3 313between 1 and 3 ; 314id1 id2 id3 c1 value 315select * from gap1 where id1=1 and id2= 1 order by id3 asc 316limit 1 ; 317id1 id2 id3 c1 value 318select * from gap1 where id1=1 and id2= 1 order by id3 desc 319limit 1 ; 320id1 id2 id3 c1 value 321select * from gap1 order by id1 asc limit 1 ; 322id1 id2 id3 c1 value 3230 0 1 1 1 324select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 ; 325id1 id2 id3 c1 value 3260 0 1 1 1 327select * from gap1 order by id1 desc limit 1 ; 328id1 id2 id3 c1 value 329500 100 1000 1000 1000 330select * from gap1 order by id1 desc, id2 desc, id3 desc 331limit 1 ; 332id1 id2 id3 c1 value 333500 100 1000 1000 1000 334select * from gap1 force index(i) where c1=1 ; 335id1 id2 id3 c1 value 3360 0 1 1 1 337select * from gap3 force index(ui) where value=1 ; 338id value 3391 1 340select * from gap1 where id1=1 and id2=1 and id3=1 ; 341id1 id2 id3 c1 value 342select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) ; 343id1 id2 id3 c1 value 344select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 345order by c1 ; 346id1 id2 id3 c1 value 347select * from gap3 where id=1 ; 348id value 3491 1 350set session autocommit=0; 351insert into gap1 (id1, id2, id3) values (-1,-1,-1); 352insert into gap1 (id1, id2, id3) values (-1,-1,-1) 353on duplicate key update value=100; 354update gap1 set value=100 where id1=1; 355ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: update gap1 set value=100 where id1=1 356update gap1 set value=100 where id1=1 and id2=1 and id3=1; 357delete from gap1 where id1=2; 358ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: delete from gap1 where id1=2 359delete from gap1 where id1=-1 and id2=-1 and id3=-1; 360commit; 361set session autocommit=1; 362insert into gap1 (id1, id2, id3) values (-1,-1,-1); 363insert into gap1 (id1, id2, id3) values (-1,-1,-1) 364on duplicate key update value=100; 365update gap1 set value=100 where id1=1; 366update gap1 set value=100 where id1=1 and id2=1 and id3=1; 367delete from gap1 where id1=2; 368delete from gap1 where id1=-1 and id2=-1 and id3=-1; 369commit; 370set session autocommit=1; 371insert into gap2 select * from gap1; 372ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: insert into gap2 select * from gap1 373insert into gap2 select * from gap1 where id1=1; 374ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: insert into gap2 select * from gap1 where id1=1 375insert into gap2 select * from gap1 where id1=1 and id2=1 and id3=1; 376create table t4 select * from gap1 where id1=1 and id2=1 and id3=1; 377drop table t4; 378create table t4 select * from gap1; 379ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: create table t4 select * from gap1 380create table t4 select * from gap1 where id1=1; 381ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: create table t4 select * from gap1 where id1=1 382update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 set gap1.value=100 where gap2.id1=3 383and gap2.id2=3 and gap2.id3=3; 384update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 set gap1.value=100 where gap2.id1=3; 385ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 set gap1.value=100 where gap2.id1=3 386update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 join gap3 on gap1.id1=gap3.id 387set gap1.value=100 where gap2.id1=3; 388ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 join gap3 on gap1.id1=gap3.id 389set gap1.value=100 where gap2.id1=3 390update gap1 set gap1.value= (select count(*) from gap2); 391ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: update gap1 set gap1.value= (select count(*) from gap2) 392delete gap1 from gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 where gap2.id1=3 393and gap2.id2=3 and gap2.id3=3; 394delete gap1 from gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 where gap2.id1=3; 395ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: delete gap1 from gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 where gap2.id1=3 396select * from gap1, gap2 limit 1 for update; 397ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1, gap2 limit 1 for update 398select * from gap1 a, gap1 b limit 1 for update; 399ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 a, gap1 b limit 1 for update 400create table u1( 401c1 int, 402c2 int, 403c3 int, 404c4 int, 405primary key (c1, c2, c3), 406unique key (c3, c1) 407); 408set session gap_lock_raise_error=1; 409begin; 410insert into u1 values (1,1,1,1); 411commit; 412begin; 413insert into u1 values (1,2,1,1) on duplicate key update c4=10; 414commit; 415begin; 416select * from u1 where c3=1 and c1 = 1 for update; 417c1 c2 c3 c4 4181 1 1 10 419select * from u1 where c3=1 for update; 420ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from u1 where c3=1 for update 421commit; 422drop table u1; 423set global gap_lock_write_log= 0; 424set global gap_lock_raise_error= 0; 425drop table if exists gap1, gap2, gap3, gap4, gap5; 426DROP DATABASE mysqlslap; 4270 428SET GLOBAL gap_lock_log_file='<GAP_LOCK_ORIG>'; 429SET GLOBAL gap_lock_log_file='<GAP_LOCK>'; 430flush general logs; 431SET @save_gap_lock_exceptions = @@global.gap_lock_exceptions; 432SET GLOBAL gap_lock_exceptions="t.*"; 433drop table if exists gap1,gap2,gap3; 434CREATE DATABASE mysqlslap; 435CREATE TABLE gap1 (id1 INT, id2 INT, id3 INT, c1 INT, value INT, 436PRIMARY KEY (id1, id2, id3), 437INDEX i (c1)) ENGINE=rocksdb; 438CREATE TABLE gap2 like gap1; 439CREATE TABLE gap3 (id INT, value INT, 440PRIMARY KEY (id), 441UNIQUE KEY ui(value)) ENGINE=rocksdb; 442insert into gap3 values (1,1), (2,2),(3,3),(4,4),(5,5); 443create table gap4 ( 444pk int primary key, 445a int, 446b int, 447key(a) 448) ENGINE=rocksdb; 449insert into gap4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4); 450create table gap5 like gap4; 451insert into gap5 values (1,1,1), (2,2,2), (3,3,3), (4,4,4); 452set session gap_lock_raise_error=1; 453set session gap_lock_write_log=1; 454set session autocommit=0; 455select * from gap1 limit 1 for update; 456ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 limit 1 for update 457select * from gap1 where value != 100 limit 1 for update; 458ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction. Query: select * from gap1 where value != 100 limit 1 for update 459set global gap_lock_write_log= 0; 460set global gap_lock_raise_error= 0; 461drop table if exists gap1, gap2, gap3, gap4, gap5; 462DROP DATABASE mysqlslap; 4630 464SET GLOBAL gap_lock_log_file='<GAP_LOCK_ORIG>'; 465SET GLOBAL gap_lock_log_file='<GAP_LOCK>'; 466flush general logs; 467SET GLOBAL gap_lock_exceptions="gap.*"; 468drop table if exists gap1,gap2,gap3; 469CREATE DATABASE mysqlslap; 470CREATE TABLE gap1 (id1 INT, id2 INT, id3 INT, c1 INT, value INT, 471PRIMARY KEY (id1, id2, id3), 472INDEX i (c1)) ENGINE=rocksdb; 473CREATE TABLE gap2 like gap1; 474CREATE TABLE gap3 (id INT, value INT, 475PRIMARY KEY (id), 476UNIQUE KEY ui(value)) ENGINE=rocksdb; 477insert into gap3 values (1,1), (2,2),(3,3),(4,4),(5,5); 478create table gap4 ( 479pk int primary key, 480a int, 481b int, 482key(a) 483) ENGINE=rocksdb; 484insert into gap4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4); 485create table gap5 like gap4; 486insert into gap5 values (1,1,1), (2,2,2), (3,3,3), (4,4,4); 487set session gap_lock_raise_error=1; 488set session gap_lock_write_log=1; 489set session autocommit=0; 490select * from gap1 limit 1 for update; 491id1 id2 id3 c1 value 4920 0 1 1 1 493select * from gap1 where value != 100 limit 1 for update; 494id1 id2 id3 c1 value 4950 0 1 1 1 496set global gap_lock_write_log= 0; 497set global gap_lock_raise_error= 0; 498drop table if exists gap1, gap2, gap3, gap4, gap5; 499DROP DATABASE mysqlslap; 5000 501SET GLOBAL gap_lock_log_file='<GAP_LOCK_ORIG>'; 502SET GLOBAL gap_lock_log_file='<GAP_LOCK>'; 503flush general logs; 504SET GLOBAL gap_lock_exceptions=@save_gap_lock_exceptions; 505