1781 database runtime_error ai_generated true

ERROR 1781 (HY000): @_NEXT cannot be set to UUID:NUMBER when @_MODE = ON.

ID: database/mysql-gtid-purge-failure

Also available as: JSON · Markdown · 中文
95%Fix Rate
85%Confidence
1Evidence
2023-10-05First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
MySQL 5.7 active — — —
MySQL 8.0 active — — —
MySQL 8.4 active — — —

Root Cause

Attempting to manually set gtid_next to a specific GTID while GTID mode is enabled, which is not allowed because GTIDs are automatically generated by the server.

generic

中文

在启用 GTID 模式时尝试手动将 gtid_next 设置为特定 GTID，这是不允许的，因为 GTID 由服务器自动生成。

Official Documentation

https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#option_mysqld_gtid-mode

Workarounds

  1. 95% success Set gtid_next to 'AUTOMATIC' first: `SET @@SESSION.GTID_NEXT = 'AUTOMATIC';` Then let the server generate the GTID automatically. If you need to inject a specific GTID (e.g., for replication recovery), use `SET GTID_NEXT = 'UUID:NUMBER'` only after ensuring GTID_MODE is OFF or using a different session. 
    Set gtid_next to 'AUTOMATIC' first: `SET @@SESSION.GTID_NEXT = 'AUTOMATIC';` Then let the server generate the GTID automatically. If you need to inject a specific GTID (e.g., for replication recovery), use `SET GTID_NEXT = 'UUID:NUMBER'` only after ensuring GTID_MODE is OFF or using a different session.
  2. 90% success If you need to skip a GTID (e.g., after a duplicate key error on replica), use `SET GTID_NEXT = 'CONSISTENCY';` followed by `BEGIN; COMMIT;` and then reset to AUTOMATIC: `SET @@SESSION.GTID_NEXT = 'AUTOMATIC';` 
    If you need to skip a GTID (e.g., after a duplicate key error on replica), use `SET GTID_NEXT = 'CONSISTENCY';` followed by `BEGIN; COMMIT;` and then reset to AUTOMATIC: `SET @@SESSION.GTID_NEXT = 'AUTOMATIC';`

中文步骤

  1. 首先将 gtid_next 设置为 'AUTOMATIC'：`SET @@SESSION.GTID_NEXT = 'AUTOMATIC';` 然后让服务器自动生成 GTID。如果需要注入特定 GTID（例如，用于复制恢复），只有在确保 GTID_MODE 为 OFF 或使用不同会话时，才使用 `SET GTID_NEXT = 'UUID:NUMBER'`。
  2. 如果需要跳过 GTID（例如，副本上发生重复键错误后），使用 `SET GTID_NEXT = 'CONSISTENCY';` 然后 `BEGIN; COMMIT;` 然后重置为 AUTOMATIC：`SET @@SESSION.GTID_NEXT = 'AUTOMATIC';`

Dead Ends

Common approaches that don't work:

  1. Set gtid_next to 'AUTOMATIC' before setting it to a specific value 90% fail

    This is the default state; the error occurs because you're trying to set gtid_next to a specific UUID:NUMBER, which is only allowed in manual mode or when GTID_MODE is OFF

  2. Disable GTID mode globally with SET GLOBAL gtid_mode = OFF 70% fail

    Changing gtid_mode requires a specific sequence (OFF -> OFF_PERMISSIVE -> ON_PERMISSIVE -> ON) and may break replication if GTIDs are in use

  3. Ignore the error and proceed with the transaction 100% fail

    The transaction will fail to commit because gtid_next is in an invalid state