Bug #15963 SBR does not show "Definer" correctly on slave host (rpl_ddl.test)
Submitted: 23 Dec 2005 13:22 Modified: 14 Mar 2006 19:14
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.18/5.1.3 OS:Linux (Linux)
Assigned to: Alexander Nozdrin

[23 Dec 2005 13:22] Jonathan Miller
Description:
Test case: rpl_ddl.test
let $my_stmt= CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1";
let $my_master_commit= true;
let $my_slave_commit= true;
--source include/rpl_stmt_seq.inc
--vertical_results
--replace_column 5 # 6 #
SHOW PROCEDURE STATUS LIKE 'p1';
--disable_query_log
SELECT '-------- switch to slave -------' as "";
--enable_query_log
connection slave;
--replace_column 5 # 6 #
SHOW PROCEDURE STATUS LIKE 'p1';
connection master;
--horizontal_results
let $my_stmt= ALTER PROCEDURE p1 COMMENT "I have been altered";
let $my_master_commit= true;
let $my_slave_commit= true;
--source include/rpl_stmt_seq.inc
--vertical_results
--replace_column 5 # 6 #
SHOW PROCEDURE STATUS LIKE 'p1';
--disable_query_log
SELECT '-------- switch to slave -------' as "";
--enable_query_log
connection slave;
--replace_column 5 # 6 #
SHOW PROCEDURE STATUS LIKE 'p1';
connection master;
--horizontal_results

let $my_stmt= DROP PROCEDURE p1;
let $my_master_commit= true;
let $my_slave_commit= true;
--source include/rpl_stmt_seq.inc
--vertical_results
SHOW PROCEDURE STATUS LIKE 'p1';
--disable_query_log
SELECT '-------- switch to slave -------' as "";
--enable_query_log
connection slave;
SHOW PROCEDURE STATUS LIKE 'p1';
connection master;
--horizontal_results

*** Results SBR ****

-------- switch to master -------
SHOW PROCEDURE STATUS LIKE 'p1';
Db      mysqltest1
Name    p1
Type    PROCEDURE
Definer root@localhost
Modified        #
Created #
Security_type   DEFINER
Comment
        -------- switch to slave -------
SHOW PROCEDURE STATUS LIKE 'p1';
Db      mysqltest1
Name    p1
Type    PROCEDURE
Definer @
Modified        #
Created #
Security_type   DEFINER
Comment
######## ALTER PROCEDURE p1 COMMENT "I have been altered"  ########
-------- switch to master -------
SHOW PROCEDURE STATUS LIKE 'p1';
Db      mysqltest1
Name    p1
Type    PROCEDURE
Definer root@localhost
Modified        #
Created #
Security_type   DEFINER
Comment I have been altered
        -------- switch to slave -------
SHOW PROCEDURE STATUS LIKE 'p1';
Db      mysqltest1
Name    p1
Type    PROCEDURE
Definer @
Modified        #
Created #
Security_type   DEFINER
Comment I have been altered

*** Results SBR ****

-------- switch to master -------
SHOW PROCEDURE STATUS LIKE 'p1';
Db      mysqltest1
Name    p1
Type    PROCEDURE
Definer root@localhost
Modified        #
Created #
Security_type   DEFINER
Comment
        -------- switch to slave -------
SHOW PROCEDURE STATUS LIKE 'p1';
Db      mysqltest1
Name    p1
Type    PROCEDURE
Definer root@localhost
Modified        #
Created #
Security_type   DEFINER
Comment

######## ALTER PROCEDURE p1 COMMENT "I have been altered"  ########

-------- switch to master -------
SHOW PROCEDURE STATUS LIKE 'p1';
Db      mysqltest1
Name    p1
Type    PROCEDURE
Definer root@localhost
Modified        #
Created #
Security_type   DEFINER
Comment I have been altered
        -------- switch to slave -------
SHOW PROCEDURE STATUS LIKE 'p1';
Db      mysqltest1
Name    p1
Type    PROCEDURE
Definer root@localhost
Modified        #
Created #
Security_type   DEFINER
Comment I have been altered

Test will be added to disable file under this bug report number.

How to repeat:
 ./mysql-test-run --force --skip-ndbcluster --mysqld=--binlog-format=statement --do-test=rpl_ddl

Suggested fix:
Definer root@localhost
[23 Dec 2005 13:23] Jonathan Miller
Correction in bug report:

The second *** SBR results *****

Should have been 

*** RBR Results ****

Sorry
[16 Feb 2006 20:50] Dmitry Lenev
This problem is also repeatable for 5.0.18.

Bug #17247 in which this problem was reported for 5.0 tree and which contains perfect description of issues that arise because of this bug was marked as duplicate.
[7 Mar 2006 19:37] Alexander Nozdrin
Pushed into 5.0 tree, currently tagged 5.0.20.
[9 Mar 2006 21:03] Alexander Nozdrin
Pushed into 5.1 tree, currently tagged 5.1.8-beta.
[14 Mar 2006 19:14] Paul Dubois
Noted in 5.0.20, 5.1.8 changelogs.

The <literal>DEFINER</literal> value for stored routines was
not replicated. (Bug #15963)