Bug #11754178 45740: MYSQLDUMP DOESN'T DUMP GENERAL_LOG AND SLOW_QUERY

                     CAUSES RESTORE PROBLEM
Problem Statement:
------------------
mysqldump is not having the dump stmts for general_log and slow_log
tables. That is because of the fix for Bug#26121. Hence, after 
dropping the mysql database, and applying the dump by enabling the 
logging, "'general_log' table not found" errors are logged into the 
server log file.

Analysis:
---------
As part of the fix for Bug#26121, we skipped the dumping of tables 
for general_log and slow_log, because the data dump of those tables 
are taking LOCKS, which is not allowed for log tables.

Fix:
----
We came up with an approach that instead of taking both meta data 
and data dump information for those tables, take only the meta data 
dump which doesn't need LOCKS.
As part of fixing the issue we came up with below algorithm.
Design before fix:
1) mysql database is having tables like db, event,... general_log,
   ... slow_log...
2) Skip general_log and slow_log while preparing the tables list
3) Take the TL_READ lock on tables which are present in the table 
   list and do 'show create table'.
4) Release the lock.

Design with the fix:
1) mysql database is having tables like db, event,... general_log,
   ... slow_log...
2) Skip general_log and slow_log while preparing the tables list
3) Explicitly call the 'show create table' for general_log and 
   slow_log
3) Take the TL_READ lock on tables which are present in the table 
   list and do 'show create table'.
4) Release the lock.

While taking the meta data dump for general_log and slow_log the 
"CREATE TABLE" is replaced with "CREATE TABLE IF NOT EXISTS". 
This is because we skipped "DROP TABLE" for those tables, 
"DROP TABLE" fails for these tables if logging is enabled. 
Customer is applying the dump by enabling logging so, if the dump 
has "DROP TABLE" it will fail. Hence, removed the "DROP TABLE" 
stmts for those tables.
  
After the fix we could observe "Table 'mysql.general_log' 
doesn't exist" errors initially that is because in the customer 
scenario they are dropping the mysql database by enabling the 
logging, Hence, those errors are expected. Once we apply the 
dump which is taken before the "drop database mysql", the errors 
will not be there.

2 files changed, 68 insertions, 3 deletions

diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 1d4a5783c3f..fac94e855a4 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -5067,5 +5067,47 @@ RETURN CONCAT(']]]]><![CDATA[>, ', s, '!')
 DROP DATABASE BUG52792;
 USE test;
 #
+# Bug#45740 MYSQLDUMP DOESN'T DUMP GENERAL_LOG AND SLOW_QUERY CAUSES RESTORE PROBLEM
+#
+SET @old_log_output_state=       @@global.log_output;
+SET @old_general_log_state=      @@global.general_log;
+SET @old_slow_query_log_state=   @@global.slow_query_log;
+call mtr.add_suppression("Failed to write to mysql.general_log");
+SET @@global.log_output="TABLE";
+SET @@global.general_log='ON';
+SET @@global.slow_query_log='ON';
+DROP DATABASE mysql;
+Warnings:
+Error	1146	Table 'mysql.proc' doesn't exist
+Error	1146	Table 'mysql.event' doesn't exist
+SHOW CREATE TABLE mysql.general_log;
+Table	Create Table
+general_log	CREATE TABLE `general_log` (
+  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `user_host` mediumtext NOT NULL,
+  `thread_id` int(11) NOT NULL,
+  `server_id` int(10) unsigned NOT NULL,
+  `command_type` varchar(64) NOT NULL,
+  `argument` mediumtext NOT NULL
+) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
+SHOW CREATE TABLE mysql.slow_log;
+Table	Create Table
+slow_log	CREATE TABLE `slow_log` (
+  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `user_host` mediumtext NOT NULL,
+  `query_time` time NOT NULL,
+  `lock_time` time NOT NULL,
+  `rows_sent` int(11) NOT NULL,
+  `rows_examined` int(11) NOT NULL,
+  `db` varchar(512) NOT NULL,
+  `last_insert_id` int(11) NOT NULL,
+  `insert_id` int(11) NOT NULL,
+  `server_id` int(10) unsigned NOT NULL,
+  `sql_text` mediumtext NOT NULL
+) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
+SET @@global.log_output=       @old_log_output_state;
+SET @@global.slow_query_log=   @old_slow_query_log_state;
+SET @@global.general_log=      @old_general_log_state;
+#
 # End of 5.1 tests
 #
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 20c788e3b9a..7393ca88f0a 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -2308,9 +2308,32 @@ DROP DATABASE BUG52792;
 
 USE test;
 
+# Wait till we reached the initial number of concurrent sessions
+--source include/wait_until_count_sessions.inc
+
 --echo #
---echo # End of 5.1 tests
+--echo # Bug#45740 MYSQLDUMP DOESN'T DUMP GENERAL_LOG AND SLOW_QUERY CAUSES RESTORE PROBLEM
 --echo #
+SET @old_log_output_state=       @@global.log_output;
+SET @old_general_log_state=      @@global.general_log;
+SET @old_slow_query_log_state=   @@global.slow_query_log;
 
-# Wait till we reached the initial number of concurrent sessions
---source include/wait_until_count_sessions.inc
+call mtr.add_suppression("Failed to write to mysql.general_log");
+--exec $MYSQL_DUMP -uroot --all-databases > $MYSQLTEST_VARDIR/tmp/bug45740.sql
+# Make log_output as table and enable general_log and slow_log
+SET @@global.log_output="TABLE";
+SET @@global.general_log='ON';
+SET @@global.slow_query_log='ON';
+DROP DATABASE mysql; 
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug45740.sql
+SHOW CREATE TABLE mysql.general_log;
+SHOW CREATE TABLE mysql.slow_log;
+--remove_file $MYSQLTEST_VARDIR/tmp/bug45740.sql
+
+SET @@global.log_output=       @old_log_output_state;
+SET @@global.slow_query_log=   @old_slow_query_log_state;
+SET @@global.general_log=      @old_general_log_state;
+ 
+--echo #
+--echo # End of 5.1 tests
+--echo #