~drizzle-trunk/drizzle/development : contents of mysql-test/suite/binlog/r/binlog

~drizzle-trunk/drizzle/development : (revision 27)

==== Setup tables ====
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a CHAR(40));
CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE trigger_table (a CHAR(7));
CREATE TABLE trigger_table2 (a INT);
==== Non-deterministic statements ====
INSERT DELAYED INTO t1 VALUES (5);
==== Some variables that *should* be unsafe ====
---- Insert directly ----
INSERT INTO t1 VALUES (@@global.sync_binlog);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t1 VALUES (@@session.insert_id);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t2 SELECT UUID();
Warnings:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t2 VALUES (@@session.sql_mode);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t2 VALUES (@@global.init_slave);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t2 VALUES (@@hostname);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
---- Insert from stored procedure ----
CREATE PROCEDURE proc()
BEGIN
INSERT INTO t1 VALUES (@@global.sync_binlog);
INSERT INTO t1 VALUES (@@session.insert_id);
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
INSERT INTO t2 SELECT UUID();
INSERT INTO t2 VALUES (@@session.sql_mode);
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
END|
CALL proc();
Warnings:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
---- Insert from stored function ----
CREATE FUNCTION func()
RETURNS INT
BEGIN
INSERT INTO t1 VALUES (@@global.sync_binlog);
INSERT INTO t1 VALUES (@@session.insert_id);
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
INSERT INTO t2 SELECT UUID();
INSERT INTO t2 VALUES (@@session.sql_mode);
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
RETURN 0;
END|
SELECT func();
func()
0
Warnings:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
---- Insert from trigger ----
CREATE TRIGGER trig
BEFORE INSERT ON trigger_table
FOR EACH ROW
BEGIN
INSERT INTO t1 VALUES (@@global.sync_binlog);
INSERT INTO t1 VALUES (@@session.insert_id);
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
INSERT INTO t2 SELECT UUID();
INSERT INTO t2 VALUES (@@session.sql_mode);
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
END|
INSERT INTO trigger_table VALUES ('bye.');
Warnings:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
---- Insert from prepared statement ----
PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)';
PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)';
PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)';
PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()';
PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@session.sql_mode)';
PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)';
PREPARE p7 FROM 'INSERT INTO t2 VALUES (@@hostname)';
EXECUTE p1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p2;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p3;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p4;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p5;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p6;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p7;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
---- Insert from nested call of triggers / functions / procedures ----
CREATE PROCEDURE proc1()
INSERT INTO trigger_table VALUES ('ha!')|
CREATE FUNCTION func2()
RETURNS INT
BEGIN
CALL proc1();
RETURN 0;
END|
CREATE TRIGGER trig3
BEFORE INSERT ON trigger_table2
FOR EACH ROW
BEGIN
DECLARE tmp INT;
SELECT func2() INTO tmp;
END|
CREATE PROCEDURE proc4()
INSERT INTO trigger_table2 VALUES (1)|
CREATE FUNCTION func5()
RETURNS INT
BEGIN
CALL proc4;
RETURN 0;
END|
PREPARE prep6 FROM 'SELECT func5()'|
EXECUTE prep6;
func5()
0
Warnings:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
==== Variables that should *not* be unsafe ====
INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
INSERT INTO t1 VALUES (@@session.foreign_key_checks);
INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
INSERT INTO t1 VALUES (@@session.unique_checks);
INSERT INTO t1 VALUES (@@session.auto_increment_increment);
INSERT INTO t1 VALUES (@@session.auto_increment_offset);
INSERT INTO t2 VALUES (@@session.character_set_client);
INSERT INTO t2 VALUES (@@session.collation_connection);
INSERT INTO t2 VALUES (@@session.collation_server);
INSERT INTO t2 VALUES (@@session.time_zone);
INSERT INTO t2 VALUES (@@session.lc_time_names);
INSERT INTO t2 VALUES (@@session.collation_database);
INSERT INTO t2 VALUES (@@session.timestamp);
INSERT INTO t2 VALUES (@@session.last_insert_id);
SET @my_var= 4711;
INSERT INTO t1 VALUES (@my_var);
SET insert_id=12;
INSERT INTO t3 VALUES (NULL);
==== Clean up ====
DROP PROCEDURE proc;
DROP FUNCTION  func;
DROP TRIGGER   trig;
DROP PROCEDURE proc1;
DROP FUNCTION  func2;
DROP TRIGGER   trig3;
DROP PROCEDURE proc4;
DROP FUNCTION  func5;
DROP PREPARE   prep6;
DROP TABLE t1, t2, t3, trigger_table, trigger_table2;
CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
UPDATE t1 SET a=1 LIMIT 1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
DELETE FROM t1 LIMIT 1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
CREATE PROCEDURE p1()
BEGIN
INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
UPDATE t1 SET a=1 LIMIT 1;
DELETE FROM t1 LIMIT 1;
END|
CALL p1();
Warnings:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
DROP PROCEDURE p1;
DROP TABLE t1;

1 by brian clean slate	1	==== Setup tables ====
	2	CREATE TABLE t1 (a INT);
	3	CREATE TABLE t2 (a CHAR(40));
	4	CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY);
	5	CREATE TABLE trigger_table (a CHAR(7));
	6	CREATE TABLE trigger_table2 (a INT);
	7	==== Non-deterministic statements ====
	8	INSERT DELAYED INTO t1 VALUES (5);
	9	==== Some variables that should be unsafe ====
	10	---- Insert directly ----
	11	INSERT INTO t1 VALUES (@@global.sync_binlog);
	12	Warnings:
	13	Warning 1592 Statement is not safe to log in statement format.
	14	INSERT INTO t1 VALUES (@@session.insert_id);
	15	Warnings:
	16	Warning 1592 Statement is not safe to log in statement format.
	17	INSERT INTO t1 VALUES (@@global.auto_increment_increment);
	18	Warnings:
	19	Warning 1592 Statement is not safe to log in statement format.
	20	INSERT INTO t2 SELECT UUID();
	21	Warnings:
	22	Warning 1592 Statement is not safe to log in statement format.
	23	INSERT INTO t2 VALUES (@@session.sql_mode);
	24	Warnings:
	25	Warning 1592 Statement is not safe to log in statement format.
	26	INSERT INTO t2 VALUES (@@global.init_slave);
	27	Warnings:
	28	Warning 1592 Statement is not safe to log in statement format.
	29	INSERT INTO t2 VALUES (@@hostname);
	30	Warnings:
	31	Warning 1592 Statement is not safe to log in statement format.
	32	---- Insert from stored procedure ----
	33	CREATE PROCEDURE proc()
	34	BEGIN
	35	INSERT INTO t1 VALUES (@@global.sync_binlog);
	36	INSERT INTO t1 VALUES (@@session.insert_id);
	37	INSERT INTO t1 VALUES (@@global.auto_increment_increment);
	38	INSERT INTO t2 SELECT UUID();
	39	INSERT INTO t2 VALUES (@@session.sql_mode);
	40	INSERT INTO t2 VALUES (@@global.init_slave);
	41	INSERT INTO t2 VALUES (@@hostname);
	42	END\|
	43	CALL proc();
	44	Warnings:
	45	Warning 1592 Statement is not safe to log in statement format.
	46	Warning 1592 Statement is not safe to log in statement format.
	47	Warning 1592 Statement is not safe to log in statement format.
	48	Warning 1592 Statement is not safe to log in statement format.
	49	Warning 1592 Statement is not safe to log in statement format.
	50	Warning 1592 Statement is not safe to log in statement format.
	51	Warning 1592 Statement is not safe to log in statement format.
	52	---- Insert from stored function ----
	53	CREATE FUNCTION func()
	54	RETURNS INT
	55	BEGIN
	56	INSERT INTO t1 VALUES (@@global.sync_binlog);
	57	INSERT INTO t1 VALUES (@@session.insert_id);
	58	INSERT INTO t1 VALUES (@@global.auto_increment_increment);
	59	INSERT INTO t2 SELECT UUID();
	60	INSERT INTO t2 VALUES (@@session.sql_mode);
	61	INSERT INTO t2 VALUES (@@global.init_slave);
	62	INSERT INTO t2 VALUES (@@hostname);
	63	RETURN 0;
	64	END\|
65	SELECT func();
66	func()
67	0
68	Warnings:
69	Warning 1592 Statement is not safe to log in statement format.
70	Warning 1592 Statement is not safe to log in statement format.
71	Warning 1592 Statement is not safe to log in statement format.
72	Warning 1592 Statement is not safe to log in statement format.
73	Warning 1592 Statement is not safe to log in statement format.
74	Warning 1592 Statement is not safe to log in statement format.
75	Warning 1592 Statement is not safe to log in statement format.
76	---- Insert from trigger ----
77	CREATE TRIGGER trig
78	BEFORE INSERT ON trigger_table
79	FOR EACH ROW
80	BEGIN
81	INSERT INTO t1 VALUES (@@global.sync_binlog);
82	INSERT INTO t1 VALUES (@@session.insert_id);
83	INSERT INTO t1 VALUES (@@global.auto_increment_increment);
84	INSERT INTO t2 SELECT UUID();
85	INSERT INTO t2 VALUES (@@session.sql_mode);
86	INSERT INTO t2 VALUES (@@global.init_slave);
87	INSERT INTO t2 VALUES (@@hostname);
88	END\|
89	INSERT INTO trigger_table VALUES ('bye.');
90	Warnings:
91	Warning 1592 Statement is not safe to log in statement format.
92	Warning 1592 Statement is not safe to log in statement format.
93	Warning 1592 Statement is not safe to log in statement format.
94	Warning 1592 Statement is not safe to log in statement format.
95	Warning 1592 Statement is not safe to log in statement format.
96	Warning 1592 Statement is not safe to log in statement format.
97	Warning 1592 Statement is not safe to log in statement format.
98	Warning 1592 Statement is not safe to log in statement format.
99	---- Insert from prepared statement ----
100	PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)';
101	PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)';
102	PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)';
103	PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()';
104	PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@session.sql_mode)';
105	PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)';
106	PREPARE p7 FROM 'INSERT INTO t2 VALUES (@@hostname)';
107	EXECUTE p1;
108	Warnings:
109	Warning 1592 Statement is not safe to log in statement format.
110	EXECUTE p2;
111	Warnings:
112	Warning 1592 Statement is not safe to log in statement format.
113	EXECUTE p3;
114	Warnings:
115	Warning 1592 Statement is not safe to log in statement format.
116	EXECUTE p4;
117	Warnings:
118	Warning 1592 Statement is not safe to log in statement format.
119	EXECUTE p5;
120	Warnings:
121	Warning 1592 Statement is not safe to log in statement format.
122	EXECUTE p6;
123	Warnings:
124	Warning 1592 Statement is not safe to log in statement format.
125	EXECUTE p7;
126	Warnings:
127	Warning 1592 Statement is not safe to log in statement format.
128	---- Insert from nested call of triggers / functions / procedures ----
129	CREATE PROCEDURE proc1()
130	INSERT INTO trigger_table VALUES ('ha!')\|
131	CREATE FUNCTION func2()
132	RETURNS INT
133	BEGIN
134	CALL proc1();
135	RETURN 0;
136	END\|
137	CREATE TRIGGER trig3
138	BEFORE INSERT ON trigger_table2
139	FOR EACH ROW
140	BEGIN
141	DECLARE tmp INT;
142	SELECT func2() INTO tmp;
143	END\|
144	CREATE PROCEDURE proc4()
145	INSERT INTO trigger_table2 VALUES (1)\|
146	CREATE FUNCTION func5()
147	RETURNS INT
148	BEGIN
149	CALL proc4;
150	RETURN 0;
151	END\|
152	PREPARE prep6 FROM 'SELECT func5()'\|
153	EXECUTE prep6;
154	func5()
155	0
156	Warnings:
157	Warning 1592 Statement is not safe to log in statement format.
158	Warning 1592 Statement is not safe to log in statement format.
159	Warning 1592 Statement is not safe to log in statement format.
160	Warning 1592 Statement is not safe to log in statement format.
161	Warning 1592 Statement is not safe to log in statement format.
162	Warning 1592 Statement is not safe to log in statement format.
163	Warning 1592 Statement is not safe to log in statement format.
164	==== Variables that should not be unsafe ====
165	INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
166	INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
167	INSERT INTO t1 VALUES (@@session.foreign_key_checks);
168	INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
169	INSERT INTO t1 VALUES (@@session.unique_checks);
170	INSERT INTO t1 VALUES (@@session.auto_increment_increment);
171	INSERT INTO t1 VALUES (@@session.auto_increment_offset);
172	INSERT INTO t2 VALUES (@@session.character_set_client);
173	INSERT INTO t2 VALUES (@@session.collation_connection);
174	INSERT INTO t2 VALUES (@@session.collation_server);
175	INSERT INTO t2 VALUES (@@session.time_zone);
176	INSERT INTO t2 VALUES (@@session.lc_time_names);
177	INSERT INTO t2 VALUES (@@session.collation_database);
178	INSERT INTO t2 VALUES (@@session.timestamp);
179	INSERT INTO t2 VALUES (@@session.last_insert_id);
180	SET @my_var= 4711;
181	INSERT INTO t1 VALUES (@my_var);
182	SET insert_id=12;
183	INSERT INTO t3 VALUES (NULL);
184	==== Clean up ====
185	DROP PROCEDURE proc;
186	DROP FUNCTION func;
187	DROP TRIGGER trig;
188	DROP PROCEDURE proc1;
189	DROP FUNCTION func2;
190	DROP TRIGGER trig3;
191	DROP PROCEDURE proc4;
192	DROP FUNCTION func5;
193	DROP PREPARE prep6;
194	DROP TABLE t1, t2, t3, trigger_table, trigger_table2;
195	CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
196	INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
197	Warnings:
198	Warning 1592 Statement is not safe to log in statement format.
199	REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
200	Warnings:
201	Warning 1592 Statement is not safe to log in statement format.
202	UPDATE t1 SET a=1 LIMIT 1;
203	Warnings:
204	Warning 1592 Statement is not safe to log in statement format.
205	DELETE FROM t1 LIMIT 1;
206	Warnings:
207	Warning 1592 Statement is not safe to log in statement format.
208	CREATE PROCEDURE p1()
209	BEGIN
210	INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
211	REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
212	UPDATE t1 SET a=1 LIMIT 1;
213	DELETE FROM t1 LIMIT 1;
214	END\|
215	CALL p1();
216	Warnings:
217	Warning 1592 Statement is not safe to log in statement format.
218	Warning 1592 Statement is not safe to log in statement format.
219	Warning 1592 Statement is not safe to log in statement format.
220	Warning 1592 Statement is not safe to log in statement format.
221	DROP PROCEDURE p1;
222	DROP TABLE t1;