MySQL Bugs: #27960: schema operations are not being recorded in ndb_apply

Bug #27960	schema operations are not being recorded in ndb_apply_status
Submitted:	19 Apr 2007 18:52	Modified:	9 Mar 2023 1:08
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Replication	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.2	OS:	Linux (32 bit)
Assigned to:		CPU Architecture:	Any
Tags:	mysql-5.1.18 ndb-6.2.1

Description:
Hi,

In testing generic replication for ndb_apply_status I noticed that schema operations that touch NDB tables such as DROP DATABASE where not being recorded. 

i.e. the start_pos and end_pos do not reflect that of the DROP that was recorded in the masters binlog.

This also includes CREATE, ALTER, etc...

The major problem with this is if a slave has to be restarted for some reason and the use has done a change master to the last recorded end_pos, then the next statement to be hit would be the DROP or the CREATE which will cause the slave to error and fail due to the DATABASE already being gone, or the table already existing.

+ DROP DATABASE tpcb;
+ CREATE TABLE T1 (c1 INT KEY)ENGINE=NDB;
+ SELECT * FROM  mysql.ndb_apply_status;
+ server_id     epoch   log_name        start_pos       end_pos
+ 1     0       master-bin.000001       2757    2940
+ select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos
+ from mysql.ndb_apply_status;
+ @log_name:=log_name   @start_pos:=start_pos   @end_pos:=end_pos
+ <log_name>    <start_pos>     <end_pos>
+ SHOW BINLOG EVENTS in 'master-bin.000001' from 2757;
+ Log_name      Pos     Event_type      Server_id       End_log_pos     Info
+ master-bin.000001     2757    Table_map       1       2809    table_id: 26 (tpcb.history)
+ master-bin.000001     2809    Write_rows      1       2940    table_id: 26 flags: STMT_END_F
+ master-bin.000001     2940    Query   1       3021    DROP DATABASE tpcb
+ master-bin.000001     3021    Query   1       3122    use `test`; CREATE TABLE T1 (c1 INT KEY)ENGINE=NDB
+ show binlog events in 'master-bin.000001'  from <start_pos> limit 1,1;
+ Log_name      Pos     Event_type      Server_id       End_log_pos     Info
+ master-bin.000001     #       Write_rows      1       <end_pos>       table_id: # flags: STMT_END_F

How to repeat:
I will attach test files for the reproduction.

Current workaround for this would be:
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N;
mysql> START SLAVE;

Suggested fix:
Any operation including schema that touches an NDB table be included in the ndb_apply_status table.

test file

Attachment: rpl_ndb_stm_innodb.test (application/octet-stream, text), 3.56 KiB.

option file

Attachment: rpl_ndb_stm_innodb-master.opt (application/octet-stream, text), 9 bytes.

include file

Attachment: select_ndb_apply_status.inc (application/octet-stream, text), 312 bytes.

include file

Attachment: show_binlog_events2.inc (application/octet-stream, text), 227 bytes.

include file

Attachment: tpcb.inc (application/octet-stream, text), 4.04 KiB.

Posted by developer:
 
The start_pos and end_pos columsn in mysql.ndb_apply_status are updated when applying DDL.

This was fixed and several tests for verifying the behavior was added as part of "WL#15455 NDB Replica threadsafe".

Fixed in NDB 8.0.33 by WL#15455. 

See same for info.

Closed.