API Portal duplicate keys on database replication

Document ID : KB000117847
Last Modified Date : 19/10/2018
Show Technical Document Details
Introduction:

On the slave server, we see this in the mysqld.log

"180928 16:12:02 [ERROR] Slave SQL: Error 'Duplicate entry '1107937' for key 'PRIMARY'' on query. Default database: 'lrs'. Query: 'insert into AuditLog (repositoryId, username, action, path, tag, date, branchStatus, comment, label, systemLog) values ('//api:xxxx/CMS/Repository/lrs-dev-master', 'InternalRequest', 'Update', '/xml_content/layer7/APIs/api-availability.xml', 'HEAD', '2018-09-28 15:50:03', '\0', '', '', '')', Error_code: 1062

180928 16:12:02 [Warning] Slave: Duplicate entry '1107937' for key 'PRIMARY' Error_code: 1062"

 
Background:

 
Environment:
API Portal 3.5 
Instructions:


1) The Cause of the Duplicate Key problem

The auditlog table has a primary key (id) as an auto-increment field 
 
mysql> desc auditlog; 
+--------------+---------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+--------------+---------------+------+-----+---------+----------------+ 
| id | int(11) | NO | PRI | NULL | auto_increment | 
| repositoryId | varchar(255) | YES | | NULL | | 
| username | varchar(50) | YES | | NULL | | 
...


The values are set from a variable and the number is automatically incremented.
 
mysql> show variables like 'auto_%'; 
+--------------------------+-------+ 
| Variable_name | Value | 
+--------------------------+-------+ 
| auto_increment_increment | 1 | 
| auto_increment_offset | 1 | 
| autocommit | ON | 
| automatic_sp_privileges | ON | 
+--------------------------+-------+ 

so there can be a conflict of data replicated from another system, where it clashes with "id" values already inserted in the auditlog table, or there can be an issue with a new locally inserted entry clashing with already in the "slave" auditlog table from being replicated from the master mysql database. 
 
 
2) A quick way to ignore mysql duplicate keys on replication : 

Howto Ignoring duplicate keys on mysql replication 

Added 1162 to the list to suppress remove duplicate error. Duplicate entry '540968' for key 'PRIMARY'' on query 

vi /etc/my.cnf 
slave-skip-errors=126,1053,1105,1129,1158,1159,1160,1161, 1062 
 
Now this means that audit records from the master will be dropped if they clash, it's not ideal since we do end up loosing data.
 

3) Better way - increment fields set to use disjoint offset values and not clash.
We can set the auto_increment to give different set of values on the master and the slave.  That way when the auditlog data is replicated from the master to the slave the value if the id field does not overlap. 

If we give each MySQL a unique values for the MySQL server options auto_increment_increment and auto_increment_offset when using replication, then there will not be any clash on values when data is replicated.

MySQL master-to-master replication. 

Example: 

Node1: 
set @@auto_increment_increment 10 
set @@auto_increment_offset 1 

This will give starting point: 
auto_increment_increment 10 
auto_increment_offset 1 


And will create ids 1,11,21,31 

Node2; 
set @@auto_increment_increment 10 
set @@auto_increment_offset 2 

This will give starting point: 
auto_increment_increment 10 
auto_increment_offset 2 


And will create ids 2,12,22,32 

For more information. see :   
Additional Information:
1.  The setup for replication in API Portal 3.5 : does not describe setting up mysql replication : 
 https://docops.ca.com/ca-api-developer-portal/3-5/en/set-up-the-api-portal/configure-the-api-portal-replication

The slave portals, all point to the master database. 

The replication is controlling the deployment of the CMS files, and it is controlled out of the primary database, and replicates the content to the slave portals. 

So there is no setup of a slave mysql database described here, although for failover purposes a slave database with is a good idea, to enable failover to it (as the new primary database) if something does happen to the master database server. 

The recommended setup is for the slave API Portal to point to the master database.

Recovery of API Portal replication (using the one master db) is described here : 
https://comm.support.ca.com/kb/slave-information-is-duplicated-in-shared-folders-of-replication-manager/kb000009306 
 

2.  Loss of some auditlog data if slave API Portal pointing to slave mysql database.  

However if you do have a slave API Portal is pointing to slave mysql db, as a warm failover option, then the slave is clearly writing some auditlog entries.   If the replication is only one way, master -> slave, then these audit records (and any other activity on the slave) will only be reflected in the slave mysql db and are not replicated back to the master db. 

That may not be important, if it is only used as a failover, and when that occurs, the slave db data is manually copied back to the master mysql database.  Otherwise to avoid loss of data a master <-> master setup may be needed so that all audit records on slave and master API Portal.