summaryrefslogtreecommitdiff
path: root/mysql-test/suite/innodb/t/undo_truncate.test
blob: af6ed2b4372b2ebd5d666cf191fc07b131e0fcc5 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
--source include/have_innodb.inc
--source include/innodb_page_size.inc
--source include/have_undo_tablespaces.inc

SET @save_undo_logs = @@GLOBAL.innodb_undo_logs;
SET @save_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;
SET @save_truncate = @@GLOBAL.innodb_undo_log_truncate;
SET GLOBAL innodb_undo_log_truncate = 0;
SET GLOBAL innodb_undo_logs = 4;
SET GLOBAL innodb_purge_rseg_truncate_frequency = 1;

SET @trunc_start=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_undo_truncations');

#-----------------------------------------------------------------------------
#
# Perform DML action using multiple clients and multiple undo tablespace.
#
#
create table t1(keyc int primary key, c char(100)) engine = innodb;
create table t2(keyc int primary key, c char(100)) engine = innodb;
#
delimiter |;
CREATE PROCEDURE populate_t1()
BEGIN
        DECLARE i INT DEFAULT 1;
        while (i <= 20000) DO
                insert into t1 values (i, 'a');
                SET i = i + 1;
        END WHILE;
END |
delimiter ;|
#
delimiter |;
CREATE PROCEDURE populate_t2()
BEGIN
        DECLARE i INT DEFAULT 1;
        while (i <= 20000) DO
                insert into t2 values (i, 'a');
                SET i = i + 1;
        END WHILE;
END |
delimiter ;|
#
#
let DATADIR = `select @@datadir`;
connect (con1,localhost,root,,);
begin;
send call populate_t1();

connect (con2,localhost,root,,);
begin;
send call populate_t2();

connection con1; reap; send update t1 set c = 'mysql';
connection con2; reap; send update t2 set c = 'mysql';
connection con1; reap; send update t1 set c = 'oracle';
connection con2; reap; send update t2 set c = 'oracle';
connection con1; reap; send delete from t1;
connection con2; reap; delete from t2;
connection con1; reap;

let CHECKFILE = $MYSQL_TMP_DIR/check.txt;
perl;
($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size1)
	= stat("$ENV{DATADIR}/undo001");
($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size2)
	= stat("$ENV{DATADIR}/undo002");
open(OUT, ">$ENV{CHECKFILE}") || die;
print OUT "let \$size1='$size1,$size2';\n";
close(OUT);
EOF

SET GLOBAL innodb_undo_log_truncate = 1;
commit; disconnect con1;
connection con2; commit; disconnect con2;

connection default;
drop table t1, t2;
drop PROCEDURE populate_t1;
drop PROCEDURE populate_t2;

--source include/wait_all_purged.inc

# Truncation will normally not occur with innodb_page_size=64k,
# and occasionally not with innodb_page_size=32k,
# because the undo log will not grow enough.
if (`select @@innodb_page_size IN (4096,8192,16384)`)
{
  let $wait_condition = (SELECT variable_value!=@trunc_start
                         FROM information_schema.global_status
                         WHERE variable_name = 'innodb_undo_truncations');
  source include/wait_condition.inc;
}

--source $CHECKFILE
perl;
($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size1)
	= stat("$ENV{DATADIR}/undo001");
($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size2)
	= stat("$ENV{DATADIR}/undo002");
open(OUT, ">$ENV{CHECKFILE}") || die;
print OUT "let \$size2='$size1,$size2';\n";
close(OUT);
EOF

--source $CHECKFILE
--remove_file $CHECKFILE

if ($size1 == $size2)
{
  # This fails for innodb_page_size=64k, occasionally also for 32k.
  if (`select @@innodb_page_size IN (4096,8192,16384)`)
  {
    echo Truncation did not happen: $size1;
  }
}

SET GLOBAL innodb_undo_logs = @save_undo_logs;
SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency;
SET GLOBAL innodb_undo_log_truncate = @save_truncate;