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:

Summary of the problem : Invalid binlog related database configuration
Potential root cause : The following global variables does not contain
 required configuration or it cannot be found:
 log_bin,server_id,binlog_format,binlog_row_image.
Suggested Actions: 1.Add --log_bin to the mysqld command line or add
 log_bin to your my.cnf file..
2.Add --server-id=n where n is a positive number to the mysqld command
 lineor add server-id=n to your my.cnf file..
3. Add --binlog-format=ROW to the mysqld command line or add 
 binlog-format=ROW to your my.cnf file..
4.Add --binlog_row_image=FULL to the mysqld command line or add
 binlog_row_image=FULL to your my.cnf file..
Component Name: MySQLSource.
Component Type: SOURCE.
Cause: Problem with configuration of MySQL
binlog_format should be ROW.
binlog_row_image should be FULL.
The server_id must be specified.
log_bin is not enabled.

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.

Google Cloud SQL for MySQL setup

You must create a read replica to enable binary logging. See Cloud SQL> Documentation> MySQL> Guides > Create read replicas.

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		Specify `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). When connecting through an SSH tunnel (see Using an SSH tunnel to connect to a source or target), specify the IP address of the tunnel. To use an Azure private endpoint to connect to Azure Database for MySQL, see Specifying Azure private endpoints in sources and targets. Starting in Striim 4.2.0.4, you may use the `zeroDateTimeBehavior` option (see MySQL Connector/J 8.1 Developer Guide / Connector/J Reference / Configuration Properties / Datetime types processing / zeroDateTimeBehavior). with `zeroDateTimeBehavior=CONVERT_TO_NULL`, 0000-00-00 00:00:00 timestamps will be converted to nulls. With `zeroDateTimeBehavior=EXCEPTION`, when the input includes a 0000-00-00 00:00:00 timestamp, the application will halt. `zeroDateTimeBehavior=ROUND` is not supported.
Excluded Tables	String		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 (separated by semicolons) and wildcards may be used exactly 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 source has DATE or DATETIME columns containing "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 (blank), reading starts with transactions that are committed after the Striim application is started. To start from an earlier point, specify the name of the file and the offset for the start position, for example, `FileName:clustrix-bin.000001;offset:720`. If your environment has multiple binlog files, specify the name of the one to use, for example, `FileName:clustrix-bin.000001`. If you are using schema evolution (see Handling schema evolution, set a Start Position only if you are sure that there have been no DDL changes after that point.Handling schema evolution 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. If you are using schema evolution (see Handling schema evolution, set a Start Timestamp only if you are sure that there have been no DDL changes after that point.Handling schema evolution 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:

BinlogFile: the binlog file from which MySQL Reader read the operation
BinlogPosition: the operation's position in the binlog file
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: {"BinlogFile":"ON.000004","TableName":"mydb.POSAUTHORIZATIONS",
    "TxnID":"1:000004:3559:1685955321000","OperationName":"INSERT","TimeStamp":1685955321000,
    "OPERATION_TS":1685955321000,"BinlogPosition":3727}
  userdata: null
  before: null
  dataPresenceBitMap: "fw8="
  beforePresenceBitMap: "AAA="
  typeUUID: {"uuidstring":"01ee037e-a8e6-6c61-a752-c2cd07892059"}

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: {"PK_UPDATE":"false","BinlogFile":"ON.000004",
    "TableName":"mydb.POSAUTHORIZATIONS","TxnID":"1:000004:3990:1685955341000",
    "OperationName":"UPDATE","TimeStamp":1685955341000,
    "OPERATION_TS":1685955341000,"BinlogPosition":4167}
  userdata: null
  before: ["COMPANY 1","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,
    0,"20130309113025","0916","USD","2.200",5150279519809946,41363,"Quicksand"]
  dataPresenceBitMap: "fw8="
  beforePresenceBitMap: "fw8="
  typeUUID: {"uuidstring":"01ee037e-a8e6-6c61-a752-c2cd07892059"}

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: {"BinlogFile":"ON.000004","TableName":"mydb.POSAUTHORIZATIONS",
    "TxnID":"1:000004:4550:1685955350000","OperationName":"DELETE",
    "TimeStamp":1685955350000,"OPERATION_TS":1685955350000,"BinlogPosition":4718}
  userdata: null
  before: null
  dataPresenceBitMap: "fw8="
  beforePresenceBitMap: "AAA="
  typeUUID: {"uuidstring":"01ee037e-a8e6-6c61-a752-c2cd07892059"}

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.

MySQL Reader data type support and correspondence

MySQL type	TQL type	comments
BIGINT	long
BIGINT UNSIGNED	long
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. Dates prior to 1583 may be offset by several days due to the discontinuity in the switch from the Julian to Gregorian calendars (see What Calendar Is Used By MySQL?).
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. Dates prior to 1583 may be offset by several days due to the discontinuity in the switch from the Julian to Gregorian calendars (see What Calendar Is Used By MySQL?).
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
JSON	JSONNode
LONGBLOB	string
LONGTEXT	string
MEDIUMBLOB	string
MEDIUMINT	int
MEDIUMINT UNSIGNED	int
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
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. 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).
TINYBLOB	string
TINYINT	byte
TINYINT UNSIGNED	byte
TINYTEXT	string
VARBINARY	string
VARCHAR	string
YEAR	int	Known issue DEV-38452: for MySQL 8 or later, when a table also includes both a YEAR and an integer type, data may not be read and an onEventDeserializationFailure error will be written to the system log. In Striim 4.2.0.3 and earlier, reading will continue; in 4.2.0.4 and later, the application will terminate.

Runtime considerations when using MySQL Reader

If when connecting to MySQL 5.7 or earlier you get errors including javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate), append useSSL=false to the connection URL, for example:

ConnectionURL:'mysql://192.168.1.10:3306?useSSL=false'

The default value of MySQL's wait_timeout is 28800 seconds (eight hours). Reducing this to 300 seconds (five minutes) can resolve a variety of errors such as "connect timed out" or "unexpected end of stream." See wait_timeout for more information.

ON UPDATE and ON DELETE commands with CASCADE, SET NULL, or SET DEFAULT clauses are not captured by MySQL's binlog, so are not read by MySQL Reader.