path: root/mysql-test/suite/innodb_zip/t/index_large_prefix_4k.test

# 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;

--disable_query_log
call mtr.add_suppression("Cannot add field .* in table .* because after adding it, the row size is");
SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
SET GLOBAL innodb_read_only_compressed=OFF;
--enable_query_log

-- echo ### Test 1 ###
# Create a table of DYNAMIC format, with a primary index of 768 bytes in
# size
create table worklog5743(a TEXT not null, primary key (a(768))) 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
SET sql_mode= '';
create index idx on worklog5743(a(900));
show warnings;
SET sql_mode= default;
# 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(750));
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_TOO_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
-- error ER_TOO_BIG_ROWSIZE
create index idx1 on worklog5743_1(a2(4000));
show warnings;
-- error ER_TOO_BIG_ROWSIZE
create index idx3 on worklog5743_1(a2(436));
show warnings;
# Bug#13391353 Limit is one byte less on on 32bit-Linux only
create index idx4 on worklog5743_1(a2(434));
show warnings;
-- error ER_TOO_BIG_ROWSIZE
create index idx5 on worklog5743_1(a1, a2(430));
show warnings;
# Bug#13391353 Limit is one byte less on on 32bit-Linux only
create index idx6 on worklog5743_1(a1, a2(428));
show warnings;

# Test edge cases for indexes using key_block_size=2
SET sql_mode= '';
--error ER_TOO_BIG_ROWSIZE
create index idx1 on worklog5743_2(a2(4000));
create index idx3 on worklog5743_2(a2(769));
show warnings;
create index idx4 on worklog5743_2(a2(768));
show warnings;
create index idx5 on worklog5743_2(a1, a2(765));
show warnings;
create index idx6 on worklog5743_2(a1, a2(764));
show warnings;
# Test edge cases for indexes using key_block_size=4
create index idx1 on worklog5743_4(a2(4000));
show warnings;
show create table worklog5743_4;
create index idx3 on worklog5743_4(a2(769));
show warnings;
create index idx4 on worklog5743_4(a2(768));
show warnings;
create index idx5 on worklog5743_4(a1, a2(765));
show warnings;
create index idx6 on worklog5743_4(a1, a2(764));
show warnings;
SET sql_mode= default;
# 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));

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 ER_TOO_LONG_KEY
create index idx1 on worklog5743(a2);
show warnings;
drop table worklog5743;

create table worklog5743(a1 int, a2 varchar(3072)) ROW_FORMAT=DYNAMIC;
-- error ER_TOO_LONG_KEY
create index idx1 on worklog5743(a2);
show warnings;
drop table worklog5743;

create table worklog5743(a1 int, a2 varchar(769)) ROW_FORMAT=DYNAMIC;
create index idx1 on worklog5743(a2);
show warnings;
drop table worklog5743;

create table worklog5743(a1 int, a2 varchar(768)) ROW_FORMAT=DYNAMIC;
create index idx1 on worklog5743(a2);
show warnings;
insert into worklog5743 values(9, repeat("a", 768));
update worklog5743 set a1 = 3333;
drop table worklog5743;

create table worklog5743(a1 int, a2 varchar(765)) ROW_FORMAT=DYNAMIC;
create index idx1 on worklog5743(a1, a2);
show warnings;
drop table worklog5743;

create table worklog5743(a1 int, a2 varchar(764)) ROW_FORMAT=DYNAMIC;
create index idx1 on worklog5743(a1, a2);
show warnings;
insert into worklog5743 values(9, repeat("a", 764));

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 table worklog5743(a TEXT) ROW_FORMAT=COMPACT;

# 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;
--disconnect con1

--connection con2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select @@session.tx_isolation;
select a = repeat("x", 25000) from worklog5743;
--disconnect con2

--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.
SET sql_mode= '';
create index idx1 on worklog5743(a(769));
show warnings;
SET sql_mode= default;
create index idx2 on worklog5743(a(768));
show warnings;
show create table worklog5743;
insert into worklog5743 values(repeat("a", 768));
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));
--disable_query_log
SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
--enable_query_log
drop table worklog5743;