• Venkata Sidagam's avatar
    Bug #11754178 45740: MYSQLDUMP DOESN'T DUMP GENERAL_LOG AND SLOW_QUERY · e7364ec2
    Venkata Sidagam authored
                         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
    e7364ec2
mysqldump.test 79.4 KB