summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/create_drop_binlog.test
blob: 6b2b1bbf1f5fad6e8362497361982819afcdb397 (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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
--source include/have_udf.inc
--source include/have_log_bin.inc
--source include/binlog_start_pos.inc

reset master;

--let $pos=`select $binlog_start_pos + 73`

--let $binlog_file=query_get_value(SHOW MASTER STATUS, File, 1)
--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1)

CREATE OR REPLACE DATABASE d1;
CREATE OR REPLACE DATABASE d1;
DROP DATABASE d1;
CREATE DATABASE IF NOT EXISTS d1;
CREATE DATABASE IF NOT EXISTS d1;
DROP DATABASE IF EXISTS d1;
DROP DATABASE IF EXISTS d1;
--echo "Runnig SHOW BINLOG EVENTS"
--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VERSIONS/
--disable_query_log
--eval SHOW BINLOG EVENTS FROM $binlog_start;
--enable_query_log
RESET MASTER;
USE test;


CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 1;
CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 1;
DROP FUNCTION f1;
CREATE FUNCTION IF NOT EXISTS f1() RETURNS INT RETURN 1;
CREATE FUNCTION IF NOT EXISTS f1() RETURNS INT RETURN 1;
DROP FUNCTION IF EXISTS f1;
DROP FUNCTION IF EXISTS f1;
--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;


CREATE TABLE t1 (a DATETIME);
CREATE OR REPLACE PROCEDURE p1() DELETE FROM t1;
CREATE OR REPLACE PROCEDURE p1() DELETE FROM t1;
DROP PROCEDURE p1;
CREATE PROCEDURE IF NOT EXISTS p1() DELETE FROM t1;
CREATE PROCEDURE IF NOT EXISTS p1() DELETE FROM t1;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p1;
--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
DROP TABLE t1;
RESET MASTER;


--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME '$UDF_EXAMPLE_SO';
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME '$UDF_EXAMPLE_SO';
DROP FUNCTION metaphon;
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME '$UDF_EXAMPLE_SO';
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME '$UDF_EXAMPLE_SO';
DROP FUNCTION IF EXISTS metaphon;
DROP FUNCTION IF EXISTS metaphon;
--replace_column 1 # 2 # 5 #
--replace_result $UDF_EXAMPLE_SO UDM_EXAMPLE_LIB
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;


--echo #
--echo # CREATE SERVER is not logged
--echo #
CREATE OR REPLACE SERVER s1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', HOST 'localhost', DATABASE 'test');
CREATE OR REPLACE SERVER s1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', HOST 'localhost', DATABASE 'test');
DROP SERVER s1;
CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', HOST 'localhost', DATABASE 'test');
CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', HOST 'localhost', DATABASE 'test');
DROP SERVER IF EXISTS s1;
DROP SERVER IF EXISTS s1;
--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;



CREATE OR REPLACE VIEW v1 AS SELECT 1;
CREATE OR REPLACE VIEW v1 AS SELECT 1;
DROP VIEW v1;
CREATE VIEW IF NOT EXISTS v1 AS SELECT 1;
CREATE VIEW IF NOT EXISTS v1 AS SELECT 1;
DROP VIEW IF EXISTS v1;
DROP VIEW IF EXISTS v1;
--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;

CREATE OR REPLACE ROLE r1;
CREATE OR REPLACE ROLE r1;
DROP ROLE r1;
CREATE ROLE IF NOT EXISTS r1;
CREATE ROLE IF NOT EXISTS r1;
DROP ROLE IF EXISTS r1;
DROP ROLE IF EXISTS r1;
--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;

CREATE OR REPLACE USER u1@localhost;
CREATE OR REPLACE USER u1@localhost;
DROP USER u1@localhost;
CREATE USER IF NOT EXISTS u1@localhost;
CREATE USER IF NOT EXISTS u1@localhost;
DROP USER IF EXISTS u1@localhost;
DROP USER IF EXISTS u1@localhost;
--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;


SET timestamp=UNIX_TIMESTAMP('2014-11-01 10:20:30');
CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE IF EXISTS t1;
CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE IF EXISTS t2;
SELECT EVENT_NAME, EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS;
DROP EVENT ev1;
DROP EVENT IF EXISTS ev1;

--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;

CREATE TABLE t1 (a INT);
CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=10;
CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=11;
DROP TRIGGER tr1;
CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=20;
CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=21;
DROP TRIGGER IF EXISTS tr1;
DROP TRIGGER IF EXISTS tr1;
DROP TABLE t1;

--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;


CREATE TABLE t1(a INT, b INT);
CREATE TABLE IF NOT EXISTS t1(a INT, b INT);
CREATE OR REPLACE INDEX i1 ON t1(a);
CREATE OR REPLACE INDEX i1 ON t1(a);
CREATE OR REPLACE INDEX i1 ON t1(a,b);
CREATE OR REPLACE INDEX i1 ON t1(a,b);
DROP TABLE t1;
--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;

# Test RESET MASTER TO

RESET MASTER;
--replace_result $pos <pos>
SHOW MASTER STATUS;
RESET MASTER TO 100;
--replace_result $pos <pos>
SHOW MASTER STATUS;
RESET MASTER;
--replace_result $pos <pos>
SHOW MASTER STATUS;

#
# mdev-7817 ALTER TABLE {ADD|DROP} INDEX IF [NOT] EXISTS does not get written into binlog if nothing to do
# 

CREATE TABLE t1(a INT, b INT);
CREATE TABLE IF NOT EXISTS t1(a INT, b INT);
CREATE INDEX IF NOT EXISTS i1 ON t1(a);
CREATE INDEX IF NOT EXISTS i1 ON t1(a);
SHOW CREATE TABLE t1;
DROP INDEX IF EXISTS i1 ON t1;
DROP INDEX IF EXISTS i1 ON t1;
DROP TABLE t1;
DROP TABLE IF EXISTS t1;
--replace_column 1 # 2 # 5 #
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;