A dump restore into RDS fails on a DEFINER clause and asks for SUPER
Access denied; you need (at least one of) the SUPER privilege(s) for this operationShort answer
The dump names another account as DEFINER on a view, trigger, routine or event. MySQL only lets you set a definer other than yourself with a special privilege, and the RDS master user does not hold SUPER. Remove the DEFINER clauses from the dump, or leave routines, triggers and events out and create them again as the master user.
Fix it fast
1Find out which cause you have
Find the line the import stopped on and see which account the dump names. The count shows how many different definers you have to deal with.
grep -o 'DEFINER=`[^`]*`@`[^`]*`' dump.sql | sort | uniq -c2Match what you see, then jump to its fix
- 1
The error comes at a CREATE VIEW, CREATE TRIGGER or CREATE PROCEDURE line that contains DEFINER=...The dump sets a DEFINER that is not the account running the importFix - 2
Tables import fine; the failure starts at the first stored routine, trigger or eventRoutines, triggers and events are part of a direct database-to-database pipeFix
- 1
What does the error look like?
Access denied; you need (at least one of) the SUPER privilege(s) for this operationAccess denied; you need (at least one of) the SUPER privilege(s) for this DEFINER operation
Cause 1 of 2
The dump sets a DEFINER that is not the account running the import
1Check
The MySQL manual says that without the SET_ANY_DEFINER privilege the only permitted definer is your own account, written literally or as CURRENT_USER. The RDS master user's privilege list has no SUPER and no SET_ANY_DEFINER, so any other definer in the dump is refused. Without a DEFINER clause the default definer is the user who runs the statement.
SHOW GRANTS FOR CURRENT_USER();2Fix
Strip the clause while streaming the dump into RDS, so every object is owned by the master user. Then check the result in a copy first, because objects whose body relies on the old owner's privileges will run with the master user's.
sed -E 's/[[:space:]]DEFINER=`[^`]+`@`[^`]+`//g' dump.sql > dump-nodefiner.sql
mysql -h mydb.xxxx.us-east-1.rds.amazonaws.com -u admin -p < dump-nodefiner.sql3Verify
The import now runs to the end, and the definer of each object is the master user.
SELECT table_name, definer FROM information_schema.views WHERE table_schema = 'app';Confirmed by dev.mysql.com
Cause 2 of 2
Routines, triggers and events are part of a direct database-to-database pipe
1Check
Amazon's import guide for RDS for MySQL tells you to leave these objects out of the dump and create them again by hand in the RDS database. Your mysqldump command line shows whether you included them (--routines, --triggers, --events).
mysqldump --help | grep -E 'routines|triggers|events'2Fix
Dump without them, then run their CREATE statements as the master user.
mysqldump -u local_user --databases app --single-transaction \
--routines=0 --triggers=0 --events=0 -p | \
mysql -h mydb.xxxx.us-east-1.rds.amazonaws.com -u admin -p3Verify
List the routines after you created them again.
SHOW PROCEDURE STATUS WHERE Db = 'app';Confirmed by docs.aws.amazon.com
How do I stop it coming back?
- Create views and routines in development with the same account that will own them in RDS.
- Keep the dump step and the definer clean-up in one script, so nobody imports a raw dump.
- Import into a throwaway RDS instance first when the dump comes from a hosting panel.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
A view, procedure or trigger names a DEFINER account that this server does not have
SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('app'@'%') does not exist
Views, stored routines, triggers and events remember the account that created them.
4 causes, each with a fixDatabases
Creating a trigger or function on RDS fails because binary logging is on
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled
RDS never gives you the SUPER privilege, and MySQL asks for it when binary logging is on and you create a trigger or stored function.
2 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fix