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 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
Triage: Triaged: D3 (Medium)

[25 Jun 2009 10:13] Roger Nay
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.
[25 Jun 2009 20:55] Sveta Smirnova
Thank you for the report.

Verified as described.
[18 Jan 2011 12:04] 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/129074
[18 Jan 2011 13:10] Bugs System
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)
[18 Jan 2011 14:40] Bugs System
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)
[18 Jan 2011 15:21] John Embretsen
Ignore commit messages of January 18. The commit message contained a wrong bug number, commits were not related to this bug.
[3 Mar 2012 15:06] xiaobin lin
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
[20 Jun 2012 16:03] Paul Dubois
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.