12

I'm using mysqldump to share databases dumps, but i'm having an issue with triggers. The command does not add "drop" or "replace" lines with the triggers, making people who already added a previous dump with same triggers names having an error :

ERROR 1359 (HY000) at line 1420: Trigger already exists

I've read on forums people saying it's a missing feature for mysql, but all posts are old and i'm wondering if there could be a way to do it now.

I know there is a way to dump schema and data separately, but i'd like to keep a single dump to share.

Jean-Loup Becquet
  • 183
  • 1
  • 2
  • 11
  • 1
    One way is to use the rhinoceros method:manually edit the dump and add IF EXISTS where you find CREATE TRIGGER syntax – Mihai Dec 22 '16 at 10:57

2 Answers2

29

There's a mysqldump option --skip-triggers you should use that to skip triggers.

hestajoe
  • 405
  • 4
  • 8
0

You don't specify version. mysqldump 5.6 and above has --add-drop-trigger option.

Bob Grandys
  • 162
  • 3
  • You are right, i'm using mysql 5.7 and the option seems to define what i need, but i'm still having the same error when i use it – Jean-Loup Becquet Dec 22 '16 at 14:54
  • @Jean-LoupBecquet that doesn't add up. It could, however, potentially be explained if you had previously used the same trigger *name* but on a different table. Trigger *names* must be unique across all tables within a single schema -- i.e. you can't have triggers with the same name on both tables t1 and t2 and mysqldump can't anticipate what might exist on the target. – Michael - sqlbot Dec 22 '16 at 18:59