1# Testcase for worklog #5743: Lift the limit of index key prefixes 2 3--source include/have_innodb.inc 4--source include/have_innodb_8k.inc 5SET default_storage_engine=InnoDB; 6 7--disable_query_log 8call mtr.add_suppression("Cannot add field .* in table .* because after adding it, the row size is"); 9--enable_query_log 10 11let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; 12 13set global innodb_file_per_table=1; 14 15-- echo ### Test 1 ### 16# Create a table of DYNAMIC format, with a primary index of 1000 bytes in 17# size 18create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC; 19show warnings; 20 21# Do some insertion and update to excercise the external cache 22# code path 23insert into worklog5743 values(repeat("a", 20000)); 24 25# default session, update the table 26update worklog5743 set a = (repeat("b", 16000)); 27 28# Create a secondary index 29SET sql_mode= ''; 30create index idx on worklog5743(a(2000)); 31show warnings; 32SET sql_mode= default; 33 34# Start a few sessions to do selections on table being updated in default 35# session, so it would rebuild the previous version from undo log. 36# 1) Default session: Initiate an update on the externally stored column 37# 2) Session con1: Select from table with repeated read 38# 3) Session con2: Select from table with read uncommitted 39# 4) Default session: rollback updates 40 41begin; 42update worklog5743 set a = (repeat("x", 17000)); 43 44# Start a new session to select the column to force it build 45# an earlier version of the clustered index through undo log. So it should 46# just see the result of repeat("b", 16000) 47select @@session.tx_isolation; 48--connect (con1,localhost,root,,) 49select a = repeat("x", 17000) from worklog5743; 50select a = repeat("b", 16000) from worklog5743; 51 52# Start another session doing "read uncommitted" query, it 53# should see the uncommitted update 54--connect (con2,localhost,root,,) 55SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 56select @@session.tx_isolation; 57select a = repeat("x", 17000) from worklog5743; 58 59# Roll back the transaction 60--connection default 61rollback; 62 63drop table worklog5743; 64 65-- echo ### Test 2 ### 66# Create a table with only a secondary index has large prefix column 67create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC; 68show warnings; 69create index idx on worklog5743(a1, a2(1250)); 70show warnings; 71 72insert into worklog5743 values(9, repeat("a", 10000)); 73 74begin; 75 76update worklog5743 set a1 = 1000; 77 78# Do a select from another connection that would use the secondary index 79--connection con1 80select @@session.tx_isolation; 81explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; 82select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; 83 84# Do read uncommitted in another session, it would show there is no 85# row with a1 = 9 86--connection con2 87SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 88select @@session.tx_isolation; 89select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; 90 91--connection default 92rollback; 93 94drop table worklog5743; 95 96-- echo ### Test 3 ### 97# Create a table with a secondary index has small (50 bytes) prefix column 98create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC; 99 100create index idx on worklog5743(a1, a2(50)); 101 102insert into worklog5743 values(9, repeat("a", 10000)); 103 104begin; 105 106update worklog5743 set a1 = 1000; 107 108# Do a select from another connection that would use the secondary index 109--connection con1 110select @@session.tx_isolation; 111explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; 112select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; 113 114# Do read uncommitted in another session, it would show there is no 115# row with a1 = 9 116--connection con2 117SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 118select @@session.tx_isolation; 119select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; 120 121--connection default 122rollback; 123 124drop table worklog5743; 125 126-- echo ### Test 4 ### 127# Create compressed tables with each KEY_BLOCK_SIZE. 128create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1; 129create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2; 130create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4; 131create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8; 132 133# The maximum overall index record (not prefix) length of a 134# compressed table is dependent on innodb-page-size (IPS), 135# key_block_size (KBS) and the number of fields (NF). 136# "Too big row" error (HA_ERR_TOO_BIG_ROW) will be returned if this 137# limit is exceeded. 138# See page_zip_empty_size() and Bug #47495 for more detail. 139 140# Test edge cases for indexes using key_block_size=1 141-- error ER_TOO_BIG_ROWSIZE 142create index idx2 on worklog5743_1(a2(4000)); 143show warnings; 144-- error ER_TOO_BIG_ROWSIZE 145create index idx3 on worklog5743_1(a2(436)); 146show warnings; 147# Bug#13391353 Limit is one byte less on on 32bit-Linux only 148create index idx4 on worklog5743_1(a2(434)); 149show warnings; 150-- error ER_TOO_BIG_ROWSIZE 151create index idx5 on worklog5743_1(a1, a2(430)); 152show warnings; 153# Bug#13391353 Limit is one byte less on on 32bit-Linux only 154create index idx6 on worklog5743_1(a1, a2(428)); 155show warnings; 156 157# Test edge cases for indexes using key_block_size=2 158-- error ER_TOO_BIG_ROWSIZE 159create index idx2 on worklog5743_2(a2(4000)); 160show warnings; 161-- error ER_TOO_BIG_ROWSIZE 162create index idx3 on worklog5743_2(a2(948)); 163show warnings; 164# Bug#13391353 Limit is one byte less on on 32bit-Linux only 165create index idx4 on worklog5743_2(a2(946)); 166show warnings; 167-- error ER_TOO_BIG_ROWSIZE 168create index idx5 on worklog5743_2(a1, a2(942)); 169show warnings; 170# Bug#13391353 Limit is one byte less on on 32bit-Linux only 171create index idx6 on worklog5743_2(a1, a2(940)); 172show warnings; 173 174# Test edge cases for indexes using key_block_size=4 175create index idx3 on worklog5743_4(a2(1537)); 176show warnings; 177create index idx4 on worklog5743_4(a2(1536)); 178show warnings; 179-- error ER_TOO_LONG_KEY 180create index idx5 on worklog5743_4(a1, a2(1533)); 181show warnings; 182create index idx6 on worklog5743_4(a1, a2(1532)); 183show warnings; 184 185# Test edge cases for indexes using key_block_size=8 186create index idx2 on worklog5743_8(a2(3073)); 187show warnings; 188create index idx3 on worklog5743_8(a2(3072)); 189show warnings; 190-- error ER_TOO_LONG_KEY 191create index idx4 on worklog5743_8(a1, a2(1533)); 192show warnings; 193create index idx5 on worklog5743_8(a1, a2(1532)); 194show warnings; 195SET sql_mode= default; 196 197# Insert a large record into each of these tables. 198insert into worklog5743_1 values(9, repeat("a", 10000)); 199insert into worklog5743_2 values(9, repeat("a", 10000)); 200insert into worklog5743_4 values(9, repeat("a", 10000)); 201insert into worklog5743_8 values(9, repeat("a", 10000), repeat("a", 10000)); 202 203select a1, left(a2, 20) from worklog5743_1; 204select a1, left(a2, 20) from worklog5743_2; 205select a1, left(a2, 20) from worklog5743_4; 206select a1, left(a2, 20) from worklog5743_8; 207 208begin; 209 210update worklog5743_1 set a1 = 1000; 211update worklog5743_2 set a1 = 1000; 212update worklog5743_4 set a1 = 1000; 213update worklog5743_8 set a1 = 1000; 214select a1, left(a2, 20) from worklog5743_1; 215select a1, left(a2, 20) from worklog5743_2; 216select a1, left(a2, 20) from worklog5743_4; 217select a1, left(a2, 20) from worklog5743_8; 218 219 220# Do a select from another connection that would use the secondary index 221--connection con1 222select @@session.tx_isolation; 223explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9; 224explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9; 225explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9; 226explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9; 227select a1, left(a2, 20) from worklog5743_1 where a1 = 9; 228select a1, left(a2, 20) from worklog5743_2 where a1 = 9; 229select a1, left(a2, 20) from worklog5743_4 where a1 = 9; 230select a1, left(a2, 20) from worklog5743_8 where a1 = 9; 231 232# Do read uncommitted in another session, it would show there is no 233# row with a1 = 9 234--connection con2 235SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 236select @@session.tx_isolation; 237select a1, left(a2, 20) from worklog5743_1 where a1 = 9; 238select a1, left(a2, 20) from worklog5743_2 where a1 = 9; 239select a1, left(a2, 20) from worklog5743_4 where a1 = 9; 240select a1, left(a2, 20) from worklog5743_8 where a1 = 9; 241 242--connection default 243rollback; 244 245drop table worklog5743_1; 246drop table worklog5743_2; 247drop table worklog5743_4; 248drop table worklog5743_8; 249 250-- echo ### Test 5 ### 251# Create a table with large varchar columns and create indexes 252# directly on these large columns to show that prefix limit is 253# automatically applied and to show that limit. 254 255# This commented form of the test causes an unlimited page split 256# on update of the int field - Bug 12636590 - INNODB; UPDATE OF 257# LARGE RECORD CAUSES UNLIMITED PAGE SPLITS IN 8K PAGE SIZE 258#create table worklog5743(a1 int, 259# a2 varchar(20000), 260# a3 varchar(3073), 261# a4 varchar(3072), 262# a5 varchar(3069), 263# a6 varchar(3068)) 264# ROW_FORMAT=DYNAMIC; 265#create index idx1 on worklog5743(a2); 266#create index idx2 on worklog5743(a3); 267#create index idx3 on worklog5743(a4); 268#show warnings; 269#-- error ER_TOO_LONG_KEY 270#create index idx4 on worklog5743(a1, a2); 271#show warnings; 272#-- error ER_TOO_LONG_KEY 273#create index idx5 on worklog5743(a1, a5); 274#show warnings; 275#create index idx6 on worklog5743(a1, a6); 276#show warnings; 277#show create table worklog5743; 278# 279#insert into worklog5743 values(9, 280# repeat("a", 20000), repeat("a", 3073), 281# repeat("a", 3072), repeat("a", 3069), 282# repeat("a", 3068)); 283# 284 285create table worklog5743(a1 int, a2 varchar(20000)) ROW_FORMAT=DYNAMIC; 286-- error ER_TOO_LONG_KEY 287create index idx1 on worklog5743(a2); 288drop table worklog5743; 289 290create table worklog5743(a1 int, a2 varchar(1537)) ROW_FORMAT=DYNAMIC; 291-- error ER_TOO_LONG_KEY 292create index idx1 on worklog5743(a2); 293drop table worklog5743; 294 295create table worklog5743(a1 int, a2 varchar(1536)) ROW_FORMAT=DYNAMIC; 296create index idx1 on worklog5743(a2); 297show warnings; 298insert into worklog5743 values(9, repeat("a", 1536)); 299update worklog5743 set a1 = 1000; 300drop table worklog5743; 301 302create table worklog5743(a1 int, a2 varchar(1533)) ROW_FORMAT=DYNAMIC; 303-- error ER_TOO_LONG_KEY 304create index idx1 on worklog5743(a1, a2); 305show warnings; 306drop table worklog5743; 307 308create table worklog5743(a1 int, a2 varchar(1532)) ROW_FORMAT=DYNAMIC; 309create index idx1 on worklog5743(a1, a2); 310show warnings; 311insert into worklog5743 values(9, repeat("a", 1532)); 312update worklog5743 set a1 = 1000; 313 314begin; 315update worklog5743 set a1 = 1000; 316 317# Do a select from another connection that would use the secondary index 318--connection con1 319select @@session.tx_isolation; 320explain select a1 from worklog5743 where a1 = 9; 321select a1 from worklog5743 where a1 = 9; 322 323# Do read uncommitted, it would show there is no row with a1 = 9 324--connection con2 325SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 326select @@session.tx_isolation; 327select a1 from worklog5743 where a1 = 9; 328 329--connection default 330rollback; 331 332drop table worklog5743; 333 334-- echo ### Test 6 ### 335# Create a table with old format, and the limit is 768 bytes. 336-- error ER_INDEX_COLUMN_TOO_LONG 337create table worklog5743(a TEXT not null, primary key (a(1000))) 338row_format=compact; 339 340create table worklog5743(a TEXT) row_format=compact; 341 342# Excercise the column length check in ha_innobase::add_index() 343-- error ER_INDEX_COLUMN_TOO_LONG 344create index idx on worklog5743(a(768)); 345 346# This should be successful 347create index idx on worklog5743(a(767)); 348 349# Perform some DMLs 350insert into worklog5743 values(repeat("a", 20000)); 351 352begin; 353insert into worklog5743 values(repeat("b", 20000)); 354update worklog5743 set a = (repeat("x", 25000)); 355 356# Start a new session to select the table to force it build 357# an earlier version of the cluster index through undo log 358select @@session.tx_isolation; 359--connection con1 360select a = repeat("a", 20000) from worklog5743; 361--disconnect con1 362 363--connection con2 364SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 365select @@session.tx_isolation; 366select a = repeat("x", 25000) from worklog5743; 367--disconnect con2 368 369--connection default 370rollback; 371 372drop table worklog5743; 373 374-- echo ### Test 7 ### 375# Some border line tests on the column length. 376# We have a limit of 3072 bytes for Barracuda table 377create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC; 378 379# Length exceeds maximum supported key length 380# It will be auto-truncated to 3072 381SET sql_mode= ''; 382create index idx1 on worklog5743(a(3073)); 383create index idx2 on worklog5743(a(3072)); 384SET sql_mode= default; 385show create table worklog5743; 386drop table worklog5743; 387 388# We have a limit of 767 bytes for Antelope tables 389create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT; 390-- error ER_INDEX_COLUMN_TOO_LONG 391create index idx on worklog5743(a(768)); 392create index idx2 on worklog5743(a(767)); 393drop table worklog5743; 394 395create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT; 396-- error ER_INDEX_COLUMN_TOO_LONG 397create index idx on worklog5743(a(768)); 398create index idx2 on worklog5743(a(767)); 399drop table worklog5743; 400 401 402eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; 403