Bug #21215 mysqldump creating incomplete backups without warning
Submitted: 21 Jul 2006 14:59 Modified: 15 Aug 2006 3:12
Reporter: Kristian Koehntopp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:mysqldump Ver 10.10 Distrib 5.0.22 OS:unknown-linux-gnu (x86_64)
Assigned to: Magnus Blåudd CPU Architecture:Any
Tags: Backup, mysqldump

[21 Jul 2006 14:59] Kristian Koehntopp
Description:
When mysqldump is used to create backups and the --master-data option is being used, a number of privs is needed for the operation to succeed.

If these privs are missing, the backup is not created, and no indication of the problem is giving in the exitcode.

A backup script at a customer site had this problem. Exitcode was checked properly, but since the exitcode was not set properly by mysqldump, the error was not detected for a long time - the customer had no backup.

Using --force makes no difference - the backup is still truncated.

How to repeat:
nvag-blade0610:/export/home/scripts/mysql # mysqldump --master-data -u backup -ppukcab -h $HOSTNAME -B nm_chfr_prod
-- MySQL dump 10.10
--
-- Host: nvag-blade0610    Database: nm_chfr_prod
-- ------------------------------------------------------
-- Server version       5.0.22-pro-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 */;
mysqldump: mysqldump: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation (1227)
mysqldump: Error: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

nvag-blade0610:/export/home/scripts/mysql # echo $?
0

Suggested fix:
1. Make sure that mysqldump NEVER EVER generates an exitcode of E_SUCCESS (0) when the dump is truncated.
2. When --force is used, make a best effort dump, but still indicate that  problems existed in the exitcode.
[24 Jul 2006 11:10] Bugs System
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/9485
[3 Aug 2006 14:47] Bugs System
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/10013

ChangeSet@1.2238, 2006-08-03 16:47:24+02:00, msvensson@neptunus.(none) +2 -0
  Make test case for bug#21215repeateble by calling "reset master"
[3 Aug 2006 16:42] Magnus Blåudd
Pushed to 5.0.25
[3 Aug 2006 18:10] Paul DuBois
Noted in 5.0.25 changelog.

The --master-data option for mysqldump requires certain privileges,
but mysqldump generated a truncated dump file without producing 
an appropriate error message or exit status if the invoking user did not
have those privileges.
[14 Aug 2006 21:19] Konstantin Osipov
Merged into 5.1.12
[15 Aug 2006 3:12] Paul DuBois
Noted in 5.1.12 changelog.