MySQL Bugs: #45740: mysqldump doesn't dump general_log and slow

Bug #45740	mysqldump doesn't dump general_log and slow_query causes restore problem
Submitted:	25 Jun 2009 10:13	Modified:	15 Oct 2012 14:09
Reporter:	Roger David Nay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.1.31, 5.1 bzr	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
A fix for http://bugs.mysql.com/bug.php?id=26121 has an unwanted side-effect. Turning off mysqldump for the general_log has created a problem when dumping and restoring. 

After restoring the tables don't exist and if you have turned on logging to TABLES in the mean time it starts creating errors in the log file.

Requires turning on logging "after the mysqldump" in the configuration files and dropping the mysql database (probably doesn't happen that often).

How to repeat:
* install version 5.1
* mysqldump --all-databases

look at the dump file the general_log, slow_log tables (create statements) are not dumped

* turn on logging to tables in my.cnf
* restart server
* drop mysql database
* import the previous dump
* errors in log because the tables don't exist

090625 11:31:38 [ERROR] Failed to write to mysql.general_log: Table 'mysql.general_log' doesn't exist
090625 11:31:38 [ERROR] Failed to write to mysql.general_log: Table 'mysql.general_log' doesn't exist
090625 11:31:38 [ERROR] Failed to write to mysql.general_log: Table 'mysql.general_log' doesn't exist
090625 11:31:40 [ERROR] Failed to write to mysql.general_log: Table 'mysql.general_log' doesn't exist

Suggested fix:
Would be nice if the CREATE TABLE statements for the general_log and slow_log tables in there even if the data is not dumped, possibly IF NOT EXISTS so you don't lose any data in the tables (seeing as they are not dumped).

*** Put some information in the manual about what ever changes are made (even if nothing is changed), there is nothing about these tables not being dumped on the mysqldump or general/slow log manual pages.

Thank you for the report.

Verified as described.

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/129074

Pushed into mysql-5.5 5.5.10 (revid:john.embretsen@oracle.com-20110118120417-bu3cpzne4myzmmvj) (version source revid:john.embretsen@oracle.com-20110118120417-bu3cpzne4myzmmvj) (merge vers: 5.5.10) (pib:24)

Pushed into mysql-trunk 5.6.2 (revid:john.embretsen@oracle.com-20110118130935-dfwm3owvzej26cif) (version source revid:john.embretsen@oracle.com-20110118130935-dfwm3owvzej26cif) (merge vers: 5.6.2) (pib:24)

Ignore commit messages of January 18. The commit message contained a wrong bug number, commits were not related to this bug.

the commit http://lists.mysql.com/commits/129074 is not for this bug.
What's the status of it?
The problem remains in the latest version of 5.5.20

Noted in 5.1.64, 5.5.25, 5.6.6 changelogs.

When dumping the mysql database, mysqldump did not include the
general_log and slow_query_log tables because they cannot be locked.
This caused a problem after reloading the dump file if that file
contained a DROP DATABASE statement for the mysql database: The
database no longer contained the log tables and attempts to log to
them failed. Now mysqldump includes statements to re-create the
general_log and slow_query_log tables so that they exist after
loading the dump file. Log table contents still are not dumped.