Striim

Welcome to the Striim Help Center and Community Site

3.10.1 MySQL / MariaDB

Follow

Striim supports MySQL versions 5.5 and later (using MySQLReader) and compatible versions of MariaDB and MaridaDB Galera Cluster (using MariaDBReader).

Striim provides templates for creating applications that read from MySQL and write to various targets. See Creating a new application using a template for details.

MySQL / MariaDB setup

To use MySQLReader or MariaDBReader, an administrator with the necessary privileges must create a user for use by the adapter and assign it the necessary privileges:

CREATE USER 'striim' IDENTIFIED BY '******';
GRANT REPLICATION SLAVE ON *.* TO 'striim';
GRANT REPLICATION CLIENT ON *.* TO 'striim';
GRANT SELECT ON *.* TO 'striim';
  • The MySQL 8 caching_sha2_password authentication plugin is not supported in this release. The mysql_native_password plugin is required.

  • The REPLICATION privileges must be granted on *.*. This is a limitation of MySQL.

  • You may use any other valid name in place of striim. Note that by default MySQL does not allow remote logins by root.

  • Replace ****** with a secure password.

  • You may narrow the SELECT statement to allow access only to those tables needed by your application. In that case, if other tables are specified in the MySQLReader properties, Striim will return an error that they do not exist.

MySQL binary log setup

MySQLReader reads from the MySQL binary log. If your MySQL server is using replication, the binary log is enabled, otherwise it may be disabled.

For MySQL, the property name for enabling the binary log, its default setting, and how and where you change that setting vary depending on the operating system and your MySQL configuration, so see the documentation for the version of MySQL you are running for instructions.

If the binary log is not enabled, Striim's attempts to read it will fail with errors such as the following:

2016-04-25 19:05:40,377 @ -WARN hz._hzInstance_1_striim351_0423.cached.thread-2 
com.webaction.runtime.Server.startSources (Server.java:2477) Failure in Starting 
Sources.
java.lang.Exception: Problem with the configuration of MySQL
Row logging must be specified.
Binary logging is not enabled.
The server ID must be specified.
Add --binlog-format=ROW to the mysqld command line or add binlog-format=ROW to your 
my.cnf file
Add --bin-log to the mysqld command line or add bin-log to your my.cnf file
Add --server-id=n where n is a positive number to the mysqld command line or add 
server-id=n to your my.cnf file
        at com.webaction.proc.MySQLReader_1_0.checkMySQLConfig(MySQLReader_1_0.java:605) ...
MariaDB binary log setup

See Activating the Binary Log.

MariaDB Galera Cluster setup

The following properties must be set on each server in the cluster:

  • binlog_format=ROW

  • log_bin=ON

  • log_slave_updates=ON

  • Server_id: see server_id

  • wsrep_gtid_mode=ON

MySQLReader and MariaDBReader properties

These two readers are identical except for the Cluster Support, Start Position, and Start Timestamp properties.

Before using MySQLReader, the tasks described in MySQL / MariaDB setup and in Installing the MySQL JDBC driver must be completed.

Before using MariaDBReader, the tasks described in MySQL / MariaDB setup and Installing the MariaDB JDBC driver must be completed.

Striim provides templates for creating applications that read from MySQL and write to various targets. See Creating a new application using a template for details.

The adapter properties are:

property

type

default value

notes

Bidirectional Marker Table

String

When performing bidirectional replication, the fully qualified name of the marker table (see Bidirectional replication). This setting is case-sensitive.

Cluster Support

String

MariaDBReader only: set to Galera when reading from a MariaDB Galera Cluster.

Compression

Boolean

False

Set to True when the output of a MySQLReader source is the input of a Cassandra Writer target.

When replicating data from one MySQL instance to another, when a table contains a column of type FLOAT, updates and deletes may fail with messages in the log including "Could not find appropriate handler for SqlType." Setting Compression to True may resolve this issue. If the table's primary key is of type FLOAT, to resolve the issue you may need to change the primary key column type in MySQL.

Connection Retry Policy

String

retryInterval=30, maxRetries=3

With the default setting, if a connection attempt is unsuccessful, the adapter will try again in 30 seconds (retryInterval. If the second attempt is unsuccessful, in 30 seconds it will try a third time (maxRetries). If that is unsuccessful, the adapter will fail and log an exception. Negative values are not supported.

Connection URL

String

When reading from MySQL, mysql:// followed by the MySQL server's IP address or network name, optionally a colon and the port number (if not specified, port 3306 is used), and optionally a slash followed by the database name.

When reading from MariaDB, jdbc:mariadb:// followed by the MariaDB server's IP address or network name, optionally a colon and the port number (if not specified, port 3306 is used), and optionally a slash followed by the database name.

When reading from a MariaDB Galera Cluster, specify the IP address and port for each server in the cluster, separated by commas: jdbc:mariadb://<IP address>:<port>,<IP address>:<port>,..., and optionally a slash followed by the database name.

Database

String

the name of the database containing the tables (may be omitted if specified in Tables or ConnectionURL)

Excluded Tables

String

Change data for any tables specified here will not be returned. For example, if Tables uses a wildcard, data from any tables specified here will be omitted. Multiple table names and wildcards may be used as for Tables.

Filter Transaction Boundaries

Boolean

True

With the default value of True, begin and commit transactions are filtered out. Set to False to include begin and commit transactions.

Password

encrypted password

the password specified for the username (see Encrypted passwords)

Send Before Image

Boolean

True

set to False to omit before data from output

Start Position

String

MariaDBReader only: With the default value of null, reading starts with transactions that are committed after the Striim application is started.

To start from an earlier point, specify a Global Transaction ID (GTID) in the format GTID: #-#-#, replacing #-#-# with the last GTID before the point where you want to start. Reading will start with the next valid GTID.

When the application is recovered after a system failure, it will automatically resume from the point where it left off.

Start Timestamp

String

null

MySQLReader only: With the default value of null, only new (based on current system time) transactions are read. If a timestamp is specified, transactions that began after that time are also read. The format is YYYY-MMM-DD HH:MM:SS. For example, to start at 5:00 pm on February 1, 2020, specify 2020-FEB-01 17:00:00.

When the application is recovered after a system failure, it will automatically resume from the point where it left off.

Tables

String

The table(s) for which to return change data. Names are case-sensitive. If a value is not specified for Database you must use fully qualified table names. You may specify multiple tables as a list separated by semicolons or with the following wildcards in the table name only (not in the database name):

  • %: any series of characters

  • _: any single character

For example, my.% would include all tables in the my database.

If any specified tables are missing Striim will issue a warning. If none of the specified tables exists, start will fail with a "found no tables" error.

Username

String

the login name for the user created as described in MySQL / MariaDB setup

MySQLReader WAEvent fields

The output data type for MySQLReader is WAEvent. The fields are:

metadata:

  • OperationName: BEGIN, INSERT, UPDATE, DELETE, COMMIT, STOP

  • PK_UPDATE: for UPDATE only, true if the primary key value was changed, otherwise false

  • TxnID: unique transaction ID generated by MySQLReader (the internal MySQL transaction ID is not written to the MySQL binary log until the COMMIT operation)

  • TimeStamp: timestamp from the MySQL binary log

  • TableName: fully qualified name of the table (for INSERT, UPDATE, and DELETE only).

To retrieve the values for these fields, use the META function. See Parsing the fields of WAEvent for CDC readers.

data: an array of fields, numbered from 0, containing:

  • for a BEGIN operation, 0 is the current database name and 1 is BEGIN

  • for an INSERT or DELETE, the values that were inserted or deleted

  • for an UPDATE, the values after the operation was completed

  • for a COMMIT, 0 is the ID number of the transaction

  • for a DDL CREATE or DDL DROP, 0 is the current database name and 1 is the CREATE or DROP statement

To retrieve the values for these fields, use SELECT ... (DATA[]). See Parsing the fields of WAEvent for CDC readers.

before (for UPDATE operations only): the same format as data, but containing the values as they were prior to the UPDATE operation

dataPresenceBitMap, beforePresenceBitMap, and typeUUID are reserved and should be ignored.

MySQLReader simple application

The following application will write change data for the specified table to SysOut. Replace wauser and ****** with the user name and password for the MySQL account you created for use by MySQLReader (see MySQL setup) and mydb and mytable with the names of the database and table(s) to be read.

CREATE APPLICATION MySQLTest;

CREATE SOURCE MySQLCDCIn USING MySQLReader (
  Username:'striim',
  Password:'******',
  ConnectionURL:'mysql://192.168.1.10:3306',
  Database:'mydb',
  Tables:'mytable'
) 
OUTPUT TO MySQLCDCStream;

CREATE TARGET MySQLCDCOut
USING SysOut(name:MySQLCDC)
INPUT FROM MySQLCDCStream;

END APPLICATION MySQLTest;

MySQLReader example output

MySQLReader's output type is WAEvent. See WAEvent contents for change data for general information.

The following are examples of WAEvents emitted by MySQLReader for various operation types. They all use the following table:

CREATE TABLE POSAUTHORIZATIONS (BUSINESS_NAME varchar(30),
  MERCHANT_ID varchar(100),
  PRIMARY_ACCOUNT bigint,
  POS bigint,
  CODE varchar(20),
  EXP char(4),
  CURRENCY_CODE char(3),
  AUTH_AMOUNT decimal(10,3),
  TERMINAL_ID bigint,
  ZIP integer,
  CITY varchar(20));
INSERT

If you performed the following INSERT on the table:

INSERT INTO POSAUTHORIZATIONS VALUES(
  'COMPANY 1',
  'D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu',
  6705362103919221351,
  0,
  '20130309113025',
  '0916',
  'USD',
  2.20,
  5150279519809946,
  41363,
  'Quicksand');

The WAEvent for that INSERT would be:

data: ["COMPANY 1","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD",2.200,5150279519809946,41363,"Quicksand"]
metadata: {"TxnID":"1:000004:613:1457120135000","TimeStamp":1457120135000,
"TableName":"mydb.posauthorizations","OperationName":"INSERT"}
before: null
typeUUID: {"uuidstring":"01e5e240-3046-0e31-9fdb-685b3587069e"}
UPDATE

If you performed the following UPDATE on the table:

UPDATE POSAUTHORIZATIONS SET BUSINESS_NAME = 'COMPANY 5A' where pos=0;

The WAEvent for that UPDATE for the row created by the INSERT above would be:

data: ["COMPANY 5A","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD",2.200,5150279519809946,41363,"Quicksand"]
metadata: {"TxnID":"1:000004:1023:1457120135000","TimeStamp":1457120135000,
"TableName":"mydb.posauthorizations","OperationName":"UPDATE"}
before: ["COMPANY 1","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD",2.200,5150279519809946,41363,"Quicksand"]
typeUUID: {"uuidstring":"01e5e240-3046-0e31-9fdb-685b3587069e"}
DELETE

If you performed the following DELETE on the table:

DELETE from POSAUTHORIZATIONS where pos=0;

The WAEvent for that DELETE for the row affected by the INSERT above would be:

data: ["COMPANY 5A","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD",2.200,5150279519809946,41363,"Quicksand"]
metadata: {"TxnID":"1:000004:1553:1457120135000","TimeStamp":1457120135000,
"TableName":"mydb.posauthorizations","OperationName":"DELETE"}
before: null
typeUUID: {"uuidstring":"01e5e240-3046-0e31-9fdb-685b3587069e"}

Note that the contents of data and before are reversed from what you might expect for a DELETE operation. This simplifies programming since you can get data for INSERT, UPDATE, and DELETE operations using only the data field.

MySQLReader data type support and correspondence

MySQL type

TQL type

comments

BIGINT

long

BIGINT UNSIGNED

long

Values greater than the midpoint of the range (9223372036854775807) are represented as negative values (the high bit is on, interpreting the value as negative).

BINARY

string

BIT

unsupported

BLOB

string

CHAR

string

DATE

org.joda.time.LocalDate

If the MySQL and Striim hosts are not in the same time zone, the value will be converted to Striim's time zone.

DATETIME

org.joda.time.DateTime

Fractional seconds, if used, are dropped. If the MySQL and Striim hosts are not in the same time zone, the value will be converted to Striim's time zone.

DECIMAL

string

DECIMAL UNSIGNED

string

DOUBLE

double

ENUM

int

The value is the integer that is MySQL's internal representation (enumeration literals are assigned numbers in the order the literals were written in the declaration).

FLOAT

float

If replicating from one MySQL database to another, see the notes for the Compression property in MySQLReader and MariaDBReader properties.

geometry types

unsupported

INT

int

INT UNSIGNED

int

Values greater than the midpoint of the range (4294967295) are represented as negative values (the high bit is on, interpreting the value as negative).

JSON

JSONNode

LONGBLOB

string

LONGTEXT

string

MEDIUMBLOB

string

MEDIUMINT

int

MEDIUMINT UNSIGNED

int

Values greater than the midpoint of the range (8388607) are represented as negative values (the high bit is on, interpreting the value as negative).

MEDIUMTEXT

string

NUMERIC

string

NUMERIC UNSIGNED

string

SET

long

The value is the integer that is MySQL's internal representation (the integer represented by the bit string in which the nth bit is set, if the nth member of the SET's literals is present in the set).

SMALLINT

short

SMALLINT UNSIGNED

short

Values greater than the midpoint of the range (32767) are represented as negative values (the high bit is on, interpreting the value as negative).

spatial types

unsupported

TEXT

string

TIME

org.joda.time.LocalTime

Fractional seconds, if used, are dropped. If the MySQL and Striim hosts are not in the same time zone, the value will be converted to Striim's time zone.

TIMESTAMP

org.joda.time.DateTime

Fractional seconds, if used, are dropped. If the MySQL and Striim hosts are not in the same time zone, the value will be converted to Striim's time zone.

TINYBLOB

string

TINYINT

byte

TINYINT UNSIGNED

byte

Values greater than the midpoint of the range (127) are represented as negative values (the high bit is on, interpreting the value as negative).

TINYTEXT

string

VARBINARY

string

VARCHAR

string

YEAR

int

3.10.1
Was this article helpful?
0 out of 0 found this helpful

Comments