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

Question

I took backup of database from workbench from a remote server. So when I import it on my local environment I got error:

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

I'm using xampp server

Getting same error either import db via console or phpmyadmin

Answer 1

Need remove all lines with GTID_PURGED

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

The final file will load without problems

Answer 2

After spending a lot of time.

I opened my database file in text editor and search for this variable "GLOBAL.GTID_PURGED". I just set it's value ""

like

SET @@GLOBAL.GTID_PURGED=""

Then I upload database via windows console now It works like a charm :)

Answer 3

Re-create the dump file by appending the --set-gtid-purged=OFF option would resolve the problem.

It was because GTIDs was added in MySQL 5.6, which is not recognized by the earlier versions.

Your command might look like below:

mysqldump -u username -ppassword -h mydbhost --set-gtid-purged=OFF db_name > dump_file.sql

More on my story, I got the same problem with a dump file originated from MySQL 5.7. I was trying the import the data into a new CentOS 7 installation with the default MariaDB installation, which is 5.5 (I guess).

The first idea came to my mind was to upgrade to latest MariaDB. Luckily their website provides a great utility to help set the package repository for Linux variaties. Moreover, digitalocean has a very short and clear guide for the upgrade process, thanks to them too!

While upgrading to the lastest MariaDB stable version 10.2 does NOT get rid of this problem. So I still have to use the option mentioned above, but it let me upgrade to the latest MariaDB anyway.

Another problem after my upgrading was that the innodb_additional_mem_pool_size config from my-innodb-heavy-4G.cnf is not supported anymore on the latest MariaDB, server failed to start. From the MySQL documentation, it was removed from MySQL 5.7. I can start the server after commenting it out. I'm not the DB expert, I would not spend more time to check how exactly MariaDB version mapping to MySQL DB, and what difference they have.