MySQL Bugs: #16425: Events: no DEFINER clause

Bug #16425	Events: no DEFINER clause
Submitted:	12 Jan 2006 4:19	Modified:	24 Mar 2007 8:22
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.6-alpha-debug	OS:	Linux (linux)
Assigned to:	Tomash Brechko	CPU Architecture:	Any
Tags:	rt_q1_2007

Description:
I quote Trudy Pelzer:

"Recall that, in order to make stored routines et al
work properly with RBR, we had to add the DEFINER
clause into the CREATE statements, since this is the
only way we have to ensure that the correct information
is sent to the slave. I think the same should/will be 
true for CREATE EVENT -- if so, we should add the
appropriate functionality right at the start."
See the DEFINER clause for CREATE TRIGGER:
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

How to repeat:
mysql> create event e_32a on schedule every 1 second do set @a=5;
Query OK, 1 row affected (0.00 sec)

mysql> create definer=current_user event e_32a on schedule every 1 second do set @a=5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'event e_32a on schedule every 1 second do set @a=5' at line 1

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8037

Back to Verified as WL#3337 touches the grammar. The latter introduces parsing of the definer clause but does not actually use it. This should be done by a patch for this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19263

ChangeSet@1.2416, 2007-02-02 20:43:33+03:00, kroki@moonlight.home +5 -0
  BUG#16425: Events: no DEFINER clause
  
  There was already support for CREATE DEFINER=... EVENT syntax in the
  parser, but DEFINER information was ignored.
  
  This patch adds processing of DEFINER, and a new ALTER DEFINER=...
  EVENT syntax.

Queued to 5.1-runtime.

Pushed into 5.1.17

I see two issues with this feature.

First, a simple event:

mysql> create definer=jon@localhost event e1 on schedule every 5 second do insert into foo values (now());
Query OK, 0 rows affected (0.00 sec)

Definer shown in mysql.event and information_schema.events:

mysql> select * from mysql.event where db='test' and name='e1'\G
*************************** 1. row ***************************
            db: test
          name: e1
          body: insert into foo values (now())
       definer: root@localhost
    execute_at: NULL
interval_value: 5
interval_field: SECOND
       created: 2007-03-08 16:12:15
      modified: 2007-03-08 16:17:16
 last_executed: NULL
        starts: 2007-03-08 06:12:15
          ends: NULL
        status: ENABLED
 on_completion: DROP
      sql_mode:
       comment:
1 row in set (0.00 sec)

mysql> select * from information_schema.events where event_schema='test' and event_name='e1'\G
*************************** 1. row ***************************
   EVENT_CATALOG: NULL
    EVENT_SCHEMA: test
      EVENT_NAME: e1
         DEFINER: root@localhost
      EVENT_BODY: SQL
EVENT_DEFINITION: insert into foo values (now())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: SECOND
        SQL_MODE:
          STARTS: 2007-03-08 06:12:15
            ENDS: NULL
          STATUS: ENABLED
   ON_COMPLETION: NOT PRESERVE
         CREATED: 2007-03-08 16:12:15
    LAST_ALTERED: 2007-03-08 16:17:16
   LAST_EXECUTED: NULL
   EVENT_COMMENT:
1 row in set (0.00 sec)

Issue #1. However, the definer is not shown in the output of SHOW CREATE EVENT:

mysql> show create event e1\G
*************************** 1. row ***************************
       Event: e1
    sql_mode:
Create Event: CREATE EVENT `e1` ON SCHEDULE EVERY 5 SECOND ON COMPLETION NOT PRESERVE ENABLE DO insert into foo values (now())
1 row in set (0.00 sec)

Issue #2. ALTER DEFINER=... EVENT ... does not work without a 'dummy' clause.

Observe:

mysql> alter definer=jon@localhost event e1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

mysql> select * from information_schema.events where event_schema='test' and event_name='e1'\G
*************************** 1. row ***************************
   EVENT_CATALOG: NULL
    EVENT_SCHEMA: test
      EVENT_NAME: e1
         DEFINER: root@localhost
      EVENT_BODY: SQL
EVENT_DEFINITION: insert into foo values (now())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: SECOND
        SQL_MODE:
          STARTS: 2007-03-08 06:12:15
            ENDS: NULL
          STATUS: ENABLED
   ON_COMPLETION: NOT PRESERVE
         CREATED: 2007-03-08 16:12:15
    LAST_ALTERED: 2007-03-08 16:17:16
   LAST_EXECUTED: NULL
   EVENT_COMMENT:
1 row in set (0.01 sec)

(Nothing changed, which is to be expected since we got an error.)

mysql> alter definer=jon@localhost event e1 enable;
Query OK, 0 rows affected (0.00 sec)

(This works, even though the ENABLE clause does nothing.)

mysql> select * from information_schema.events where event_schema='test' and event_name='e1'\G
*************************** 1. row ***************************
   EVENT_CATALOG: NULL
    EVENT_SCHEMA: test
      EVENT_NAME: e1
         DEFINER: jon@localhost
      EVENT_BODY: SQL
EVENT_DEFINITION: insert into foo values (now())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: SECOND
        SQL_MODE:
          STARTS: 2007-03-08 06:12:15
            ENDS: NULL
          STATUS: ENABLED
   ON_COMPLETION: NOT PRESERVE
         CREATED: 2007-03-08 16:12:15
    LAST_ALTERED: 2007-03-08 16:27:21
   LAST_EXECUTED: NULL
   EVENT_COMMENT:
1 row in set (0.01 sec)

mysql> alter definer=root@localhost event e1 comment '';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.events where event_schema='test' and event_name='e1'\G
*************************** 1. row ***************************
   EVENT_CATALOG: NULL
    EVENT_SCHEMA: test
      EVENT_NAME: e1
         DEFINER: root@localhost
      EVENT_BODY: SQL
EVENT_DEFINITION: insert into foo values (now())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: SECOND
        SQL_MODE:
          STARTS: 2007-03-08 06:12:15
            ENDS: NULL
          STATUS: ENABLED
   ON_COMPLETION: NOT PRESERVE
         CREATED: 2007-03-08 16:12:15
    LAST_ALTERED: 2007-03-08 16:29:41
   LAST_EXECUTED: NULL
   EVENT_COMMENT:
1 row in set (0.00 sec)

(Works, even though the comment is empty.)

-----

Issue #1 is perhaps not really an issue since (for example) SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION don't show the DEFINER clause either.

However, I think that Issue #2 should be fixed - it's silly to require a "nothing" clause to change the definer.

If you prefer, I'll document the addition of DEFINER, close this bug and open a new bug for Issue #2. Just let me know.

Hello Jon,

First of all, in the very first statement you do

  create definer=jon@localhost event ...

but the output shows

  definer: root@localhost

I think it's a result of wrong cut-n-paste, you never tell there's an error in that, and I also tested in latest 5.1, jon@localhost is printed as the definer.

Regarding issue #1: as you say it yourself later, other SHOW CREATE ... do not show the definer either.

Regarding issue #2:  I think this is intentional, that's why the definer comes before the object type, i.e. ALTER definer EVENT ...  You may think of the definer as a property of what will come next, so you should also provide the new altered data.  It works the same way with views, you have to say ALTER definer=... VIEW v AS ..., you can't omit AS part.  Alas, I should say "It _worked_ the same way for views", as the patch for this but has introduced the conflict in the parser, and ALTER definer=... VIEW ... no longer works :-/.  This will be fixed.

Hi Tomas,

Yes, I had two terminal windows open and running mysql in both, was logged in as root@localhost in one and jon@localhost in the other. The original CREATE EVENT and query on I_S.EVENTS should have been:

mysql> select version(), current_user;
+-------------------+----------------+
| version()         | current_user   |
+-------------------+----------------+
| 5.1.17-beta-debug | root@localhost |
+-------------------+----------------+
1 row in set (0.06 sec)

mysql> create definer=jon@localhost event e1 on schedule every 5 second do insert into foo values (now());
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.events where event_schema='test' and event_name='e1'\G
*************************** 1. row ***************************
   EVENT_CATALOG: NULL
    EVENT_SCHEMA: test
      EVENT_NAME: e1
         DEFINER: jon@localhost
      EVENT_BODY: SQL
EVENT_DEFINITION: insert into foo values (now())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: SECOND
        SQL_MODE:
          STARTS: 2007-03-08 12:33:12
            ENDS: NULL
          STATUS: ENABLED
   ON_COMPLETION: NOT PRESERVE
         CREATED: 2007-03-08 22:33:12
    LAST_ALTERED: 2007-03-08 22:33:12
   LAST_EXECUTED: NULL
   EVENT_COMMENT:
1 row in set (0.00 sec)

D'oh! :(

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21600

ChangeSet@1.2462, 2007-03-09 15:52:50+03:00, kroki@moonlight.home +3 -0
  Resolve one shift/reduce conflict introduced with the push of the fix
  for bug#16425: Events: no DEFINER clause.  The problem was that there
  were two rules
  
    ALTER view_algorithm_opt definer ... VIEW ...
    ALTER definer EVENT ...
  
  so when there was 'ALTER definer' in the input it was unclear if empty
  view_algorithm_opt should be executed or not.
  
  We solve this by introducing three distinct rules
  
    ALTER view_algorithm definer ... VIEW ...
    ALTER definer ... VIEW ...
    ALTER definer EVENT ...
  
  that remove the ambiguity.

The patch that resolves shift/reduce conflict introduced with the main fix is queued to 5.1-runtime.

Fixed in 5.1.17

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented addition of DEFINER support in 5.1.17 changelog, CREATE|ALTER EVENT Syntax.