I just can't migrate my external DB because unable to skip replication errors

[Sameer Panjwani]

I have made 4 attempts thus far in migrating my DB from an external server to GCloud SQL 2nd Gen but have failed every time. 

The mysqldump process was successfully completed. Close to 2TB data was successfully imported into the cloud and the replication process successfully started. However, for some reason or the other, would get a 1062 duplicate error. I couldn't figure a way to skip it because of the lack of a SUPER privilege. I know those errors can be ignored but I'm not allowed to set any variable to tell the replication to skip 1062 errors. 

I would then just make another attempt, re-dumping the DB (it takes a few days cos of the size), then again resuming replication, only to face the same issue just before it can reach the final sync.

I need some solution to figure this out, quite tired of trying from scratch every time, this gone on for a few weeks and wasted bills as well.

[Yasser Karout]

Unfortunately, it is not possible to skip these errors on the slave instance. There is an open Feature Request [1] for Cloud SQL to allow users to modify '--slave-skip-errors’ Flag on MySQL to allow the replication process to continue.

You can check to make sure your source database meets the requirements listed here for replication in Cloud SQL [2] and these best practices regarding long running exports/imports [3]. The blue box here [4] could also be relevant to you since it mentions which elements could be causing the SUPER errors.

[1] http://issuetracker.google.com/128603238

[2] https://cloud.google.com/sql/docs/mysql/replication/replication-from-external#server-requirements

[3] https://cloud.google.com/sql/docs/mysql/import-export/#tips_for_long-running_import_and_export_processes

[4] https://cloud.google.com/sql/docs/mysql/import-export/importing#importing_a_sql_dump_file

[Sameer Panjwani]

I have plenty of legacy apps and some of them or their installed packages have been coded in a way that if the insertion returns a 1062 duplicate error then the app's code will call an update query, e.g. if($insertion_failure_code=="1062") update_query($params). Yes the applications could be coded better to avoid this but the fact is many are not and I don't see it being practical to find and update all such coding instances and then attempt a Cloud SQL migration. I am quite sure I'm not the only one who would face this issue.

 

I really do want to migrate to Cloud SQL and so hoping for some workaround on this issue. Else I'm just forced to consider other platforms when I don't want to.

 

What else could I do to get it to work? Or are you suggesting I just don't consider Cloud SQL for now and look at other options?

[Sameer Panjwani]

I have plenty of legacy apps and some of them or their installed packages have been coded in a way that if the insertion returns a 1062 duplicate error then to call an update query. Yes the applications could be coded better to avoid this but the fact is many are not and I don't see it as being practical to find and update all such coding instances and then attempt a Cloud SQL migration. I am quite sure I'm not the only one who would face this issue.

I really do want to migrate to Cloud SQL and so hoping for some workaround on this issue. Else I'm just forced to consider other platforms when I don't want to.

What else could I do to get it to work? Or are you suggesting I just don't consider Cloud SQL for now and look at other options?

On Sat, 15 Jun 2019, 01:49 'Yasser Karout' via Google Cloud SQL discuss, <google-cloud...@googlegroups.com> wrote:

Unfortunately, it is not possible to skip these errors on the slave instance. There is an open Feature Request [1] for Cloud SQL to allow users to modify '--slave-skip-errors’ Flag on MySQL to allow the replication process to continue.

You can check to make sure your source database meets the requirements listed here for replication in Cloud SQL [2] and these best practices regarding long running exports/imports [3]. The blue box here [4] about the data could also be relevant for your error.

[1] http://issuetracker.google.com/128603238

[2] https://cloud.google.com/sql/docs/mysql/replication/replication-from-external#server-requirements

[3] https://cloud.google.com/sql/docs/mysql/import-export/#tips_for_long-running_import_and_export_processes

[4] https://cloud.google.com/sql/docs/mysql/import-export/importing#importing_a_sql_dump_file

--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/N5URxQatCo0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/5a89c2d5-2f60-43c8-890c-5bcb1c604252%40googlegroups.com.

[Yasser Karout]

I looked quite a bit and could not find a workaround to skip these errors in Cloud SQL. Unfortunately, these errors have to be addressed in the initial DB. Another option is to use MySQL on a Compute Engine instance as mentioned in this thread [1].

I also suggest posting this issue on StackOverflow but with MySQL tags, to see if anyone in the community has any other suggestions.

[1] https://issuetracker.google.com/issues/128603238#comment4