📋

Description

This error indicates an attempt to modify the `@@GLOBAL.GTID_MODE` system variable, which controls Global Transaction Identifiers, under conditions where such a change is not permitted. It typically occurs when the server is actively running in a specific GTID state, is part of a replication topology, or has other operational restrictions.

💬

Error Message

SET @@GLOBAL.GTID_MODE = %s is not allowed because %s.

🔍

Known Causes

4 known causes

⚠️

Conflicting GTID State

The server's current `GTID_MODE` setting (e.g., `ON` or `OFF`) prevents a direct global change without a server restart or specific preparatory steps.

⚠️

Replication Topology Constraint

Altering `GTID_MODE` globally is often restricted when the server is actively participating in a replication setup to maintain data consistency.

⚠️

Server Startup Configuration

The `GTID_MODE` might be explicitly set in the server's configuration file (`my.cnf`) at startup, disallowing runtime global modifications.

⚠️

Read-Only Server Mode

If the server is in a read-only state (e.g., `super_read_only` enabled), global configuration changes are blocked to protect data integrity.

🛠️

Solutions

3 solutions available

1. Restart MySQL Server with Correct Configuration medium

Modify the my.cnf/my.ini file and restart the MySQL server.

1

Locate your MySQL configuration file. This is typically named `my.cnf` on Linux/macOS or `my.ini` on Windows. Common locations include `/etc/mysql/my.cnf`, `/etc/my.cnf`, or within the MySQL installation directory.

2

Open the configuration file in a text editor with administrative privileges.

3

Find the `[mysqld]` section. If it doesn't exist, create it.

[mysqld]

4

Add or modify the `gtid_mode` setting to the desired value (e.g., `ON`, `OFF`, or `LOCAL`). For example, to enable GTID, add `gtid_mode = ON`.

gtid_mode = ON

5

Save the configuration file.

6

Restart the MySQL server. The command to do this varies by operating system and installation method.

# For systemd-based systems (e.g., Ubuntu 15.04+, CentOS 7+)
sudo systemctl restart mysql

# For older SysVinit systems (e.g., Ubuntu < 15.04, CentOS 6)
sudo service mysql restart

# For Windows (using Services)
# Open Services.msc, find MySQL, and restart.

2. Use `SET GLOBAL` with `SUPER` Privilege (Temporary) easy

Temporarily change GTID mode by restarting the server with the correct configuration first, then use SET GLOBAL.

1

Ensure that the MySQL server is configured to allow GTID changes at startup. This means you've already performed the steps in 'Restart MySQL Server with Correct Configuration' to set `gtid_mode` in your `my.cnf`/`my.ini` file and restarted the server.

2

Connect to your MySQL server as a user with the `SUPER` privilege (e.g., the `root` user).

mysql -u root -p

3

Execute the `SET GLOBAL` command with the desired GTID mode. Replace `%s` with your desired value (e.g., `ON`, `OFF`, `LOCAL`).

SET GLOBAL gtid_mode = 'ON';

4

Verify the change by checking the global variable.

SHOW GLOBAL VARIABLES LIKE 'gtid_mode';

3. Rebuild Replication Topology (Advanced) advanced

If the error occurs during replication setup or after a failed GTID mode change, rebuilding the replication setup might be necessary.

1

Identify the replication channel experiencing the error and the affected servers (primary and replica).

2

Stop replication on all involved servers.

STOP SLAVE;
STOP REPLICA;

3

On the primary server, ensure `gtid_mode` is correctly set in `my.cnf`/`my.ini` and restart it.

4

On the replica server(s), ensure `gtid_mode` is correctly set in `my.cnf`/`my.ini` and restart them.

5

Re-initialize the replica(s). This may involve taking a fresh backup of the primary and restoring it on the replica, or using `CHANGE MASTER TO` or `CHANGE REPLICATION SOURCE TO` with the correct GTID settings and a consistent snapshot.

# Example for CHANGE MASTER TO (older versions)
CHANGE MASTER TO MASTER_HOST='<primary_ip>', MASTER_USER='<repl_user>', MASTER_PASSWORD='<repl_password>', MASTER_PORT=<primary_port>, MASTER_AUTO_POSITION=1;

# Example for CHANGE REPLICATION SOURCE TO (MySQL 8.0+)
CHANGE REPLICATION SOURCE TO SOURCE_HOST='<primary_ip>', SOURCE_USER='<repl_user>', SOURCE_PASSWORD='<repl_password>', SOURCE_PORT=<primary_port>, SOURCE_AUTO_POSITION=1;

6

Start replication on the replica(s).

START SLAVE;
START REPLICA;

7

Monitor replication status to ensure it's running correctly.

SHOW SLAVE STATUS;
SHOW REPLICA STATUS;

🔗

Related Errors

5 related errors

MySQL Error 3111: Global GTID Mode Change Restricted