This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of  3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.

We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.

In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.

Going forward will look into the detailed analysis made to tackle the issue of OOM.

DB Environment:-

• Service – Group Replication Cluster
• Cluster Nodes – 3
• GR mode – Single Primary …

Thanks Oystein Grovlen for your comment .

Yes Sir, I understand and completely agree with your point .

LikeLike

In reply to hercules7sakthi.

Justin’s point is that you chose to explicitly commit the transaction after the failure. You had the choice to to repeat the statement or roll back to maintain atomicity.

LikeLiked by 1 person

In reply to Justin Swanhart.

Hi Justin,

Thanks for your comment . Our applications has the proper error handling mechanism to handle the failures .

I had the issue, when I tested the variable innodb_rollback_on_timeout with different threshold ( ON and OFF ). The complete flow was documented in this blog . And, there is no deadlock happens , it is a LOCK WAIT TIMEOUT .

Even the document says “InnoDB rolls back only the last statement on a transaction timeout by default.”

LikeLike

Your application should retry the update or roll back. InnoDB is not losing atomicity. You commit a transaction with changes and that os just a bug in your app. Note that InnoDB always rolls back whole transactions on deadlock. Your application has to handle failed statements.

LikeLiked by 1 person

The blog title seems something crazy ?

Yes, by default your InnoDB transactions don’t have the Atomicity . But, you can control the atomicity of your InnoDB transaction with the variable innodb_rollback_on_timeout. We had a weird data inconsistency issue within the transaction . In this blog , I am going to explain “How it will happen?” and “How it can be fixed ?” .

Whats is Atomicity ?

From Wiki ,

Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.

What is …

MySQL Connector in 8.0.19 includes the support of DNS SRV.

The following URL can be a good starting point to understand what this feature is about.


https://www.slideshare.net/Grypyrg/mysql-connectors-8019-dns-srv

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-dns-srv.html

https://learn.hashicorp.com/consul/security-networking/forwarding


MySQL Router in MySQL InnoDB Cluster setup offers the transparent access to the backend database servers.   However, the application has to know the IP Address of the Router in order to get …

                Had an interesting requirement from one of our client to have the two MySQL partitions ( partition_full / partition_half ) for store the Names based on the user input.

Requirement :

• The table have two columns first_name and last_name. With the user input of both columns, it needs to be automatically compute the data for another column full_name . And, the status needs be consider as “FULL” .
• If the column last_name don’t have the input from the user, then the first_name data needs to be compute as the full_name . And, the status needs be considered as “HALF” .
• Need the separate partitions for both status HALF and FULL

We can achieve …

Sometimes, you may experience “ERROR 1034: Incorrect key file” while running the ALTER TABLE or CREATE INDEX command:

mysql> alter table ontime add key(FlightDate);
ERROR 1034 (HY000): Incorrect key file for table 'ontime'; try to repair it

As the error message mentions key file, it is reasonable to assume we’re dealing with the MyISAM storage engine (the legacy storage engine which used to have such a thing), but no, we can clearly see this table is InnoDB!

When the error message in MySQL is confusing or otherwise unhelpful, it is a good idea to check the MySQL error log:

2019-02-24T02:02:26.100600Z 9 [Warning] [MY-012637] [InnoDB] 1048576 bytes should have been written. Only 696320 bytes written. Retrying for the remaining bytes.
2019-02-24T02:02:26.100884Z 9 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2019-02-24T02:02:26.100894Z 9 [ERROR] [MY-012639] [InnoDB] Write to file (merge) …

In this blog I will go through a number of popular applications that use
NDB Cluster 8.0 and also how these applications have developed over the
years.

There is a presentation at slideshare.net accompanying this blog.

The first major NDB development project was to build a prototype of a
number portability application together with a swedish telecom provider.
The aim of this prototype was to show how one could build advanced
telecom applications and manage it through standardised interfaces.
The challenge here was that the telecom applications have stringent
requirement, both on uptime and on latency. If the database access
took too long time the telecom applications would suffer from
abandoned calls and other problems. Obviously the uptime of the …