Description:
We do have 2 sites on each site ndbd cluster, with 2mysqld and 2 ndbd.
Between sites we do have circle replication.
For one application we store a table which contains information about nr of customer, with timestamps, but there is no PK, as it's rather a general log table.
We noticed, that when doing delete on this table with limit bigger than 430 replication breaks with error:
2023-12-21T05:10:02.786214Z 7 [ERROR] [MY-010584] [Repl]
Slave SQL for channel '': Could not execute Delete_rows event on table schema.table_name;
Got error 261 'DML count in transaction exceeds config parameter MaxDMLOperationsPerTransaction/MaxNoOfConcurrentOp' from NDB, Error_code: 1296;
Got error 261 'DML count in transaction exceeds config parameter MaxDMLOperationsPerTransaction/MaxNoOfConcurrentOp' from NDB, Error_code: 1296;
Got error 4350 'Transaction already aborted' from NDB, Error_code: 1296; Can't find record in 'table_name',
Error_code: 1032; Can't find record in 'table_name', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.001252, end_log_pos 404554, Error_code: MY-001032
parameters, which defines nr of operation are quite impressive:
MaxNoOfConcurrentOperations 256000
MaxDMLOperationsPerTransaction (default) 4294967295
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
DataMemory=12G # How much memory to allocate for data storage
IndexMemory=4G
MaxNoOfConcurrentOperations=256000
MaxNoOfOrderedIndexes=1024
MaxNoOfAttributes=50000
RedoBuffer=64M
MaxNoOfConcurrentScans=500
MaxNoOfTables=512
MaxNoOfTriggers=2048
MaxNoOfUniqueHashIndexes=2048
FragmentLogFileSize=256M
max_heap_table_size 16777216
tmp_table_size 16777216
but testing also with:
max_heap_table_size 1677721600
tmp_table_size 1677721600
with the same result.
Changing
MaxDMLOperationsPerTransaction = 256000
as documentation says, that it should not be bigger that MaxNoOfConcurrentOperations, changed that replication was breaking over 450 deleted entries.
We fixed the issue with adding primary key to problematic table.
I found 1 very old case for mysql 5.0 (if I'm wright) with information that no PK can cause such issue. But now even if there is no PK mysql creates automaticcaly hidden PK, so we expected that PK will not be a problem here.
As we haven't found such information in documentation, the question is:
is it proper behaviour, that replication stops with provided error, when no PK is being set by user on table, and we delete few houndreds of entries from a table?
Table definition:
| table_call_stats | CREATE TABLE `table_call_stats` (
`customer_ref` bigint NOT NULL,
`san_ref` bigint NOT NULL,
`destination_ref` bigint DEFAULT NULL,
`hunting_group_ref` bigint DEFAULT NULL,
`call_timestamp` timestamp NOT NULL,
`total_duration` int DEFAULT '0',
`conversation_duration` int DEFAULT '0',
`call_busy` tinyint NOT NULL DEFAULT '0',
`call_no_answer` tinyint NOT NULL DEFAULT '0',
`call_ringing` tinyint NOT NULL DEFAULT '0',
`queue` tinyint NOT NULL DEFAULT '0',
`queue_time` int DEFAULT '0',
KEY `idx_table_call_stats_customer_san` (`customer_ref`,`san_ref`),
KEY `idx_table_call_stats_customer_san_destination` (`customer_ref`,`san_ref`,`destination_ref`),
KEY `idx_table_call_stats_customer_san_hunting_group` (`customer_ref`,`san_ref`,`hunting_group_ref`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
How to repeat:
describer in description.
