MySQL

Striim supports MySQL versions 5.5 and later.

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

MySQL 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 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 source properties for the initial load application, Striim will return an error that they do not exist.

On-premise MySQL 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 on-premise MySQL, the property name for enabling the binary log, whether it is one or off by default, and how and where you change that setting vary depending on the operating system and your MySQL configuration, so for instructions see the binary log documentation for the version of MySQL you are running.

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) ...

On-premise MariaDB Xpand setup

See Configure MariaDB Xpand as a Replication Master. Set the global variables binlog_format to row and sql_log_bin to true.

Amazon Aurora for MySQL setup

See How do I enable binary logging for my Amazon Aurora MySQL cluster?.

Amazon RDS for MySQL setup

Create a new parameter group for the database (see Creating a DB Parameter Group).
Edit the parameter group, change binlog_format to row and binlog_row_image to full, and save the parameter group (see Modifying Parameters in a DB Parameter Group).
Reboot the database instance (see Rebooting a DB Instance).
In a database client, enter the following command to set the binlog retention period to one week:
```
call mysql.rds_set_configuration('binlog retention hours', 168);
```

Azure Database for MySQL setup

You must create a read replica to enable binary logging. See Read replicas in Azure Database for MySQL.

MySQL Reader properties

Before using one of these readers, the tasks described in MySQL setup must be completed.MySQL / MariaDB setup

When this reader is deployed to a Forwarding Agent, you must install the appropriate JDBC driver as described in Installing third-party drivers in the Forwarding Agent.

Striim provides templates for creating applications that read from MySQL and write to various targets. See Creating an 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.
CDDL Action	enum	Process	see Handling schema evolution.
CDDL Capture	Boolean	False	see Handling schema evolution.
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). Appending `?zeroDateTimeBehavior=convertToNull` to the connection URL will convert "zero" DATE and DATETIME values) to nulls (see MySQLReader data type support and correspondence).
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)
Replace Invalid Date	String		MySQL Reader and MariaDB Xpand Reader only: if the database contains "zero" dates (`0000-00-00 00:00:00` or `0000-00-00`), specify a replacement date in the format `YYYY-MMM-dd HH:mm:ss` (see Joda-Time > Pattern-based formatting).
Send Before Image	Boolean	True	set to False to omit `before` data from output
Start Position	String		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. If your environment has multiple binlog files, instead specify the name of the file and the offset for the start position, for example, `FileName:clustrix-bin.000001;offset:720`. When the application is recovered after a system failure, it will automatically resume from the point where it left off. See also Switching from initial load to continuous replication.
Start Timestamp	String	null	MySQL Reader and MariaDB Xpand Reader only: see: 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. See also Switching from initial load to continuous replication.
Tables	String		The table(s) for which to return change data in the format `<database>.<table>`. Names are case-sensitive. 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. The `%` wildcard is allowed only at the end of the string. For example, `mydb.prefix%` is valid, but `mydb.%suffix` is not. 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 MariaDB setup

MySQL Reader WAEvent fields

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

metadata:

OperationName: BEGIN, INSERT, UPDATE, DELETE, COMMIT, STOP
When schema evolution is enabled, OperationName for DDL events will be Alter, AlterColumns, Create, or Drop. This metadata is reserved for internal use by Striim and subject to change, so should not be used in CQs, open processors, or custom Java functions.
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	long
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. Appending `?zeroDateTimeBehavior=convertToNull` to the connection URL will convert "zero" values (`0000-00-00`) to nulls (see Configuration Properties for Connector/J).
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. Appending `?zeroDateTimeBehavior=convertToNull` to the connection URL will convert "zero" values (`0000-00-00 00:00:00`) to nulls (see Configuration Properties for Connector/J).
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 MySQL Reader 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

In this section:

Striim Cloud 4.1.2 documentation