1################### include/rpl_stmt_seq.inc ###########################
2#                                                                      #
3# Check if a given SQL statement (->$my_stmt) / AUTOCOMMIT mode /      #
4# storage engine somehow involved causes COMMIT or ROLLBACK.           #
5#                                                                      #
6#                                                                      #
7# The typical test sequence                                            #
8# -------------------------                                            #
9# 1. master connection: INSERT without commit                          #
10#    check table content of master and slave                           #
11# 2. master connection: EXECUTE the statement                          #
12#    check table content of master and slave                           #
13# 3. master connection: ROLLBACK                                       #
14#    check table content of master and slave                           #
15# 4. flush the logs                                                    #
16#                                                                      #
17# The variables                                                        #
18#    $show_binlog       -- print binlog entries                        #
19#                          0 - default + fits to the file with         #
20#                              results                                 #
21#                          1 - useful for debugging                    #
22#                          This variable is used within                #
23#                          include/rpl_stmt_seq.inc.                   #
24#    $manipulate        -- Manipulation of the binary logs             #
25#                          0 - do nothing                              #
26#                          1 - so that the output of SHOW BINLOG       #
27#                              EVENTS IN <current log> contains only   #
28#                              commands of the current test sequence   #
29#                              This is especially useful, if the       #
30#                              $show_binlog is set to 1 and many       #
31#                              subtest are executed.                   #
32#                          This variable is used within                #
33#                          include/rpl_stmt_seq.inc.                   #
34# have to be set before sourcing this script.                          #
35#                                                                      #
36# Please be very careful when editing this routine, because the        #
37# handling of the $variables is extreme sensitive.                     #
38#                                                                      #
39########################################################################
40
41# Last update:
42# 2007-02-12 ML Replace comments via SQL by "--echo ..."
43#
44
45let $VERSION=`select version()`;
46
47--echo
48--echo ######## $my_stmt ########
49
50
51###############################################################
52# Predict the number of the current log
53###############################################################
54# Disable the logging of the log number computation.
55--disable_query_log
56# $_log_num_n should contain the number of the current binlog in numeric style.
57# If this routine is called for the first time, $_log_num will not initialized
58# and contain the value '' instead of '1'. So we will correct it here.
59#
60eval set @aux= IF('$_log_num_n' = '', '1', '$_log_num_n');
61let $_log_num_n= `SELECT @aux`;
62eval set @aux= LPAD('$_log_num_n',6,'0');
63#              SELECT @aux AS "@aux is";
64#
65# $_log_num_s should contain the number of the current binlog in string style.
66let $_log_num_s= `select @aux`;
67# eval SELECT '$log_num' ;
68--enable_query_log
69
70###############################################################
71# INSERT
72###############################################################
73connection master;
74# Maybe it would be smarter to use a table with an autoincrement column.
75let $MAX= `SELECT MAX(f1) FROM t1` ;
76eval INSERT INTO t1 SET f1= $MAX + 1;
77# results before DDL(to be tested)
78SELECT MAX(f1) FROM t1;
79if ($show_binlog)
80{
81  --let $binlog_file= master-bin.$_log_num_s
82  --source include/show_binlog_events.inc
83}
84sync_slave_with_master;
85
86connection slave;
87# results before DDL(to be tested)
88SELECT MAX(f1) FROM t1;
89if ($show_binlog)
90{
91  --let $binlog_file= slave-bin.$_log_num_s
92  --source include/show_binlog_events.inc
93}
94
95###############################################################
96# command to be tested
97###############################################################
98connection master;
99eval $my_stmt;
100# Devaluate $my_stmt, to detect script bugs
101let $my_stmt= ERROR: YOU FORGOT TO FILL IN THE STATEMENT;
102# results after DDL(to be tested)
103SELECT MAX(f1) FROM t1;
104if ($show_binlog)
105{
106  --let $binlog_file= master-bin.$_log_num_s
107  --source include/show_binlog_events.inc
108}
109sync_slave_with_master;
110
111connection slave;
112# results after DDL(to be tested)
113SELECT MAX(f1) FROM t1;
114if ($show_binlog)
115{
116  --let $binlog_file= slave-bin.$_log_num_s
117  --source include/show_binlog_events.inc
118}
119
120###############################################################
121# ROLLBACK
122###############################################################
123connection master;
124ROLLBACK;
125# results after final ROLLBACK
126SELECT MAX(f1) FROM t1;
127# Try to detect if the DDL command caused that the INSERT is committed
128# $MAX holds the highest/last value just before the insert of MAX + 1
129--disable_query_log
130eval SELECT CONCAT(CONCAT('TEST-INFO: MASTER: The INSERT is ',
131               IF(MAX(f1) = $MAX + 1, 'committed', 'not committed')),
132               IF((MAX(f1) = $MAX + 1) XOR NOT $my_master_commit,
133                ' (Succeeded)',
134                ' (Failed)')) AS ""
135               FROM mysqltest1.t1;
136--enable_query_log
137if ($show_binlog)
138{
139  --let $binlog_file= master-bin.$_log_num_s
140  --source include/show_binlog_events.inc
141}
142sync_slave_with_master;
143
144connection slave;
145# results after final ROLLBACK
146SELECT MAX(f1) FROM t1;
147--disable_query_log
148eval SELECT CONCAT(CONCAT('TEST-INFO: SLAVE:  The INSERT is ',
149               IF(MAX(f1) = $MAX + 1, 'committed', 'not committed')),
150               IF((MAX(f1) = $MAX + 1) XOR NOT $my_slave_commit,
151                ' (Succeeded)',
152                ' (Failed)')) AS ""
153               FROM mysqltest1.t1;
154--enable_query_log
155if ($show_binlog)
156{
157  --let $binlog_file= slave-bin.$_log_num_s
158  --source include/show_binlog_events.inc
159}
160
161###############################################################
162# Manipulate binlog
163###############################################################
164if ($manipulate)
165{
166#### Manipulate the binary logs,
167# so that the output of SHOW BINLOG EVENTS IN <current log>
168# contains only commands of the current test sequence.
169# - flush the master and the slave log
170#   ---> both start to write into new logs with incremented number
171# - increment $_log_num_n
172connection master;
173flush logs;
174# sleep 1;
175# eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s';
176sync_slave_with_master;
177
178connection slave;
179# the final content of the binary log
180flush logs;
181# The next sleep is urgent needed.
182# Without this sleep the slaves crashes often, when the SHOW BINLOG
183# is executed.   :-(
184# sleep 1;
185# eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s';
186inc $_log_num_n;
187}
188
189connection master;
190