Rafal,
Here's the new patch: http://lists.mysql.com/commits/37727.
I was not able to refactor the test as you suggested. I had numerous
problems getting something like "send $str" to work. In fact, it doesn't
work. So I wasn't able to parameterize chunks of the test. Also, each
statement behaves slightly differently so you will see minor differences
amongst tests. As a result, the test grew in size. The test for restore is
included too. Maybe Serge can do something with it...
I addressed the other things in the code.
It's 0130 for me. I'll be in Wednesday as soon as I can roll my tired butt
out of bed.
Chuck
> -----Original Message-----
> From: Rafal Somla [mailto:rsomla@stripped]
> Sent: Tuesday, November 13, 2007 5:01 AM
> To: cbell@stripped
> Cc: commits@stripped; Lars Thalmann
> Subject: Re: bk commit into 5.2 tree (cbell:1.2610)
>
> 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
>
> --
> MySQL Code Commits Mailing List
> For list archives: http://lists.mysql.com/commits
> To unsubscribe:
> http://lists.mysql.com/commits?unsub=1
>
