| Bug #12461 | SP's are replicated as stm calls in rbr. RAND() produce diff results on slave | ||
|---|---|---|---|
| Submitted: | 9 Aug 2005 14:45 | Modified: | 30 Sep 2005 18:37 |
| Reporter: | Jonathan Miller | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S1 (Critical) |
| Version: | 5.0-wl1012 | OS: | Linux (Linux) |
| Assigned to: | Mats Kindahl | CPU Architecture: | Any |
[17 Aug 2005 19:52]
Jonathan Miller
Another example: + create table test.t2 (n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)); + create table test.t3 (n MEDIUMINT NOT NULL AUTO_INCREMENT, d DATETIME, PRIMARY KEY(n)); + create trigger test.t2_ai after insert on test.t2 for each row update test.t2 set f=f+3;// + create procedure test.p3() + begin + INSERT INTO test.t3 (d) VALUES (NOW()); + end// + create trigger test.t3_bi_t2 before insert on test.t2 for each row call test.p3()// + create procedure test.p2() + begin + INSERT INTO test.t2 (f,d) VALUES (RAND(),NOW()); + end// + select * from test.t2; + n f d + 1 30.3579 2005-08-17 22:42:13 + 2 27.4394 2005-08-17 22:42:14 + 3 24.1234 2005-08-17 22:42:15 + 4 21.2988 2005-08-17 22:42:16 + 5 18.1236 2005-08-17 22:42:17 + 6 15.7219 2005-08-17 22:42:18 + 7 12.2387 2005-08-17 22:42:19 + 8 9.02777 2005-08-17 22:42:20 + 9 6.4227 2005-08-17 22:42:21 + 10 3.03022 2005-08-17 22:42:22 + select * from test.t3; + n d + 1 2005-08-17 22:42:13 + 2 2005-08-17 22:42:14 + 3 2005-08-17 22:42:15 + 4 2005-08-17 22:42:16 + 5 2005-08-17 22:42:17 + 6 2005-08-17 22:42:18 + 7 2005-08-17 22:42:19 + 8 2005-08-17 22:42:20 + 9 2005-08-17 22:42:21 + 10 2005-08-17 22:42:22 + select * from test.t2; + n f d + 1 30.5827 2005-08-17 22:42:13 + 2 27.1274 2005-08-17 22:42:14 + 3 24.8888 2005-08-17 22:42:15 + 4 21.0617 2005-08-17 22:42:16 + 5 18.6424 2005-08-17 22:42:17 + 6 15.0266 2005-08-17 22:42:18 + 7 12.2057 2005-08-17 22:42:19 + 8 9.94891 2005-08-17 22:42:20 + 9 6.12739 2005-08-17 22:42:21 + 10 3.79025 2005-08-17 22:42:22 + select * from test.t3; + n d + 1 2005-08-17 22:42:13 + 2 2005-08-17 22:42:14 + 3 2005-08-17 22:42:15 + 4 2005-08-17 22:42:16 + 5 2005-08-17 22:42:17 + 6 2005-08-17 22:42:18 + 7 2005-08-17 22:42:19 + 8 2005-08-17 22:42:20 + 9 2005-08-17 22:42:21 + 10 2005-08-17 22:42:22 + + <End test section 2 (Tiggers & SP)> + ----------------------------------- + DROP PROCEDURE test.p2; + DROP TRIGGER test.t2_ai; + DROP TRIGGER test.t3_bi_t2; + DROP TABLE test.t2; + DROP TABLE test.t3;
[18 Aug 2005 15:56]
Jonathan Miller
A Different test case using UUID() + CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, PRIMARY KEY(a)); + INSERT INTO test.t1 VALUES(1,UUID()); + create procedure test.p1() + begin + INSERT INTO test.t1 VALUES(2,UUID()); + end| + CALL test.p1(); + SELECT * FROM test.t1; + a blob_column + 1 35b204d6-614f-1028-bc79-00e081203f51 + 2 35b27c04-614f-1028-bc79-00e081203f51 + SHOW CREATE TABLE test.t1; + Table Create Table + t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL default '0', + `blob_column` longblob, + PRIMARY KEY (`a`) + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + SELECT * FROM test.t1; + a blob_column + 1 35b204d6-614f-1028-bc79-00e081203f51 + 2 35b6f4aa-614f-1028-beb8-00e081203f51 + DROP PROCEDURE IF EXISTS test.p1; + DROP TABLE test.t1;
[18 Aug 2005 16:28]
Jonathan Miller
Here is a different test case with USER and CURRENT_USER() + CREATE USER tester IDENTIFIED BY 'test'; + GRANT ALL ON test.* TO 'tester'@'%' IDENTIFIED BY 'test'; + GRANT ALL ON test.* TO ''@'localhost%'; + FLUSH PRIVILEGES; + CREATE TABLE test.t1 (a INT, users VARCHAR(255), PRIMARY KEY(a)); + INSERT INTO test.t1 VALUES(1,USER()); + INSERT INTO test.t1 VALUES(2,CURRENT_USER()); + create procedure test.p1() + begin + INSERT INTO test.t1 VALUES(3,USER()); + INSERT INTO test.t1 VALUES(4,CURRENT_USER()); + end| + CALL test.p1(); + SELECT * FROM test.t1; + a users + 1 tester@localhost + 2 @localhost + 3 tester@localhost + 4 @localhost + SELECT * FROM test.t1; + a users + 1 tester@localhost + 2 @localhost + 3 + 4 @
Description: I don't see that the replicating of a stored procedure is handled any diiferent in row base Vs. Statement based replication. Consider the following: # Includes -- source include/have_binlog_format_row.inc -- source include/master-slave.inc # Begin clean up test section connection master; --disable_warnings drop procedure if exists test.p1; drop table if exists test.t1; --enable_warnings # Begin test section 1 for non deterministic SP create table test.t1 (n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)); delimiter //; create procedure test.p1() begin INSERT INTO test.t1 (f,d) VALUES (RAND(),NOW()); end// delimiter ;// select * from mysql.proc where name='p1' and db='test'; let $1=10; disable_query_log; disable_result_log; while ($1) { call test.p1(); sleep 1; dec $1; } enable_result_log; enable_query_log; show binlog events; select * from test.t1; sync_slave_with_master; select * from test.t1; drop procedure test.p1; drop table test.t1; ******************* results ************** + Log_name Pos Event_type Server_id End_log_pos Info + master-bin.000001 4 Format_desc 1 102 Server ver: 5.0.11-beta-log, Binlog ver: 4 + master-bin.000001 102 Query 1 193 use `test`; drop table if exists test.t1 + master-bin.000001 193 Query 1 351 use `test`; create table test.t1 (n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)) + master-bin.000001 351 Query 1 499 use `test`; create procedure test.p1() + begin + INSERT INTO test.t1 (f,d) VALUES (RAND(),NOW()); + end + master-bin.000001 499 Query 1 584 use `test`; call test.p1() + master-bin.000001 584 Query 1 669 use `test`; call test.p1() + master-bin.000001 669 Query 1 754 use `test`; call test.p1() + master-bin.000001 754 Query 1 839 use `test`; call test.p1() + master-bin.000001 839 Query 1 924 use `test`; call test.p1() + master-bin.000001 924 Query 1 1009 use `test`; call test.p1() + master-bin.000001 1009 Query 1 1094 use `test`; call test.p1() + master-bin.000001 1094 Query 1 1179 use `test`; call test.p1() + master-bin.000001 1179 Query 1 1264 use `test`; call test.p1() + master-bin.000001 1264 Query 1 1349 use `test`; call test.p1() + select * from test.t1; + n f d + 1 0.103928 2005-08-09 16:49:34 + 2 0.86411 2005-08-09 16:49:35 + 3 0.00876278 2005-08-09 16:49:36 + 4 0.451485 2005-08-09 16:49:37 + 5 0.231137 2005-08-09 16:49:38 + 6 0.801229 2005-08-09 16:49:39 + 7 0.312733 2005-08-09 16:49:40 + 8 0.159977 2005-08-09 16:49:41 + 9 0.861688 2005-08-09 16:49:42 + 10 0.828509 2005-08-09 16:49:43 + select * from test.t1; + n f d + 1 0.132618 2005-08-09 16:49:34 + 2 0.964415 2005-08-09 16:49:35 + 3 0.424222 2005-08-09 16:49:36 + 4 0.227862 2005-08-09 16:49:37 + 5 0.866645 2005-08-09 16:49:38 + 6 0.649637 2005-08-09 16:49:39 + 7 0.648252 2005-08-09 16:49:40 + 8 0.292348 2005-08-09 16:49:41 + 9 0.516986 2005-08-09 16:49:42 + 10 0.707887 2005-08-09 16:49:43 **************************************** thoughts: Why are we passing the call to the slave? "+ master-bin.000001 924 Query 1 1009 use `test`; call test.p1()" I agree that the create should be replicated, because we want the SP on the slave, but if we are calling the sp() on the slave, then there is no diiference between statement base and row based. Also, please note that the now() function would have produce different results if the slave had been stopped and started, or if it was under enough stress. How to repeat: see above