Reputation: 22307
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
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
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
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