MySQL Bugs: #48934: Delete from table; 3600 rows not replicated from MASTER to SLAVE

Bug #48934	Delete from table; 3600 rows not replicated from MASTER to SLAVE
Submitted:	20 Nov 2009 9:17	Modified:	19 Jul 2010 9:20
Reporter:	Jaap Postuma	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Replication	Severity:	S2 (Serious)
Version:	mysql-5.1-telco-7.0, 7.1.4b	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	delete, failing, replication

Description:
The command "DELETE FROM TABLE;" executed on the MASTER is not replicated to the SLAVE in case the deletion concerns 3600 rows. The "SHOW SLAVE STATUS;"
shows in first instance that the SECONDS_BEHIND_MASTER increases. After about 90 seconds the SLAVE stops running. The rows have been removed from the MASTER but not from the SLAVE.
The following error is printed in the error log. 

091120  9:22:24 [ERROR] Slave SQL: Could not execute Delete_rows event on table snadb.Towns; Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDB, Error_code: 1297; 

The MaxNoOfConcurrentOperations is already set to 250000, which should be more than enough.

In case of e.g. 500 rows the replication is executed successfully. Also the TRUNCATE command is working in case of 3600 rows.

In first instance the table is build with INSERT commands. Replication takes place without any problem. The tables are in sync. Also deleting a small number of rows does not give any problems.

For each Zone the follwoing cluster settings are applicable.

PL_2_3# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: SC_2_1:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=7    @192.168.0.3  (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0, Master)
id=9    @192.168.0.4  (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0)
 
[ndb_mgmd(MGM)] 2 node(s)
id=1    @192.168.0.1  (mysql-5.1.35 ndb-7.0.7)
id=2    @192.168.0.2  (mysql-5.1.35 ndb-7.0.7)
 
[mysqld(API)]   3 node(s)
id=3 (not connected, accepting connect from any host)
id=6    @192.168.0.3  (mysql-5.1.35 ndb-7.0.7)
id=8    @192.168.0.4  (mysql-5.1.35 ndb-7.0.7)
 
ndb_mgm> 

Contents of config.ini file.

# TCP/IP options:
[tcp default]
PortNumber=2202

# MySQL Cluster Management Server configuration
[ndb_mgmd]
Id=1
HostName=SC_2_1
DataDir=/home/mysql/nodes/SC_2_1

[ndb_mgmd]
Id=2
HostName=SC_2_2
Datadir=/home/mysql/nodes/SC_2_2

# MySQL Server configuration
# An empty mysql connection that is used by the ndb_xxx tools (like ndb_desc or ndb_restore)
[mysqld]
Id=3

[mysqld]
Id=6
HostName=PL_2_3

[mysqld]
Id=8
HostName=PL_2_4

# MySQL Cluster Data Node Daemon configuration
[ndbd default]
NoOfReplicas=2
DataMemory=10G
IndexMemory=2G
MaxNoOfConcurrentOperations=250000
MaxNoOfExecutionThreads=2

# Buffering and Logging
RedoBuffer=256M
UndoDataBuffer=256M

# Controlling Timeouts, Intervals and Disk Paging
TimeBetweenLocalCheckpoints=15

# Logging and checkpointing
NoOfFragmentLogFiles=300
FragmentLogFileSize=16M

[ndbd]
Id=7
HostName=PL_2_3
DataDir=/home/mysql/nodes/PL_2_3

[ndbd]
Id=9
HostName=PL_2_4
DataDir=/home/mysql/nodes/PL_2_4

Contents of my.cnf

 
# Options for mysqld process:
[mysqld]                          
 
# Enable binary logging
log-bin=binlog
binlog-format=mixed
 
# Configuration for GeoRed
server-id=1
log-slave-updates
binlog_cache_size=16M
skip-slave-start
ndb_log_orig
slave_allow_batching=1
 
# Changed default database engine from MyISAM to NDBCLUSTER
default-storage-engine=NDBCLUSTER
 
# Run NDB storage engine
ndbcluster
 
# Location of management server
ndb-connectstring=SC_2_1,SC_2_2
 
datadir=/home/mysql/nodes/PL_2_3
 
# Options for ndbd process:
[mysql_cluster]
 
# Location of management server
ndb-connectstring=SC_2_1,SC_2_2PL_2_3# 

How to repeat:
The problem is reproducable without any problem. No other mysql queries or background traffic is runnning.

Two quick questions:

- how long does it take to execute the DELETE on the master?
- what does the CREATE statement for the table look like?

The DELETE statement is executed very rapidly, for sure within 1 second.

The INSERT statement looks like. It is just a small row inside table Towns within database snadb.

"INSERT INTO Towns (CityNumber,CityId,TimeStamp) values ($citynumber,$citynumber,${TimeStamp})" snadb

I was actually more interested in the output of 

  SHOW CREATE TABLE `Towns`; 

...

mysql> SHOW CREATE TABLE `Towns`;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Towns | CREATE TABLE `Towns` (
  `CityNumber` int(15) DEFAULT NULL,
  `CityId` int(15) DEFAULT NULL,
  `TimeStamp` char(30) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>

the problem is that the table does not have a primary key,

Eventhough no primary key, I assume that it is still faulty.

Just to confirm that with PRIMARY KEY the problem does not occur.
So problem only applicable if no PRIMARY KEY specified.

Thank you for the feedback.

Verified as described.

Test case for MTR:

--source include/have_binlog_format_mixed_or_row.inc
--source include/have_ndb.inc
--source include/ndb_master-slave.inc

CREATE TABLE `Towns` (
  `CityNumber` int(15) DEFAULT NULL,
  `CityId` int(15) DEFAULT NULL,
  `TimeStamp` char(30) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 ;

--disable_query_log
let $i=4000;
while ($i)
{
--eval insert into Towns values($i, $i, '$i')
dec $i;
}
--enable_query_log

delete from Towns;

select count(*) from Towns;

sleep 3;
connection slave;
select count(*) from Towns;
--vertical_results
show slave status;