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

Question

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.

Uranium

One way is to use the rhinoceros method:manually edit the dump and add IF EXISTS where you find CREATE TRIGGER syntax – Sheepshead

Answer 1

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

Traditional

Answer 2

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

Milestone

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 – Uranium

@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. – Bean

Answer 3

For example, using --skip-triggers, you can export the schema and data of the tables of apple database without its triggers to backup.sql as shown below. *By default, trigers are exported by implicit --triggers and my answer explains how to perfectly export databases:

mysqldump -u john -p --skip-triggers apple > backup.sql

Cordite