Cannot replicate anonymous transaction when AUTO_POSITION = 1

Question

Our QA MySQL database was on AWS RDS 5.7.mysql_aurora.2.12.0, it has a downstream Debezium kafka connect listening to the binlog. 
I did a Blue/Green upgrade to 8.0.mysql_aurora.3.05.2. Then the Debezium kafka connect is down with the following error message

```
io.debezium.DebeziumException: Cannot replicate anonymous transaction when AUTO_POSITION = 1, at file /rdsdbdata/log/binlog/mysql-bin-changelog.000022, position 194.; the first event '' at 4, the last event read from '/rdsdbdata/log/binlog/mysql-bin-changelog.000022' at 259, the last byte read from '/rdsdbdata/log/binlog/mysql-bin-changelog.000022' at 259. Error code: 1236; SQLSTATE: HY000.
	
at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.wrap(MySqlStreamingChangeEventSource.java:1194) ~[debezium-connector-mysql-1.9.7.Final.jar:1.9.7.Final]
	
at io.debezium.connector.mysql.MySqlStreamingChangeEventSource$ReaderThreadLifecycleListener.onCommunicationFailure(MySqlStreamingChangeEventSource.java:1239) ~[debezium-connector-mysql-1.9.7.Final.jar:1.9.7.Final]
	
at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:1079) ~[mysql-binlog-connector-java-0.27.2.jar:0.27.2]
	
at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:631) ~[mysql-binlog-connector-java-0.27.2.jar:0.27.2]
	
at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:932) ~[mysql-binlog-connector-java-0.27.2.jar:0.27.2]
	
at java.base/java.lang.Thread.run(Thread.java:833) [?:?]
```

I went into the binlog by `show binlog events in 'mysql-bin-changelog.000022';` and see

|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
| ----------- | ----------- | ----------- | ----------- | ----------- | ----------- |
mysql-bin-changelog.000022|4|Format_desc|670670036|123|"Server ver: 5.7.40-log, Binlog ver: 4"
mysql-bin-changelog.000022|123|Previous_gtids|670670036|194|e17c0827-4b5d-3422-9599-274a493a596f:1-623260
mysql-bin-changelog.000022|194|Anonymous_Gtid|670670036|259|SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql-bin-changelog.000022|259|Query|670670036|400|GRANT REPLICATION CLIENT ON *.* TO 'timeadmin'@'%'

I tried several options
1. Set `db-cluster-parameter-group` to `gtid-mode=ON_PERMISSIVE`
```
aws rds modify-db-cluster-parameter-group \
    --db-cluster-parameter-group-name xxxxxx \
    --parameters "ParameterName=gtid-mode,ParameterValue=ON_PERMISSIVE,ApplyMethod=pending-reboot"
```
Debezium kafka connect error out with:
```
io.debezium.DebeziumException: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = ON_PERMISSIVE instead of ON. Error code: 1236; SQLSTATE: HY000.
```

2. Set `db-cluster-parameter-group` to `gtid-mode=OFF`
```
aws rds modify-db-cluster-parameter-group \
    --db-cluster-parameter-group-name xxxxxx \
    --parameters "ParameterName=gtid-mode,ParameterValue=OFF,ApplyMethod=pending-reboot"
```
Debezium kafka connect error out with:
```
io.debezium.DebeziumException: Cannot replicate GTID-transaction when @@GLOBAL.GTID_MODE = OFF, at file /rdsdbdata/log/binlog/mysql-bin-changelog.000020, position 486663.; the first event 'mysql-bin-changelog.000020' at 485797, the last event read from '/rdsdbdata/log/binlog/mysql-bin-changelog.000020' at 486728, the last byte read from '/rdsdbdata/log/binlog/mysql-bin-changelog.000020' at 486728. Error code: 1236; SQLSTATE: HY000.
```

I think it has to do with something with the `SET @@SESSION.GTID_NEXT= 'ANONYMOUS'` in the bin-changelog.

Also, I want to avoid this from happening again because I still need to upgrade our prod MySQL database to 8.0

Thanks a lot in advance.

Accepted Answer

Hi,

I wasn't able to run this command
```
PURGE BINARY LOGS TO 'mysql-bin-changelog.000024';
```
It kept saying `Access denied; you need (at least one of) the SUPER or BINLOG_ADMIN privilege(s) for this operation`. I was not able to figure out a way to get the privilege because the secret and privileges are managed by Terraform.

After a lot of testing and resetting, I was able to figure out another way to fix this. At least, it worked for me.
I turned the snapshot mode on the Debezium application to never (`snapshot.mode = never`) and restarted the Debezium application and it helped me get through that Anonymous transaction. The binlog `mysql-bin-changelog.000024` is somehow deleted.  I just need to change the snapshot mode back to `when_needed` and restart the application once again.

This approach not only fixes the existing QA MySQL database problem but also prevents issues when we did our PROD Blue/Green MySQL 5.7 --> 8 upgrade. We just set `snapshot.mode = never` before the switchover of Blue/Green deployment, then switchover-blue-green-deployment, toggle the snapshot mode back to the original setting (for me it is `when_needed`). Although the Blue/Green upgrade MySQL still created some Anonymous transactions in the binlog, the Debezium application was able to process it or skip it (I am not sure what exactly happened in Debezium during the switchover, but nothing was produced in the Kafka topics)

Answer

The issue you're facing is related to the changes in GTID (Global Transaction Identifier) handling between MySQL 5.7 and 8.0. In MySQL 5.7, anonymous transactions (transactions without a GTID) were allowed, but in MySQL 8.0, all transactions must have a GTID.

> it's important to note that in MySQL 5.7, GTIDs were not enabled by default. You had to explicitly enable GTIDs in the MySQL configuration file (`my.cnf`) to use them. If GTIDs were not enabled, transactions could be performed without a GTID, but this was not recommended if you intended to use GTIDs for replication purposes.

The error `Cannot replicate anonymous transaction when AUTO_POSITION = 1` is because Debezium is trying to read an anonymous transaction from the binlog, but it's not allowed in MySQL 8.0 when using GTID mode (which is the default).

To resolve this issue, you need to purge the binary logs that contain anonymous transactions, and then restart replication from the first GTID transaction after that point. Here are the steps you can follow:

1. Stop the Debezium Kafka Connect process.

2. Connect to your MySQL instance and identify the first binary log file that contains GTID transactions after the anonymous transaction. You can use the following query to find it:

```sql
SHOW BINARY LOGS;
```

This will list all the binary log files. Look for the first file after `mysql-bin-changelog.000022` that doesn't contain any anonymous transactions.

3. Purge all binary logs up to the log file you identified in the previous step. For example, if the first GTID transaction is in `mysql-bin-changelog.000024`, run the following command:

```sql
PURGE BINARY LOGS TO 'mysql-bin-changelog.000024';
```

4. Restart the Debezium Kafka Connect process, and it should now be able to read the binary logs starting from `mysql-bin-changelog.000024`.

To avoid this issue in the future, you can take the following steps:

1. Before upgrading to MySQL 8.0, make sure to purge all binary logs that contain anonymous transactions.

2. After the upgrade, enable the `binlog_row_image=FULL` setting. This will ensure that Debezium can read the complete row images from the binlog, even if there are any anonymous transactions remaining.

3. Consider disabling anonymous transactions in your applications by setting `@@SESSION.GTID_MODE = ON` or `@@GLOBAL.GTID_MODE = ON` (depending on your requirements).

Log_name	Pos	Event_type	Server_id	End_log_pos	Info
mysql-bin-changelog.000022	4	Format_desc	670670036	123	"Server ver: 5.7.40-log, Binlog ver: 4"
mysql-bin-changelog.000022	123	Previous_gtids	670670036	194	e17c0827-4b5d-3422-9599-274a493a596f:1-623260
mysql-bin-changelog.000022	194	Anonymous_Gtid	670670036	259	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql-bin-changelog.000022	259	Query	670670036	400	GRANT REPLICATION CLIENT ON . TO 'timeadmin'@'%'

Cannot replicate anonymous transaction when AUTO_POSITION = 1

Relevant content