Detect and Clean Root‑Defined MySQL Objects After Renaming the Root User

When the MySQL root account is renamed, objects that were created with DEFINER='root'@'%' (procedures, functions, views, triggers) become invalid because the original definer no longer exists. The article shows how to locate these objects and automatically rebuild them.

Finding objects with the old definer

-- Check for objects whose DEFINER is 'root'@'%'
SELECT routine_schema AS ob_schema,
       routine_name   AS ob_name,
       routine_type   AS ob_type,
       DEFINER        AS ob_definer
FROM   information_schema.ROUTINES
WHERE  ROUTINE_SCHEMA NOT IN ('sys')
  AND  DEFINER = 'root@%'
UNION ALL
SELECT table_schema,
       table_name,
       'view' AS type,
       DEFINER
FROM   information_schema.VIEWS
WHERE  table_schema NOT IN ('sys')
  AND  DEFINER = 'root@%'
UNION ALL
SELECT TRIGGER_schema,
       TRIGGER_NAME AS object_name,
       'TRIGGER'   AS object_type,
       DEFINER
FROM   information_schema.TRIGGERS
WHERE  TRIGGER_SCHEMA NOT IN ('sys')
  AND  DEFINER = 'root@%'
ORDER BY 4;

The query returns the schema, object name, type, and definer for each offending object.

Generating SHOW CREATE statements

SELECT CONCAT('show create ', t.ob_type, ' `', t.ob_schema, '`.`', t.ob_name, '` ;')
FROM (
  SELECT routine_schema AS ob_schema,
         routine_name   AS ob_name,
         routine_type   AS ob_type,
         DEFINER        AS ob_definer
  FROM   information_schema.ROUTINES
  WHERE  ROUTINE_SCHEMA NOT IN ('sys')
    AND  DEFINER NOT IN (SELECT CONCAT(user, '@', host) FROM mysql.user)
  UNION ALL
  SELECT table_schema,
         table_name,
         'view' AS type,
         DEFINER
  FROM   information_schema.VIEWS
  WHERE  table_schema NOT IN ('sys')
    AND  DEFINER NOT IN (SELECT CONCAT(user, '@', host) FROM mysql.user)
  UNION ALL
  SELECT TRIGGER_schema,
         TRIGGER_NAME AS object_name,
         'TRIGGER'   AS object_type,
         DEFINER
  FROM   information_schema.TRIGGERS
  WHERE  TRIGGER_SCHEMA NOT IN ('sys')
    AND  DEFINER NOT IN (SELECT CONCAT(user, '@', host) FROM mysql.user)
) t;

The resulting statements can be executed to retrieve the exact DDL of each object.

Python automation script

import sys
import argparse
from datetime import datetime, timedelta

def parse_arguments():
    """Parse command‑line arguments"""
    parser = argparse.ArgumentParser(description='MySQL shell')
    parser.add_argument('--host', default='localhost', help='MySQL server host')
    parser.add_argument('--port', type=int, default=3306, help='MySQL server port')
    parser.add_argument('--user', required=True, help='MySQL username')
    parser.add_argument('--password', required=True, help='MySQL password')
    parser.add_argument('--socket', help='MySQL socket file path')
    return parser.parse_args(sys.argv[1:])

def get_session(args):
    """Create a MySQL Shell session"""
    try:
        if hasattr(args, 'socket') and args.socket:
            shell.connect({'socket': args.socket, 'user': args.user, 'password': args.password})
        else:
            shell.connect({'host': args.host, 'port': args.port, 'user': args.user, 'password': args.password})
        return shell.get_session()
    except Exception as e:
        print(f"Connection failed: {e}")

def execute_sql(session, sql):
    """Run a SQL statement and return the result set"""
    try:
        return session.run_sql(sql)
    except Exception as e:
        print(f"SQL execution failed: {sql}
Error: {e}")
        return None

def show_binlog_status(session):
    sql_statements = [
        "select concat('show create ',t.ob_type,' `', t.ob_schema,'`.`',t.ob_name,'` ;') from ("
        "SELECT routine_schema AS ob_schema, routine_name AS ob_name, routine_type AS ob_type, DEFINER AS ob_definer "
        "FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA NOT IN ('sys') AND DEFINER ='root@%' "
        "UNION ALL SELECT table_schema, table_name, 'view' AS type, DEFINER FROM information_schema.VIEWS "
        "WHERE table_schema NOT IN ('sys') AND DEFINER ='root@%' "
        "UNION ALL SELECT TRIGGER_schema, TRIGGER_NAME AS object_name, 'TRIGGER' AS object_type, DEFINER "
        "FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA NOT IN ('sys') AND DEFINER ='root@%' "
        "order by 4) t;"
    ]
    try:
        for sql in sql_statements:
            result = execute_sql(session, sql)
            if result:
                rows = result.fetch_all()
                for row in rows:
                    show_sql = row[0]
                    drop_sql = show_sql.replace('show create', 'drop')
                    print(f"
{drop_sql}
")
                    ddl_res = execute_sql(session, show_sql)
                    if ddl_res:
                        ddl_rows = ddl_res.fetch_all()
                        if 'view' in show_sql.lower():
                            for stmt in ddl_rows:
                                print('DELIMITER //')
                                print(stmt[1])
                                print('//')
                                print('DELIMITER ;')
                        else:
                            for stmt in ddl_rows:
                                print('DELIMITER //')
                                print(stmt[2])
                                print('//')
                                print('DELIMITER ;')
    except Exception as e:
        print(f"Error during execution: {e}")

def main():
    args = parse_arguments()
    session = get_session(args)
    if not session:
        print("Unable to connect to the database!")
        return
    try:
        show_binlog_status(session)
    finally:
        if session:
            session.close()

if __name__ == "__main__":
    main()

The script connects to a MySQL instance, runs the combined query to list all objects with the old definer, builds corresponding DROP statements, and prints them with proper delimiters for views and other objects, allowing bulk cleanup across dozens of databases.

Key takeaways

Always audit DEFINER values before renaming privileged accounts.

Use information_schema to locate affected routines, views, and triggers.

Automate the generation of SHOW CREATE and DROP statements to avoid manual reconstruction.

A short Python script can safely process hundreds of databases with minimal effort.