MySQL Bugs: #56421: Altering an event causes definer to change on slave

Bug #56421	Altering an event causes definer to change on slave
Submitted:	31 Aug 2010 19:14	Modified:	24 Sep 2010 18:59
Reporter:	Joe Grasse	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.48	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When you try to alter an event on a master server and don't set the definer, the definer gets changed on the slave.

How to repeat:
-- Create test event on master server:
CREATE SCHEMA IF NOT EXISTS `test`;
CREATE DEFINER='root'@'localhost' EVENT 
  `test`.`test` 
ON SCHEDULE 
  EVERY 1 SECOND 
STARTS 
  '2010-08-31 00:00:00' 
ON COMPLETION 
  PRESERVE 
ENABLE 
COMMENT 
  'test' 
DO 
  SELECT 1;

-- Check definer on slave:
SELECT event_schema,event_name,definer,status FROM information_schema.events ORDER BY event_schema,event_name;

-- Alter event on master server:
ALTER EVENT `test`.`test` DISABLE;

-- See that definer got changed incorrectly on slave:
SELECT event_schema,event_name,definer,status FROM information_schema.events ORDER BY event_schema,event_name;

Suggested fix:
Don't update the definer unless specifically set in the alter statement.

After looking at this more, it looks like if the definer is not provided in the ALTER statement then it will default to CURRENT_USER. So, since the master server is always filling in CURRENT_USER, if not provided, then during replication CURRENT_USER should be expanded and sent through the binlog so that the slave can replay it correctly.

Thank you for the report.

I can not repeat described behavior.

What query `SELECT event_schema,event_name,definer,status FROM information_schema.events ORDER BY event_schema,event_name;` returned on slave before you changed event on master?

Updated instructions:
-- Create test event on master server:
CREATE SCHEMA IF NOT EXISTS `test`;
CREATE DEFINER='root'@'localhost' EVENT 
  `test`.`test` 
ON SCHEDULE 
  EVERY 1 SECOND 
STARTS 
  '2010-08-31 00:00:00' 
ON COMPLETION 
  PRESERVE 
ENABLE 
COMMENT 
  'test' 
DO 
  SELECT 1;

-- Check definer on master:
SELECT event_schema,event_name,definer,status FROM information_schema.events ORDER BY event_schema,event_name;
+--------------+------------+----------------+---------+
| event_schema | event_name | definer        | status  |
+--------------+------------+----------------+---------+
| test         | test       | root@localhost | ENABLED |
+--------------+------------+----------------+---------+

-- Check definer on slave:
SELECT event_schema,event_name,definer,status FROM information_schema.events ORDER BY event_schema,event_name;
+--------------+------------+----------------+--------------------+
| event_schema | event_name | definer        | status             |
+--------------+------------+----------------+--------------------+
| test         | test       | root@localhost | SLAVESIDE_DISABLED |
+--------------+------------+----------------+--------------------+

-- Alter event on master server with user other than root@localhost (example msandbox@%):
ALTER EVENT `test`.`test` DISABLE;

-- Check definer on master:
SELECT event_schema,event_name,definer,status FROM information_schema.events ORDER BY event_schema,event_name;
+--------------+------------+------------+----------+
| event_schema | event_name | definer    | status   |
+--------------+------------+------------+----------+
| test         | test       | msandbox@% | DISABLED |
+--------------+------------+------------+----------+

-- See that definer got changed incorrectly on slave:
SELECT event_schema,event_name,definer,status FROM information_schema.events ORDER BY event_schema,event_name;
+--------------+------------+---------+--------------------+
| event_schema | event_name | definer | status             |
+--------------+------------+---------+--------------------+
| test         | test       | @       | SLAVESIDE_DISABLED |
+--------------+------------+---------+--------------------+

Thank you for the feedback.

According to http://dev.mysql.com/doc/refman/5.1/en/alter-event.html: "When a user executes a successful ALTER EVENT  statement, that user becomes the definer for the affected event. " So this is not a bug.

The problem I am reporting is the definer on the slave changing to "@" not the definer on the master changing to "msandbox@%".

I don't understand how you think this isn't a bug. After updating an event on the master the slave shows the definer as "@", which is not a valid user.

I am sorry, missed it is empty on your side.

This is duplicate of bug #48321 Please upgrade.