Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in AWS by (19.1k points)

So I try to import SQL file into rds (1G MEM, 1 CPU). The SQL file is like 1.4G

mysql -h xxxx.rds.amazonaws.com -u user -ppass --max-allowed-packet=33554432 db < db.sql

It got stuck at:

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

The actual SQL content is:

/*!50003 CREATE*/ /*!50017 DEFINER=`another_user`@`1.2.3.4`*/ /*!50003 TRIGGER `change_log_BINS` BEFORE INSERT ON `change_log` FOR EACH ROW

IF (NEW.created_at IS NULL OR NEW.created_at = '00-00-00 00:00:00' OR NEW.created_at = '') THEN

        SET NEW.created_at = NOW();

END IF */;;

another_user does not exist in rds, so I do:

GRANT ALL PRIVILEGES ON db.* TO another_user@'localhost';

1 Answer

0 votes
by (44.4k points)

Either remove the DEFINER=.. statement from your sqldump file or replace the user values with CURRENT_USER.

The MySQL server provided by RDS does not allow a DEFINER syntax for another user (in my experience).

You can use a sed script to get rid of them from the file:

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i previousfile.sql

You can also learn more about Amazon Web Services, on AWS Tutorial and join AWS Certification

Related questions

Want to get 50% Hike on your Salary?

Learn how we helped 50,000+ professionals like you !

+1 vote
1 answer
0 votes
1 answer

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...