MySQL Master/Master(Active-Active) Replication failed

[SAJAN GONE]

Hi,


We have an application running on two data centers with MAster/Master replication setup on 5.7(Row based). We recently had a rep failure on both sides as we there were Inserts on both the data centers at the same time. At both the sides same value of primary key was inserted which resulted in replication failure.

Below is the structure of the table.

CREATE TABLE `storage` (
`key` varchar(255) NOT NULL,
`value` blob NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`key`),
KEY `KEY_UNIQUE` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here is the content of the table on DC1 at the moment of rep failure:

+----------------------------------------------+--------------------------------------+---------------------+
| key | value | created_at |
+----------------------------------------------+--------------------------------------+---------------------+
| 1:rgtv2:2ce69d04-863a-42b1-8284-50d3a864aa8b | 1d53140f-5074-479e-8094-a0e5bb1b60c6 | 2016-11-18 08:05:34 |
| 1:rgv2:4696784be7a99abeb4be7a13f0cd3b4940afa4e6:179.208.143.127 | 1d53140f-5074-479e-8094-a0e5bb1b60c6 | 2016-11-18 08:05:34 |
| 1:tgv2:AWzpqfHSVB+4Rkg3wJME+hOx | 2ce69d04-863a-42b1-8284-50d3a864aa8b:a3d96f33a9304560d9050fef94d9525790f35112 | 2016-11-18 08:05:34 |
| 1:tgv2:MDhjYzY2Y2ItYmRhNy00ZmRhLThmMTYtMzk0MjhhOTIxMTk1OjE0Nzk0NzQzMjg0NTg | 2ce69d04-863a-42b1-8284-50d3a864aa8b:a3d96f33a9304560d9050fef94d9525790f35112 | 2016-11-18 08:05:34 |
+----------------------------------------------------------------------------+-------------------------------------------------------------------------------+——————————+

At DC2:

KET
+----------------------------------------------+--------------------------------------+---------------------+
| key | value | created_at |
+----------------------------------------------+--------------------------------------+---------------------+
| 1:rgtv2:2ce69d04-863a-42b1-8284-50d3a864aa8b | bb0ea19d-dc09-4afc-863c-4c0db95fdf1f | 2016-11-18 08:05:34 |
| 1:rgv2:4696784be7a99abeb4be7a13f0cd3b4940afa4e6:179.208.143.127 | bb0ea19d-dc09-4afc-863c-4c0db95fdf1f | 2016-11-18 08:05:34 |
| 1:tgv2:AWzpqfHSVB+4Rkg3wJME+hOx | 2ce69d04-863a-42b1-8284-50d3a864aa8b:a3d96f33a9304560d9050fef94d9525790f35112 | 2016-11-18 08:05:34 |
| 1:tgv2:MDhjYzY2Y2ItYmRhNy00ZmRhLThmMTYtMzk0MjhhOTIxMTk1OjE0Nzk0NzQzMjg0NTg | 2ce69d04-863a-42b1-8284-50d3a864aa8b:a3d96f33a9304560d9050fef94d9525790f35112 | 2016-11-18 08:05:34 |
+----------------------------------------------------------------------------+-------------------------------------------------------------------------------+——————————+

After investigating through the binary logs, ON DC1 for all the entries we were able to see the server ID as 1(MySQL Server ID on DC1). However on DC2's logs for the bottom two entries the server ID was registered as 1 which means they have been replicated to DC2 from DC1 which makes sense as we are seeing exactly same data for all the columns for those two entries. However for top two entries on DC2 server ID is 11(Dc2's Server ID) which means applications on Dc2 itself have inserted those records. As the primary key was same on both the DC's we ran into replication failure.

Is there any way I can get the HostName/IP address of the app servers which have inserted those records into that table from any of the MySQL logs or monitoring tables. I was not able to find such kind of information related to threads/hosts/IPaddress from the binary logs. Also we have disabled our general log unfortunately because of the amount of activity with this application.


Please let me know if there is any way I can find such information.


Thanks,
Sajan Gone.