{"id":2477,"date":"2018-08-13T06:15:05","date_gmt":"2018-08-13T06:15:05","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2477"},"modified":"2024-01-27T17:28:56","modified_gmt":"2024-01-27T17:28:56","slug":"galera-cluster-to-aws-aurora-migration-ha_err_found_dupp_key","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/galera-cluster-to-aws-aurora-migration-ha_err_found_dupp_key","title":{"rendered":"Galera cluster to AWS Aurora migration & HA_ERR_FOUND_DUPP_KEY"},"content":{"rendered":"\n

In this post we will see a case study of a Galera Cluster migration to AWS Aurora and quick solution to the replication issue.<\/p>\n\n\n\n

A friend received an error in a Master-Master replication as follows:<\/p>\n\n\n\n

Could not execute Write_rows event on table _database._table; Duplicate entry '65eJ8RmzASppBuQD2Iz73AAy8gPKIEmP-2018-08-03 08:30:03' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin-changelog.000010, end_log_pos 1107814\n<\/pre>\n\n\n\n
We talked about it and I immediately started blabbering cons of writing on both masters, how to handle, roles of apps and so on. I was pointing to him regarding the error duplicate key about primary key.<\/p>\n\n\n\n
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY<\/h2>\n\n\n\n
He intervened and revealed, it is Galera Cluster replicated to Aurora and he is not writing on Aurora.<\/p>\n\n\n\n
He was actually migrating his Galera Cluster to Aurora and master-master was to support his rollback plans. Consider following diagram for simplicity of understanding:<\/p>\n\n\n\n
\"galera
                                                          Galera to AWS migration – sample architecture<\/figcaption><\/figure>\n\n\n\n\n\n\n\n
Okay, so we had replication errors on glr-3 node. The next check we made sure that Aurora instance is not being written. We further looked into binary log to confirm source or server-id that’s being replicated.<\/p>\n\n\n\n
Download Aurora binary log and review:<\/p>\n\n\n\n
$] mysqlbinlog --read-from-remote-server --host=aurora.cluster-xxxx.us-east-1.rds.amazonaws.com --port=3306 --user mysql_user --password mysql_pass --result-file=\/tmp\/mysql-bin-changelog.000010\n\n$] less mysql-bin-changelog.000010\n...\n#180803 8:03:41 server id 1<\/strong> end_log_pos 2279 CRC32 0x3e912bce Xid = 42555\nCOMMIT\/*!*\/;\n# at 2279\n#180803 8:03:41 server id 1<\/strong> end_log_pos 2347 CRC32 0xd193b55a Query thread_id=7214612 exec_time=722 error_code=0\n...\n#180803 8:03:54 server id 2<\/strong> end_log_pos 10455 CRC32 0xa4dca5a2 Query thread_id=986300 exec_time=709 error_code=0\nSET TIMESTAMP=1533283434\/*!*\/;\n<\/pre>\n\n\n\n
\"Galera
                                                                          Galera to Aurora – replication error<\/figcaption><\/figure>\n\n\n\n
That revealed the second server-id which gave another pointer: all the nodes of Galera Cluster are being written. Of-Course then, this is bound to happen when mysql finds different server-id than master, it is going to write the changes eventually resulting into duplicate key errors.<\/p>\n\n\n\n
Asynchronous replication in MySQL depends on server-id to determine the source of the query. This is particularly important to avoid endless loops in master-master or circular replication.<\/p>\n\n\n\n
Now in this scenario, it was found that all the nodes Galera Cluster had unique server-ids and all were being written. Thus binary log had entries from all the nodes while only one node was participating in master-master replication.<\/p>\n\n\n\n
Since the changes for server-ids 2 and 3 were already applied by Galera-replication, we are getting duplicate key errors.<\/p>\n\n\n\n
So, here I thought of two solutions:
1. Write on only one node (and replicate from there).
2. Use same server-ids on all the Galera nodes.<\/p>\n\n\n\n
3rd option could have been disabling log-slave-updates but that\u2019s not possible in Aurora cluster (or actually any server which has chained replication further). Also, consider that this is a problem because we’re setting master-master replication; had it been master-slave, we’d have not noticed this.<\/p>\n\n\n\n
\"log-slave-updates\"
log-slave-updates<\/figcaption><\/figure>\n\n\n\n
1. Write on only one galera node and replicate from there.<\/strong>
This can be configured on maxscale or any load balancer in order to configure & route the write traffic, though that option was denied.<\/p>\n\n\n\n
2. Use same server-ids on all Galera nodes.<\/strong>
Galera is certification based replication. On a cluster, before transaction commit, a certification test is performed on each node for determining whether or not the write-set (changes) can be applied to each of the node. If certification fails, write-set is dropped and cluster rollbacks the transaction.
This all happens based on a GTID comprises of UUID (dataset-id, cluster-id) and sequence number.<\/p>\n\n\n\n
Sample: kdar8582-r271-00d2-8582-1jdgnv05e21d:8518<\/code><\/pre>\n\n\n\n
where kdar8582-r271-00d2-8582-1jdgnv05e21d is dataset-id while 8518 is sequence number.<\/p>\n\n\n\n
You can read more about Galera Replication on documentation page<\/a>. My idea to cover some of the basics here was to show that server-id<\/span> has no role to play in Galera Replication. We can surely configure same server-id on each Galera Cluster node. That will nullify the multiple server-ids and hence tackle the duplicate key errors in multi-master replication with Aurora Cluster.<\/p>\n\n\n\n
My friend went that way to complete the migration successfully. Consider sharing in comments if you have more information on “using same server-id in Galera and its effects”.<\/p>\n\n\n\n
Hope this helps.<\/p>\n","protected":false},"excerpt":{"rendered":"In this post we will see a case study of a Galera Cluster migration to AWS Aurora and quick solution to the replication issue. A friend received an error in…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[868,864,8,377,865],"tags":[464,460,465,381,461,463,466,462,100],"class_list":{"0":"post-2477","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-aws-rds","7":"category-mysql-galera-cluster","8":"category-mysql","9":"category-mysql-articles","10":"category-percona-xtradb-cluster","11":"tag-aurora-migration","12":"tag-could-not-execute-write_rows-event","13":"tag-error_code-1062","14":"tag-galera","15":"tag-galera-cluster-to-aws","16":"tag-galera-replication","17":"tag-ha_err_found_dupp_key","18":"tag-migration","19":"tag-replication"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2477","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2477"}],"version-history":[{"count":5,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2477\/revisions"}],"predecessor-version":[{"id":2835,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2477\/revisions\/2835"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2477"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2477"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2477"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}