Bug #69938 `Got temporary error 899 'Rowid already allocated' from NDBCLUSTER'
Submitted: 6 Aug 2013 12:25 Modified: 26 Aug 2013 11:44
Reporter: Stelios Doulakis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:7.2.5 OS:Linux (Cent OS 6.4)
Assigned to: Gustaf Thorslund CPU Architecture:Any
Tags: Got temporary error 899, INSERT Failed, ndbcluster

[6 Aug 2013 12:25] Stelios Doulakis
Description:
We get this error when we are trying to insert data into MySQL Cluster DB:

2013-08-02 03:39:24.006 [29246] [1] ERROR: MYSQL: mysql_stmt_execute() failed: `Got temporary error 899 'Rowid already allocated' from NDBCLUSTER'
2013-08-02 03:39:24.006 [29246] [1] ERROR: DB_CDR: MySQL: update failed
2013-08-02 03:39:24.006 [29246] [1] ERROR: DB_CDR: MySQL: SQL was: `INSERT INTO MT (  session_esme,   session_client,   msg_id,   msg_type_name,   msg_timestamp,   msg_smsc_id,   msg_binfo,   msg_meta_data,   state,   state_timestamp,   smpp_sequence_number,   smpp_service_type,   smpp_source_addr_ton,   smpp_source_addr_npi,   smpp_source_add,   smpp_dest_addr_ton,   smpp_dest_addr_npi,   smpp_destination_addr,   smpp_esm_class,   smpp_registered_delivery,   smpp_data_coding,   smpp_sm_length,   smpp_short_message,   smpp_message_payload) VALUES (    ?, ?, ?, ?, ?, ?, ?, ?,   'RECEIVED_FROM_CLIENT',   ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'

This is very critical for us because we lose some rows,
Is it possible to investigate this?

The cluster is 8 node cluster with 2 nodegroups and 4 managment servers,
We have 2 million Inserts per day with pick 2000/second.

How to repeat:
Set up one cluster with 8 nodes and 2 nodegroups and run multiple inserts (2000 per minute).
[19 Aug 2013 10:32] Gustaf Thorslund
Hi Stelios,

This looks like someone is trying to insert a row with a key that's already used. Can you please provide the schema for the table MT?

/Gustaf
[19 Aug 2013 10:55] Stelios Doulakis
Hello Gustaf

The Table structure for table `MT` is:

CREATE TABLE IF NOT EXISTS `MT` (
  `session_esme` char(20) COLLATE latin1_general_ci NOT NULL,
  `session_client` char(20) COLLATE latin1_general_ci NOT NULL,
  `msg_id` char(36) COLLATE latin1_general_ci NOT NULL,
  `msg_foreign_id` char(36) COLLATE latin1_general_ci DEFAULT NULL,
  `msg_type_name` char(26) COLLATE latin1_general_ci NOT NULL,
  `msg_timestamp` int(11) NOT NULL,
  `msg_smsc_id` char(36) COLLATE latin1_general_ci DEFAULT NULL,
  `state` char(40) COLLATE latin1_general_ci NOT NULL,
  `state_timestamp` int(11) DEFAULT NULL,
  `smpp_command_status` int(11) DEFAULT '0',
  `smpp_sequence_number` char(10) COLLATE latin1_general_ci NOT NULL,
  `smpp_service_type` char(6) COLLATE latin1_general_ci DEFAULT NULL,
  `smpp_source_addr_ton` int(11) NOT NULL,
  `smpp_source_addr_npi` int(11) NOT NULL,
  `smpp_source_add` char(21) COLLATE latin1_general_ci DEFAULT NULL,
  `smpp_dest_addr_ton` int(11) NOT NULL,
  `smpp_dest_addr_npi` int(11) NOT NULL,
  `smpp_destination_addr` char(21) COLLATE latin1_general_ci DEFAULT NULL,
  `smpp_esm_class` int(11) NOT NULL,
  `smpp_registered_delivery` int(11) NOT NULL,
  `smpp_data_coding` int(11) NOT NULL,
  `smpp_sm_length` int(11) NOT NULL,
  `smpp_short_message` varchar(650) COLLATE latin1_general_ci DEFAULT NULL,
  `smpp_message_payload` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `msg_meta_data` char(36) COLLATE latin1_general_ci DEFAULT NULL,
  `msg_binfo` char(36) COLLATE latin1_general_ci DEFAULT NULL,
  `cc` int(3) NOT NULL,
  `hlr_state` char(255) COLLATE latin1_general_ci NOT NULL,
  `hlr_timestamp` int(11) NOT NULL,
  `hlr_location` char(20) COLLATE latin1_general_ci DEFAULT NULL,
  `hlr_mcc` char(3) COLLATE latin1_general_ci DEFAULT NULL,
  `hlr_mnc` char(2) COLLATE latin1_general_ci DEFAULT NULL,
  `default_smsc` char(36) COLLATE latin1_general_ci DEFAULT NULL,
  `ported_smsc1` char(36) COLLATE latin1_general_ci DEFAULT NULL,
  `ported_smsc2` char(36) COLLATE latin1_general_ci DEFAULT NULL,
  `updated_billing` tinyint(1) DEFAULT NULL,
  `pb_id` int(11) DEFAULT NULL,
  `web2sms_client` int(11) NOT NULL,
  `w2sms_client` int(11) NOT NULL,
  `w2sms_price` char(5) COLLATE latin1_general_ci NOT NULL,
  `child_price` float DEFAULT NULL,
  PRIMARY KEY (`msg_id`),
  KEY `idx_source_time` (`smpp_source_add`,`msg_timestamp`),
  KEY `idx_smsc_time` (`msg_smsc_id`,`msg_timestamp`),
  KEY `idx_meta_time` (`msg_meta_data`,`msg_timestamp`),
  KEY `smpp_destination_addr` (`smpp_destination_addr`),
  KEY `idx_esme_time` (`session_esme`,`msg_timestamp`),
  KEY `idx_w2sms_time` (`w2sms_client`,`msg_timestamp`),
  KEY `smpp_source_add` (`smpp_source_add`),
  KEY `session_esme` (`session_esme`),
  KEY `msg_meta_data` (`msg_meta_data`),
  KEY `msg_smsc_id` (`msg_smsc_id`),
  KEY `state` (`state`),
  KEY `msg_timestamp` (`msg_timestamp`),
  KEY `idx_service_time` (`smpp_service_type`,`msg_timestamp`),
  KEY `smpp_message_payload` (`smpp_message_payload`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
[19 Aug 2013 12:40] Gustaf Thorslund
Hello Stelios,

PRIMARY KEY (`msg_id`), so it looks like you are trying to insert a row with a msg_id that already exists. Are those message ids generated in some unique way? Maybe you could add a part to your error handling to select the already existing row and output it together with the row you wanted to insert into a log file so you can dig into why it failed.

/Gustaf
[20 Aug 2013 7:25] Stelios Doulakis
Hello Gustaf,
Thank you for your fast response,
I inform you that we have tried to insert 5000 rows into an other table,

Example of INSERT Query (This row doesn't exist in DB and the primary key is auto increment):

INSERT INTO amd_smpp_gw.ported(msisdn,mcc_mnc)
VALUES ( 3333333333, 33333 ) 
ON DUPLICATE KEY UPDATE mcc_mnc =33333,
timestamp = CURRENT_TIMESTAMP;

The Table structure for table `ported`

CREATE TABLE IF NOT EXISTS `ported` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `msisdn` char(20) CHARACTER SET latin1 NOT NULL,
  `mcc_mnc` char(6) CHARACTER SET latin1 NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `msisdn` (`msisdn`),
  KEY `mcc_mnc` (`mcc_mnc`)
) ENGINE=ndbcluster  DEFAULT CHARSET=latin2 AUTO_INCREMENT=172623113 ;

and also got this error:

#1297 - Got temporary error 899 'Rowid already allocated' from NDBCLUSTER

so i think that there is no problem with duplicate primary keys.

Please tell me if you need something else to find a solution.
[26 Aug 2013 11:43] Bernd Ocklin
- Error code 899 is considered a TEMPORARY error, clients should retry (potentially after a small delay) on encountering it (like with all temp errors)
- Error code 899 may be returned on INSERT operations during node recovery.
- Error code 899 may be returned on INSERT operations on DD tables in 6.3
- All other known causes of error 899 are fixed, but may still be present in older versions
- There may be currently unknown causes of error 899.

We fixed a couple of cases for 899 in 7.2.14. This maintenance version should come out any day from now. I'll be closing this bug now. If you still encounter 899 after the update then we are highly interested to hear from you.
[1 Jul 2016 9:43] Vyronas Tsingaras
The issue persists on 7.2.23