Why it is important that transaction do not commit on failed master in a MySQL HA cluster

-

In this article we will look into a very important concept of distributed computing and that is we need to prevent happening of transaction on failed master in a HA cluster of MySQL, being other nodes has elected a new master (earlier slave) due to Network partitioning.

Here we will assume the setup of HA cluster as follows:

Client/s first connect to Service Discovery module to know the current Master, they cache the information for a specific interval. They then talk to the Master to proceed their transaction/s. The transaction are then replicated to connected replicas.

The client who has the Master information will cache it and do not change it until told to do so.

We will tell you, what will happen if we allow transactions on a failed master, using a real world use case, and so you will know why it is important to prevent it.

The setup is as follows: 

  • 3 Clients.
  • 1 Service Discovery Module.
  • 3 MySQL server, 1 Master/Primary and 2 Replicas/Secondary.

Please see the diagram representation below: STATE A


Now assume that there is a network partition between the Master and the 2 Replicas. The replicas with a common consent (quorum)  choose a Master between them and lets say update the information in the Service Discovery module.

But our Client (C1) has still the information of old master and connected to it. Now since transaction are allowed on old master, hence C1 can execute transaction on it and can create inconsistency in HA cluster data state because that transaction never reaches the new HA cluster.

Please see the diagram representation below: STATE B



Let us understand this with a real world example:

A customer Manoj is going to order a phone from an online shopping company. He is attached to master and is our client C1, but now before he can complete the transaction, network partition happen and the cluster comes in state 2. Hence later on it is possible that his order never shipped (because the order fulfillment team is fetching data from new cluster state) nor able to see in the database, but might be possible that Manoj has got a success message and even money cut from the bank.

SOLUTION

  1. Stop application to write on a failed master like by using MySQL semi synchronous replication.
  2. Logging of important Application event in a fail safe manner like Kafka cluster or MongoDB Replica set/Cluster.

I personally believe that you should implement both the methods for better visibility and consistency, if time and project's cost let you do that.

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can be on disk or
Read more