When Stitch replicates data from a MySQL-backed database, it will check for the required user permissions and database server settings. If permissions or server settings aren’t properly defined, you may receive an error during the Extraction phase of the replication process. These errors will surface in the integration’s Extraction Logs.


Applicable integrations

The errors and troubleshooting steps in this article are applicable to the following database integrations:


Extraction error messages and troubleshooting

Below are the errors you might see if Stitch has trouble replicating data from a MySQL-backed database, as well as how to resolve them.

Meaning

Binary logging is not enabled for the MySQL server.


Troubleshooting

Enable binary logging on your MySQL server. This is done by defining the log-bin parameter.

The steps for enabling binary logging will vary depending on the type of MySQL database you’re using. Click the links below to view instructions for that database.

Note: Amazon MySQL RDS and Amazon Aurora RDS databases should have binary logging enabled by default. If you receive this error for either of these databases, verify that the rest of the required server settings are defined correctly.

Meaning

The server ID of the instance Stitch is connected to is not unique. When using Log-based Replication, the ID of the server Stitch connects to must be unique. This is because MySQL doesn’t allow replication to occur using the same server ID simultaneously across multiple connections.


Troubleshooting

If you’re using Log-based Replication and any of the following are true, you’ll need to define a unique server ID in the integration’s Settings page in Stitch:

  1. You connected one or more read replicas
  2. You connected multiple databases that are all on the same server
  3. You added a new MySQL-based Stitch integration, and the database is on the same server as other previously-connected databases

To do this, refer to the instructions for the database you’re connecting:

Meaning

The likely cause of this error is setting server parameters - in this case, related to enabling binlog replication - on the master server without rebooting the master.

Rebooting the master instance is required for server parameter changes to take effect.


Troubleshooting

Reboot the master instance to ensure the server parameters for enabling binlog are applied.

Meaning

The binlog_format server parameter is not set to ROW, which Stitch requires for MySQL Log-based Replication.


Troubleshooting

Change the binlog_format paramter to ROW. This ensures that binary replication is “row-based”, meaning that events affecting individual rows are captured.

The steps for changing this parameter will vary depending on the type of MySQL database you’re using. Click the links below to view instructions for that database.

Meaning

The binlog_row_image parameter is not set to FULL, which Stitch requires for MySQL Log-based Replication.


Troubleshooting

Changing the binlog_row_image parameter to FULL. This ensures that when a row is updated in the source database, the entire row is captured versus only the updated values.

The steps for changing this parameter will vary depending on the type of MySQL database you’re using. Click the links below to view instructions for that database.

Meaning

The data replicated for a table does not match the expected schema. This occurs when the structure of a table changes in the source and Stitch is unable to verify its schema..

This can be caused by:

  • Altering the table’s schema by removing a column, adding a new column to the middle of the table, or changing the order of columns
  • Changing the data type of a column
  • The Stitch database user not having access to all the columns in the table

This occurs due to how Log-based Incremental Replication works, and how log messages are structured in database binary files. Refer to the Log-based Incremental Replication documentation for more info and examples.


Troubleshooting

To resolve the error, you’ll need to perform a table-level reset. To do this, open the Table Settings page for the table and click the Reset Keys button. This will clear the table’s Replication Key value and queue a full re-replication of the table.

To prevent this error in the future:

  • When adding or removing columns, or changing data types, perform the table-level reset in Stitch immediately after the modification
  • Ensure the Stitch user has access to all the columns in the table. Although Stitch will be limited to the columns you specify while using SELECT during the initial replication job, values for restricted columns will still appear in binary log files.

    Because these values are in the binary log files but not in the initial SELECT replication, Stitch will interpret this as columns being reordered. Ensuring that the Stitch database user has access to all columns at the start can avoid this error.

Meaning

The source object is a database view. Binary logging doesn’t track events that occur in database views, so Stitch is unable to use Log-based Replication for views.


Troubleshooting

Update the view’s Replication Method to either Key-based Incremental or Full Table.

Meaning

The requested binary log file has been removed from the server. When a binary log file is removed before Stitch can read from it, replicaiton will be unable to proceed.

This error typically arises due to insufficient log retention settings, which define how long log files remain on a server before they’re automatically purged.

It can also be caused by binary log files being purged before historical replication completes or critical errors that prevent Stitch from replicating data. When this occurs, it means Stitch was unable to proceed to the binary log file before the retention period passed and the log was purged.


Troubleshooting

To resolve the error, you’ll need to perform an integration-level reset. To do this, open the Integration Settings page and click the Reset Keys button. This will clear the integration’s Replication Keys and queue a full re-replication of the integration.

To prevent this error in the future, verify your server’s log retention settings and update them if needed. Stitch recommends a minimum of 3 days, but 7 days is preferred. This ensures that you have time to resolve any issues that arise before logs age out and are purged.

The steps for changing this parameter will vary depending on the type of MySQL database you’re using. Click the links below to view instructions for that database.

In addition, make sure you resolve any connection or replication issues quickly. This will reduce the likelihood that binary log files will age out and cause this error.

Meaning

When Stitch replicates data using Log-based Replication, it will “bookmark” its place by saving a position in the log file. When the next replication job begins, Stitch will use this value to identify the place in the log where it should begin reading data.

This error means that the log position Stitch has is “ahead” of the server. To summarize, the log position Stitch is attempting to use is greater than the log position that the MySQL database has available.

Typically this will be an issue only if the server uses a very small max_binlog_size value.


Troubleshooting

Perform an integration-level reset. To do this, open the Integration Settings page and click the Reset Keys button. This will clear the integration’s Replication Keys and queue a full re-replication of the integration.

We also recommend checking the value of the max_binlog_size server parameter and increasing it if it’s too small. By default, this is set to MySQL’s maximum of 1 GB (1073741824 bytes).

Meaning

The binlog_row_image parameter doesn’t exist in the version that the source MySQL database is running. This means that binary logging, or binlog replication, is not a feature supported by the current database version.


Troubleshooting

The source MySQL database must be running MySQL version 5.6.2 or greater to use binlog replication. If you can upgrade your database version, you may be able to use binlog replication.


Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.