MySQL Bugs: #24556: mysqldump --all-databases does not dump all databases

Bug #24556	mysqldump --all-databases does not dump all databases
Submitted:	23 Nov 2006 20:05	Modified:	5 Dec 2006 22:51
Reporter:	Martijn Wenke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Windows (Win 2003 server)
Assigned to:		CPU Architecture:	Any
Tags:	--all-databases, mysqldump, windows 2003

Description:
 Dear Sir, Miss,

I tried to setup a scheduled MySQL backup with MySQLDump, but this tool does not always dump all the databases.

- I have MySQL version 5.0.27
- I use SQLyog 5.19 to administer the database
- Platform is an up to date Win 2003
- running on a (slow) test machine: Compaq Deskpro 800Mhz

The Windows 2003 scheduler runs a cmd file that contains this lines:

[begin]
set backuplog=C:\scripts\Log\mysql_backup_log.txt
SET dumpfile=D:\Backup\MySQL_DB\all-databases.sql
...
mysqldump -uroot -pxs2mysql --all-databases >> %dumpfile%
...
ECHO --- Checking dump file for databases:>> %backuplog%
ECHO. >> %backuplog%
findstr /C:"USE `" %dumpfile%>> %backuplog%
[end]

MySQL contains several small databases. Most of the times it only dumps one or two of them.

I added some lines to this script (before the dump command) for checking the databases:

[check commands added]
...
REM --- Repairing databases

ECHO. >> %backuplog%
ECHO ** Repairing MySQL databases... >> %backuplog%
ECHO. >> %backuplog%

mysqlcheck -uroot -pxs2mysql --auto-repair --extended --all-databases >> %backuplog%

REM --- Optimizing databases

ECHO. >> %backuplog%
ECHO ** Optimizing MySQL databases... >> %backuplog%
ECHO. >> %backuplog%

mysqlcheck -uroot -pxs2mysql --optimize --all-databases >> %backuplog%
...
[end of check commands]

When I look at the logfile I see all te tables and databases are checked en optimized. But still not a complete dump.
For example trade and reservering are missing in the dump. In the example below you see only decision and joomla are in the dump file.
These are recently created for the CMS (test) sites.

A ran this script to do a manual check and found no problems:

[check begin]
mysqlcheck -uroot -pxs2mysql --check-upgrade --all-databases
pause

mysqlcheck -uroot -pxs2mysql --auto-repair --extended --all-databases
pause
[check end]

Please, any suggestions?

Regards,
Martijn

-------- Example of the logfile -------

[log file]
** Repairing MySQL databases...

decision.jos_banner OK
decision.jos_bannerclient OK
decision.jos_bannerfinish OK
...
joomla.jos_banner OK
joomla.jos_bannerclient OK
joomla.jos_bannerfinish OK
...
trade.categories OK
trade.cms_items OK
trade.objects OK
trade.photos OK
trade.users OK
mysql.columns_priv OK
mysql.db OK
mysql.func OK
mysql.help_category OK
...
mysql.user OK
reservering.bellacasa_reserveringen OK
reservering.bellacasa_settings OK
reservering.bellacasa_tijdvakken OK
reservering.bellacasa_users OK

** Optimizing MySQL databases...

decision.jos_banner Table is already up to date
decision.jos_bannerclient Table is already up to date
...
reservering.bellacasa_tijdvakken Table is already up to date
reservering.bellacasa_users Table is already up to date

--- Checking dump file for databases:

USE `decision`;
USE `joomla`;
[end of log file]

How to repeat:
I can repeat this problem, just by running my script file. Sometimes it dumps 2 databases, sometimes 4 databases, etc.

I tried several switches of mysqldump including:

- debug option: this one is not accepted. I tried the debug option but I get this error: Option 'debug' used, but is disabled

- option to flush logs: without a positive result.

Thank you for a problem report. Please, send SHOW PROCESSLIST results next time when you are dumping all the databases. Are you sure no other users are working with databases when you are making a dump?

Is it a fresh install of 5.0.27 or you had different version before? Had you installed MySQL server as a member of local Administrators group on Windows? (Error 13 is "Permission denied", hence the question.)

>Is it a fresh install of 5.0.27 or you had different version before?

I don't know exacty I installed the MySQL softare at the first time. I had an older version running.
Some time ago I removed the new version and installed it again. I exported the tables with SQLYog and imported them in the fresh installed MySQL.

Sometimes I think the server is a little bit slow: for example a takes some time after closing an application a file is "unlocked" so I can delete it.

Is there a way to get verbose/debug info in a log file?

> Had
>you installed MySQL server as a member of local Administrators group on
>Windows?

Yes, I used an admin account

It look' s McAfee VirusScan Enterprise 8 causes the problems.

Maybe it locks (during scanning) some temporary files while mysqldump tries to edit/open or delete them.

I turned of this setting of the "On Access scanner":
"When reading from disk"
So the real time scanner, scans files during tests only by this way:
"When writing to disk"

During the tests all backups/dumps are 100% OK

Used McAfee scan engine: 5100
Patch level: 14

Closed as not a result of a bug in MySQL's code.

Last time the scheduled backup script was running the backup was not OK again!
Part of the log file (generated by the script):

"** Logging MySQL processes... 
 
*************************** 1. row ***************************
     Id: 3
   User: root
   Host: localhost:1381
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: SHOW FULL PROCESSLIST
 
** Creating backup using mysqldump ... 
 
Dump finished - checking D:\Backup\MySQL_DB\all-databases.sql ...
 
OK, dump file is found! 
 
 
** Analyzing dump file D:\Backup\MySQL_DB\all-databases.sql ...
 
--- Checking dump file for databases:
 
USE `decision`;
USE `joomla`;
 
--- Checking dump file for completion time:
 
 
 
** Show all databases in MySQL system ...
 
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: decision
*************************** 3. row ***************************
Database: joomla
*************************** 4. row ***************************
Database: margarita-trade
*************************** 5. row ***************************
Database: mysql
*************************** 6. row ***************************
Database: reservering
*************************** 7. row ***************************
Database: test"

You can see mysqldump does skip some databases: margarita-trade, mysql, reservering & test.

So can anyone tell me how to log verbose/debug information to the logfile?

Regards,
Martijn