Bug #44848 mysqldump produces stdout even if it encouters errors
Submitted: 13 May 2009 15:51 Modified: 12 Mar 2010 4:38
Reporter: Jozef Slezacek Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S1 (Critical)
Version:5.0.77, 5.1.45 OS:Any
Assigned to: CPU Architecture:Any
Tags: Backup, mysqldump, stdout
Triage: Triaged: D5 (Feature request)

[13 May 2009 15:51] Jozef Slezacek
Description:
If mysqldump is ran and it encounters an error, it sends a default header information to stdout instead of only producing stderr and exiting.

This causes a problem when you write dumps to the same file, that a corrupt/incomplete dump will be overwriting the old (good) backup.

How to repeat:

USE TEST;

CREATE TABLE `merge1` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `merge2` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `merge` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL
) ENGINE=MERGE UNION=(merge1,merge2) DEFAULT CHARSET=latin1;

insert into merge1 values(1,'test1');
insert into merge1 values(2,'test2');

mysqldump -u root test > merge.dump
The dump is fine.

I break the merge table now:
alter table merge2 add column broken int;

mysqldump -u root test > merge.dump
mysqldump: Got error: 1168: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist when using LOCK TABLES

In this case, the dump output is overwritten with the following header:

-- MySQL dump 10.11
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.77-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Suggested fix:
Expected behaviour:
Don't send any stdout, only stderr and exit if a complete dump is not possible.
[13 May 2009 16:45] Valeriy Kravchuk
And what if the problematic table will be last of 1000 others that will be dumped successfully? Where mysqldump should put a dump of other 999 tables?

You just should not overwrite existing dump until you are sure that the new one is successful. Actually, you'd better keep 2 last good copies at least.

I think that change of behavior you ask about will help only in rare cases, when the very first (or only) table to dump is problematic.
[13 Jun 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[12 Mar 2010 4:14] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=51975 was marked as duplicate of this one.
[12 Mar 2010 4:38] Valeriy Kravchuk
It is easy to verify (see that duplicate). Just run 

mysqldump -user-dsdhshhs >/dev/null
mysqldump -user-fnjfdnfd 2>/dev/null

to make sure that usage messages (as well as --help option results) goes to stdout, NOT stderr as expected.