StarQuest Technical Documents

SQDR Plus: Tips for MySQL Data Replication

Last Update: 16 November 2018
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL033

Abstract

This technical document contains tips and troubleshooting hints when using SQDR Plus for incremental data replication 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

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.0.x configured for strong password encryption

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

  • Use MySQL Connector/ODBC 8.0.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';

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.


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.