EVOLUTION-MANAGER

Edit File: innodb_defragment_fill_factor.test

--source include/have_innodb.inc
--source include/big_test.inc
--source include/not_valgrind.inc
--source include/not_embedded.inc
--source include/have_innodb_16k.inc

--echo Testing tables with large records
# Create table.
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), c VARCHAR(256), KEY SECOND(a, b,c)) ENGINE=INNODB;

# Populate table.
INSERT INTO t1 VALUES (1, REPEAT('A', 256), REPEAT('B', 256));
INSERT INTO t1 (b) SELECT b from t1;
INSERT INTO t1 (b) SELECT b from t1;
INSERT INTO t1 (b) SELECT b from t1;
INSERT INTO t1 (b) SELECT b from t1;
INSERT INTO t1 (b) SELECT b from t1;
INSERT INTO t1 (b) SELECT b from t1;
INSERT INTO t1 (b) SELECT b from t1;
INSERT INTO t1 (b) SELECT b from t1;
INSERT INTO t1 (b) SELECT b from t1;
INSERT INTO t1 (b) SELECT b from t1;

--disable_query_log
let $size = 10;
while ($size)
{
    let $j =  100 * $size;
    eval delete from t1 where a between $j - 20 and $j + 5;
    dec $size;
}
--enable_query_log

SET GLOBAL innodb_fast_shutdown = 0;
--source include/restart_mysqld.inc
optimize table t1;

select count(*) from t1;

--let $primary_before = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'PRIMARY', Value, 1)

select count(*) from t1 force index (second);

--let $second_before = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'second', Value, 1)

--echo # A few more insertions on the page should not cause a page split.
insert into t1 values (81, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (83, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (87, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (82, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (86, REPEAT('A', 256), REPEAT('B', 256));
--let $primary_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'PRIMARY', Value, 1)
--let $second_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'second', Value, 1)

if ($primary_before != $primary_after) {
   --echo Insertion caused page split on primary, which should be avoided by innodb_defragment_fill_factor.
}

if ($second_before != $second_after) {
   --echo Insertion caused page split on second, which should be avoided by innodb_defragment_fill_factor.
}

--echo # Insert more rows to cause a page split
insert into t1 values (180, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (181, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (182, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (183, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (184, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (185, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (186, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (187, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (188, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (189, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (190, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (191, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (192, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (193, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (194, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (195, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (196, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (197, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (198, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (199, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (200, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (201, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (202, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (203, REPEAT('A', 256), REPEAT('B', 256));
insert into t1 values (204, REPEAT('A', 256), REPEAT('B', 256));

--let $primary_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'PRIMARY', Value, 1)

--let $second_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'second', Value, 1)

if ($primary_before == $primary_after) {
   --echo Too little space is reserved on primary index.
}

if ($second_before == $second_after) {
   --echo Too little space is reserved on second index.
}

DROP TABLE t1;

--echo Testing table with small records
CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(16), c VARCHAR(32), KEY SECOND(a,b,c)) ENGINE=INNODB;
# Populate table.
--disable_query_log
INSERT INTO t2 VALUES (1, REPEAT('A', 16), REPEAT('B', 32));
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
INSERT INTO t2 (b) SELECT b from t2;
--enable_query_log

--disable_query_log
let $size = 40;
while ($size)
{
    let $j =  100 * $size;
    eval delete from t2 where a between $j - 20 and $j;
    dec $size;
}
--enable_query_log

SET GLOBAL innodb_fast_shutdown = 0;
--source include/restart_mysqld.inc
optimize table t2;

select count(*) from t2;
select count(*) from t2 force index(second);

--let $second_before = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t2%' and index_name = 'second', Value, 1)

--echo The page should have room for about 20 insertions
insert into t2 values(1181, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1191, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1182, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1192, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1183, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1193, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1184, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1194, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1185, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1195, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1186, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1196, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1187, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1197, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1188, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1198, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1189, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1199, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1190, REPEAT('A', 16), REPEAT('B',32));
insert into t2 values(1180, REPEAT('A', 16), REPEAT('B',32));

--let $second_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t2%' and index_name = 'second', Value, 1)

if ($second_before != $second_after) {
   --echo Insertion caused page split on second, which should be avoided by innodb_defragment_fill_factor.
}

--echo # Insert more rows to cause a page split
insert into t2 values (180, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (181, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (182, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (183, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (184, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (185, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (186, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (187, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (188, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (189, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (190, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (191, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (192, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (193, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (194, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (195, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (196, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (197, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (198, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (199, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (200, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (201, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (202, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (203, REPEAT('A', 16), REPEAT('B', 32));
insert into t2 values (204, REPEAT('A', 16), REPEAT('B', 32));

--let $second_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t2%' and index_name = 'second', Value, 1)

if ($second_before == $second_after) {
   --echo Too little space is reserved on second index.
}

DROP TABLE t2;