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, you can run the following queries on 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 which includes such metadata, you can do one of the following:

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

  1. Create a migration job without starting it. That is, choose Createinstead 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.