Search docs.molo17.com

    MySQL CDC logo Target MySQL

    Target MySQL

    Prerequisites

    To have Gluesync working on your MySQL instance you will need to have:

    • Valid user credentials with permission to read, and write to the target tables.

    Setup via Web UI

    • Hostname / IP Address: DNS record or IP Address of your server;

    • Port: Optional, defaults to 3306;

    • Database name: Name of your database;

    • Username: Username with read/write access to the tables;

    • Password: Password belonging to the given username;

    • Max connections count: Maximum number of connections the pool can instantiate;

    • Enable TLS: Optional, defaults to false;

    • Certificate path: Optional, path to the certificate file.

    Setup under AWS RDS / DBaaS

    AWS RDS, like any other DBaaS, allows database connections only via SSL. Flag Use SSL to true during the agent’s setup phase.

    Specific configuration

    • ALLOW INVALID DATES: Optional, defaults to false;

    • ZERO DATE BEHAVIOR: Optional, defaults to CONVERT_TO_NULL;

    Setup via Rest APIs

    Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

    Connect the agent

    curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
    --header 'Content-Type: application/json' \
    --header 'Authorization: ••••••' \
    --data '{
            "hostCredentials": {
            "connectionName": "myAgentNickName",
            "host": "host-address",
            "port": 3306,
            "databaseName": "db_name",
            "username": "",
            "password": "",
            "maxConnectionsCount": 100,
            "enableTls": true,
            "certificatePath": "/myPath/cert.pem"
          }
    }'

    Supporting Bulk loads

    Edit the MySQL config file, usually located at /etc/my.cnf or /etc/mysql/my.cnf then under the [mysqld] section place or edit the following parameter:

    [mysqld]
    
    ##### INSERT THIS IN THE [mysqld] SECTION #####
    local_infile=1 # For bulk operations support

    Updates with bulk operations during CDC

    When Use bulk operations during CDC (useBulkOperationsDuringCDC) is on for an entity, the agent does not apply an update as an UPDATE statement. It loads the change into a staging table, deletes the existing row from the target table and inserts the new version of it. With the option off, updates are applied as UPDATE statements.

    For the target table this has the following effects:

    • UPDATE triggers on the target table do not fire, while DELETE and INSERT triggers do;

    • Foreign keys that reference the table with ON DELETE CASCADE delete their child rows on every update, and ON DELETE SET NULL clears the child columns. A foreign key with RESTRICT or NO ACTION makes the write fail while child rows exist;

    • Tools that read the binary log of the target, such as another CDC pipeline, see a DELETE and an INSERT event instead of an UPDATE;

    • Columns that Gluesync does not write, such as a column with a default value, are set again as for a new row.

    Troubleshooting

    Here is the list of common errors.

    ALLOW_INVALID_DATES flag

    This flag is used to allow the storage of invalid dates within MySQL. Values stored in this way can be converted to NULL or rounded to the nearest closest value which is 0001-01-01 by playing with the zeroDateTimeBehavior parameter that can be set at connection string level.

    Supported values are:

    • EXCEPTION (the MySQL JDBC’s driver default), which throws an SQLException with an SQLState of S1009.

    • CONVERT_TO_NULL (the Gluesync’s default), which returns NULL instead of the date.

    • ROUND, which rounds the date to the nearest closest value which is 0001-01-01.

    For any further information, please refer to the MySQL documentation.

    This values can be set at connection string level or at the agent’s custome settings level (suggested).