List:Commits« Previous MessageNext Message »
From:Andrei Elkin Date:January 27 2006 11:34am
Subject:bk commit into 5.0 tree (aelkin:1.2010) BUG#13227
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of elkin. When elkin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2010 06/01/27 13:34:47 aelkin@stripped +2 -0
  BUG#13227 test case to examine that triggers firing by an update on slave and that using select
  work ok.

  mysql-test/r/rpl_trigger_select.result
    1.1 06/01/27 13:34:34 aelkin@stripped +727 -0
    New BitKeeper file ``mysql-test/r/rpl_trigger_select.result''

  mysql-test/r/rpl_trigger_select.result
    1.0 06/01/27 13:34:34 aelkin@stripped +0 -0
    BitKeeper file /usr_rh9/home/elkin.rh9/MySQL/FIXES/mysql-5.0-bug13227/mysql-test/r/rpl_trigger_select.result

  mysql-test/t/rpl_trigger_select.test
    1.1 06/01/26 16:27:04 aelkin@stripped +71 -0

  mysql-test/t/rpl_trigger_select.test
    1.0 06/01/26 16:27:04 aelkin@stripped +0 -0
    BitKeeper file /usr_rh9/home/elkin.rh9/MySQL/FIXES/mysql-5.0-bug13227/mysql-test/t/rpl_trigger_select.test

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	aelkin
# Host:	dsl-hkigw8-feb0de00-199.dhcp.inet.fi
# Root:	/usr_rh9/home/elkin.rh9/MySQL/FIXES/mysql-5.0-bug13227
--- New file ---
+++ mysql-test/r/rpl_trigger_select.result	06/01/27 13:34:34
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
test case for BUG#13227
-------------------
10
-------------------
drop table if exists t110;
drop table if exists t210,t310;
create table t110 (f1 int)  /* 2 replicate */;
insert into t110 values (-5);
insert into t110 values (-4);
insert into t110 values (-3);
insert into t110 values (-2);
insert into t110 values (-1);
select * from t110;
f1
-5
-4
-3
-2
-1
create trigger trg110 before update on t110 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t210 where f1=NEW.f1;
INSERT INTO t310 values (r);
end|
create table t210 (f1 int, f2 int) /* slave local */;
create table t310 (f3 int) /* slave local */;
insert into t210 values (5, 5*100);
insert into t210 values (4, 4*100);
insert into t210 values (3, 3*100);
insert into t210 values (2, 2*100);
insert into t210 values (1, 1*100);
select * from t210;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t110 SET f1=5 where f1=-5;
SELECT * from t110 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t310 /* must be f3 5*100 */;
f3
500
UPDATE t110 SET f1=5 where f1=-5;
UPDATE t110 SET f1=4 where f1=-4;
UPDATE t110 SET f1=3 where f1=-3;
UPDATE t110 SET f1=2 where f1=-2;
UPDATE t110 SET f1=1 where f1=-1;
SELECT * from t110 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t310 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg110;
drop table t210,t310;
drop table t110;
-------------------
9
-------------------
drop table if exists t19;
drop table if exists t29,t39;
create table t19 (f1 int)  /* 2 replicate */;
insert into t19 values (-5);
insert into t19 values (-4);
insert into t19 values (-3);
insert into t19 values (-2);
insert into t19 values (-1);
select * from t19;
f1
-5
-4
-3
-2
-1
create trigger trg19 before update on t19 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t29 where f1=NEW.f1;
INSERT INTO t39 values (r);
end|
create table t29 (f1 int, f2 int) /* slave local */;
create table t39 (f3 int) /* slave local */;
insert into t29 values (5, 5*100);
insert into t29 values (4, 4*100);
insert into t29 values (3, 3*100);
insert into t29 values (2, 2*100);
insert into t29 values (1, 1*100);
select * from t29;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t19 SET f1=5 where f1=-5;
SELECT * from t19 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t39 /* must be f3 5*100 */;
f3
500
UPDATE t19 SET f1=5 where f1=-5;
UPDATE t19 SET f1=4 where f1=-4;
UPDATE t19 SET f1=3 where f1=-3;
UPDATE t19 SET f1=2 where f1=-2;
UPDATE t19 SET f1=1 where f1=-1;
SELECT * from t19 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t39 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg19;
drop table t29,t39;
drop table t19;
-------------------
8
-------------------
drop table if exists t18;
drop table if exists t28,t38;
create table t18 (f1 int)  /* 2 replicate */;
insert into t18 values (-5);
insert into t18 values (-4);
insert into t18 values (-3);
insert into t18 values (-2);
insert into t18 values (-1);
select * from t18;
f1
-5
-4
-3
-2
-1
create trigger trg18 before update on t18 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t28 where f1=NEW.f1;
INSERT INTO t38 values (r);
end|
create table t28 (f1 int, f2 int) /* slave local */;
create table t38 (f3 int) /* slave local */;
insert into t28 values (5, 5*100);
insert into t28 values (4, 4*100);
insert into t28 values (3, 3*100);
insert into t28 values (2, 2*100);
insert into t28 values (1, 1*100);
select * from t28;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t18 SET f1=5 where f1=-5;
SELECT * from t18 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t38 /* must be f3 5*100 */;
f3
500
UPDATE t18 SET f1=5 where f1=-5;
UPDATE t18 SET f1=4 where f1=-4;
UPDATE t18 SET f1=3 where f1=-3;
UPDATE t18 SET f1=2 where f1=-2;
UPDATE t18 SET f1=1 where f1=-1;
SELECT * from t18 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t38 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg18;
drop table t28,t38;
drop table t18;
-------------------
7
-------------------
drop table if exists t17;
drop table if exists t27,t37;
create table t17 (f1 int)  /* 2 replicate */;
insert into t17 values (-5);
insert into t17 values (-4);
insert into t17 values (-3);
insert into t17 values (-2);
insert into t17 values (-1);
select * from t17;
f1
-5
-4
-3
-2
-1
create trigger trg17 before update on t17 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t27 where f1=NEW.f1;
INSERT INTO t37 values (r);
end|
create table t27 (f1 int, f2 int) /* slave local */;
create table t37 (f3 int) /* slave local */;
insert into t27 values (5, 5*100);
insert into t27 values (4, 4*100);
insert into t27 values (3, 3*100);
insert into t27 values (2, 2*100);
insert into t27 values (1, 1*100);
select * from t27;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t17 SET f1=5 where f1=-5;
SELECT * from t17 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t37 /* must be f3 5*100 */;
f3
500
UPDATE t17 SET f1=5 where f1=-5;
UPDATE t17 SET f1=4 where f1=-4;
UPDATE t17 SET f1=3 where f1=-3;
UPDATE t17 SET f1=2 where f1=-2;
UPDATE t17 SET f1=1 where f1=-1;
SELECT * from t17 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t37 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg17;
drop table t27,t37;
drop table t17;
-------------------
6
-------------------
drop table if exists t16;
drop table if exists t26,t36;
create table t16 (f1 int)  /* 2 replicate */;
insert into t16 values (-5);
insert into t16 values (-4);
insert into t16 values (-3);
insert into t16 values (-2);
insert into t16 values (-1);
select * from t16;
f1
-5
-4
-3
-2
-1
create trigger trg16 before update on t16 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t26 where f1=NEW.f1;
INSERT INTO t36 values (r);
end|
create table t26 (f1 int, f2 int) /* slave local */;
create table t36 (f3 int) /* slave local */;
insert into t26 values (5, 5*100);
insert into t26 values (4, 4*100);
insert into t26 values (3, 3*100);
insert into t26 values (2, 2*100);
insert into t26 values (1, 1*100);
select * from t26;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t16 SET f1=5 where f1=-5;
SELECT * from t16 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t36 /* must be f3 5*100 */;
f3
500
UPDATE t16 SET f1=5 where f1=-5;
UPDATE t16 SET f1=4 where f1=-4;
UPDATE t16 SET f1=3 where f1=-3;
UPDATE t16 SET f1=2 where f1=-2;
UPDATE t16 SET f1=1 where f1=-1;
SELECT * from t16 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t36 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg16;
drop table t26,t36;
drop table t16;
-------------------
5
-------------------
drop table if exists t15;
drop table if exists t25,t35;
create table t15 (f1 int)  /* 2 replicate */;
insert into t15 values (-5);
insert into t15 values (-4);
insert into t15 values (-3);
insert into t15 values (-2);
insert into t15 values (-1);
select * from t15;
f1
-5
-4
-3
-2
-1
create trigger trg15 before update on t15 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t25 where f1=NEW.f1;
INSERT INTO t35 values (r);
end|
create table t25 (f1 int, f2 int) /* slave local */;
create table t35 (f3 int) /* slave local */;
insert into t25 values (5, 5*100);
insert into t25 values (4, 4*100);
insert into t25 values (3, 3*100);
insert into t25 values (2, 2*100);
insert into t25 values (1, 1*100);
select * from t25;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t15 SET f1=5 where f1=-5;
SELECT * from t15 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t35 /* must be f3 5*100 */;
f3
500
UPDATE t15 SET f1=5 where f1=-5;
UPDATE t15 SET f1=4 where f1=-4;
UPDATE t15 SET f1=3 where f1=-3;
UPDATE t15 SET f1=2 where f1=-2;
UPDATE t15 SET f1=1 where f1=-1;
SELECT * from t15 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t35 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg15;
drop table t25,t35;
drop table t15;
-------------------
4
-------------------
drop table if exists t14;
drop table if exists t24,t34;
create table t14 (f1 int)  /* 2 replicate */;
insert into t14 values (-5);
insert into t14 values (-4);
insert into t14 values (-3);
insert into t14 values (-2);
insert into t14 values (-1);
select * from t14;
f1
-5
-4
-3
-2
-1
create trigger trg14 before update on t14 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t24 where f1=NEW.f1;
INSERT INTO t34 values (r);
end|
create table t24 (f1 int, f2 int) /* slave local */;
create table t34 (f3 int) /* slave local */;
insert into t24 values (5, 5*100);
insert into t24 values (4, 4*100);
insert into t24 values (3, 3*100);
insert into t24 values (2, 2*100);
insert into t24 values (1, 1*100);
select * from t24;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t14 SET f1=5 where f1=-5;
SELECT * from t14 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t34 /* must be f3 5*100 */;
f3
500
UPDATE t14 SET f1=5 where f1=-5;
UPDATE t14 SET f1=4 where f1=-4;
UPDATE t14 SET f1=3 where f1=-3;
UPDATE t14 SET f1=2 where f1=-2;
UPDATE t14 SET f1=1 where f1=-1;
SELECT * from t14 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t34 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg14;
drop table t24,t34;
drop table t14;
-------------------
3
-------------------
drop table if exists t13;
drop table if exists t23,t33;
create table t13 (f1 int)  /* 2 replicate */;
insert into t13 values (-5);
insert into t13 values (-4);
insert into t13 values (-3);
insert into t13 values (-2);
insert into t13 values (-1);
select * from t13;
f1
-5
-4
-3
-2
-1
create trigger trg13 before update on t13 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t23 where f1=NEW.f1;
INSERT INTO t33 values (r);
end|
create table t23 (f1 int, f2 int) /* slave local */;
create table t33 (f3 int) /* slave local */;
insert into t23 values (5, 5*100);
insert into t23 values (4, 4*100);
insert into t23 values (3, 3*100);
insert into t23 values (2, 2*100);
insert into t23 values (1, 1*100);
select * from t23;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t13 SET f1=5 where f1=-5;
SELECT * from t13 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t33 /* must be f3 5*100 */;
f3
500
UPDATE t13 SET f1=5 where f1=-5;
UPDATE t13 SET f1=4 where f1=-4;
UPDATE t13 SET f1=3 where f1=-3;
UPDATE t13 SET f1=2 where f1=-2;
UPDATE t13 SET f1=1 where f1=-1;
SELECT * from t13 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t33 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg13;
drop table t23,t33;
drop table t13;
-------------------
2
-------------------
drop table if exists t12;
drop table if exists t22,t32;
create table t12 (f1 int)  /* 2 replicate */;
insert into t12 values (-5);
insert into t12 values (-4);
insert into t12 values (-3);
insert into t12 values (-2);
insert into t12 values (-1);
select * from t12;
f1
-5
-4
-3
-2
-1
create trigger trg12 before update on t12 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t22 where f1=NEW.f1;
INSERT INTO t32 values (r);
end|
create table t22 (f1 int, f2 int) /* slave local */;
create table t32 (f3 int) /* slave local */;
insert into t22 values (5, 5*100);
insert into t22 values (4, 4*100);
insert into t22 values (3, 3*100);
insert into t22 values (2, 2*100);
insert into t22 values (1, 1*100);
select * from t22;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t12 SET f1=5 where f1=-5;
SELECT * from t12 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t32 /* must be f3 5*100 */;
f3
500
UPDATE t12 SET f1=5 where f1=-5;
UPDATE t12 SET f1=4 where f1=-4;
UPDATE t12 SET f1=3 where f1=-3;
UPDATE t12 SET f1=2 where f1=-2;
UPDATE t12 SET f1=1 where f1=-1;
SELECT * from t12 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t32 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg12;
drop table t22,t32;
drop table t12;
-------------------
1
-------------------
drop table if exists t11;
drop table if exists t21,t31;
create table t11 (f1 int)  /* 2 replicate */;
insert into t11 values (-5);
insert into t11 values (-4);
insert into t11 values (-3);
insert into t11 values (-2);
insert into t11 values (-1);
select * from t11;
f1
-5
-4
-3
-2
-1
create trigger trg11 before update on t11 /* slave local */
for each row
begin
DECLARE r integer;
SELECT f2 INTO r FROM t21 where f1=NEW.f1;
INSERT INTO t31 values (r);
end|
create table t21 (f1 int, f2 int) /* slave local */;
create table t31 (f3 int) /* slave local */;
insert into t21 values (5, 5*100);
insert into t21 values (4, 4*100);
insert into t21 values (3, 3*100);
insert into t21 values (2, 2*100);
insert into t21 values (1, 1*100);
select * from t21;
f1	f2
5	500
4	400
3	300
2	200
1	100
UPDATE t11 SET f1=5 where f1=-5;
SELECT * from t11 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
f1
5
-4
-3
-2
-1
SELECT * from t31 /* must be f3 5*100 */;
f3
500
UPDATE t11 SET f1=5 where f1=-5;
UPDATE t11 SET f1=4 where f1=-4;
UPDATE t11 SET f1=3 where f1=-3;
UPDATE t11 SET f1=2 where f1=-2;
UPDATE t11 SET f1=1 where f1=-1;
SELECT * from t11 /* must be f1 5 ... 1 */;
f1
5
4
3
2
1
SELECT * from t31 /* must be f3 5 * 100 ...  100 */;
f3
500
400
300
200
100
drop trigger trg11;
drop table t21,t31;
drop table t11;

--- New file ---
+++ mysql-test/t/rpl_trigger_select.test	06/01/26 16:27:04
#
# Test specific triggers including SELECT into var with replication
# #13227

source include/master-slave.inc;
let $max_rows=5;

### Setup

connection master;
create table t1 (f1 int);
let $i=$max_rows;
while ($i)
{
        eval insert into t1 values (-$i);
        dec $i;
}

connection slave;
delimiter |;
create trigger trg1 before update on t1  #invisible to master
for each row
begin
  DECLARE r integer;
  SELECT f2 INTO r FROM t2 where f1=NEW.f1;
  INSERT INTO t3 values (r);
end|
delimiter ;|
create table t2 (f1 int, f2 int);        #invisible to master
create table t3 (f3 int);                #invisible to master
let $i=$max_rows;
while ($i) 
{
        eval insert into t2 values ($i, $i*100);
        dec $i;
}

### Test
#connection slave;
# trigger works as specified when updates from slave
select * from t1;
select * from t2;
eval UPDATE t1 SET f1=$max_rows where f1=-$max_rows;
dec $max_rows;
SELECT * from t1 /* must be f1 1 -2 -3 */;
SELECT * from t3 /* must be f2 100 */;

connection master;  #commented master tried 20 times on slave was ok
let $i=$max_rows;
while ($i)
{
        eval UPDATE t1 SET f1=$i where f1=-$i;
        dec $i;
}

connection slave;
eval SELECT * from t1 /* must be f1 1 ... $max_rows 1 */;
eval SELECT * from t3 /* must be f3 ++$max_rows * 100 ...  100 */;

### cleanup
#connection slave;
#drop trigger trg1;
#drop table t2,t3;

#connection master;
#drop table t1;


#
# End of test
#

Thread
bk commit into 5.0 tree (aelkin:1.2010) BUG#13227Andrei Elkin27 Jan