Error
Error Code:
1788
MySQL Error 1788: Invalid GTID_MODE Transition
Description
This error indicates that you have attempted to change the `GTID_MODE` global system variable in MySQL to a state that is not directly adjacent to its current value. MySQL requires `GTID_MODE` to be transitioned one step at a time through a specific sequence (OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON) to ensure data consistency, especially in replication environments.
Error Message
The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.
Known Causes
3 known causesNon-Adjacent GTID_MODE Change
This occurs when an administrator tries to transition `GTID_MODE` from its current value to a state that is not the immediate next or previous step in the defined sequence (e.g., from `OFF` directly to `ON_PERMISSIVE`).
Inconsistent Replication State
In a MySQL replication setup, `GTID_MODE` changes must be applied uniformly and simultaneously across all servers (master and replicas). Changing it on only a subset of servers can lead to this error.
Unaware of GTID Transition Protocol
Administrators who are not fully familiar with the strict, multi-step protocol required for safely enabling or disabling GTID replication might inadvertently attempt an unsupported state change.
Solutions
3 solutions available1. Gradual GTID_MODE Transition medium
Safely transition GTID_MODE by moving one step at a time across all servers.
1
Identify the current `@@GLOBAL.GTID_MODE` on all servers in your replication topology.
SELECT @@GLOBAL.GTID_MODE;2
Plan the transition path. For example, if you are at `OFF`, you must go through `OFF_PERMISSIVE` before reaching `ON_PERMISSIVE` or `ON`.
Example path: OFF -> OFF_PERMISSIVE -> ON_PERMISSIVE -> ON3
On each server, stop replication threads if they are running.
STOP SLAVE; -- or STOP REPLICA; for MySQL 8.0.22+4
On each server, modify the MySQL configuration file (e.g., `my.cnf` or `my.ini`) to set the `gtid_mode` parameter to the next allowed step. For example, if transitioning from `OFF` to `OFF_PERMISSIVE`, set `gtid_mode = OFF_PERMISSIVE`.
[mysqld]
gtid_mode = OFF_PERMISSIVE
5
Restart each MySQL server. This change will be applied globally upon restart.
sudo systemctl restart mysql6
Verify the `@@GLOBAL.GTID_MODE` on each server after the restart.
SELECT @@GLOBAL.GTID_MODE;7
Once all servers have successfully transitioned to the intermediate step, repeat steps 3-7 for the next step in your planned transition path until you reach the desired `GTID_MODE` (e.g., `ON`).
Repeat for OFF_PERMISSIVE -> ON_PERMISSIVE, and ON_PERMISSIVE -> ON8
After reaching the final desired `GTID_MODE`, restart the replication threads.
START SLAVE; -- or START REPLICA; for MySQL 8.0.22+2. Rolling Restart with Configuration Update medium
Apply the GTID_MODE change by restarting servers one by one, ensuring each server is in a valid intermediate state.
1
Ensure all servers are running the same MySQL version.
SELECT VERSION();2
Stop replication on all servers.
STOP SLAVE; -- or STOP REPLICA; for MySQL 8.0.22+3
Choose a master server and modify its configuration file (`my.cnf` or `my.ini`) to set `gtid_mode` to the next valid step. For example, if currently `OFF`, set it to `OFF_PERMISSIVE`.
[mysqld]
gtid_mode = OFF_PERMISSIVE
4
Restart the chosen master server.
sudo systemctl restart mysql5
Verify the `@@GLOBAL.GTID_MODE` on the restarted master.
SELECT @@GLOBAL.GTID_MODE;6
Repeat steps 3-5 for each slave server, one at a time, ensuring each server is restarted with the `gtid_mode` set to the same intermediate value as the master. This is crucial for maintaining replication consistency.
Repeat for each slave, then move to the next GTID_MODE step if needed.7
Once all servers are running the intermediate `GTID_MODE`, repeat steps 3-6 to advance to the next step, and continue this process until the desired `GTID_MODE` is achieved across the entire topology.
Iteratively move through OFF_PERMISSIVE -> ON_PERMISSIVE -> ON8
After all servers are on the final desired `GTID_MODE`, start replication threads.
START SLAVE; -- or START REPLICA; for MySQL 8.0.22+3. Emergency Re-configuration (Use with Caution) advanced
For critical situations, this involves a more direct, but potentially disruptive, approach to GTID_MODE changes.
1
This method assumes you have a strong understanding of your replication setup and potential impact. It is highly recommended to have backups before proceeding.
Backup all your MySQL databases.2
Stop all replication threads on all servers.
STOP SLAVE; -- or STOP REPLICA; for MySQL 8.0.22+3
On ALL servers, edit the MySQL configuration file (`my.cnf` or `my.ini`) to set the desired `gtid_mode` directly. **WARNING:** This bypasses the one-step transition rule and can lead to replication inconsistencies if not handled perfectly. This is generally not recommended for production systems without thorough testing.
[mysqld]
gtid_mode = ON
4
Restart ALL MySQL servers simultaneously. This is the riskiest part, as all servers must come up with the new `gtid_mode` in sync.
sudo systemctl restart mysql5
Verify the `@@GLOBAL.GTID_MODE` on all servers. If replication does not start or shows errors, you will need to troubleshoot extensively, potentially involving manual GTID purging or re-initializing replication.
SELECT @@GLOBAL.GTID_MODE;6
If the direct transition was successful, start replication threads.
START SLAVE; -- or START REPLICA; for MySQL 8.0.22+