August 17, 2025

Fixing MySQL DEFINER Errors on AWS RDS (Trigger Import)

When importing a MySQL dump into Amazon RDS, you might hit an error like:

ERROR 1227 (42000) at line 1278: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

1 2	ERROR 1227 (42000) at line 1278: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

In the dump you’ll often see a trigger, view, or routine created with a DEFINER that doesn’t match your current user, for example:

/*!50017 DEFINER=`dx_stage_db_user`@`%`*/ /*!50003 TRIGGER before_insert_tbl_user
BEFORE INSERT ON tbl_user
FOR EACH ROW
BEGIN
  DECLARE max_serial INT;
  SELECT IFNULL(MAX(WWID), 0) + 1 INTO max_serial FROM tbl_user;
  SET NEW.WWID = max_serial;
END */

/*!50017 DEFINER=`dx_stage_db_user`@`%`*/ /*!50003 TRIGGER before_insert_tbl_user

BEFORE INSERT ON tbl_user

FOR EACH ROW

BEGIN

DECLARE max_serial INT;

SELECT IFNULL(MAX(WWID), 0) + 1 INTO max_serial FROM tbl_user;

SET NEW.WWID = max_serial;

END */

On RDS you typically don’t have SUPER (legacy) or SET_USER_ID (modern) privileges, so MySQL refuses to create the object with someone else’s DEFINER.

TL;DR (the quick, safe fix)

Strip the DEFINER=... clauses from the dump so MySQL defaults to the importing user.

# 1) Make a copy
cp db.sql db.nodef.sql

# 2) Remove all DEFINER=... (handles triggers, views, routines, events)
sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`//g' db.nodef.sql

# 3) Import the cleaned file
mysql -h $DB_HOST -u $DB_USER -p $DB_NAME < db.nodef.sql

# 1) Make a copy

cp db.sql db.nodef.sql

# 2) Remove all DEFINER=... (handles triggers, views, routines, events)

sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`//g' db.nodef.sql

# 3) Import the cleaned file

mysql -h $DB_HOST -u $DB_USER -p $DB_NAME < db.nodef.sql

Notes

The versioned comments like /*!50017 ... */ are fine to keep. Removing only the DEFINER=... text is enough.
On macOS, use sed -E -i '' 's/.../.../g' file.sql (empty string after -i).

Alternative: keep an explicit definer (without elevated privileges)

If you prefer to keep an explicit definer, rewrite to CURRENT_USER:

sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' db.sql

1 2	sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' db.sql

CURRENT_USER is allowed in object definers and avoids the privilege requirement.

Why this happens (in one minute)

A DEFINER tells MySQL which account owns and executes the object (trigger/view/routine) with its privileges.
Creating an object owned by someone else requires SET_USER_ID (or historically SUPER).
Managed services like Amazon RDS generally restrict those privileges for safety, so imports with hard-coded definers fail.

Best practices for creating dumps

Avoid shipping other people’s definers in the first place.

If you can use mysqlpump:

mysqlpump --skip-definer --routines --triggers --databases yourdb > dump.sql

1
2

mysqlpump --skip-definer --routines --triggers --databases yourdb > dump.sql
If you’re using mysqldump, there isn’t a native --skip-definer flag. Pipe through sed during dump or import:

mysqldump --routines --triggers yourdb \ | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' \ > dump.nodef.sql

1
2
3
4

mysqldump --routines --triggers yourdb \
| sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' \
> dump.nodef.sql

Tip: If your source has multiple databases or mixed object types (views, triggers, procedures, events), a single sed pattern like the one above usually covers them all.

Verify after import

Confirm that the objects were created and now belong to your importing user.

-- Triggers
SHOW TRIGGERS LIKE 'tbl_user'\G

-- Routines
SELECT ROUTINE_NAME, ROUTINE_TYPE, DEFINER
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE();

-- Views
SELECT TABLE_NAME, DEFINER
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

-- Triggers

SHOW TRIGGERS LIKE 'tbl_user'\G

-- Routines

SELECT ROUTINE_NAME, ROUTINE_TYPE, DEFINER

FROM information_schema.ROUTINES

WHERE ROUTINE_SCHEMA = DATABASE();

-- Views

SELECT TABLE_NAME, DEFINER

FROM information_schema.VIEWS

WHERE TABLE_SCHEMA = DATABASE();

You should see DEFINER set to the current account (or omitted in SHOW TRIGGERS, depending on version).

About that sample trigger (race condition warning)

The example trigger sets WWID using MAX(WWID)+1. This pattern is subject to race conditions under concurrency (two inserts can compute the same next value). Prefer:

Make WWID an AUTO_INCREMENT column with a unique index, or
Use a dedicated sequence/allocator table with INSERT ... ON DUPLICATE KEY UPDATE semantics.

This isn’t required to complete the import—but it’s a reliability improvement worth planning.

Edge cases & troubleshooting

Different quoting: If your dump uses 'user'@'host' (single quotes) instead of backticks, broaden the regex:

sed -E -i "s/DEFINER=(`[^`]+`@`[^`]+`|'[^']+'@'[^']+')//g" dump.sql

1
2

sed -E -i "s/DEFINER=(`[^`]+`@`[^`]+`|'[^']+'@'[^']+')//g" dump.sql
Aurora MySQL vs. RDS MySQL: Both restrict SUPER; some engines/versions gate SET_USER_ID similarly. Stripping or CURRENT_USER is still the most portable approach.
Security model: If you truly need objects to run with elevated rights, re-think the design (e.g., narrower privileges, role-based grants) rather than relying on privileged definers in managed platforms.

Reusable command block

Drop this into your runbook for future imports:

DB_HOST="db.example.cluster-xxxx.us-east-1.rds.amazonaws.com"
DB_USER="import_user"
DB_NAME="appdb"
DUMP="dump.sql"

cp "$DUMP" "${DUMP%.sql}.nodef.sql"
sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`//g' "${DUMP%.sql}.nodef.sql"

mysql -h "$DB_HOST" -u "$DB_USER" -p "$DB_NAME" < "${DUMP%.sql}.nodef.sql"

DB_HOST="db.example.cluster-xxxx.us-east-1.rds.amazonaws.com"

DB_USER="import_user"

DB_NAME="appdb"

DUMP="dump.sql"

cp "$DUMP" "${DUMP%.sql}.nodef.sql"

sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`//g' "${DUMP%.sql}.nodef.sql"

mysql -h "$DB_HOST" -u "$DB_USER" -p "$DB_NAME" < "${DUMP%.sql}.nodef.sql"

Summary

RDS blocks creating objects owned by someone else; hard-coded DEFINER values trigger ERROR 1227.
Fix: strip the DEFINER or rewrite it to CURRENT_USER before importing.
Prevent: create dumps without definers, or sanitize them in-flight.
Improve: avoid MAX()+1 triggers; prefer AUTO_INCREMENT or proper sequencing.