diff options
author | Jimmy Yang <jimmy.yang@oracle.com> | 2011-05-31 02:12:32 -0700 |
---|---|---|
committer | Jimmy Yang <jimmy.yang@oracle.com> | 2011-05-31 02:12:32 -0700 |
commit | 9e2b7fa7d5f0cbe4920be5567314b6de1af660a4 (patch) | |
tree | 21100612140d5618d083e91268a4594a0836953c /mysql-test/suite | |
parent | 53e9aabe126ad73845958818f5872fcd4425588c (diff) | |
download | mariadb-git-9e2b7fa7d5f0cbe4920be5567314b6de1af660a4.tar.gz |
Implement worklog #5743 InnoDB: Lift the limit of index key prefixes.
With this change, the index prefix column length lifted from 767 bytes
to 3072 bytes if "innodb_large_prefix" is set to "true".
rb://603 approved by Marko
Diffstat (limited to 'mysql-test/suite')
-rw-r--r-- | mysql-test/suite/innodb/r/innodb-index.result | 92 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_index_large_prefix.result | 185 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb-index.test | 50 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb_index_large_prefix.test | 271 | ||||
-rw-r--r-- | mysql-test/suite/sys_vars/r/all_vars.result | 2 |
5 files changed, 529 insertions, 71 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result index e43f70a2365..7d7062c9161 100644 --- a/mysql-test/suite/innodb/r/innodb-index.result +++ b/mysql-test/suite/innodb/r/innodb-index.result @@ -842,31 +842,31 @@ create table t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob, i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob, q blob,r blob,s blob,t blob,u blob) engine=innodb row_format=dynamic; -create index t1a on t1 (a(1)); -create index t1b on t1 (b(1)); -create index t1c on t1 (c(1)); -create index t1d on t1 (d(1)); -create index t1e on t1 (e(1)); -create index t1f on t1 (f(1)); -create index t1g on t1 (g(1)); -create index t1h on t1 (h(1)); -create index t1i on t1 (i(1)); -create index t1j on t1 (j(1)); -create index t1k on t1 (k(1)); -create index t1l on t1 (l(1)); -create index t1m on t1 (m(1)); -create index t1n on t1 (n(1)); -create index t1o on t1 (o(1)); -create index t1p on t1 (p(1)); -create index t1q on t1 (q(1)); -create index t1r on t1 (r(1)); -create index t1s on t1 (s(1)); -create index t1t on t1 (t(1)); -create index t1u on t1 (u(1)); +create index t1a on t1 (a(767)); +create index t1b on t1 (b(767)); +create index t1c on t1 (c(767)); +create index t1d on t1 (d(767)); +create index t1e on t1 (e(767)); +create index t1f on t1 (f(767)); +create index t1g on t1 (g(767)); +create index t1h on t1 (h(767)); +create index t1i on t1 (i(767)); +create index t1j on t1 (j(767)); +create index t1k on t1 (k(767)); +create index t1l on t1 (l(767)); +create index t1m on t1 (m(767)); +create index t1n on t1 (n(767)); +create index t1o on t1 (o(767)); +create index t1p on t1 (p(767)); +create index t1q on t1 (q(767)); +create index t1r on t1 (r(767)); +create index t1s on t1 (s(767)); +create index t1t on t1 (t(767)); +create index t1u on t1 (u(767)); ERROR HY000: Too big row -create index t1ut on t1 (u(1), t(1)); +create index t1ut on t1 (u(767), t(767)); ERROR HY000: Too big row -create index t1st on t1 (s(1), t(1)); +create index t1st on t1 (s(767), t(767)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -891,32 +891,32 @@ t1 CREATE TABLE `t1` ( `s` blob, `t` blob, `u` blob, - KEY `t1a` (`a`(1)), - KEY `t1b` (`b`(1)), - KEY `t1c` (`c`(1)), - KEY `t1d` (`d`(1)), - KEY `t1e` (`e`(1)), - KEY `t1f` (`f`(1)), - KEY `t1g` (`g`(1)), - KEY `t1h` (`h`(1)), - KEY `t1i` (`i`(1)), - KEY `t1j` (`j`(1)), - KEY `t1k` (`k`(1)), - KEY `t1l` (`l`(1)), - KEY `t1m` (`m`(1)), - KEY `t1n` (`n`(1)), - KEY `t1o` (`o`(1)), - KEY `t1p` (`p`(1)), - KEY `t1q` (`q`(1)), - KEY `t1r` (`r`(1)), - KEY `t1s` (`s`(1)), - KEY `t1t` (`t`(1)), - KEY `t1st` (`s`(1),`t`(1)) + KEY `t1a` (`a`(767)), + KEY `t1b` (`b`(767)), + KEY `t1c` (`c`(767)), + KEY `t1d` (`d`(767)), + KEY `t1e` (`e`(767)), + KEY `t1f` (`f`(767)), + KEY `t1g` (`g`(767)), + KEY `t1h` (`h`(767)), + KEY `t1i` (`i`(767)), + KEY `t1j` (`j`(767)), + KEY `t1k` (`k`(767)), + KEY `t1l` (`l`(767)), + KEY `t1m` (`m`(767)), + KEY `t1n` (`n`(767)), + KEY `t1o` (`o`(767)), + KEY `t1p` (`p`(767)), + KEY `t1q` (`q`(767)), + KEY `t1r` (`r`(767)), + KEY `t1s` (`s`(767)), + KEY `t1t` (`t`(767)), + KEY `t1st` (`s`(767),`t`(767)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC -create index t1u on t1 (u(1)); +create index t1u on t1 (u(767)); ERROR HY000: Too big row alter table t1 row_format=compact; -create index t1u on t1 (u(1)); +create index t1u on t1 (u(767)); drop table t1; set global innodb_file_per_table=0; set global innodb_file_format=Antelope; diff --git a/mysql-test/suite/innodb/r/innodb_index_large_prefix.result b/mysql-test/suite/innodb/r/innodb_index_large_prefix.result new file mode 100644 index 00000000000..6e2d3527ee9 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_index_large_prefix.result @@ -0,0 +1,185 @@ +set global innodb_file_format="Barracuda"; +set global innodb_file_per_table=1; +set global innodb_large_prefix=1; +create table worklog5743(a TEXT not null, primary key (a(1000))) +ROW_FORMAT=DYNAMIC, engine = innodb; +insert into worklog5743 values(repeat("a", 20000)); +update worklog5743 set a = (repeat("b", 16000)); +create index idx on worklog5743(a(2000)); +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; +create table worklog5743(a1 int, a2 TEXT not null) +ROW_FORMAT=DYNAMIC, engine = innodb; +create index idx on worklog5743(a1, a2(2000)); +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 Using where +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; +create table worklog5743(a1 int, a2 TEXT not null) +ROW_FORMAT=DYNAMIC, engine = innodb; +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 Using where +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; +create table worklog5743_2(a1 int, a2 TEXT not null) +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; +create table worklog5743_4(a1 int, a2 TEXT not null) +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; +create index idx1 on worklog5743_2(a1, a2(942)); +ERROR HY000: Too big row +create index idx1 on worklog5743_2(a1, a2(940)); +create index idx1 on worklog5743_4(a1, a2(1966)); +ERROR HY000: Too big row +create index idx1 on worklog5743_4(a1, a2(1964)); +insert into worklog5743_2 values(9, repeat("a", 10000)); +insert into worklog5743_4 values(9, repeat("a", 10000)); +begin; +update worklog5743_2 set a1 = 1000; +update worklog5743_4 set a1 = 1000; +select @@session.tx_isolation; +@@session.tx_isolation +REPEATABLE-READ +explain select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE worklog5743_2 ref idx1 idx1 5 const 1 Using where +select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +a1 a2 = repeat("a", 10000) +9 1 +select a1, a2 = repeat("a", 10000) from worklog5743_4 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_2 where a1 = 9; +a1 a2 = repeat("a", 10000) +select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; +a1 a2 = repeat("a", 10000) +rollback; +drop table worklog5743_2; +drop table worklog5743_4; +create table worklog5743(a1 int, a2 varchar(3000)) +ROW_FORMAT=DYNAMIC, engine = innodb; +create index idx on worklog5743(a1, a2); +insert into worklog5743 values(9, repeat("a", 3000)); +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 idx idx 5 const 1 Using where; 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; +create table worklog5743(a TEXT not null, primary key (a(1000))) +engine = innodb; +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +create table worklog5743(a TEXT) engine = innodb; +create index idx on worklog5743(a(1000)); +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +create index idx on worklog5743(a(725)); +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; +create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; +create index idx 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 `idx` (`a`(3072)), + KEY `idx2` (`a`(3072)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +drop table worklog5743; +create table worklog5743(a TEXT not null) engine = innodb; +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; diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index 05d1d37c422..baeeb240c06 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -369,36 +369,36 @@ create table t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob, i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob, q blob,r blob,s blob,t blob,u blob) engine=innodb row_format=dynamic; -create index t1a on t1 (a(1)); -create index t1b on t1 (b(1)); -create index t1c on t1 (c(1)); -create index t1d on t1 (d(1)); -create index t1e on t1 (e(1)); -create index t1f on t1 (f(1)); -create index t1g on t1 (g(1)); -create index t1h on t1 (h(1)); -create index t1i on t1 (i(1)); -create index t1j on t1 (j(1)); -create index t1k on t1 (k(1)); -create index t1l on t1 (l(1)); -create index t1m on t1 (m(1)); -create index t1n on t1 (n(1)); -create index t1o on t1 (o(1)); -create index t1p on t1 (p(1)); -create index t1q on t1 (q(1)); -create index t1r on t1 (r(1)); -create index t1s on t1 (s(1)); -create index t1t on t1 (t(1)); +create index t1a on t1 (a(767)); +create index t1b on t1 (b(767)); +create index t1c on t1 (c(767)); +create index t1d on t1 (d(767)); +create index t1e on t1 (e(767)); +create index t1f on t1 (f(767)); +create index t1g on t1 (g(767)); +create index t1h on t1 (h(767)); +create index t1i on t1 (i(767)); +create index t1j on t1 (j(767)); +create index t1k on t1 (k(767)); +create index t1l on t1 (l(767)); +create index t1m on t1 (m(767)); +create index t1n on t1 (n(767)); +create index t1o on t1 (o(767)); +create index t1p on t1 (p(767)); +create index t1q on t1 (q(767)); +create index t1r on t1 (r(767)); +create index t1s on t1 (s(767)); +create index t1t on t1 (t(767)); --error 139 -create index t1u on t1 (u(1)); +create index t1u on t1 (u(767)); --error 139 -create index t1ut on t1 (u(1), t(1)); -create index t1st on t1 (s(1), t(1)); +create index t1ut on t1 (u(767), t(767)); +create index t1st on t1 (s(767), t(767)); show create table t1; --error 139 -create index t1u on t1 (u(1)); +create index t1u on t1 (u(767)); alter table t1 row_format=compact; -create index t1u on t1 (u(1)); +create index t1u on t1 (u(767)); drop table t1; eval set global innodb_file_per_table=$per_table; diff --git a/mysql-test/suite/innodb/t/innodb_index_large_prefix.test b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test new file mode 100644 index 00000000000..3ed8aa6e096 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test @@ -0,0 +1,271 @@ +# Testcase for worklog #5743: Lift the limit of index key prefixes + +--source include/have_innodb.inc + +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; + +# 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, engine = innodb; + +# 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)); + +# 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; + +# Create a table with only a secondary index has large prefix column +create table worklog5743(a1 int, a2 TEXT not null) +ROW_FORMAT=DYNAMIC, engine = innodb; + +create index idx on worklog5743(a1, a2(2000)); + +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; + +# 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, engine = innodb; + +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; + +# Create a table of ROW_FORMAT=COMPRESSED format +create table worklog5743_2(a1 int, a2 TEXT not null) +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; + +create table worklog5743_4(a1 int, a2 TEXT not null) +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; + +# The maximum overall index record (not prefix) length for this table +# is page_zip_empty_size() / 2, which is 960. "Too big row" error ( +# HA_ERR_TO_BIG_ROW) will be printed if this limit is exceeded. +# Considering other fields and their overhead, the maximum length +# for column a2 is 940 or 941 depending on the zlib version used and +# compressBound() value used in page_zip_empty_size() (please refer +# to Bug #47495 for more detail). +-- error 139 +create index idx1 on worklog5743_2(a1, a2(942)); + +create index idx1 on worklog5743_2(a1, a2(940)); + +# similarly, the maximum index record length for the table is +# 1984. Considering other fields and their overhead, the +# maximum length for column a2 is 1964 or 1965 (please refer +# to Bug #47495 for more detail). +-- error 139 +create index idx1 on worklog5743_4(a1, a2(1966)); + +create index idx1 on worklog5743_4(a1, a2(1964)); + +insert into worklog5743_2 values(9, repeat("a", 10000)); +insert into worklog5743_4 values(9, repeat("a", 10000)); + +begin; + +update worklog5743_2 set a1 = 1000; +update worklog5743_4 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_2 where a1 = 9; +select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +select a1, a2 = repeat("a", 10000) 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, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; + +--connection default +rollback; + +drop table worklog5743_2; +drop table worklog5743_4; + +# Create a table with varchar column, and create index directly on this +# large column (without prefix) +create table worklog5743(a1 int, a2 varchar(3000)) +ROW_FORMAT=DYNAMIC, engine = innodb; + +# Create an index with large column without prefix +create index idx on worklog5743(a1, a2); + +insert into worklog5743 values(9, repeat("a", 3000)); + +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; + +# 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))) +engine = innodb; + +create table worklog5743(a TEXT) engine = innodb; + +# Excercise the column length check in ha_innobase::add_index() +-- error ER_INDEX_COLUMN_TOO_LONG +create index idx on worklog5743(a(1000)); + +# This should be successful +create index idx on worklog5743(a(725)); + +# 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; + +# Some border line test on the column length. +# We have a limit of 3072 bytes for Barracuda table +create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; + +# Length exceeds maximum supported key length, will auto-truncated to 3072 +create index idx 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 table +create table worklog5743(a TEXT not null) engine = innodb; + +-- 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; diff --git a/mysql-test/suite/sys_vars/r/all_vars.result b/mysql-test/suite/sys_vars/r/all_vars.result index fae91659ecd..3cee368853f 100644 --- a/mysql-test/suite/sys_vars/r/all_vars.result +++ b/mysql-test/suite/sys_vars/r/all_vars.result @@ -14,8 +14,10 @@ There should be *no* variables listed below: INNODB_ROLLBACK_SEGMENTS INNODB_STATS_METHOD INNODB_FILE_FORMAT_MAX +INNODB_LARGE_PREFIX INNODB_ROLLBACK_SEGMENTS INNODB_STATS_METHOD INNODB_FILE_FORMAT_MAX +INNODB_LARGE_PREFIX drop table t1; drop table t2; |