Error
Error Code:
3062
MySQL Error 3062: Operation Requires GTID_MODE ON
Description
This error indicates that MySQL cannot perform a specific operation because Global Transaction Identifiers (GTIDs) are currently disabled (`GTID_MODE = OFF`). This typically occurs when attempting actions that fundamentally rely on GTIDs for ensuring data consistency or managing replication, such as setting up GTID-based replication or certain administrative tasks.
Error Message
Cannot %s when GTID_MODE = OFF.
Known Causes
3 known causesGTID-Based Replication Setup
You are attempting to configure or start replication using GTID-based methods, which require GTID_MODE to be enabled on the server.
GTID-Related Administrative Task
An administrative command or statement that implicitly or explicitly relies on GTID functionality was executed while GTID_MODE is disabled.
Server Configuration Change
An attempt was made to modify server settings or perform an operation that requires GTID_MODE to be active for proper execution or consistency.
Solutions
3 solutions available1. Temporarily Enable GTID_MODE for a Single Operation easy
Enables GTID_MODE for the current session, allowing the operation to complete without a server restart.
1
Connect to your MySQL server using a client that allows setting session variables (like the `mysql` command-line client).
2
Set the `gtid_mode` to `ON` for the current session.
SET SESSION gtid_mode = ON;3
Execute the operation that previously failed (e.g., replication setup, point-in-time recovery).
/* Your original failing SQL command here */4
The `gtid_mode` will automatically revert to its global setting when the session ends.
2. Enable GTID_MODE Globally (Requires Server Restart) medium
Permanently enables GTID_MODE for the MySQL server by modifying the configuration file and restarting the server.
1
Locate your MySQL configuration file (e.g., `my.cnf` or `my.ini`). This is typically found in `/etc/mysql/`, `/etc/`, or within the MySQL installation directory.
2
Open the configuration file with a text editor (e.g., `nano`, `vim`, `notepad`). You might need root privileges.
sudo nano /etc/mysql/my.cnf3
Find the `[mysqld]` section and add or modify the following lines:
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON4
Save the changes to the configuration file and exit the editor.
5
Restart the MySQL server to apply the changes.
sudo systemctl restart mysql6
Verify that `gtid_mode` is now `ON` by connecting to MySQL and running:
SHOW GLOBAL VARIABLES LIKE 'gtid_mode';3. Replicate to a Server with GTID_MODE Enabled medium
If you are setting up replication, ensure the *replica* server has GTID_MODE enabled.
1
On the *replica* server, follow the steps in the "Enable GTID_MODE Globally" solution to configure and restart the MySQL server.
2
On the *source* server, ensure you are using GTID-based replication. This typically involves setting `log_bin` and `gtid_mode = ON` on the source as well.
[mysqld]
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON3
Configure the replication channel on the replica to use GTIDs. When setting up the replica, you'll use `CHANGE REPLICATION SOURCE TO MASTER_AUTO_POSITION = 1;` (or `CHANGE REPLICATION MASTER TO MASTER_AUTO_POSITION = 1;` for older versions).
CHANGE REPLICATION SOURCE TO SOURCE_HOST='<source_host>', SOURCE_USER='<user>', SOURCE_PASSWORD='<password>', MASTER_AUTO_POSITION = 1;