Merge lp:~akopytov/percona-server/backup-locks into lp:percona-server/5.6

[Alexey Kopytov]

    Implementation of
    https://blueprints.launchpad.net/percona-server/+spec/backup-locks

    The patch introduces 3 new SQL statements, LOCK TABLES FOR BACKUP, LOCK
    BINLOG FOR BACKUP and UNLOCK BINLOG, that can be used as a lightweight
    alternative to FLUSH TABLES WITH READ LOCK for both physical and logical
    backups.

    LOCK TABLES FOR BACKUP uses a new MDL lock type to block updates to
    all non-transactional tables and DDL statements for all tables. More
    specifically, if there’s an active LOCK TABLES FOR BACKUP lock, all
    DDL statements and all updates to MyISAM, CSV, MEMORY and ARCHIVE
    tables will be blocked in the “Waiting for backup lock” status as
    seen in PERFORMANCE_SCHEMA or PROCESSLIST. SELECT queries for all
    tables and INSERT/UPDATE/DELETE against InnoDB, Blackhole and
    Federated tables are not affected by LOCK TABLES FOR BACKUP. UNLOCK
    TABLES releases the lock acquired by LOCK TABLES FOR BACKUP.

    LOCK BINLOG FOR BACKUP uses another MDL lock type to block all
    operations that might change either binary log position or
    Exec_Master_Log_Pos (i.e. master binary log position corresponding
    to the current SQL thread state on a replication slave) as reported
    by SHOW MASTER/SLAVE STATUS. More specifically, a commit will only
    be blocked if the binary log is enabled (both globally, and for
    connection with sql_log_bin), or if commit is performed by a slave
    thread and would advance Exec_Master_Log_Pos. Connections that are
    currently blocked on the global binlog lock can be identified by the
    “Waiting for binlog lock” status in PROCESSLIST.

    UNLOCK BINLOG releases the LOCK BINLOG FOR BACKUP lock, if acquired by
    the current connection. The intended use case for XtraBackup is:

    LOCK TABLES FOR BACKUP
    ... copy .frm, MyISAM, CSV, etc. ...
    LOCK BINLOG FOR BACKUP
    UNLOCK TABLES
    ... get binlog coordinates ...
    ... wait for redo log copying to finish ...
    UNLOCK BINLOG

    Both LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP have no effect if
    the current connection already owns a FTWRL lock, as it’s a more
    restrictive lock. If FTWRL is executed in a connection that has acquired
    LTFB or LBFB, FTWRL fails with an error.

    mysqldump has also been extended with a new option,
    --lock-for-backup (disabled by default). When used together with the
    --single-transaction option, the option makes mysqldump issue LOCK
    TABLES FOR BACKUP before starting the dump operation to prevent
    unsafe statements that would normally result in an inconsistent
    backup. When used without the --single-transaction option,
    --lock-for-backup is automatically converted to
    --lock-all-tables. --lock-for-backup is mutually exclusive with
    --lock-all-tables, i.e. specifying both on the command line will
    lead to an error. If the backup locks feature is not supported by
    the target server, but --lock-for-backup is specified on the command
    line, mysqldump aborts with an error.

[Alexey Kopytov (akopytov)]

http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/522/

[Alexey Kopytov (akopytov)]

Removed a few spurious changes, added missing code comments, updated revision comments.

[Laurynas Biveinis (laurynas-biveinis)]

    If time is short, this may go in as-is and the review comments can
    be addressed in a follow-up as needed.

    core:

    - Is the binlogging of "ROLLBACK" statement properly protected,
      i.e. does the ordered_commit call in MYSQL_BIN_LOG::rollback
      need the protection too? The testcase would be: start
      transaction, write to both InnoDB and MyISAM tables, ROLLBACK.

    - I was not able to figure out why do we need binlog lock
      protection in Rotate_log_event::do_update_pos?

    - Nor I was able to figure out why dispatch_command added

@@ -1877,6 +1877,14 @@

   THD_STAGE_INFO(thd, stage_cleaning_up);

+ if (thd->killed == THD::KILL_QUERY ||
+ thd->killed == THD::KILL_TIMEOUT ||
+ thd->killed == THD::KILL_BAD_DATA)
+ {
+ thd->killed= THD::NOT_KILLED;
+ thd->mysys_var->abort= 0;
+ }
+
   thd->reset_query();
   thd->set_command(COM_SLEEP);

    - Dead store error= 1 in the case of
      backup_binlog_lock.acquire_protection failure.

    - Indentation error at sql_parse.cc line 4233

    parser:

    - The parser changes cause "unlock" as a statement label to be
      treated as ER_PARSE_ERROR instead ER_SP_BADSTATEMENT, as
      recorded in funcs_1/storedproc. Which leaves the if
      (lex->sphead) ... in unlock symbol rule as dead code. Is that
      intentional? It is possible to avoid this by rewriting it as

unlock:
          UNLOCK_SYM
          {
            LEX *lex= Lex;

            if (lex->sphead)
            {
              my_error(ER_SP_BADSTATEMENT, MYF(0), "UNLOCK");
              MYSQL_YYABORT;
            }
          }
          unlock_variant
          {}
 ;

unlock_variant:
          BINLOG_SYM
          {
            Lex->sql_command= SQLCOM_UNLOCK_BINLOG;
          }
        | table_or_tables
          {
            Lex->sql_command= SQLCOM_UNLOCK_TABLES;
          }
        ;

      which is also a bit more localized change against trunk.

    mysqldump:

    - might be good to include a note in docs that --lock-for-backup
      always disables --lock-tables (by --single-transaction if
      specified or by --lock-for-backup converted to --lock-all-tables
      otherwise).

    - It looks like mysqldump will issue an unbalanced UNLOCK TABLES
      if --single-transaction is the only option given. This appears
      to be benign and pre-existing.

    MTR:

    - possible to do s/STOP SLAVE;--source
      include/wait_for_slave_to_stop.inc/--source
      include/stop_slave.inc, likewise for START SLAVE, unless for
      some reason you prefer the current form?

    - It is of course true that there are many possible test
      combinations that could be added to the testcases. I tried to
      list a selected few that are single-threaded and IMHO required:

    - Need a test that checks for a LTFB/LBFB failure if issued by a
      user without RELOAD privilege.

    - Need a test for crash-safe slave: slave server binlog on, log
      slave updates off, --master-info-repository=TABLE,
      --relay-log-info-repository=TABLE, LOCK BINLOG on the slave,
      verify that updates from the master are not blocked.

    - I was not able to find a test where a single connection w...

review: Needs Information

[Alexey Kopytov (akopytov)]

> Review: Needs Information
>
> If time is short, this may go in as-is and the review comments can
> be addressed in a follow-up as needed.
>
> core:
>
> - Is the binlogging of "ROLLBACK" statement properly protected,
> i.e. does the ordered_commit call in MYSQL_BIN_LOG::rollback
> need the protection too? The testcase would be: start
> transaction, write to both InnoDB and MyISAM tables, ROLLBACK.
>

No, ROLLBACK does not need protection for the same reasons as FTWRL
only blocks COMMITs, but not ROLLBACKs.

> - I was not able to figure out why do we need binlog lock
> protection in Rotate_log_event::do_update_pos?
>

Because it updates master binary coordinates via
Relay_log_info::inc_group_relay_log_pos().

> - Nor I was able to figure out why dispatch_command added
>
> @@ -1877,6 +1877,14 @@
>
> THD_STAGE_INFO(thd, stage_cleaning_up);
>
> + if (thd->killed == THD::KILL_QUERY ||
> + thd->killed == THD::KILL_TIMEOUT ||
> + thd->killed == THD::KILL_BAD_DATA)
> + {
> + thd->killed= THD::NOT_KILLED;
> + thd->mysys_var->abort= 0;
> + }
> +
> thd->reset_query();
> thd->set_command(COM_SLEEP);
>

That is required to properly handle errors in situations when a COMMIT is
waiting on a binlog lock and that fails for whatever reasons.

> - Dead store error= 1 in the case of
> backup_binlog_lock.acquire_protection failure.

Removed.

>
> - Indentation error at sql_parse.cc line 4233
>

Fixed.

> parser:
>
> - The parser changes cause "unlock" as a statement label to be
> treated as ER_PARSE_ERROR instead ER_SP_BADSTATEMENT, as
> recorded in funcs_1/storedproc. Which leaves the if
> (lex->sphead) ... in unlock symbol rule as dead code. Is that
> intentional? It is possible to avoid this by rewriting it as
>

That is not dead code. That code prevents UNLOCK TABLES/BINLOG from
inclusion into stored procedures. In fact, the current behavior re:
unlock as a statement label is more correct. It is indeed not a
statement that is not allowed in SP, but a genuine parse error.

[...]

> MTR:
>
> - possible to do s/STOP SLAVE;--source
> include/wait_for_slave_to_stop.inc/--source
> include/stop_slave.inc, likewise for START SLAVE, unless for
> some reason you prefer the current form?
>

For my purposes, both would be exactly the same sequence of commands
executed by MTR.

> - It is of course true that there are many possible test
> combinations that could be added to the testcases. I tried to
> list a selected few that are single-threaded and IMHO required:
>

I have all of that + many more items in my list of things that would be
nice to test. I simply don’t have time for that ATM. Feel free to create
a QA request, and I will chime in with more suggestions.

--
Best regards,
Alexey.

[Laurynas Biveinis (laurynas-biveinis)]

> I have all of that + many more items in my list of things that would be
> nice to test. I simply don’t have time for that ATM. Feel free to create
> a QA request, and I will chime in with more suggestions.

OK: https://blueprints.launchpad.net/percona-server/+spec/backup-locks-mtr

=== modified file 'client/client_priv.h'
--- client/client_priv.h	2013-06-25 13:13:06 +0000
+++ client/client_priv.h	2014-02-20 11:26:00 +0000
@@ -104,6 +104,7 @@
   OPT_SERVER_PUBLIC_KEY,
   OPT_ENABLE_CLEARTEXT_PLUGIN,
   OPT_INNODB_OPTIMIZE_KEYS,
+  OPT_LOCK_FOR_BACKUP,
   OPT_MAX_CLIENT_OPTION
 };
 

=== modified file 'client/mysqldump.c'
--- client/mysqldump.c	2013-12-16 08:45:31 +0000
+++ client/mysqldump.c	2014-02-20 11:26:00 +0000
@@ -143,6 +143,7 @@
 static char * opt_mysql_unix_port=0;
 static char *opt_bind_addr = NULL;
 static int   first_error=0;
+static uint opt_lock_for_backup= 0;
 static DYNAMIC_STRING extended_row;
 #include <sslopt-vars.h>
 FILE *md_result_file= 0;
@@ -247,6 +248,11 @@
    "Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START SLAVE' to bottom of dump.",
    &opt_slave_apply, &opt_slave_apply, 0, GET_BOOL, NO_ARG,
    0, 0, 0, 0, 0, 0},
+  {"lock-for-backup", OPT_LOCK_FOR_BACKUP, "Use lightweight metadata locks "
+   "to block updates to non-transactional tables and DDL to all tables. "
+   "This works only with --single-transaction, otherwise this option is "
+   "automatically converted to --lock-all-tables.", &opt_lock_for_backup,
+   &opt_lock_for_backup, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
   {"bind-address", 0, "IP address to bind to.",
    (uchar**) &opt_bind_addr, (uchar**) &opt_bind_addr, 0, GET_STR,
    REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
@@ -995,6 +1001,23 @@
     return(EX_USAGE);
   }
 
+  if (opt_lock_for_backup && opt_lock_all_tables)
+  {
+    fprintf(stderr, "%s: You can't use --lock-for-backup and "
+            "--lock-all-tables at the same time.\n", my_progname);
+    return(EX_USAGE);
+  }
+
+  /*
+     Convert --lock-for-backup to --lock-all-tables if --single-transaction is
+     not specified.
+  */
+  if (!opt_single_transaction && opt_lock_for_backup)
+  {
+    opt_lock_all_tables= 1;
+    opt_lock_for_backup= 0;
+  }
+
   /* We don't delete master logs if slave data option */
   if (opt_slave_data)
   {
@@ -5390,6 +5413,20 @@
                                     "FLUSH TABLES WITH READ LOCK") );
 }
 
+/**
+   Execute LOCK TABLES FOR BACKUP if supported by the server.
+
+   @note If LOCK TABLES FOR BACKUP is not supported by the server, then nothing
+         is done and no error condition is returned.
+
+   @returns  whether there was an error or not
+*/
+
+static int do_lock_tables_for_backup(MYSQL *mysql_con)
+{
+  return mysql_query_with_error_report(mysql_con, 0,
+                                       "LOCK TABLES FOR BACKUP");
+}
 
 static int do_unlock_tables(MYSQL *mysql_con)
 {
@@ -6153,6 +6190,35 @@
     die(EX_MYSQLERR, DYNAMIC_STR_ERROR_MSG);
 }
 
+/**
+   Check if the server supports LOCK TABLES FOR BACKUP.
+
+   @returns  TRUE if there is support, FALSE otherwise.
+*/
+
+static my_bool server_supports_backup_locks(void)
+{
+  MYSQL_RES *res;
+  MYSQL_ROW row;
+  my_bool rc;
+
+  if (mysql_query_with_error_report(mysql, &res,
+                                    "SHOW VARIABLES LIKE 'have_backup_locks'"))
+    return FALSE;
+
+  if ((row= mysql_fetch_row(res)) == NULL)
+  {
+    mysql_free_result(res);
+    return FALSE;
+  }
+
+  rc= mysql_num_fields(res) > 1 && !strcmp(row[1], "YES");
+
+  mysql_free_result(res);
+
+  return rc;
+}
+
 
 int main(int argc, char **argv)
 {
@@ -6194,12 +6260,25 @@
   if (!path)
     write_header(md_result_file, *argv);
 
+  if (opt_lock_for_backup && !server_supports_backup_locks())
+  {
+    fprintf(stderr, "%s: Error: --lock-for-backup was specified with "
+            "--single-transaction, but the server does not support "
+            "LOCK TABLES FOR BACKUP.\n",
+            my_progname);
+    goto err;
+  }
+
   if (opt_slave_data && do_stop_slave_sql(mysql))
     goto err;
 
   if ((opt_lock_all_tables || opt_master_data ||
-       (opt_single_transaction && flush_logs)) &&
-      do_flush_tables_read_lock(mysql))
+       (opt_single_transaction && flush_logs)))
+  {
+    if (do_flush_tables_read_lock(mysql))
+      goto err;
+  }
+  else if (opt_lock_for_backup && do_lock_tables_for_backup(mysql))
     goto err;
 
   /*
@@ -6244,7 +6323,8 @@
     goto err;
   if (opt_slave_data && do_show_slave_status(mysql))
     goto err;
-  if (opt_single_transaction && do_unlock_tables(mysql)) /* unlock but no commit! */
+  if (opt_single_transaction && (!opt_lock_for_backup || opt_master_data) &&
+      do_unlock_tables(mysql))                  /* unlock but no commit! */
     goto err;
 
   if (opt_alltspcs)

=== added file 'mysql-test/include/backup_locks_binlog.inc'
--- mysql-test/include/backup_locks_binlog.inc	1970-01-01 00:00:00 +0000
+++ mysql-test/include/backup_locks_binlog.inc	2014-02-20 11:26:00 +0000
@@ -0,0 +1,803 @@
+########################################################################
+# Binlog-related backup locks tests
+########################################################################
+
+--source include/have_innodb.inc
+--source include/not_embedded.inc
+--source include/have_archive.inc
+--source include/have_csv.inc
+--source include/have_blackhole.inc
+--source include/have_log_bin.inc
+
+SELECT @@binlog_format;
+
+RESET MASTER;
+
+SHOW VARIABLES LIKE 'have_backup_locks';
+
+--echo #-----------------------------------------------------------------------
+--echo # Setup
+--echo #-----------------------------------------------------------------------
+
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
+CREATE VIEW v_memory AS SELECT * FROM t_memory;
+CREATE VIEW v_csv AS SELECT * FROM t_csv;
+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
+CREATE VIEW v_archive AS SELECT * FROM t_archive;
+
+--echo #-----------------------------------------------------------------------
+--echo # Single-threaded tests
+--echo #-----------------------------------------------------------------------
+
+#
+# LBFB + DML/DDL in the same connection
+#
+
+LOCK BINLOG FOR BACKUP;
+
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+HANDLER t_innodb CLOSE;
+
+DELETE FROM t_myisam;
+INSERT INTO t_myisam VALUES(0);
+UPDATE t_myisam SET a = 1;
+REPLACE INTO t_myisam VALUES(1);
+SELECT * from t_myisam;
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+HANDLER t_myisam CLOSE;
+
+DELETE FROM t_memory;
+INSERT INTO t_memory VALUES(0);
+UPDATE t_memory SET a = 1;
+REPLACE INTO t_memory VALUES(1);
+SELECT * from t_memory;
+
+DELETE FROM t_archive;
+INSERT INTO t_archive VALUES(0);
+SELECT * from t_archive;
+
+DELETE FROM t_csv;
+INSERT INTO t_csv VALUES(0);
+UPDATE t_csv SET a = 1;
+REPLACE INTO t_csv VALUES(1);
+SELECT * from t_csv;
+
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * from t_blackhole;
+
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+DROP TABLE tt_innodb;
+DROP TABLE tt_myisam;
+DROP TABLE tt_memory;
+DROP TABLE tt_csv;
+DROP TABLE tt_blackhole;
+DROP TABLE tt_archive;
+
+--error ER_BAD_TABLE_ERROR
+DROP TABLE non_existing;
+
+TRUNCATE TABLE t_innodb;
+TRUNCATE TABLE t_myisam;
+TRUNCATE TABLE t_memory;
+TRUNCATE TABLE t_csv;
+TRUNCATE TABLE t_blackhole;
+
+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
+
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_innodb DROP COLUMN b;
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_myisam DROP COLUMN b;
+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_memory DROP COLUMN b;
+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_csv DROP COLUMN b;
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_blackhole DROP COLUMN b;
+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_archive DROP COLUMN b;
+
+ALTER TABLE t_innodb ADD KEY tmp (a);
+ALTER TABLE t_innodb DROP KEY tmp;
+ALTER TABLE t_myisam ADD KEY tmp (a);
+ALTER TABLE t_myisam DROP KEY tmp;
+ALTER TABLE t_memory ADD KEY tmp (a);
+ALTER TABLE t_memory DROP KEY tmp;
+ALTER TABLE t_blackhole ADD KEY tmp (a);
+ALTER TABLE t_blackhole DROP KEY tmp;
+
+CREATE DATABASE test1;
+DROP DATABASE test1;
+
+delimiter |;
+
+CREATE PROCEDURE p1()
+BEGIN
+  SELECT 1;
+END|
+
+DROP PROCEDURE p1|
+
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+  RETURN 1;
+END|
+
+DROP FUNCTION f1|
+
+delimiter ;|
+
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+DROP VIEW v1;
+
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+INSERT INTO v_csv VALUES(1);
+INSERT INTO v_memory VALUES(1);
+INSERT INTO v_archive VALUES(1);
+
+UNLOCK BINLOG;
+
+#
+# Test incompatibility with delay_key_write=ALL
+#
+SELECT @@delay_key_write;
+
+SET GLOBAL delay_key_write=ALL;
+--error ER_OPTION_PREVENTS_STATEMENT
+LOCK TABLES FOR BACKUP;
+
+SET GLOBAL delay_key_write=default;
+SELECT @@delay_key_write;
+
+#
+# read-only=1 + backup locks
+#
+
+SET GLOBAL read_only=1;
+
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+
+LOCK TABLES FOR BACKUP;
+
+INSERT INTO t_innodb VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO t_myisam VALUES(0);
+
+UNLOCK TABLES;
+
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+
+SET GLOBAL read_only=0;
+
+#
+# Test that temporary tables are not affected by backup locks
+#
+LOCK TABLES FOR BACKUP;
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+
+LOCK BINLOG FOR BACKUP;
+
+START TRANSACTION;
+SELECT * FROM tt_archive;
+SELECT * FROM tt_blackhole;
+SELECT * FROM tt_memory;
+SELECT * FROM tt_innodb;
+SELECT * FROM tt_myisam;
+INSERT INTO tt_innodb VALUES(6);
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_memory VALUES(6);
+INSERT INTO tt_csv VALUES(6);
+INSERT INTO tt_blackhole VALUES(6);
+INSERT INTO tt_archive VALUES(6);
+COMMIT;
+
+SELECT * FROM tt_archive;
+SELECT * FROM tt_blackhole;
+SELECT * FROM tt_memory;
+SELECT * FROM tt_innodb;
+SELECT * FROM tt_myisam;
+
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+
+UNLOCK BINLOG;
+
+UNLOCK TABLES;
+
+#
+# test that AUTOCOMMIT updates succeed with a BINLOG lock in the same connection with binlog disabled
+#
+
+SELECT @@log_bin;
+
+LOCK BINLOG FOR BACKUP;
+
+INSERT INTO t_innodb VALUES(1);
+INSERT INTO t_myisam VALUES(1);
+
+UNLOCK BINLOG;
+
+#
+# Log tables
+#
+
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET @old_long_query_time = @@SESSION.long_query_time;
+
+SET SESSION long_query_time = 0;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+
+LOCK TABLES FOR BACKUP;
+
+SELECT 1;
+
+LOCK BINLOG FOR BACKUP;
+
+SELECT 1;
+
+UNLOCK BINLOG;
+
+UNLOCK TABLES;
+
+SET SESSION long_query_time = @old_long_query_time;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+
+--echo #-----------------------------------------------------------------------
+--echo # Multi-threaded tests
+--echo #-----------------------------------------------------------------------
+
+--echo # connection default
+
+CREATE USER user@localhost;
+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
+
+--connect(con1,localhost,root,,)
+
+--connect(con2,localhost,user,,)
+
+--connection default
+LOCK TABLES FOR BACKUP;
+
+--connection con1
+--echo # connection con1
+
+SET SESSION lock_wait_timeout = 1;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SET GLOBAL delay_key_write=ALL;
+
+SET SESSION lock_wait_timeout = DEFAULT;
+
+--connection default
+--echo # connection default
+UNLOCK TABLES;
+
+#
+# read-only=1 + backup locks
+#
+
+--connection con2
+--echo # connection con2
+
+START TRANSACTION;
+INSERT INTO t_innodb VALUES(0);
+
+--connection default
+--echo # connection default
+
+SET GLOBAL read_only=1;
+
+--connection con2
+--echo # connection con2
+
+--error ER_OPTION_PREVENTS_STATEMENT
+COMMIT;
+
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_innodb VALUES(0);
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_myisam VALUES(0);
+
+--connection con1
+--echo # connection con1
+
+LOCK TABLES FOR BACKUP;
+
+--connection con2
+--echo # connection con2
+
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_innodb VALUES(0);
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_myisam VALUES(0);
+
+--connection default
+--echo # connection default
+
+SET GLOBAL read_only=0;
+
+--connection con2
+--echo # connection con2
+
+SET SESSION lock_wait_timeout = 1;
+
+INSERT INTO t_innodb VALUES(0);
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_myisam VALUES(0);
+
+SET SESSION lock_wait_timeout = 0;
+
+--connection default
+--echo # connection default
+
+SET GLOBAL read_only=1;
+
+--connection con2
+--echo # connection con2
+
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_innodb VALUES(0);
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_myisam VALUES(0);
+
+--connection con1
+--echo # connection con1
+
+UNLOCK TABLES;
+
+--connection con2
+--echo # connection con2
+
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_innodb VALUES(0);
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_myisam VALUES(0);
+
+
+--connection default
+--echo # connection default
+
+SET GLOBAL read_only=0;
+
+#
+# Test that temporary tables are not affected by tables lock, but updates that
+# would write into the binary log are blocked by the binlog lock.
+#
+--connection default
+--echo # connection default
+LOCK TABLES FOR BACKUP;
+
+--connection con1
+--echo # connection con1
+
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+
+--connection default
+--echo # connection default
+LOCK BINLOG FOR BACKUP;
+
+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+
+--connection con1
+--echo # connection con1
+
+SET SESSION lock_wait_timeout = 1;
+
+START TRANSACTION;
+if (`SELECT @@binlog_format = 'STATEMENT'`)
+{
+   --error ER_LOCK_WAIT_TIMEOUT
+   INSERT INTO tt_myisam VALUES(6);
+}
+if (!`SELECT @@binlog_format = 'STATEMENT'`)
+{
+  INSERT INTO tt_myisam VALUES(6);
+}
+
+# Updates to temporary tables are not binlogged in the ROW mode
+INSERT INTO tt_innodb VALUES(6);
+SELECT * FROM tt_archive;
+SELECT * FROM tt_blackhole;
+SELECT * FROM tt_memory;
+SELECT * FROM tt_innodb;
+SELECT * FROM tt_myisam;
+SELECT * FROM tt_csv;
+if (`SELECT @@binlog_format = 'STATEMENT'`)
+{
+   --error ER_LOCK_WAIT_TIMEOUT
+   COMMIT;
+if (!`SELECT @@binlog_format = 'STATEMENT'`)
+{
+   COMMIT;
+}
+}
+
+--connection default
+--echo # connection default
+
+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+--disable_query_log
+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
+--enable_query_log
+
+UNLOCK BINLOG;
+UNLOCK TABLES;
+
+--connection con1
+--echo # connection con1
+
+COMMIT;
+
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+
+--connection default
+--echo # connection default
+
+LOCK BINLOG FOR BACKUP;
+
+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+
+--connection con1
+--echo # connection con1
+
+#
+# test that AUTOCOMMIT updates are blocked with a BINLOG lock in another
+# connection with binlog enabled
+#
+
+SET SESSION lock_wait_timeout = 1;
+
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_innodb VALUES(1);
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_myisam VALUES(1);
+
+SET SESSION lock_wait_timeout = DEFAULT;
+
+--connection default
+--echo # connection default
+
+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+--disable_query_log
+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
+--enable_query_log
+
+UNLOCK BINLOG;
+
+#
+# LTFB + DDL / DML in another connection
+#
+
+--connection default
+--echo # connection default
+
+LOCK BINLOG FOR BACKUP;
+
+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+
+--connection con1
+--echo # connection con1
+
+SET SESSION lock_wait_timeout = 1;
+
+--error ER_LOCK_WAIT_TIMEOUT
+DELETE FROM t_innodb;
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_innodb VALUES(0);
+--error ER_LOCK_WAIT_TIMEOUT
+UPDATE t_innodb SET a = 1;
+--error ER_LOCK_WAIT_TIMEOUT
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+HANDLER t_innodb CLOSE;
+
+START TRANSACTION;
+SELECT * from t_innodb;
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+--error ER_LOCK_WAIT_TIMEOUT
+COMMIT;
+
+SELECT * FROM t_innodb;
+COMMIT;
+
+START TRANSACTION;
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_myisam VALUES(1);
+COMMIT;
+
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_myisam VALUES(0);
+--error ER_LOCK_WAIT_TIMEOUT
+UPDATE t_myisam SET a = 1;
+SELECT * FROM t_myisam;
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+HANDLER t_myisam CLOSE;
+
+# Blackhole updates are not binlogged in the ROW mode
+if (`SELECT @@binlog_format = 'ROW'`)
+{
+  DELETE FROM t_blackhole;
+}
+if (!`SELECT @@binlog_format = 'ROW'`)
+{
+--error ER_LOCK_WAIT_TIMEOUT
+  DELETE FROM t_blackhole;
+}
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_blackhole VALUES(0);
+# Blackhole updates are not binlogged in the ROW mode
+if (`SELECT @@binlog_format = 'ROW'`)
+{
+  UPDATE t_blackhole SET a = 1;
+}
+if (!`SELECT @@binlog_format = 'ROW'`)
+{
+  --error ER_LOCK_WAIT_TIMEOUT
+  UPDATE t_blackhole SET a = 1;
+}
+--error ER_LOCK_WAIT_TIMEOUT
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * FROM t_blackhole;
+
+--error ER_LOCK_WAIT_TIMEOUT
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+
+--error ER_LOCK_WAIT_TIMEOUT
+DROP TABLE tt_innodb;
+
+--error ER_BAD_TABLE_ERROR
+DROP TABLE non_existing;
+
+--error ER_TABLE_EXISTS_ERROR
+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
+
+--error ER_LOCK_WAIT_TIMEOUT
+TRUNCATE TABLE t_innodb;
+
+--error ER_LOCK_WAIT_TIMEOUT
+RENAME TABLE t_innodb TO tmp, tmp to t_innodb;
+
+--error ER_LOCK_WAIT_TIMEOUT
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
+
+--error ER_LOCK_WAIT_TIMEOUT
+ALTER TABLE t_innodb ADD KEY (a);
+
+--error ER_LOCK_WAIT_TIMEOUT
+CREATE DATABASE test1;
+
+--error ER_LOCK_WAIT_TIMEOUT
+DROP DATABASE test1;
+
+--error ER_LOCK_WAIT_TIMEOUT
+CREATE PROCEDURE p1() SELECT 1;
+
+--error ER_LOCK_WAIT_TIMEOUT
+DROP PROCEDURE p1;
+
+--error ER_LOCK_WAIT_TIMEOUT
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+
+--error ER_LOCK_WAIT_TIMEOUT
+DROP VIEW v1;
+
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO v_innodb VALUES(1);
+
+--connection default
+--echo # connection default
+
+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+--disable_query_log
+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
+--enable_query_log
+
+UNLOCK BINLOG;
+
+--connection con1
+--echo # connection con1
+
+# DDLs are not transactional, so previous DDL statements against t_innodb will
+# be executed despite the error
+SHOW CREATE TABLE t_innodb;
+
+DROP TABLE t_innodb;
+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
+
+--connection default
+--echo # connection default
+
+#
+# INSERT DELAYED
+#
+
+LOCK BINLOG FOR BACKUP;
+
+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+
+--connection con1
+--echo # connection con1
+
+if (`SELECT @@binlog_format = 'STATEMENT'`)
+{
+   --error ER_LOCK_WAIT_TIMEOUT
+   INSERT DELAYED INTO t_myisam VALUES(0);
+}
+if (!`SELECT @@binlog_format = 'STATEMENT'`)
+{
+   INSERT DELAYED INTO t_myisam VALUES(0);
+}
+
+--connection default
+--echo # connection default
+
+if (!`SELECT @@binlog_format = 'STATEMENT'`)
+{
+  let $wait_condition=
+      SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+      WHERE STATE = "Waiting for binlog lock" AND
+      COMMAND = "Delayed insert";
+  --source include/wait_condition.inc
+}
+  
+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+--disable_query_log
+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
+--enable_query_log
+
+UNLOCK BINLOG;
+
+if (!`SELECT @@binlog_format = 'STATEMENT'`)
+{
+  let $wait_condition=
+      SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST
+      WHERE STATE = "Waiting for binlog lock" AND
+      COMMAND = "Delayed";
+  --source include/wait_condition.inc
+}
+
+SELECT * FROm t_myisam;
+
+
+#
+# Log tables
+#
+
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+
+LOCK BINLOG FOR BACKUP;
+
+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+
+--connection con1
+--echo # connection con1
+
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+
+SELECT 1;
+
+--connection default
+--echo # connection default
+
+SELECT 1;
+
+--connection con1
+--echo # connection con1
+
+SET SESSION long_query_time = @old_long_query_time;
+
+--connection default
+--echo # connection default
+
+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
+--disable_query_log
+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
+--enable_query_log
+
+UNLOCK BINLOG;
+
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+
+--echo #-----------------------------------------------------------------------
+--echo # Cleanup
+--echo #-----------------------------------------------------------------------
+
+DROP USER user@localhost;
+
+--disconnect con1
+--disconnect con2
+
+--source include/wait_until_count_sessions.inc
+
+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;

=== added file 'mysql-test/r/backup_locks.result'
--- mysql-test/r/backup_locks.result	1970-01-01 00:00:00 +0000
+++ mysql-test/r/backup_locks.result	2014-02-20 11:26:00 +0000
@@ -0,0 +1,883 @@
+SHOW VARIABLES LIKE 'have_backup_locks';
+Variable_name	Value
+have_backup_locks	YES
+#-----------------------------------------------------------------------
+# Setup
+#-----------------------------------------------------------------------
+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
+CREATE VIEW v_memory AS SELECT * FROM t_memory;
+CREATE VIEW v_csv AS SELECT * FROM t_csv;
+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
+CREATE VIEW v_archive AS SELECT * FROM t_archive;
+#-----------------------------------------------------------------------
+# Basic syntax checks
+#-----------------------------------------------------------------------
+LOCK TABLES t_innodb FOR BACKUP;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR BACKUP' at line 1
+LOCK TABLES t_innodb READ FOR BACKUP;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR BACKUP' at line 1
+LOCK TABLES t_innodb FOR BACKUP READ;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR BACKUP READ' at line 1
+LOCK BINLOG t_innodb FOR BACKUP;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't_innodb FOR BACKUP' at line 1
+LOCK BINLOG t_innodb READ FOR BACKUP;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't_innodb READ FOR BACKUP' at line 1
+LOCK BINLOG t_innodb FOR BACKUP READ;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't_innodb FOR BACKUP READ' at line 1
+LOCK TABLES FOR BACKUP;
+UNLOCK TABLES;
+LOCK BINLOG FOR BACKUP;
+UNLOCK BINLOG;
+#-----------------------------------------------------------------------
+# No backup locks are allowed in stored routines
+#-----------------------------------------------------------------------
+CREATE PROCEDURE p1()
+BEGIN
+LOCK TABLES FOR BACKUP;
+END|
+ERROR 0A000: LOCK is not allowed in stored procedures
+CREATE PROCEDURE p2()
+BEGIN
+LOCK BINLOG FOR BACKUP;
+END|
+ERROR 0A000: LOCK is not allowed in stored procedures
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+LOCK TABLES FOR BACKUP;
+RETURN 1;
+END|
+ERROR 0A000: LOCK is not allowed in stored procedures
+CREATE FUNCTION f2() RETURNS INT DETERMINISTIC
+BEGIN
+LOCK BINLOG FOR BACKUP;
+RETURN 1;
+END|
+ERROR 0A000: LOCK is not allowed in stored procedures
+#-----------------------------------------------------------------------
+# Single-threaded tests
+#-----------------------------------------------------------------------
+SET SESSION lock_wait_timeout=1;
+FLUSH STATUS;
+LOCK TABLES FOR BACKUP;
+LOCK TABLES FOR BACKUP;
+SHOW STATUS LIKE 'Com_lock_tables_for_backup';
+Variable_name	Value
+Com_lock_tables_for_backup	2
+UNLOCK TABLES;
+SHOW STATUS LIKE 'Com_unlock_tables';
+Variable_name	Value
+Com_unlock_tables	1
+LOCK BINLOG FOR BACKUP;
+LOCK BINLOG FOR BACKUP;
+SHOW STATUS LIKE 'Com_lock_binlog_for_backup';
+Variable_name	Value
+Com_lock_binlog_for_backup	2
+UNLOCK BINLOG;
+SHOW STATUS LIKE 'Com_unlock_binlog';
+Variable_name	Value
+Com_unlock_binlog	1
+LOCK TABLES FOR BACKUP;
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * FROM t_innodb;
+a
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+1
+HANDLER t_innodb CLOSE;
+DELETE FROM t_myisam;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT DELAYED INTO t_myisam VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UPDATE t_myisam SET a = 1;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+REPLACE INTO t_myisam VALUES(1);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+SELECT * FROM t_myisam;
+a
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+HANDLER t_myisam CLOSE;
+DELETE FROM t_memory;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT INTO t_memory VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT DELAYED INTO t_memory VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UPDATE t_memory SET a = 1;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+REPLACE INTO t_memory VALUES(1);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+SELECT * FROM t_memory;
+a
+DELETE FROM t_archive;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT INTO t_archive VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT DELAYED INTO t_archive VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UPDATE t_archive SET a = 1;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+REPLACE INTO t_archive VALUES(1);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+SELECT * FROM t_archive;
+a
+DELETE FROM t_csv;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT INTO t_csv VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UPDATE t_csv SET a = 1;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+REPLACE INTO t_csv VALUES(1);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+SELECT * FROM t_csv;
+a
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+INSERT DELAYED INTO t_blackhole VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * FROM t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+DROP TABLE t_innodb;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+DROP TABLE t_myisam;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+DROP TABLE t_memory;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+DROP TABLE t_csv;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+DROP TABLE t_blackhole;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+DROP TABLE t_archive;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+DROP TABLE non_existing;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+TRUNCATE TABLE t_innodb;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+TRUNCATE TABLE t_myisam;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+TRUNCATE TABLE t_memory;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+TRUNCATE TABLE t_csv;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+TRUNCATE TABLE t_blackhole;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+TRUNCATE TABLE t_archive;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+RENAME TABLE t_innodb TO tmp;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+RENAME TABLE t_myisam TO tmp;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+RENAME TABLE t_memory TO tmp;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+RENAME TABLE t_csv TO tmp;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+RENAME TABLE t_blackhole TO tmp;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+RENAME TABLE t_archive TO tmp;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_memory ADD COLUMN b CHAR(10);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_csv ADD COLUMN b CHAR(10);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_archive ADD COLUMN b CHAR(10);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_innodb ADD KEY (a);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_myisam ADD KEY (a);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_memory ADD KEY (a);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_csv ADD KEY (a);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_blackhole ADD KEY (a);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+ALTER TABLE t_archive ADD KEY (a);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+DROP DATABASE test;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+CREATE DATABASE test1;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+CREATE PROCEDURE p1()
+BEGIN
+SELECT 1;
+END|
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+RETURN 1;
+END|
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT INTO v_csv VALUES(1);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT INTO v_memory VALUES(1);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+INSERT INTO v_archive VALUES(1);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UNLOCK TABLES;
+LOCK BINLOG FOR BACKUP;
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+a
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+1
+HANDLER t_innodb CLOSE;
+DELETE FROM t_myisam;
+INSERT INTO t_myisam VALUES(0);
+UPDATE t_myisam SET a = 1;
+REPLACE INTO t_myisam VALUES(1);
+SELECT * from t_myisam;
+a
+1
+1
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+1
+HANDLER t_myisam CLOSE;
+DELETE FROM t_memory;
+INSERT INTO t_memory VALUES(0);
+UPDATE t_memory SET a = 1;
+REPLACE INTO t_memory VALUES(1);
+SELECT * from t_memory;
+a
+1
+1
+DELETE FROM t_archive;
+INSERT INTO t_archive VALUES(0);
+SELECT * from t_archive;
+a
+0
+DELETE FROM t_csv;
+INSERT INTO t_csv VALUES(0);
+UPDATE t_csv SET a = 1;
+REPLACE INTO t_csv VALUES(1);
+SELECT * from t_csv;
+a
+1
+1
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * from t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+DROP TABLE tt_innodb;
+DROP TABLE tt_myisam;
+DROP TABLE tt_memory;
+DROP TABLE tt_csv;
+DROP TABLE tt_blackhole;
+DROP TABLE tt_archive;
+DROP TABLE non_existing;
+ERROR 42S02: Unknown table 'test.non_existing'
+TRUNCATE TABLE t_innodb;
+TRUNCATE TABLE t_myisam;
+TRUNCATE TABLE t_memory;
+TRUNCATE TABLE t_csv;
+TRUNCATE TABLE t_blackhole;
+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_innodb DROP COLUMN b;
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_myisam DROP COLUMN b;
+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_memory DROP COLUMN b;
+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_csv DROP COLUMN b;
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_blackhole DROP COLUMN b;
+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_archive DROP COLUMN b;
+ALTER TABLE t_innodb ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_innodb DROP KEY tmp;
+ALTER TABLE t_myisam ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_myisam DROP KEY tmp;
+ALTER TABLE t_memory ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_memory DROP KEY tmp;
+ALTER TABLE t_blackhole ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_blackhole DROP KEY tmp;
+CREATE DATABASE test1;
+DROP DATABASE test1;
+CREATE PROCEDURE p1()
+BEGIN
+SELECT 1;
+END|
+DROP PROCEDURE p1|
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+RETURN 1;
+END|
+DROP FUNCTION f1|
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+DROP VIEW v1;
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+INSERT INTO v_csv VALUES(1);
+INSERT INTO v_memory VALUES(1);
+INSERT INTO v_archive VALUES(1);
+UNLOCK BINLOG;
+SELECT @@delay_key_write;
+@@delay_key_write
+ON
+SET GLOBAL delay_key_write=ALL;
+LOCK TABLES FOR BACKUP;
+ERROR HY000: The MySQL server is running with the delay_key_write=ALL option so it cannot execute this statement
+SET GLOBAL delay_key_write=default;
+SELECT @@delay_key_write;
+@@delay_key_write
+ON
+SET GLOBAL read_only=1;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+LOCK TABLES FOR BACKUP;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UNLOCK TABLES;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+SET GLOBAL read_only=0;
+LOCK TABLES FOR BACKUP;
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+LOCK BINLOG FOR BACKUP;
+START TRANSACTION;
+SELECT * FROM tt_archive;
+a
+5
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+SELECT * FROM tt_innodb;
+a
+5
+SELECT * FROM tt_myisam;
+a
+5
+INSERT INTO tt_innodb VALUES(6);
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_memory VALUES(6);
+INSERT INTO tt_csv VALUES(6);
+INSERT INTO tt_blackhole VALUES(6);
+INSERT INTO tt_archive VALUES(6);
+COMMIT;
+SELECT * FROM tt_archive;
+a
+5
+6
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+6
+SELECT * FROM tt_innodb;
+a
+5
+6
+SELECT * FROM tt_myisam;
+a
+5
+6
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+UNLOCK BINLOG;
+UNLOCK TABLES;
+SELECT @@log_bin;
+@@log_bin
+0
+LOCK BINLOG FOR BACKUP;
+INSERT INTO t_innodb VALUES(1);
+INSERT INTO t_myisam VALUES(1);
+UNLOCK BINLOG;
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+LOCK TABLES FOR BACKUP;
+SELECT 1;
+1
+1
+LOCK BINLOG FOR BACKUP;
+SELECT 1;
+1
+1
+UNLOCK BINLOG;
+UNLOCK TABLES;
+SET SESSION long_query_time = @old_long_query_time;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+SET SESSION lock_wait_timeout=default;
+#-----------------------------------------------------------------------
+# Multi-threaded tests
+#-----------------------------------------------------------------------
+# connection default
+SET GLOBAL lock_wait_timeout=1;
+CREATE USER user@localhost;
+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
+LOCK TABLES FOR BACKUP;
+# connection con1
+SET GLOBAL delay_key_write=ALL;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+# connection default
+UNLOCK TABLES;
+# connection con2
+START TRANSACTION;
+INSERT INTO t_innodb VALUES(0);
+# connection default
+SET GLOBAL read_only=1;
+# connection con2
+COMMIT;
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection con1
+LOCK TABLES FOR BACKUP;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection default
+SET GLOBAL read_only=0;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+# connection default
+SET GLOBAL read_only=1;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection con1
+UNLOCK TABLES;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection default
+SET GLOBAL read_only=0;
+# connection default
+LOCK TABLES FOR BACKUP;
+# connection con1
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+START TRANSACTION;
+SELECT * FROM tt_archive;
+a
+5
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+SELECT * FROM tt_innodb;
+a
+5
+SELECT * FROM tt_myisam;
+a
+5
+INSERT INTO tt_innodb VALUES(6);
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_memory VALUES(6);
+INSERT INTO tt_csv VALUES(6);
+INSERT INTO tt_blackhole VALUES(6);
+INSERT INTO tt_archive VALUES(6);
+COMMIT;
+SELECT * FROM tt_archive;
+a
+5
+6
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+6
+SELECT * FROM tt_innodb;
+a
+5
+6
+SELECT * FROM tt_myisam;
+a
+5
+6
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+# connection default
+UNLOCK BINLOG;
+UNLOCK TABLES;
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SELECT @@log_bin;
+@@log_bin
+0
+INSERT INTO t_innodb VALUES(1);
+INSERT INTO t_myisam VALUES(1);
+# connection default
+UNLOCK BINLOG;
+# connection default
+LOCK TABLES FOR BACKUP;
+# connection con1
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+a
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+1
+HANDLER t_innodb CLOSE;
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT DELAYED INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_myisam;
+a
+0
+0
+1
+1
+1
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+0
+HANDLER t_myisam CLOSE;
+INSERT INTO t_memory VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_memory;
+a
+1
+INSERT INTO t_archive VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_archive;
+a
+0
+1
+INSERT INTO t_csv VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_csv;
+a
+1
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * FROM t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP TABLE t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP TABLE non_existing;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+TRUNCATE TABLE t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+RENAME TABLE t_innodb TO tmp;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ALTER TABLE t_innodb ADD KEY (a);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP DATABASE test;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE DATABASE test1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE PROCEDURE p1() SELECT 1;
+# connection default
+KILL QUERY #;
+# connection con1
+ERROR 70100: Query execution was interrupted
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 1;
+# connection default
+KILL QUERY #;
+# connection con1
+ERROR 70100: Query execution was interrupted
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+# connection default
+UNLOCK TABLES;
+LOCK BINLOG FOR BACKUP;
+# connection con1
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+a
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+1
+HANDLER t_innodb CLOSE;
+DELETE FROM t_myisam;
+INSERT INTO t_myisam VALUES(0);
+UPDATE t_myisam SET a = 1;
+REPLACE INTO t_myisam VALUES(1);
+SELECT * from t_myisam;
+a
+1
+1
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+1
+HANDLER t_myisam CLOSE;
+DELETE FROM t_memory;
+INSERT INTO t_memory VALUES(0);
+UPDATE t_memory SET a = 1;
+REPLACE INTO t_memory VALUES(1);
+SELECT * from t_memory;
+a
+1
+1
+INSERT INTO t_archive VALUES(0);
+SELECT * from t_archive;
+a
+0
+1
+0
+DELETE FROM t_csv;
+INSERT INTO t_csv VALUES(0);
+UPDATE t_csv SET a = 1;
+REPLACE INTO t_csv VALUES(1);
+SELECT * from t_csv;
+a
+1
+1
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * from t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+DROP TABLE tt_innodb;
+DROP TABLE tt_myisam;
+DROP TABLE tt_memory;
+DROP TABLE tt_csv;
+DROP TABLE tt_blackhole;
+DROP TABLE tt_archive;
+DROP TABLE non_existing;
+ERROR 42S02: Unknown table 'test.non_existing'
+TRUNCATE TABLE t_innodb;
+TRUNCATE TABLE t_myisam;
+TRUNCATE TABLE t_memory;
+TRUNCATE TABLE t_csv;
+TRUNCATE TABLE t_blackhole;
+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_innodb DROP COLUMN b;
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_myisam DROP COLUMN b;
+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_memory DROP COLUMN b;
+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_csv DROP COLUMN b;
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_blackhole DROP COLUMN b;
+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_archive DROP COLUMN b;
+ALTER TABLE t_innodb ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_innodb DROP KEY tmp;
+ALTER TABLE t_myisam ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_myisam DROP KEY tmp;
+ALTER TABLE t_memory ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_memory DROP KEY tmp;
+ALTER TABLE t_blackhole ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_blackhole DROP KEY tmp;
+CREATE DATABASE test1;
+DROP DATABASE test1;
+CREATE PROCEDURE p1()
+BEGIN
+SELECT 1;
+END|
+DROP PROCEDURE p1|
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+RETURN 1;
+END|
+DROP FUNCTION f1|
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+DROP VIEW v1;
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+INSERT INTO v_csv VALUES(1);
+INSERT INTO v_memory VALUES(1);
+INSERT INTO v_archive VALUES(1);
+# connection default
+UNLOCK BINLOG;
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+LOCK TABLES FOR BACKUP;
+# connection con1
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+SELECT 1;
+1
+1
+# connection default
+LOCK BINLOG FOR BACKUP;
+SELECT 1;
+1
+1
+# connection con1
+SET SESSION long_query_time = @old_long_query_time;
+# connection default
+UNLOCK BINLOG;
+UNLOCK TABLES;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+#-----------------------------------------------------------------------
+# Cleanup
+#-----------------------------------------------------------------------
+SET GLOBAL lock_wait_timeout=default;
+DROP USER user@localhost;
+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;

=== added file 'mysql-test/r/backup_locks_binlog_mixed.result'
--- mysql-test/r/backup_locks_binlog_mixed.result	1970-01-01 00:00:00 +0000
+++ mysql-test/r/backup_locks_binlog_mixed.result	2014-02-20 11:26:00 +0000
@@ -0,0 +1,573 @@
+SET @old_binlog_format=@@binlog_format;
+SET GLOBAL binlog_format='MIXED';
+SET SESSION binlog_format='MIXED';
+SELECT @@binlog_format;
+@@binlog_format
+MIXED
+RESET MASTER;
+SHOW VARIABLES LIKE 'have_backup_locks';
+Variable_name	Value
+have_backup_locks	YES
+#-----------------------------------------------------------------------
+# Setup
+#-----------------------------------------------------------------------
+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
+CREATE VIEW v_memory AS SELECT * FROM t_memory;
+CREATE VIEW v_csv AS SELECT * FROM t_csv;
+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
+CREATE VIEW v_archive AS SELECT * FROM t_archive;
+#-----------------------------------------------------------------------
+# Single-threaded tests
+#-----------------------------------------------------------------------
+LOCK BINLOG FOR BACKUP;
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+a
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+1
+HANDLER t_innodb CLOSE;
+DELETE FROM t_myisam;
+INSERT INTO t_myisam VALUES(0);
+UPDATE t_myisam SET a = 1;
+REPLACE INTO t_myisam VALUES(1);
+SELECT * from t_myisam;
+a
+1
+1
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+1
+HANDLER t_myisam CLOSE;
+DELETE FROM t_memory;
+INSERT INTO t_memory VALUES(0);
+UPDATE t_memory SET a = 1;
+REPLACE INTO t_memory VALUES(1);
+SELECT * from t_memory;
+a
+1
+1
+DELETE FROM t_archive;
+INSERT INTO t_archive VALUES(0);
+SELECT * from t_archive;
+a
+0
+DELETE FROM t_csv;
+INSERT INTO t_csv VALUES(0);
+UPDATE t_csv SET a = 1;
+REPLACE INTO t_csv VALUES(1);
+SELECT * from t_csv;
+a
+1
+1
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * from t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+DROP TABLE tt_innodb;
+DROP TABLE tt_myisam;
+DROP TABLE tt_memory;
+DROP TABLE tt_csv;
+DROP TABLE tt_blackhole;
+DROP TABLE tt_archive;
+DROP TABLE non_existing;
+ERROR 42S02: Unknown table 'test.non_existing'
+TRUNCATE TABLE t_innodb;
+TRUNCATE TABLE t_myisam;
+TRUNCATE TABLE t_memory;
+TRUNCATE TABLE t_csv;
+TRUNCATE TABLE t_blackhole;
+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_innodb DROP COLUMN b;
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_myisam DROP COLUMN b;
+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_memory DROP COLUMN b;
+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_csv DROP COLUMN b;
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_blackhole DROP COLUMN b;
+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_archive DROP COLUMN b;
+ALTER TABLE t_innodb ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_innodb DROP KEY tmp;
+ALTER TABLE t_myisam ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_myisam DROP KEY tmp;
+ALTER TABLE t_memory ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_memory DROP KEY tmp;
+ALTER TABLE t_blackhole ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_blackhole DROP KEY tmp;
+CREATE DATABASE test1;
+DROP DATABASE test1;
+CREATE PROCEDURE p1()
+BEGIN
+SELECT 1;
+END|
+DROP PROCEDURE p1|
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+RETURN 1;
+END|
+DROP FUNCTION f1|
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+DROP VIEW v1;
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+INSERT INTO v_csv VALUES(1);
+INSERT INTO v_memory VALUES(1);
+INSERT INTO v_archive VALUES(1);
+UNLOCK BINLOG;
+SELECT @@delay_key_write;
+@@delay_key_write
+ON
+SET GLOBAL delay_key_write=ALL;
+LOCK TABLES FOR BACKUP;
+ERROR HY000: The MySQL server is running with the delay_key_write=ALL option so it cannot execute this statement
+SET GLOBAL delay_key_write=default;
+SELECT @@delay_key_write;
+@@delay_key_write
+ON
+SET GLOBAL read_only=1;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+LOCK TABLES FOR BACKUP;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UNLOCK TABLES;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+SET GLOBAL read_only=0;
+LOCK TABLES FOR BACKUP;
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+LOCK BINLOG FOR BACKUP;
+START TRANSACTION;
+SELECT * FROM tt_archive;
+a
+5
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+SELECT * FROM tt_innodb;
+a
+5
+SELECT * FROM tt_myisam;
+a
+5
+INSERT INTO tt_innodb VALUES(6);
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_memory VALUES(6);
+INSERT INTO tt_csv VALUES(6);
+INSERT INTO tt_blackhole VALUES(6);
+INSERT INTO tt_archive VALUES(6);
+COMMIT;
+SELECT * FROM tt_archive;
+a
+5
+6
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+6
+SELECT * FROM tt_innodb;
+a
+5
+6
+SELECT * FROM tt_myisam;
+a
+5
+6
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+UNLOCK BINLOG;
+UNLOCK TABLES;
+SELECT @@log_bin;
+@@log_bin
+1
+LOCK BINLOG FOR BACKUP;
+INSERT INTO t_innodb VALUES(1);
+INSERT INTO t_myisam VALUES(1);
+UNLOCK BINLOG;
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+LOCK TABLES FOR BACKUP;
+SELECT 1;
+1
+1
+LOCK BINLOG FOR BACKUP;
+SELECT 1;
+1
+1
+UNLOCK BINLOG;
+UNLOCK TABLES;
+SET SESSION long_query_time = @old_long_query_time;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+#-----------------------------------------------------------------------
+# Multi-threaded tests
+#-----------------------------------------------------------------------
+# connection default
+CREATE USER user@localhost;
+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
+LOCK TABLES FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+SET GLOBAL delay_key_write=ALL;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET SESSION lock_wait_timeout = DEFAULT;
+# connection default
+UNLOCK TABLES;
+# connection con2
+START TRANSACTION;
+INSERT INTO t_innodb VALUES(0);
+# connection default
+SET GLOBAL read_only=1;
+# connection con2
+COMMIT;
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection con1
+LOCK TABLES FOR BACKUP;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection default
+SET GLOBAL read_only=0;
+# connection con2
+SET SESSION lock_wait_timeout = 1;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET SESSION lock_wait_timeout = 0;
+Warnings:
+Warning	1292	Truncated incorrect lock_wait_timeout value: '0'
+# connection default
+SET GLOBAL read_only=1;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection con1
+UNLOCK TABLES;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection default
+SET GLOBAL read_only=0;
+# connection default
+LOCK TABLES FOR BACKUP;
+# connection con1
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+START TRANSACTION;
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_innodb VALUES(6);
+SELECT * FROM tt_archive;
+a
+5
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+SELECT * FROM tt_innodb;
+a
+5
+6
+SELECT * FROM tt_myisam;
+a
+5
+6
+SELECT * FROM tt_csv;
+a
+5
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+UNLOCK TABLES;
+# connection con1
+COMMIT;
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+INSERT INTO t_innodb VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO t_myisam VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET SESSION lock_wait_timeout = DEFAULT;
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+DELETE FROM t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t_innodb SET a = 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+REPLACE INTO t_innodb VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * from t_innodb;
+a
+0
+0
+0
+0
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+0
+HANDLER t_innodb CLOSE;
+START TRANSACTION;
+SELECT * from t_innodb;
+a
+0
+0
+0
+0
+1
+1
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+COMMIT;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_innodb;
+a
+0
+0
+0
+0
+1
+1
+COMMIT;
+START TRANSACTION;
+INSERT INTO t_myisam VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+COMMIT;
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t_myisam SET a = 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_myisam;
+a
+1
+1
+1
+1
+1
+1
+1
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+1
+HANDLER t_myisam CLOSE;
+DELETE FROM t_blackhole;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO t_blackhole VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t_blackhole SET a = 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+REPLACE INTO t_blackhole VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP TABLE tt_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP TABLE non_existing;
+ERROR 42S02: Unknown table 'test.non_existing'
+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
+ERROR 42S01: Table 't_innodb' already exists
+TRUNCATE TABLE t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+RENAME TABLE t_innodb TO tmp, tmp to t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ALTER TABLE t_innodb ADD KEY (a);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE DATABASE test1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP DATABASE test1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE PROCEDURE p1() SELECT 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP PROCEDURE p1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP VIEW v1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO v_innodb VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+# connection con1
+SHOW CREATE TABLE t_innodb;
+Table	Create Table
+t_innodb	CREATE TABLE `t_innodb` (
+  `a` int(11) NOT NULL,
+  `b` char(10) DEFAULT NULL,
+  KEY `a` (`a`),
+  KEY `a_2` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t_innodb;
+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+INSERT DELAYED INTO t_myisam VALUES(0);
+Warnings:
+Warning	1287	'INSERT DELAYED' is deprecated and will be removed in a future release. Please use INSERT instead
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+SELECT * FROm t_myisam;
+a
+0
+1
+1
+1
+1
+1
+1
+1
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+SELECT 1;
+1
+1
+# connection default
+SELECT 1;
+1
+1
+# connection con1
+SET SESSION long_query_time = @old_long_query_time;
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+#-----------------------------------------------------------------------
+# Cleanup
+#-----------------------------------------------------------------------
+DROP USER user@localhost;
+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;
+SET GLOBAL binlog_format = @old_binlog_format;

=== added file 'mysql-test/r/backup_locks_binlog_row.result'
--- mysql-test/r/backup_locks_binlog_row.result	1970-01-01 00:00:00 +0000
+++ mysql-test/r/backup_locks_binlog_row.result	2014-02-20 11:26:00 +0000
@@ -0,0 +1,579 @@
+SET @old_binlog_format=@@binlog_format;
+SET GLOBAL binlog_format='ROW';
+SET SESSION binlog_format='ROW';
+SELECT @@binlog_format;
+@@binlog_format
+ROW
+RESET MASTER;
+SHOW VARIABLES LIKE 'have_backup_locks';
+Variable_name	Value
+have_backup_locks	YES
+#-----------------------------------------------------------------------
+# Setup
+#-----------------------------------------------------------------------
+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
+CREATE VIEW v_memory AS SELECT * FROM t_memory;
+CREATE VIEW v_csv AS SELECT * FROM t_csv;
+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
+CREATE VIEW v_archive AS SELECT * FROM t_archive;
+#-----------------------------------------------------------------------
+# Single-threaded tests
+#-----------------------------------------------------------------------
+LOCK BINLOG FOR BACKUP;
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+a
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+1
+HANDLER t_innodb CLOSE;
+DELETE FROM t_myisam;
+INSERT INTO t_myisam VALUES(0);
+UPDATE t_myisam SET a = 1;
+REPLACE INTO t_myisam VALUES(1);
+SELECT * from t_myisam;
+a
+1
+1
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+1
+HANDLER t_myisam CLOSE;
+DELETE FROM t_memory;
+INSERT INTO t_memory VALUES(0);
+UPDATE t_memory SET a = 1;
+REPLACE INTO t_memory VALUES(1);
+SELECT * from t_memory;
+a
+1
+1
+DELETE FROM t_archive;
+INSERT INTO t_archive VALUES(0);
+SELECT * from t_archive;
+a
+0
+DELETE FROM t_csv;
+INSERT INTO t_csv VALUES(0);
+UPDATE t_csv SET a = 1;
+REPLACE INTO t_csv VALUES(1);
+SELECT * from t_csv;
+a
+1
+1
+DELETE FROM t_blackhole;
+Warnings:
+Warning	1870	Row events are not logged for DELETE statements that modify BLACKHOLE tables in row format. Table(s): 't_blackhole.'
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+Warnings:
+Warning	1870	Row events are not logged for UPDATE statements that modify BLACKHOLE tables in row format. Table(s): 't_blackhole.'
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * from t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+DROP TABLE tt_innodb;
+DROP TABLE tt_myisam;
+DROP TABLE tt_memory;
+DROP TABLE tt_csv;
+DROP TABLE tt_blackhole;
+DROP TABLE tt_archive;
+DROP TABLE non_existing;
+ERROR 42S02: Unknown table 'test.non_existing'
+TRUNCATE TABLE t_innodb;
+TRUNCATE TABLE t_myisam;
+TRUNCATE TABLE t_memory;
+TRUNCATE TABLE t_csv;
+TRUNCATE TABLE t_blackhole;
+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_innodb DROP COLUMN b;
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_myisam DROP COLUMN b;
+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_memory DROP COLUMN b;
+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_csv DROP COLUMN b;
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_blackhole DROP COLUMN b;
+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_archive DROP COLUMN b;
+ALTER TABLE t_innodb ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_innodb DROP KEY tmp;
+ALTER TABLE t_myisam ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_myisam DROP KEY tmp;
+ALTER TABLE t_memory ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_memory DROP KEY tmp;
+ALTER TABLE t_blackhole ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_blackhole DROP KEY tmp;
+CREATE DATABASE test1;
+DROP DATABASE test1;
+CREATE PROCEDURE p1()
+BEGIN
+SELECT 1;
+END|
+DROP PROCEDURE p1|
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+RETURN 1;
+END|
+DROP FUNCTION f1|
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+DROP VIEW v1;
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+INSERT INTO v_csv VALUES(1);
+INSERT INTO v_memory VALUES(1);
+INSERT INTO v_archive VALUES(1);
+UNLOCK BINLOG;
+SELECT @@delay_key_write;
+@@delay_key_write
+ON
+SET GLOBAL delay_key_write=ALL;
+LOCK TABLES FOR BACKUP;
+ERROR HY000: The MySQL server is running with the delay_key_write=ALL option so it cannot execute this statement
+SET GLOBAL delay_key_write=default;
+SELECT @@delay_key_write;
+@@delay_key_write
+ON
+SET GLOBAL read_only=1;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+LOCK TABLES FOR BACKUP;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UNLOCK TABLES;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+SET GLOBAL read_only=0;
+LOCK TABLES FOR BACKUP;
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+LOCK BINLOG FOR BACKUP;
+START TRANSACTION;
+SELECT * FROM tt_archive;
+a
+5
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+SELECT * FROM tt_innodb;
+a
+5
+SELECT * FROM tt_myisam;
+a
+5
+INSERT INTO tt_innodb VALUES(6);
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_memory VALUES(6);
+INSERT INTO tt_csv VALUES(6);
+INSERT INTO tt_blackhole VALUES(6);
+INSERT INTO tt_archive VALUES(6);
+COMMIT;
+SELECT * FROM tt_archive;
+a
+5
+6
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+6
+SELECT * FROM tt_innodb;
+a
+5
+6
+SELECT * FROM tt_myisam;
+a
+5
+6
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+UNLOCK BINLOG;
+UNLOCK TABLES;
+SELECT @@log_bin;
+@@log_bin
+1
+LOCK BINLOG FOR BACKUP;
+INSERT INTO t_innodb VALUES(1);
+INSERT INTO t_myisam VALUES(1);
+UNLOCK BINLOG;
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+LOCK TABLES FOR BACKUP;
+SELECT 1;
+1
+1
+LOCK BINLOG FOR BACKUP;
+SELECT 1;
+1
+1
+UNLOCK BINLOG;
+UNLOCK TABLES;
+SET SESSION long_query_time = @old_long_query_time;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+#-----------------------------------------------------------------------
+# Multi-threaded tests
+#-----------------------------------------------------------------------
+# connection default
+CREATE USER user@localhost;
+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
+LOCK TABLES FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+SET GLOBAL delay_key_write=ALL;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET SESSION lock_wait_timeout = DEFAULT;
+# connection default
+UNLOCK TABLES;
+# connection con2
+START TRANSACTION;
+INSERT INTO t_innodb VALUES(0);
+# connection default
+SET GLOBAL read_only=1;
+# connection con2
+COMMIT;
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection con1
+LOCK TABLES FOR BACKUP;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection default
+SET GLOBAL read_only=0;
+# connection con2
+SET SESSION lock_wait_timeout = 1;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET SESSION lock_wait_timeout = 0;
+Warnings:
+Warning	1292	Truncated incorrect lock_wait_timeout value: '0'
+# connection default
+SET GLOBAL read_only=1;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection con1
+UNLOCK TABLES;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection default
+SET GLOBAL read_only=0;
+# connection default
+LOCK TABLES FOR BACKUP;
+# connection con1
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+START TRANSACTION;
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_innodb VALUES(6);
+SELECT * FROM tt_archive;
+a
+5
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+SELECT * FROM tt_innodb;
+a
+5
+6
+SELECT * FROM tt_myisam;
+a
+5
+6
+SELECT * FROM tt_csv;
+a
+5
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+UNLOCK TABLES;
+# connection con1
+COMMIT;
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+INSERT INTO t_innodb VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO t_myisam VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET SESSION lock_wait_timeout = DEFAULT;
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+DELETE FROM t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t_innodb SET a = 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+REPLACE INTO t_innodb VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * from t_innodb;
+a
+0
+0
+0
+0
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+0
+HANDLER t_innodb CLOSE;
+START TRANSACTION;
+SELECT * from t_innodb;
+a
+0
+0
+0
+0
+1
+1
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+COMMIT;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_innodb;
+a
+0
+0
+0
+0
+1
+1
+COMMIT;
+START TRANSACTION;
+INSERT INTO t_myisam VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+COMMIT;
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t_myisam SET a = 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_myisam;
+a
+1
+1
+1
+1
+1
+1
+1
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+1
+HANDLER t_myisam CLOSE;
+DELETE FROM t_blackhole;
+Warnings:
+Warning	1870	Row events are not logged for DELETE statements that modify BLACKHOLE tables in row format. Table(s): 't_blackhole.'
+INSERT INTO t_blackhole VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t_blackhole SET a = 1;
+Warnings:
+Warning	1870	Row events are not logged for UPDATE statements that modify BLACKHOLE tables in row format. Table(s): 't_blackhole.'
+REPLACE INTO t_blackhole VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP TABLE tt_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP TABLE non_existing;
+ERROR 42S02: Unknown table 'test.non_existing'
+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
+ERROR 42S01: Table 't_innodb' already exists
+TRUNCATE TABLE t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+RENAME TABLE t_innodb TO tmp, tmp to t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ALTER TABLE t_innodb ADD KEY (a);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE DATABASE test1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP DATABASE test1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE PROCEDURE p1() SELECT 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP PROCEDURE p1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP VIEW v1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO v_innodb VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+# connection con1
+SHOW CREATE TABLE t_innodb;
+Table	Create Table
+t_innodb	CREATE TABLE `t_innodb` (
+  `a` int(11) NOT NULL,
+  `b` char(10) DEFAULT NULL,
+  KEY `a` (`a`),
+  KEY `a_2` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t_innodb;
+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+INSERT DELAYED INTO t_myisam VALUES(0);
+Warnings:
+Warning	1287	'INSERT DELAYED' is deprecated and will be removed in a future release. Please use INSERT instead
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+SELECT * FROm t_myisam;
+a
+0
+1
+1
+1
+1
+1
+1
+1
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+SELECT 1;
+1
+1
+# connection default
+SELECT 1;
+1
+1
+# connection con1
+SET SESSION long_query_time = @old_long_query_time;
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+#-----------------------------------------------------------------------
+# Cleanup
+#-----------------------------------------------------------------------
+DROP USER user@localhost;
+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;
+SET GLOBAL binlog_format = @old_binlog_format;

=== added file 'mysql-test/r/backup_locks_binlog_stmt.result'
--- mysql-test/r/backup_locks_binlog_stmt.result	1970-01-01 00:00:00 +0000
+++ mysql-test/r/backup_locks_binlog_stmt.result	2014-02-20 11:26:00 +0000
@@ -0,0 +1,575 @@
+SET @old_binlog_format=@@binlog_format;
+SET GLOBAL binlog_format='STATEMENT';
+SET SESSION binlog_format='STATEMENT';
+SELECT @@binlog_format;
+@@binlog_format
+STATEMENT
+RESET MASTER;
+SHOW VARIABLES LIKE 'have_backup_locks';
+Variable_name	Value
+have_backup_locks	YES
+#-----------------------------------------------------------------------
+# Setup
+#-----------------------------------------------------------------------
+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
+CREATE VIEW v_memory AS SELECT * FROM t_memory;
+CREATE VIEW v_csv AS SELECT * FROM t_csv;
+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
+CREATE VIEW v_archive AS SELECT * FROM t_archive;
+#-----------------------------------------------------------------------
+# Single-threaded tests
+#-----------------------------------------------------------------------
+LOCK BINLOG FOR BACKUP;
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+a
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+1
+HANDLER t_innodb CLOSE;
+DELETE FROM t_myisam;
+INSERT INTO t_myisam VALUES(0);
+UPDATE t_myisam SET a = 1;
+REPLACE INTO t_myisam VALUES(1);
+SELECT * from t_myisam;
+a
+1
+1
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+1
+HANDLER t_myisam CLOSE;
+DELETE FROM t_memory;
+INSERT INTO t_memory VALUES(0);
+UPDATE t_memory SET a = 1;
+REPLACE INTO t_memory VALUES(1);
+SELECT * from t_memory;
+a
+1
+1
+DELETE FROM t_archive;
+INSERT INTO t_archive VALUES(0);
+SELECT * from t_archive;
+a
+0
+DELETE FROM t_csv;
+INSERT INTO t_csv VALUES(0);
+UPDATE t_csv SET a = 1;
+REPLACE INTO t_csv VALUES(1);
+SELECT * from t_csv;
+a
+1
+1
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * from t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+DROP TABLE tt_innodb;
+DROP TABLE tt_myisam;
+DROP TABLE tt_memory;
+DROP TABLE tt_csv;
+DROP TABLE tt_blackhole;
+DROP TABLE tt_archive;
+DROP TABLE non_existing;
+ERROR 42S02: Unknown table 'test.non_existing'
+TRUNCATE TABLE t_innodb;
+TRUNCATE TABLE t_myisam;
+TRUNCATE TABLE t_memory;
+TRUNCATE TABLE t_csv;
+TRUNCATE TABLE t_blackhole;
+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_innodb DROP COLUMN b;
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_myisam DROP COLUMN b;
+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_memory DROP COLUMN b;
+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_csv DROP COLUMN b;
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_blackhole DROP COLUMN b;
+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_archive DROP COLUMN b;
+ALTER TABLE t_innodb ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_innodb DROP KEY tmp;
+ALTER TABLE t_myisam ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_myisam DROP KEY tmp;
+ALTER TABLE t_memory ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_memory DROP KEY tmp;
+ALTER TABLE t_blackhole ADD KEY tmp (a);
+Warnings:
+Note	1831	Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
+ALTER TABLE t_blackhole DROP KEY tmp;
+CREATE DATABASE test1;
+DROP DATABASE test1;
+CREATE PROCEDURE p1()
+BEGIN
+SELECT 1;
+END|
+DROP PROCEDURE p1|
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+RETURN 1;
+END|
+DROP FUNCTION f1|
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+DROP VIEW v1;
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+INSERT INTO v_csv VALUES(1);
+INSERT INTO v_memory VALUES(1);
+INSERT INTO v_archive VALUES(1);
+UNLOCK BINLOG;
+SELECT @@delay_key_write;
+@@delay_key_write
+ON
+SET GLOBAL delay_key_write=ALL;
+LOCK TABLES FOR BACKUP;
+ERROR HY000: The MySQL server is running with the delay_key_write=ALL option so it cannot execute this statement
+SET GLOBAL delay_key_write=default;
+SELECT @@delay_key_write;
+@@delay_key_write
+ON
+SET GLOBAL read_only=1;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+LOCK TABLES FOR BACKUP;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
+UNLOCK TABLES;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+SET GLOBAL read_only=0;
+LOCK TABLES FOR BACKUP;
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+LOCK BINLOG FOR BACKUP;
+START TRANSACTION;
+SELECT * FROM tt_archive;
+a
+5
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+SELECT * FROM tt_innodb;
+a
+5
+SELECT * FROM tt_myisam;
+a
+5
+INSERT INTO tt_innodb VALUES(6);
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_memory VALUES(6);
+INSERT INTO tt_csv VALUES(6);
+INSERT INTO tt_blackhole VALUES(6);
+INSERT INTO tt_archive VALUES(6);
+COMMIT;
+SELECT * FROM tt_archive;
+a
+5
+6
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+6
+SELECT * FROM tt_innodb;
+a
+5
+6
+SELECT * FROM tt_myisam;
+a
+5
+6
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+UNLOCK BINLOG;
+UNLOCK TABLES;
+SELECT @@log_bin;
+@@log_bin
+1
+LOCK BINLOG FOR BACKUP;
+INSERT INTO t_innodb VALUES(1);
+INSERT INTO t_myisam VALUES(1);
+UNLOCK BINLOG;
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+LOCK TABLES FOR BACKUP;
+SELECT 1;
+1
+1
+LOCK BINLOG FOR BACKUP;
+SELECT 1;
+1
+1
+UNLOCK BINLOG;
+UNLOCK TABLES;
+SET SESSION long_query_time = @old_long_query_time;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+#-----------------------------------------------------------------------
+# Multi-threaded tests
+#-----------------------------------------------------------------------
+# connection default
+CREATE USER user@localhost;
+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
+LOCK TABLES FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+SET GLOBAL delay_key_write=ALL;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET SESSION lock_wait_timeout = DEFAULT;
+# connection default
+UNLOCK TABLES;
+# connection con2
+START TRANSACTION;
+INSERT INTO t_innodb VALUES(0);
+# connection default
+SET GLOBAL read_only=1;
+# connection con2
+COMMIT;
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection con1
+LOCK TABLES FOR BACKUP;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection default
+SET GLOBAL read_only=0;
+# connection con2
+SET SESSION lock_wait_timeout = 1;
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET SESSION lock_wait_timeout = 0;
+Warnings:
+Warning	1292	Truncated incorrect lock_wait_timeout value: '0'
+# connection default
+SET GLOBAL read_only=1;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection con1
+UNLOCK TABLES;
+# connection con2
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+# connection default
+SET GLOBAL read_only=0;
+# connection default
+LOCK TABLES FOR BACKUP;
+# connection con1
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+START TRANSACTION;
+INSERT INTO tt_myisam VALUES(6);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO tt_innodb VALUES(6);
+SELECT * FROM tt_archive;
+a
+5
+SELECT * FROM tt_blackhole;
+a
+SELECT * FROM tt_memory;
+a
+5
+SELECT * FROM tt_innodb;
+a
+5
+6
+SELECT * FROM tt_myisam;
+a
+5
+6
+SELECT * FROM tt_csv;
+a
+5
+COMMIT;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+UNLOCK TABLES;
+# connection con1
+COMMIT;
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+INSERT INTO t_innodb VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO t_myisam VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET SESSION lock_wait_timeout = DEFAULT;
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET SESSION lock_wait_timeout = 1;
+DELETE FROM t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO t_innodb VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t_innodb SET a = 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+REPLACE INTO t_innodb VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * from t_innodb;
+a
+0
+0
+0
+0
+1
+1
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+a
+0
+HANDLER t_innodb CLOSE;
+START TRANSACTION;
+SELECT * from t_innodb;
+a
+0
+0
+0
+0
+1
+1
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+COMMIT;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_innodb;
+a
+0
+0
+0
+0
+1
+1
+COMMIT;
+START TRANSACTION;
+INSERT INTO t_myisam VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+COMMIT;
+INSERT INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t_myisam SET a = 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_myisam;
+a
+1
+1
+1
+1
+1
+1
+1
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+a
+1
+HANDLER t_myisam CLOSE;
+DELETE FROM t_blackhole;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO t_blackhole VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t_blackhole SET a = 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+REPLACE INTO t_blackhole VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t_blackhole;
+a
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP TABLE tt_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP TABLE non_existing;
+ERROR 42S02: Unknown table 'test.non_existing'
+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
+ERROR 42S01: Table 't_innodb' already exists
+TRUNCATE TABLE t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+RENAME TABLE t_innodb TO tmp, tmp to t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ALTER TABLE t_innodb ADD KEY (a);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE DATABASE test1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP DATABASE test1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE PROCEDURE p1() SELECT 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP PROCEDURE p1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DROP VIEW v1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO v_innodb VALUES(1);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+# connection con1
+SHOW CREATE TABLE t_innodb;
+Table	Create Table
+t_innodb	CREATE TABLE `t_innodb` (
+  `a` int(11) NOT NULL,
+  `b` char(10) DEFAULT NULL,
+  KEY `a` (`a`),
+  KEY `a_2` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t_innodb;
+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
+# connection default
+LOCK BINLOG FOR BACKUP;
+# connection con1
+INSERT DELAYED INTO t_myisam VALUES(0);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+SELECT * FROm t_myisam;
+a
+0
+1
+1
+1
+1
+1
+1
+1
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+LOCK BINLOG FOR BACKUP;
+# connection con1
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+SELECT 1;
+1
+1
+# connection default
+SELECT 1;
+1
+1
+# connection con1
+SET SESSION long_query_time = @old_long_query_time;
+# connection default
+must_be_1
+1
+UNLOCK BINLOG;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+#-----------------------------------------------------------------------
+# Cleanup
+#-----------------------------------------------------------------------
+DROP USER user@localhost;
+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;
+SET GLOBAL binlog_format = @old_binlog_format;

=== added file 'mysql-test/r/backup_locks_mysqldump.result'
--- mysql-test/r/backup_locks_mysqldump.result	1970-01-01 00:00:00 +0000
+++ mysql-test/r/backup_locks_mysqldump.result	2014-02-20 11:26:00 +0000
@@ -0,0 +1,143 @@
+SHOW VARIABLES LIKE 'have_backup_locks';
+Variable_name	Value
+have_backup_locks	YES
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT) ENGINE=MyISAM;
+SET @old_general_log = @@general_log;
+SET @old_log_output = @@log_output;
+TRUNCATE TABLE mysql.general_log;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+# Check that --lock-for-backup is converted to --lock-all-tables if
+# --single-transaction is not specified
+SELECT argument FROM mysql.general_log WHERE argument != '';
+argument
+SET GLOBAL general_log = ON
+root@localhost on 
+/*!40100 SET @@SQL_MODE='' */
+/*!40103 SET TIME_ZONE='+00:00' */
+FLUSH TABLES
+FLUSH TABLES WITH READ LOCK
+SHOW VARIABLES LIKE 'gtid\_mode'
+SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
+SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
+SHOW VARIABLES LIKE 'ndbinfo\_version'
+test
+show tables
+show table status like 't1'
+SET SQL_QUOTE_SHOW_CREATE=1
+SET SESSION character_set_results = 'binary'
+show create table `t1`
+SET SESSION character_set_results = 'utf8'
+show fields from `t1`
+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
+SET SESSION character_set_results = 'binary'
+use `test`
+select @@collation_database
+SHOW TRIGGERS LIKE 't1'
+SET SESSION character_set_results = 'utf8'
+show table status like 't2'
+SET SQL_QUOTE_SHOW_CREATE=1
+SET SESSION character_set_results = 'binary'
+show create table `t2`
+SET SESSION character_set_results = 'utf8'
+show fields from `t2`
+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
+SET SESSION character_set_results = 'binary'
+use `test`
+select @@collation_database
+SHOW TRIGGERS LIKE 't2'
+SET SESSION character_set_results = 'utf8'
+SELECT argument FROM mysql.general_log WHERE argument != ''
+TRUNCATE TABLE mysql.general_log;
+# Check that --lock-for-backup --single-transaction uses LOCK TABLES FOR
+# BACKUP
+SELECT argument FROM mysql.general_log WHERE argument != '';
+argument
+root@localhost on 
+/*!40100 SET @@SQL_MODE='' */
+/*!40103 SET TIME_ZONE='+00:00' */
+SHOW VARIABLES LIKE 'have_backup_locks'
+LOCK TABLES FOR BACKUP
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
+START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
+SHOW VARIABLES LIKE 'gtid\_mode'
+SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
+SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
+SHOW VARIABLES LIKE 'ndbinfo\_version'
+test
+show tables
+show table status like 't1'
+SET SQL_QUOTE_SHOW_CREATE=1
+SET SESSION character_set_results = 'binary'
+show create table `t1`
+SET SESSION character_set_results = 'utf8'
+show fields from `t1`
+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
+SET SESSION character_set_results = 'binary'
+use `test`
+select @@collation_database
+SHOW TRIGGERS LIKE 't1'
+SET SESSION character_set_results = 'utf8'
+show table status like 't2'
+SET SQL_QUOTE_SHOW_CREATE=1
+SET SESSION character_set_results = 'binary'
+show create table `t2`
+SET SESSION character_set_results = 'utf8'
+show fields from `t2`
+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
+SET SESSION character_set_results = 'binary'
+use `test`
+select @@collation_database
+SHOW TRIGGERS LIKE 't2'
+SET SESSION character_set_results = 'utf8'
+SELECT argument FROM mysql.general_log WHERE argument != ''
+TRUNCATE TABLE mysql.general_log;
+# Check that --master-data disables --lock-for-backup
+SELECT argument FROM mysql.general_log WHERE argument != '';
+argument
+root@localhost on 
+/*!40100 SET @@SQL_MODE='' */
+/*!40103 SET TIME_ZONE='+00:00' */
+SHOW VARIABLES LIKE 'have_backup_locks'
+FLUSH /*!40101 LOCAL */ TABLES
+FLUSH TABLES WITH READ LOCK
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
+START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
+SHOW VARIABLES LIKE 'gtid\_mode'
+SHOW MASTER STATUS
+UNLOCK TABLES
+SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
+SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
+SHOW VARIABLES LIKE 'ndbinfo\_version'
+test
+show tables
+show table status like 't1'
+SET SQL_QUOTE_SHOW_CREATE=1
+SET SESSION character_set_results = 'binary'
+show create table `t1`
+SET SESSION character_set_results = 'utf8'
+show fields from `t1`
+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
+SET SESSION character_set_results = 'binary'
+use `test`
+select @@collation_database
+SHOW TRIGGERS LIKE 't1'
+SET SESSION character_set_results = 'utf8'
+show table status like 't2'
+SET SQL_QUOTE_SHOW_CREATE=1
+SET SESSION character_set_results = 'binary'
+show create table `t2`
+SET SESSION character_set_results = 'utf8'
+show fields from `t2`
+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
+SET SESSION character_set_results = 'binary'
+use `test`
+select @@collation_database
+SHOW TRIGGERS LIKE 't2'
+SET SESSION character_set_results = 'utf8'
+SELECT argument FROM mysql.general_log WHERE argument != ''
+TRUNCATE TABLE mysql.general_log;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL general_log = @old_general_log;
+DROP TABLE t1, t2;

=== modified file 'mysql-test/suite/funcs_1/r/storedproc.result'
--- mysql-test/suite/funcs_1/r/storedproc.result	2013-05-10 09:39:17 +0000
+++ mysql-test/suite/funcs_1/r/storedproc.result	2014-02-20 11:26:00 +0000
@@ -5522,7 +5522,9 @@
 unlock:BEGIN
 SELECT @x;
 END//
-ERROR 0A000: UNLOCK is not allowed in stored procedures
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':BEGIN
+SELECT @x;
+END' at line 2
 DROP PROCEDURE IF EXISTS sp1;
 CREATE PROCEDURE sp1()
 unsigned:BEGIN

=== modified file 'mysql-test/suite/funcs_1/t/storedproc.test'
--- mysql-test/suite/funcs_1/t/storedproc.test	2011-12-09 21:08:37 +0000
+++ mysql-test/suite/funcs_1/t/storedproc.test	2014-02-20 11:26:00 +0000
@@ -5226,7 +5226,7 @@
 DROP PROCEDURE IF EXISTS sp1;
 
 delimiter //;
---error ER_SP_BADSTATEMENT
+--error ER_PARSE_ERROR
 CREATE PROCEDURE sp1()
   unlock:BEGIN
     SELECT @x;

=== added file 'mysql-test/suite/rpl/r/rpl_backup_locks.result'
--- mysql-test/suite/rpl/r/rpl_backup_locks.result	1970-01-01 00:00:00 +0000
+++ mysql-test/suite/rpl/r/rpl_backup_locks.result	2014-02-20 11:26:00 +0000
@@ -0,0 +1,34 @@
+include/master-slave.inc
+Warnings:
+Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
+Note	####	Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
+[connection master]
+CREATE TABLE t_innodb (a INT) ENGINE=InnoDB;
+CREATE TABLE t_myisam (a INT) ENGINE=MyISAM;
+STOP SLAVE;
+include/wait_for_slave_to_stop.inc
+# connection master
+INSERT INTO t_innodb VALUES (0);
+# connection slave
+LOCK TABLES FOR BACKUP;
+LOCK BINLOG FOR BACKUP;
+START SLAVE;
+include/wait_for_slave_to_start.inc
+include/check_slave_param.inc [Exec_Master_Log_Pos]
+UNLOCK BINLOG;
+UNLOCK TABLES;
+# connection master
+# connection slave
+STOP SLAVE;
+# connection master
+INSERT INTO t_myisam VALUES (0);
+# connection slave
+LOCK TABLES FOR BACKUP;
+START SLAVE;
+LOCK BINLOG FOR BACKUP;
+UNLOCK TABLES;
+include/check_slave_param.inc [Exec_Master_Log_Pos]
+UNLOCK BINLOG;
+# connection master
+DROP TABLE t_innodb, t_myisam;
+include/rpl_end.inc

=== added file 'mysql-test/suite/rpl/t/rpl_backup_locks.test'
--- mysql-test/suite/rpl/t/rpl_backup_locks.test	1970-01-01 00:00:00 +0000
+++ mysql-test/suite/rpl/t/rpl_backup_locks.test	2014-02-20 11:26:00 +0000
@@ -0,0 +1,95 @@
+########################################################################
+# Replication-related tests for backup locks
+########################################################################
+
+--source include/have_innodb.inc
+--source include/master-slave.inc
+
+CREATE TABLE t_innodb (a INT) ENGINE=InnoDB;
+CREATE TABLE t_myisam (a INT) ENGINE=MyISAM;
+
+sync_slave_with_master;
+
+STOP SLAVE;
+--source include/wait_for_slave_to_stop.inc
+
+--connection master
+--echo # connection master
+
+INSERT INTO t_innodb VALUES (0);
+
+--connection slave
+--echo # connection slave
+
+--let $master_log_pos= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1)
+
+LOCK TABLES FOR BACKUP;
+LOCK BINLOG FOR BACKUP;
+
+START SLAVE;
+--source include/wait_for_slave_to_start.inc
+
+let $wait_condition=
+    SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE STATE = "Waiting for binlog lock" AND
+    (INFO = "INSERT INTO t_innodb VALUES (0)" OR INFO IS NULL);
+--source include/wait_condition.inc
+
+--let $slave_param= Exec_Master_Log_Pos
+--let $slave_param_value= $master_log_pos
+--source include/check_slave_param.inc
+
+UNLOCK BINLOG;
+UNLOCK TABLES;
+
+--connection master
+--echo # connection master
+sync_slave_with_master;
+
+
+--connection slave
+--echo # connection slave
+STOP SLAVE;
+
+--connection master
+--echo # connection master
+
+INSERT INTO t_myisam VALUES (0);
+
+--connection slave
+--echo # connection slave
+
+--let $master_log_pos= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1)
+
+LOCK TABLES FOR BACKUP;
+
+START SLAVE;
+
+let $wait_condition=
+    SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE STATE = "Waiting for backup lock" AND
+    (INFO = "INSERT INTO t_myisam VALUES (0)" OR INFO IS NULL);
+--source include/wait_condition.inc
+
+LOCK BINLOG FOR BACKUP;
+UNLOCK TABLES;
+
+let $wait_condition=
+    SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE STATE = "Waiting for binlog lock" AND
+    (INFO = "INSERT INTO t_myisam VALUES (0)" OR INFO IS NULL);
+--source include/wait_condition.inc
+
+--let $slave_param= Exec_Master_Log_Pos
+--let $slave_param_value= $master_log_pos
+--source include/check_slave_param.inc
+
+UNLOCK BINLOG;
+
+--connection master
+--echo # connection master
+
+DROP TABLE t_innodb, t_myisam;
+sync_slave_with_master;
+
+--source include/rpl_end.inc

=== added file 'mysql-test/suite/sys_vars/r/have_backup_locks_basic.result'
--- mysql-test/suite/sys_vars/r/have_backup_locks_basic.result	1970-01-01 00:00:00 +0000
+++ mysql-test/suite/sys_vars/r/have_backup_locks_basic.result	2014-02-20 11:26:00 +0000
@@ -0,0 +1,11 @@
+SELECT @@GLOBAL.have_backup_locks="YES";
+@@GLOBAL.have_backup_locks="YES"
+1
+SELECT @@SESSION.have_backup_locks;
+ERROR HY000: Variable 'have_backup_locks' is a GLOBAL variable
+SHOW GLOBAL VARIABLES LIKE 'have_backup_locks';
+Variable_name	Value
+have_backup_locks	YES
+SHOW SESSION VARIABLES LIKE 'have_backup_locks';
+Variable_name	Value
+have_backup_locks	YES

=== added file 'mysql-test/suite/sys_vars/t/have_backup_locks_basic.test'
--- mysql-test/suite/sys_vars/t/have_backup_locks_basic.test	1970-01-01 00:00:00 +0000
+++ mysql-test/suite/sys_vars/t/have_backup_locks_basic.test	2014-02-20 11:26:00 +0000
@@ -0,0 +1,7 @@
+SELECT @@GLOBAL.have_backup_locks="YES";
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+SELECT @@SESSION.have_backup_locks;
+
+SHOW GLOBAL VARIABLES LIKE 'have_backup_locks';
+
+SHOW SESSION VARIABLES LIKE 'have_backup_locks';

=== added file 'mysql-test/t/backup_locks.test'
--- mysql-test/t/backup_locks.test	1970-01-01 00:00:00 +0000
+++ mysql-test/t/backup_locks.test	2014-02-20 11:26:00 +0000
@@ -0,0 +1,1091 @@
+########################################################################
+# Tests for LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP
+########################################################################
+
+--source include/have_innodb.inc
+--source include/not_embedded.inc
+--source include/have_archive.inc
+--source include/have_csv.inc
+--source include/have_blackhole.inc
+
+SHOW VARIABLES LIKE 'have_backup_locks';
+
+--echo #-----------------------------------------------------------------------
+--echo # Setup
+--echo #-----------------------------------------------------------------------
+
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
+CREATE VIEW v_memory AS SELECT * FROM t_memory;
+CREATE VIEW v_csv AS SELECT * FROM t_csv;
+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
+CREATE VIEW v_archive AS SELECT * FROM t_archive;
+
+--echo #-----------------------------------------------------------------------
+--echo # Basic syntax checks
+--echo #-----------------------------------------------------------------------
+
+--error ER_PARSE_ERROR
+LOCK TABLES t_innodb FOR BACKUP;
+--error ER_PARSE_ERROR
+LOCK TABLES t_innodb READ FOR BACKUP;
+--error ER_PARSE_ERROR
+LOCK TABLES t_innodb FOR BACKUP READ;
+--error ER_PARSE_ERROR
+LOCK BINLOG t_innodb FOR BACKUP;
+--error ER_PARSE_ERROR
+LOCK BINLOG t_innodb READ FOR BACKUP;
+--error ER_PARSE_ERROR
+LOCK BINLOG t_innodb FOR BACKUP READ;
+
+LOCK TABLES FOR BACKUP;
+UNLOCK TABLES;
+
+LOCK BINLOG FOR BACKUP;
+UNLOCK BINLOG;
+
+--echo #-----------------------------------------------------------------------
+--echo # No backup locks are allowed in stored routines
+--echo #-----------------------------------------------------------------------
+
+delimiter |;
+--error ER_SP_BADSTATEMENT
+CREATE PROCEDURE p1()
+BEGIN
+  LOCK TABLES FOR BACKUP;
+END|
+
+--error ER_SP_BADSTATEMENT
+CREATE PROCEDURE p2()
+BEGIN
+  LOCK BINLOG FOR BACKUP;
+END|
+
+--error ER_SP_BADSTATEMENT
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+  LOCK TABLES FOR BACKUP;
+  RETURN 1;
+END|
+
+--error ER_SP_BADSTATEMENT
+CREATE FUNCTION f2() RETURNS INT DETERMINISTIC
+BEGIN
+  LOCK BINLOG FOR BACKUP;
+  RETURN 1;
+END|
+
+delimiter ;|
+
+--echo #-----------------------------------------------------------------------
+--echo # Single-threaded tests
+--echo #-----------------------------------------------------------------------
+
+SET SESSION lock_wait_timeout=1;
+
+FLUSH STATUS;
+
+#
+# Multiple TFB locks in the same connection
+#
+LOCK TABLES FOR BACKUP;
+LOCK TABLES FOR BACKUP;
+SHOW STATUS LIKE 'Com_lock_tables_for_backup';
+
+UNLOCK TABLES;
+SHOW STATUS LIKE 'Com_unlock_tables';
+
+#
+# Multiple BFB locks in the same connection
+#
+LOCK BINLOG FOR BACKUP;
+LOCK BINLOG FOR BACKUP;
+
+SHOW STATUS LIKE 'Com_lock_binlog_for_backup';
+
+UNLOCK BINLOG;
+SHOW STATUS LIKE 'Com_unlock_binlog';
+
+#
+# LTFB + DDL / DML in the same connection
+#
+
+LOCK TABLES FOR BACKUP;
+
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * FROM t_innodb;
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+HANDLER t_innodb CLOSE;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DELETE FROM t_myisam;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO t_myisam VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT DELAYED INTO t_myisam VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+UPDATE t_myisam SET a = 1;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+REPLACE INTO t_myisam VALUES(1);
+SELECT * FROM t_myisam;
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+HANDLER t_myisam CLOSE;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DELETE FROM t_memory;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO t_memory VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT DELAYED INTO t_memory VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+UPDATE t_memory SET a = 1;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+REPLACE INTO t_memory VALUES(1);
+SELECT * FROM t_memory;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DELETE FROM t_archive;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO t_archive VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT DELAYED INTO t_archive VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+UPDATE t_archive SET a = 1;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+REPLACE INTO t_archive VALUES(1);
+SELECT * FROM t_archive;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DELETE FROM t_csv;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO t_csv VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+UPDATE t_csv SET a = 1;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+REPLACE INTO t_csv VALUES(1);
+SELECT * FROM t_csv;
+
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT DELAYED INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * FROM t_blackhole;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DROP TABLE t_innodb;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DROP TABLE t_myisam;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DROP TABLE t_memory;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DROP TABLE t_csv;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DROP TABLE t_blackhole;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DROP TABLE t_archive;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DROP TABLE non_existing;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+TRUNCATE TABLE t_innodb;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+TRUNCATE TABLE t_myisam;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+TRUNCATE TABLE t_memory;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+TRUNCATE TABLE t_csv;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+TRUNCATE TABLE t_blackhole;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+TRUNCATE TABLE t_archive;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+RENAME TABLE t_innodb TO tmp;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+RENAME TABLE t_myisam TO tmp;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+RENAME TABLE t_memory TO tmp;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+RENAME TABLE t_csv TO tmp;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+RENAME TABLE t_blackhole TO tmp;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+RENAME TABLE t_archive TO tmp;
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_memory ADD COLUMN b CHAR(10);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_csv ADD COLUMN b CHAR(10);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_archive ADD COLUMN b CHAR(10);
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_innodb ADD KEY (a);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_myisam ADD KEY (a);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_memory ADD KEY (a);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_csv ADD KEY (a);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_blackhole ADD KEY (a);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+ALTER TABLE t_archive ADD KEY (a);
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+DROP DATABASE test;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE DATABASE test1;
+
+delimiter |;
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE PROCEDURE p1()
+BEGIN
+  SELECT 1;
+END|
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+  RETURN 1;
+END|
+
+delimiter ;|
+
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO v_myisam VALUES(1);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO v_csv VALUES(1);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO v_memory VALUES(1);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO v_archive VALUES(1);
+
+UNLOCK TABLES;
+
+#
+# LBFB + DML/DDL in the same connection (and binlog disabled)
+#
+
+LOCK BINLOG FOR BACKUP;
+
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+HANDLER t_innodb CLOSE;
+
+DELETE FROM t_myisam;
+INSERT INTO t_myisam VALUES(0);
+UPDATE t_myisam SET a = 1;
+REPLACE INTO t_myisam VALUES(1);
+SELECT * from t_myisam;
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+HANDLER t_myisam CLOSE;
+
+DELETE FROM t_memory;
+INSERT INTO t_memory VALUES(0);
+UPDATE t_memory SET a = 1;
+REPLACE INTO t_memory VALUES(1);
+SELECT * from t_memory;
+
+DELETE FROM t_archive;
+INSERT INTO t_archive VALUES(0);
+SELECT * from t_archive;
+
+DELETE FROM t_csv;
+INSERT INTO t_csv VALUES(0);
+UPDATE t_csv SET a = 1;
+REPLACE INTO t_csv VALUES(1);
+SELECT * from t_csv;
+
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * from t_blackhole;
+
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+DROP TABLE tt_innodb;
+DROP TABLE tt_myisam;
+DROP TABLE tt_memory;
+DROP TABLE tt_csv;
+DROP TABLE tt_blackhole;
+DROP TABLE tt_archive;
+
+--error ER_BAD_TABLE_ERROR
+DROP TABLE non_existing;
+
+TRUNCATE TABLE t_innodb;
+TRUNCATE TABLE t_myisam;
+TRUNCATE TABLE t_memory;
+TRUNCATE TABLE t_csv;
+TRUNCATE TABLE t_blackhole;
+
+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
+
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_innodb DROP COLUMN b;
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_myisam DROP COLUMN b;
+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_memory DROP COLUMN b;
+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_csv DROP COLUMN b;
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_blackhole DROP COLUMN b;
+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_archive DROP COLUMN b;
+
+ALTER TABLE t_innodb ADD KEY tmp (a);
+ALTER TABLE t_innodb DROP KEY tmp;
+ALTER TABLE t_myisam ADD KEY tmp (a);
+ALTER TABLE t_myisam DROP KEY tmp;
+ALTER TABLE t_memory ADD KEY tmp (a);
+ALTER TABLE t_memory DROP KEY tmp;
+ALTER TABLE t_blackhole ADD KEY tmp (a);
+ALTER TABLE t_blackhole DROP KEY tmp;
+
+CREATE DATABASE test1;
+DROP DATABASE test1;
+
+delimiter |;
+
+CREATE PROCEDURE p1()
+BEGIN
+  SELECT 1;
+END|
+
+DROP PROCEDURE p1|
+
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+  RETURN 1;
+END|
+
+DROP FUNCTION f1|
+
+delimiter ;|
+
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+DROP VIEW v1;
+
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+INSERT INTO v_csv VALUES(1);
+INSERT INTO v_memory VALUES(1);
+INSERT INTO v_archive VALUES(1);
+
+UNLOCK BINLOG;
+
+#
+# Test incompatibility with delay_key_write=ALL
+#
+SELECT @@delay_key_write;
+
+SET GLOBAL delay_key_write=ALL;
+--error ER_OPTION_PREVENTS_STATEMENT
+LOCK TABLES FOR BACKUP;
+
+SET GLOBAL delay_key_write=default;
+SELECT @@delay_key_write;
+
+#
+# read-only=1 + backup locks
+#
+
+SET GLOBAL read_only=1;
+
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+
+LOCK TABLES FOR BACKUP;
+
+INSERT INTO t_innodb VALUES(0);
+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
+INSERT INTO t_myisam VALUES(0);
+
+UNLOCK TABLES;
+
+INSERT INTO t_innodb VALUES(0);
+INSERT INTO t_myisam VALUES(0);
+
+SET GLOBAL read_only=0;
+
+#
+# Test that temporary tables are not affected by backup locks
+#
+LOCK TABLES FOR BACKUP;
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+
+LOCK BINLOG FOR BACKUP;
+
+START TRANSACTION;
+SELECT * FROM tt_archive;
+SELECT * FROM tt_blackhole;
+SELECT * FROM tt_memory;
+SELECT * FROM tt_innodb;
+SELECT * FROM tt_myisam;
+INSERT INTO tt_innodb VALUES(6);
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_memory VALUES(6);
+INSERT INTO tt_csv VALUES(6);
+INSERT INTO tt_blackhole VALUES(6);
+INSERT INTO tt_archive VALUES(6);
+COMMIT;
+
+SELECT * FROM tt_archive;
+SELECT * FROM tt_blackhole;
+SELECT * FROM tt_memory;
+SELECT * FROM tt_innodb;
+SELECT * FROM tt_myisam;
+
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+
+UNLOCK BINLOG;
+UNLOCK TABLES;
+
+#
+# test that AUTOCOMMIT updates succeed with a BINLOG lock in the same connection with binlog disabled
+#
+
+SELECT @@log_bin;
+
+LOCK BINLOG FOR BACKUP;
+
+INSERT INTO t_innodb VALUES(1);
+INSERT INTO t_myisam VALUES(1);
+
+UNLOCK BINLOG;
+
+#
+# Log tables
+#
+
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+SET @old_long_query_time = @@SESSION.long_query_time;
+
+SET SESSION long_query_time = 0;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+
+LOCK TABLES FOR BACKUP;
+
+SELECT 1;
+
+LOCK BINLOG FOR BACKUP;
+
+SELECT 1;
+
+UNLOCK BINLOG;
+
+UNLOCK TABLES;
+
+SET SESSION long_query_time = @old_long_query_time;
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL general_log = @old_general_log;
+
+SET SESSION lock_wait_timeout=default;
+
+
+--echo #-----------------------------------------------------------------------
+--echo # Multi-threaded tests
+--echo #-----------------------------------------------------------------------
+
+--echo # connection default
+SET GLOBAL lock_wait_timeout=1;
+
+CREATE USER user@localhost;
+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
+
+--connect(con1,localhost,root,,)
+
+--connect(con2,localhost,user,,)
+
+--connection default
+LOCK TABLES FOR BACKUP;
+
+--connection con1
+--echo # connection con1
+--error ER_LOCK_WAIT_TIMEOUT
+SET GLOBAL delay_key_write=ALL;
+
+--connection default
+--echo # connection default
+UNLOCK TABLES;
+
+#
+# read-only=1 + backup locks
+#
+
+--connection con2
+--echo # connection con2
+
+START TRANSACTION;
+INSERT INTO t_innodb VALUES(0);
+
+--connection default
+--echo # connection default
+
+SET GLOBAL read_only=1;
+
+--connection con2
+--echo # connection con2
+
+--error ER_OPTION_PREVENTS_STATEMENT
+COMMIT;
+
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_innodb VALUES(0);
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_myisam VALUES(0);
+
+--connection con1
+--echo # connection con1
+
+LOCK TABLES FOR BACKUP;
+
+--connection con2
+--echo # connection con2
+
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_innodb VALUES(0);
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_myisam VALUES(0);
+
+--connection default
+--echo # connection default
+
+SET GLOBAL read_only=0;
+
+--connection con2
+--echo # connection con2
+INSERT INTO t_innodb VALUES(0);
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_myisam VALUES(0);
+
+--connection default
+--echo # connection default
+
+SET GLOBAL read_only=1;
+
+--connection con2
+--echo # connection con2
+
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_innodb VALUES(0);
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_myisam VALUES(0);
+
+--connection con1
+--echo # connection con1
+
+UNLOCK TABLES;
+
+--connection con2
+--echo # connection con2
+
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_innodb VALUES(0);
+--error ER_OPTION_PREVENTS_STATEMENT
+INSERT INTO t_myisam VALUES(0);
+
+--connection default
+--echo # connection default
+
+SET GLOBAL read_only=0;
+
+#
+# Test that temporary tables are not affected by backup locks
+#
+--connection default
+--echo # connection default
+LOCK TABLES FOR BACKUP;
+
+--connection con1
+--echo # connection con1
+
+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+INSERT INTO tt_innodb VALUES(5);
+INSERT INTO tt_myisam VALUES(5);
+INSERT INTO tt_memory VALUES(5);
+INSERT INTO tt_csv VALUES(5);
+INSERT INTO tt_blackhole VALUES(5);
+INSERT INTO tt_archive VALUES(5);
+
+--connection default
+--echo # connection default
+LOCK BINLOG FOR BACKUP;
+
+--connection con1
+--echo # connection con1
+
+START TRANSACTION;
+SELECT * FROM tt_archive;
+SELECT * FROM tt_blackhole;
+SELECT * FROM tt_memory;
+SELECT * FROM tt_innodb;
+SELECT * FROM tt_myisam;
+INSERT INTO tt_innodb VALUES(6);
+INSERT INTO tt_myisam VALUES(6);
+INSERT INTO tt_memory VALUES(6);
+INSERT INTO tt_csv VALUES(6);
+INSERT INTO tt_blackhole VALUES(6);
+INSERT INTO tt_archive VALUES(6);
+COMMIT;
+
+SELECT * FROM tt_archive;
+SELECT * FROM tt_blackhole;
+SELECT * FROM tt_memory;
+SELECT * FROM tt_innodb;
+SELECT * FROM tt_myisam;
+
+DROP TEMPORARY TABLE tt_innodb;
+DROP TEMPORARY TABLE tt_myisam;
+DROP TEMPORARY TABLE tt_memory;
+DROP TEMPORARY TABLE tt_csv;
+DROP TEMPORARY TABLE tt_blackhole;
+DROP TEMPORARY TABLE tt_archive;
+
+--connection default
+--echo # connection default
+
+UNLOCK BINLOG;
+UNLOCK TABLES;
+
+LOCK BINLOG FOR BACKUP;
+
+--connection con1
+--echo # connection con1
+
+#
+# test that AUTOCOMMIT updates are not blocked with a BINLOG lock in another
+# connection with binlog disabled
+#
+
+SELECT @@log_bin;
+
+INSERT INTO t_innodb VALUES(1);
+INSERT INTO t_myisam VALUES(1);
+
+--connection default
+--echo # connection default
+
+UNLOCK BINLOG;
+
+#
+# LTFB + DDL / DML in another connection
+#
+
+--connection default
+--echo # connection default
+
+LOCK TABLES FOR BACKUP;
+
+--connection con1
+--echo # connection con1
+
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+HANDLER t_innodb CLOSE;
+
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_myisam VALUES(0);
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT DELAYED INTO t_myisam VALUES(0);
+SELECT * FROM t_myisam;
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+HANDLER t_myisam CLOSE;
+
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_memory VALUES(0);
+SELECT * FROM t_memory;
+
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_archive VALUES(0);
+SELECT * FROM t_archive;
+
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t_csv VALUES(0);
+SELECT * FROM t_csv;
+
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * FROM t_blackhole;
+
+--error ER_LOCK_WAIT_TIMEOUT
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+
+--error ER_LOCK_WAIT_TIMEOUT
+DROP TABLE t_innodb;
+
+--error ER_LOCK_WAIT_TIMEOUT
+DROP TABLE non_existing;
+
+--error ER_LOCK_WAIT_TIMEOUT
+TRUNCATE TABLE t_innodb;
+
+--error ER_LOCK_WAIT_TIMEOUT
+RENAME TABLE t_innodb TO tmp;
+
+--error ER_LOCK_WAIT_TIMEOUT
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
+
+--error ER_LOCK_WAIT_TIMEOUT
+ALTER TABLE t_innodb ADD KEY (a);
+
+--error ER_LOCK_WAIT_TIMEOUT
+DROP DATABASE test;
+--error ER_LOCK_WAIT_TIMEOUT
+CREATE DATABASE test1;
+
+# The server overrides lock_wait_timeout to 1 year when opening system
+# tables. So ER_LOCK_WAIT_TIMEOUT can't be used here.
+
+--let $id=`SELECT CONNECTION_ID()`
+
+--send CREATE PROCEDURE p1() SELECT 1
+
+--connection default
+--echo # connection default
+
+let $wait_condition=
+    SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE STATE = "Waiting for backup lock" AND
+    INFO = "CREATE PROCEDURE p1() SELECT 1";
+--source include/wait_condition.inc
+
+--disable_query_log
+--echo KILL QUERY #;
+--eval KILL QUERY $id
+--enable_query_log
+
+--connection con1
+--echo # connection con1
+
+--error ER_QUERY_INTERRUPTED
+--reap
+
+--send CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 1
+
+--connection default
+--echo # connection default
+
+let $wait_condition=
+    SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE STATE = "Waiting for backup lock" AND
+    INFO = "CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 1";
+--source include/wait_condition.inc
+
+--disable_query_log
+--echo KILL QUERY #;
+--eval KILL QUERY $id
+--enable_query_log
+
+--connection con1
+--echo # connection con1
+
+--error ER_QUERY_INTERRUPTED
+--reap
+
+--error ER_LOCK_WAIT_TIMEOUT
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO v_myisam VALUES(1);
+
+--connection default
+--echo # connection default
+
+UNLOCK TABLES;
+
+#
+# LBFB + DML/DDL in another connection (and binlog disabled)
+#
+
+LOCK BINLOG FOR BACKUP;
+
+--connection con1
+--echo # connection con1
+
+DELETE FROM t_innodb;
+INSERT INTO t_innodb VALUES(0);
+UPDATE t_innodb SET a = 1;
+REPLACE INTO t_innodb VALUES(1);
+SELECT * from t_innodb;
+HANDLER t_innodb OPEN;
+HANDLER t_innodb READ a FIRST;
+HANDLER t_innodb CLOSE;
+
+DELETE FROM t_myisam;
+INSERT INTO t_myisam VALUES(0);
+UPDATE t_myisam SET a = 1;
+REPLACE INTO t_myisam VALUES(1);
+SELECT * from t_myisam;
+HANDLER t_myisam OPEN;
+HANDLER t_myisam READ a FIRST;
+HANDLER t_myisam CLOSE;
+
+DELETE FROM t_memory;
+INSERT INTO t_memory VALUES(0);
+UPDATE t_memory SET a = 1;
+REPLACE INTO t_memory VALUES(1);
+SELECT * from t_memory;
+
+INSERT INTO t_archive VALUES(0);
+SELECT * from t_archive;
+
+DELETE FROM t_csv;
+INSERT INTO t_csv VALUES(0);
+UPDATE t_csv SET a = 1;
+REPLACE INTO t_csv VALUES(1);
+SELECT * from t_csv;
+
+DELETE FROM t_blackhole;
+INSERT INTO t_blackhole VALUES(0);
+UPDATE t_blackhole SET a = 1;
+REPLACE INTO t_blackhole VALUES(1);
+SELECT * from t_blackhole;
+
+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
+
+DROP TABLE tt_innodb;
+DROP TABLE tt_myisam;
+DROP TABLE tt_memory;
+DROP TABLE tt_csv;
+DROP TABLE tt_blackhole;
+DROP TABLE tt_archive;
+
+--error ER_BAD_TABLE_ERROR
+DROP TABLE non_existing;
+
+TRUNCATE TABLE t_innodb;
+TRUNCATE TABLE t_myisam;
+TRUNCATE TABLE t_memory;
+TRUNCATE TABLE t_csv;
+TRUNCATE TABLE t_blackhole;
+
+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
+
+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_innodb DROP COLUMN b;
+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_myisam DROP COLUMN b;
+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_memory DROP COLUMN b;
+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_csv DROP COLUMN b;
+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_blackhole DROP COLUMN b;
+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
+ALTER TABLE t_archive DROP COLUMN b;
+
+ALTER TABLE t_innodb ADD KEY tmp (a);
+ALTER TABLE t_innodb DROP KEY tmp;
+ALTER TABLE t_myisam ADD KEY tmp (a);
+ALTER TABLE t_myisam DROP KEY tmp;
+ALTER TABLE t_memory ADD KEY tmp (a);
+ALTER TABLE t_memory DROP KEY tmp;
+ALTER TABLE t_blackhole ADD KEY tmp (a);
+ALTER TABLE t_blackhole DROP KEY tmp;
+
+CREATE DATABASE test1;
+DROP DATABASE test1;
+
+delimiter |;
+
+CREATE PROCEDURE p1()
+BEGIN
+  SELECT 1;
+END|
+
+DROP PROCEDURE p1|
+
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+  RETURN 1;
+END|
+
+DROP FUNCTION f1|
+
+delimiter ;|
+
+CREATE VIEW v1 AS SELECT * FROM t_innodb;
+DROP VIEW v1;
+
+INSERT INTO v_innodb VALUES(1);
+INSERT INTO v_blackhole VALUES(1);
+INSERT INTO v_myisam VALUES(1);
+INSERT INTO v_csv VALUES(1);
+INSERT INTO v_memory VALUES(1);
+INSERT INTO v_archive VALUES(1);
+
+--connection default
+--echo # connection default
+
+UNLOCK BINLOG;
+
+#
+# Log tables
+#
+
+SET @old_general_log = @@general_log;
+SET @old_slow_query_log = @@slow_query_log;
+SET @old_log_output = @@log_output;
+
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+
+LOCK TABLES FOR BACKUP;
+
+--connection con1
+--echo # connection con1
+
+SET @old_long_query_time = @@SESSION.long_query_time;
+SET SESSION long_query_time = 0;
+
+SELECT 1;
+
+--connection default
+--echo # connection default
+
+LOCK BINLOG FOR BACKUP;
+
+SELECT 1;