Error
Error Code:
1774
MySQL Error 1774: Malformed GTID Specification
Description
MySQL Error 1774 indicates that a Global Transaction Identifier (GTID) string provided to the server is syntactically incorrect or improperly formatted. This typically occurs during replication setup, failover, or when manually manipulating GTID-related functions.
Error Message
Malformed GTID specification '%s'.
Known Causes
4 known causesIncorrect GTID Format
The GTID string does not adhere to the expected format (e.g., `server_uuid:transaction_id`), missing components or having incorrect delimiters.
Typographical Errors
Simple mistakes like typos, extra spaces, or misplaced characters within the GTID string prevent MySQL from parsing it correctly.
Invalid Characters Used
The GTID string contains characters that are not allowed in the GTID specification, leading to a parsing error.
Misuse of GTID Functions/Variables
Attempting to set or use GTID-related system variables or functions with an invalid GTID string as input.
Solutions
3 solutions available1. Correcting GTID String Syntax easy
Ensures the GTID string adheres to the expected format.
1
Examine the GTID specification that triggered the error. This is usually found in error logs or configuration files.
2
Verify the format. A valid GTID specification typically looks like `server_uuid:interval1:interval2,...`. For example, `aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-5:10`. Ensure there are no extra commas, spaces, or invalid characters.
3
If you are manually setting `gtid_executed` or `gtid_purged`, correct the syntax in your configuration file (e.g., `my.cnf` or `my.ini`) or the `SET GLOBAL` command.
SET GLOBAL gtid_executed = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-5';
SET GLOBAL gtid_purged = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-5';4
Restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql2. Rebuilding GTID Information medium
Resets and rebuilds the GTID execution and purged information.
1
Stop the MySQL server.
sudo systemctl stop mysql2
Locate the MySQL data directory. This is often `/var/lib/mysql` but can be specified in `my.cnf`.
3
Backup the `gtid_executed` and `gtid_purged` files. These are typically `gtid_executed.000001` and `gtid_purged.000001` (or similar sequential naming) within the data directory.
cp /var/lib/mysql/gtid_executed.000001 /var/lib/mysql/gtid_executed.000001.bak
cp /var/lib/mysql/gtid_purged.000001 /var/lib/mysql/gtid_purged.000001.bak4
Delete the original `gtid_executed` and `gtid_purged` files.
rm /var/lib/mysql/gtid_executed.000001
rm /var/lib/mysql/gtid_purged.0000015
Start the MySQL server. The server will generate new, empty GTID files.
sudo systemctl start mysql6
After the server starts, you may need to re-apply the correct GTID set if this is part of a replication setup. This often involves using the `CHANGE MASTER TO` command with the appropriate `MASTER_AUTO_POSITION=1` and potentially manually setting `gtid_executed` and `gtid_purged` if the server cannot determine them correctly.
CHANGE MASTER TO MASTER_HOST='<master_host>', MASTER_USER='<replication_user>', MASTER_PASSWORD='<replication_password>', MASTER_PORT=<master_port>, MASTER_AUTO_POSITION=1;3. Disabling and Re-enabling GTID medium
Temporarily disables GTID to allow server startup, then re-enables it properly.
1
Stop the MySQL server.
sudo systemctl stop mysql2
Edit your MySQL configuration file (e.g., `my.cnf` or `my.ini`). Find the `[mysqld]` section.
3
Comment out or remove the `gtid_mode` and `enforce_gtid_consistency` lines. If you have `log_bin` enabled, ensure it remains.
[mysqld]
# gtid_mode = ON
# enforce_gtid_consistency = ON
log_bin = mysql-bin4
Start the MySQL server. This should allow it to start without the GTID error.
sudo systemctl start mysql5
Once the server is running, connect to it and re-enable GTID with the correct settings.
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL log_bin = 'mysql-bin';6
Gracefully restart the MySQL server for the `SET GLOBAL` changes to be fully recognized and persisted.
sudo systemctl restart mysql7
Verify that GTID is enabled and working correctly by checking `SHOW GLOBAL VARIABLES LIKE 'gtid_mode';` and `SHOW MASTER STATUS;`.
SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
SHOW MASTER STATUS;