MySQL Bugs: #11931: Replication of procedure containing SELECT INTO

Bug #11931	Replication of procedure containing SELECT INTO
Submitted:	14 Jul 2005 10:28	Modified:	11 Mar 2006 17:22
Reporter:	Are Casilla	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.7-beta-standard-log	OS:	Linux (DEBIAN 2.6.8-2-686)
Assigned to:	Andrei Elkin	CPU Architecture:	Any

Description:
SELECT test3.comment INTO strunk FROM test3 WHERE test3.id = '1';

Any SELECT INTO is not working on the SLAVE.
The Replication stop with ERROR

How to repeat:
CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `fname` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `t` VALUES (1, 'blablabla');

CREATE TABLE `test3` (
  `id` int(10) NOT NULL auto_increment,
  `comment` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  AUTO_INCREMENT=3 ;

INSERT INTO `test3` VALUES (1, 'testtest 1');
INSERT INTO `test3` VALUES (2, 'test 2');
  
DROP PROCEDURE IF EXISTS simpleproc2;
delimiter //
CREATE PROCEDURE simpleproc2 ()
    NOT DETERMINISTIC
    BEGIN
  	DECLARE strunk  varchar(128); /* The Trunk used for outgoing Dialing */
    SELECT test3.comment INTO strunk FROM test3 WHERE test3.id = '1';
    INSERT INTO t (fname) VALUES(strunk);
    END
    //
delimiter ;

CALL simpleproc2();

tested against 5.0.10 . 
On master i got:

mysql> select * from t; 
+----+------------+ 
| id | fname      |
+----+------------+
|  1 | blablabla  |
|  2 | testtest 1 |
+----+------------+
2 rows in set (0.00 sec)

on slave:

 select * from t;
+----+-----------+
| id | fname     |
+----+-----------+
|  1 | blablabla |
|  2 | NULL      |
+----+-----------+
2 rows in set (0.00 sec)

BUG#16621 might be related.

Please check also whether you get same error as in BUG#16983 (which is duplicate of 16621).

Cheers,

Geert

I could not reproduce. Since there is SELECT into var involved I can not exclude a relation
with #13227 which appeared to be fixed at the time I tried to summon it. 
Will write the final conclusion after testing in one more env.

Testing on two linux platforms FC3,Ubuntu does not hit this problem.

I suspect the artifact was gone along with #13227 `Trigger fired by replication does not allow SELECTs' somewhere in between of 5.0.12 and 5.0.19.
Although without calling explicitly a stored proc the test script of that bug luckily mimics the present case.

Changing status similarly to its parent (potential) status.