Error

Error Code: 1777

MySQL Error 1777: GTID_MODE Off Prevents Auto-Position

📦 MySQL

📋

Description

This error indicates an attempt to configure or modify a MySQL replication channel using `SOURCE_AUTO_POSITION = 1` when the Global Transaction Identifier (GTID) mode is disabled on the server. It occurs because auto-positioning relies entirely on GTIDs to track replication progress, which cannot function if `@@GLOBAL.GTID_MODE` is set to `OFF`.

💬

Error Message

CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1 cannot be executed because @@GLOBAL.GTID_MODE = OFF.

🔍

Known Causes

3 known causes

⚠️

GTID Mode Disabled

The `gtid_mode` system variable is globally set to `OFF`, preventing any GTID-based operations, including auto-positioning.

⚠️

Server Not GTID-Enabled

The MySQL server instance has not been properly initialized or configured to support GTID replication from its startup.

⚠️

Misconfigured Replication Command

The `CHANGE REPLICATION SOURCE TO` statement incorrectly includes `SOURCE_AUTO_POSITION = 1` when GTIDs are not active on the source server.

🛠️

Solutions

3 solutions available

1. Enable GTID Mode on the Source Server advanced

The most robust solution is to enable GTID mode on the source server, which is a prerequisite for auto-position.

Stop the MySQL server on the source. Ensure no applications are writing to the database during this process.

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

# For init.d-based systems (e.g., older Ubuntu/Debian, CentOS 6)
sudo service mysql stop

Edit the MySQL configuration file (my.cnf or my.ini). The location varies by OS and installation method. Common locations include /etc/my.cnf, /etc/mysql/my.cnf, or within /etc/mysql/conf.d/.

sudo nano /etc/my.cnf

Add or uncomment the following lines under the [mysqld] section to enable GTID mode and set the required binlog format.

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
binlog_format = ROW

Save the configuration file and restart the MySQL server.

# For systemd-based systems
sudo systemctl start mysqld

# For init.d-based systems
sudo service mysql start

Verify that GTID mode is enabled by connecting to the MySQL server and running the following command.

SHOW GLOBAL VARIABLES LIKE 'gtid_mode';

Once GTID mode is confirmed as ON, you can execute the CHANGE REPLICATION SOURCE TO command on the replica.

CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1;

2. Manually Specify Source Log File and Position medium

If enabling GTID mode is not immediately feasible, manually track and specify the source's binary log file and position.

On the source server, ensure binary logging is enabled (`log_bin` variable is set) and the `binlog_format` is ROW. If not, you will need to restart the source with these settings as described in Solution 1.

SHOW GLOBAL VARIABLES LIKE 'log_bin';
SHOW GLOBAL VARIABLES LIKE 'binlog_format';

On the source server, identify the current binary log file and its position. This will be your starting point for replication.

SHOW MASTER STATUS;

Note down the 'File' and 'Position' values from the output of `SHOW MASTER STATUS;`. For example, 'mysql-bin.000001' and '12345'.

text
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000001 | 12345    |
+------------------+----------+

On the replica server, execute the `CHANGE REPLICATION SOURCE TO` command, specifying the `SOURCE_LOG_FILE` and `SOURCE_LOG_POS` obtained from the source.

CHANGE REPLICATION SOURCE TO SOURCE_HOST='<source_host_ip>', SOURCE_USER='<replication_user>', SOURCE_PASSWORD='<replication_password>', SOURCE_LOG_FILE='<file_name_from_source>', SOURCE_LOG_POS=<position_from_source>;

Start the replication threads.

START REPLICA;

Monitor replication status. You will need to periodically update the `SOURCE_LOG_FILE` and `SOURCE_LOG_POS` on the replica as the source binary logs rotate and advance. This is a manual process and is prone to errors if not managed diligently.

SHOW REPLICA STATUS;

3. Temporarily Disable Auto-Position easy

A quick workaround if you need to establish replication immediately and will address GTID later.

On the replica server, execute the `CHANGE REPLICATION SOURCE TO` command with `SOURCE_AUTO_POSITION = 0` (or simply omit `SOURCE_AUTO_POSITION = 1`).

CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 0;

This allows the command to succeed without GTID mode enabled. However, you will then need to manually specify the `SOURCE_LOG_FILE` and `SOURCE_LOG_POS` as detailed in Solution 2.

CHANGE REPLICATION SOURCE TO SOURCE_HOST='<source_host_ip>', SOURCE_USER='<replication_user>', SOURCE_PASSWORD='<replication_password>', SOURCE_LOG_FILE='<file_name_from_source>', SOURCE_LOG_POS=<position_from_source>;

After setting up replication with manual log file and position, plan to implement GTID mode on the source server and reconfigure replication to use `SOURCE_AUTO_POSITION = 1` for a more robust and manageable setup.

text
# Example plan:
# 1. Enable GTID on source (requires downtime).
# 2. Take a fresh backup/snapshot of the source.
# 3. Restore backup on replica.
# 4. Reconfigure replica to use SOURCE_AUTO_POSITION = 1.

🔗

Related Errors

5 related errors