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
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.0.18/5.1.3
• OS: Linux (Linux)
• Assigned to: Alexander Nozdrin
• CPU Architecture: Any

[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)