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

[12 May 2008 2:15] ws lee
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.
[12 May 2008 19:27] Sveta Smirnova
Thank you for the report.

Verified as described.
[23 May 2008 12:29] Martin Skold
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;
   }
[23 May 2008 12:33] Martin Skold
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).
[2 Jul 2008 5:08] Bugs System
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.
[2 Jul 2008 5:08] Bugs System
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.
[6 Aug 2008 15:03] Bugs System
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.
[6 Aug 2008 15:12] Bugs System
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.
[7 Aug 2008 5:30] Bugs System
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.
[7 Aug 2008 8:08] Martin Skold
Pushed to mysql-5.1-telco-6.2
[7 Aug 2008 10:26] Bugs System
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.
[7 Aug 2008 11:51] Bugs System
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
[11 Sep 2008 18:07] Jon Stephens
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.
[5 Oct 2008 16:36] Jon Stephens
Already documented for NDB 6.2/6.3; closed.
[12 Dec 2008 23:28] Bugs System
Pushed into 6.0.6-alpha  (revid:martin.skold@mysql.com-20080807102639-d7pdkuxaatb38c1h) (version source revid:jonas@mysql.com-20080812185642-1nevjb94zj621dqx) (pib:5)