MySQL Bugs: #34861: mysqldump with --tab gives weird output for triggers.

Bug #34861	mysqldump with --tab gives weird output for triggers.
Submitted:	26 Feb 2008 20:42	Modified:	21 Jul 2009 19:00
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.1.23	OS:	Any
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
mysqldump dumps trigger creations to the terminal when running with the --tab option on databases with triggers defined.

How to repeat:
mysql> use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TRIGGER trg1 BEFORE UPDATE ON City FOR EACH ROW SET @a = 1;
Query OK, 0 rows affected (0.02 sec)

mysql> \q
Bye
flupps@flupps:~$ mysqldump --tab=/tmp world

/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;

DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`flupps`@`localhost` */ /*!50003 TRIGGER `trg1` BEFORE UPDATE ON `City` FOR EACH ROW SET @a = 1 */;;

DELIMITER ;
/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
flupps@flupps:~$ 

Suggested fix:
Are we supposed to redirect this into a separate .sql file ourselves? Just feels weird that we use the terminal for something like this.

Thank you for the report.

Verified as described.

Same behavior for routines.

Bug #44730 was marked as duplicate of this one.

--echo
--echo Bug #34861 - mysqldump with --tab gives weird output for triggers.
--echo

CREATE TABLE t1 (f1 INT);
CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW SET @f1 = 1;
CREATE PROCEDURE pr1 () SELECT "Meow";
CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SELECT "Meow";

--echo
SHOW TRIGGERS;
SHOW EVENTS;
SELECT name,body FROM mysql.proc WHERE NAME = 'pr1';

--echo
--echo dump table; if anything goes to stdout, it ends up here: ---------------
--exec $MYSQL_DUMP --compact --routines --triggers --events --result-file=$MYSQLTEST_VARDIR/tmp/test_34861.sql --tab=$MYSQLTEST_VARDIR/tmp/ test

--echo
--echo drop everything
DROP EVENT ev1;
DROP TRIGGER tr1;
DROP TABLE t1;
DROP PROCEDURE pr1;

--echo
--echo reload table; this should restore table and, arguably, trigger
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/t1.sql
SHOW TRIGGERS;
SHOW EVENTS;
SELECT name,body FROM mysql.proc WHERE NAME = 'pr1';

--echo
--echo reload db; this should restore routines and events (and trigger?)
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/test_34861.sql
SHOW TRIGGERS;
SHOW EVENTS;
SELECT name,body FROM mysql.proc WHERE NAME = 'pr1';

--echo
--echo cleanup
--remove_file $MYSQLTEST_VARDIR/tmp/t1.sql
--remove_file $MYSQLTEST_VARDIR/tmp/test_34861.sql
DROP EVENT ev1;
DROP PROCEDURE pr1;
DROP TRIGGER tr1;
DROP TABLE t1;

Behavior for --routines is correct; they pertain to a database, not a table, and can therefore not be dumped in conjunction with a "corresponding table."

Likewise for --events.

Behavior for --triggers is debatable.

You do not need to redirect stdout ("mysqldump ... > my_db.sql") if you find that to look awkward; the alternative syntax is "mysqldump ... --result-file=my_db.sql" (or "-r" for short).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75052

2912 Tatiana A. Nurnberg	2009-05-27
      Bug#34861: mysqldump with --tab gives weird output for triggers.
      
      mysqldump --tab still dumped triggers to stdout rather than to
      individual tables.
      
      We now append triggers to the .sql file for the corresponding
      table.
      
      --events and --routines correspond to a database rather than a
      table and will still go to stdout with --tab unless redirected
      with --result-file (-r).
     @ client/mysqldump.c
        Extend open_sql_file_for_table() so we can open-append.
        Change dump_triggers_for_table() so it will append its
        output to the table's .sql-file when --tab is used.
     @ mysql-test/r/mysqldump.result
        Show that when using --tab, triggers now end up in the .sql
        file with the corresponding table (after that table), while
        --routines and --events go to stdout or --result-file.
     @ mysql-test/t/mysqldump.test
        Show that when using --tab, triggers now end up in the .sql
        file with the corresponding table (after that table), while
        --routines and --events go to stdout or --result-file.

queued for 5.1.36, 6.0.12 in bugteam

Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:azundris@mysql.com-20090527131621-wwbf75dmilf15beb) (merge vers: 5.1.36) (pib:6)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:azundris@mysql.com-20090529162230-vl0jt0typh9fc98d) (merge vers: 6.0.12-alpha) (pib:11)

Noted in 5.1.36, 5.4.4 changelogs.

mysqldump --tab dumped triggers to stdout rather than to the .sql
file for the corresponding table.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed to 5.4.2.