3258 kevin.lewis@stripped 2011-07-19
Cleanup from previous WL5756 patch. It was missing 4k and 8k versions of innodb_index_large_prefix.test which is only for 16k pages. Also, some cleanup of comments in test files.
added:
mysql-test/suite/innodb/r/innodb_index_large_prefix_4k.result
mysql-test/suite/innodb/r/innodb_index_large_prefix_8k.result
mysql-test/suite/innodb/t/innodb_index_large_prefix_4k.test
mysql-test/suite/innodb/t/innodb_index_large_prefix_8k.test
modified:
mysql-test/suite/innodb/r/innodb_16k.result
mysql-test/suite/innodb/r/innodb_4k.result
mysql-test/suite/innodb/r/innodb_8k.result
mysql-test/suite/innodb/t/innodb.test
mysql-test/suite/innodb/t/innodb_16k.test
mysql-test/suite/innodb/t/innodb_4k.test
mysql-test/suite/innodb/t/innodb_8k.test
3257 Inaam Rana 2011-07-19
A port of random readahead fix which went in trunk with following revid.
revno: 3278 [merge]
revision-id: inaam.rana@stripped
added:
mysql-test/suite/sys_vars/r/innodb_random_read_ahead_basic.result
mysql-test/suite/sys_vars/t/innodb_random_read_ahead_basic.test
modified:
storage/innobase/buf/buf0buf.c
storage/innobase/buf/buf0rea.c
storage/innobase/handler/ha_innodb.cc
storage/innobase/include/buf0buf.h
storage/innobase/include/buf0buf.ic
storage/innobase/include/buf0lru.h
storage/innobase/include/buf0rea.h
storage/innobase/include/srv0srv.h
storage/innobase/srv/srv0srv.c
=== modified file 'mysql-test/suite/innodb/r/innodb_16k.result'
--- a/mysql-test/suite/innodb/r/innodb_16k.result revid:inaam.rana@stripped
+++ b/mysql-test/suite/innodb/r/innodb_16k.result revid:kevin.lewis@stripped
@@ -1,17 +1,17 @@
SET default_storage_engine=InnoDB;
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
-# Test 1) Show the page size from Inofrmation Schema
+# Test 1) Show the page size from Information Schema
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_page_size';
variable_value
16384
-# Test 2) With 4k pages, the number of buffer pool pages is different.
+# Test 2) The number of buffer pool pages is dependent upon the page size.
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
variable_value
-512
-# Test 3) With 4k pages, the root page numbers are different.
+{checked_511_or_512}
+# Test 3) The root page numbers are dependent upon the page size.
# Pulled from innodb-system-table-view.test
SELECT NAME, TABLE_ID, PAGE_NO FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES;
NAME TABLE_ID PAGE_NO
@@ -21,8 +21,8 @@ REF_IND 11 304
ID_IND 12 305
PRIMARY 13 307
PRIMARY 14 309
-# Test 4) With 4k pages, the maximum row size is less than for
-# larger pages; Redundant: 8123, Compact: 8126.
+# Test 4) The maximum row size is dependent upon the page size.
+# Redundant: 8123, Compact: 8126.
# Compressed: 8126, Dynamic: 8126.
# Each row format has its own amount of overhead that
# varies depending on number of fields and other overhead.
=== modified file 'mysql-test/suite/innodb/r/innodb_4k.result'
--- a/mysql-test/suite/innodb/r/innodb_4k.result revid:inaam.rana@stripped
+++ b/mysql-test/suite/innodb/r/innodb_4k.result revid:kevin.lewis@stripped
@@ -1,17 +1,17 @@
SET default_storage_engine=InnoDB;
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
-# Test 1) Show the page size from Inofrmation Schema
+# Test 1) Show the page size from Information Schema
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_page_size';
variable_value
4096
-# Test 2) With 4k pages, the number of buffer pool pages is different.
+# Test 2) The number of buffer pool pages is dependent upon the page size.
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
variable_value
2048
-# Test 3) With 4k pages, the root page numbers are different.
+# Test 3) The root page numbers are dependent upon the page size.
# Pulled from innodb-system-table-view.test
SELECT NAME, TABLE_ID, PAGE_NO FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES;
NAME TABLE_ID PAGE_NO
@@ -21,8 +21,8 @@ REF_IND 11 804
ID_IND 12 805
PRIMARY 13 807
PRIMARY 14 810
-# Test 4) With 4k pages, the maximum row size is less than for
-# larger pages; Redundant: 1979, Compact: 1982.
+# Test 4) The maximum row size is dependent upon the page size.
+# Redundant: 1979, Compact: 1982.
# Compressed: 1982, Dynamic: 1982.
# Each row format has its own amount of overhead that
# varies depending on number of fields and other overhead.
=== modified file 'mysql-test/suite/innodb/r/innodb_8k.result'
--- a/mysql-test/suite/innodb/r/innodb_8k.result revid:inaam.rana@stripped
+++ b/mysql-test/suite/innodb/r/innodb_8k.result revid:kevin.lewis@stripped
@@ -1,17 +1,17 @@
SET default_storage_engine=InnoDB;
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
-# Test 1) Show the page size from Inofrmation Schema
+# Test 1) Show the page size from Information Schema
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_page_size';
variable_value
8192
-# Test 2) With 8k pages, the number of buffer pool pages is different.
+# Test 2) The number of buffer pool pages is dependent upon the page size.
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
variable_value
1024
-# Test 3) With 8k pages, the root page numbers are different.
+# Test 3) The root page numbers are dependent upon the page size.
# Pulled from innodb-system-table-view.test
SELECT NAME, TABLE_ID, PAGE_NO FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES;
NAME TABLE_ID PAGE_NO
@@ -21,8 +21,8 @@ REF_IND 11 468
ID_IND 12 469
PRIMARY 13 472
PRIMARY 14 474
-# Test 4) With 8k pages, the maximum row size is less than for
-# larger pages; Redundant: 4027, Compact: 4030.
+# Test 4) The maximum row size is dependent upon the page size.
+# Redundant: 4027, Compact: 4030.
# Compressed: 4030, Dynamic: 4030.
# Each row format has its own amount of overhead that
# varies depending on number of fields and other overhead.
=== added file 'mysql-test/suite/innodb/r/innodb_index_large_prefix_4k.result'
--- a/mysql-test/suite/innodb/r/innodb_index_large_prefix_4k.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/r/innodb_index_large_prefix_4k.result revid:kevin.lewis@stripped
@@ -0,0 +1,413 @@
+SET default_storage_engine=InnoDB;
+set global innodb_file_format="Barracuda";
+set global innodb_file_per_table=1;
+set global innodb_large_prefix=1;
+### Test 1 ###
+create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC;
+show warnings;
+Level Code Message
+insert into worklog5743 values(repeat("a", 20000));
+update worklog5743 set a = (repeat("b", 16000));
+create index idx on worklog5743(a(900));
+show warnings;
+Level Code Message
+begin;
+update worklog5743 set a = (repeat("x", 17000));
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+select a = repeat("x", 17000) from worklog5743;
+a = repeat("x", 17000)
+0
+select a = repeat("b", 16000) from worklog5743;
+a = repeat("b", 16000)
+1
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a = repeat("x", 17000) from worklog5743;
+a = repeat("x", 17000)
+1
+rollback;
+drop table worklog5743;
+### Test 2 ###
+create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
+show warnings;
+Level Code Message
+create index idx on worklog5743(a1, a2(900));
+show warnings;
+Level Code Message
+insert into worklog5743 values(9, repeat("a", 10000));
+begin;
+update worklog5743 set a1 = 1111;
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743 ref idx idx 5 const 1
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+a1 a2 = repeat("a", 10000)
+9 1
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+a1 a2 = repeat("a", 10000)
+rollback;
+drop table worklog5743;
+### Test 3 ###
+create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
+create index idx on worklog5743(a1, a2(50));
+insert into worklog5743 values(9, repeat("a", 10000));
+begin;
+update worklog5743 set a1 = 2222;
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743 ref idx idx 5 const 1
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+a1 a2 = repeat("a", 10000)
+9 1
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+a1 a2 = repeat("a", 10000)
+rollback;
+drop table worklog5743;
+### Test 4 ###
+create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1;
+create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
+create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_1(a2(4000));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+set global innodb_large_prefix=1;
+create index idx2 on worklog5743_1(a2(4000));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx3 on worklog5743_1(a2(436));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx4 on worklog5743_1(a2(435));
+show warnings;
+Level Code Message
+create index idx5 on worklog5743_1(a1, a2(430));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx6 on worklog5743_1(a1, a2(429));
+show warnings;
+Level Code Message
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_2(a2(4000));
+Warnings:
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+set global innodb_large_prefix=1;
+create index idx2 on worklog5743_2(a2(4000));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx3 on worklog5743_2(a2(948));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx4 on worklog5743_2(a2(947));
+show warnings;
+Level Code Message
+create index idx5 on worklog5743_2(a1, a2(942));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx6 on worklog5743_2(a1, a2(941));
+show warnings;
+Level Code Message
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_4(a2(4000));
+Warnings:
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+set global innodb_large_prefix=1;
+create index idx2 on worklog5743_4(a2(4000));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx3 on worklog5743_4(a2(1965));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx4 on worklog5743_4(a2(1964));
+show warnings;
+Level Code Message
+create index idx5 on worklog5743_4(a1, a2(1960));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx6 on worklog5743_4(a1, a2(1959));
+show warnings;
+Level Code Message
+insert into worklog5743_1 values(9, repeat("a", 10000));
+insert into worklog5743_2 values(9, repeat("a", 10000));
+insert into worklog5743_4 values(9, repeat("a", 10000));
+set global innodb_large_prefix=0;
+insert into worklog5743_1 values(2, repeat("b", 10000));
+insert into worklog5743_2 values(2, repeat("b", 10000));
+insert into worklog5743_4 values(2, repeat("b", 10000));
+set global innodb_large_prefix=1;
+select a1, left(a2, 20) from worklog5743_1;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+2 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_2;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+2 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_4;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+2 bbbbbbbbbbbbbbbbbbbb
+begin;
+update worklog5743_1 set a1 = 1000;
+update worklog5743_2 set a1 = 1000;
+update worklog5743_4 set a1 = 1000;
+select a1, left(a2, 20) from worklog5743_1;
+a1 left(a2, 20)
+1000 aaaaaaaaaaaaaaaaaaaa
+1000 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_2;
+a1 left(a2, 20)
+1000 aaaaaaaaaaaaaaaaaaaa
+1000 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_4;
+a1 left(a2, 20)
+1000 aaaaaaaaaaaaaaaaaaaa
+1000 bbbbbbbbbbbbbbbbbbbb
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743_1 ref idx6 idx6 5 const 1
+explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743_2 ref idx6 idx6 5 const 1
+explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743_4 ref idx6 idx6 5 const 1
+select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+a1 left(a2, 20)
+select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+a1 left(a2, 20)
+select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+a1 left(a2, 20)
+rollback;
+drop table worklog5743_1;
+drop table worklog5743_2;
+drop table worklog5743_4;
+### Test 5 ###
+create table worklog5743(a1 int, a2 varchar(20000)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(3072)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(1964)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(1963)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+show warnings;
+Level Code Message
+insert into worklog5743 values(9, repeat("a", 1963));
+update worklog5743 set a1 = 3333;
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(1960)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a1, a2);
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(1959)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a1, a2);
+show warnings;
+Level Code Message
+insert into worklog5743 values(9, repeat("a", 1959));
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(1950)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a1, a2);
+show warnings;
+Level Code Message
+insert into worklog5743 values(9, repeat("a", 1950));
+begin;
+update worklog5743 set a1 = 4444;
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+explain select a1 from worklog5743 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743 ref idx1 idx1 5 const 1 Using index
+select a1 from worklog5743 where a1 = 9;
+a1
+9
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a1 from worklog5743 where a1 = 9;
+a1
+rollback;
+drop table worklog5743;
+### Test 6 ###
+create table worklog5743(a TEXT not null, primary key (a(1000)));
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
+create table worklog5743(a TEXT);
+create index idx on worklog5743(a(768));
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
+create index idx on worklog5743(a(767));
+insert into worklog5743 values(repeat("a", 20000));
+begin;
+insert into worklog5743 values(repeat("b", 20000));
+update worklog5743 set a = (repeat("x", 25000));
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+select a = repeat("a", 20000) from worklog5743;
+a = repeat("a", 20000)
+1
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a = repeat("x", 25000) from worklog5743;
+a = repeat("x", 25000)
+1
+1
+rollback;
+drop table worklog5743;
+### Test 7 ###
+create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a(1965));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx2 on worklog5743(a(1964));
+show warnings;
+Level Code Message
+show create table worklog5743;
+Table Create Table
+worklog5743 CREATE TABLE `worklog5743` (
+ `a` text NOT NULL,
+ KEY `idx2` (`a`(1964))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+insert into worklog5743 values(repeat("a", 1964));
+drop table worklog5743;
+create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
+create index idx on worklog5743(a(768));
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
+create index idx2 on worklog5743(a(767));
+drop table worklog5743;
+create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
+create index idx on worklog5743(a(768));
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
+create index idx2 on worklog5743(a(767));
+drop table worklog5743;
+SET GLOBAL innodb_file_format=Antelope;
+SET GLOBAL innodb_file_per_table=0;
+SET GLOBAL innodb_file_format_max=Antelope;
+SET GLOBAL innodb_large_prefix=0;
=== added file 'mysql-test/suite/innodb/r/innodb_index_large_prefix_8k.result'
--- a/mysql-test/suite/innodb/r/innodb_index_large_prefix_8k.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/r/innodb_index_large_prefix_8k.result revid:kevin.lewis@stripped
@@ -0,0 +1,455 @@
+SET default_storage_engine=InnoDB;
+set global innodb_file_format="Barracuda";
+set global innodb_file_per_table=1;
+set global innodb_large_prefix=1;
+### Test 1 ###
+create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC;
+show warnings;
+Level Code Message
+insert into worklog5743 values(repeat("a", 20000));
+update worklog5743 set a = (repeat("b", 16000));
+create index idx on worklog5743(a(2000));
+show warnings;
+Level Code Message
+begin;
+update worklog5743 set a = (repeat("x", 17000));
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+select a = repeat("x", 17000) from worklog5743;
+a = repeat("x", 17000)
+0
+select a = repeat("b", 16000) from worklog5743;
+a = repeat("b", 16000)
+1
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a = repeat("x", 17000) from worklog5743;
+a = repeat("x", 17000)
+1
+rollback;
+drop table worklog5743;
+### Test 2 ###
+create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
+show warnings;
+Level Code Message
+create index idx on worklog5743(a1, a2(2000));
+show warnings;
+Level Code Message
+insert into worklog5743 values(9, repeat("a", 10000));
+begin;
+update worklog5743 set a1 = 1000;
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743 ref idx idx 5 const 1
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+a1 a2 = repeat("a", 10000)
+9 1
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+a1 a2 = repeat("a", 10000)
+rollback;
+drop table worklog5743;
+### Test 3 ###
+create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
+create index idx on worklog5743(a1, a2(50));
+insert into worklog5743 values(9, repeat("a", 10000));
+begin;
+update worklog5743 set a1 = 1000;
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743 ref idx idx 5 const 1
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+a1 a2 = repeat("a", 10000)
+9 1
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+a1 a2 = repeat("a", 10000)
+rollback;
+drop table worklog5743;
+### Test 4 ###
+create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1;
+create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
+create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
+create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8;
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_1(a2(4000));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+set global innodb_large_prefix=1;
+create index idx2 on worklog5743_1(a2(4000));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx3 on worklog5743_1(a2(436));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx4 on worklog5743_1(a2(435));
+show warnings;
+Level Code Message
+create index idx5 on worklog5743_1(a1, a2(430));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx6 on worklog5743_1(a1, a2(429));
+show warnings;
+Level Code Message
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_2(a2(4000));
+Warnings:
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+set global innodb_large_prefix=1;
+create index idx2 on worklog5743_2(a2(4000));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx3 on worklog5743_2(a2(948));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx4 on worklog5743_2(a2(947));
+show warnings;
+Level Code Message
+create index idx5 on worklog5743_2(a1, a2(942));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx6 on worklog5743_2(a1, a2(941));
+show warnings;
+Level Code Message
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_4(a2(4000));
+Warnings:
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+set global innodb_large_prefix=1;
+create index idx2 on worklog5743_4(a2(4000));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx3 on worklog5743_4(a2(1972));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx4 on worklog5743_4(a2(1971));
+show warnings;
+Level Code Message
+create index idx5 on worklog5743_4(a1, a2(1966));
+ERROR HY000: Too big row
+show warnings;
+Level Code Message
+Error 139 Too big row
+Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 4030. You have to change some columns to TEXT or BLOBs
+Error 1030 Got error 139 from storage engine
+create index idx6 on worklog5743_4(a1, a2(1965));
+show warnings;
+Level Code Message
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_8(a2(1000));
+Warnings:
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 767 bytes
+Warning 1071 Specified key was too long; max key length is 767 bytes
+set global innodb_large_prefix=1;
+create index idx2 on worklog5743_8(a2(3073));
+Warnings:
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+create index idx3 on worklog5743_8(a2(3072));
+show warnings;
+Level Code Message
+create index idx4 on worklog5743_8(a1, a2(3069));
+ERROR 42000: Specified key was too long; max key length is 3072 bytes
+show warnings;
+Level Code Message
+Error 1071 Specified key was too long; max key length is 3072 bytes
+create index idx5 on worklog5743_8(a1, a2(3068));
+show warnings;
+Level Code Message
+create index idx6 on worklog5743_8(a1, a2(2000), a3(1069));
+ERROR 42000: Specified key was too long; max key length is 3072 bytes
+show warnings;
+Level Code Message
+Error 1071 Specified key was too long; max key length is 3072 bytes
+create index idx7 on worklog5743_8(a1, a2(2000), a3(1068));
+show warnings;
+Level Code Message
+insert into worklog5743_1 values(9, repeat("a", 10000));
+insert into worklog5743_2 values(9, repeat("a", 10000));
+insert into worklog5743_4 values(9, repeat("a", 10000));
+insert into worklog5743_8 values(9, repeat("a", 10000), repeat("a", 10000));
+set global innodb_large_prefix=0;
+insert into worklog5743_1 values(2, repeat("b", 10000));
+insert into worklog5743_2 values(2, repeat("b", 10000));
+insert into worklog5743_4 values(2, repeat("b", 10000));
+insert into worklog5743_8 values(2, repeat("b", 10000), repeat("b", 10000));
+set global innodb_large_prefix=1;
+select a1, left(a2, 20) from worklog5743_1;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+2 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_2;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+2 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_4;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+2 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_8;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+2 bbbbbbbbbbbbbbbbbbbb
+begin;
+update worklog5743_1 set a1 = 1000;
+update worklog5743_2 set a1 = 1000;
+update worklog5743_4 set a1 = 1000;
+update worklog5743_8 set a1 = 1000;
+select a1, left(a2, 20) from worklog5743_1;
+a1 left(a2, 20)
+1000 aaaaaaaaaaaaaaaaaaaa
+1000 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_2;
+a1 left(a2, 20)
+1000 aaaaaaaaaaaaaaaaaaaa
+1000 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_4;
+a1 left(a2, 20)
+1000 aaaaaaaaaaaaaaaaaaaa
+1000 bbbbbbbbbbbbbbbbbbbb
+select a1, left(a2, 20) from worklog5743_8;
+a1 left(a2, 20)
+1000 aaaaaaaaaaaaaaaaaaaa
+1000 bbbbbbbbbbbbbbbbbbbb
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743_1 ref idx6 idx6 5 const 1
+explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743_2 ref idx6 idx6 5 const 1
+explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743_4 ref idx6 idx6 5 const 1
+explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743_8 ref idx5,idx7 idx5 5 const 1
+select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
+a1 left(a2, 20)
+9 aaaaaaaaaaaaaaaaaaaa
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+a1 left(a2, 20)
+select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+a1 left(a2, 20)
+select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+a1 left(a2, 20)
+select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
+a1 left(a2, 20)
+rollback;
+drop table worklog5743_1;
+drop table worklog5743_2;
+drop table worklog5743_4;
+drop table worklog5743_8;
+### Test 5 ###
+create table worklog5743(a1 int, a2 varchar(20000)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+Warnings:
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+insert into worklog5743 values(9, repeat("a", 20000));
+update worklog5743 set a1 = 1000;
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(3073)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+Warnings:
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+show warnings;
+Level Code Message
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+insert into worklog5743 values(9, repeat("a", 3073));
+update worklog5743 set a1 = 1000;
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(3072)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+show warnings;
+Level Code Message
+insert into worklog5743 values(9, repeat("a", 3072));
+update worklog5743 set a1 = 1000;
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(3069)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a1, a2);
+ERROR 42000: Specified key was too long; max key length is 3072 bytes
+show warnings;
+Level Code Message
+Error 1071 Specified key was too long; max key length is 3072 bytes
+drop table worklog5743;
+create table worklog5743(a1 int, a2 varchar(3068)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a1, a2);
+show warnings;
+Level Code Message
+insert into worklog5743 values(9, repeat("a", 3072));
+Warnings:
+Warning 1265 Data truncated for column 'a2' at row 1
+update worklog5743 set a1 = 1000;
+begin;
+update worklog5743 set a1 = 1000;
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+explain select a1 from worklog5743 where a1 = 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE worklog5743 ref idx1 idx1 5 const 1 Using index
+select a1 from worklog5743 where a1 = 9;
+a1
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a1 from worklog5743 where a1 = 9;
+a1
+rollback;
+drop table worklog5743;
+### Test 6 ###
+create table worklog5743(a TEXT not null, primary key (a(1000)));
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
+create table worklog5743(a TEXT);
+create index idx on worklog5743(a(768));
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
+create index idx on worklog5743(a(767));
+insert into worklog5743 values(repeat("a", 20000));
+begin;
+insert into worklog5743 values(repeat("b", 20000));
+update worklog5743 set a = (repeat("x", 25000));
+select @@session.tx_isolation;
+@@session.tx_isolation
+REPEATABLE-READ
+select a = repeat("a", 20000) from worklog5743;
+a = repeat("a", 20000)
+1
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+@@session.tx_isolation
+READ-UNCOMMITTED
+select a = repeat("x", 25000) from worklog5743;
+a = repeat("x", 25000)
+1
+1
+rollback;
+drop table worklog5743;
+### Test 7 ###
+create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a(3073));
+Warnings:
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+Warning 1071 Specified key was too long; max key length is 3072 bytes
+create index idx2 on worklog5743(a(3072));
+show create table worklog5743;
+Table Create Table
+worklog5743 CREATE TABLE `worklog5743` (
+ `a` text NOT NULL,
+ KEY `idx1` (`a`(3072)),
+ KEY `idx2` (`a`(3072))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+drop table worklog5743;
+create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
+create index idx on worklog5743(a(768));
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
+create index idx2 on worklog5743(a(767));
+drop table worklog5743;
+create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
+create index idx on worklog5743(a(768));
+ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
+create index idx2 on worklog5743(a(767));
+drop table worklog5743;
+SET GLOBAL innodb_file_format=Antelope;
+SET GLOBAL innodb_file_per_table=0;
+SET GLOBAL innodb_file_format_max=Antelope;
+SET GLOBAL innodb_large_prefix=0;
=== modified file 'mysql-test/suite/innodb/t/innodb.test'
--- a/mysql-test/suite/innodb/t/innodb.test revid:inaam.rana@stripped
+++ b/mysql-test/suite/innodb/t/innodb.test revid:kevin.lewis@stripped
@@ -1315,7 +1315,7 @@ drop table t1;
# Test for testable InnoDB status variables. This test
# uses previous ones(pages_created, rows_deleted, ...).
---replace_result 512 {checked_valid} 1024 {checked_valid} 2048 {checked_valid}
+--replace_result 511 {checked_valid} 512 {checked_valid} 1024 {checked_valid} 2048 {checked_valid}
SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
--replace_result 4096 {checked_valid} 8192 {checked_valid} 16384 {checked_valid}
SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size';
=== modified file 'mysql-test/suite/innodb/t/innodb_16k.test'
--- a/mysql-test/suite/innodb/t/innodb_16k.test revid:inaam.rana@stripped
+++ b/mysql-test/suite/innodb/t/innodb_16k.test revid:kevin.lewis@stripped
@@ -15,20 +15,22 @@ LET $innodb_strict_mode_orig = `select @
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
---echo # Test 1) Show the page size from Inofrmation Schema
+--echo # Test 1) Show the page size from Information Schema
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_page_size';
---echo # Test 2) With 4k pages, the number of buffer pool pages is different.
+--echo # Test 2) The number of buffer pool pages is dependent upon the page size.
+--replace_result 511 {checked_511_or_512} 512 {checked_511_or_512}
+
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
---echo # Test 3) With 4k pages, the root page numbers are different.
+--echo # Test 3) The root page numbers are dependent upon the page size.
--echo # Pulled from innodb-system-table-view.test
SELECT NAME, TABLE_ID, PAGE_NO FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES;
---echo # Test 4) With 4k pages, the maximum row size is less than for
---echo # larger pages; Redundant: 8123, Compact: 8126.
+--echo # Test 4) The maximum row size is dependent upon the page size.
+--echo # Redundant: 8123, Compact: 8126.
--echo # Compressed: 8126, Dynamic: 8126.
--echo # Each row format has its own amount of overhead that
--echo # varies depending on number of fields and other overhead.
=== modified file 'mysql-test/suite/innodb/t/innodb_4k.test'
--- a/mysql-test/suite/innodb/t/innodb_4k.test revid:inaam.rana@stripped
+++ b/mysql-test/suite/innodb/t/innodb_4k.test revid:kevin.lewis@stripped
@@ -15,20 +15,20 @@ LET $innodb_strict_mode_orig = `select @
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
---echo # Test 1) Show the page size from Inofrmation Schema
+--echo # Test 1) Show the page size from Information Schema
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_page_size';
---echo # Test 2) With 4k pages, the number of buffer pool pages is different.
+--echo # Test 2) The number of buffer pool pages is dependent upon the page size.
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
---echo # Test 3) With 4k pages, the root page numbers are different.
+--echo # Test 3) The root page numbers are dependent upon the page size.
--echo # Pulled from innodb-system-table-view.test
SELECT NAME, TABLE_ID, PAGE_NO FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES;
---echo # Test 4) With 4k pages, the maximum row size is less than for
---echo # larger pages; Redundant: 1979, Compact: 1982.
+--echo # Test 4) The maximum row size is dependent upon the page size.
+--echo # Redundant: 1979, Compact: 1982.
--echo # Compressed: 1982, Dynamic: 1982.
--echo # Each row format has its own amount of overhead that
--echo # varies depending on number of fields and other overhead.
=== modified file 'mysql-test/suite/innodb/t/innodb_8k.test'
--- a/mysql-test/suite/innodb/t/innodb_8k.test revid:inaam.rana@stripped
+++ b/mysql-test/suite/innodb/t/innodb_8k.test revid:kevin.lewis@stripped
@@ -15,20 +15,20 @@ LET $innodb_strict_mode_orig = `select @
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
---echo # Test 1) Show the page size from Inofrmation Schema
+--echo # Test 1) Show the page size from Information Schema
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_page_size';
---echo # Test 2) With 8k pages, the number of buffer pool pages is different.
+--echo # Test 2) The number of buffer pool pages is dependent upon the page size.
SELECT variable_value FROM information_schema.global_status
WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
---echo # Test 3) With 8k pages, the root page numbers are different.
+--echo # Test 3) The root page numbers are dependent upon the page size.
--echo # Pulled from innodb-system-table-view.test
SELECT NAME, TABLE_ID, PAGE_NO FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES;
---echo # Test 4) With 8k pages, the maximum row size is less than for
---echo # larger pages; Redundant: 4027, Compact: 4030.
+--echo # Test 4) The maximum row size is dependent upon the page size.
+--echo # Redundant: 4027, Compact: 4030.
--echo # Compressed: 4030, Dynamic: 4030.
--echo # Each row format has its own amount of overhead that
--echo # varies depending on number of fields and other overhead.
=== added file 'mysql-test/suite/innodb/t/innodb_index_large_prefix_4k.test'
--- a/mysql-test/suite/innodb/t/innodb_index_large_prefix_4k.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb_index_large_prefix_4k.test revid:kevin.lewis@stripped
@@ -0,0 +1,420 @@
+# Testcase for worklog #5743: Lift the limit of index key prefixes
+
+--source include/have_innodb.inc
+--source include/have_innodb_4k.inc
+SET default_storage_engine=InnoDB;
+
+let $innodb_file_format_orig=`select @@innodb_file_format`;
+let $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
+let $innodb_file_format_max_orig=`select @@innodb_file_format_max`;
+let $innodb_large_prefix_orig=`select @@innodb_large_prefix`;
+
+set global innodb_file_format="Barracuda";
+set global innodb_file_per_table=1;
+set global innodb_large_prefix=1;
+
+-- echo ### Test 1 ###
+# Create a table of DYNAMIC format, with a primary index of 1000 bytes in
+# size
+create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC;
+show warnings;
+
+# Do some insertion and update to excercise the external cache
+# code path
+insert into worklog5743 values(repeat("a", 20000));
+
+# default session, update the table
+update worklog5743 set a = (repeat("b", 16000));
+
+# Create a secondary index
+create index idx on worklog5743(a(900));
+show warnings;
+
+# Start a few sessions to do selections on table being updated in default
+# session, so it would rebuild the previous version from undo log.
+# 1) Default session: Initiate an update on the externally stored column
+# 2) Session con1: Select from table with repeated read
+# 3) Session con2: Select from table with read uncommitted
+# 4) Default session: rollback updates
+
+begin;
+update worklog5743 set a = (repeat("x", 17000));
+
+# Start a new session to select the column to force it build
+# an earlier version of the clustered index through undo log. So it should
+# just see the result of repeat("b", 16000)
+select @@session.tx_isolation;
+--connect (con1,localhost,root,,)
+select a = repeat("x", 17000) from worklog5743;
+select a = repeat("b", 16000) from worklog5743;
+
+# Start another session doing "read uncommitted" query, it
+# should see the uncommitted update
+--connect (con2,localhost,root,,)
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a = repeat("x", 17000) from worklog5743;
+
+# Roll back the transaction
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 2 ###
+# Create a table with only a secondary index has large prefix column
+create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
+show warnings;
+create index idx on worklog5743(a1, a2(900));
+show warnings;
+
+insert into worklog5743 values(9, repeat("a", 10000));
+
+begin;
+
+update worklog5743 set a1 = 1111;
+
+# Do a select from another connection that would use the secondary index
+--connection con1
+select @@session.tx_isolation;
+explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+
+# Do read uncommitted in another session, it would show there is no
+# row with a1 = 9
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 3 ###
+# Create a table with a secondary index has small (50 bytes) prefix column
+create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
+
+create index idx on worklog5743(a1, a2(50));
+
+insert into worklog5743 values(9, repeat("a", 10000));
+
+begin;
+
+update worklog5743 set a1 = 2222;
+
+# Do a select from another connection that would use the secondary index
+--connection con1
+select @@session.tx_isolation;
+explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+
+# Do read uncommitted in another session, it would show there is no
+# row with a1 = 9
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 4 ###
+# Create compressed tables with each KEY_BLOCK_SIZE.
+create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1;
+create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
+create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
+
+# The maximum overall index record (not prefix) length of a
+# compressed table is dependent on innodb-page-size (IPS),
+# key_block_size (KBS) and the number of fields (NF).
+# "Too big row" error (HA_ERR_TO_BIG_ROW) will be returned if this
+# limit is exceeded.
+# See page_zip_empty_size() and Bug #47495 for more detail.
+
+# Test edge cases for indexes using key_block_size=1
+set global innodb_large_prefix=0;
+-- error 139
+create index idx1 on worklog5743_1(a2(4000));
+show warnings;
+set global innodb_large_prefix=1;
+-- error 139
+create index idx2 on worklog5743_1(a2(4000));
+show warnings;
+-- error 139
+create index idx3 on worklog5743_1(a2(436));
+show warnings;
+create index idx4 on worklog5743_1(a2(435));
+show warnings;
+-- error 139
+create index idx5 on worklog5743_1(a1, a2(430));
+show warnings;
+create index idx6 on worklog5743_1(a1, a2(429));
+show warnings;
+
+# Test edge cases for indexes using key_block_size=2
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_2(a2(4000));
+show warnings;
+set global innodb_large_prefix=1;
+-- error 139
+create index idx2 on worklog5743_2(a2(4000));
+show warnings;
+-- error 139
+create index idx3 on worklog5743_2(a2(948));
+show warnings;
+create index idx4 on worklog5743_2(a2(947));
+show warnings;
+-- error 139
+create index idx5 on worklog5743_2(a1, a2(942));
+show warnings;
+create index idx6 on worklog5743_2(a1, a2(941));
+show warnings;
+
+# Test edge cases for indexes using key_block_size=4
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_4(a2(4000));
+show warnings;
+set global innodb_large_prefix=1;
+-- error 139
+create index idx2 on worklog5743_4(a2(4000));
+show warnings;
+-- error 139
+create index idx3 on worklog5743_4(a2(1965));
+show warnings;
+create index idx4 on worklog5743_4(a2(1964));
+show warnings;
+-- error 139
+create index idx5 on worklog5743_4(a1, a2(1960));
+show warnings;
+create index idx6 on worklog5743_4(a1, a2(1959));
+show warnings;
+
+# Insert a large record into each of these tables.
+insert into worklog5743_1 values(9, repeat("a", 10000));
+insert into worklog5743_2 values(9, repeat("a", 10000));
+insert into worklog5743_4 values(9, repeat("a", 10000));
+
+# Now if we change the global innodb_large_prefix back to 767,
+# updates to these indexes should still be allowed.
+set global innodb_large_prefix=0;
+insert into worklog5743_1 values(2, repeat("b", 10000));
+insert into worklog5743_2 values(2, repeat("b", 10000));
+insert into worklog5743_4 values(2, repeat("b", 10000));
+set global innodb_large_prefix=1;
+
+select a1, left(a2, 20) from worklog5743_1;
+select a1, left(a2, 20) from worklog5743_2;
+select a1, left(a2, 20) from worklog5743_4;
+
+begin;
+
+update worklog5743_1 set a1 = 1000;
+update worklog5743_2 set a1 = 1000;
+update worklog5743_4 set a1 = 1000;
+select a1, left(a2, 20) from worklog5743_1;
+select a1, left(a2, 20) from worklog5743_2;
+select a1, left(a2, 20) from worklog5743_4;
+
+# Do a select from another connection that would use the secondary index
+--connection con1
+select @@session.tx_isolation;
+explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+
+# Do read uncommitted in another session, it would show there is no
+# row with a1 = 9
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+
+--connection default
+rollback;
+
+drop table worklog5743_1;
+drop table worklog5743_2;
+drop table worklog5743_4;
+
+-- echo ### Test 5 ###
+# Create a table with large varchar columns and create indexes
+# directly on these large columns to show that prefix limit is
+# automatically applied and to show that limit.
+
+# This commented form of the test causes an unlimited page split
+# on update of the int field - Bug 12636590 - INNODB; UPDATE OF
+# LARGE RECORD CAUSES UNLIMITED PAGE SPLITS IN 8K PAGE SIZE
+#create table worklog5743(a1 int,
+# a2 varchar(20000),
+# a3 varchar(3073),
+# a4 varchar(3072),
+# a5 varchar(3069),
+# a6 varchar(3068))
+# ROW_FORMAT=DYNAMIC;
+#create index idx1 on worklog5743(a2);
+#create index idx2 on worklog5743(a3);
+#create index idx3 on worklog5743(a4);
+#show warnings;
+#-- error ER_TOO_LONG_KEY
+#create index idx4 on worklog5743(a1, a2);
+#show warnings;
+#-- error ER_TOO_LONG_KEY
+#create index idx5 on worklog5743(a1, a5);
+#show warnings;
+#create index idx6 on worklog5743(a1, a6);
+#show warnings;
+#show create table worklog5743;
+#
+#insert into worklog5743 values(9,
+# repeat("a", 20000), repeat("a", 3073),
+# repeat("a", 3072), repeat("a", 3069),
+# repeat("a", 3068));
+#
+
+create table worklog5743(a1 int, a2 varchar(20000)) ROW_FORMAT=DYNAMIC;
+-- error 139
+create index idx1 on worklog5743(a2);
+show warnings;
+drop table worklog5743;
+
+create table worklog5743(a1 int, a2 varchar(3072)) ROW_FORMAT=DYNAMIC;
+-- error 139
+create index idx1 on worklog5743(a2);
+show warnings;
+drop table worklog5743;
+
+create table worklog5743(a1 int, a2 varchar(1964)) ROW_FORMAT=DYNAMIC;
+-- error 139
+create index idx1 on worklog5743(a2);
+show warnings;
+drop table worklog5743;
+
+create table worklog5743(a1 int, a2 varchar(1963)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+show warnings;
+insert into worklog5743 values(9, repeat("a", 1963));
+update worklog5743 set a1 = 3333;
+drop table worklog5743;
+
+create table worklog5743(a1 int, a2 varchar(1960)) ROW_FORMAT=DYNAMIC;
+-- error 139
+create index idx1 on worklog5743(a1, a2);
+show warnings;
+drop table worklog5743;
+
+create table worklog5743(a1 int, a2 varchar(1959)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a1, a2);
+show warnings;
+insert into worklog5743 values(9, repeat("a", 1959));
+# Bug 12637786 - INNODB; ASSERT WITH LARGE RECORDS USING LARGE INDEX PREFIXES
+# will hapen if we update this table.
+# update worklog5743 set a1 = 4444;
+drop table worklog5743;
+
+# Use a large index prefix, but smaller than the max to avoid Bug 12637786
+create table worklog5743(a1 int, a2 varchar(1950)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a1, a2);
+show warnings;
+insert into worklog5743 values(9, repeat("a", 1950));
+
+begin;
+update worklog5743 set a1 = 4444;
+
+# Do a select from another connection that would use the secondary index
+--connection con1
+select @@session.tx_isolation;
+explain select a1 from worklog5743 where a1 = 9;
+select a1 from worklog5743 where a1 = 9;
+
+# Do read uncommitted, it would show there is no row with a1 = 9
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a1 from worklog5743 where a1 = 9;
+
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 6 ###
+# Create a table with old format, and the limit is 768 bytes.
+-- error ER_INDEX_COLUMN_TOO_LONG
+create table worklog5743(a TEXT not null, primary key (a(1000)));
+
+create table worklog5743(a TEXT);
+
+# Excercise the column length check in ha_innobase::add_index()
+-- error ER_INDEX_COLUMN_TOO_LONG
+create index idx on worklog5743(a(768));
+
+# This should be successful
+create index idx on worklog5743(a(767));
+
+# Perform some DMLs
+insert into worklog5743 values(repeat("a", 20000));
+
+begin;
+insert into worklog5743 values(repeat("b", 20000));
+update worklog5743 set a = (repeat("x", 25000));
+
+# Start a new session to select the table to force it build
+# an earlier version of the cluster index through undo log
+select @@session.tx_isolation;
+--connection con1
+select a = repeat("a", 20000) from worklog5743;
+
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a = repeat("x", 25000) from worklog5743;
+
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 7 ###
+# Some border line tests on the column length.
+# We have a limit of 3072 bytes for Barracuda table
+create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
+
+# Length exceeds maximum supported key length
+# It will be auto-truncated to 3072 if the page size were not 4k.
+# With this page size, the prefix length is less.
+-- error 139
+create index idx1 on worklog5743(a(1965));
+show warnings;
+create index idx2 on worklog5743(a(1964));
+show warnings;
+show create table worklog5743;
+insert into worklog5743 values(repeat("a", 1964));
+drop table worklog5743;
+
+# We have a limit of 767 bytes for Antelope tables
+create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
+-- error ER_INDEX_COLUMN_TOO_LONG
+create index idx on worklog5743(a(768));
+create index idx2 on worklog5743(a(767));
+drop table worklog5743;
+
+create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
+-- error ER_INDEX_COLUMN_TOO_LONG
+create index idx on worklog5743(a(768));
+create index idx2 on worklog5743(a(767));
+drop table worklog5743;
+
+
+eval SET GLOBAL innodb_file_format=$innodb_file_format_orig;
+eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
+eval SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig;
+eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig;
=== added file 'mysql-test/suite/innodb/t/innodb_index_large_prefix_8k.test'
--- a/mysql-test/suite/innodb/t/innodb_index_large_prefix_8k.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb_index_large_prefix_8k.test revid:kevin.lewis@stripped
@@ -0,0 +1,433 @@
+# Testcase for worklog #5743: Lift the limit of index key prefixes
+
+--source include/have_innodb.inc
+--source include/have_innodb_8k.inc
+SET default_storage_engine=InnoDB;
+
+let $innodb_file_format_orig=`select @@innodb_file_format`;
+let $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
+let $innodb_file_format_max_orig=`select @@innodb_file_format_max`;
+let $innodb_large_prefix_orig=`select @@innodb_large_prefix`;
+
+set global innodb_file_format="Barracuda";
+set global innodb_file_per_table=1;
+set global innodb_large_prefix=1;
+
+-- echo ### Test 1 ###
+# Create a table of DYNAMIC format, with a primary index of 1000 bytes in
+# size
+create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC;
+show warnings;
+
+# Do some insertion and update to excercise the external cache
+# code path
+insert into worklog5743 values(repeat("a", 20000));
+
+# default session, update the table
+update worklog5743 set a = (repeat("b", 16000));
+
+# Create a secondary index
+create index idx on worklog5743(a(2000));
+show warnings;
+
+# Start a few sessions to do selections on table being updated in default
+# session, so it would rebuild the previous version from undo log.
+# 1) Default session: Initiate an update on the externally stored column
+# 2) Session con1: Select from table with repeated read
+# 3) Session con2: Select from table with read uncommitted
+# 4) Default session: rollback updates
+
+begin;
+update worklog5743 set a = (repeat("x", 17000));
+
+# Start a new session to select the column to force it build
+# an earlier version of the clustered index through undo log. So it should
+# just see the result of repeat("b", 16000)
+select @@session.tx_isolation;
+--connect (con1,localhost,root,,)
+select a = repeat("x", 17000) from worklog5743;
+select a = repeat("b", 16000) from worklog5743;
+
+# Start another session doing "read uncommitted" query, it
+# should see the uncommitted update
+--connect (con2,localhost,root,,)
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a = repeat("x", 17000) from worklog5743;
+
+# Roll back the transaction
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 2 ###
+# Create a table with only a secondary index has large prefix column
+create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
+show warnings;
+create index idx on worklog5743(a1, a2(2000));
+show warnings;
+
+insert into worklog5743 values(9, repeat("a", 10000));
+
+begin;
+
+update worklog5743 set a1 = 1000;
+
+# Do a select from another connection that would use the secondary index
+--connection con1
+select @@session.tx_isolation;
+explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+
+# Do read uncommitted in another session, it would show there is no
+# row with a1 = 9
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 3 ###
+# Create a table with a secondary index has small (50 bytes) prefix column
+create table worklog5743(a1 int, a2 TEXT not null) ROW_FORMAT=DYNAMIC;
+
+create index idx on worklog5743(a1, a2(50));
+
+insert into worklog5743 values(9, repeat("a", 10000));
+
+begin;
+
+update worklog5743 set a1 = 1000;
+
+# Do a select from another connection that would use the secondary index
+--connection con1
+select @@session.tx_isolation;
+explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+
+# Do read uncommitted in another session, it would show there is no
+# row with a1 = 9
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9;
+
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 4 ###
+# Create compressed tables with each KEY_BLOCK_SIZE.
+create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1;
+create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
+create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
+create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8;
+
+# The maximum overall index record (not prefix) length of a
+# compressed table is dependent on innodb-page-size (IPS),
+# key_block_size (KBS) and the number of fields (NF).
+# "Too big row" error (HA_ERR_TO_BIG_ROW) will be returned if this
+# limit is exceeded.
+# See page_zip_empty_size() and Bug #47495 for more detail.
+
+# Test edge cases for indexes using key_block_size=1
+set global innodb_large_prefix=0;
+-- error 139
+create index idx1 on worklog5743_1(a2(4000));
+show warnings;
+set global innodb_large_prefix=1;
+-- error 139
+create index idx2 on worklog5743_1(a2(4000));
+show warnings;
+-- error 139
+create index idx3 on worklog5743_1(a2(436));
+show warnings;
+create index idx4 on worklog5743_1(a2(435));
+show warnings;
+-- error 139
+create index idx5 on worklog5743_1(a1, a2(430));
+show warnings;
+create index idx6 on worklog5743_1(a1, a2(429));
+show warnings;
+
+# Test edge cases for indexes using key_block_size=2
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_2(a2(4000));
+show warnings;
+set global innodb_large_prefix=1;
+-- error 139
+create index idx2 on worklog5743_2(a2(4000));
+show warnings;
+-- error 139
+create index idx3 on worklog5743_2(a2(948));
+show warnings;
+create index idx4 on worklog5743_2(a2(947));
+show warnings;
+-- error 139
+create index idx5 on worklog5743_2(a1, a2(942));
+show warnings;
+create index idx6 on worklog5743_2(a1, a2(941));
+show warnings;
+
+# Test edge cases for indexes using key_block_size=4
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_4(a2(4000));
+show warnings;
+set global innodb_large_prefix=1;
+-- error 139
+create index idx2 on worklog5743_4(a2(4000));
+show warnings;
+-- error 139
+create index idx3 on worklog5743_4(a2(1972));
+show warnings;
+create index idx4 on worklog5743_4(a2(1971));
+show warnings;
+-- error 139
+create index idx5 on worklog5743_4(a1, a2(1966));
+show warnings;
+create index idx6 on worklog5743_4(a1, a2(1965));
+show warnings;
+
+# Test edge cases for indexes using key_block_size=8
+set global innodb_large_prefix=0;
+create index idx1 on worklog5743_8(a2(1000));
+show warnings;
+set global innodb_large_prefix=1;
+create index idx2 on worklog5743_8(a2(3073));
+show warnings;
+create index idx3 on worklog5743_8(a2(3072));
+show warnings;
+-- error ER_TOO_LONG_KEY
+create index idx4 on worklog5743_8(a1, a2(3069));
+show warnings;
+create index idx5 on worklog5743_8(a1, a2(3068));
+show warnings;
+-- error ER_TOO_LONG_KEY
+create index idx6 on worklog5743_8(a1, a2(2000), a3(1069));
+show warnings;
+create index idx7 on worklog5743_8(a1, a2(2000), a3(1068));
+show warnings;
+
+# Insert a large record into each of these tables.
+insert into worklog5743_1 values(9, repeat("a", 10000));
+insert into worklog5743_2 values(9, repeat("a", 10000));
+insert into worklog5743_4 values(9, repeat("a", 10000));
+insert into worklog5743_8 values(9, repeat("a", 10000), repeat("a", 10000));
+
+# Now if we change the global innodb_large_prefix back to 767,
+# updates to these indexes should still be allowed.
+set global innodb_large_prefix=0;
+insert into worklog5743_1 values(2, repeat("b", 10000));
+insert into worklog5743_2 values(2, repeat("b", 10000));
+insert into worklog5743_4 values(2, repeat("b", 10000));
+insert into worklog5743_8 values(2, repeat("b", 10000), repeat("b", 10000));
+set global innodb_large_prefix=1;
+
+select a1, left(a2, 20) from worklog5743_1;
+select a1, left(a2, 20) from worklog5743_2;
+select a1, left(a2, 20) from worklog5743_4;
+select a1, left(a2, 20) from worklog5743_8;
+
+begin;
+
+update worklog5743_1 set a1 = 1000;
+update worklog5743_2 set a1 = 1000;
+update worklog5743_4 set a1 = 1000;
+update worklog5743_8 set a1 = 1000;
+select a1, left(a2, 20) from worklog5743_1;
+select a1, left(a2, 20) from worklog5743_2;
+select a1, left(a2, 20) from worklog5743_4;
+select a1, left(a2, 20) from worklog5743_8;
+
+
+# Do a select from another connection that would use the secondary index
+--connection con1
+select @@session.tx_isolation;
+explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+explain select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
+
+# Do read uncommitted in another session, it would show there is no
+# row with a1 = 9
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a1, left(a2, 20) from worklog5743_1 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_2 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_4 where a1 = 9;
+select a1, left(a2, 20) from worklog5743_8 where a1 = 9;
+
+--connection default
+rollback;
+
+drop table worklog5743_1;
+drop table worklog5743_2;
+drop table worklog5743_4;
+drop table worklog5743_8;
+
+-- echo ### Test 5 ###
+# Create a table with large varchar columns and create indexes
+# directly on these large columns to show that prefix limit is
+# automatically applied and to show that limit.
+
+# This commented form of the test causes an unlimited page split
+# on update of the int field - Bug 12636590 - INNODB; UPDATE OF
+# LARGE RECORD CAUSES UNLIMITED PAGE SPLITS IN 8K PAGE SIZE
+#create table worklog5743(a1 int,
+# a2 varchar(20000),
+# a3 varchar(3073),
+# a4 varchar(3072),
+# a5 varchar(3069),
+# a6 varchar(3068))
+# ROW_FORMAT=DYNAMIC;
+#create index idx1 on worklog5743(a2);
+#create index idx2 on worklog5743(a3);
+#create index idx3 on worklog5743(a4);
+#show warnings;
+#-- error ER_TOO_LONG_KEY
+#create index idx4 on worklog5743(a1, a2);
+#show warnings;
+#-- error ER_TOO_LONG_KEY
+#create index idx5 on worklog5743(a1, a5);
+#show warnings;
+#create index idx6 on worklog5743(a1, a6);
+#show warnings;
+#show create table worklog5743;
+#
+#insert into worklog5743 values(9,
+# repeat("a", 20000), repeat("a", 3073),
+# repeat("a", 3072), repeat("a", 3069),
+# repeat("a", 3068));
+#
+
+create table worklog5743(a1 int, a2 varchar(20000)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+show warnings;
+insert into worklog5743 values(9, repeat("a", 20000));
+update worklog5743 set a1 = 1000;
+drop table worklog5743;
+
+create table worklog5743(a1 int, a2 varchar(3073)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+show warnings;
+insert into worklog5743 values(9, repeat("a", 3073));
+update worklog5743 set a1 = 1000;
+drop table worklog5743;
+
+create table worklog5743(a1 int, a2 varchar(3072)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a2);
+show warnings;
+insert into worklog5743 values(9, repeat("a", 3072));
+update worklog5743 set a1 = 1000;
+drop table worklog5743;
+
+create table worklog5743(a1 int, a2 varchar(3069)) ROW_FORMAT=DYNAMIC;
+-- error ER_TOO_LONG_KEY
+create index idx1 on worklog5743(a1, a2);
+show warnings;
+drop table worklog5743;
+
+create table worklog5743(a1 int, a2 varchar(3068)) ROW_FORMAT=DYNAMIC;
+create index idx1 on worklog5743(a1, a2);
+show warnings;
+insert into worklog5743 values(9, repeat("a", 3072));
+update worklog5743 set a1 = 1000;
+
+begin;
+update worklog5743 set a1 = 1000;
+
+# Do a select from another connection that would use the secondary index
+--connection con1
+select @@session.tx_isolation;
+explain select a1 from worklog5743 where a1 = 9;
+select a1 from worklog5743 where a1 = 9;
+
+# Do read uncommitted, it would show there is no row with a1 = 9
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a1 from worklog5743 where a1 = 9;
+
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 6 ###
+# Create a table with old format, and the limit is 768 bytes.
+-- error ER_INDEX_COLUMN_TOO_LONG
+create table worklog5743(a TEXT not null, primary key (a(1000)));
+
+create table worklog5743(a TEXT);
+
+# Excercise the column length check in ha_innobase::add_index()
+-- error ER_INDEX_COLUMN_TOO_LONG
+create index idx on worklog5743(a(768));
+
+# This should be successful
+create index idx on worklog5743(a(767));
+
+# Perform some DMLs
+insert into worklog5743 values(repeat("a", 20000));
+
+begin;
+insert into worklog5743 values(repeat("b", 20000));
+update worklog5743 set a = (repeat("x", 25000));
+
+# Start a new session to select the table to force it build
+# an earlier version of the cluster index through undo log
+select @@session.tx_isolation;
+--connection con1
+select a = repeat("a", 20000) from worklog5743;
+
+--connection con2
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+select @@session.tx_isolation;
+select a = repeat("x", 25000) from worklog5743;
+
+--connection default
+rollback;
+
+drop table worklog5743;
+
+-- echo ### Test 7 ###
+# Some border line tests on the column length.
+# We have a limit of 3072 bytes for Barracuda table
+create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC;
+
+# Length exceeds maximum supported key length
+# It will be auto-truncated to 3072
+create index idx1 on worklog5743(a(3073));
+create index idx2 on worklog5743(a(3072));
+show create table worklog5743;
+drop table worklog5743;
+
+# We have a limit of 767 bytes for Antelope tables
+create table worklog5743(a TEXT not null) ROW_FORMAT=REDUNDANT;
+-- error ER_INDEX_COLUMN_TOO_LONG
+create index idx on worklog5743(a(768));
+create index idx2 on worklog5743(a(767));
+drop table worklog5743;
+
+create table worklog5743(a TEXT not null) ROW_FORMAT=COMPACT;
+-- error ER_INDEX_COLUMN_TOO_LONG
+create index idx on worklog5743(a(768));
+create index idx2 on worklog5743(a(767));
+drop table worklog5743;
+
+
+eval SET GLOBAL innodb_file_format=$innodb_file_format_orig;
+eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
+eval SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig;
+eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (kevin.lewis:3257 to 3258) | kevin.lewis | 20 Jul |
