Troubleshooting Inbound Replication

Troubleshooting Inbound Replication Errors

Isolate inbound replication errors by retrieving inbound replication-related information.

Using MySQL Shell

Use MySQL Shell or a MySQL client program to retrieve inbound replication-related information.

This task requires the following:

A running replica DB system.
MySQL Shell or a MySQL client program connected to the replica DB system.

Run one or more of the following commands on the replica DB system to retrieve inbound replication-related information:

SHOW REPLICA STATUS \G
Shows status information on essential parameters of the replica threads. See SHOW REPLICA STATUS.
SELECT * FROM performance_schema.replication_connection_status \G
Shows the current status of the I/O thread that handles the replica's connection to the source. See replication_connection_status.
SELECT * FROM performance_schema.replication_applier_status_by_worker \G
Shows details of the transactions handled by applier threads on a replica. See replication_applier_status_by_worker.

Inbound Replication Receiver Error Codes

This table shows some of the common inbound replication receiver error codes.

Table 22-4 Inbound Replication Receiver Error Codes

Error Code	Description
`MY-1045: ER_ACCESS_DENIED_ERROR; Access denied for user '%s'@'%s'`	Source username or password is incorrect. Check your credentials. User does not have permission to access the source. Check the source and ensure the replication user is correctly configured.
`MY-1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation`	You can do either of the following: Specify the same username in `DEFINER` and Applier username. You need the `SET_USER_ID` privilege only when the usernames in `DEFINER` and Applier username are different. See Creating a Replication Channel. Contact Oracle Support to grant the`SET_USER_ID` privilege to the Applier username.
`MY-1236: ER_MASTER_FATAL_ERROR_READING_BINLOG Got fatal error %d from master when reading data from binary log: '%s'`	See Resolving Fatal Error 1236.
`MY-2003: Network connection has been refused`	Hostname and port are incorrect, or the source is not running. Check the hostname and port you defined and confirm the source is running.
`MY-3159: ER_SECURE_TRANSPORT_REQUIRED Connections using insecure transport are prohibited while --require_secure_transport=ON`	The source requires a secure connection. Confirm you selected one of the SSL options when you defined your channel.

For the full list of MySQL error messages, see Server Error Message Reference.

Inbound Replication Applier Error Codes

This table shows some of the common inbound replication applier error codes.

Table 22-5 Inbound Replication Applier Error Codes

Error Code Description

Error Code	Description
`MY-1146: ER_NO_SUCH_TABLE;` `MY-1032: HA_ERR_KEY_NOT_FOUND` `MY-1062: HA_ERR_FOUND_DUPP_KEY` `MY-1064: ERROR IN SQL syntax` `MY-1050: ER_TABLE_EXISTS_ERROR` `MY-1051: ER_BAD_TABLE_ERROR` `MY-1146: ER_NO_SUCH_TABLE` `MY-1007: ER_DB_CREATE_EXISTS` `MY-1054: ER_BAD_FIELD_ERROR`	These errors occur if the data on the replica is no longer synchronised with the source. This can occur if the data on the replica has been edited manually, for example. To correct these errors, you must resynchronise the source and replica, and resume the replication channel.
`MY-1205: ER_LOCK_WAIT_TIMEOUT`	Timeout exceeded. To correct this error, resume the replication channel. See Resuming a Channel.
`MY-1595/ MY-013121: RELAY LOG READ FAILURE`	The relay log is corrupted and cannot be read. To correct this error, reset the replication channel. See Resetting a Channel.

MY-1146: ER_NO_SUCH_TABLE;
MY-1032: HA_ERR_KEY_NOT_FOUND
MY-1062: HA_ERR_FOUND_DUPP_KEY
MY-1064: ERROR IN SQL syntax
MY-1050: ER_TABLE_EXISTS_ERROR
MY-1051: ER_BAD_TABLE_ERROR
MY-1146: ER_NO_SUCH_TABLE
MY-1007: ER_DB_CREATE_EXISTS
MY-1054: ER_BAD_FIELD_ERROR

These errors occur if the data on the replica is no longer synchronised with the source. This can occur if the data on the replica has been edited manually, for example.

To correct these errors, you must resynchronise the source and replica, and resume the replication channel.

MY-1205: ER_LOCK_WAIT_TIMEOUT Timeout exceeded. To correct this error, resume the replication channel. See Resuming a Channel.

MY-1595/ MY-013121: RELAY LOG READ FAILURE The relay log is corrupted and cannot be read. To correct this error, reset the replication channel. See Resetting a Channel.

For the full list of MySQL error messages, see Server Error Message Reference.

Resolving Fatal Error 1236

You get the error when either the GTID set from the logical dump is not applied on the DB system or the binary logs were purged from the source. Binary logs are a mandatory requirement for replication.

Using MySQL Shell

Use MySQL Shell to resolve the Fatal Error 1236 that you get while running inbound replication.

This task assumes the following:

You get the Fatal Error 1236 while running inbound replication:

Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting
      using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs
      containing GTIDs that the slave requires.

In the initial connection handshake, the replica (DB System) sends a GTID set containing the transactions that it has already received, committed, or both. The source responds by sending all transactions recorded in its binary log whose GTID is not included in the GTID set sent by the replica. This exchange ensures that the source only sends the transactions with a GTID that the replica has not already recorded or committed.

Do the following to resolve the issue:

To apply the GTID set during load, use the updateGtidSet: "append" option in the MySQL Shell loadDump() command. See MySQL Shell Dump Loading Utility.
Review and increase the expire log configuration of the source to ensure sufficient time for the data transfer to the replica. See binlog_expire_logs_seconds and expire_logs_days.

Resolving Replica and Source Synchronization Issues

You get the synchronization issues when the replica has fallen far behind the source, and replication is unrecoverable.

Note

Before you attempt to recover from this error, it is recommended to investigate why the error occurred and take corrective action.

Using MySQL Shell

Use MySQL Shell to resolve the replica and source synchronization issues.

This task assumes the following:

Your inbound replication source and replica are no longer synchronized, the replica has fallen far behind the source, and replication is unrecoverable.

Note

If the source gtid values are reset to a lower value than those of the replica, if an erroneous

reset
                    master

was issued on the source, for example, inbound replication is not recoverable. In that scenario, recreate the DB system and inbound replication configuration.

Do the following to re-synchronize the replica with the source:

Stop the existing replication channel and pause all transactions on the source.
Export the source data to object storage. See Exporting a MySQL Instance.
Remove older database objects and pre-existing databases on the replica.
Import the dump from Object Storage into the DB System. See Importing Using MySQL Shell.
Retrieve the gtidExecuted value from the replica.
Retrieve the gtidExecuted value from the source dump @.json file.
Compare the replica's gtidExecuted with the source dump gtidExecuted to get the gtidExecuted delta.
For example, if the source dump's gtidExecuted is serverUUID: 1-2000, and the replica's gtidExecuted is serverUUID:1-1000, the delta value is 1001-2000.
Connect to the replica from the command line and apply the delta to the replica using the CALL sys.SET_GTID_PURGED("+<ServerUUID>:<DeltaValue>) stored procedure.
For example, using the delta value shown in the earlier example:
```
call sys.SET_GTID_PURGED("+<ServerUUID>:1001-2000")
```
After the process completes, resume the replication channel.

Oracle Cloud Infrastructure Documentation