How to fix ERROR 1449: User specified as definer doesn't exist in MySQL

-- Find all procedures, functions, and events with their definers
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DEFINER
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME;

-- Find all triggers with their definers
SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, TRIGGER_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY TRIGGER_SCHEMA, TRIGGER_NAME;

-- Find all views with their definers
SELECT TABLE_SCHEMA, TABLE_NAME, DEFINER
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- See all current users in the database
SELECT CONCAT(user, '@', host) AS user_account FROM mysql.user;

-- Create the missing user account
CREATE USER 'missing_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant appropriate privileges
GRANT ALL PRIVILEGES ON your_database.* TO 'missing_user'@'localhost';

-- Or for production, grant only necessary privileges
GRANT EXECUTE ON your_database.* TO 'missing_user'@'localhost';

-- Apply the changes
FLUSH PRIVILEGES;

SELECT CONCAT(user, '@', host) AS user_account FROM mysql.user WHERE user = 'missing_user';

-- For a VIEW, use ALTER VIEW
ALTER DEFINER='existing_user'@'localhost' VIEW view_name AS
SELECT column1, column2 FROM table_name;

-- For a STORED PROCEDURE, use ALTER PROCEDURE
ALTER PROCEDURE procedure_name SQL SECURITY INVOKER;
-- Note: This changes from DEFINER to INVOKER, which changes how privileges are applied

-- For STORED FUNCTION, use ALTER FUNCTION
ALTER FUNCTION function_name SQL SECURITY INVOKER;

-- Drop the old procedure
DROP PROCEDURE procedure_name;

-- Recreate with a new definer
-- (The current user becomes the definer)
CREATE DEFINER='existing_user'@'localhost' PROCEDURE procedure_name()
BEGIN
  -- Original procedure code here
END;

SHOW CREATE PROCEDURE procedure_name;
SHOW CREATE FUNCTION function_name;
SHOW CREATE TRIGGER trigger_name;

-- Update all procedures with a missing definer to a new user
UPDATE mysql.proc
SET definer = 'new_user@localhost'
WHERE db = 'your_database' AND definer = 'old_missing_user@localhost';

-- Update all triggers with a missing definer
UPDATE mysql.triggers
SET definer = 'new_user@localhost'
WHERE trigger_schema = 'your_database' AND definer = 'old_missing_user@localhost';

-- Apply changes
FLUSH PRIVILEGES;

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, DEFINER
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database';

# Export without definers (best for migrations between different user accounts)
mysqldump --skip-definer database_name > dump.sql

# Import as usual
mysql database_name < dump.sql

# Remove DEFINER clauses from the dump file
sed -i 's/DEFINER=[^*]*\*/\*/g' dump.sql

# Or in macOS (BSD sed)
sed -i '' 's/DEFINER=[^*]*\*/\*/g' dump.sql

# Then import
mysql database_name < dump.sql

-- Test a view
SELECT * FROM your_view LIMIT 1;

-- Test a stored procedure
CALL your_procedure();

-- Check that triggers fire (test with INSERT/UPDATE/DELETE)
INSERT INTO trigger_table (column) VALUES ('test');

-- Check that scheduled events run (view event status)
SHOW EVENTS FROM your_database;

-- Verify user exists
SELECT user FROM mysql.user WHERE user = 'new_user';

-- Check SHOW CREATE to confirm the definer was updated
SHOW CREATE VIEW your_view;
SHOW CREATE PROCEDURE your_procedure;