Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 544
Stephen Swensen Avatar asked Oct 22 '10 21:10

Stephen Swensen


2 Answers

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.

like image 170
ducin Avatar answered Sep 19 '22 02:09

ducin


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

like image 27
emre Avatar answered Sep 19 '22 02:09

emre