ConvertStringFromMySQL Filter

The ConvertStringFromMySQLFilter is designed to be used in replicators that are used in conjunction either with existing native MySQL to MySQL replication deployments, or clustering deployments where the replication has been configured to use native MySQL byte storage for strings. These are incompatible with heterogeneous deployments as the string is stored internally and in the THL in a format that is useful only within similarly configured replicators.

Conversion can be selected to happen for all valid columns (VARCHAR or CHAR column types only), or for selected columns within specific tables and schemas. All conversions are made with the relevant character set for the table and THL event.

note

Conversion will not occur on incompatible columns. For example, conversion will not be applied to INT columns. This is the case even if the column has been explicitly set to convert the column.

Pre-configured filter name	convertstringfrommysql
Classname	com.continuent.tungsten.replicator.ConvertStringFromMySQLFilter
Property prefix	Not defined
Stage compatibility	any
tpm Option compatibility
Data compatibility	Row events only

Parameters

Parameter	Type	Default	Description
definitionsFile	string	support/filters-config/convertstringfrommysql.json	JSON file containing the definition of which events and which tables to skip

Configuration of the filter is made using the generic JSON file, which supports both default options to happen for all tables not otherwise explicitly specified. The default JSON file converts all valid (VARCHAR or CHAR) column types only:

{
  "__default": {
      "*" : "true",
  },
  "SCHEMA" : {
      "TABLE" : {
          "COLUMN" : "true",
      },
  }
}

warning

For column specific selection to work, the column names must be included within the THL. The colnames filter must have been enabled either before this filter, or on the extractor where the data was originally extracted.

The default section handles the default response when an explicit schema or table name does not appear. Further sections are then organised by schema, table and column name. Where the setting is true, conversion will take place. A false disables conversion.

To enable conversion on a single column DESCRIPTION within the SALES.INVOICE schema/table while disabling conversion on all other columns:

{
  "__default": {
      "*" : "false",
  },
  "SALES" : {
      "INVOICE" : {
          "DESCRIPTION" : "true",
      },
  }
}

To convert all compatible columns in all tables within a schema:

{
  "__default": {
      "*" : "false",
  },
  "SALES" : {
      "*" : {
          "*" : "true",
      },
  }
}

A primary use case for this filter is for Cluster-Extractor replication from a cluster to a datawarehouse. For more details, please see "Replicating Data Out of a Cluster".

Source Cluster Example

For Cluster-Extractor replication to a datawarehouse, the source cluster nodes must use ROW-based MySQL binary logging, and also must have two extractor filters enabled, colnames and pkey.

For example, on every cluster node the lines below would be added to the /etc/tungsten/tungsten.ini> file in the service stanza, then tpm update would be executed:

svc-extractor-filters=colnames,pkey
property=replicator.filter.pkey.addColumnsToDeletes=true
property=replicator.filter.pkey.addPkeyToInserts=true

For staging deployments, prepend two hyphens to each line and include on the command line.

Target Cluster-Extractor Example

On the replication Applier node, copy the convertstringfrommysql.json filter configuration sample file into the /opt/continuent/share directory then edit it to suit:

shell> cp /opt/continuent/tungsten/tungsten-replicator/support/filters-config/convertstringfrommysql.json /opt/continuent/share/
shell> vi /opt/continuent/share/convertstringfrommysql.json

Once the convertstringfrommysql JSON configuration file has been edited, update the /etc/tungsten/tungsten.ini file to add and configure the convertstringfrommysql filter.

For example, configure a service named omega on host6 to read from the cluster nodes defined by cluster-alias alpha.

[alpha]
topology=cluster-alias
master=host1
members=host1,host2,host3
thl-port=2112

[omega]
topology=cluster-slave
relay=host6
relay-source=alpha
repl-svc-remote-filters=convertstringfrommysql
property=replicator.filter.convertstringfrommysql.definitionsFile=/opt/replicator/share/convertstringfrommysql.json