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 which contain metadata defined by users with the DEFINER clause will 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, use these queries and check if there are 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 which includes such metadata, you can do one of the following:

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

    1. Create a migration job without starting it. That is, choose Create instead of Create & Start.

    2. Create the users from your source MySQL instance on your destination Cloud SQL instance using the Cloud SQL API or UI.

    3. Start the migration job from the migration job list or the specific job's page.

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