1# ==== Purpose ====
2#
3# This include will check if the optimized delete (delete_all_rows) was
4# used or not varying the session sql_log_bin variable with 0 and 1.
5#
6# It is expected that the optimized delete (delete_all_rows) will be used:
7# a) If binlog_format != ROW
8# b) If binlog_format == ROW but the statement will not be binlogged
9#
10# The include, varying sql_log_bin with 0 and 1, creates a new table (t1),
11# fills it with data, and deletes all t1 data with a single query.
12#
13# It uses an EXPLAIN to assert if delete_all_rows was used or not.
14#
15# The include also asserts if the statement was binlogged if it is expected
16# to be.
17#
18# ==== Usage ====
19#
20# [--let $storage_engine= InnoDB | MyISAM]
21# --source include/delete_all_rows.inc
22#
23# Parameters:
24#   $storage_engine
25#     The storage engine that will be used in the CREATE TABLE statements.
26#     If not specified, InnoDB will be used.
27#
28
29if (!$storage_engine)
30{
31  --let $_storage_engine= InnoDB
32}
33if ($storage_engine)
34{
35  --let $_storage_engine= $storage_engine
36}
37
38##
39## Determine if the server has the binary log enabled
40##
41--let $have_log_bin= query_get_value(SHOW GLOBAL VARIABLES LIKE 'log_bin', Value, 1)
42
43# Create t1 and populate it
44--eval CREATE TABLE t1 (c1 INT) ENGINE=$_storage_engine
45INSERT INTO t1 VALUES (1), (2), (3);
46
47##
48## Session sql_log_bin = 0
49##
50--echo
51SET sql_log_bin= 0;
52
53# Assert that delete_all_rows() will be used
54--let $explain_extra= query_get_value("EXPLAIN DELETE FROM t1", Extra, 1)
55--let $assert_text= DELETE will use delete_all_rows
56--let $assert_cond= "$explain_extra" = "Deleting all rows"
57--source include/assert.inc
58
59# Save current master position
60if ($have_log_bin == ON)
61{
62  --let $saved_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
63}
64DELETE FROM t1;
65
66# Assert that this was not binlogged if binary log is enabled
67if ($have_log_bin == ON)
68{
69  --let $current_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
70  --let $assert_text= DELETE was not binlogged
71  --let $assert_cond= $current_pos = $saved_pos
72  --source include/assert.inc
73}
74
75##
76## Session sql_log_bin = 1
77##
78--echo
79SET sql_log_bin= 1;
80# Populate t1 again
81INSERT INTO t1 VALUES (1), (2), (3);
82
83# Assert if delete_all_rows() will be used or not
84--let $explain_extra_expected= "Deleting all rows"
85# The extra info in explain will be NULL if the statement is binlogged
86# with binlog_format = ROW
87--let $explain_extra= query_get_value("EXPLAIN DELETE FROM t1", Extra, 1)
88--let $assert_text= DELETE will use delete_all_rows
89if ($have_log_bin == ON)
90{
91  if (`SELECT @@GLOBAL.binlog_format = 'ROW'`)
92  {
93    --let $assert_text= DELETE will not use delete_all_rows
94    --let $explain_extra_expected= "NULL"
95  }
96}
97--let $assert_cond= "$explain_extra" = $explain_extra_expected
98--source include/assert.inc
99
100# Save current master position
101if ($have_log_bin == ON)
102{
103  --let $saved_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
104}
105
106DELETE FROM t1;
107# Assert that this was binlogged if binary log is enabled
108if ($have_log_bin == ON)
109{
110  --let $current_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
111  --let $assert_text= DELETE was binlogged
112  --let $assert_cond= $current_pos > $saved_pos
113  --source include/assert.inc
114}
115
116# Cleanup
117DROP TABLE t1;
118