Create and run a MySQL migration job containing metadata with a DEFINER clause

Overview

A MySQL migration job doesn't migrate user data. Therefore, sources that contain user-defined metadata with the DEFINER clause fail when invoked on the new Cloud SQL replica, because the users don't yet exist there.

To identify which DEFINER values exist in your metadata, query your MySQL source database. Check the results for entries for either root%localhost or for users that don't exist in the destination instance:

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA NOT 
IN ('mysql', 'sys');
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA NOT 
IN ('mysql', 'sys');
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA NOT 
IN ('mysql', 'sys');
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA NOT 
IN ('mysql', 'sys');

To run a migration job from a source that includes such metadata, do one of the following:

  • Create the users on the destination Cloud SQL replica instance before starting your migration job.

    1. To create a draft migration job without starting it, select Create instead of Create & Start.

    2. On your destination Cloud SQL instance, recreate the users from your source MySQL instance by using the Cloud SQL API or the Google Cloud console.

    3. From the list of migration jobs or the page for the specific job, start the migration job.

  • Update the DEFINER clause to INVOKER on your source MySQL instance prior to setting up your migration job.