MySQL Bugs: #36658: trigger is deleted when rename table

Bug #36658	trigger is deleted when rename table
Submitted:	12 May 2008 2:15	Modified:	5 Oct 2008 16:36
Reporter:	ws lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	5.1.23 rc, 5.1 BK, 5.1-telco	OS:	Solaris (5.10)
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
replica is 2.
i have data sql node 2 and data node 2.

when rename table in one sql dode, the other sql node trigger was deleted.

How to repeat:
----------------------
<step1> In sqlnode 1
----------------------
test> create table t1(a int, b varchar(10), c date) engine=ndb;

tess> delimiter //
CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
   SET new.c = date(now());
End //
delimiter ;

test> select trigger_name,event_object_table from information_schema.triggers where trigger_name='trg1';
+--------------+--------------------+
| trigger_name | event_object_table |
+--------------+--------------------+
| trg1         | t1                 | 
+--------------+--------------------+

----------------------
<step2> In sqlnode 2
----------------------
test> delimiter //
CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
   SET new.c = date(now());
End //
delimiter ;

test> select trigger_name,event_object_table from information_schema.triggers where trigger_name='trg1';
+--------------+--------------------+
| trigger_name | event_object_table |
+--------------+--------------------+
| trg1         | t1                 | 
+--------------+--------------------+

----------------------
<step3> In sqlnode 1
----------------------
test> rename table t1 to t2;
test> select trigger_name,event_object_table from information_schema.triggers where trigger_name='trg1';
+--------------+--------------------+
| trigger_name | event_object_table |
+--------------+--------------------+
| trg1         | t2                 | 
+--------------+--------------------+

----------------------
<step4> In sqlnode 2
----------------------
test> select trigger_name,event_object_table from information_schema.triggers where trigger_name='trg1';
Empty set (0.04 sec)

In this result, in sqlnode 2 triggers is deleted.

i want fix about this bug.

Thank you for the report.

Verified as described.

I believe the problem here is that rename table does not invoke
the proper rename code in remote mysqld's. Instead it drops the
corresponding .frm file and autodiscovers the table from ndb
(copies the frm from ndb DataDictionary). More specifically in
ndb_binlog_thread_handle_schema_event the 
case SOT_RENAME_TABLE:
  // fall through
should not fall through to the ndb_create_table_from_engine, but
instead invoke the proper code for renaming the table something
similar like how DROP DATABASE; is distributed:

case SOT_RENAME:
  if (ndbcluster_check_if_local_table(schema->db, schema->name))
  {
    DBUG_PRINT("info", ("NDB Binlog: Skipping renaming locally defined table '%s.%s'",
                         schema->db, schema->name));
    sql_print_error("NDB Binlog: Skipping renaming locally defined table '%s.%s' from binlog schema event '%s' from node %d. ",
                     schema->db, schema->name, schema->query,
                     schema->node_id);
   }
   else
   {
     const int no_print_error[1]= {0};
     run_query(thd, schema->query,
               schema->query + schema->query_length,
               no_print_error,    /* print error */
               TRUE);   /* don't binlog the query */
      ....
     /* acknowledge this query _after_ epoch completion */
     post_epoch_unlock= 1;
   }

Something more is needed in the above code to ensure that the
rename does not call ha_ndbcluster::rename_table since this would
fail (some kind of state variable in handler object that specifies
that this is a local rename only).

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/48851

2633 Martin Skold	2008-07-01
      bug#36658 trigger is deleted when rename table: Do local rename and drop table (without calling ndb) when remote schema events are received in binlog thread.

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/48852

2633 Martin Skold	2008-07-01
      bug#36658 trigger is deleted when rename table: Do local rename and drop table (without calling ndb) when remote schema events are received in binlog thread.

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/51018

2633 Martin Skold	2008-08-06
      bug#36658 trigger is deleted when rename table: Do local rename and drop table (without calling ndb) when remote schema events are received in binlog thread.

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/51019

2633 Martin Skold	2008-08-06
      bug#36658 trigger is deleted when rename table: Do local rename and drop table (without calling ndb) when remote schema events are received in binlog thread.

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/51070

2642 Martin Skold	2008-08-07
      bug#36658 trigger is deleted when rename table: Do local rename and drop table (without calling ndb) when remote schema events are received in binlog thread.

Pushed to mysql-5.1-telco-6.2

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/51085

2644 Martin Skold	2008-08-07
      bug#36658 trigger is deleted when rename table: Ignore missing table.

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/51087

2650 Martin Skold	2008-08-07 [merge]
      Merge

Documented in the NDB 6.2.16 and 6.3.17 changelogs as follows:

        Renaming an NDBCLUSTER table on one SQL node, caused a trigger on this
        table to be deleted on another SQL node.

Already documented for NDB 6.2/6.3; closed.

Pushed into 6.0.6-alpha  (revid:martin.skold@mysql.com-20080807102639-d7pdkuxaatb38c1h) (version source revid:jonas@mysql.com-20080812185642-1nevjb94zj621dqx) (pib:5)