Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.4, 10.6.27
-
None
-
None
Description
Replication breaks when the function DEFINER is an user account, while the same scenario works as expected when the DEFINER is a role.
Replication fails when: DEFINER = 'user1'@'localhost'
Last_Errno: 1142 or 1370
Replication succeeds with: DEFINER = role1
error cannot be reproduced.
CREATE FUNCTION results in different mysql.procs_priv contents on primary and replica.
– On Primary - Create database and objects
CREATE DATABASE testdb;
|
USE testdb;
|
|
|
CREATE TABLE holiday (d DATE);
|
INSERT INTO holiday VALUES (CURDATE());
|
|
|
CREATE TABLE t1 ( id INT PRIMARY KEY, val INT);
|
INSERT INTO t1 VALUES (1,0); |
– On Primary - Create user and role
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password'; |
CREATE ROLE role1;
|
|
|
GRANT SELECT ON testdb.holiday TO role1;
|
|
|
GRANT role1 TO 'user1'@'localhost'; |
SET DEFAULT ROLE role1 FOR 'user1'@'localhost'; |
– On Primary - Now create the DEFINER function
DELIMITER // |
|
|
CREATE DEFINER='user1'@'localhost' |
FUNCTION testdb.COUNT_HOLIDAY()
|
RETURNS INT
|
DETERMINISTIC
|
RETURN
|
(
|
SELECT COUNT(*)
|
FROM holiday
|
);
|
//
|
DELIMITER ;
|
– Check what happened on Primary and Replica
SHOW GRANTS FOR 'user1'@'localhost'; |
|
|
SELECT * FROM mysql.procs_priv WHERE Db='testdb' AND Routine_name='COUNT_HOLIDAY'; |
|
– Primary: empty, Replica: contains Execute,Alter Routine
Replication breaks with Last_Errno: 1142 when we login as `user1`@`localhost` to execute
UPDATE t1 SET val=COUNT_HOLIDAY();
|