ERROR 1193 (HY000): Unknown system variable ‘GTID_PURGED’ – How to Solve?

In the MySQL database, you may the following error during migrations or imports:

ERROR 1193 (HY000): Unknown system variable 'GTID_PURGED'

This error is related to Global Transaction Identifiers (GTIDs) in your SQL file, which is not being identified by the MySQL version. In this post, I’ll show you how you can fix this error.

What Causes the “Unknown system variable ‘GTID_PURGED'” Error?

In MySQL servers, the GTID_PURGED variable tracks transactions that have been purged from the binary logs. Each transaction is assigned a GTID, which is used to maintain consistency during replication.

The Unknown system variable ‘GTID_PURGED’ commonly occurs in the following scenarios:

• You are migrating a database that was exported from a server with GTIDs enabled
• The SQL dump file contains statements related to GTID_PURGED
• You are using an older version of MySQL that does not support GTIDs

Solution to MySQL GTID Error: Unknown System Variable ‘GTID_PURGED’

You can solve this error simply by removing the GTID (@@GLOBAL.GTID_PURGED) references from the SQL file that you’re importing.

Open the terminal and navigate to the directory containing the SQL file.

cd /path/to/your/sql/file

Now, use the sed command to remove the GTID references from the SQL file:

sed -i '/@@GLOBAL.GTID_PURGED=/d' your_sql_file.sql

You can replace your_sql_file.sql with your actual SQL file name in the above command.

Once the SQL file is modified, you can then import the file into the MySQL database without any error.

I hope this tutorial has helped you fix the ERROR 1193 (HY000): Unknown system variable ‘GTID_PURGED’ issue in MySQL. In case you have any other doubts or queries, feel free to leave a comment below.