Overview

Over the past few days we have been working with a number of customers on the best way to handle Triggers within their MySQL environment when combined with Tungsten Replicator. We looked at situations where Tungsten Replicator was either part of a Tungsten Clustering installation or a standalone replication pipeline.

This blog dives head first into the minefield of Triggers and Replication.

Summary and Recommendations

The conclusion was that there is no easy one-answer-fits-all solution – It really depends on the complexity of your environment and the amount of flexibility you have in being able to adjust. Our top level summary and recommendations are as follows:

If using Tungsten Clustering and you need to use Triggers:

• Switch to …

Discover the new Continuent Tungsten Replicator (AMI) – the most advanced & flexible replication engine for MySQL, MariaDB & Percona Server, including Amazon RDS MySQL and Amazon Aurora

We’re excited to announce the availability on the Amazon Marketplace of a new version of the Tungsten Replicator (AMI).

Tungsten Replicator (AMI) is a replication engine that provides high-performance and improved replication functionality over the native MySQL replication solution and provides the ability to apply real-time MySQL data feeds into a range of analytics and big data databases.

Tungsten Replicator (AMI) …

The Replicator is a critical piece of the Tungsten Clustering solution for MySQL / MariaDB, as well as its own stand-alone data replication product. Automatic recovery is a feature that enables the Replicator to go back online in the event of a transient failure. In this blog we discuss how to enable Automatic Recovery. For more information about Auto-Recovery, please click here to visit the online documentation page.

The Question Recently, a customer asked us:

We see that the replicators receive a transaction which has a deadlock error in it:

pendingError : Event application failed: seqno=82880882 fragno=0 message=java.sql.SQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

If one performs a service online, it comes back online without issue and continues …

The Question Recently, a customer asked us:

What is the meaning of this error message found in trepsvc.log?

2019/05/14 01:48:04.973 | mysql02.prod.example.com | [east - binlog-to-q-0] INFO pipeline.SingleThreadStageTask Performing rollback of possible partial transaction: seqno=(unavailable)

Simple Overview The Skinny

This message is an indication that we are dropping any uncommitted or incomplete data read from the MySQL binary logs due to a pending error.

The Answer Safety First

This error is often seen before another error and is an indication that we are rolling back anything uncommitted, for safety. On a master this is normally very little and would likely be internal transactions in the trep_commit_seqno table, for example.

As you may know with the replicator we always extract complete transactions, and so this particular message is …

The Question Recently, a customer asked us:

Why would Tungsten Replicator be slow to apply to MySQL?

The Answer Performance Tuning 101

When you run trepctl status and see:
appliedLatency : 7332.394
like this on a slave, it is almost always due to the inability for the target database to keep up with the applier.

This means that we often need to look first to the database layer for the solution.

Here are some of the things to think about when dealing with this issue:

Architecture and Environment
√ Are you on bare metal?
√ Using the cloud?
√ Dev or Prod?
√ Network speed and latency?
√ Distance the data needs to travel?
√ Network round trip times? Is the …

Overview The Skinny

From time to time we are asked how to check whether or not there are data discrepancies between Master/Slave nodes within a MySQL (or MariaDB) cluster that’s managed with Tungsten Clustering. This is always a challenging task, not least because we hope and believe that our replication mechanism would avoid such occurrences, that said there can be factors outside of our control that can appear to “corrupt” data – such as inadvertent execution of DML against a slave using a root level user account.

Tungsten Replicator, the core replication component in our Tungsten Clustering solution for MySQL (& MariaDB), is just that, a replicator – it takes transactions from the binary logs and replicates them around. The replicator isn’t a data synchronisation tool in that respect, the …

When Tungsten Replicator extracts data, the information that has been extracted is written down into the Tungsten History Log, or THL. These files are in a specific format and they are used to store all of the extracted information in a format that can easily be used to recreate and generate data in a target.

Each transaction from the source is written into the THL as an event, so within a single THL file there will be one or more events stored. For each event, we record information about the overall transaction, as well as then information about the transaction itself. That event can contain one or more statements, or rows, or both. Because we don’t want to get an ever increasing single file, the replicator will also divide up the THL into multiple files to tmake the data easier to manage.

We’ll get down into the details soon, until then, let’s start by looking at the basics of the THL, files and sequence numbers and how to …

The Tungsten Replicator is an extraordinarily powerful and flexible tool capable of moving vast volumes of data from source to target.

In this blog post we will discuss one specific aspect of the THL (Transaction History Log) – the METADATA unsafe_for_block_commit flag.

What do you mean, Unsafe?

In a recent customer support case, we were asked the meaning of the unsafe_for_block_commit flag. For example, list the event information for sequence number 3481394254:

[tungsten@tr2-mysql01 (sandbox) ~]$ thl list -seqno 3481394254 | more
SEQ# = 3481394254 / FRAG# = 0 (last frag)
- TIME = 2018-09-16 06:52:47.0
- EPOCH# = 3480364140
- EVENTID = mysql-bin.001068:0000000294739578;622252
- SOURCEID = tr2-mysql01.sandbox.yourdomain.com
- METADATA = [mysql_server_id=1;unsafe_for_block_commit;dbms_type=mysql;tz_aware=true;service=brm;shard=shard_1736]
- TYPE = …