StarQuest: keeping data in motion

 

Stelo Technical Documents

SQDR Plus: Tips for MySQL Data Replication

Last Update: 14 March 2024
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL033

Abstract

This technical document contains tips and troubleshooting hints when using SQDR for data replication to or from a MySQL or MySQL-compatible (e.g. MariaDB, Amazon Web Services Aurora) database.

The following technical documents provide additional information:

Contents:

Managing MySQL Log Files

SQDR Plus extracts incremental changes from the binary log files. If a log file is deleted prematurely (before it has been examined by SQDR Plus), all incremental subscriptions will be flagged as requiring new baselines.

On the other hand, if binary logs are not managed and deleted when no longer needed, they can continue to grow, resulting in increased disk space usage.

To determine which log file is currently in use by SQDR Plus, look at the Summary panel in SQDR Manager. Then issue the statement:

PURGE BINARY LOGS TO 'log-file-name';

For example, in this screenshot, SQDR Control Center shows that the log in use is srv122-bin.000007:

 

To delete the older logs, issue the statement:
PURGE BINARY LOGS TO 'srv122-bin.000007';

This will purge older log files up to (but not including) file 000007.

Also, when configuing binary logging (by editing the my.cfg or my.ini file), you can specify an expiration value:

expire-logs-days=2

binlog retention hours parameter (AWS Aurora or AWS RDS for MySQL)

When using AWS Aurora or AWS RDS for MySQL, you may need to increase the binlog retention hours parameter to prevent premature deletion of the binlog files. See the AWS documentations for mysql.rds_set_configuration for details.

example:

call mysql.rds_set_configuration('binlog retention hours', 24);

Error: Could not find first log file name

Symptom:

The following error appears in the Diagostics log for a MySQL agent:

MySqlLogReader.onCommunicationFailure:
com.github.shyiko.mysql.binlog.network.ServerException:
Could not find first log file name in binary log index file at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:949)

Solution:

This may be the result of logs being purged before SQDR Plus has scanned them.

Recover the current log position (move the log read position to "now") by doing the following:

  • Stop the Agent: select the Agent and choose Database/Stop Agent.
  • Select Database/Recovery/Set Startup Mode and select Warm (restored).
  • Start the Agent.

Baselines may need to be run.

Subscription wizard failure with MySQL Connector/ODBC 8.0.28 & later

Symptom: Creating a new subscription from a MySQL source fails with the error Unknown ODBC error or Invalid cursor state after the Criteria panel - i.e. when the subscription wizard is obtaining column information.

Solution: Use MySQL Connector/ODBC 8.0.27 or the MariaDB ODBC driver; do not use later versions of the MySQL Connector/ODBC driver.

Source does not appear as enabled for incremental replication

Symptom:
When creating a source using Data Replicator Manager, the checkbox for incremental replication on the Advanced panel is greyed out.

Solution:
Verify that this step documented in the Quick Start Guide was performed:

After creating the agent and before creating an SQDR source in Data Replicator Manager, use either the agent user (SQDR) or another user with sufficient authority (e.g. root) to grant read access to SQDR.SQ_PROPERTIES to the SQDR client user. Otherwise, SQDR will not know that MySQL is enabled for incremental replication.

GRANT SELECT ON SQDR.SQ_PROPERTIES TO SQDRUSER

Close Window Processing never completes

Symptom: In Data Replicator Manager, the status icon for the subscription is green, but contains a black dot. This indicates that SQDR is still in the process of synchronizing the baseline.

Solution: Examine the MySQL server configuration (e.g. my.ini) and look for the setting binlog-do-db, which is used to restrict binary logging to specified databases. If this property is used, the configuration must explictly allow binary logging for both the source database and the agent database (SQDR). The binlog-do-db property can be used multiple times e.g.

binlog-do-db=MYDB
binlog-do-db=SQDR

Also search for the property binlog-ignore-db, which excludes specific databases from binary logging.

Auto_increment columns (MySQL to Db2 for i)

SQDR does not create the destination table with the auto_increment property, since the purpose of SQDR is to replicate data from the source database, where typically a column has already been assigned the appropriate value. However, this scenario may be handled by a future version of SQDR.

Index name clashes (MySQL to Db2 for i)

Issue: Index names in MySQL are qualified with the base table name, but an index name on Db2 for i is not. As a result of this syntax "mismatch" between MySQL and Db2 for i, a valid set of index names in MySQL, such as ap_vendor.vendor and po_vendor.vendor, will fail to produce the second index "vendor" on Db2 for i.

Also, index names on Db2 for i are part of the same namespace within a schema, so a schema cannot contain both a table named "abc" and an index named "abc".

Solution: Insure your index names are uniquely named. For example, you can name them as a concatenation of the base table name and the index name. This approach to naming will also work well within MySQL, since you will end up with index names such as table1.table1_indexA, table2.table2_indexA, etc.

Column Default values (MySQL to Db2 for i)

Issue: SQDR does not create the destination table with default column values, since the purpose of SQDR is to replicate data from a source database, where typically such columns have already been populated.

Solution: The following MySQL script will interrogate the information_schema on MySQL and produce an SQL script containing "ALTER TABLE ALTER COLUMN" statements that can then be run on the Db2 for i system, using the RUN SQL feature of iSeries Navigator. The script also changes the defaults for "date" from '0000-00-00' to '1900-01-01'.)

SELECT CONCAT('ALTER TABLE ', upper(TABLE_SCHEMA) , '.' , upper(TABLE_NAME),
' ALTER COLUMN ', upper(COLUMN_NAME), ' SET DEFAULT ',
case DATA_TYPE
when 'datetime' then concat('''', COLUMN_DEFAULT,'''')
when 'date' then concat('''', IF( COLUMN_DEFAULT='0000-00-00', '1900-01-01', COLUMN_DEFAULT),'''')
when 'time' then concat('''', COLUMN_DEFAULT,'''')
when 'enum' then concat('''', COLUMN_DEFAULT,'''')
when 'char' then concat('''', COLUMN_DEFAULT,'''')
when 'varchar' then concat('''', COLUMN_DEFAULT,'''')
else COLUMN_DEFAULT
end
,';') FROM COLUMNS
where table_schema='myschema' and column_default is not null;

Unable to create function due to lack of SUPER privilege

Symptom: the following warning appears in the Diagnostics when starting the agent

WARNING CaptureAgent.checkTimezoneUdf:CREATE FUNCTION "SQDR"."SQTZ" ()
...
java.sql.SQLException: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Solution: Temporarily set the database parameter log_bin_trust_function_creator to 1/true/on (it is not necessary to restart the database instance) and restart the agent. After the agent successfully creates the function, you can revert the setting to its original value.

See the Tier 1 MySQL host computer requirements section of the Quick Start Guide to Using SQDR Plus v4 with MySQL (Part 1) for details.

Working with MySQL 8.x configured for strong password encryption

When using a MySQL 8.x server configured for strong password encryption (my.cnf/ini contains default_authentication_plugin=caching_sha2_password (default on new installations of MySQL 8.x) or users are configured for Authentication type caching_sha2_password), the following considerations apply:

  • Use MySQL Connector/ODBC 8.x when configuring source and destination in Data Replicator Manager.
  • Use SQDR Plus 5.01 or later.
  • Configure the Agent user to use legacy password authentication. This requirement will be removed in a future version of SQDR Plus.

ALTER USER SQDR IDENTIFIED WITH mysql_native_password BY 'mypassword';

Failure to configure the Agent user to use legacy password authentication will result in the error unsupported authentication method: sha256_password.

Fetching large amounts of data may cause memory issues

If fetching large amounts of data from a MySQL source results in memory issues for the SQDR service, add NO_CACHE=1 to the connection string or modify the ODBC data source and select Don't cache results of forward-only cursors on the Cursors/Results panel.


Increasing the max_allowed_packet parameter (MySQL destination)

Symptom: The following error occurred when replicating a table containing a large (4.5mb) video to a MySQL destination:

Insert failed at destination. [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.17]MySQL server has gone away

Solution: Increase the value of the max_allowed_packet parameter (default 4mb) on the MySQL destination. This statement will increase it to 100Mb:

set global max_allowed_packet=104857600;

Content-dependent error (MySQL destination)

Symptom:

When replicating a table to a MySQL/MariaDB destination, certain content can result in the error:

Attempt to recover subscription failed(Cleanup.) Unexpected Exception encountered during Replication

This error is related to source tables containing VARCHAR (e.g. VARCHAR(3500)) or CLOB (mapped to VARCHAR(3500) columns. You can verify this by excluding the column from the replication and confirming that the replication succeeds.

Solution:

  • On the Destination panel, select Insert using ODBC rather than Use Native-loader function. This will result in longer load times, especially over a WAN connection.

OR

  • Use the SQDR Service Properties utility and change the value of MaxMySQLParms from its default of 2000 to a lower value. Because this issue is content-dependent, there is no recommended value. There will be a slight loss of performance

Use SQDR Plus 5.22 or later for AWS Aurora

Symptom

Replication from AWS Aurora stops with the following error:

MySqlLogReader.onEventDeserializationFailure:
java.io.IOException: Unknown event type 100

Solution

Update to SQDR Plus 5.22 or later.

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.