CODEWITHSUNDEEP
google-cloud-sql
bjorndv
bjorndv

Reputation: 623

Cloud sql database export import issue

When exporting an sql database from an instance (2nd gen) and importing it on another sql instance, I get the following error: ERROR 1839 (HY000) at line 24:

@@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_MODE = ON.

According to https://superuser.com/questions/906843/import-mysql-data-failed-with-error-1839 this could be solved by making an sql dump with 

--set-gtid-purged=OFF

But the gcloud sql instances export command does not support this. Is there another way to solve this issue?

Upvotes: 20

Views: 35516

Answers (5)

Netwons
Netwons

Reputation: 1648

1.you can remove this line from the .sql file

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
  1. save file
  2. import to database

Upvotes: 2

levi-manoel
levi-manoel

Reputation: 71

You can run reset master on the database that you are using to solve this, worked for me

Upvotes: 7

Prince Kumar
Prince Kumar

Reputation: 146

We have found the below error

ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

We have import the DB after the login MySQL connect from the command line Then after using the below command to import

mysql> SOURCE stagingdump.sql

I did not say this is the best way but I have resolved my error this way.

Upvotes: 1

Mujeeb
Mujeeb

Reputation: 1215

(In an SQL Editor) run RESET MASTER on the database you're importing to.

issuing RESET MASTER resets the GTID execution history.

https://dev.mysql.com/doc/refman/5.7/en/reset-master.html

Upvotes: 31

Danny Xu
Danny Xu

Reputation: 189

would you try the following two ways?

Option 1) Remove this line from the sql dump file, and import it to your database?
SET @@GLOBAL.GTID_PURGED='1b313522-1ee5-11e6-9c51-0242ac110003:1-2020';

Option 2) turn off binary logging on your instance, then do the export?

Reference Article

Upvotes: 12

Related Questions