Bug #32702 Backup: DDL blocker does not block all it should
Submitted: 25 Nov 2007 18:53 Modified: 26 Mar 2009 23:15
Reporter: Chuck Bell Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Rafal Somla CPU Architecture:Any

[25 Nov 2007 18:53] Chuck Bell
Description:
The DDL blocker does not block all DDL operations. It currently blocks the following DDL commands:

* CREATE TABLE
* ALTER TABLE
* RENAME TABLE
* REPAIR TABLE
* OPTIMIZE TABLE
* DROP TABLE
* CREATE DATABASE
* DROP DATABASE
* ALTER DATABASE
* TRUNCATE TABLE

How to repeat:
N/A

Suggested fix:
Review this list and add to it the DDL commands needed to block. For example, it has been suggested that the CREATE INDEX and DROP INDEX commands need to be included.
[18 Dec 2007 0:29] Peter Gulutzan
It's safest to go, not with "DDL", but with
"schema change", that is, anything that might
change metadata.

So we have this list:

Statement              32702   Manual  Manual
                       List    "DDL"?  "Implicit
                                       Commit"?
---------              ------- ------  ---------

ALTER DATABASE         Y       Y
ALTER EVENT                            Y
ALTER FUNCTION                         Y
ALTER LOGFILE GROUP            Y
ALTER PROCEDURE                        Y
ALTER SERVER                   Y
ALTER TABLE            Y       Y       Y
ALTER TABLESPACE               Y
ALTER VIEW                             Y
CREATE DATABASE        Y       Y       Y
CREATE EVENT                           Y
CREATE FUNCTION                        Y
CREATE INDEX           ?       Y       Y
CREATE LOGFILE GROUP           Y
CREATE PROCEDURE                       Y
CREATE SERVER                  Y
CREATE TABLE           Y       Y       Y
CREATE TABLESPACE              Y
CREATE TRIGGER                         Y
CREATE USER                            Y
CREATE VIEW                            Y
DROP DATABASE                  Y       Y
DROP EVENT                             Y
DROP FUNCTION                          Y
DROP INDEX             ?       Y       Y
DROP LOGFILE GROUP             Y
DROP PROCEDURE                         Y
DROP SERVER                    Y
DROP TABLE             Y       Y       Y
DROP TABLESPACE                Y
DROP TRIGGER                           Y
DROP USER                              Y
DROP VIEW                              Y
GRANT                                  Y
INSTALL PLUGIN
OPTIMIZE TABLE         Y
RENAME TABLE           Y               Y
RENAME USER                            Y
REPAIR TABLE           Y
RESTORE
REVOKE                                 Y
SET PASSWORD                           Y
TRUNCATE TABLE         Y               Y
UNINSTALL PLUGIN

Notes:

"32702 List" column is 'Y' if the statement is in the list
in the original Bug#32702 bug description, or '?' if the
statement was noted as a possible addition for the list.

"Manual DDL" column is 'Y' is the statment is in the
manual's list of Data Definition Language statements
http://dev.mysql.com/doc/refman/6.0/en/data-definition.html

"Manual Implicit Commit" column is 'Y' if the statement is in
the manual's list of statements that cause an implicit commit
http://dev.mysql.com/doc/refman/6.0/en/implicit-commit.html

Although the 32702 List included one Table Maintenance
Statement (OPTIMIZE TABLE), I did not include the other
Table Maintenance Statements (ANALYZE, BACKUP, CHECK,
CHECKSUM, REPAIR, RESTORE).

CREATE TABLE includes CREATE TEMPORARY TABLE.
[11 Sep 2008 20:38] Chuck Bell
Clarification needed for several items on the list.

Statement              32702   Manual  Manual     
                       List    "DDL"?  "Implicit  Status of 
                                       Commit"?   DDL_blocker
---------              ------- ------  ---------  --------------
ALTER DATABASE         Y       Y                        Y
ALTER EVENT                            Y           will be added
ALTER FUNCTION                         Y           will be added
ALTER LOGFILE GROUP            Y                   will be added
ALTER PROCEDURE                        Y           will be added
ALTER SERVER                   Y                   will be added
ALTER TABLE            Y       Y       Y                Y
ALTER TABLESPACE               Y                   will be added
ALTER VIEW                             Y                Y
CREATE DATABASE        Y       Y       Y                Y 
CREATE EVENT                           Y           will be added
CREATE FUNCTION                        Y           will be added
CREATE INDEX           ?       Y       Y                Y
CREATE LOGFILE GROUP           Y                   will be added
CREATE PROCEDURE                       Y           will be added
CREATE SERVER                  Y                   will be added
CREATE TABLE           Y       Y       Y                Y
CREATE TABLESPACE              Y                   will be added
CREATE TRIGGER                         Y           will be added
CREATE USER                            Y           will be added
CREATE VIEW                            Y                Y
DROP DATABASE                  Y       Y                Y
DROP EVENT                             Y           will be added
DROP FUNCTION                          Y           will be added
DROP INDEX             ?       Y       Y                Y
DROP LOGFILE GROUP             Y                   will be added
DROP PROCEDURE                         Y           will be added
DROP SERVER                    Y                   will be added
DROP TABLE             Y       Y       Y                Y
DROP TABLESPACE                Y                   will be added
DROP TRIGGER                           Y           will be added
DROP USER                              Y           will be added
DROP VIEW                              Y           will be added
GRANT                                  Y           will be added
INSTALL PLUGIN                                *** why is this on the list? ***
OPTIMIZE TABLE         Y                                Y
RENAME TABLE           Y               Y                Y
RENAME USER                            Y           will be added
REPAIR TABLE           Y                                Y
RESTORE                                            will be added (but not
needed IMHO)
REVOKE                                 Y           will be added
SET PASSWORD                           Y      ***  why is this on the list? ***
TRUNCATE TABLE         Y               Y                Y
UNINSTALL PLUGIN                              ***  why is this on the list?***

Notes: 
* Status of DDL Blocker = 'Y' means it is currently being blocked.
[11 Sep 2008 20:41] Chuck Bell
Correction: ALTER VIEW and CREATE VIEW should read "will be added".
[26 Nov 2008 9:51] Rafal Somla
This bug will be fixed by WL#4644.
[17 Dec 2008 14:34] 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/61867

2741 Rafal Somla	2008-12-17
      BUG#32702 - Backup: DDL blocker does not block all it should
      WL#4644 - Online Backup: Fix the DDL blocker
      
      After this patch, the DDL blocker will be renamed to Backup Metadata Lock and 
      will block all the statements listed in WL#4644. Test backup_ddl_blocker will be 
      renamed to backup_bml and extended to test all these statements.
      
      This patch implements an alternative, simpler and I believe better solution than 
      the one currently described in LLD. Namely, it marks all statements which need 
      to be blocked by BML with special CF_BLOCKED_BY_BML flag. Then registering and 
      unregistering marked statements with BML is done globally in 
      mysql_execute_command(). 
      
      This makes changes in the parser code minimal, makes it easy to trace the list 
      of statements which ale blocked and easily ensures that each bml_enter() call is 
      matched with bml_leave().
[11 Feb 2009 13:12] 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/65903

2765 Rafal Somla	2009-02-11
      BUG#32702 - Backup: DDL blocker does not block all it should
      
      After this patch, the DDL blocker will be renamed to Backup Metadata Lock and 
      will block all the statements listed in WL#4644. Test backup_ddl_blocker will be 
      renamed to backup_bml and extended to test all these statements.
      
      This patch implements desing described in WL#4644. It marks all statements which 
      need to be blocked by BML with special CF_BLOCKED_BY_BML flag. Then registering 
      and unregistering marked statements with BML is done globally in 
      mysql_execute_command(). 
      
      This makes changes in the parser code minimal, makes it easy to trace the list 
      of statements which ale blocked and easily ensures that each bml_enter() call is 
      matched with bml_leave().
[11 Feb 2009 18:06] Chuck Bell
Review of revised patch begun using provided tree. Thanks, Rafal.
[12 Feb 2009 14:51] Chuck Bell
Patch approved.
[13 Feb 2009 11:58] Jørgen Løland
Patch approved.
[13 Feb 2009 13:27] 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/66200

2771 Rafal Somla	2009-02-13
      BUG#32702 - Backup: DDL blocker does not block all it should
      
      After this patch, the DDL blocker will be renamed to Backup Metadata Lock and 
      will block all the statements listed in WL#4644. Test backup_ddl_blocker will be 
      renamed to backup_bml and extended to test all these statements.
      
      This patch implements desing described in WL#4644. It marks all statements which 
      need to be blocked by BML with special CF_BLOCKED_BY_BML flag. Then registering 
      and unregistering marked statements with BML is done globally in 
      mysql_execute_command(). 
      
      This makes changes in the parser code minimal, makes it easy to trace the list 
      of statements which ale blocked and easily ensures that each bml_enter() call is 
      matched with bml_leave().
[16 Feb 2009 10:09] Rafal Somla
Pushed into team tree on 13 Feb 2009 (mysql 6.0.8)
[26 Mar 2009 12:33] Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090326121822-pt84kzxxayzho4mn) (version source revid:rafal.somla@sun.com-20090302164601-znhm4tadplfi2iqu) (merge vers: 6.0.11-alpha) (pib:6)
[26 Mar 2009 23:15] Paul Dubois
Noted in 6.0.11 changelog.

The DDL blocker for BACKUP DATABASE did not block all statements that
it should. The blocker is now called the Backup Metadata Lock and
blocks statements that change database metadata.
[14 Dec 2009 22:39] 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/94011

3005 Chuck Bell	2009-12-14
      BUG#32702 - Backup: DDL blocker does not block all it should
        
      After this patch, the DDL blocker will be renamed to 
      Backup Metadata Lock and will block all the statements 
      listed in WL#4644. Test backup_ddl_blocker will be 
      renamed to backup_bml and extended to test all these 
      statements.
        
      This patch implements desing described in WL#4644. It 
      marks all statements which need to be blocked by BML with 
      special CF_BLOCKED_BY_BML flag. Then registering 
      and unregistering marked statements with BML is done 
      globally in mysql_execute_command(). 
       
      This makes changes in the parser code minimal, makes it 
      easy to trace the list of statements which ale blocked 
      and easily ensures that each bml_enter() call is matched 
      with bml_leave().
      
      original changeset: 2599.121.10
     @ libmysqld/CMakeLists.txt
         Rename ddl_blocker.{h,cc} -> bml.{h,cc}.
     @ libmysqld/Makefile.am
         Rename ddl_blocker.{h,cc} -> bml.{h,cc}.
     @ sql/CMakeLists.txt
         Rename ddl_blocker.{h,cc} -> bml.{h,cc}.
     @ sql/Makefile.am
         Rename ddl_blocker.{h,cc} -> bml.{h,cc}.
     @ sql/bml.cc
        Rename "DDL blocker" -> "Backup Metadata Lock":
            - change class and member names.
            - update documentation.
     @ sql/bml.h
        Rename "DDL blocker" -> "Backup Metadata Lock":
            - change class and member names.
            - update documentation.