CODEWITHSUNDEEP
databasemysqldatabase-backups
Stephen Swensen
Stephen Swensen

Reputation: 22307

mysqldump Fully Qualifies Triggers with Database Name

I'm am trying to use mysqldump to export a database which needs to be imported using a different database name. Looking at the SQL generated by mysqldump, it appears that triggers are the only object names which are fully-qualified with the source database name thus foiling my needs. Is there anyway to direct mysqldump to not fully-qualify any object names including triggers?

Upvotes: 5

Views: 2025

Answers (3)

ducin
ducin

Reputation: 26487

I had the same problem and I found the solution. I was using MySQL Workbench to design my database and I've created some triggers there. All of them used the syntax CREATE TRIGGER trigger_name except for one: CREATE TRIGGER dbname.trigger_name (it was just my mistake). Mysqldump output included all triggers in the same way: only one had database name.

Mysqldump uses your original CREATE TRIGGER instructions which you can see via SHOW CREATE TRIGGER. If you have a trigger defined with a database name, simply replace it (drop and create) with a one without dbname.

Upvotes: 6

Ryaner
Ryaner

Reputation: 761

Not an ideal solution but pumping the output through the following has gotten rid of the database name on the triggers for me. 

mysqldump ... opts ... | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/'

Upvotes: -1

emre
emre

Reputation: 21

Most probably you add the database name when you create the trigger. Try updating your trigger without the database name in it.

Upvotes: 2

Related Questions