List:Commits« Previous MessageNext Message »
From:Venkata Sidagam Date:May 7 2012 11:33am
Subject:bzr push into mysql-5.1 branch (venkata.sidagam:3730 to 3731) Bug#26121
Bug#11754178
View as plain text  
 3731 Venkata Sidagam	2012-05-07
      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.
     @ client/mysqldump.c
        In get_table_structure() added code to skip the DROP TABLE stmts for general_log
        and slow_log tables, because when logging is enabled those stmts will fail. And
        replaced CREATE TABLE with CREATE IF NOT EXISTS for those tables, just to make 
        sure CREATE stmt for those tables doesn't fail since we removed DROP stmts for
        those tables.
        In dump_all_tables_in_db() added code to call get_table_structure() for 
        general_log and slow_log tables.
     @ mysql-test/r/mysqldump.result
        Added a test as part of fix for Bug #11754178
     @ mysql-test/t/mysqldump.test
        Added a test as part of fix for Bug #11754178

    modified:
      client/mysqldump.c
      mysql-test/r/mysqldump.result
      mysql-test/t/mysqldump.test
 3730 Yasufumi Kinoshita	2012-04-27
      Bug#11758510 (#50723): INNODB CHECK TABLE FATAL SEMAPHORE WAIT TIMEOUT POSSIBLY TOO SHORT FOR BI
      Fixed not to check timeout during the check table.

    modified:
      storage/innobase/dict/dict0load.c
      storage/innobase/include/srv0srv.h
      storage/innobase/row/row0mysql.c
      storage/innobase/sync/sync0arr.c
      storage/innodb_plugin/dict/dict0load.c
      storage/innodb_plugin/handler/ha_innodb.cc
      storage/innodb_plugin/include/srv0srv.h
      storage/innodb_plugin/sync/sync0arr.c
=== modified file 'client/mysqldump.c'
--- a/client/mysqldump.c	2012-02-28 16:20:30 +0000
+++ b/client/mysqldump.c	2012-05-07 11:16:44 +0000
@@ -84,6 +84,15 @@
 #define IGNORE_DATA 0x01 /* don't dump data for this table */
 #define IGNORE_INSERT_DELAYED 0x02 /* table doesn't support INSERT DELAYED */
 
+/* general_log or slow_log tables under mysql database */
+static inline my_bool general_log_or_slow_log_tables(const char *db, 
+                                                     const char *table)
+{
+  return (strcmp(db, "mysql") == 0) &&
+         ((strcmp(table, "general_log") == 0) ||
+          (strcmp(table, "slow_log") == 0));
+}
+
 static void add_load_option(DYNAMIC_STRING *str, const char *option,
                              const char *option_value);
 static ulong find_set(TYPELIB *lib, const char *x, uint length,
@@ -2458,6 +2467,7 @@ static uint get_table_structure(char *ta
                                 "TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'";
   FILE       *sql_file= md_result_file;
   int        len;
+  my_bool    is_log_table;
   MYSQL_RES  *result;
   MYSQL_ROW  row;
   DBUG_ENTER("get_table_structure");
@@ -2542,9 +2552,12 @@ static uint get_table_structure(char *ta
       /*
         Even if the "table" is a view, we do a DROP TABLE here.  The
         view-specific code below fills in the DROP VIEW.
+        We will skip the DROP TABLE for general_log and slow_log, since
+        those stmts will fail, in case we apply dump by enabling logging.
        */
-        fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",
-                opt_quoted_table);
+        if (!general_log_or_slow_log_tables(db, table))
+          fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",
+                  opt_quoted_table);
         check_io(sql_file);
       }
 
@@ -2656,12 +2669,25 @@ static uint get_table_structure(char *ta
 
       row= mysql_fetch_row(result);
 
-      fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" :
-              "/*!40101 SET @saved_cs_client     = @@character_set_client */;\n"
-              "/*!40101 SET character_set_client = utf8 */;\n"
-              "%s;\n"
-              "/*!40101 SET character_set_client = @saved_cs_client */;\n",
-              row[1]);
+      is_log_table= general_log_or_slow_log_tables(db, table);
+      if (is_log_table)
+        row[1]+= 13; /* strlen("CREATE TABLE ")= 13 */
+      if (opt_compatible_mode & 3)
+      {
+        fprintf(sql_file,
+                is_log_table ? "CREATE TABLE IF NOT EXISTS %s;\n" : "%s;\n",
+                row[1]);
+      }
+      else
+      {
+        fprintf(sql_file,
+                "/*!40101 SET @saved_cs_client     = @@character_set_client */;\n"
+                "/*!40101 SET character_set_client = utf8 */;\n"
+                "%s%s;\n"
+                "/*!40101 SET character_set_client = @saved_cs_client */;\n",
+                is_log_table ? "CREATE TABLE IF NOT EXISTS " : "",
+                row[1]);
+      }
 
       check_io(sql_file);
       mysql_free_result(result);
@@ -4261,6 +4287,22 @@ static int dump_all_tables_in_db(char *d
   if (opt_xml)
     print_xml_tag(md_result_file, "", "\n", "database", "name=", database, NullS);
 
+  if (strcmp(database, "mysql") == 0)
+  {
+    char table_type[NAME_LEN];
+    char ignore_flag;
+    uint num_fields;
+    num_fields= get_table_structure((char *) "general_log", 
+                                    database, table_type, &ignore_flag);
+    if (num_fields == 0)
+      verbose_msg("-- Warning: get_table_structure() failed with some internal "
+                  "error for 'general_log' table\n");
+    num_fields= get_table_structure((char *) "slow_log", 
+                                    database, table_type, &ignore_flag);
+    if (num_fields == 0)
+      verbose_msg("-- Warning: get_table_structure() failed with some internal "
+                  "error for 'slow_log' table\n");
+  }
   if (lock_tables)
   {
     DYNAMIC_STRING query;

=== modified file 'mysql-test/r/mysqldump.result'
--- a/mysql-test/r/mysqldump.result	2012-01-10 08:03:45 +0000
+++ b/mysql-test/r/mysqldump.result	2012-05-07 11:16:44 +0000
@@ -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
 #

=== modified file 'mysql-test/t/mysqldump.test'
--- a/mysql-test/t/mysqldump.test	2012-01-10 08:03:45 +0000
+++ b/mysql-test/t/mysqldump.test	2012-05-07 11:16:44 +0000
@@ -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 #

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-5.1 branch (venkata.sidagam:3730 to 3731) Bug#26121Bug#11754178Venkata Sidagam11 Jun