MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Rafal Somla Date:November 13 2007 10:01am
Subject:Re: bk commit into 5.2 tree (cbell:1.2610)
View as plain text  
Hi Chuck,

I think the code is correct. There are small issues which I've described inlined 
in the patch. Note my comment how to modify the code so that it can be used to 
prevent multiple BACKUP/RESTORE operations to execute at the same time.

However, as it is written now, the code will cause DDL statements which are 
issued while DDL blocker is active to wait until the block is removed and then 
execute. I think it should behave differently: the DDLs issued when the DDL 
blocker is active should *not* execute and return with an error. (Lars - what do 
you think?)

In tests, you forgot to test how RESTORE + DDLs behave. I also spotted some 
other test issues which are described below. I analyzed only the first test but 
any fixes should be applied to all of them.

I have a suggestion for organization of the tests. Since you are running similar 
test several times, I suggest to write an include file with the test and provide 
parameters (i.e. the DDLs to execute) via variables set before the file is 
included. That should reduce the size of the tests significantly and make them 
easier to comprehend.

I also have a suggestion for the code organization (however, since it is a 
temporary solution, it can be safely ignored). Namely, I think that all the 4 
functions you wrote are closely related together (as they implement the DDL 
blocker) but not related to the backup system (they could be usefull in other 
contexts as well). Therefore I'd:

  - define all of them in one place in a single source file (so that a reader can
    easily see how they work together)
  - remove any reference to "backup" form their names and documentation.

For example, functions could be named:

   start_DDL_stmt(...)
   end_DDL_stmt(...)

   block_DDL(...)
   unblock_DDL(...)

cbell@stripped wrote:
> Below is the list of changes that have just been committed into a local
> 5.2 repository of cbell. When cbell does a push these changes will
> be propagated to the main repository and, within 24 hours after the
> push, to the public repository.
> For information on how to access the public repository
> see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
> 
> ChangeSet@stripped, 2007-11-08 14:15:22-05:00, cbell@mysql_cab_desk. +5 -0
>   WL#4062 : Online Backup: Metadata freeze
>   
>   This patch implements the first iteration of the metadata freeze (hence
>   DDL blocker) for the online backup project. It uses a normal pthread
>   condition variable loop to block all DDL operations while a backup is
>   running and another to block a backup if any DDL operations are running.
>   
>   Note: This patch requires the patch for WL#3324 from 1 November.
> 
>   mysql-test/r/backup_ddl_blocker.result@stripped, 2007-11-08 14:15:18-05:00,
> cbell@mysql_cab_desk. +691 -0
>     WL#4062 : Online Backup: Metadata freeze
>     
>     New result file.
> 
>   mysql-test/r/backup_ddl_blocker.result@stripped, 2007-11-08 14:15:18-05:00,
> cbell@mysql_cab_desk. +0 -0
> 
>   mysql-test/t/backup_ddl_blocker.test@stripped, 2007-11-08 14:15:19-05:00,
> cbell@mysql_cab_desk. +1235 -0
>     WL#4062 : Online Backup: Metadata freeze
>     
>     This adds a test for the DDL blocker.
> 
>   mysql-test/t/backup_ddl_blocker.test@stripped, 2007-11-08 14:15:19-05:00,
> cbell@mysql_cab_desk. +0 -0
> 
>   sql/backup/sql_backup.cc@stripped, 2007-11-08 14:15:18-05:00, cbell@mysql_cab_desk.
> +111 -5
>     WL#4062 : Online Backup: Metadata freeze
>     
>     This patch adds the methods for checking if DDL operations are running. If there
> are, 
>     the backup process is blocked until the DDL operations are complete.
> 
>   sql/mysqld.cc@stripped, 2007-11-08 14:15:16-05:00, cbell@mysql_cab_desk. +18 -0
>     WL#4062 : Online Backup: Metadata freeze
>     
>     This patch adds the mutex and condition variables for the DDL blocker.
> 
>   sql/sql_parse.cc@stripped, 2007-11-08 14:15:17-05:00, cbell@mysql_cab_desk. +111 -1
>     WL#4062 : Online Backup: Metadata freeze
>     
>     This patch adds the methods for checking the DDL blocker and blocking DDL
> operations
>     while the backup process is running.
> 


> diff -Nrup a/mysql-test/r/backup_ddl_blocker.result
> b/mysql-test/r/backup_ddl_blocker.result
> --- /dev/null	Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/r/backup_ddl_blocker.result	2007-11-08 14:15:18 -05:00
> @@ -0,0 +1,691 @@
> +DROP DATABASE IF EXISTS bup_ddl_blocker;
> +CREATE DATABASE bup_ddl_blocker;
> +
> +Starting Test 1
> +
> +DROP TABLE IF EXISTS bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +con1: Creating tables
> +CREATE TABLE bup_ddl_blocker.t2 (col_a CHAR(40)) ENGINE=MYISAM;
> +CREATE TABLE bup_ddl_blocker.t4 (col_a CHAR(40)) ENGINE=MYISAM;
> +con1: Loading data
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("03 Some data to test");
> +con1: Show that the new data doesn't exist before backup.
> +SELECT * FROM bup_ddl_blocker.t2;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker.t4;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +get_lock("DDL_in_progress", 0)
> +1
> +con2: Get a DDL going and stop in the middle
> +CREATE TABLE bup_ddl_blocker.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +con3: Get a DDL going and stop in the middle
> +ALTER TABLE bup_ddl_blocker.t2 ADD COLUMN col_b int;
> +con1: Backing up database -- will block with lock
> +BACKUP DATABASE bup_ddl_blocker TO "bup_ddl_blocker.bak";
> +con6: Checking locks
> +con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +get_lock("DDL_blocked", 0)
> +1
> +con4: Try a DDL but it is blocked by backup -- will not be in backup
> +CREATE TABLE bup_ddl_blocker.t3 (col_a CHAR(40)) ENGINE=INNODB;
> +con5: Try a DDL but it is blocked by backup -- will not be in backup
> +ALTER TABLE bup_ddl_blocker.t4 ADD COLUMN col_b int;
> +con6: Checking locks
> +con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +release_lock("DDL_blocked")
> +1
> +con2: Completing DDL
> +con3: Completing DDL
> +con4: Completing DDL
> +con5: Completing DDL
> +Backup Summary
> + header     =       36 bytes
> + meta-data  =      316 bytes
> + data       =      288 bytes
> +              --------------
> + total             640 bytes
> +
> +Verifying test 1 results:
> +
> +con1: Showing columns after updates and backup
> +DESCRIBE bup_ddl_blocker.t1;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t2;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +col_b	int(11)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t3;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t4;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +col_b	int(11)	YES		NULL	
> +con1: Dropping the database tables
> +DROP TABLE bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +Restore Summary
> + header     =       36 bytes
> + meta-data  =      316 bytes
> + data       =      288 bytes
> +              --------------
> + total             640 bytes
> +con1: Showing columns that were backed up
> +DESCRIBE bup_ddl_blocker.t1;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t2;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +col_b	int(11)	YES		NULL	
> +con1: Table t3 should not be in restored data.
> +DESCRIBE bup_ddl_blocker.t3;
> +ERROR 42S02: Table 'bup_ddl_blocker.t3' doesn't exist
> +con1: Table t4 should not have new column in restored data.
> +DESCRIBE bup_ddl_blocker.t4;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +con1: Cleanup
> +DROP TABLE bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +bup_ddl_blocker.t4;
> +
> +Starting Test 2
> +
> +DROP TABLE IF EXISTS bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +con1: Creating tables
> +CREATE TABLE bup_ddl_blocker.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker.t2 (col_a CHAR(40)) ENGINE=MYISAM;
> +CREATE TABLE bup_ddl_blocker.t3 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker.t4 (col_a CHAR(40)) ENGINE=MYISAM;
> +con1: Loading data
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("03 Some data to test");
> +con1: Show that the new data doesn't exist before backup.
> +SELECT * FROM bup_ddl_blocker.t1;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker.t2;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker.t3;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker.t4;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +get_lock("DDL_in_progress", 0)
> +1
> +con2: Get a DDL going and stop in the middle
> +RENAME TABLE bup_ddl_blocker.t1 TO bup_ddl_blocker.t01;
> +con3: Get a DDL going and stop in the middle
> +REPAIR TABLE bup_ddl_blocker.t2;
> +con1: Backing up database -- will block with lock
> +BACKUP DATABASE bup_ddl_blocker TO "bup_ddl_blocker.bak";
> +con6: Checking locks
> +con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +get_lock("DDL_blocked", 0)
> +1
> +con4: Try a DDL but it is blocked by backup -- will not be in backup
> +RENAME TABLE bup_ddl_blocker.t3 TO bup_ddl_blocker.t03;
> +con5: Try a DDL but it is blocked by backup -- will not be in backup
> +REPAIR TABLE bup_ddl_blocker.t4;
> +con6: Checking locks
> +con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +release_lock("DDL_blocked")
> +1
> +con2: Completing DDL
> +con3: Completing DDL
> +Table	Op	Msg_type	Msg_text
> +bup_ddl_blocker.t2	repair	status	OK
> +con4: Completing DDL
> +con5: Completing DDL
> +Table	Op	Msg_type	Msg_text
> +bup_ddl_blocker.t4	repair	status	OK
> +Backup Summary
> + header     =       41 bytes
> + meta-data  =      379 bytes
> + data       =      552 bytes
> +              --------------
> + total             972 bytes
> +
> +Verifying test 2 results:
> +
> +con1: Showing columns after updates and backup
> +DESCRIBE bup_ddl_blocker.t01;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t2;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t03;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t4;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +con1: Dropping the database tables
> +DROP TABLE bup_ddl_blocker.t01, bup_ddl_blocker.t2,
> +bup_ddl_blocker.t03, bup_ddl_blocker.t4;
> +con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +Restore Summary
> + header     =       41 bytes
> + meta-data  =      379 bytes
> + data       =      552 bytes
> +              --------------
> + total             972 bytes
> +con1: Showing columns that were backed up
> +con1: Table t1 should not be in restored data.
> +DESCRIBE bup_ddl_blocker.t1;
> +ERROR 42S02: Table 'bup_ddl_blocker.t1' doesn't exist
> +DESCRIBE bup_ddl_blocker.t01;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t2;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +con1: Table t03 should not be in restored data.
> +DESCRIBE bup_ddl_blocker.t03;
> +ERROR 42S02: Table 'bup_ddl_blocker.t03' doesn't exist
> +DESCRIBE bup_ddl_blocker.t3;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t4;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +con1: Cleanup
> +DROP TABLE bup_ddl_blocker.t01, bup_ddl_blocker.t2,
> +bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +
> +Starting Test 3
> +
> +DROP TABLE IF EXISTS bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +con1: Creating tables
> +CREATE TABLE bup_ddl_blocker.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker.t2 (col_a CHAR(40)) ENGINE=MYISAM;
> +CREATE TABLE bup_ddl_blocker.t3 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker.t4 (col_a CHAR(40)) ENGINE=MYISAM;
> +con1: Loading data
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("03 Some data to test");
> +con1: Show that the new data doesn't exist before backup.
> +SELECT * FROM bup_ddl_blocker.t1;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker.t2;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker.t3;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker.t4;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +get_lock("DDL_in_progress", 0)
> +1
> +con2: Get a DDL going and stop in the middle
> +OPTIMIZE TABLE bup_ddl_blocker.t1;
> +con3: Get a DDL going and stop in the middle
> +DROP TABLE bup_ddl_blocker.t2;
> +con1: Backing up database -- will block with lock
> +BACKUP DATABASE bup_ddl_blocker TO "bup_ddl_blocker.bak";
> +con6: Checking locks
> +con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +get_lock("DDL_blocked", 0)
> +1
> +con4: Try a DDL but it is blocked by backup -- will not be in backup
> +OPTIMIZE TABLE bup_ddl_blocker.t3;
> +con5: Try a DDL but it is blocked by backup -- will not be in backup
> +DROP TABLE bup_ddl_blocker.t4;
> +con6: Checking locks
> +con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +release_lock("DDL_blocked")
> +1
> +con2: Completing DDL
> +Table	Op	Msg_type	Msg_text
> +bup_ddl_blocker.t1	optimize	status	OK
> +con3: Completing DDL
> +con4: Completing DDL
> +Table	Op	Msg_type	Msg_text
> +bup_ddl_blocker.t3	optimize	status	OK
> +con5: Completing DDL
> +Backup Summary
> + header     =       36 bytes
> + meta-data  =      284 bytes
> + data       =      414 bytes
> +              --------------
> + total             734 bytes
> +
> +Verifying test 3 results:
> +
> +con1: Showing columns after updates and backup
> +DESCRIBE bup_ddl_blocker.t1;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +con1: Table t2 was dropped.
> +DESCRIBE bup_ddl_blocker.t2;
> +ERROR 42S02: Table 'bup_ddl_blocker.t2' doesn't exist
> +DESCRIBE bup_ddl_blocker.t3;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +con1: Table t4 was dropped.
> +DESCRIBE bup_ddl_blocker.t4;
> +ERROR 42S02: Table 'bup_ddl_blocker.t4' doesn't exist
> +con1: Dropping the database tables
> +DROP TABLE bup_ddl_blocker.t1, bup_ddl_blocker.t3;
> +con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +Restore Summary
> + header     =       36 bytes
> + meta-data  =      284 bytes
> + data       =      414 bytes
> +              --------------
> + total             734 bytes
> +con1: Showing columns that were backed up
> +DESCRIBE bup_ddl_blocker.t1;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +con1: Table t2 should not be in restored data.
> +DESCRIBE bup_ddl_blocker.t2;
> +ERROR 42S02: Table 'bup_ddl_blocker.t2' doesn't exist
> +DESCRIBE bup_ddl_blocker.t3;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t4;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +con1: Cleanup
> +DROP TABLE bup_ddl_blocker.t1, bup_ddl_blocker.t3,
> +bup_ddl_blocker.t4;
> +
> +Starting Test 4
> +
> +DROP DATABASE IF EXISTS bup_ddl_blocker_1;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_2;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_3;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_4;
> +con1: Creating databases
> +CREATE DATABASE bup_ddl_blocker_2;
> +CREATE DATABASE bup_ddl_blocker_4;
> +con1: Creating tables
> +CREATE TABLE bup_ddl_blocker_2.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker_4.t1 (col_a CHAR(40)) ENGINE=MYISAM;
> +con1: Loading data
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("03 Some data to test");
> +con1: Show that the new data doesn't exist before backup.
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +Database (bup_ddl_blocker_%)
> +bup_ddl_blocker_2
> +bup_ddl_blocker_4
> +SELECT * FROM bup_ddl_blocker_2.t1;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker_4.t1;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +get_lock("DDL_in_progress", 0)
> +1
> +con2: Get a DDL going and stop in the middle
> +CREATE DATABASE bup_ddl_blocker_1;
> +con3: Get a DDL going and stop in the middle
> +DROP DATABASE bup_ddl_blocker_2;
> +con1: Backing up database -- will block with lock
> +BACKUP DATABASE * TO "bup_ddl_blocker.bak";
> +con6: Checking locks
> +con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +get_lock("DDL_blocked", 0)
> +1
> +con4: Try a DDL but it is blocked by backup -- will not be in backup
> +CREATE DATABASE bup_ddl_blocker_3;
> +con5: Try a DDL but it is blocked by backup -- will not be in backup
> +DROP DATABASE bup_ddl_blocker_4;
> +con6: Checking locks
> +con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +release_lock("DDL_blocked")
> +1
> +con2: Completing DDL
> +con3: Completing DDL
> +con4: Completing DDL
> +con5: Completing DDL
> +Backup Summary
> + header     =       66 bytes
> + meta-data  =      102 bytes
> + data       =      138 bytes
> +              --------------
> + total             306 bytes
> +
> +Verifying test 4 results:
> +
> +con1: Showing databases after updates and backup
> +con1: bup_ddl_blocker_2 and bup_ddl_blocker_4 are not present
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +Database (bup_ddl_blocker_%)
> +bup_ddl_blocker_1
> +bup_ddl_blocker_3
> +con1: Dropping the database tables
> +DROP DATABASE bup_ddl_blocker_1;
> +DROP DATABASE bup_ddl_blocker_3;
> +con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +Restore Summary
> + header     =       66 bytes
> + meta-data  =      102 bytes
> + data       =      138 bytes
> +              --------------
> + total             306 bytes
> +con1: Showing databases that were backed up
> +con1: bup_ddl_blocker_2 and bup_ddl_blocker_3 are not present
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +Database (bup_ddl_blocker_%)
> +bup_ddl_blocker_1
> +bup_ddl_blocker_4
> +con1: Cleanup
> +DROP DATABASE bup_ddl_blocker_1;
> +DROP DATABASE bup_ddl_blocker_4;
> +
> +Starting Test 5
> +
> +DROP DATABASE IF EXISTS bup_ddl_blocker_1;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_2;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_3;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_4;
> +con1: Creating databases
> +CREATE DATABASE bup_ddl_blocker_1;
> +CREATE DATABASE bup_ddl_blocker_2 CHARACTER SET latin1;
> +CREATE DATABASE bup_ddl_blocker_3;
> +CREATE DATABASE bup_ddl_blocker_4 CHARACTER SET latin1;
> +con1: Creating tables
> +CREATE TABLE bup_ddl_blocker_1.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker_2.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker_3.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker_4.t1 (col_a CHAR(40)) ENGINE=MYISAM;
> +con1: Loading data
> +INSERT INTO bup_ddl_blocker_1.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_1.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_1.t1 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker_3.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_3.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_3.t1 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("03 Some data to test");
> +con1: Show that the new data doesn't exist before backup.
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +Database (bup_ddl_blocker_%)
> +bup_ddl_blocker_1
> +bup_ddl_blocker_2
> +bup_ddl_blocker_3
> +bup_ddl_blocker_4
> +SELECT * FROM bup_ddl_blocker_1.t1;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker_2.t1;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker_3.t1;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker_4.t1;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +get_lock("DDL_in_progress", 0)
> +1
> +con2: Get a DDL going and stop in the middle
> +RENAME DATABASE bup_ddl_blocker_1 TO bup_ddl_blocker_01;
> +con3: Get a DDL going and stop in the middle
> +ALTER DATABASE bup_ddl_blocker_2 CHARACTER SET latin2;
> +con1: Backing up database -- will block with lock
> +BACKUP DATABASE * TO "bup_ddl_blocker.bak";
> +con6: Checking locks
> +con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +get_lock("DDL_blocked", 0)
> +1
> +con4: Try a DDL but it is blocked by backup -- will not be in backup
> +RENAME DATABASE bup_ddl_blocker_3 TO bup_ddl_blocker_03;
> +con5: Try a DDL but it is blocked by backup -- will not be in backup
> +ALTER DATABASE bup_ddl_blocker_4 CHARACTER SET latin2;
> +con6: Checking locks
> +con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +release_lock("DDL_blocked")
> +1
> +con2: Completing DDL
> +con3: Completing DDL
> +con4: Completing DDL
> +con5: Completing DDL
> +Backup Summary
> + header     =      118 bytes
> + meta-data  =      388 bytes
> + data       =      552 bytes
> +              --------------
> + total            1058 bytes
> +
> +Verifying test 5 results:
> +
> +con1: Showing databases after updates and backup
> +con1: bup_ddl_blocker_1 and bup_ddl_blocker_3 are renamed and 
> +con1: bup_ddl_blocker_2 and bup_ddl_blocker_4 have had their charset changed. 
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +Database (bup_ddl_blocker_%)
> +bup_ddl_blocker_01
> +bup_ddl_blocker_03
> +bup_ddl_blocker_2
> +bup_ddl_blocker_4
> +SHOW CREATE DATABASE bup_ddl_blocker_2;
> +Database	Create Database
> +bup_ddl_blocker_2	CREATE DATABASE `bup_ddl_blocker_2` /*!40100 DEFAULT CHARACTER SET
> latin2 */
> +SHOW CREATE DATABASE bup_ddl_blocker_4;
> +Database	Create Database
> +bup_ddl_blocker_4	CREATE DATABASE `bup_ddl_blocker_4` /*!40100 DEFAULT CHARACTER SET
> latin2 */
> +con1: Dropping the database tables
> +DROP DATABASE bup_ddl_blocker_01;
> +DROP DATABASE bup_ddl_blocker_2;
> +DROP DATABASE bup_ddl_blocker_03;
> +DROP DATABASE bup_ddl_blocker_4;
> +con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +Restore Summary
> + header     =      118 bytes
> + meta-data  =      388 bytes
> + data       =      552 bytes
> +              --------------
> + total            1058 bytes
> +con1: Showing databases that were backed up
> +con1: bup_ddl_blocker_1 has been renamed and
> +con1: bup_ddl_blocker_4 has not had its character set changed. 
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +Database (bup_ddl_blocker_%)
> +bup_ddl_blocker_01
> +bup_ddl_blocker_2
> +bup_ddl_blocker_3
> +bup_ddl_blocker_4
> +SHOW CREATE DATABASE bup_ddl_blocker_2;
> +Database	Create Database
> +bup_ddl_blocker_2	CREATE DATABASE `bup_ddl_blocker_2` /*!40100 DEFAULT CHARACTER SET
> latin1 */
> +SHOW CREATE DATABASE bup_ddl_blocker_4;
> +Database	Create Database
> +bup_ddl_blocker_4	CREATE DATABASE `bup_ddl_blocker_4` /*!40100 DEFAULT CHARACTER SET
> latin1 */
> +con1: Cleanup
> +DROP DATABASE bup_ddl_blocker_01;
> +DROP DATABASE bup_ddl_blocker_2;
> +DROP DATABASE bup_ddl_blocker_3;
> +DROP DATABASE bup_ddl_blocker_4;
> +
> +Starting Test 6
> +
> +DROP TABLE IF EXISTS test.t2;
> +DROP TABLE IF EXISTS bup_ddl_blocker.t1, bup_ddl_blocker.t3;
> +con1: Creating tables
> +CREATE TABLE bup_ddl_blocker.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE test.t2 (col_a CHAR(40)) ENGINE=MYISAM;
> +CREATE TABLE bup_ddl_blocker.t3 (col_a CHAR(40)) ENGINE=CSV;
> +con1: Loading data
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("03 Some data to test");
> +INSERT INTO test.t2 VALUES ("01 Some data to test");
> +INSERT INTO test.t2 VALUES ("02 Some data to test");
> +INSERT INTO test.t2 VALUES ("03 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("03 Some data to test");
> +con1: Show that the new data doesn't exist before backup.
> +SELECT * FROM bup_ddl_blocker.t1;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM test.t2;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +SELECT * FROM bup_ddl_blocker.t3;
> +col_a
> +01 Some data to test
> +02 Some data to test
> +03 Some data to test
> +con5: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_blocked", 0);
> +get_lock("DDL_blocked", 0)
> +1
> +con2: Get a DDL going and stop in the middle
> +ALTER TABLE bup_ddl_blocker.t1 ADD COLUMN col_b int;
> +con3: Get a DDL going and stop in the middle
> +ALTER TABLE test.t2 ADD COLUMN col_b int;
> +con4: Get a DDL going and stop in the middle
> +ALTER TABLE bup_ddl_blocker.t3 ADD COLUMN col_b int;
> +con5: Checking locks
> +con5: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +release_lock("DDL_blocked")
> +1
> +con2: Completing DDL
> +con3: Completing DDL
> +con4: Completing DDL
> +con1: Showing columns after updates and backup
> +DESCRIBE bup_ddl_blocker.t1;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +col_b	int(11)	YES		NULL	
> +DESCRIBE test.t2;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +col_b	int(11)	YES		NULL	
> +DESCRIBE bup_ddl_blocker.t3;
> +Field	Type	Null	Key	Default	Extra
> +col_a	char(40)	YES		NULL	
> +col_b	int(11)	YES		NULL	
> +
> +Verifying test 6 results:
> +
> +T1 should have the changes after backup - count(*) = 2
> +SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS
> +WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'bup_ddl_blocker';
> +count(*)
> +2
> +T2 should the changes after backup - count(*) = 2
> +SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS
> +WHERE TABLE_NAME = 't2' AND TABLE_SCHEMA = 'test';
> +count(*)
> +2
> +T3 should not have the changes after backup - count(*) = 2
> +SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS
> +WHERE TABLE_NAME = 't3' AND TABLE_SCHEMA = 'bup_ddl_blocker';
> +count(*)
> +2
> +con1: Cleanup
> +DROP DATABASE bup_ddl_blocker;
> +DROP TABLE test.t2;


> diff -Nrup a/mysql-test/t/backup_ddl_blocker.test
> b/mysql-test/t/backup_ddl_blocker.test
> --- /dev/null	Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/t/backup_ddl_blocker.test	2007-11-08 14:15:19 -05:00
> @@ -0,0 +1,1235 @@
> +#
> +# This test is for the DDL blocker
> +# The goals of the test should be to ensure the following assumptions for
> +# the behaviour of the DDL blocker hold true.
> +#
> +# a) DDL in progress are not blocked 

My understanding: If there is a DDL running (not finished yet), and we start 
BACKUP/RESTORE, then the DDL should finish and BACKUP/RESTORE should wait for it.

> +# b) DDL that are running are allowed to complete and backup blocks

So, in my understanding, this is the same as a).

> +# c) backup blocks all DDL even if not part of backup

My understanding: If BACKUP/RESTORE operation is in progress and someone issues 
DDL statement, this statement will block and wait until the BACKUP/RESTORE 
operation completes. (Shouldn't it rather end with error?)

> +# d) DDL operations do not block each other

My understanding: if BACKUP/RESTORE is not running, DDL operations can be 
executed from several parallel connections without any additional restrictions.

Note: I think this is difficult to test (as parrallel DDLs can interleave in an 
infinite number of ways) and perhaps testing this is not needed as this property 
should be evident from the way DDL blocker is implemented (I realize it is 
contriversial to say that we don't need to test certain property of the code 
since it is "obvious", but well... maybe here it is the case?).

> +#
> +# The results of the backup should show (based on statements above):
> +#
> +# a) Test result for this assumption: Included in backup
> +# b) Test result for this assumption: Included in backup
> +# c) Test result for this assumption: Not included in backup

I understand this as additional requirements that in situation a) above (and b) 
which I see as equal to a)) the result of the DDL statement should be included 
in backup.

In situation c) the result of the DDL statement should not be included in the 
backup image (this requirement makes sense only if we allow blocked DDL to wait 
and continue  after BACKUP/RESTORE operation).

> +#
> +# The test shall run two sets of data definition statements. 
> +#
> +# 1) test DDL in progress that block backup and while the backup in progress
> +#    check that DDLs that start after the backup are blocked
> +# 2) DDL do not block each other
> +#
> +# These set of DDL statements to test include the following. The DDL 
> +# statements shall be run in sets with each set a separate test. These tests
> +# will show the assumptions above to be true.
> +#
> +# Test  DDL Tested
> +# ----  ------------------
> +#  1    CREATE TABLE
> +#  1    ALTER TABLE
> +#  2    RENAME TABLE
> +#  2    REPAIR TABLE
> +#  3    OPTIMIZE TABLE
> +#  3    DROP TABLE
> +#  4    CREATE DATABASE
> +#  4    DROP DATABASE
> +#  5    RENAME DATABASE
> +#  5    ALTER DATABASE
> +#  6    DDL statements do not block each other
> +#
> +# TODO : Add a native driver to the test when one becomes available
> +#
> +
> +--source include/have_innodb.inc
> +--source include/have_debug.inc
> +
> +--disable_warnings
> +DROP DATABASE IF EXISTS bup_ddl_blocker;
> +--enable_warnings
> +
> +CREATE DATABASE bup_ddl_blocker;
> +
> +#
> +# Connections used in this test
> +#
> +# con1       used to create data, load data, and run the backup 
> +# con2-con5  used for DDL statements: 2 before backup and 2 during backup
> +# con6       used for setting and releasing breakpoints
> +#
> +
> +connect (con1,localhost,root,,);
> +connect (con2,localhost,root,,);
> +connect (con3,localhost,root,,);
> +connect (con4,localhost,root,,);
> +connect (con5,localhost,root,,);
> +connect (con6,localhost,root,,);
> +
> +connection con1;
> +
> +#
> +# Test 1 - Test DDL in progress that block backup and while the backup in progress
> +#          check that DDLs that start after the backup are blocked, part 1 of 5
> +#
> +
> +--echo 
> +--echo Starting Test 1
> +--echo 
> +
> +#
> +# Test 1 sequence diagram (not UML)
> +#
> +#   con1     con2     con3     con4     con6
> +#  (setup)    |        |        |        |        |
> +#     |       |        |        |        |        |
> +#     |       |        |        |        | lock("DDL_in_progress");
> +#     |       |        |        |        |  <wait for locks>

Problem: Shouldn't we wait after issuing the DDL statements, not before them?

> +#     |   CREATE b.t1  |        |        |        |
> +#     |       |   ALTER b.t2    |        |        |

OK: The DDLs will stop at "DDL_in_progres" breakpoint, which is after 
check_DDL_blocker() call (so the folowing BACKUP should wait until they finish).

> +# BACKUP b    |        |        |        |        |

OK: BACKUP will wait in block_DDL() for the DDLs to complete...

> +#     |       |        |        |        | lock("DDL_blocked");
> +#     |     <...>    <...>      |        |        |

OK: DDLs will complete and BACKUP will stop at "DDL_blocked" which is after 
activating the DDL blocker.

Problem: Shouldn't we wait for BACKUP to reach this breakpoint before continuing.

> +#     |       |        |   CREATE b.t3   |        |
> +#     |       |        |        |   ALTER b.t4    |
> +#     |       |        |        |        | <wait for locks>

Problem: the DDLs will stop on the "DDL_blocked" breakpoint, not because they 
are blocked by the DDL blocker (i.e. not inside check_DDL_blocker()).

I think the "DDL_blocked" breakpoints should be removed from the big switch - it 
looks like we need only the "DDL_in_progress" breakpoints there.

> +#     |       |        |        |        |  <release locks>
> +#   <...>     |        |      <...>    <...>      |
> +# (results)   |        |        |        |        |
> +#
> +# Note: The resume of the commands is indicated with <...> and
> +#       may occur in any order.
> +#
> +
> +--disable_warnings
> +DROP TABLE IF EXISTS bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +                     bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +--enable_warnings
> +
> +# Create transaction tables and load them with data.
> +--echo con1: Creating tables
> +CREATE TABLE bup_ddl_blocker.t2 (col_a CHAR(40)) ENGINE=MYISAM;
> +CREATE TABLE bup_ddl_blocker.t4 (col_a CHAR(40)) ENGINE=MYISAM;
> +
> +--echo con1: Loading data
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("03 Some data to test");
> +
> +--echo con1: Show that the new data doesn't exist before backup.
> +SELECT * FROM bup_ddl_blocker.t2;
> +SELECT * FROM bup_ddl_blocker.t4;
> +
> +connection con6;
> +
> +# Set the breakpoint for DDL in progress.
> +--echo con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +
> +connection con2;
> +
> +--echo con2: Get a DDL going and stop in the middle
> +send CREATE TABLE bup_ddl_blocker.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +
> +connection con3;
> +
> +--echo con3: Get a DDL going and stop in the middle
> +send ALTER TABLE bup_ddl_blocker.t2 ADD COLUMN col_b int;
> +
> +# Start the backup and allow it to break on lock.
> +
> +connection con1;
> +
> +--echo con1: Backing up database -- will block with lock
> +send BACKUP DATABASE bup_ddl_blocker TO "bup_ddl_blocker.bak";
> +
> +connection con6;
> +
> +# Wait for lock to be acquired and execution to reach breakpoint
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "CREATE TABLE bup_ddl_blocker.t1%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "ALTER TABLE bup_ddl_blocker.t2%";
> +--source include/wait_condition.inc
> +
> +# Now set the breakpoint for DDL blocker.
> +# This releases lock on DDL_in_progress stopping all DDLs.
> +--echo con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +
> +connection con4;
> +
> +--echo con4: Try a DDL but it is blocked by backup -- will not be in backup
> +send CREATE TABLE bup_ddl_blocker.t3 (col_a CHAR(40)) ENGINE=INNODB;
> +
> +connection con5;
> +
> +--echo con5: Try a DDL but it is blocked by backup -- will not be in backup
> +send ALTER TABLE bup_ddl_blocker.t4 ADD COLUMN col_b int;
> +
> +connection con6;
> +
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "CREATE TABLE bup_ddl_blocker.t3%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "ALTER TABLE bup_ddl_blocker.t4%";
> +--source include/wait_condition.inc
> +
> +--echo con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +
> +# Reconnect to connections and allow them to finish.
> +
> +connection con2;
> +--echo con2: Completing DDL
> +reap;
> +
> +connection con3;
> +--echo con3: Completing DDL
> +reap;
> +
> +connection con4;
> +--echo con4: Completing DDL
> +reap;
> +
> +connection con5;
> +--echo con5: Completing DDL
> +reap;
> +
> +# Reconnect to con1 and let backup finish.
> +
> +connection con1;
> +reap;
> +
> +--echo
> +--echo Verifying test 1 results:
> +--echo
> +
> +# Show that all changes got applied.
> +--echo con1: Showing columns after updates and backup
> +DESCRIBE bup_ddl_blocker.t1;
> +DESCRIBE bup_ddl_blocker.t2;
> +DESCRIBE bup_ddl_blocker.t3;
> +DESCRIBE bup_ddl_blocker.t4;
> +
> +--echo con1: Dropping the database tables
> +DROP TABLE bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +           bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +
> +--echo con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +
> +--echo con1: Showing columns that were backed up
> +
> +# Table t3 and changes to t4 should not be in restore
> +DESCRIBE bup_ddl_blocker.t1;
> +DESCRIBE bup_ddl_blocker.t2;
> +--echo con1: Table t3 should not be in restored data.
> +--error 1146
> +DESCRIBE bup_ddl_blocker.t3;
> +--echo con1: Table t4 should not have new column in restored data.
> +DESCRIBE bup_ddl_blocker.t4;
> +
> +--echo con1: Cleanup
> +DROP TABLE bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +           bup_ddl_blocker.t4;
> +
> +remove_file $MYSQLTEST_VARDIR/master-data/bup_ddl_blocker.bak;
> +
> +
> +#
> +# Test 2 - Test DDL in progress that block backup and while the backup in progress
> +#          check that DDLs that start after the backup are blocked, part 2 of 5
> +#
> +
> +--echo 
> +--echo Starting Test 2
> +--echo 
> +
> +#
> +# Test 2 sequence diagram (not UML)
> +#
> +#   con1     con2     con3     con4     con6
> +#  (setup)    |        |        |        |        |
> +#     |       |        |        |        |        |
> +#     |       |        |        |        | lock("DDL_in_progress");
> +#     |       |        |        |        |  <wait for locks>
> +#     |   RENAME b.t1  |        |        |        |
> +#     |       |   REPAIR b.t2   |        |        |
> +# BACKUP b    |        |        |        |        |
> +#     |       |        |        |        | lock("DDL_blocked");
> +#     |     <...>    <...>      |        |        |
> +#     |       |        |   RENAME b.t3   |        |
> +#     |       |        |        |   REPAIR b.t4   |
> +#     |       |        |        |        | <wait for locks>
> +#     |       |        |        |        |  <release locks>
> +#   <...>     |        |      <...>    <...>      |
> +# (results)   |        |        |        |        |
> +#
> +# Note: The resume of the commands is indicated with <...> and
> +#       may occur in any order.
> +#
> +
> +--disable_warnings
> +DROP TABLE IF EXISTS bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +                     bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +--enable_warnings
> +
> +# Create transaction tables and load them with data.
> +--echo con1: Creating tables
> +CREATE TABLE bup_ddl_blocker.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker.t2 (col_a CHAR(40)) ENGINE=MYISAM;
> +CREATE TABLE bup_ddl_blocker.t3 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker.t4 (col_a CHAR(40)) ENGINE=MYISAM;
> +
> +--echo con1: Loading data
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("03 Some data to test");
> +
> +--echo con1: Show that the new data doesn't exist before backup.
> +SELECT * FROM bup_ddl_blocker.t1;
> +SELECT * FROM bup_ddl_blocker.t2;
> +SELECT * FROM bup_ddl_blocker.t3;
> +SELECT * FROM bup_ddl_blocker.t4;
> +
> +connection con6;
> +
> +# Set the breakpoint for DDL in progress.
> +--echo con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +
> +connection con2;
> +
> +--echo con2: Get a DDL going and stop in the middle
> +send RENAME TABLE bup_ddl_blocker.t1 TO bup_ddl_blocker.t01;
> +
> +connection con3;
> +
> +--echo con3: Get a DDL going and stop in the middle
> +send REPAIR TABLE bup_ddl_blocker.t2;
> +
> +# Start the backup and allow it to break on lock.
> +
> +connection con1;
> +
> +--echo con1: Backing up database -- will block with lock
> +send BACKUP DATABASE bup_ddl_blocker TO "bup_ddl_blocker.bak";
> +
> +connection con6;
> +
> +# Wait for lock to be acquired and execution to reach breakpoint
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "RENAME TABLE bup_ddl_blocker.t1%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "REPAIR TABLE bup_ddl_blocker.t2%";
> +--source include/wait_condition.inc
> +
> +# Now set the breakpoint for DDL blocker.
> +# This releases lock on DDL_in_progress stopping all DDLs.
> +--echo con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +
> +connection con4;
> +
> +--echo con4: Try a DDL but it is blocked by backup -- will not be in backup
> +send RENAME TABLE bup_ddl_blocker.t3 TO bup_ddl_blocker.t03;
> +
> +connection con5;
> +
> +--echo con5: Try a DDL but it is blocked by backup -- will not be in backup
> +send REPAIR TABLE bup_ddl_blocker.t4;
> +
> +connection con6;
> +
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "RENAME TABLE bup_ddl_blocker.t3%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "REPAIR TABLE bup_ddl_blocker.t4%";
> +--source include/wait_condition.inc
> +
> +--echo con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +
> +# Reconnect to connections and allow them to finish.
> +
> +connection con2;
> +--echo con2: Completing DDL
> +reap;
> +
> +connection con3;
> +--echo con3: Completing DDL
> +reap;
> +
> +connection con4;
> +--echo con4: Completing DDL
> +reap;
> +
> +connection con5;
> +--echo con5: Completing DDL
> +reap;
> +
> +# Reconnect to con1 and let backup finish.
> +
> +connection con1;
> +reap;
> +
> +--echo
> +--echo Verifying test 2 results:
> +--echo
> +
> +# Show that all changes got applied.
> +--echo con1: Showing columns after updates and backup
> +DESCRIBE bup_ddl_blocker.t01;
> +DESCRIBE bup_ddl_blocker.t2;
> +DESCRIBE bup_ddl_blocker.t03;
> +DESCRIBE bup_ddl_blocker.t4;
> +
> +--echo con1: Dropping the database tables
> +DROP TABLE bup_ddl_blocker.t01, bup_ddl_blocker.t2,
> +           bup_ddl_blocker.t03, bup_ddl_blocker.t4;
> +
> +--echo con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +
> +--echo con1: Showing columns that were backed up
> +
> +# Table t3 and changes to t4 should not be in restore
> +--echo con1: Table t1 should not be in restored data.
> +--error 1146
> +DESCRIBE bup_ddl_blocker.t1;
> +DESCRIBE bup_ddl_blocker.t01;
> +DESCRIBE bup_ddl_blocker.t2;
> +--echo con1: Table t03 should not be in restored data.
> +--error 1146
> +DESCRIBE bup_ddl_blocker.t03;
> +DESCRIBE bup_ddl_blocker.t3;
> +DESCRIBE bup_ddl_blocker.t4;
> +
> +--echo con1: Cleanup
> +DROP TABLE bup_ddl_blocker.t01, bup_ddl_blocker.t2,
> +           bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +
> +remove_file $MYSQLTEST_VARDIR/master-data/bup_ddl_blocker.bak;
> +
> +#
> +# Test 3 - Test DDL in progress that block backup and while the backup in progress
> +#          check that DDLs that start after the backup are blocked, part 3 of 5
> +#
> +
> +--echo 
> +--echo Starting Test 3
> +--echo 
> +
> +#
> +# Test 3 sequence diagram (not UML)
> +#
> +#   con1     con2     con3     con4     con6
> +#  (setup)    |        |        |        |        |
> +#     |       |        |        |        |        |
> +#     |       |        |        |        | lock("DDL_in_progress");
> +#     |       |        |        |        |  <wait for locks>
> +#     | OPTIMIZE b.t1  |        |        |        |
> +#     |       |    DROP b.t2    |        |        |
> +# BACKUP b    |        |        |        |        |
> +#     |       |        |        |        | lock("DDL_blocked");
> +#     |     <...>    <...>      |        |        |
> +#     |       |        | OPTIMIZE b.t3   |        |
> +#     |       |        |        |    DROP b.t4    |
> +#     |       |        |        |        | <wait for locks>
> +#     |       |        |        |        |  <release locks>
> +#   <...>     |        |      <...>    <...>      |
> +# (results)   |        |        |        |        |
> +#
> +# Note: The resume of the commands is indicated with <...> and
> +#       may occur in any order.
> +#
> +
> +--disable_warnings
> +DROP TABLE IF EXISTS bup_ddl_blocker.t1, bup_ddl_blocker.t2,
> +                     bup_ddl_blocker.t3, bup_ddl_blocker.t4;
> +--enable_warnings
> +
> +# Create transaction tables and load them with data.
> +--echo con1: Creating tables
> +CREATE TABLE bup_ddl_blocker.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker.t2 (col_a CHAR(40)) ENGINE=MYISAM;
> +CREATE TABLE bup_ddl_blocker.t3 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker.t4 (col_a CHAR(40)) ENGINE=MYISAM;
> +
> +--echo con1: Loading data
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t2 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t4 VALUES ("03 Some data to test");
> +
> +--echo con1: Show that the new data doesn't exist before backup.
> +SELECT * FROM bup_ddl_blocker.t1;
> +SELECT * FROM bup_ddl_blocker.t2;
> +SELECT * FROM bup_ddl_blocker.t3;
> +SELECT * FROM bup_ddl_blocker.t4;
> +
> +connection con6;
> +
> +# Set the breakpoint for DDL in progress.
> +--echo con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +
> +connection con2;
> +
> +--echo con2: Get a DDL going and stop in the middle
> +send OPTIMIZE TABLE bup_ddl_blocker.t1;
> +
> +connection con3;
> +
> +--echo con3: Get a DDL going and stop in the middle
> +send DROP TABLE bup_ddl_blocker.t2;
> +
> +# Start the backup and allow it to break on lock.
> +
> +connection con1;
> +
> +--echo con1: Backing up database -- will block with lock
> +send BACKUP DATABASE bup_ddl_blocker TO "bup_ddl_blocker.bak";
> +
> +connection con6;
> +
> +# Wait for lock to be acquired and execution to reach breakpoint
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "OPTIMIZE TABLE bup_ddl_blocker.t1%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "DROP TABLE bup_ddl_blocker.t2%";
> +--source include/wait_condition.inc
> +
> +# Now set the breakpoint for DDL blocker.
> +# This releases lock on DDL_in_progress stopping all DDLs.
> +--echo con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +
> +connection con4;
> +
> +--echo con4: Try a DDL but it is blocked by backup -- will not be in backup
> +send OPTIMIZE TABLE bup_ddl_blocker.t3;
> +
> +connection con5;
> +
> +--echo con5: Try a DDL but it is blocked by backup -- will not be in backup
> +send DROP TABLE bup_ddl_blocker.t4;
> +
> +connection con6;
> +
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "OPTIMIZE TABLE bup_ddl_blocker.t3%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "DROP TABLE bup_ddl_blocker.t4%";
> +--source include/wait_condition.inc
> +
> +--echo con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +
> +# Reconnect to connections and allow them to finish.
> +
> +connection con2;
> +--echo con2: Completing DDL
> +reap;
> +
> +connection con3;
> +--echo con3: Completing DDL
> +reap;
> +
> +connection con4;
> +--echo con4: Completing DDL
> +reap;
> +
> +connection con5;
> +--echo con5: Completing DDL
> +reap;
> +
> +# Reconnect to con1 and let backup finish.
> +
> +connection con1;
> +reap;
> +
> +--echo
> +--echo Verifying test 3 results:
> +--echo
> +
> +# Show that all changes got applied.
> +--echo con1: Showing columns after updates and backup
> +DESCRIBE bup_ddl_blocker.t1;
> +--echo con1: Table t2 was dropped.
> +--error 1146
> +DESCRIBE bup_ddl_blocker.t2;
> +DESCRIBE bup_ddl_blocker.t3;
> +--echo con1: Table t4 was dropped.
> +--error 1146
> +DESCRIBE bup_ddl_blocker.t4;
> +
> +--echo con1: Dropping the database tables
> +DROP TABLE bup_ddl_blocker.t1, bup_ddl_blocker.t3;
> +
> +--echo con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +
> +--echo con1: Showing columns that were backed up
> +
> +# Table t3 and changes to t4 should not be in restore
> +DESCRIBE bup_ddl_blocker.t1;
> +--echo con1: Table t2 should not be in restored data.
> +--error 1146
> +DESCRIBE bup_ddl_blocker.t2;
> +DESCRIBE bup_ddl_blocker.t3;
> +DESCRIBE bup_ddl_blocker.t4;
> +
> +--echo con1: Cleanup
> +DROP TABLE bup_ddl_blocker.t1, bup_ddl_blocker.t3,
> +           bup_ddl_blocker.t4;
> +
> +remove_file $MYSQLTEST_VARDIR/master-data/bup_ddl_blocker.bak;
> +
> +#
> +# Test 4 - Test DDL in progress that block backup and while the backup in progress
> +#          check that DDLs that start after the backup are blocked, part 4 of 5
> +#
> +
> +--echo 
> +--echo Starting Test 4
> +--echo 
> +
> +#
> +# Test 4 sequence diagram (not UML)
> +#
> +#   con1     con2     con3     con4     con6
> +#  (setup)    |        |        |        |        |
> +#     |       |        |        |        |        |
> +#     |       |        |        |        | lock("DDL_in_progress");
> +#     |       |        |        |        |  <wait for locks>
> +#     |   CREATE db1   |        |        |        |
> +#     |       |      DROP db2   |        |        |
> +# BACKUP b    |        |        |        |        |
> +#     |       |        |        |        | lock("DDL_blocked");
> +#     |     <...>    <...>      |        |        |
> +#     |       |        |    CREATE db3   |        |
> +#     |       |        |        |      DROP db4   |
> +#     |       |        |        |        | <wait for locks>
> +#     |       |        |        |        |  <release locks>
> +#   <...>     |        |      <...>    <...>      |
> +# (results)   |        |        |        |        |
> +#
> +# Note: The resume of the commands is indicated with <...> and
> +#       may occur in any order.
> +#
> +
> +--disable_warnings
> +DROP DATABASE IF EXISTS bup_ddl_blocker_1;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_2;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_3;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_4;
> +--enable_warnings
> +
> +# Create databases.
> +--echo con1: Creating databases
> +CREATE DATABASE bup_ddl_blocker_2;
> +CREATE DATABASE bup_ddl_blocker_4;
> +
> +# Create transaction tables and load them with data.
> +--echo con1: Creating tables
> +CREATE TABLE bup_ddl_blocker_2.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker_4.t1 (col_a CHAR(40)) ENGINE=MYISAM;
> +
> +--echo con1: Loading data
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("03 Some data to test");
> +
> +--echo con1: Show that the new data doesn't exist before backup.
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +
> +SELECT * FROM bup_ddl_blocker_2.t1;
> +SELECT * FROM bup_ddl_blocker_4.t1;
> +
> +connection con6;
> +
> +# Set the breakpoint for DDL in progress.
> +--echo con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +
> +connection con2;
> +
> +--echo con2: Get a DDL going and stop in the middle
> +send CREATE DATABASE bup_ddl_blocker_1;
> +
> +connection con3;
> +
> +--echo con3: Get a DDL going and stop in the middle
> +send DROP DATABASE bup_ddl_blocker_2;
> +
> +# Start the backup and allow it to break on lock.
> +
> +connection con1;
> +
> +--echo con1: Backing up database -- will block with lock
> +send BACKUP DATABASE * TO "bup_ddl_blocker.bak";
> +
> +connection con6;
> +
> +# Wait for lock to be acquired and execution to reach breakpoint
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "CREATE DATABASE bup_ddl_blocker_1%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "DROP DATABASE bup_ddl_blocker_2%";
> +--source include/wait_condition.inc
> +
> +# Now set the breakpoint for DDL blocker.
> +# This releases lock on DDL_in_progress stopping all DDLs.
> +--echo con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +
> +connection con4;
> +
> +--echo con4: Try a DDL but it is blocked by backup -- will not be in backup
> +send CREATE DATABASE bup_ddl_blocker_3;
> +
> +connection con5;
> +
> +--echo con5: Try a DDL but it is blocked by backup -- will not be in backup
> +send DROP DATABASE bup_ddl_blocker_4;
> +
> +connection con6;
> +
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "CREATE DATABASE bup_ddl_blocker_3%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "DROP DATABASE bup_ddl_blocker_4%";
> +--source include/wait_condition.inc
> +
> +--echo con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +
> +# Reconnect to connections and allow them to finish.
> +
> +connection con2;
> +--echo con2: Completing DDL
> +reap;
> +
> +connection con3;
> +--echo con3: Completing DDL
> +reap;
> +
> +connection con4;
> +--echo con4: Completing DDL
> +reap;
> +
> +connection con5;
> +--echo con5: Completing DDL
> +reap;
> +
> +# Reconnect to con1 and let backup finish.
> +
> +connection con1;
> +reap;
> +
> +--echo
> +--echo Verifying test 4 results:
> +--echo
> +
> +# Show that all changes got applied.
> +--echo con1: Showing databases after updates and backup
> +--echo con1: bup_ddl_blocker_2 and bup_ddl_blocker_4 are not present
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +
> +--echo con1: Dropping the database tables
> +DROP DATABASE bup_ddl_blocker_1;
> +DROP DATABASE bup_ddl_blocker_3;
> +
> +--echo con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +
> +--echo con1: Showing databases that were backed up
> +--echo con1: bup_ddl_blocker_2 and bup_ddl_blocker_3 are not present
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +
> +--echo con1: Cleanup
> +DROP DATABASE bup_ddl_blocker_1;
> +DROP DATABASE bup_ddl_blocker_4;
> +
> +remove_file $MYSQLTEST_VARDIR/master-data/bup_ddl_blocker.bak;
> +
> +#
> +# Test 5 - Test DDL in progress that block backup and while the backup in progress
> +#          check that DDLs that start after the backup are blocked, part 5 of 5
> +#
> +
> +--echo 
> +--echo Starting Test 5
> +--echo 
> +
> +#
> +# Test 5 sequence diagram (not UML)
> +#
> +#   con1     con2     con3     con4     con6
> +#  (setup)    |        |        |        |        |
> +#     |       |        |        |        |        |
> +#     |       |        |        |        | lock("DDL_in_progress");
> +#     |       |        |        |        |  <wait for locks>
> +#     |   RENAME db1   |        |        |        |
> +#     |       |     ALTER db2   |        |        |
> +# BACKUP b    |        |        |        |        |
> +#     |       |        |        |        | lock("DDL_blocked");
> +#     |     <...>    <...>      |        |        |
> +#     |       |        |    RENAME db3   |        |
> +#     |       |        |        |     ALTER db4   |
> +#     |       |        |        |        | <wait for locks>
> +#     |       |        |        |        |  <release locks>
> +#   <...>     |        |      <...>    <...>      |
> +# (results)   |        |        |        |        |
> +#
> +# Note: The resume of the commands is indicated with <...> and
> +#       may occur in any order.
> +#
> +
> +--disable_warnings
> +DROP DATABASE IF EXISTS bup_ddl_blocker_1;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_2;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_3;
> +DROP DATABASE IF EXISTS bup_ddl_blocker_4;
> +--enable_warnings
> +
> +# Create databases.
> +--echo con1: Creating databases
> +CREATE DATABASE bup_ddl_blocker_1;
> +CREATE DATABASE bup_ddl_blocker_2 CHARACTER SET latin1;
> +CREATE DATABASE bup_ddl_blocker_3;
> +CREATE DATABASE bup_ddl_blocker_4 CHARACTER SET latin1;
> +
> +# Create transaction tables and load them with data.
> +--echo con1: Creating tables
> +CREATE TABLE bup_ddl_blocker_1.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker_2.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker_3.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE bup_ddl_blocker_4.t1 (col_a CHAR(40)) ENGINE=MYISAM;
> +
> +--echo con1: Loading data
> +INSERT INTO bup_ddl_blocker_1.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_1.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_1.t1 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_2.t1 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker_3.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_3.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_3.t1 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker_4.t1 VALUES ("03 Some data to test");
> +
> +--echo con1: Show that the new data doesn't exist before backup.
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +
> +SELECT * FROM bup_ddl_blocker_1.t1;
> +SELECT * FROM bup_ddl_blocker_2.t1;
> +SELECT * FROM bup_ddl_blocker_3.t1;
> +SELECT * FROM bup_ddl_blocker_4.t1;
> +
> +connection con6;
> +
> +# Set the breakpoint for DDL in progress.
> +--echo con6: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_in_progress", 0);
> +
> +connection con2;
> +
> +--echo con2: Get a DDL going and stop in the middle
> +send RENAME DATABASE bup_ddl_blocker_1 TO bup_ddl_blocker_01;
> +
> +connection con3;
> +
> +--echo con3: Get a DDL going and stop in the middle
> +send ALTER DATABASE bup_ddl_blocker_2 CHARACTER SET latin2;
> +
> +# Start the backup and allow it to break on lock.
> +
> +connection con1;
> +
> +--echo con1: Backing up database -- will block with lock
> +send BACKUP DATABASE * TO "bup_ddl_blocker.bak";
> +
> +connection con6;
> +
> +# Wait for lock to be acquired and execution to reach breakpoint
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "RENAME DATABASE bup_ddl_blocker_1%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_in_progress"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "ALTER DATABASE bup_ddl_blocker_2%";
> +--source include/wait_condition.inc
> +
> +# Now set the breakpoint for DDL blocker.
> +# This releases lock on DDL_in_progress stopping all DDLs.
> +--echo con6: Getting lock on DDL blocker.
> +SELECT get_lock("DDL_blocked", 0);
> +
> +connection con4;
> +
> +--echo con4: Try a DDL but it is blocked by backup -- will not be in backup
> +send RENAME DATABASE bup_ddl_blocker_3 TO bup_ddl_blocker_03;
> +
> +connection con5;
> +
> +--echo con5: Try a DDL but it is blocked by backup -- will not be in backup
> +send ALTER DATABASE bup_ddl_blocker_4 CHARACTER SET latin2;
> +
> +connection con6;
> +
> +--echo con6: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "BACKUP DATABASE%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "RENAME DATABASE bup_ddl_blocker_3%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "ALTER DATABASE bup_ddl_blocker_4%";
> +--source include/wait_condition.inc
> +
> +--echo con6: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +
> +# Reconnect to connections and allow them to finish.
> +
> +connection con2;
> +--echo con2: Completing DDL
> +reap;
> +
> +connection con3;
> +--echo con3: Completing DDL
> +reap;
> +
> +connection con4;
> +--echo con4: Completing DDL
> +reap;
> +
> +connection con5;
> +--echo con5: Completing DDL
> +reap;
> +
> +# Reconnect to con1 and let backup finish.
> +
> +connection con1;
> +reap;
> +
> +--echo
> +--echo Verifying test 5 results:
> +--echo
> +
> +# Show that all changes got applied.
> +--echo con1: Showing databases after updates and backup
> +--echo con1: bup_ddl_blocker_1 and bup_ddl_blocker_3 are renamed and 
> +--echo con1: bup_ddl_blocker_2 and bup_ddl_blocker_4 have had their charset changed.
> 
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +SHOW CREATE DATABASE bup_ddl_blocker_2;
> +SHOW CREATE DATABASE bup_ddl_blocker_4;
> +
> +--echo con1: Dropping the database tables
> +DROP DATABASE bup_ddl_blocker_01;
> +DROP DATABASE bup_ddl_blocker_2;
> +DROP DATABASE bup_ddl_blocker_03;
> +DROP DATABASE bup_ddl_blocker_4;
> +
> +--echo con1: Restoring the database
> +RESTORE FROM "bup_ddl_blocker.bak";
> +
> +--echo con1: Showing databases that were backed up
> +--echo con1: bup_ddl_blocker_1 has been renamed and
> +
> +#
> +# NOTE: The online backup does not preserve character set information as 
> +# it should. Thus, the results of this test will not show the changes
> +# made with the alter database command to bup_ddl_blocker_2. This has
> +# been reported as BUG#11111. 
> +#
> +
> +--echo con1: bup_ddl_blocker_4 has not had its character set changed. 
> +
> +SHOW DATABASES LIKE 'bup_ddl_blocker_%';
> +SHOW CREATE DATABASE bup_ddl_blocker_2;
> +SHOW CREATE DATABASE bup_ddl_blocker_4;
> +
> +--echo con1: Cleanup
> +DROP DATABASE bup_ddl_blocker_01;
> +DROP DATABASE bup_ddl_blocker_2;
> +DROP DATABASE bup_ddl_blocker_3;
> +DROP DATABASE bup_ddl_blocker_4;
> +
> +remove_file $MYSQLTEST_VARDIR/master-data/bup_ddl_blocker.bak;
> +
> +#
> +# Test 6 - Test DDL do not block each other
> +#
> +
> +--echo 
> +--echo Starting Test 6
> +--echo 
> +
> +#
> +# Test 6 sequence diagram (not UML)
> +#
> +#   con1     con2     con3     con4       con5
> +#  (setup)    |        |        |          |
> +#     |       |        |        |          |
> +#     |   ALTER a.t1   |        |          |
> +#     |       |   ALTER b.t2    |          |
> +#     |       |        |    ALTER a.t3     |
> +#     |       |        |        | lock("DDL_in_progress");
> +#     |       |        |        |    <wait for locks>
> +#     |       |        |        |    <release locks>
> +#     |     <...>    <...>    <...>        |
> +# (results)   |        |        |          | 
> +#
> +# Note: The resume of the commands is indicated with <...> and
> +#       may occur in any order.
> +#
> +
> +--disable_warnings
> +DROP TABLE IF EXISTS test.t2;
> +DROP TABLE IF EXISTS bup_ddl_blocker.t1, bup_ddl_blocker.t3;
> +--enable_warnings
> +
> +# Create transaction tables and load them with data.
> +--echo con1: Creating tables
> +CREATE TABLE bup_ddl_blocker.t1 (col_a CHAR(40)) ENGINE=INNODB;
> +CREATE TABLE test.t2 (col_a CHAR(40)) ENGINE=MYISAM;
> +CREATE TABLE bup_ddl_blocker.t3 (col_a CHAR(40)) ENGINE=CSV;
> +
> +--echo con1: Loading data
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t1 VALUES ("03 Some data to test");
> +
> +INSERT INTO test.t2 VALUES ("01 Some data to test");
> +INSERT INTO test.t2 VALUES ("02 Some data to test");
> +INSERT INTO test.t2 VALUES ("03 Some data to test");
> +
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("01 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("02 Some data to test");
> +INSERT INTO bup_ddl_blocker.t3 VALUES ("03 Some data to test");
> +
> +--echo con1: Show that the new data doesn't exist before backup.
> +SELECT * FROM bup_ddl_blocker.t1;
> +SELECT * FROM test.t2;
> +SELECT * FROM bup_ddl_blocker.t3;
> +
> +connection con5;
> +
> +# Set the breakpoint for DDL in progress.
> +--echo con5: Getting lock on DDL in progress.
> +SELECT get_lock("DDL_blocked", 0);
> +
> +connection con2;
> +
> +--echo con2: Get a DDL going and stop in the middle
> +send ALTER TABLE bup_ddl_blocker.t1 ADD COLUMN col_b int;
> +
> +connection con3;
> +
> +--echo con3: Get a DDL going and stop in the middle
> +send ALTER TABLE test.t2 ADD COLUMN col_b int;
> +
> +connection con4;
> +
> +--echo con4: Get a DDL going and stop in the middle
> +send ALTER TABLE bup_ddl_blocker.t3 ADD COLUMN col_b int;
> +
> +connection con5;
> +
> +# Wait for lock to be acquired and execution to reach breakpoint
> +--echo con5: Checking locks
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "ALTER TABLE bup_ddl_blocker.t1%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "ALTER TABLE test.t2%";
> +--source include/wait_condition.inc
> +
> +let $wait_condition = SELECT state = "debug_sync_point: DDL_blocked"
> +                      FROM INFORMATION_SCHEMA.PROCESSLIST
> +                      WHERE info LIKE "ALTER TABLE bup_ddl_blocker.t3%";
> +--source include/wait_condition.inc
> +
> +--echo con5: Releasing lock
> +SELECT release_lock("DDL_blocked");
> +
> +# Reconnect to con2, con3, and con4 and allow them to finish.
> +
> +connection con2;
> +--echo con2: Completing DDL
> +reap;
> +
> +connection con3;
> +--echo con3: Completing DDL
> +reap;
> +
> +connection con4;
> +--echo con4: Completing DDL
> +reap;
> +
> +# Reconnect to con1 and show results
> +
> +connection con1;
> +
> +# Do selects to show that all changes got applied.
> +--echo con1: Showing columns after updates and backup
> +DESCRIBE bup_ddl_blocker.t1;
> +DESCRIBE test.t2;
> +DESCRIBE bup_ddl_blocker.t3;
> +
> +--echo
> +--echo Verifying test 6 results:
> +--echo
> +
> +--echo T1 should have the changes after backup - count(*) = 2
> +SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS
> +WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'bup_ddl_blocker';
> +
> +--echo T2 should the changes after backup - count(*) = 2
> +SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS
> +WHERE TABLE_NAME = 't2' AND TABLE_SCHEMA = 'test';
> +
> +--echo T3 should not have the changes after backup - count(*) = 2
> +SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS
> +WHERE TABLE_NAME = 't3' AND TABLE_SCHEMA = 'bup_ddl_blocker';
> +
> +--echo con1: Cleanup
> +DROP DATABASE bup_ddl_blocker;
> +DROP TABLE test.t2;
> +


> diff -Nrup a/sql/backup/sql_backup.cc b/sql/backup/sql_backup.cc
> --- a/sql/backup/sql_backup.cc	2007-10-17 16:20:36 -04:00
> +++ b/sql/backup/sql_backup.cc	2007-11-08 14:15:18 -05:00
> @@ -31,6 +31,8 @@
>  #include "be_default.h"
>  #include "be_snapshot.h"
>  
> +extern void block_DDL(my_bool block);
> +
>  namespace backup {
>  
>  // Helper functions
> @@ -59,6 +61,55 @@ bool test_error_flag= FALSE;
>  #endif
>  }
>  
> +extern pthread_mutex_t THR_LOCK_DDL_blocker;
> +extern pthread_cond_t COND_backup_blocked;
> +extern pthread_cond_t COND_DDL_blocker;
> +extern int DDL_blocks;
> +extern my_bool DDL_blocked;
> +
> +/**
> +   block_DDL
> +
> +   This method is used to block all DDL commands. It checks the counter
> +   DDL_blocks and if > 0 it blocks the backup until all DDL operations are
> +   complete and the condition variable has been signaled. 
> +
> +   The method also sets the boolean DDL_blocked to TRUE to tell the DDL
> +   operations that they must block until the backup operation is complete.
> +
> +   @params thd THD object.
> +   @returns TRUE
> +  */
> +my_bool block_DDL(THD *thd)
> +{
> +  DBUG_ENTER("check_DDL_blocked()");

Wrong name here...

> +  /*
> +    Check the ddl blocker condition. Rest until ddl blocker is released.
> +  */
> +  pthread_mutex_lock(&THR_LOCK_DDL_blocker);

I think that adding here the statement:

      if (DDL_blocked) return FALSE;

will make it possible to detect that other BACKUP/RESTORE operation is running 
and then abort the new one.

> +  thd->enter_cond(&COND_backup_blocked, &THR_LOCK_DDL_blocker,
> +                  "Online backup: Checking block on DDL changes");
> +  while (DDL_blocks != 0)
> +    pthread_cond_wait(&COND_backup_blocked, &THR_LOCK_DDL_blocker);
> +  DDL_blocked= TRUE;
> +  thd->exit_cond("Online backup: Ok to run operation - no DDL in progress");
> +  DBUG_RETURN(TRUE);
> +}
> +
> +/**
> +   unblock_DDL
> +
> +   This method is used to unblock all DDL commands. It sets the boolean
> +   DDL_blocked to FALSE to tell the DDL operations that they can proceed.
> +  */
> +void unblock_DDL()
> +{
> +  pthread_mutex_lock(&THR_LOCK_DDL_blocker);
> +  DDL_blocked= FALSE;
> +  pthread_cond_broadcast(&COND_DDL_blocker);
> +  pthread_mutex_unlock(&THR_LOCK_DDL_blocker);
> +}
> +
>  /**
>    Call backup kernel API to execute backup related SQL statement.
>  
> @@ -118,7 +169,15 @@ execute_backup_command(THD *thd, LEX *le
>        {
>          info.report_error(backup::log_level::INFO,ER_BACKUP_RESTORE_START);
>  
> -        // TODO: freeze all DDL operations here
> +        /*
> +           NOTE: This cannot be done here due to the limitations of the
> +           DDL blocker. The decision was to block all DDL operations but
> +           if all DDL operations are blocked, the restore cannot restore its
> +           own tables because...all DDLs are blocked! 
> +
> +           @todo Move the DDL blocker to here once the general solution has
> +                 been implemented.
> +        */
>  
>          info.save_errors();
>          info.restore_all_dbs();
> @@ -140,7 +199,12 @@ execute_backup_command(THD *thd, LEX *le
>            send_summary(thd,info);
>          }
>  
> -        // TODO: unfreeze DDL here
> +        /*
> +          Unfreeze all DDL operations by turning off DDL blocker.
> +        */
> +        unblock_DDL();
> +        BACKUP_SYNC("DDL_unblocked");
> +
>        }
>      } // if (!stream)
>  
> @@ -148,6 +212,12 @@ execute_backup_command(THD *thd, LEX *le
>      
>     restore_error:
>  
> +    /*
> +      Unfreeze all DDL operations by turning off DDL blocker.
> +    */
> +    unblock_DDL();
> +    BACKUP_SYNC("DDL_unblocked");
> +
>      res= res ? res : backup::ERROR;
>      
>     finish_restore:
> @@ -169,6 +239,19 @@ execute_backup_command(THD *thd, LEX *le
>      }
>      else
>      {
> +      /*
> +        Freeze all DDL operations by turning on DDL blocker.
> +
> +        Note: The block_ddl() call must occur before the information_schema
> +              is read so that any new tables (e.g. CREATE in progress) can
> +              be counted. Waiting until after this step caused backup to
> +              skip new or dropped tables.
> +      */

Good that you added this comment as otherwise, sonner or later, I'll move the 
DDL block further down...

> +      BACKUP_SYNC("DDL_in_progress");
> +      if (!block_DDL(thd))
> +        goto backup_error;
> +      BACKUP_SYNC("DDL_blocked");
> +
>        backup::Backup_info info(thd);
>  
>        if (check_info(thd,info))
> @@ -176,8 +259,6 @@ execute_backup_command(THD *thd, LEX *le
>  
>        info.report_error(backup::log_level::INFO,ER_BACKUP_BACKUP_START);
>  
> -      // TODO: freeze all DDL operations here
> -
>        info.save_errors();
>  
>        if (lex->db_list.is_empty())
> @@ -212,13 +293,22 @@ execute_backup_command(THD *thd, LEX *le
>          send_summary(thd,info);
>        }
>  
> -      // TODO: unfreeze DDL here
> +      /*
> +        Unfreeze all DDL operations by turning off DDL blocker.
> +      */
> +      unblock_DDL();
> +      BACKUP_SYNC("DDL_unblocked");
> +
>      } // if (!stream)
>  
>      goto finish_backup;
>     
>     backup_error:
>     
> +    /*
> +      Unfreeze all DDL operations by turning off DDL blocker.
> +    */
> +    unblock_DDL();
>      res= res ? res : backup::ERROR;
>     
>     finish_backup:
> @@ -313,6 +403,7 @@ int mysql_backup(THD *thd,
>  
>   error:
>  
> +  unblock_DDL();
>    DBUG_RETURN(backup::ERROR);
>  }
>  
> @@ -1058,10 +1149,25 @@ int mysql_restore(THD *thd, backup::Rest
>    if (backup::restore_meta_data(thd, info, s))
>      DBUG_RETURN(backup::ERROR);
>  
> +  /*
> +    Freeze all DDL operations by turning on DDL blocker.
> +
> +    @todo Move this back to execute_backup_command() once a generalized 
> +          solution is implemented.
> +  */
> +  BACKUP_SYNC("DDL_in_progress");
> +  if (!block_DDL(thd))
> +    DBUG_RETURN(backup::ERROR);
> +
> +  BACKUP_SYNC("DDL_blocked");
> +
>    DBUG_PRINT("restore",("Restoring table data"));
>  
>    if (backup::restore_table_data(thd,info,s))
> +  {
> +    unblock_DDL();
>      DBUG_RETURN(backup::ERROR);
> +  }

I think unblock_DDL() should be called also when restore_table_data() fails.

>  
>    DBUG_PRINT("restore",("Done."));
>  


> diff -Nrup a/sql/mysqld.cc b/sql/mysqld.cc
> --- a/sql/mysqld.cc	2007-10-17 09:57:33 -04:00
> +++ b/sql/mysqld.cc	2007-11-08 14:15:16 -05:00
> @@ -577,6 +577,16 @@ pthread_mutex_t LOCK_mysql_create_db, LO
>  		LOCK_crypt, LOCK_bytes_sent, LOCK_bytes_received,
>  	        LOCK_global_system_variables,
>  		LOCK_user_conn, LOCK_slave_list, LOCK_active_mi;
> +
> +/*
> +  These variables are used to implement the metadata freeze "DDL blocker"
> +  for online backup.
> +*/
> +pthread_mutex_t THR_LOCK_DDL_blocker; 
> +pthread_mutex_t THR_LOCK_DDL_is_blocked; 
> +pthread_cond_t COND_DDL_blocker;
> +pthread_cond_t COND_backup_blocked;
> +
>  /*
>    The below lock protects access to two global server variables:
>    max_prepared_stmt_count and prepared_stmt_count. These variables
> @@ -1335,6 +1345,10 @@ static void clean_up_mutexes()
>    (void) pthread_mutex_destroy(&LOCK_bytes_sent);
>    (void) pthread_mutex_destroy(&LOCK_bytes_received);
>    (void) pthread_mutex_destroy(&LOCK_user_conn);
> +  (void) pthread_mutex_destroy(&THR_LOCK_DDL_blocker);
> +  (void) pthread_mutex_destroy(&THR_LOCK_DDL_is_blocked);
> +  (void) pthread_cond_destroy(&COND_DDL_blocker);
> +  (void) pthread_cond_destroy(&COND_backup_blocked);
>    Events::destroy_mutexes();
>  #ifdef HAVE_OPENSSL
>    (void) pthread_mutex_destroy(&LOCK_des_key_file);
> @@ -3074,6 +3088,10 @@ static int init_thread_environment()
>    (void) pthread_mutex_init(&LOCK_global_read_lock, MY_MUTEX_INIT_FAST);
>    (void) pthread_mutex_init(&LOCK_prepared_stmt_count, MY_MUTEX_INIT_FAST);
>    (void) pthread_mutex_init(&LOCK_uuid_generator, MY_MUTEX_INIT_FAST);
> +  (void) pthread_mutex_init(&THR_LOCK_DDL_blocker, MY_MUTEX_INIT_FAST);
> +  (void) pthread_mutex_init(&THR_LOCK_DDL_is_blocked, MY_MUTEX_INIT_FAST);
> +  (void) pthread_cond_init(&COND_DDL_blocker, NULL);
> +  (void) pthread_cond_init(&COND_backup_blocked, NULL);
>  #ifdef HAVE_OPENSSL
>    (void) pthread_mutex_init(&LOCK_des_key_file,MY_MUTEX_INIT_FAST);
>  #ifndef HAVE_YASSL


> diff -Nrup a/sql/sql_parse.cc b/sql/sql_parse.cc
> --- a/sql/sql_parse.cc	2007-09-14 05:20:44 -04:00
> +++ b/sql/sql_parse.cc	2007-11-08 14:15:17 -05:00
> @@ -27,6 +27,7 @@
>  #include "sp_cache.h"
>  #include "events.h"
>  #include "sql_trigger.h"
> +#include "debug.h"
>  
>  /**
>    @defgroup Runtime_Environment Runtime Environment
> @@ -87,6 +88,68 @@ const char *xa_state_names[]={
>    "NON-EXISTING", "ACTIVE", "IDLE", "PREPARED"
>  };
>  
> +extern pthread_mutex_t THR_LOCK_DDL_blocker; 
> +extern pthread_mutex_t THR_LOCK_DDL_is_blocked; 
> +extern pthread_cond_t COND_DDL_blocker;
> +extern pthread_cond_t COND_backup_blocked;
> +my_bool DDL_blocked= FALSE;
> +int DDL_blocks= 0;
> +
> +/**
> +   block_backup()
> +
> +   Increments the backup's counter to indicate a DDL is in progress.
> +  */
> +static void block_backup()
> +{
> +  DBUG_ENTER("block_backup()");
> +  pthread_mutex_lock(&THR_LOCK_DDL_blocker);
> +  DDL_blocks++;
> +  pthread_mutex_unlock(&THR_LOCK_DDL_blocker);
> +  DBUG_VOID_RETURN;
> +}
> +
> +/**
> +   unblock_backup()
> +
> +   Decrements the backup's counter to indicate a DDL is done.
> +   Signals backup process if counter == 0.
> +  */
> +static void unblock_backup()
> +{
> +  DBUG_ENTER("unblock_backup()");
> +  pthread_mutex_lock(&THR_LOCK_DDL_blocker);
> +  DDL_blocks--;

This code calls for troubles in case it is executed when DDL_blocks == 0 (or <= 
0). I'd guard it with "if (DDL_blocks > 0)" to be on the safe side.

> +  if (DDL_blocks == 0)
> +    pthread_cond_broadcast(&COND_backup_blocked);
> +  pthread_mutex_unlock(&THR_LOCK_DDL_blocker);
> +  DBUG_VOID_RETURN;
> +}
> +
> +/**
> +    check_ddl_blocker
> +
> +    Check to see if we are blocked from continuing. If so,
> +    wait until the backup process signals the condition.
> +
> +    @param thd The THD object from the caller.
> +    @returns TRUE
> +  */
> +static my_bool check_DDL_blocker(THD *thd)
> +{
> +  DBUG_ENTER("check_DDL_blocker()");
> +  /*
> +    Check the ddl blocker condition. Rest until ddl blocker is released.
> +  */
> +  pthread_mutex_lock(&THR_LOCK_DDL_is_blocked);
> +  thd->enter_cond(&COND_DDL_blocker, &THR_LOCK_DDL_is_blocked,
> +                  "DDL blocker: Checking block on DDL changes");
> +  while (DDL_blocked)
> +    pthread_cond_wait(&COND_DDL_blocker, &THR_LOCK_DDL_is_blocked);
> +  block_backup();
> +  thd->exit_cond("DDL blocker: Ok to run DDL");
> +  DBUG_RETURN(TRUE);
> +}
>  
>  static void unlock_locked_tables(THD *thd)
>  {

SELECT CREATE
> @@ -2138,6 +2201,9 @@ mysql_execute_command(THD *thd)
>            }
>          }
>  
> +        BACKUP_SYNC("DDL_blocked");
> +        check_DDL_blocker(thd);
> +        BACKUP_SYNC("DDL_in_progress");
>          /*
>            select_create is currently not re-execution friendly and
>            needs to be created for every execution of a PS/SP.
> @@ -2157,6 +2223,7 @@ mysql_execute_command(THD *thd)
>            res= handle_select(thd, lex, result, 0);
>            delete result;
>          }
> +        unblock_backup();
>        }
>        else if (!(create_info.options & HA_LEX_CREATE_TMP_TABLE))
>          create_table= lex->unlink_first_table(&link_to_local);

CREATE TABLE
> @@ -2164,6 +2231,9 @@ mysql_execute_command(THD *thd)
>      }
>      else
>      {
> +      BACKUP_SYNC("DDL_blocked");
> +      check_DDL_blocker(thd);
> +      BACKUP_SYNC("DDL_in_progress");
>        /* So that CREATE TEMPORARY TABLE gets to binlog at commit/rollback */
>        if (create_info.options & HA_LEX_CREATE_TMP_TABLE)
>          thd->options|= OPTION_KEEP_LOG;
> @@ -2177,6 +2247,7 @@ mysql_execute_command(THD *thd)
>                                  create_table->table_name, &create_info,
>                                  &alter_info, 0, 0);
>        }
> +      unblock_backup();
>        if (!res)
>  	send_ok(thd);
>      }

ALTER TABLE
> @@ -2331,6 +2402,9 @@ end_with_restore_list:
>        }
>  
>        thd->enable_slow_log= opt_log_slow_admin_statements;
> +      BACKUP_SYNC("DDL_blocked");
> +      check_DDL_blocker(thd);
> +      BACKUP_SYNC("DDL_in_progress");
>        res= mysql_alter_table(thd, select_lex->db, lex->name.str,
>                               &create_info,
>                               first_table,
> @@ -2338,6 +2412,7 @@ end_with_restore_list:
>                               select_lex->order_list.elements,
>                               (ORDER *) select_lex->order_list.first,
>                               lex->ignore);
> +      unblock_backup();
>        break;
>      }
>    case SQLCOM_RENAME_TABLE:

RENAME TABLE
> @@ -2366,8 +2441,15 @@ end_with_restore_list:
>          goto error;
>      }
>  
> +      BACKUP_SYNC("DDL_blocked");
> +      check_DDL_blocker(thd);
> +      BACKUP_SYNC("DDL_in_progress");
>      if (end_active_trans(thd) || mysql_rename_tables(thd, first_table, 0))
> -      goto error;
> +      {
> +        unblock_backup();
> +        goto error;
> +      }
> +      unblock_backup();
>      break;
>    }
>  #ifndef EMBEDDED_LIBRARY

REPAIR TABLE
> @@ -2417,7 +2499,11 @@ end_with_restore_list:
>      if (check_table_access(thd, SELECT_ACL | INSERT_ACL, all_tables, 0))
>        goto error; /* purecov: inspected */
>      thd->enable_slow_log= opt_log_slow_admin_statements;
> +    BACKUP_SYNC("DDL_blocked");
> +    check_DDL_blocker(thd);
> +    BACKUP_SYNC("DDL_in_progress");
>      res= mysql_repair_table(thd, first_table, &lex->check_opt);
> +    unblock_backup();

One day I'd like to ask Guilhem why REPAIR table must be blocked during backup. 
I'd like to believe that from the point of view of the kernel it is OK to repair 
or optimize tables during ongoing backup (it might be problematic with 
restores). Then, if MyISAM or default drivers have problem with this they could 
deal with this problem internally (and more optimally).

>      /* ! we write after unlocking the table */
>      if (!res && !lex->no_write_to_binlog)
>      {

OTIMIZE TABLE
> @@ -2467,9 +2553,13 @@ end_with_restore_list:
>      if (check_table_access(thd, SELECT_ACL | INSERT_ACL, all_tables, 0))
>        goto error; /* purecov: inspected */
>      thd->enable_slow_log= opt_log_slow_admin_statements;
> +    BACKUP_SYNC("DDL_blocked");
> +    check_DDL_blocker(thd);
> +    BACKUP_SYNC("DDL_in_progress");
>      res= (specialflag & (SPECIAL_SAFE_MODE | SPECIAL_NO_NEW_FUNC)) ?
>        mysql_recreate_table(thd, first_table) :
>        mysql_optimize_table(thd, first_table, &lex->check_opt);
> +    unblock_backup();

I wonder if we can unblock before calling mysql_optimize_table() - after all it 
shouldn't drop or alter the table. Just asking myself - for now let's play it 
safe and leave it here.

>      /* ! we write after unlocking the table */
>      if (!res && !lex->no_write_to_binlog)
>      {

DROP TABLE
> @@ -2811,9 +2901,13 @@ end_with_restore_list:
>        /* So that DROP TEMPORARY TABLE gets to binlog at commit/rollback */
>        thd->options|= OPTION_KEEP_LOG;
>      }
> +      BACKUP_SYNC("DDL_blocked");
> +      check_DDL_blocker(thd);
> +      BACKUP_SYNC("DDL_in_progress");
>      /* DDL and binlog write order protected by LOCK_open */
>      res= mysql_rm_table(thd, first_table, lex->drop_if_exists,
>  			lex->drop_temporary);
> +      unblock_backup();
>    }
>    break;
>    case SQLCOM_SHOW_PROCESSLIST:

CREATE DB
> @@ -3040,8 +3134,12 @@ end_with_restore_list:
>      if (check_access(thd,CREATE_ACL,lex->name.str, 0, 1, 0,
>                       is_schema_db(lex->name.str)))
>        break;
> +    BACKUP_SYNC("DDL_blocked");
> +    check_DDL_blocker(thd);
> +    BACKUP_SYNC("DDL_in_progress");
>      res= mysql_create_db(thd,(lower_case_table_names == 2 ? alias :
>                                lex->name.str), &create_info, 0);
> +    unblock_backup();
>      break;
>    }
>    case SQLCOM_DROP_DB:

DROP DB
> @@ -3081,7 +3179,11 @@ end_with_restore_list:
>                   ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));
>        goto error;
>      }
> +    BACKUP_SYNC("DDL_blocked");
> +    check_DDL_blocker(thd);
> +    BACKUP_SYNC("DDL_in_progress");
>      res= mysql_rm_db(thd, lex->name.str, lex->drop_if_exists, 0);
> +    unblock_backup();
>      break;
>    }
>    case SQLCOM_RENAME_DB:

RENAME DB
> @@ -3126,13 +3228,20 @@ end_with_restore_list:
>                   ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));
>        goto error;
>      }
> +    BACKUP_SYNC("DDL_blocked");
> +    check_DDL_blocker(thd);
> +    BACKUP_SYNC("DDL_in_progress");
>      res= mysql_rename_db(thd, olddb, newdb);
> +    unblock_backup();
>      if (!res)
>        send_ok(thd);
>      break;
>    }
>    case SQLCOM_ALTER_DB:
>    {
> +    BACKUP_SYNC("DDL_blocked");
> +    check_DDL_blocker(thd);
> +    BACKUP_SYNC("DDL_in_progress");
>      LEX_STRING *db= &lex->name;
>      HA_CREATE_INFO create_info(lex->create_info);
>      if (check_db_name(db))
> @@ -3165,6 +3274,7 @@ end_with_restore_list:
>        goto error;
>      }
>      res= mysql_alter_db(thd, db->str, &create_info);
> +    unblock_backup();
>      break;
>    }
>    case SQLCOM_SHOW_CREATE_DB:
> 
> 

Rafal
Thread
bk commit into 5.2 tree (cbell:1.2610)cbell8 Nov
  • Re: bk commit into 5.2 tree (cbell:1.2610)Rafal Somla13 Nov
    • RE: bk commit into 5.2 tree (cbell:1.2610)Chuck Bell14 Nov