Bug #51167 mysqldump --tab writes some output to stdout, not files
Submitted: 13 Feb 2010 20:31 Modified: 14 Feb 2010 10:48
Reporter: Paul DuBois Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: CPU Architecture:Any

[13 Feb 2010 20:31] Paul DuBois
mysqldump with the --tab=dir_name option dumps each table using a .sql file for the table's CREATE TABLE statement and a .txt file for the table's data.  I've been looking into the effect of --tab for non-table objects and I find this:

* Views have a .sql file, no .txt file
* --triggers output goes to associated table's .sql file
* --routines output goes to stdout
* --events output goes to stdout

In other words:

* For database objects that exist in the database directory (tables, triggers, views), files are created in the --tab directory.

* For database objects that do not exist in the database directory (stored functions and procedures, events) output goes to stdout.

The problem with this is that for some database objects, no files are created in the output directory. Perhaps handling of stored  routines and procedures was simply overlooked for the case when  --tab is given.

How to repeat:
Execute mysqldump --tab for a database that contains triggers, stored functions and procedures, and events.

Suggested fix:
To facilitate reloading the *entire* contents of the database, routines and events should be written to output files, not stdout.

Some care would have to be taken not to use the same filename when  writing objects of different types with the same name, such as a table and procedure both named p1. This could be handled by adding a short "qualifier" to the file names. For example:

t.sql - table or view t
t.func.sql - stored function t
t.proc.sql - stored procedure t
t.event.sql - event t

Note: Bug#44730 and Bug#34861 report similar behavior. Apparently triggers used to go to stdout and now go to the corresponding table's .sql file. I disagree with the assessment in Bug#34861 that --routines and --events output should go to stdout because that makes it impossible to dump and reload individual objects of these types with mysqldump --tab. The proposed file-naming convention makes this possible.
[14 Feb 2010 10:48] Valeriy Kravchuk
Thank you for the problem report.
[8 Nov 2013 18:17] Sveta Smirnova
Bug #70798 was marked as duplicate of this one.