Communications Link Failure from SELECT Queries

Error Summary

• This error is seen when the Change Data Capture (CDC) SELECT queries take a long time to complete at the Source even for a reduced query batch size.

Potential Causes

• A slow network connection or high CPU/memory usage in the Source system.

• No index or a combined index is created on the Auto-Incrementing Column and the Update Timestamp Column in the Source table.

  In the absence of an index, the database has to scan the entire table to find the required data. An index on the necessary column reduces the scan time. In the case of a combined index, the order of columns in the index may affect the time taken by the query to complete if the column that returns the most number of rows is not placed first in the combined index.

  Therefore, the absence of an index or an incorrectly created index causes the queries to timeout if the Source table has a large number of records.

Suggested Actions

• Contact your Source system administrator to diagnose issues related to a slow network connection or high CPU/memory usage.

• Create an individual index on the Auto-Incrementing Column and the Update Timestamp Column used by the CDC SELECT queries.

  Perform the following steps to create an index:

  Note: Replace the placeholder values in the commands with your own. For example, <table_name> with shipping_order_lifecycle.

  1. Log in to your MySQL server instance as root, and connect to your database. For example, mysql -u root -p <database_name>.

  2. Enter the following command to create an index:

     CREATE INDEX <index_name> ON <table_name> (<column_name_1>);
     

     Examples:

     The following command creates an individual index on the update_dt column in the shipping_order_lifecycle table, where the index name is update_dt_idx:

     CREATE INDEX update_dt_idx ON shipping_order_lifecycle (update_dt);
     

     The following command creates a combined index on the id and update_dt columns in the shipping_order_lifecycle table, where the index name is id_update_dt_idx:

     CREATE INDEX id_update_dt_idx ON shipping_order_lifecycle (id, update_dt);
     

See Also

• CREATE INDEX Statement

• Multi-Column Indexes

• Change Data Capture

Revision History