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

Proposed by Alexey Kopytov
Status: Superseded
Proposed branch: lp:~akopytov/percona-server/backup-locks
Merge into: lp:percona-server/5.6
Diff against target: 6338 lines (+5554/-63)
42 files modified
client/client_priv.h (+1/-0)
client/mysqldump.c (+83/-3)
mysql-test/include/backup_locks_binlog.inc (+803/-0)
mysql-test/r/backup_locks.result (+883/-0)
mysql-test/r/backup_locks_binlog_mixed.result (+573/-0)
mysql-test/r/backup_locks_binlog_row.result (+579/-0)
mysql-test/r/backup_locks_binlog_stmt.result (+575/-0)
mysql-test/r/backup_locks_mysqldump.result (+143/-0)
mysql-test/suite/funcs_1/r/storedproc.result (+3/-1)
mysql-test/suite/funcs_1/t/storedproc.test (+1/-1)
mysql-test/suite/rpl/r/rpl_backup_locks.result (+34/-0)
mysql-test/suite/rpl/t/rpl_backup_locks.test (+95/-0)
mysql-test/suite/sys_vars/r/have_backup_locks_basic.result (+11/-0)
mysql-test/suite/sys_vars/t/have_backup_locks_basic.test (+7/-0)
mysql-test/t/backup_locks.test (+1091/-0)
mysql-test/t/backup_locks_binlog_mixed.test (+9/-0)
mysql-test/t/backup_locks_binlog_row.test (+9/-0)
mysql-test/t/backup_locks_binlog_stmt.test (+9/-0)
mysql-test/t/backup_locks_mysqldump.test (+58/-0)
sql/binlog.cc (+27/-1)
sql/handler.h (+7/-0)
sql/lock.cc (+145/-0)
sql/log_event.cc (+19/-0)
sql/mdl.cc (+54/-23)
sql/mdl.h (+2/-0)
sql/mysqld.cc (+6/-0)
sql/rpl_rli.cc (+24/-0)
sql/set_var.h (+1/-0)
sql/share/errmsg-utf8.txt (+4/-0)
sql/sql_base.cc (+46/-0)
sql/sql_class.cc (+8/-0)
sql/sql_class.h (+49/-0)
sql/sql_cmd.h (+2/-0)
sql/sql_insert.cc (+34/-14)
sql/sql_parse.cc (+91/-1)
sql/sql_reload.cc (+3/-1)
sql/sql_yacc.yy (+31/-13)
sql/sys_vars.cc (+27/-1)
storage/blackhole/ha_blackhole.cc (+1/-1)
storage/federated/ha_federated.cc (+2/-1)
storage/innobase/handler/ha_innodb.cc (+2/-1)
storage/perfschema/ha_perfschema.cc (+2/-1)
To merge this branch: bzr merge lp:~akopytov/percona-server/backup-locks
Reviewer Review Type Date Requested Status
Laurynas Biveinis (community) Needs Information
Review via email: mp+206724@code.launchpad.net

This proposal has been superseded by a proposal from 2014-02-20.

Description of the change

    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.

To post a comment you must log in.
Revision history for this message
Alexey Kopytov (akopytov) wrote :
Revision history for this message
Alexey Kopytov (akopytov) wrote :

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

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :
Download full text (3.7 KiB)

    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...

Read more...

review: Needs Information
538. By 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. These statements can be used as a
lightweight alternative to FLUSH TABLES WITH READ LOCK for both physical
and logical backups.

LOCK TABLES FOR BACKUP
----------------------

LOCK TABLES FOR BACKUP uses a new MDL lock type to block updates to
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
visible in PERFORMANCE_SCHEMA or PROCESSLIST. SELECT queries for all
tables and INSERT/REPLACE/UPDATE/DELETE against InnoDB, Blackhole and
Federated tables are not affected by LOCK TABLES FOR BACKUP. Blackhole
tables obviously have no relevance for backups, and Federated tables are
ignored by both logical and physical backup tools.

Unlike FLUSH TABLES WITH READ LOCK, LOCK TABLES FOR BACKUP does not
flush tables, i.e. storage engines are not forced to close tables and
tables are not expelled from the table cache. As a result, LTFB only
waits for conflicting statements to complete (i.e. DDL and updates to
non-transactional tables). It never waits for SELECTs, or updates to
InnoDB tables to complete, for example.

If an “unsafe” statement is executed in the same connection that is
holding a LTFB lock, it fails with the following error:

ERROR 1880 (HY000): Can’t execute the query because you have a conflicting backup lock

UNLOCK TABLES releases the lock acquired by LOCK TABLES FOR BACKUP.

LOCK BINLOG 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 or Exec_Gtid_Set (i.e. master binary log coordinates
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 or
Executed_Gtid_Set. Connections that are currently blocked on the global
binlog lock can be identified by the “Waiting for binlog lock” status in
PROCESSLIST.

UNLOCK BINLOG
-------------

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

Privileges
----------

Both LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP require the
RELOAD privilege. The reason for that is to have the same requirements
as FTWRL.

Interaction with other global locks
-----------------------------------

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.

If the server is operating in the read-only mode (i.e. read_only=1),
statements that are unsafe for backups will be either blocked or fail
with an error, depending on whether they are executed in the same
connection that owns LTFB lock, or other connections.

MyISAM index and data buffering
----------------------------—--

MyISAM key buffering is normally write-through, i.e. by the time each
update to a MyISAM table is completed, all index updates are written to
disk. The only exception is delayed key writing feature which is
disabled by default.

When the global system variable delay_key_write is set to ALL, key
buffers for all MyISAM tables are not flushed between updates, so a
physical backup of those tables may result in broken MyISAM indexes. To
prevent this, LOCK TABLES FOR BACKUP will fail with an error if
delay_key_write=ALL. An attempt to set delay_key_write to ALL when
there’s an active backup lock will also fail with an error.

Another option to involve delayed key writing is to create MyISAM
tables with the DELAY_KEY_WRITE option and set the delay_key_write
variable to ON (which is the default). In this case, LOCK TABLES FOR
BACKUP will not be able to prevent stale index files from appearing in
the backup. Users are encouraged to set delay_key_writes to OFF in
my.cnf, or repair MyISAM indexes after restoring from a physical backup
created with backup locks.

MyISAM may also cache data for bulk inserts, e.g. when executing
multi-row INSERTs or LOAD DATA statements. Those caches, however, are
flushed between statements, so have no effect on physical backups as
long as we block all statements updating MyISAM tables.

mysqldump
---------

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.

If --master-data is used together with --single-transaction,
--lock-for-backup does not have any effect, i.e. FTWRL will still be
used to get the binary log coordinates. This limitation will be removed
after implementing the following blueprint:
https://blueprints.launchpad.net/percona-server/+spec/enhancements-for-start-transaction-with-consistent

New server and status variables
--------------------

have_backup_locks

  This is a server variable implemented to help other utilities decide
  what locking strategy can be implemented for a server. When
  available, the backup locks feature is supported by the server and
  the variable value is always YES.

Com_lock_tables_for_backup
Com_lock_binlog_for_backup
Com_unlock_binlog

  These status variables indicate the number of times the corresponding
  statements have been executed.

Revision history for this message
Alexey Kopytov (akopytov) wrote :

> 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.

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

> 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

Unmerged revisions

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'client/client_priv.h'
2--- client/client_priv.h 2013-06-25 13:13:06 +0000
3+++ client/client_priv.h 2014-02-20 11:26:00 +0000
4@@ -104,6 +104,7 @@
5 OPT_SERVER_PUBLIC_KEY,
6 OPT_ENABLE_CLEARTEXT_PLUGIN,
7 OPT_INNODB_OPTIMIZE_KEYS,
8+ OPT_LOCK_FOR_BACKUP,
9 OPT_MAX_CLIENT_OPTION
10 };
11
12
13=== modified file 'client/mysqldump.c'
14--- client/mysqldump.c 2013-12-16 08:45:31 +0000
15+++ client/mysqldump.c 2014-02-20 11:26:00 +0000
16@@ -143,6 +143,7 @@
17 static char * opt_mysql_unix_port=0;
18 static char *opt_bind_addr = NULL;
19 static int first_error=0;
20+static uint opt_lock_for_backup= 0;
21 static DYNAMIC_STRING extended_row;
22 #include <sslopt-vars.h>
23 FILE *md_result_file= 0;
24@@ -247,6 +248,11 @@
25 "Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START SLAVE' to bottom of dump.",
26 &opt_slave_apply, &opt_slave_apply, 0, GET_BOOL, NO_ARG,
27 0, 0, 0, 0, 0, 0},
28+ {"lock-for-backup", OPT_LOCK_FOR_BACKUP, "Use lightweight metadata locks "
29+ "to block updates to non-transactional tables and DDL to all tables. "
30+ "This works only with --single-transaction, otherwise this option is "
31+ "automatically converted to --lock-all-tables.", &opt_lock_for_backup,
32+ &opt_lock_for_backup, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
33 {"bind-address", 0, "IP address to bind to.",
34 (uchar**) &opt_bind_addr, (uchar**) &opt_bind_addr, 0, GET_STR,
35 REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
36@@ -995,6 +1001,23 @@
37 return(EX_USAGE);
38 }
39
40+ if (opt_lock_for_backup && opt_lock_all_tables)
41+ {
42+ fprintf(stderr, "%s: You can't use --lock-for-backup and "
43+ "--lock-all-tables at the same time.\n", my_progname);
44+ return(EX_USAGE);
45+ }
46+
47+ /*
48+ Convert --lock-for-backup to --lock-all-tables if --single-transaction is
49+ not specified.
50+ */
51+ if (!opt_single_transaction && opt_lock_for_backup)
52+ {
53+ opt_lock_all_tables= 1;
54+ opt_lock_for_backup= 0;
55+ }
56+
57 /* We don't delete master logs if slave data option */
58 if (opt_slave_data)
59 {
60@@ -5390,6 +5413,20 @@
61 "FLUSH TABLES WITH READ LOCK") );
62 }
63
64+/**
65+ Execute LOCK TABLES FOR BACKUP if supported by the server.
66+
67+ @note If LOCK TABLES FOR BACKUP is not supported by the server, then nothing
68+ is done and no error condition is returned.
69+
70+ @returns whether there was an error or not
71+*/
72+
73+static int do_lock_tables_for_backup(MYSQL *mysql_con)
74+{
75+ return mysql_query_with_error_report(mysql_con, 0,
76+ "LOCK TABLES FOR BACKUP");
77+}
78
79 static int do_unlock_tables(MYSQL *mysql_con)
80 {
81@@ -6153,6 +6190,35 @@
82 die(EX_MYSQLERR, DYNAMIC_STR_ERROR_MSG);
83 }
84
85+/**
86+ Check if the server supports LOCK TABLES FOR BACKUP.
87+
88+ @returns TRUE if there is support, FALSE otherwise.
89+*/
90+
91+static my_bool server_supports_backup_locks(void)
92+{
93+ MYSQL_RES *res;
94+ MYSQL_ROW row;
95+ my_bool rc;
96+
97+ if (mysql_query_with_error_report(mysql, &res,
98+ "SHOW VARIABLES LIKE 'have_backup_locks'"))
99+ return FALSE;
100+
101+ if ((row= mysql_fetch_row(res)) == NULL)
102+ {
103+ mysql_free_result(res);
104+ return FALSE;
105+ }
106+
107+ rc= mysql_num_fields(res) > 1 && !strcmp(row[1], "YES");
108+
109+ mysql_free_result(res);
110+
111+ return rc;
112+}
113+
114
115 int main(int argc, char **argv)
116 {
117@@ -6194,12 +6260,25 @@
118 if (!path)
119 write_header(md_result_file, *argv);
120
121+ if (opt_lock_for_backup && !server_supports_backup_locks())
122+ {
123+ fprintf(stderr, "%s: Error: --lock-for-backup was specified with "
124+ "--single-transaction, but the server does not support "
125+ "LOCK TABLES FOR BACKUP.\n",
126+ my_progname);
127+ goto err;
128+ }
129+
130 if (opt_slave_data && do_stop_slave_sql(mysql))
131 goto err;
132
133 if ((opt_lock_all_tables || opt_master_data ||
134- (opt_single_transaction && flush_logs)) &&
135- do_flush_tables_read_lock(mysql))
136+ (opt_single_transaction && flush_logs)))
137+ {
138+ if (do_flush_tables_read_lock(mysql))
139+ goto err;
140+ }
141+ else if (opt_lock_for_backup && do_lock_tables_for_backup(mysql))
142 goto err;
143
144 /*
145@@ -6244,7 +6323,8 @@
146 goto err;
147 if (opt_slave_data && do_show_slave_status(mysql))
148 goto err;
149- if (opt_single_transaction && do_unlock_tables(mysql)) /* unlock but no commit! */
150+ if (opt_single_transaction && (!opt_lock_for_backup || opt_master_data) &&
151+ do_unlock_tables(mysql)) /* unlock but no commit! */
152 goto err;
153
154 if (opt_alltspcs)
155
156=== added file 'mysql-test/include/backup_locks_binlog.inc'
157--- mysql-test/include/backup_locks_binlog.inc 1970-01-01 00:00:00 +0000
158+++ mysql-test/include/backup_locks_binlog.inc 2014-02-20 11:26:00 +0000
159@@ -0,0 +1,803 @@
160+########################################################################
161+# Binlog-related backup locks tests
162+########################################################################
163+
164+--source include/have_innodb.inc
165+--source include/not_embedded.inc
166+--source include/have_archive.inc
167+--source include/have_csv.inc
168+--source include/have_blackhole.inc
169+--source include/have_log_bin.inc
170+
171+SELECT @@binlog_format;
172+
173+RESET MASTER;
174+
175+SHOW VARIABLES LIKE 'have_backup_locks';
176+
177+--echo #-----------------------------------------------------------------------
178+--echo # Setup
179+--echo #-----------------------------------------------------------------------
180+
181+# Save the initial number of concurrent sessions.
182+--source include/count_sessions.inc
183+
184+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
185+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
186+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
187+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
188+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
189+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
190+
191+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
192+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
193+CREATE VIEW v_memory AS SELECT * FROM t_memory;
194+CREATE VIEW v_csv AS SELECT * FROM t_csv;
195+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
196+CREATE VIEW v_archive AS SELECT * FROM t_archive;
197+
198+--echo #-----------------------------------------------------------------------
199+--echo # Single-threaded tests
200+--echo #-----------------------------------------------------------------------
201+
202+#
203+# LBFB + DML/DDL in the same connection
204+#
205+
206+LOCK BINLOG FOR BACKUP;
207+
208+DELETE FROM t_innodb;
209+INSERT INTO t_innodb VALUES(0);
210+UPDATE t_innodb SET a = 1;
211+REPLACE INTO t_innodb VALUES(1);
212+SELECT * from t_innodb;
213+HANDLER t_innodb OPEN;
214+HANDLER t_innodb READ a FIRST;
215+HANDLER t_innodb CLOSE;
216+
217+DELETE FROM t_myisam;
218+INSERT INTO t_myisam VALUES(0);
219+UPDATE t_myisam SET a = 1;
220+REPLACE INTO t_myisam VALUES(1);
221+SELECT * from t_myisam;
222+HANDLER t_myisam OPEN;
223+HANDLER t_myisam READ a FIRST;
224+HANDLER t_myisam CLOSE;
225+
226+DELETE FROM t_memory;
227+INSERT INTO t_memory VALUES(0);
228+UPDATE t_memory SET a = 1;
229+REPLACE INTO t_memory VALUES(1);
230+SELECT * from t_memory;
231+
232+DELETE FROM t_archive;
233+INSERT INTO t_archive VALUES(0);
234+SELECT * from t_archive;
235+
236+DELETE FROM t_csv;
237+INSERT INTO t_csv VALUES(0);
238+UPDATE t_csv SET a = 1;
239+REPLACE INTO t_csv VALUES(1);
240+SELECT * from t_csv;
241+
242+DELETE FROM t_blackhole;
243+INSERT INTO t_blackhole VALUES(0);
244+UPDATE t_blackhole SET a = 1;
245+REPLACE INTO t_blackhole VALUES(1);
246+SELECT * from t_blackhole;
247+
248+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
249+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
250+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
251+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
252+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
253+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
254+
255+DROP TABLE tt_innodb;
256+DROP TABLE tt_myisam;
257+DROP TABLE tt_memory;
258+DROP TABLE tt_csv;
259+DROP TABLE tt_blackhole;
260+DROP TABLE tt_archive;
261+
262+--error ER_BAD_TABLE_ERROR
263+DROP TABLE non_existing;
264+
265+TRUNCATE TABLE t_innodb;
266+TRUNCATE TABLE t_myisam;
267+TRUNCATE TABLE t_memory;
268+TRUNCATE TABLE t_csv;
269+TRUNCATE TABLE t_blackhole;
270+
271+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
272+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
273+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
274+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
275+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
276+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
277+
278+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
279+ALTER TABLE t_innodb DROP COLUMN b;
280+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
281+ALTER TABLE t_myisam DROP COLUMN b;
282+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
283+ALTER TABLE t_memory DROP COLUMN b;
284+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
285+ALTER TABLE t_csv DROP COLUMN b;
286+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
287+ALTER TABLE t_blackhole DROP COLUMN b;
288+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
289+ALTER TABLE t_archive DROP COLUMN b;
290+
291+ALTER TABLE t_innodb ADD KEY tmp (a);
292+ALTER TABLE t_innodb DROP KEY tmp;
293+ALTER TABLE t_myisam ADD KEY tmp (a);
294+ALTER TABLE t_myisam DROP KEY tmp;
295+ALTER TABLE t_memory ADD KEY tmp (a);
296+ALTER TABLE t_memory DROP KEY tmp;
297+ALTER TABLE t_blackhole ADD KEY tmp (a);
298+ALTER TABLE t_blackhole DROP KEY tmp;
299+
300+CREATE DATABASE test1;
301+DROP DATABASE test1;
302+
303+delimiter |;
304+
305+CREATE PROCEDURE p1()
306+BEGIN
307+ SELECT 1;
308+END|
309+
310+DROP PROCEDURE p1|
311+
312+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
313+BEGIN
314+ RETURN 1;
315+END|
316+
317+DROP FUNCTION f1|
318+
319+delimiter ;|
320+
321+CREATE VIEW v1 AS SELECT * FROM t_innodb;
322+DROP VIEW v1;
323+
324+INSERT INTO v_innodb VALUES(1);
325+INSERT INTO v_blackhole VALUES(1);
326+INSERT INTO v_myisam VALUES(1);
327+INSERT INTO v_csv VALUES(1);
328+INSERT INTO v_memory VALUES(1);
329+INSERT INTO v_archive VALUES(1);
330+
331+UNLOCK BINLOG;
332+
333+#
334+# Test incompatibility with delay_key_write=ALL
335+#
336+SELECT @@delay_key_write;
337+
338+SET GLOBAL delay_key_write=ALL;
339+--error ER_OPTION_PREVENTS_STATEMENT
340+LOCK TABLES FOR BACKUP;
341+
342+SET GLOBAL delay_key_write=default;
343+SELECT @@delay_key_write;
344+
345+#
346+# read-only=1 + backup locks
347+#
348+
349+SET GLOBAL read_only=1;
350+
351+INSERT INTO t_innodb VALUES(0);
352+INSERT INTO t_myisam VALUES(0);
353+
354+LOCK TABLES FOR BACKUP;
355+
356+INSERT INTO t_innodb VALUES(0);
357+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
358+INSERT INTO t_myisam VALUES(0);
359+
360+UNLOCK TABLES;
361+
362+INSERT INTO t_innodb VALUES(0);
363+INSERT INTO t_myisam VALUES(0);
364+
365+SET GLOBAL read_only=0;
366+
367+#
368+# Test that temporary tables are not affected by backup locks
369+#
370+LOCK TABLES FOR BACKUP;
371+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
372+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
373+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
374+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
375+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
376+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
377+
378+INSERT INTO tt_innodb VALUES(5);
379+INSERT INTO tt_myisam VALUES(5);
380+INSERT INTO tt_memory VALUES(5);
381+INSERT INTO tt_csv VALUES(5);
382+INSERT INTO tt_blackhole VALUES(5);
383+INSERT INTO tt_archive VALUES(5);
384+
385+LOCK BINLOG FOR BACKUP;
386+
387+START TRANSACTION;
388+SELECT * FROM tt_archive;
389+SELECT * FROM tt_blackhole;
390+SELECT * FROM tt_memory;
391+SELECT * FROM tt_innodb;
392+SELECT * FROM tt_myisam;
393+INSERT INTO tt_innodb VALUES(6);
394+INSERT INTO tt_myisam VALUES(6);
395+INSERT INTO tt_memory VALUES(6);
396+INSERT INTO tt_csv VALUES(6);
397+INSERT INTO tt_blackhole VALUES(6);
398+INSERT INTO tt_archive VALUES(6);
399+COMMIT;
400+
401+SELECT * FROM tt_archive;
402+SELECT * FROM tt_blackhole;
403+SELECT * FROM tt_memory;
404+SELECT * FROM tt_innodb;
405+SELECT * FROM tt_myisam;
406+
407+DROP TEMPORARY TABLE tt_innodb;
408+DROP TEMPORARY TABLE tt_myisam;
409+DROP TEMPORARY TABLE tt_memory;
410+DROP TEMPORARY TABLE tt_csv;
411+DROP TEMPORARY TABLE tt_blackhole;
412+DROP TEMPORARY TABLE tt_archive;
413+
414+UNLOCK BINLOG;
415+
416+UNLOCK TABLES;
417+
418+#
419+# test that AUTOCOMMIT updates succeed with a BINLOG lock in the same connection with binlog disabled
420+#
421+
422+SELECT @@log_bin;
423+
424+LOCK BINLOG FOR BACKUP;
425+
426+INSERT INTO t_innodb VALUES(1);
427+INSERT INTO t_myisam VALUES(1);
428+
429+UNLOCK BINLOG;
430+
431+#
432+# Log tables
433+#
434+
435+SET @old_general_log = @@general_log;
436+SET @old_slow_query_log = @@slow_query_log;
437+SET @old_log_output = @@log_output;
438+SET @old_long_query_time = @@SESSION.long_query_time;
439+
440+SET SESSION long_query_time = 0;
441+SET GLOBAL log_output = 'TABLE';
442+SET GLOBAL general_log = ON;
443+SET GLOBAL slow_query_log = ON;
444+
445+LOCK TABLES FOR BACKUP;
446+
447+SELECT 1;
448+
449+LOCK BINLOG FOR BACKUP;
450+
451+SELECT 1;
452+
453+UNLOCK BINLOG;
454+
455+UNLOCK TABLES;
456+
457+SET SESSION long_query_time = @old_long_query_time;
458+SET GLOBAL log_output = @old_log_output;
459+SET GLOBAL slow_query_log = @old_slow_query_log;
460+SET GLOBAL general_log = @old_general_log;
461+
462+--echo #-----------------------------------------------------------------------
463+--echo # Multi-threaded tests
464+--echo #-----------------------------------------------------------------------
465+
466+--echo # connection default
467+
468+CREATE USER user@localhost;
469+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
470+
471+--connect(con1,localhost,root,,)
472+
473+--connect(con2,localhost,user,,)
474+
475+--connection default
476+LOCK TABLES FOR BACKUP;
477+
478+--connection con1
479+--echo # connection con1
480+
481+SET SESSION lock_wait_timeout = 1;
482+
483+--error ER_LOCK_WAIT_TIMEOUT
484+SET GLOBAL delay_key_write=ALL;
485+
486+SET SESSION lock_wait_timeout = DEFAULT;
487+
488+--connection default
489+--echo # connection default
490+UNLOCK TABLES;
491+
492+#
493+# read-only=1 + backup locks
494+#
495+
496+--connection con2
497+--echo # connection con2
498+
499+START TRANSACTION;
500+INSERT INTO t_innodb VALUES(0);
501+
502+--connection default
503+--echo # connection default
504+
505+SET GLOBAL read_only=1;
506+
507+--connection con2
508+--echo # connection con2
509+
510+--error ER_OPTION_PREVENTS_STATEMENT
511+COMMIT;
512+
513+--error ER_OPTION_PREVENTS_STATEMENT
514+INSERT INTO t_innodb VALUES(0);
515+--error ER_OPTION_PREVENTS_STATEMENT
516+INSERT INTO t_myisam VALUES(0);
517+
518+--connection con1
519+--echo # connection con1
520+
521+LOCK TABLES FOR BACKUP;
522+
523+--connection con2
524+--echo # connection con2
525+
526+--error ER_OPTION_PREVENTS_STATEMENT
527+INSERT INTO t_innodb VALUES(0);
528+--error ER_OPTION_PREVENTS_STATEMENT
529+INSERT INTO t_myisam VALUES(0);
530+
531+--connection default
532+--echo # connection default
533+
534+SET GLOBAL read_only=0;
535+
536+--connection con2
537+--echo # connection con2
538+
539+SET SESSION lock_wait_timeout = 1;
540+
541+INSERT INTO t_innodb VALUES(0);
542+--error ER_LOCK_WAIT_TIMEOUT
543+INSERT INTO t_myisam VALUES(0);
544+
545+SET SESSION lock_wait_timeout = 0;
546+
547+--connection default
548+--echo # connection default
549+
550+SET GLOBAL read_only=1;
551+
552+--connection con2
553+--echo # connection con2
554+
555+--error ER_OPTION_PREVENTS_STATEMENT
556+INSERT INTO t_innodb VALUES(0);
557+--error ER_OPTION_PREVENTS_STATEMENT
558+INSERT INTO t_myisam VALUES(0);
559+
560+--connection con1
561+--echo # connection con1
562+
563+UNLOCK TABLES;
564+
565+--connection con2
566+--echo # connection con2
567+
568+--error ER_OPTION_PREVENTS_STATEMENT
569+INSERT INTO t_innodb VALUES(0);
570+--error ER_OPTION_PREVENTS_STATEMENT
571+INSERT INTO t_myisam VALUES(0);
572+
573+
574+--connection default
575+--echo # connection default
576+
577+SET GLOBAL read_only=0;
578+
579+#
580+# Test that temporary tables are not affected by tables lock, but updates that
581+# would write into the binary log are blocked by the binlog lock.
582+#
583+--connection default
584+--echo # connection default
585+LOCK TABLES FOR BACKUP;
586+
587+--connection con1
588+--echo # connection con1
589+
590+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
591+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
592+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
593+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
594+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
595+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
596+
597+INSERT INTO tt_innodb VALUES(5);
598+INSERT INTO tt_myisam VALUES(5);
599+INSERT INTO tt_memory VALUES(5);
600+INSERT INTO tt_csv VALUES(5);
601+INSERT INTO tt_blackhole VALUES(5);
602+INSERT INTO tt_archive VALUES(5);
603+
604+--connection default
605+--echo # connection default
606+LOCK BINLOG FOR BACKUP;
607+
608+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
609+
610+--connection con1
611+--echo # connection con1
612+
613+SET SESSION lock_wait_timeout = 1;
614+
615+START TRANSACTION;
616+if (`SELECT @@binlog_format = 'STATEMENT'`)
617+{
618+ --error ER_LOCK_WAIT_TIMEOUT
619+ INSERT INTO tt_myisam VALUES(6);
620+}
621+if (!`SELECT @@binlog_format = 'STATEMENT'`)
622+{
623+ INSERT INTO tt_myisam VALUES(6);
624+}
625+
626+# Updates to temporary tables are not binlogged in the ROW mode
627+INSERT INTO tt_innodb VALUES(6);
628+SELECT * FROM tt_archive;
629+SELECT * FROM tt_blackhole;
630+SELECT * FROM tt_memory;
631+SELECT * FROM tt_innodb;
632+SELECT * FROM tt_myisam;
633+SELECT * FROM tt_csv;
634+if (`SELECT @@binlog_format = 'STATEMENT'`)
635+{
636+ --error ER_LOCK_WAIT_TIMEOUT
637+ COMMIT;
638+if (!`SELECT @@binlog_format = 'STATEMENT'`)
639+{
640+ COMMIT;
641+}
642+}
643+
644+--connection default
645+--echo # connection default
646+
647+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
648+--disable_query_log
649+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
650+--enable_query_log
651+
652+UNLOCK BINLOG;
653+UNLOCK TABLES;
654+
655+--connection con1
656+--echo # connection con1
657+
658+COMMIT;
659+
660+DROP TEMPORARY TABLE tt_innodb;
661+DROP TEMPORARY TABLE tt_myisam;
662+DROP TEMPORARY TABLE tt_memory;
663+DROP TEMPORARY TABLE tt_csv;
664+DROP TEMPORARY TABLE tt_blackhole;
665+DROP TEMPORARY TABLE tt_archive;
666+
667+--connection default
668+--echo # connection default
669+
670+LOCK BINLOG FOR BACKUP;
671+
672+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
673+
674+--connection con1
675+--echo # connection con1
676+
677+#
678+# test that AUTOCOMMIT updates are blocked with a BINLOG lock in another
679+# connection with binlog enabled
680+#
681+
682+SET SESSION lock_wait_timeout = 1;
683+
684+--error ER_LOCK_WAIT_TIMEOUT
685+INSERT INTO t_innodb VALUES(1);
686+--error ER_LOCK_WAIT_TIMEOUT
687+INSERT INTO t_myisam VALUES(1);
688+
689+SET SESSION lock_wait_timeout = DEFAULT;
690+
691+--connection default
692+--echo # connection default
693+
694+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
695+--disable_query_log
696+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
697+--enable_query_log
698+
699+UNLOCK BINLOG;
700+
701+#
702+# LTFB + DDL / DML in another connection
703+#
704+
705+--connection default
706+--echo # connection default
707+
708+LOCK BINLOG FOR BACKUP;
709+
710+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
711+
712+--connection con1
713+--echo # connection con1
714+
715+SET SESSION lock_wait_timeout = 1;
716+
717+--error ER_LOCK_WAIT_TIMEOUT
718+DELETE FROM t_innodb;
719+--error ER_LOCK_WAIT_TIMEOUT
720+INSERT INTO t_innodb VALUES(0);
721+--error ER_LOCK_WAIT_TIMEOUT
722+UPDATE t_innodb SET a = 1;
723+--error ER_LOCK_WAIT_TIMEOUT
724+REPLACE INTO t_innodb VALUES(1);
725+SELECT * from t_innodb;
726+HANDLER t_innodb OPEN;
727+HANDLER t_innodb READ a FIRST;
728+HANDLER t_innodb CLOSE;
729+
730+START TRANSACTION;
731+SELECT * from t_innodb;
732+DELETE FROM t_innodb;
733+INSERT INTO t_innodb VALUES(0);
734+UPDATE t_innodb SET a = 1;
735+REPLACE INTO t_innodb VALUES(1);
736+--error ER_LOCK_WAIT_TIMEOUT
737+COMMIT;
738+
739+SELECT * FROM t_innodb;
740+COMMIT;
741+
742+START TRANSACTION;
743+--error ER_LOCK_WAIT_TIMEOUT
744+INSERT INTO t_myisam VALUES(1);
745+COMMIT;
746+
747+--error ER_LOCK_WAIT_TIMEOUT
748+INSERT INTO t_myisam VALUES(0);
749+--error ER_LOCK_WAIT_TIMEOUT
750+UPDATE t_myisam SET a = 1;
751+SELECT * FROM t_myisam;
752+HANDLER t_myisam OPEN;
753+HANDLER t_myisam READ a FIRST;
754+HANDLER t_myisam CLOSE;
755+
756+# Blackhole updates are not binlogged in the ROW mode
757+if (`SELECT @@binlog_format = 'ROW'`)
758+{
759+ DELETE FROM t_blackhole;
760+}
761+if (!`SELECT @@binlog_format = 'ROW'`)
762+{
763+--error ER_LOCK_WAIT_TIMEOUT
764+ DELETE FROM t_blackhole;
765+}
766+--error ER_LOCK_WAIT_TIMEOUT
767+INSERT INTO t_blackhole VALUES(0);
768+# Blackhole updates are not binlogged in the ROW mode
769+if (`SELECT @@binlog_format = 'ROW'`)
770+{
771+ UPDATE t_blackhole SET a = 1;
772+}
773+if (!`SELECT @@binlog_format = 'ROW'`)
774+{
775+ --error ER_LOCK_WAIT_TIMEOUT
776+ UPDATE t_blackhole SET a = 1;
777+}
778+--error ER_LOCK_WAIT_TIMEOUT
779+REPLACE INTO t_blackhole VALUES(1);
780+SELECT * FROM t_blackhole;
781+
782+--error ER_LOCK_WAIT_TIMEOUT
783+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
784+
785+--error ER_LOCK_WAIT_TIMEOUT
786+DROP TABLE tt_innodb;
787+
788+--error ER_BAD_TABLE_ERROR
789+DROP TABLE non_existing;
790+
791+--error ER_TABLE_EXISTS_ERROR
792+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
793+
794+--error ER_LOCK_WAIT_TIMEOUT
795+TRUNCATE TABLE t_innodb;
796+
797+--error ER_LOCK_WAIT_TIMEOUT
798+RENAME TABLE t_innodb TO tmp, tmp to t_innodb;
799+
800+--error ER_LOCK_WAIT_TIMEOUT
801+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
802+
803+--error ER_LOCK_WAIT_TIMEOUT
804+ALTER TABLE t_innodb ADD KEY (a);
805+
806+--error ER_LOCK_WAIT_TIMEOUT
807+CREATE DATABASE test1;
808+
809+--error ER_LOCK_WAIT_TIMEOUT
810+DROP DATABASE test1;
811+
812+--error ER_LOCK_WAIT_TIMEOUT
813+CREATE PROCEDURE p1() SELECT 1;
814+
815+--error ER_LOCK_WAIT_TIMEOUT
816+DROP PROCEDURE p1;
817+
818+--error ER_LOCK_WAIT_TIMEOUT
819+CREATE VIEW v1 AS SELECT * FROM t_innodb;
820+
821+--error ER_LOCK_WAIT_TIMEOUT
822+DROP VIEW v1;
823+
824+--error ER_LOCK_WAIT_TIMEOUT
825+INSERT INTO v_innodb VALUES(1);
826+
827+--connection default
828+--echo # connection default
829+
830+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
831+--disable_query_log
832+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
833+--enable_query_log
834+
835+UNLOCK BINLOG;
836+
837+--connection con1
838+--echo # connection con1
839+
840+# DDLs are not transactional, so previous DDL statements against t_innodb will
841+# be executed despite the error
842+SHOW CREATE TABLE t_innodb;
843+
844+DROP TABLE t_innodb;
845+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
846+
847+--connection default
848+--echo # connection default
849+
850+#
851+# INSERT DELAYED
852+#
853+
854+LOCK BINLOG FOR BACKUP;
855+
856+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
857+
858+--connection con1
859+--echo # connection con1
860+
861+if (`SELECT @@binlog_format = 'STATEMENT'`)
862+{
863+ --error ER_LOCK_WAIT_TIMEOUT
864+ INSERT DELAYED INTO t_myisam VALUES(0);
865+}
866+if (!`SELECT @@binlog_format = 'STATEMENT'`)
867+{
868+ INSERT DELAYED INTO t_myisam VALUES(0);
869+}
870+
871+--connection default
872+--echo # connection default
873+
874+if (!`SELECT @@binlog_format = 'STATEMENT'`)
875+{
876+ let $wait_condition=
877+ SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
878+ WHERE STATE = "Waiting for binlog lock" AND
879+ COMMAND = "Delayed insert";
880+ --source include/wait_condition.inc
881+}
882+
883+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
884+--disable_query_log
885+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
886+--enable_query_log
887+
888+UNLOCK BINLOG;
889+
890+if (!`SELECT @@binlog_format = 'STATEMENT'`)
891+{
892+ let $wait_condition=
893+ SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST
894+ WHERE STATE = "Waiting for binlog lock" AND
895+ COMMAND = "Delayed";
896+ --source include/wait_condition.inc
897+}
898+
899+SELECT * FROm t_myisam;
900+
901+
902+#
903+# Log tables
904+#
905+
906+SET @old_general_log = @@general_log;
907+SET @old_slow_query_log = @@slow_query_log;
908+SET @old_log_output = @@log_output;
909+
910+SET GLOBAL log_output = 'TABLE';
911+SET GLOBAL general_log = ON;
912+SET GLOBAL slow_query_log = ON;
913+
914+LOCK BINLOG FOR BACKUP;
915+
916+--let $orig_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
917+
918+--connection con1
919+--echo # connection con1
920+
921+SET @old_long_query_time = @@SESSION.long_query_time;
922+SET SESSION long_query_time = 0;
923+
924+SELECT 1;
925+
926+--connection default
927+--echo # connection default
928+
929+SELECT 1;
930+
931+--connection con1
932+--echo # connection con1
933+
934+SET SESSION long_query_time = @old_long_query_time;
935+
936+--connection default
937+--echo # connection default
938+
939+--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
940+--disable_query_log
941+--eval SELECT $orig_binlog_pos = $binlog_pos AS must_be_1
942+--enable_query_log
943+
944+UNLOCK BINLOG;
945+
946+SET GLOBAL log_output = @old_log_output;
947+SET GLOBAL slow_query_log = @old_slow_query_log;
948+SET GLOBAL general_log = @old_general_log;
949+
950+--echo #-----------------------------------------------------------------------
951+--echo # Cleanup
952+--echo #-----------------------------------------------------------------------
953+
954+DROP USER user@localhost;
955+
956+--disconnect con1
957+--disconnect con2
958+
959+--source include/wait_until_count_sessions.inc
960+
961+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
962+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;
963
964=== added file 'mysql-test/r/backup_locks.result'
965--- mysql-test/r/backup_locks.result 1970-01-01 00:00:00 +0000
966+++ mysql-test/r/backup_locks.result 2014-02-20 11:26:00 +0000
967@@ -0,0 +1,883 @@
968+SHOW VARIABLES LIKE 'have_backup_locks';
969+Variable_name Value
970+have_backup_locks YES
971+#-----------------------------------------------------------------------
972+# Setup
973+#-----------------------------------------------------------------------
974+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
975+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
976+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
977+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
978+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
979+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
980+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
981+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
982+CREATE VIEW v_memory AS SELECT * FROM t_memory;
983+CREATE VIEW v_csv AS SELECT * FROM t_csv;
984+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
985+CREATE VIEW v_archive AS SELECT * FROM t_archive;
986+#-----------------------------------------------------------------------
987+# Basic syntax checks
988+#-----------------------------------------------------------------------
989+LOCK TABLES t_innodb FOR BACKUP;
990+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
991+LOCK TABLES t_innodb READ FOR BACKUP;
992+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
993+LOCK TABLES t_innodb FOR BACKUP READ;
994+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
995+LOCK BINLOG t_innodb FOR BACKUP;
996+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
997+LOCK BINLOG t_innodb READ FOR BACKUP;
998+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
999+LOCK BINLOG t_innodb FOR BACKUP READ;
1000+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
1001+LOCK TABLES FOR BACKUP;
1002+UNLOCK TABLES;
1003+LOCK BINLOG FOR BACKUP;
1004+UNLOCK BINLOG;
1005+#-----------------------------------------------------------------------
1006+# No backup locks are allowed in stored routines
1007+#-----------------------------------------------------------------------
1008+CREATE PROCEDURE p1()
1009+BEGIN
1010+LOCK TABLES FOR BACKUP;
1011+END|
1012+ERROR 0A000: LOCK is not allowed in stored procedures
1013+CREATE PROCEDURE p2()
1014+BEGIN
1015+LOCK BINLOG FOR BACKUP;
1016+END|
1017+ERROR 0A000: LOCK is not allowed in stored procedures
1018+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
1019+BEGIN
1020+LOCK TABLES FOR BACKUP;
1021+RETURN 1;
1022+END|
1023+ERROR 0A000: LOCK is not allowed in stored procedures
1024+CREATE FUNCTION f2() RETURNS INT DETERMINISTIC
1025+BEGIN
1026+LOCK BINLOG FOR BACKUP;
1027+RETURN 1;
1028+END|
1029+ERROR 0A000: LOCK is not allowed in stored procedures
1030+#-----------------------------------------------------------------------
1031+# Single-threaded tests
1032+#-----------------------------------------------------------------------
1033+SET SESSION lock_wait_timeout=1;
1034+FLUSH STATUS;
1035+LOCK TABLES FOR BACKUP;
1036+LOCK TABLES FOR BACKUP;
1037+SHOW STATUS LIKE 'Com_lock_tables_for_backup';
1038+Variable_name Value
1039+Com_lock_tables_for_backup 2
1040+UNLOCK TABLES;
1041+SHOW STATUS LIKE 'Com_unlock_tables';
1042+Variable_name Value
1043+Com_unlock_tables 1
1044+LOCK BINLOG FOR BACKUP;
1045+LOCK BINLOG FOR BACKUP;
1046+SHOW STATUS LIKE 'Com_lock_binlog_for_backup';
1047+Variable_name Value
1048+Com_lock_binlog_for_backup 2
1049+UNLOCK BINLOG;
1050+SHOW STATUS LIKE 'Com_unlock_binlog';
1051+Variable_name Value
1052+Com_unlock_binlog 1
1053+LOCK TABLES FOR BACKUP;
1054+DELETE FROM t_innodb;
1055+INSERT INTO t_innodb VALUES(0);
1056+UPDATE t_innodb SET a = 1;
1057+REPLACE INTO t_innodb VALUES(1);
1058+SELECT * FROM t_innodb;
1059+a
1060+1
1061+1
1062+HANDLER t_innodb OPEN;
1063+HANDLER t_innodb READ a FIRST;
1064+a
1065+1
1066+HANDLER t_innodb CLOSE;
1067+DELETE FROM t_myisam;
1068+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1069+INSERT INTO t_myisam VALUES(0);
1070+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1071+INSERT DELAYED INTO t_myisam VALUES(0);
1072+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1073+UPDATE t_myisam SET a = 1;
1074+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1075+REPLACE INTO t_myisam VALUES(1);
1076+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1077+SELECT * FROM t_myisam;
1078+a
1079+HANDLER t_myisam OPEN;
1080+HANDLER t_myisam READ a FIRST;
1081+a
1082+HANDLER t_myisam CLOSE;
1083+DELETE FROM t_memory;
1084+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1085+INSERT INTO t_memory VALUES(0);
1086+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1087+INSERT DELAYED INTO t_memory VALUES(0);
1088+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1089+UPDATE t_memory SET a = 1;
1090+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1091+REPLACE INTO t_memory VALUES(1);
1092+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1093+SELECT * FROM t_memory;
1094+a
1095+DELETE FROM t_archive;
1096+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1097+INSERT INTO t_archive VALUES(0);
1098+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1099+INSERT DELAYED INTO t_archive VALUES(0);
1100+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1101+UPDATE t_archive SET a = 1;
1102+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1103+REPLACE INTO t_archive VALUES(1);
1104+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1105+SELECT * FROM t_archive;
1106+a
1107+DELETE FROM t_csv;
1108+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1109+INSERT INTO t_csv VALUES(0);
1110+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1111+UPDATE t_csv SET a = 1;
1112+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1113+REPLACE INTO t_csv VALUES(1);
1114+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1115+SELECT * FROM t_csv;
1116+a
1117+DELETE FROM t_blackhole;
1118+INSERT INTO t_blackhole VALUES(0);
1119+INSERT DELAYED INTO t_blackhole VALUES(0);
1120+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1121+UPDATE t_blackhole SET a = 1;
1122+REPLACE INTO t_blackhole VALUES(1);
1123+SELECT * FROM t_blackhole;
1124+a
1125+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
1126+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1127+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
1128+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1129+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
1130+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1131+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
1132+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1133+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
1134+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1135+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
1136+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1137+DROP TABLE t_innodb;
1138+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1139+DROP TABLE t_myisam;
1140+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1141+DROP TABLE t_memory;
1142+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1143+DROP TABLE t_csv;
1144+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1145+DROP TABLE t_blackhole;
1146+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1147+DROP TABLE t_archive;
1148+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1149+DROP TABLE non_existing;
1150+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1151+TRUNCATE TABLE t_innodb;
1152+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1153+TRUNCATE TABLE t_myisam;
1154+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1155+TRUNCATE TABLE t_memory;
1156+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1157+TRUNCATE TABLE t_csv;
1158+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1159+TRUNCATE TABLE t_blackhole;
1160+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1161+TRUNCATE TABLE t_archive;
1162+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1163+RENAME TABLE t_innodb TO tmp;
1164+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1165+RENAME TABLE t_myisam TO tmp;
1166+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1167+RENAME TABLE t_memory TO tmp;
1168+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1169+RENAME TABLE t_csv TO tmp;
1170+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1171+RENAME TABLE t_blackhole TO tmp;
1172+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1173+RENAME TABLE t_archive TO tmp;
1174+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1175+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
1176+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1177+ALTER TABLE t_myisam ADD COLUMN b CHAR(10);
1178+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1179+ALTER TABLE t_memory ADD COLUMN b CHAR(10);
1180+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1181+ALTER TABLE t_csv ADD COLUMN b CHAR(10);
1182+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1183+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10);
1184+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1185+ALTER TABLE t_archive ADD COLUMN b CHAR(10);
1186+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1187+ALTER TABLE t_innodb ADD KEY (a);
1188+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1189+ALTER TABLE t_myisam ADD KEY (a);
1190+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1191+ALTER TABLE t_memory ADD KEY (a);
1192+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1193+ALTER TABLE t_csv ADD KEY (a);
1194+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1195+ALTER TABLE t_blackhole ADD KEY (a);
1196+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1197+ALTER TABLE t_archive ADD KEY (a);
1198+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1199+DROP DATABASE test;
1200+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1201+CREATE DATABASE test1;
1202+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1203+CREATE PROCEDURE p1()
1204+BEGIN
1205+SELECT 1;
1206+END|
1207+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1208+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
1209+BEGIN
1210+RETURN 1;
1211+END|
1212+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1213+CREATE VIEW v1 AS SELECT * FROM t_innodb;
1214+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1215+INSERT INTO v_innodb VALUES(1);
1216+INSERT INTO v_blackhole VALUES(1);
1217+INSERT INTO v_myisam VALUES(1);
1218+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1219+INSERT INTO v_csv VALUES(1);
1220+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1221+INSERT INTO v_memory VALUES(1);
1222+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1223+INSERT INTO v_archive VALUES(1);
1224+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1225+UNLOCK TABLES;
1226+LOCK BINLOG FOR BACKUP;
1227+DELETE FROM t_innodb;
1228+INSERT INTO t_innodb VALUES(0);
1229+UPDATE t_innodb SET a = 1;
1230+REPLACE INTO t_innodb VALUES(1);
1231+SELECT * from t_innodb;
1232+a
1233+1
1234+1
1235+HANDLER t_innodb OPEN;
1236+HANDLER t_innodb READ a FIRST;
1237+a
1238+1
1239+HANDLER t_innodb CLOSE;
1240+DELETE FROM t_myisam;
1241+INSERT INTO t_myisam VALUES(0);
1242+UPDATE t_myisam SET a = 1;
1243+REPLACE INTO t_myisam VALUES(1);
1244+SELECT * from t_myisam;
1245+a
1246+1
1247+1
1248+HANDLER t_myisam OPEN;
1249+HANDLER t_myisam READ a FIRST;
1250+a
1251+1
1252+HANDLER t_myisam CLOSE;
1253+DELETE FROM t_memory;
1254+INSERT INTO t_memory VALUES(0);
1255+UPDATE t_memory SET a = 1;
1256+REPLACE INTO t_memory VALUES(1);
1257+SELECT * from t_memory;
1258+a
1259+1
1260+1
1261+DELETE FROM t_archive;
1262+INSERT INTO t_archive VALUES(0);
1263+SELECT * from t_archive;
1264+a
1265+0
1266+DELETE FROM t_csv;
1267+INSERT INTO t_csv VALUES(0);
1268+UPDATE t_csv SET a = 1;
1269+REPLACE INTO t_csv VALUES(1);
1270+SELECT * from t_csv;
1271+a
1272+1
1273+1
1274+DELETE FROM t_blackhole;
1275+INSERT INTO t_blackhole VALUES(0);
1276+UPDATE t_blackhole SET a = 1;
1277+REPLACE INTO t_blackhole VALUES(1);
1278+SELECT * from t_blackhole;
1279+a
1280+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
1281+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
1282+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
1283+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
1284+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
1285+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
1286+DROP TABLE tt_innodb;
1287+DROP TABLE tt_myisam;
1288+DROP TABLE tt_memory;
1289+DROP TABLE tt_csv;
1290+DROP TABLE tt_blackhole;
1291+DROP TABLE tt_archive;
1292+DROP TABLE non_existing;
1293+ERROR 42S02: Unknown table 'test.non_existing'
1294+TRUNCATE TABLE t_innodb;
1295+TRUNCATE TABLE t_myisam;
1296+TRUNCATE TABLE t_memory;
1297+TRUNCATE TABLE t_csv;
1298+TRUNCATE TABLE t_blackhole;
1299+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
1300+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
1301+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
1302+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
1303+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
1304+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
1305+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
1306+ALTER TABLE t_innodb DROP COLUMN b;
1307+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
1308+ALTER TABLE t_myisam DROP COLUMN b;
1309+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
1310+ALTER TABLE t_memory DROP COLUMN b;
1311+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
1312+ALTER TABLE t_csv DROP COLUMN b;
1313+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
1314+ALTER TABLE t_blackhole DROP COLUMN b;
1315+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
1316+ALTER TABLE t_archive DROP COLUMN b;
1317+ALTER TABLE t_innodb ADD KEY tmp (a);
1318+Warnings:
1319+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
1320+ALTER TABLE t_innodb DROP KEY tmp;
1321+ALTER TABLE t_myisam ADD KEY tmp (a);
1322+Warnings:
1323+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
1324+ALTER TABLE t_myisam DROP KEY tmp;
1325+ALTER TABLE t_memory ADD KEY tmp (a);
1326+Warnings:
1327+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
1328+ALTER TABLE t_memory DROP KEY tmp;
1329+ALTER TABLE t_blackhole ADD KEY tmp (a);
1330+Warnings:
1331+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
1332+ALTER TABLE t_blackhole DROP KEY tmp;
1333+CREATE DATABASE test1;
1334+DROP DATABASE test1;
1335+CREATE PROCEDURE p1()
1336+BEGIN
1337+SELECT 1;
1338+END|
1339+DROP PROCEDURE p1|
1340+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
1341+BEGIN
1342+RETURN 1;
1343+END|
1344+DROP FUNCTION f1|
1345+CREATE VIEW v1 AS SELECT * FROM t_innodb;
1346+DROP VIEW v1;
1347+INSERT INTO v_innodb VALUES(1);
1348+INSERT INTO v_blackhole VALUES(1);
1349+INSERT INTO v_myisam VALUES(1);
1350+INSERT INTO v_csv VALUES(1);
1351+INSERT INTO v_memory VALUES(1);
1352+INSERT INTO v_archive VALUES(1);
1353+UNLOCK BINLOG;
1354+SELECT @@delay_key_write;
1355+@@delay_key_write
1356+ON
1357+SET GLOBAL delay_key_write=ALL;
1358+LOCK TABLES FOR BACKUP;
1359+ERROR HY000: The MySQL server is running with the delay_key_write=ALL option so it cannot execute this statement
1360+SET GLOBAL delay_key_write=default;
1361+SELECT @@delay_key_write;
1362+@@delay_key_write
1363+ON
1364+SET GLOBAL read_only=1;
1365+INSERT INTO t_innodb VALUES(0);
1366+INSERT INTO t_myisam VALUES(0);
1367+LOCK TABLES FOR BACKUP;
1368+INSERT INTO t_innodb VALUES(0);
1369+INSERT INTO t_myisam VALUES(0);
1370+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
1371+UNLOCK TABLES;
1372+INSERT INTO t_innodb VALUES(0);
1373+INSERT INTO t_myisam VALUES(0);
1374+SET GLOBAL read_only=0;
1375+LOCK TABLES FOR BACKUP;
1376+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
1377+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
1378+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
1379+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
1380+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
1381+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
1382+INSERT INTO tt_innodb VALUES(5);
1383+INSERT INTO tt_myisam VALUES(5);
1384+INSERT INTO tt_memory VALUES(5);
1385+INSERT INTO tt_csv VALUES(5);
1386+INSERT INTO tt_blackhole VALUES(5);
1387+INSERT INTO tt_archive VALUES(5);
1388+LOCK BINLOG FOR BACKUP;
1389+START TRANSACTION;
1390+SELECT * FROM tt_archive;
1391+a
1392+5
1393+SELECT * FROM tt_blackhole;
1394+a
1395+SELECT * FROM tt_memory;
1396+a
1397+5
1398+SELECT * FROM tt_innodb;
1399+a
1400+5
1401+SELECT * FROM tt_myisam;
1402+a
1403+5
1404+INSERT INTO tt_innodb VALUES(6);
1405+INSERT INTO tt_myisam VALUES(6);
1406+INSERT INTO tt_memory VALUES(6);
1407+INSERT INTO tt_csv VALUES(6);
1408+INSERT INTO tt_blackhole VALUES(6);
1409+INSERT INTO tt_archive VALUES(6);
1410+COMMIT;
1411+SELECT * FROM tt_archive;
1412+a
1413+5
1414+6
1415+SELECT * FROM tt_blackhole;
1416+a
1417+SELECT * FROM tt_memory;
1418+a
1419+5
1420+6
1421+SELECT * FROM tt_innodb;
1422+a
1423+5
1424+6
1425+SELECT * FROM tt_myisam;
1426+a
1427+5
1428+6
1429+DROP TEMPORARY TABLE tt_innodb;
1430+DROP TEMPORARY TABLE tt_myisam;
1431+DROP TEMPORARY TABLE tt_memory;
1432+DROP TEMPORARY TABLE tt_csv;
1433+DROP TEMPORARY TABLE tt_blackhole;
1434+DROP TEMPORARY TABLE tt_archive;
1435+UNLOCK BINLOG;
1436+UNLOCK TABLES;
1437+SELECT @@log_bin;
1438+@@log_bin
1439+0
1440+LOCK BINLOG FOR BACKUP;
1441+INSERT INTO t_innodb VALUES(1);
1442+INSERT INTO t_myisam VALUES(1);
1443+UNLOCK BINLOG;
1444+SET @old_general_log = @@general_log;
1445+SET @old_slow_query_log = @@slow_query_log;
1446+SET @old_log_output = @@log_output;
1447+SET @old_long_query_time = @@SESSION.long_query_time;
1448+SET SESSION long_query_time = 0;
1449+SET GLOBAL log_output = 'TABLE';
1450+SET GLOBAL general_log = ON;
1451+SET GLOBAL slow_query_log = ON;
1452+LOCK TABLES FOR BACKUP;
1453+SELECT 1;
1454+1
1455+1
1456+LOCK BINLOG FOR BACKUP;
1457+SELECT 1;
1458+1
1459+1
1460+UNLOCK BINLOG;
1461+UNLOCK TABLES;
1462+SET SESSION long_query_time = @old_long_query_time;
1463+SET GLOBAL log_output = @old_log_output;
1464+SET GLOBAL slow_query_log = @old_slow_query_log;
1465+SET GLOBAL general_log = @old_general_log;
1466+SET SESSION lock_wait_timeout=default;
1467+#-----------------------------------------------------------------------
1468+# Multi-threaded tests
1469+#-----------------------------------------------------------------------
1470+# connection default
1471+SET GLOBAL lock_wait_timeout=1;
1472+CREATE USER user@localhost;
1473+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
1474+LOCK TABLES FOR BACKUP;
1475+# connection con1
1476+SET GLOBAL delay_key_write=ALL;
1477+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1478+# connection default
1479+UNLOCK TABLES;
1480+# connection con2
1481+START TRANSACTION;
1482+INSERT INTO t_innodb VALUES(0);
1483+# connection default
1484+SET GLOBAL read_only=1;
1485+# connection con2
1486+COMMIT;
1487+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
1488+INSERT INTO t_innodb VALUES(0);
1489+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
1490+INSERT INTO t_myisam VALUES(0);
1491+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
1492+# connection con1
1493+LOCK TABLES FOR BACKUP;
1494+# connection con2
1495+INSERT INTO t_innodb VALUES(0);
1496+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
1497+INSERT INTO t_myisam VALUES(0);
1498+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
1499+# connection default
1500+SET GLOBAL read_only=0;
1501+# connection con2
1502+INSERT INTO t_innodb VALUES(0);
1503+INSERT INTO t_myisam VALUES(0);
1504+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1505+# connection default
1506+SET GLOBAL read_only=1;
1507+# connection con2
1508+INSERT INTO t_innodb VALUES(0);
1509+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
1510+INSERT INTO t_myisam VALUES(0);
1511+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
1512+# connection con1
1513+UNLOCK TABLES;
1514+# connection con2
1515+INSERT INTO t_innodb VALUES(0);
1516+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
1517+INSERT INTO t_myisam VALUES(0);
1518+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
1519+# connection default
1520+SET GLOBAL read_only=0;
1521+# connection default
1522+LOCK TABLES FOR BACKUP;
1523+# connection con1
1524+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
1525+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
1526+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
1527+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
1528+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
1529+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
1530+INSERT INTO tt_innodb VALUES(5);
1531+INSERT INTO tt_myisam VALUES(5);
1532+INSERT INTO tt_memory VALUES(5);
1533+INSERT INTO tt_csv VALUES(5);
1534+INSERT INTO tt_blackhole VALUES(5);
1535+INSERT INTO tt_archive VALUES(5);
1536+# connection default
1537+LOCK BINLOG FOR BACKUP;
1538+# connection con1
1539+START TRANSACTION;
1540+SELECT * FROM tt_archive;
1541+a
1542+5
1543+SELECT * FROM tt_blackhole;
1544+a
1545+SELECT * FROM tt_memory;
1546+a
1547+5
1548+SELECT * FROM tt_innodb;
1549+a
1550+5
1551+SELECT * FROM tt_myisam;
1552+a
1553+5
1554+INSERT INTO tt_innodb VALUES(6);
1555+INSERT INTO tt_myisam VALUES(6);
1556+INSERT INTO tt_memory VALUES(6);
1557+INSERT INTO tt_csv VALUES(6);
1558+INSERT INTO tt_blackhole VALUES(6);
1559+INSERT INTO tt_archive VALUES(6);
1560+COMMIT;
1561+SELECT * FROM tt_archive;
1562+a
1563+5
1564+6
1565+SELECT * FROM tt_blackhole;
1566+a
1567+SELECT * FROM tt_memory;
1568+a
1569+5
1570+6
1571+SELECT * FROM tt_innodb;
1572+a
1573+5
1574+6
1575+SELECT * FROM tt_myisam;
1576+a
1577+5
1578+6
1579+DROP TEMPORARY TABLE tt_innodb;
1580+DROP TEMPORARY TABLE tt_myisam;
1581+DROP TEMPORARY TABLE tt_memory;
1582+DROP TEMPORARY TABLE tt_csv;
1583+DROP TEMPORARY TABLE tt_blackhole;
1584+DROP TEMPORARY TABLE tt_archive;
1585+# connection default
1586+UNLOCK BINLOG;
1587+UNLOCK TABLES;
1588+LOCK BINLOG FOR BACKUP;
1589+# connection con1
1590+SELECT @@log_bin;
1591+@@log_bin
1592+0
1593+INSERT INTO t_innodb VALUES(1);
1594+INSERT INTO t_myisam VALUES(1);
1595+# connection default
1596+UNLOCK BINLOG;
1597+# connection default
1598+LOCK TABLES FOR BACKUP;
1599+# connection con1
1600+DELETE FROM t_innodb;
1601+INSERT INTO t_innodb VALUES(0);
1602+UPDATE t_innodb SET a = 1;
1603+REPLACE INTO t_innodb VALUES(1);
1604+SELECT * from t_innodb;
1605+a
1606+1
1607+1
1608+HANDLER t_innodb OPEN;
1609+HANDLER t_innodb READ a FIRST;
1610+a
1611+1
1612+HANDLER t_innodb CLOSE;
1613+INSERT INTO t_myisam VALUES(0);
1614+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1615+INSERT DELAYED INTO t_myisam VALUES(0);
1616+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1617+SELECT * FROM t_myisam;
1618+a
1619+0
1620+0
1621+1
1622+1
1623+1
1624+HANDLER t_myisam OPEN;
1625+HANDLER t_myisam READ a FIRST;
1626+a
1627+0
1628+HANDLER t_myisam CLOSE;
1629+INSERT INTO t_memory VALUES(0);
1630+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1631+SELECT * FROM t_memory;
1632+a
1633+1
1634+INSERT INTO t_archive VALUES(0);
1635+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1636+SELECT * FROM t_archive;
1637+a
1638+0
1639+1
1640+INSERT INTO t_csv VALUES(0);
1641+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1642+SELECT * FROM t_csv;
1643+a
1644+1
1645+DELETE FROM t_blackhole;
1646+INSERT INTO t_blackhole VALUES(0);
1647+UPDATE t_blackhole SET a = 1;
1648+REPLACE INTO t_blackhole VALUES(1);
1649+SELECT * FROM t_blackhole;
1650+a
1651+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
1652+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1653+DROP TABLE t_innodb;
1654+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1655+DROP TABLE non_existing;
1656+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1657+TRUNCATE TABLE t_innodb;
1658+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1659+RENAME TABLE t_innodb TO tmp;
1660+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1661+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
1662+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1663+ALTER TABLE t_innodb ADD KEY (a);
1664+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1665+DROP DATABASE test;
1666+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1667+CREATE DATABASE test1;
1668+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1669+CREATE PROCEDURE p1() SELECT 1;
1670+# connection default
1671+KILL QUERY #;
1672+# connection con1
1673+ERROR 70100: Query execution was interrupted
1674+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 1;
1675+# connection default
1676+KILL QUERY #;
1677+# connection con1
1678+ERROR 70100: Query execution was interrupted
1679+CREATE VIEW v1 AS SELECT * FROM t_innodb;
1680+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1681+INSERT INTO v_innodb VALUES(1);
1682+INSERT INTO v_blackhole VALUES(1);
1683+INSERT INTO v_myisam VALUES(1);
1684+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1685+# connection default
1686+UNLOCK TABLES;
1687+LOCK BINLOG FOR BACKUP;
1688+# connection con1
1689+DELETE FROM t_innodb;
1690+INSERT INTO t_innodb VALUES(0);
1691+UPDATE t_innodb SET a = 1;
1692+REPLACE INTO t_innodb VALUES(1);
1693+SELECT * from t_innodb;
1694+a
1695+1
1696+1
1697+HANDLER t_innodb OPEN;
1698+HANDLER t_innodb READ a FIRST;
1699+a
1700+1
1701+HANDLER t_innodb CLOSE;
1702+DELETE FROM t_myisam;
1703+INSERT INTO t_myisam VALUES(0);
1704+UPDATE t_myisam SET a = 1;
1705+REPLACE INTO t_myisam VALUES(1);
1706+SELECT * from t_myisam;
1707+a
1708+1
1709+1
1710+HANDLER t_myisam OPEN;
1711+HANDLER t_myisam READ a FIRST;
1712+a
1713+1
1714+HANDLER t_myisam CLOSE;
1715+DELETE FROM t_memory;
1716+INSERT INTO t_memory VALUES(0);
1717+UPDATE t_memory SET a = 1;
1718+REPLACE INTO t_memory VALUES(1);
1719+SELECT * from t_memory;
1720+a
1721+1
1722+1
1723+INSERT INTO t_archive VALUES(0);
1724+SELECT * from t_archive;
1725+a
1726+0
1727+1
1728+0
1729+DELETE FROM t_csv;
1730+INSERT INTO t_csv VALUES(0);
1731+UPDATE t_csv SET a = 1;
1732+REPLACE INTO t_csv VALUES(1);
1733+SELECT * from t_csv;
1734+a
1735+1
1736+1
1737+DELETE FROM t_blackhole;
1738+INSERT INTO t_blackhole VALUES(0);
1739+UPDATE t_blackhole SET a = 1;
1740+REPLACE INTO t_blackhole VALUES(1);
1741+SELECT * from t_blackhole;
1742+a
1743+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
1744+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
1745+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
1746+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
1747+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
1748+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
1749+DROP TABLE tt_innodb;
1750+DROP TABLE tt_myisam;
1751+DROP TABLE tt_memory;
1752+DROP TABLE tt_csv;
1753+DROP TABLE tt_blackhole;
1754+DROP TABLE tt_archive;
1755+DROP TABLE non_existing;
1756+ERROR 42S02: Unknown table 'test.non_existing'
1757+TRUNCATE TABLE t_innodb;
1758+TRUNCATE TABLE t_myisam;
1759+TRUNCATE TABLE t_memory;
1760+TRUNCATE TABLE t_csv;
1761+TRUNCATE TABLE t_blackhole;
1762+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
1763+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
1764+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
1765+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
1766+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
1767+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
1768+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
1769+ALTER TABLE t_innodb DROP COLUMN b;
1770+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
1771+ALTER TABLE t_myisam DROP COLUMN b;
1772+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
1773+ALTER TABLE t_memory DROP COLUMN b;
1774+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
1775+ALTER TABLE t_csv DROP COLUMN b;
1776+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
1777+ALTER TABLE t_blackhole DROP COLUMN b;
1778+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
1779+ALTER TABLE t_archive DROP COLUMN b;
1780+ALTER TABLE t_innodb ADD KEY tmp (a);
1781+Warnings:
1782+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
1783+ALTER TABLE t_innodb DROP KEY tmp;
1784+ALTER TABLE t_myisam ADD KEY tmp (a);
1785+Warnings:
1786+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
1787+ALTER TABLE t_myisam DROP KEY tmp;
1788+ALTER TABLE t_memory ADD KEY tmp (a);
1789+Warnings:
1790+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
1791+ALTER TABLE t_memory DROP KEY tmp;
1792+ALTER TABLE t_blackhole ADD KEY tmp (a);
1793+Warnings:
1794+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
1795+ALTER TABLE t_blackhole DROP KEY tmp;
1796+CREATE DATABASE test1;
1797+DROP DATABASE test1;
1798+CREATE PROCEDURE p1()
1799+BEGIN
1800+SELECT 1;
1801+END|
1802+DROP PROCEDURE p1|
1803+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
1804+BEGIN
1805+RETURN 1;
1806+END|
1807+DROP FUNCTION f1|
1808+CREATE VIEW v1 AS SELECT * FROM t_innodb;
1809+DROP VIEW v1;
1810+INSERT INTO v_innodb VALUES(1);
1811+INSERT INTO v_blackhole VALUES(1);
1812+INSERT INTO v_myisam VALUES(1);
1813+INSERT INTO v_csv VALUES(1);
1814+INSERT INTO v_memory VALUES(1);
1815+INSERT INTO v_archive VALUES(1);
1816+# connection default
1817+UNLOCK BINLOG;
1818+SET @old_general_log = @@general_log;
1819+SET @old_slow_query_log = @@slow_query_log;
1820+SET @old_log_output = @@log_output;
1821+SET GLOBAL log_output = 'TABLE';
1822+SET GLOBAL general_log = ON;
1823+SET GLOBAL slow_query_log = ON;
1824+LOCK TABLES FOR BACKUP;
1825+# connection con1
1826+SET @old_long_query_time = @@SESSION.long_query_time;
1827+SET SESSION long_query_time = 0;
1828+SELECT 1;
1829+1
1830+1
1831+# connection default
1832+LOCK BINLOG FOR BACKUP;
1833+SELECT 1;
1834+1
1835+1
1836+# connection con1
1837+SET SESSION long_query_time = @old_long_query_time;
1838+# connection default
1839+UNLOCK BINLOG;
1840+UNLOCK TABLES;
1841+SET GLOBAL log_output = @old_log_output;
1842+SET GLOBAL slow_query_log = @old_slow_query_log;
1843+SET GLOBAL general_log = @old_general_log;
1844+#-----------------------------------------------------------------------
1845+# Cleanup
1846+#-----------------------------------------------------------------------
1847+SET GLOBAL lock_wait_timeout=default;
1848+DROP USER user@localhost;
1849+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
1850+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;
1851
1852=== added file 'mysql-test/r/backup_locks_binlog_mixed.result'
1853--- mysql-test/r/backup_locks_binlog_mixed.result 1970-01-01 00:00:00 +0000
1854+++ mysql-test/r/backup_locks_binlog_mixed.result 2014-02-20 11:26:00 +0000
1855@@ -0,0 +1,573 @@
1856+SET @old_binlog_format=@@binlog_format;
1857+SET GLOBAL binlog_format='MIXED';
1858+SET SESSION binlog_format='MIXED';
1859+SELECT @@binlog_format;
1860+@@binlog_format
1861+MIXED
1862+RESET MASTER;
1863+SHOW VARIABLES LIKE 'have_backup_locks';
1864+Variable_name Value
1865+have_backup_locks YES
1866+#-----------------------------------------------------------------------
1867+# Setup
1868+#-----------------------------------------------------------------------
1869+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
1870+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
1871+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
1872+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
1873+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
1874+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
1875+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
1876+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
1877+CREATE VIEW v_memory AS SELECT * FROM t_memory;
1878+CREATE VIEW v_csv AS SELECT * FROM t_csv;
1879+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
1880+CREATE VIEW v_archive AS SELECT * FROM t_archive;
1881+#-----------------------------------------------------------------------
1882+# Single-threaded tests
1883+#-----------------------------------------------------------------------
1884+LOCK BINLOG FOR BACKUP;
1885+DELETE FROM t_innodb;
1886+INSERT INTO t_innodb VALUES(0);
1887+UPDATE t_innodb SET a = 1;
1888+REPLACE INTO t_innodb VALUES(1);
1889+SELECT * from t_innodb;
1890+a
1891+1
1892+1
1893+HANDLER t_innodb OPEN;
1894+HANDLER t_innodb READ a FIRST;
1895+a
1896+1
1897+HANDLER t_innodb CLOSE;
1898+DELETE FROM t_myisam;
1899+INSERT INTO t_myisam VALUES(0);
1900+UPDATE t_myisam SET a = 1;
1901+REPLACE INTO t_myisam VALUES(1);
1902+SELECT * from t_myisam;
1903+a
1904+1
1905+1
1906+HANDLER t_myisam OPEN;
1907+HANDLER t_myisam READ a FIRST;
1908+a
1909+1
1910+HANDLER t_myisam CLOSE;
1911+DELETE FROM t_memory;
1912+INSERT INTO t_memory VALUES(0);
1913+UPDATE t_memory SET a = 1;
1914+REPLACE INTO t_memory VALUES(1);
1915+SELECT * from t_memory;
1916+a
1917+1
1918+1
1919+DELETE FROM t_archive;
1920+INSERT INTO t_archive VALUES(0);
1921+SELECT * from t_archive;
1922+a
1923+0
1924+DELETE FROM t_csv;
1925+INSERT INTO t_csv VALUES(0);
1926+UPDATE t_csv SET a = 1;
1927+REPLACE INTO t_csv VALUES(1);
1928+SELECT * from t_csv;
1929+a
1930+1
1931+1
1932+DELETE FROM t_blackhole;
1933+INSERT INTO t_blackhole VALUES(0);
1934+UPDATE t_blackhole SET a = 1;
1935+REPLACE INTO t_blackhole VALUES(1);
1936+SELECT * from t_blackhole;
1937+a
1938+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
1939+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
1940+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
1941+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
1942+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
1943+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
1944+DROP TABLE tt_innodb;
1945+DROP TABLE tt_myisam;
1946+DROP TABLE tt_memory;
1947+DROP TABLE tt_csv;
1948+DROP TABLE tt_blackhole;
1949+DROP TABLE tt_archive;
1950+DROP TABLE non_existing;
1951+ERROR 42S02: Unknown table 'test.non_existing'
1952+TRUNCATE TABLE t_innodb;
1953+TRUNCATE TABLE t_myisam;
1954+TRUNCATE TABLE t_memory;
1955+TRUNCATE TABLE t_csv;
1956+TRUNCATE TABLE t_blackhole;
1957+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
1958+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
1959+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
1960+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
1961+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
1962+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
1963+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
1964+ALTER TABLE t_innodb DROP COLUMN b;
1965+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
1966+ALTER TABLE t_myisam DROP COLUMN b;
1967+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
1968+ALTER TABLE t_memory DROP COLUMN b;
1969+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
1970+ALTER TABLE t_csv DROP COLUMN b;
1971+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
1972+ALTER TABLE t_blackhole DROP COLUMN b;
1973+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
1974+ALTER TABLE t_archive DROP COLUMN b;
1975+ALTER TABLE t_innodb ADD KEY tmp (a);
1976+Warnings:
1977+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
1978+ALTER TABLE t_innodb DROP KEY tmp;
1979+ALTER TABLE t_myisam ADD KEY tmp (a);
1980+Warnings:
1981+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
1982+ALTER TABLE t_myisam DROP KEY tmp;
1983+ALTER TABLE t_memory ADD KEY tmp (a);
1984+Warnings:
1985+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
1986+ALTER TABLE t_memory DROP KEY tmp;
1987+ALTER TABLE t_blackhole ADD KEY tmp (a);
1988+Warnings:
1989+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
1990+ALTER TABLE t_blackhole DROP KEY tmp;
1991+CREATE DATABASE test1;
1992+DROP DATABASE test1;
1993+CREATE PROCEDURE p1()
1994+BEGIN
1995+SELECT 1;
1996+END|
1997+DROP PROCEDURE p1|
1998+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
1999+BEGIN
2000+RETURN 1;
2001+END|
2002+DROP FUNCTION f1|
2003+CREATE VIEW v1 AS SELECT * FROM t_innodb;
2004+DROP VIEW v1;
2005+INSERT INTO v_innodb VALUES(1);
2006+INSERT INTO v_blackhole VALUES(1);
2007+INSERT INTO v_myisam VALUES(1);
2008+INSERT INTO v_csv VALUES(1);
2009+INSERT INTO v_memory VALUES(1);
2010+INSERT INTO v_archive VALUES(1);
2011+UNLOCK BINLOG;
2012+SELECT @@delay_key_write;
2013+@@delay_key_write
2014+ON
2015+SET GLOBAL delay_key_write=ALL;
2016+LOCK TABLES FOR BACKUP;
2017+ERROR HY000: The MySQL server is running with the delay_key_write=ALL option so it cannot execute this statement
2018+SET GLOBAL delay_key_write=default;
2019+SELECT @@delay_key_write;
2020+@@delay_key_write
2021+ON
2022+SET GLOBAL read_only=1;
2023+INSERT INTO t_innodb VALUES(0);
2024+INSERT INTO t_myisam VALUES(0);
2025+LOCK TABLES FOR BACKUP;
2026+INSERT INTO t_innodb VALUES(0);
2027+INSERT INTO t_myisam VALUES(0);
2028+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
2029+UNLOCK TABLES;
2030+INSERT INTO t_innodb VALUES(0);
2031+INSERT INTO t_myisam VALUES(0);
2032+SET GLOBAL read_only=0;
2033+LOCK TABLES FOR BACKUP;
2034+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
2035+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
2036+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
2037+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
2038+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
2039+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
2040+INSERT INTO tt_innodb VALUES(5);
2041+INSERT INTO tt_myisam VALUES(5);
2042+INSERT INTO tt_memory VALUES(5);
2043+INSERT INTO tt_csv VALUES(5);
2044+INSERT INTO tt_blackhole VALUES(5);
2045+INSERT INTO tt_archive VALUES(5);
2046+LOCK BINLOG FOR BACKUP;
2047+START TRANSACTION;
2048+SELECT * FROM tt_archive;
2049+a
2050+5
2051+SELECT * FROM tt_blackhole;
2052+a
2053+SELECT * FROM tt_memory;
2054+a
2055+5
2056+SELECT * FROM tt_innodb;
2057+a
2058+5
2059+SELECT * FROM tt_myisam;
2060+a
2061+5
2062+INSERT INTO tt_innodb VALUES(6);
2063+INSERT INTO tt_myisam VALUES(6);
2064+INSERT INTO tt_memory VALUES(6);
2065+INSERT INTO tt_csv VALUES(6);
2066+INSERT INTO tt_blackhole VALUES(6);
2067+INSERT INTO tt_archive VALUES(6);
2068+COMMIT;
2069+SELECT * FROM tt_archive;
2070+a
2071+5
2072+6
2073+SELECT * FROM tt_blackhole;
2074+a
2075+SELECT * FROM tt_memory;
2076+a
2077+5
2078+6
2079+SELECT * FROM tt_innodb;
2080+a
2081+5
2082+6
2083+SELECT * FROM tt_myisam;
2084+a
2085+5
2086+6
2087+DROP TEMPORARY TABLE tt_innodb;
2088+DROP TEMPORARY TABLE tt_myisam;
2089+DROP TEMPORARY TABLE tt_memory;
2090+DROP TEMPORARY TABLE tt_csv;
2091+DROP TEMPORARY TABLE tt_blackhole;
2092+DROP TEMPORARY TABLE tt_archive;
2093+UNLOCK BINLOG;
2094+UNLOCK TABLES;
2095+SELECT @@log_bin;
2096+@@log_bin
2097+1
2098+LOCK BINLOG FOR BACKUP;
2099+INSERT INTO t_innodb VALUES(1);
2100+INSERT INTO t_myisam VALUES(1);
2101+UNLOCK BINLOG;
2102+SET @old_general_log = @@general_log;
2103+SET @old_slow_query_log = @@slow_query_log;
2104+SET @old_log_output = @@log_output;
2105+SET @old_long_query_time = @@SESSION.long_query_time;
2106+SET SESSION long_query_time = 0;
2107+SET GLOBAL log_output = 'TABLE';
2108+SET GLOBAL general_log = ON;
2109+SET GLOBAL slow_query_log = ON;
2110+LOCK TABLES FOR BACKUP;
2111+SELECT 1;
2112+1
2113+1
2114+LOCK BINLOG FOR BACKUP;
2115+SELECT 1;
2116+1
2117+1
2118+UNLOCK BINLOG;
2119+UNLOCK TABLES;
2120+SET SESSION long_query_time = @old_long_query_time;
2121+SET GLOBAL log_output = @old_log_output;
2122+SET GLOBAL slow_query_log = @old_slow_query_log;
2123+SET GLOBAL general_log = @old_general_log;
2124+#-----------------------------------------------------------------------
2125+# Multi-threaded tests
2126+#-----------------------------------------------------------------------
2127+# connection default
2128+CREATE USER user@localhost;
2129+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
2130+LOCK TABLES FOR BACKUP;
2131+# connection con1
2132+SET SESSION lock_wait_timeout = 1;
2133+SET GLOBAL delay_key_write=ALL;
2134+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2135+SET SESSION lock_wait_timeout = DEFAULT;
2136+# connection default
2137+UNLOCK TABLES;
2138+# connection con2
2139+START TRANSACTION;
2140+INSERT INTO t_innodb VALUES(0);
2141+# connection default
2142+SET GLOBAL read_only=1;
2143+# connection con2
2144+COMMIT;
2145+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2146+INSERT INTO t_innodb VALUES(0);
2147+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2148+INSERT INTO t_myisam VALUES(0);
2149+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2150+# connection con1
2151+LOCK TABLES FOR BACKUP;
2152+# connection con2
2153+INSERT INTO t_innodb VALUES(0);
2154+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2155+INSERT INTO t_myisam VALUES(0);
2156+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2157+# connection default
2158+SET GLOBAL read_only=0;
2159+# connection con2
2160+SET SESSION lock_wait_timeout = 1;
2161+INSERT INTO t_innodb VALUES(0);
2162+INSERT INTO t_myisam VALUES(0);
2163+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2164+SET SESSION lock_wait_timeout = 0;
2165+Warnings:
2166+Warning 1292 Truncated incorrect lock_wait_timeout value: '0'
2167+# connection default
2168+SET GLOBAL read_only=1;
2169+# connection con2
2170+INSERT INTO t_innodb VALUES(0);
2171+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2172+INSERT INTO t_myisam VALUES(0);
2173+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2174+# connection con1
2175+UNLOCK TABLES;
2176+# connection con2
2177+INSERT INTO t_innodb VALUES(0);
2178+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2179+INSERT INTO t_myisam VALUES(0);
2180+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2181+# connection default
2182+SET GLOBAL read_only=0;
2183+# connection default
2184+LOCK TABLES FOR BACKUP;
2185+# connection con1
2186+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
2187+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
2188+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
2189+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
2190+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
2191+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
2192+INSERT INTO tt_innodb VALUES(5);
2193+INSERT INTO tt_myisam VALUES(5);
2194+INSERT INTO tt_memory VALUES(5);
2195+INSERT INTO tt_csv VALUES(5);
2196+INSERT INTO tt_blackhole VALUES(5);
2197+INSERT INTO tt_archive VALUES(5);
2198+# connection default
2199+LOCK BINLOG FOR BACKUP;
2200+# connection con1
2201+SET SESSION lock_wait_timeout = 1;
2202+START TRANSACTION;
2203+INSERT INTO tt_myisam VALUES(6);
2204+INSERT INTO tt_innodb VALUES(6);
2205+SELECT * FROM tt_archive;
2206+a
2207+5
2208+SELECT * FROM tt_blackhole;
2209+a
2210+SELECT * FROM tt_memory;
2211+a
2212+5
2213+SELECT * FROM tt_innodb;
2214+a
2215+5
2216+6
2217+SELECT * FROM tt_myisam;
2218+a
2219+5
2220+6
2221+SELECT * FROM tt_csv;
2222+a
2223+5
2224+# connection default
2225+must_be_1
2226+1
2227+UNLOCK BINLOG;
2228+UNLOCK TABLES;
2229+# connection con1
2230+COMMIT;
2231+DROP TEMPORARY TABLE tt_innodb;
2232+DROP TEMPORARY TABLE tt_myisam;
2233+DROP TEMPORARY TABLE tt_memory;
2234+DROP TEMPORARY TABLE tt_csv;
2235+DROP TEMPORARY TABLE tt_blackhole;
2236+DROP TEMPORARY TABLE tt_archive;
2237+# connection default
2238+LOCK BINLOG FOR BACKUP;
2239+# connection con1
2240+SET SESSION lock_wait_timeout = 1;
2241+INSERT INTO t_innodb VALUES(1);
2242+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2243+INSERT INTO t_myisam VALUES(1);
2244+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2245+SET SESSION lock_wait_timeout = DEFAULT;
2246+# connection default
2247+must_be_1
2248+1
2249+UNLOCK BINLOG;
2250+# connection default
2251+LOCK BINLOG FOR BACKUP;
2252+# connection con1
2253+SET SESSION lock_wait_timeout = 1;
2254+DELETE FROM t_innodb;
2255+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2256+INSERT INTO t_innodb VALUES(0);
2257+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2258+UPDATE t_innodb SET a = 1;
2259+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2260+REPLACE INTO t_innodb VALUES(1);
2261+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2262+SELECT * from t_innodb;
2263+a
2264+0
2265+0
2266+0
2267+0
2268+1
2269+1
2270+HANDLER t_innodb OPEN;
2271+HANDLER t_innodb READ a FIRST;
2272+a
2273+0
2274+HANDLER t_innodb CLOSE;
2275+START TRANSACTION;
2276+SELECT * from t_innodb;
2277+a
2278+0
2279+0
2280+0
2281+0
2282+1
2283+1
2284+DELETE FROM t_innodb;
2285+INSERT INTO t_innodb VALUES(0);
2286+UPDATE t_innodb SET a = 1;
2287+REPLACE INTO t_innodb VALUES(1);
2288+COMMIT;
2289+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2290+SELECT * FROM t_innodb;
2291+a
2292+0
2293+0
2294+0
2295+0
2296+1
2297+1
2298+COMMIT;
2299+START TRANSACTION;
2300+INSERT INTO t_myisam VALUES(1);
2301+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2302+COMMIT;
2303+INSERT INTO t_myisam VALUES(0);
2304+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2305+UPDATE t_myisam SET a = 1;
2306+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2307+SELECT * FROM t_myisam;
2308+a
2309+1
2310+1
2311+1
2312+1
2313+1
2314+1
2315+1
2316+HANDLER t_myisam OPEN;
2317+HANDLER t_myisam READ a FIRST;
2318+a
2319+1
2320+HANDLER t_myisam CLOSE;
2321+DELETE FROM t_blackhole;
2322+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2323+INSERT INTO t_blackhole VALUES(0);
2324+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2325+UPDATE t_blackhole SET a = 1;
2326+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2327+REPLACE INTO t_blackhole VALUES(1);
2328+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2329+SELECT * FROM t_blackhole;
2330+a
2331+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
2332+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2333+DROP TABLE tt_innodb;
2334+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2335+DROP TABLE non_existing;
2336+ERROR 42S02: Unknown table 'test.non_existing'
2337+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
2338+ERROR 42S01: Table 't_innodb' already exists
2339+TRUNCATE TABLE t_innodb;
2340+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2341+RENAME TABLE t_innodb TO tmp, tmp to t_innodb;
2342+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2343+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
2344+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2345+ALTER TABLE t_innodb ADD KEY (a);
2346+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2347+CREATE DATABASE test1;
2348+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2349+DROP DATABASE test1;
2350+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2351+CREATE PROCEDURE p1() SELECT 1;
2352+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2353+DROP PROCEDURE p1;
2354+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2355+CREATE VIEW v1 AS SELECT * FROM t_innodb;
2356+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2357+DROP VIEW v1;
2358+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2359+INSERT INTO v_innodb VALUES(1);
2360+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2361+# connection default
2362+must_be_1
2363+1
2364+UNLOCK BINLOG;
2365+# connection con1
2366+SHOW CREATE TABLE t_innodb;
2367+Table Create Table
2368+t_innodb CREATE TABLE `t_innodb` (
2369+ `a` int(11) NOT NULL,
2370+ `b` char(10) DEFAULT NULL,
2371+ KEY `a` (`a`),
2372+ KEY `a_2` (`a`)
2373+) ENGINE=InnoDB DEFAULT CHARSET=latin1
2374+DROP TABLE t_innodb;
2375+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
2376+# connection default
2377+LOCK BINLOG FOR BACKUP;
2378+# connection con1
2379+INSERT DELAYED INTO t_myisam VALUES(0);
2380+Warnings:
2381+Warning 1287 'INSERT DELAYED' is deprecated and will be removed in a future release. Please use INSERT instead
2382+# connection default
2383+must_be_1
2384+1
2385+UNLOCK BINLOG;
2386+SELECT * FROm t_myisam;
2387+a
2388+0
2389+1
2390+1
2391+1
2392+1
2393+1
2394+1
2395+1
2396+SET @old_general_log = @@general_log;
2397+SET @old_slow_query_log = @@slow_query_log;
2398+SET @old_log_output = @@log_output;
2399+SET GLOBAL log_output = 'TABLE';
2400+SET GLOBAL general_log = ON;
2401+SET GLOBAL slow_query_log = ON;
2402+LOCK BINLOG FOR BACKUP;
2403+# connection con1
2404+SET @old_long_query_time = @@SESSION.long_query_time;
2405+SET SESSION long_query_time = 0;
2406+SELECT 1;
2407+1
2408+1
2409+# connection default
2410+SELECT 1;
2411+1
2412+1
2413+# connection con1
2414+SET SESSION long_query_time = @old_long_query_time;
2415+# connection default
2416+must_be_1
2417+1
2418+UNLOCK BINLOG;
2419+SET GLOBAL log_output = @old_log_output;
2420+SET GLOBAL slow_query_log = @old_slow_query_log;
2421+SET GLOBAL general_log = @old_general_log;
2422+#-----------------------------------------------------------------------
2423+# Cleanup
2424+#-----------------------------------------------------------------------
2425+DROP USER user@localhost;
2426+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
2427+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;
2428+SET GLOBAL binlog_format = @old_binlog_format;
2429
2430=== added file 'mysql-test/r/backup_locks_binlog_row.result'
2431--- mysql-test/r/backup_locks_binlog_row.result 1970-01-01 00:00:00 +0000
2432+++ mysql-test/r/backup_locks_binlog_row.result 2014-02-20 11:26:00 +0000
2433@@ -0,0 +1,579 @@
2434+SET @old_binlog_format=@@binlog_format;
2435+SET GLOBAL binlog_format='ROW';
2436+SET SESSION binlog_format='ROW';
2437+SELECT @@binlog_format;
2438+@@binlog_format
2439+ROW
2440+RESET MASTER;
2441+SHOW VARIABLES LIKE 'have_backup_locks';
2442+Variable_name Value
2443+have_backup_locks YES
2444+#-----------------------------------------------------------------------
2445+# Setup
2446+#-----------------------------------------------------------------------
2447+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
2448+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
2449+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
2450+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
2451+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
2452+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
2453+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
2454+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
2455+CREATE VIEW v_memory AS SELECT * FROM t_memory;
2456+CREATE VIEW v_csv AS SELECT * FROM t_csv;
2457+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
2458+CREATE VIEW v_archive AS SELECT * FROM t_archive;
2459+#-----------------------------------------------------------------------
2460+# Single-threaded tests
2461+#-----------------------------------------------------------------------
2462+LOCK BINLOG FOR BACKUP;
2463+DELETE FROM t_innodb;
2464+INSERT INTO t_innodb VALUES(0);
2465+UPDATE t_innodb SET a = 1;
2466+REPLACE INTO t_innodb VALUES(1);
2467+SELECT * from t_innodb;
2468+a
2469+1
2470+1
2471+HANDLER t_innodb OPEN;
2472+HANDLER t_innodb READ a FIRST;
2473+a
2474+1
2475+HANDLER t_innodb CLOSE;
2476+DELETE FROM t_myisam;
2477+INSERT INTO t_myisam VALUES(0);
2478+UPDATE t_myisam SET a = 1;
2479+REPLACE INTO t_myisam VALUES(1);
2480+SELECT * from t_myisam;
2481+a
2482+1
2483+1
2484+HANDLER t_myisam OPEN;
2485+HANDLER t_myisam READ a FIRST;
2486+a
2487+1
2488+HANDLER t_myisam CLOSE;
2489+DELETE FROM t_memory;
2490+INSERT INTO t_memory VALUES(0);
2491+UPDATE t_memory SET a = 1;
2492+REPLACE INTO t_memory VALUES(1);
2493+SELECT * from t_memory;
2494+a
2495+1
2496+1
2497+DELETE FROM t_archive;
2498+INSERT INTO t_archive VALUES(0);
2499+SELECT * from t_archive;
2500+a
2501+0
2502+DELETE FROM t_csv;
2503+INSERT INTO t_csv VALUES(0);
2504+UPDATE t_csv SET a = 1;
2505+REPLACE INTO t_csv VALUES(1);
2506+SELECT * from t_csv;
2507+a
2508+1
2509+1
2510+DELETE FROM t_blackhole;
2511+Warnings:
2512+Warning 1870 Row events are not logged for DELETE statements that modify BLACKHOLE tables in row format. Table(s): 't_blackhole.'
2513+INSERT INTO t_blackhole VALUES(0);
2514+UPDATE t_blackhole SET a = 1;
2515+Warnings:
2516+Warning 1870 Row events are not logged for UPDATE statements that modify BLACKHOLE tables in row format. Table(s): 't_blackhole.'
2517+REPLACE INTO t_blackhole VALUES(1);
2518+SELECT * from t_blackhole;
2519+a
2520+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
2521+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
2522+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
2523+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
2524+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
2525+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
2526+DROP TABLE tt_innodb;
2527+DROP TABLE tt_myisam;
2528+DROP TABLE tt_memory;
2529+DROP TABLE tt_csv;
2530+DROP TABLE tt_blackhole;
2531+DROP TABLE tt_archive;
2532+DROP TABLE non_existing;
2533+ERROR 42S02: Unknown table 'test.non_existing'
2534+TRUNCATE TABLE t_innodb;
2535+TRUNCATE TABLE t_myisam;
2536+TRUNCATE TABLE t_memory;
2537+TRUNCATE TABLE t_csv;
2538+TRUNCATE TABLE t_blackhole;
2539+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
2540+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
2541+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
2542+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
2543+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
2544+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
2545+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
2546+ALTER TABLE t_innodb DROP COLUMN b;
2547+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
2548+ALTER TABLE t_myisam DROP COLUMN b;
2549+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
2550+ALTER TABLE t_memory DROP COLUMN b;
2551+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
2552+ALTER TABLE t_csv DROP COLUMN b;
2553+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
2554+ALTER TABLE t_blackhole DROP COLUMN b;
2555+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
2556+ALTER TABLE t_archive DROP COLUMN b;
2557+ALTER TABLE t_innodb ADD KEY tmp (a);
2558+Warnings:
2559+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
2560+ALTER TABLE t_innodb DROP KEY tmp;
2561+ALTER TABLE t_myisam ADD KEY tmp (a);
2562+Warnings:
2563+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
2564+ALTER TABLE t_myisam DROP KEY tmp;
2565+ALTER TABLE t_memory ADD KEY tmp (a);
2566+Warnings:
2567+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
2568+ALTER TABLE t_memory DROP KEY tmp;
2569+ALTER TABLE t_blackhole ADD KEY tmp (a);
2570+Warnings:
2571+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
2572+ALTER TABLE t_blackhole DROP KEY tmp;
2573+CREATE DATABASE test1;
2574+DROP DATABASE test1;
2575+CREATE PROCEDURE p1()
2576+BEGIN
2577+SELECT 1;
2578+END|
2579+DROP PROCEDURE p1|
2580+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
2581+BEGIN
2582+RETURN 1;
2583+END|
2584+DROP FUNCTION f1|
2585+CREATE VIEW v1 AS SELECT * FROM t_innodb;
2586+DROP VIEW v1;
2587+INSERT INTO v_innodb VALUES(1);
2588+INSERT INTO v_blackhole VALUES(1);
2589+INSERT INTO v_myisam VALUES(1);
2590+INSERT INTO v_csv VALUES(1);
2591+INSERT INTO v_memory VALUES(1);
2592+INSERT INTO v_archive VALUES(1);
2593+UNLOCK BINLOG;
2594+SELECT @@delay_key_write;
2595+@@delay_key_write
2596+ON
2597+SET GLOBAL delay_key_write=ALL;
2598+LOCK TABLES FOR BACKUP;
2599+ERROR HY000: The MySQL server is running with the delay_key_write=ALL option so it cannot execute this statement
2600+SET GLOBAL delay_key_write=default;
2601+SELECT @@delay_key_write;
2602+@@delay_key_write
2603+ON
2604+SET GLOBAL read_only=1;
2605+INSERT INTO t_innodb VALUES(0);
2606+INSERT INTO t_myisam VALUES(0);
2607+LOCK TABLES FOR BACKUP;
2608+INSERT INTO t_innodb VALUES(0);
2609+INSERT INTO t_myisam VALUES(0);
2610+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
2611+UNLOCK TABLES;
2612+INSERT INTO t_innodb VALUES(0);
2613+INSERT INTO t_myisam VALUES(0);
2614+SET GLOBAL read_only=0;
2615+LOCK TABLES FOR BACKUP;
2616+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
2617+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
2618+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
2619+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
2620+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
2621+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
2622+INSERT INTO tt_innodb VALUES(5);
2623+INSERT INTO tt_myisam VALUES(5);
2624+INSERT INTO tt_memory VALUES(5);
2625+INSERT INTO tt_csv VALUES(5);
2626+INSERT INTO tt_blackhole VALUES(5);
2627+INSERT INTO tt_archive VALUES(5);
2628+LOCK BINLOG FOR BACKUP;
2629+START TRANSACTION;
2630+SELECT * FROM tt_archive;
2631+a
2632+5
2633+SELECT * FROM tt_blackhole;
2634+a
2635+SELECT * FROM tt_memory;
2636+a
2637+5
2638+SELECT * FROM tt_innodb;
2639+a
2640+5
2641+SELECT * FROM tt_myisam;
2642+a
2643+5
2644+INSERT INTO tt_innodb VALUES(6);
2645+INSERT INTO tt_myisam VALUES(6);
2646+INSERT INTO tt_memory VALUES(6);
2647+INSERT INTO tt_csv VALUES(6);
2648+INSERT INTO tt_blackhole VALUES(6);
2649+INSERT INTO tt_archive VALUES(6);
2650+COMMIT;
2651+SELECT * FROM tt_archive;
2652+a
2653+5
2654+6
2655+SELECT * FROM tt_blackhole;
2656+a
2657+SELECT * FROM tt_memory;
2658+a
2659+5
2660+6
2661+SELECT * FROM tt_innodb;
2662+a
2663+5
2664+6
2665+SELECT * FROM tt_myisam;
2666+a
2667+5
2668+6
2669+DROP TEMPORARY TABLE tt_innodb;
2670+DROP TEMPORARY TABLE tt_myisam;
2671+DROP TEMPORARY TABLE tt_memory;
2672+DROP TEMPORARY TABLE tt_csv;
2673+DROP TEMPORARY TABLE tt_blackhole;
2674+DROP TEMPORARY TABLE tt_archive;
2675+UNLOCK BINLOG;
2676+UNLOCK TABLES;
2677+SELECT @@log_bin;
2678+@@log_bin
2679+1
2680+LOCK BINLOG FOR BACKUP;
2681+INSERT INTO t_innodb VALUES(1);
2682+INSERT INTO t_myisam VALUES(1);
2683+UNLOCK BINLOG;
2684+SET @old_general_log = @@general_log;
2685+SET @old_slow_query_log = @@slow_query_log;
2686+SET @old_log_output = @@log_output;
2687+SET @old_long_query_time = @@SESSION.long_query_time;
2688+SET SESSION long_query_time = 0;
2689+SET GLOBAL log_output = 'TABLE';
2690+SET GLOBAL general_log = ON;
2691+SET GLOBAL slow_query_log = ON;
2692+LOCK TABLES FOR BACKUP;
2693+SELECT 1;
2694+1
2695+1
2696+LOCK BINLOG FOR BACKUP;
2697+SELECT 1;
2698+1
2699+1
2700+UNLOCK BINLOG;
2701+UNLOCK TABLES;
2702+SET SESSION long_query_time = @old_long_query_time;
2703+SET GLOBAL log_output = @old_log_output;
2704+SET GLOBAL slow_query_log = @old_slow_query_log;
2705+SET GLOBAL general_log = @old_general_log;
2706+#-----------------------------------------------------------------------
2707+# Multi-threaded tests
2708+#-----------------------------------------------------------------------
2709+# connection default
2710+CREATE USER user@localhost;
2711+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
2712+LOCK TABLES FOR BACKUP;
2713+# connection con1
2714+SET SESSION lock_wait_timeout = 1;
2715+SET GLOBAL delay_key_write=ALL;
2716+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2717+SET SESSION lock_wait_timeout = DEFAULT;
2718+# connection default
2719+UNLOCK TABLES;
2720+# connection con2
2721+START TRANSACTION;
2722+INSERT INTO t_innodb VALUES(0);
2723+# connection default
2724+SET GLOBAL read_only=1;
2725+# connection con2
2726+COMMIT;
2727+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2728+INSERT INTO t_innodb VALUES(0);
2729+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2730+INSERT INTO t_myisam VALUES(0);
2731+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2732+# connection con1
2733+LOCK TABLES FOR BACKUP;
2734+# connection con2
2735+INSERT INTO t_innodb VALUES(0);
2736+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2737+INSERT INTO t_myisam VALUES(0);
2738+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2739+# connection default
2740+SET GLOBAL read_only=0;
2741+# connection con2
2742+SET SESSION lock_wait_timeout = 1;
2743+INSERT INTO t_innodb VALUES(0);
2744+INSERT INTO t_myisam VALUES(0);
2745+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2746+SET SESSION lock_wait_timeout = 0;
2747+Warnings:
2748+Warning 1292 Truncated incorrect lock_wait_timeout value: '0'
2749+# connection default
2750+SET GLOBAL read_only=1;
2751+# connection con2
2752+INSERT INTO t_innodb VALUES(0);
2753+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2754+INSERT INTO t_myisam VALUES(0);
2755+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2756+# connection con1
2757+UNLOCK TABLES;
2758+# connection con2
2759+INSERT INTO t_innodb VALUES(0);
2760+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2761+INSERT INTO t_myisam VALUES(0);
2762+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
2763+# connection default
2764+SET GLOBAL read_only=0;
2765+# connection default
2766+LOCK TABLES FOR BACKUP;
2767+# connection con1
2768+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
2769+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
2770+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
2771+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
2772+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
2773+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
2774+INSERT INTO tt_innodb VALUES(5);
2775+INSERT INTO tt_myisam VALUES(5);
2776+INSERT INTO tt_memory VALUES(5);
2777+INSERT INTO tt_csv VALUES(5);
2778+INSERT INTO tt_blackhole VALUES(5);
2779+INSERT INTO tt_archive VALUES(5);
2780+# connection default
2781+LOCK BINLOG FOR BACKUP;
2782+# connection con1
2783+SET SESSION lock_wait_timeout = 1;
2784+START TRANSACTION;
2785+INSERT INTO tt_myisam VALUES(6);
2786+INSERT INTO tt_innodb VALUES(6);
2787+SELECT * FROM tt_archive;
2788+a
2789+5
2790+SELECT * FROM tt_blackhole;
2791+a
2792+SELECT * FROM tt_memory;
2793+a
2794+5
2795+SELECT * FROM tt_innodb;
2796+a
2797+5
2798+6
2799+SELECT * FROM tt_myisam;
2800+a
2801+5
2802+6
2803+SELECT * FROM tt_csv;
2804+a
2805+5
2806+# connection default
2807+must_be_1
2808+1
2809+UNLOCK BINLOG;
2810+UNLOCK TABLES;
2811+# connection con1
2812+COMMIT;
2813+DROP TEMPORARY TABLE tt_innodb;
2814+DROP TEMPORARY TABLE tt_myisam;
2815+DROP TEMPORARY TABLE tt_memory;
2816+DROP TEMPORARY TABLE tt_csv;
2817+DROP TEMPORARY TABLE tt_blackhole;
2818+DROP TEMPORARY TABLE tt_archive;
2819+# connection default
2820+LOCK BINLOG FOR BACKUP;
2821+# connection con1
2822+SET SESSION lock_wait_timeout = 1;
2823+INSERT INTO t_innodb VALUES(1);
2824+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2825+INSERT INTO t_myisam VALUES(1);
2826+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2827+SET SESSION lock_wait_timeout = DEFAULT;
2828+# connection default
2829+must_be_1
2830+1
2831+UNLOCK BINLOG;
2832+# connection default
2833+LOCK BINLOG FOR BACKUP;
2834+# connection con1
2835+SET SESSION lock_wait_timeout = 1;
2836+DELETE FROM t_innodb;
2837+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2838+INSERT INTO t_innodb VALUES(0);
2839+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2840+UPDATE t_innodb SET a = 1;
2841+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2842+REPLACE INTO t_innodb VALUES(1);
2843+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2844+SELECT * from t_innodb;
2845+a
2846+0
2847+0
2848+0
2849+0
2850+1
2851+1
2852+HANDLER t_innodb OPEN;
2853+HANDLER t_innodb READ a FIRST;
2854+a
2855+0
2856+HANDLER t_innodb CLOSE;
2857+START TRANSACTION;
2858+SELECT * from t_innodb;
2859+a
2860+0
2861+0
2862+0
2863+0
2864+1
2865+1
2866+DELETE FROM t_innodb;
2867+INSERT INTO t_innodb VALUES(0);
2868+UPDATE t_innodb SET a = 1;
2869+REPLACE INTO t_innodb VALUES(1);
2870+COMMIT;
2871+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2872+SELECT * FROM t_innodb;
2873+a
2874+0
2875+0
2876+0
2877+0
2878+1
2879+1
2880+COMMIT;
2881+START TRANSACTION;
2882+INSERT INTO t_myisam VALUES(1);
2883+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2884+COMMIT;
2885+INSERT INTO t_myisam VALUES(0);
2886+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2887+UPDATE t_myisam SET a = 1;
2888+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2889+SELECT * FROM t_myisam;
2890+a
2891+1
2892+1
2893+1
2894+1
2895+1
2896+1
2897+1
2898+HANDLER t_myisam OPEN;
2899+HANDLER t_myisam READ a FIRST;
2900+a
2901+1
2902+HANDLER t_myisam CLOSE;
2903+DELETE FROM t_blackhole;
2904+Warnings:
2905+Warning 1870 Row events are not logged for DELETE statements that modify BLACKHOLE tables in row format. Table(s): 't_blackhole.'
2906+INSERT INTO t_blackhole VALUES(0);
2907+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2908+UPDATE t_blackhole SET a = 1;
2909+Warnings:
2910+Warning 1870 Row events are not logged for UPDATE statements that modify BLACKHOLE tables in row format. Table(s): 't_blackhole.'
2911+REPLACE INTO t_blackhole VALUES(1);
2912+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2913+SELECT * FROM t_blackhole;
2914+a
2915+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
2916+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2917+DROP TABLE tt_innodb;
2918+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2919+DROP TABLE non_existing;
2920+ERROR 42S02: Unknown table 'test.non_existing'
2921+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
2922+ERROR 42S01: Table 't_innodb' already exists
2923+TRUNCATE TABLE t_innodb;
2924+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2925+RENAME TABLE t_innodb TO tmp, tmp to t_innodb;
2926+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2927+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
2928+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2929+ALTER TABLE t_innodb ADD KEY (a);
2930+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2931+CREATE DATABASE test1;
2932+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2933+DROP DATABASE test1;
2934+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2935+CREATE PROCEDURE p1() SELECT 1;
2936+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2937+DROP PROCEDURE p1;
2938+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2939+CREATE VIEW v1 AS SELECT * FROM t_innodb;
2940+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2941+DROP VIEW v1;
2942+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2943+INSERT INTO v_innodb VALUES(1);
2944+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2945+# connection default
2946+must_be_1
2947+1
2948+UNLOCK BINLOG;
2949+# connection con1
2950+SHOW CREATE TABLE t_innodb;
2951+Table Create Table
2952+t_innodb CREATE TABLE `t_innodb` (
2953+ `a` int(11) NOT NULL,
2954+ `b` char(10) DEFAULT NULL,
2955+ KEY `a` (`a`),
2956+ KEY `a_2` (`a`)
2957+) ENGINE=InnoDB DEFAULT CHARSET=latin1
2958+DROP TABLE t_innodb;
2959+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
2960+# connection default
2961+LOCK BINLOG FOR BACKUP;
2962+# connection con1
2963+INSERT DELAYED INTO t_myisam VALUES(0);
2964+Warnings:
2965+Warning 1287 'INSERT DELAYED' is deprecated and will be removed in a future release. Please use INSERT instead
2966+# connection default
2967+must_be_1
2968+1
2969+UNLOCK BINLOG;
2970+SELECT * FROm t_myisam;
2971+a
2972+0
2973+1
2974+1
2975+1
2976+1
2977+1
2978+1
2979+1
2980+SET @old_general_log = @@general_log;
2981+SET @old_slow_query_log = @@slow_query_log;
2982+SET @old_log_output = @@log_output;
2983+SET GLOBAL log_output = 'TABLE';
2984+SET GLOBAL general_log = ON;
2985+SET GLOBAL slow_query_log = ON;
2986+LOCK BINLOG FOR BACKUP;
2987+# connection con1
2988+SET @old_long_query_time = @@SESSION.long_query_time;
2989+SET SESSION long_query_time = 0;
2990+SELECT 1;
2991+1
2992+1
2993+# connection default
2994+SELECT 1;
2995+1
2996+1
2997+# connection con1
2998+SET SESSION long_query_time = @old_long_query_time;
2999+# connection default
3000+must_be_1
3001+1
3002+UNLOCK BINLOG;
3003+SET GLOBAL log_output = @old_log_output;
3004+SET GLOBAL slow_query_log = @old_slow_query_log;
3005+SET GLOBAL general_log = @old_general_log;
3006+#-----------------------------------------------------------------------
3007+# Cleanup
3008+#-----------------------------------------------------------------------
3009+DROP USER user@localhost;
3010+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
3011+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;
3012+SET GLOBAL binlog_format = @old_binlog_format;
3013
3014=== added file 'mysql-test/r/backup_locks_binlog_stmt.result'
3015--- mysql-test/r/backup_locks_binlog_stmt.result 1970-01-01 00:00:00 +0000
3016+++ mysql-test/r/backup_locks_binlog_stmt.result 2014-02-20 11:26:00 +0000
3017@@ -0,0 +1,575 @@
3018+SET @old_binlog_format=@@binlog_format;
3019+SET GLOBAL binlog_format='STATEMENT';
3020+SET SESSION binlog_format='STATEMENT';
3021+SELECT @@binlog_format;
3022+@@binlog_format
3023+STATEMENT
3024+RESET MASTER;
3025+SHOW VARIABLES LIKE 'have_backup_locks';
3026+Variable_name Value
3027+have_backup_locks YES
3028+#-----------------------------------------------------------------------
3029+# Setup
3030+#-----------------------------------------------------------------------
3031+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
3032+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
3033+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
3034+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
3035+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
3036+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
3037+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
3038+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
3039+CREATE VIEW v_memory AS SELECT * FROM t_memory;
3040+CREATE VIEW v_csv AS SELECT * FROM t_csv;
3041+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
3042+CREATE VIEW v_archive AS SELECT * FROM t_archive;
3043+#-----------------------------------------------------------------------
3044+# Single-threaded tests
3045+#-----------------------------------------------------------------------
3046+LOCK BINLOG FOR BACKUP;
3047+DELETE FROM t_innodb;
3048+INSERT INTO t_innodb VALUES(0);
3049+UPDATE t_innodb SET a = 1;
3050+REPLACE INTO t_innodb VALUES(1);
3051+SELECT * from t_innodb;
3052+a
3053+1
3054+1
3055+HANDLER t_innodb OPEN;
3056+HANDLER t_innodb READ a FIRST;
3057+a
3058+1
3059+HANDLER t_innodb CLOSE;
3060+DELETE FROM t_myisam;
3061+INSERT INTO t_myisam VALUES(0);
3062+UPDATE t_myisam SET a = 1;
3063+REPLACE INTO t_myisam VALUES(1);
3064+SELECT * from t_myisam;
3065+a
3066+1
3067+1
3068+HANDLER t_myisam OPEN;
3069+HANDLER t_myisam READ a FIRST;
3070+a
3071+1
3072+HANDLER t_myisam CLOSE;
3073+DELETE FROM t_memory;
3074+INSERT INTO t_memory VALUES(0);
3075+UPDATE t_memory SET a = 1;
3076+REPLACE INTO t_memory VALUES(1);
3077+SELECT * from t_memory;
3078+a
3079+1
3080+1
3081+DELETE FROM t_archive;
3082+INSERT INTO t_archive VALUES(0);
3083+SELECT * from t_archive;
3084+a
3085+0
3086+DELETE FROM t_csv;
3087+INSERT INTO t_csv VALUES(0);
3088+UPDATE t_csv SET a = 1;
3089+REPLACE INTO t_csv VALUES(1);
3090+SELECT * from t_csv;
3091+a
3092+1
3093+1
3094+DELETE FROM t_blackhole;
3095+INSERT INTO t_blackhole VALUES(0);
3096+UPDATE t_blackhole SET a = 1;
3097+REPLACE INTO t_blackhole VALUES(1);
3098+SELECT * from t_blackhole;
3099+a
3100+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
3101+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
3102+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
3103+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
3104+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
3105+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
3106+DROP TABLE tt_innodb;
3107+DROP TABLE tt_myisam;
3108+DROP TABLE tt_memory;
3109+DROP TABLE tt_csv;
3110+DROP TABLE tt_blackhole;
3111+DROP TABLE tt_archive;
3112+DROP TABLE non_existing;
3113+ERROR 42S02: Unknown table 'test.non_existing'
3114+TRUNCATE TABLE t_innodb;
3115+TRUNCATE TABLE t_myisam;
3116+TRUNCATE TABLE t_memory;
3117+TRUNCATE TABLE t_csv;
3118+TRUNCATE TABLE t_blackhole;
3119+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
3120+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
3121+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
3122+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
3123+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
3124+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
3125+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
3126+ALTER TABLE t_innodb DROP COLUMN b;
3127+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
3128+ALTER TABLE t_myisam DROP COLUMN b;
3129+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
3130+ALTER TABLE t_memory DROP COLUMN b;
3131+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
3132+ALTER TABLE t_csv DROP COLUMN b;
3133+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
3134+ALTER TABLE t_blackhole DROP COLUMN b;
3135+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
3136+ALTER TABLE t_archive DROP COLUMN b;
3137+ALTER TABLE t_innodb ADD KEY tmp (a);
3138+Warnings:
3139+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_innodb'. This is deprecated and will be disallowed in a future release.
3140+ALTER TABLE t_innodb DROP KEY tmp;
3141+ALTER TABLE t_myisam ADD KEY tmp (a);
3142+Warnings:
3143+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_myisam'. This is deprecated and will be disallowed in a future release.
3144+ALTER TABLE t_myisam DROP KEY tmp;
3145+ALTER TABLE t_memory ADD KEY tmp (a);
3146+Warnings:
3147+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_memory'. This is deprecated and will be disallowed in a future release.
3148+ALTER TABLE t_memory DROP KEY tmp;
3149+ALTER TABLE t_blackhole ADD KEY tmp (a);
3150+Warnings:
3151+Note 1831 Duplicate index 'tmp' defined on the table 'test.t_blackhole'. This is deprecated and will be disallowed in a future release.
3152+ALTER TABLE t_blackhole DROP KEY tmp;
3153+CREATE DATABASE test1;
3154+DROP DATABASE test1;
3155+CREATE PROCEDURE p1()
3156+BEGIN
3157+SELECT 1;
3158+END|
3159+DROP PROCEDURE p1|
3160+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
3161+BEGIN
3162+RETURN 1;
3163+END|
3164+DROP FUNCTION f1|
3165+CREATE VIEW v1 AS SELECT * FROM t_innodb;
3166+DROP VIEW v1;
3167+INSERT INTO v_innodb VALUES(1);
3168+INSERT INTO v_blackhole VALUES(1);
3169+INSERT INTO v_myisam VALUES(1);
3170+INSERT INTO v_csv VALUES(1);
3171+INSERT INTO v_memory VALUES(1);
3172+INSERT INTO v_archive VALUES(1);
3173+UNLOCK BINLOG;
3174+SELECT @@delay_key_write;
3175+@@delay_key_write
3176+ON
3177+SET GLOBAL delay_key_write=ALL;
3178+LOCK TABLES FOR BACKUP;
3179+ERROR HY000: The MySQL server is running with the delay_key_write=ALL option so it cannot execute this statement
3180+SET GLOBAL delay_key_write=default;
3181+SELECT @@delay_key_write;
3182+@@delay_key_write
3183+ON
3184+SET GLOBAL read_only=1;
3185+INSERT INTO t_innodb VALUES(0);
3186+INSERT INTO t_myisam VALUES(0);
3187+LOCK TABLES FOR BACKUP;
3188+INSERT INTO t_innodb VALUES(0);
3189+INSERT INTO t_myisam VALUES(0);
3190+ERROR HY000: Can’t execute the query because you have a conflicting backup lock
3191+UNLOCK TABLES;
3192+INSERT INTO t_innodb VALUES(0);
3193+INSERT INTO t_myisam VALUES(0);
3194+SET GLOBAL read_only=0;
3195+LOCK TABLES FOR BACKUP;
3196+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
3197+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
3198+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
3199+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
3200+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
3201+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
3202+INSERT INTO tt_innodb VALUES(5);
3203+INSERT INTO tt_myisam VALUES(5);
3204+INSERT INTO tt_memory VALUES(5);
3205+INSERT INTO tt_csv VALUES(5);
3206+INSERT INTO tt_blackhole VALUES(5);
3207+INSERT INTO tt_archive VALUES(5);
3208+LOCK BINLOG FOR BACKUP;
3209+START TRANSACTION;
3210+SELECT * FROM tt_archive;
3211+a
3212+5
3213+SELECT * FROM tt_blackhole;
3214+a
3215+SELECT * FROM tt_memory;
3216+a
3217+5
3218+SELECT * FROM tt_innodb;
3219+a
3220+5
3221+SELECT * FROM tt_myisam;
3222+a
3223+5
3224+INSERT INTO tt_innodb VALUES(6);
3225+INSERT INTO tt_myisam VALUES(6);
3226+INSERT INTO tt_memory VALUES(6);
3227+INSERT INTO tt_csv VALUES(6);
3228+INSERT INTO tt_blackhole VALUES(6);
3229+INSERT INTO tt_archive VALUES(6);
3230+COMMIT;
3231+SELECT * FROM tt_archive;
3232+a
3233+5
3234+6
3235+SELECT * FROM tt_blackhole;
3236+a
3237+SELECT * FROM tt_memory;
3238+a
3239+5
3240+6
3241+SELECT * FROM tt_innodb;
3242+a
3243+5
3244+6
3245+SELECT * FROM tt_myisam;
3246+a
3247+5
3248+6
3249+DROP TEMPORARY TABLE tt_innodb;
3250+DROP TEMPORARY TABLE tt_myisam;
3251+DROP TEMPORARY TABLE tt_memory;
3252+DROP TEMPORARY TABLE tt_csv;
3253+DROP TEMPORARY TABLE tt_blackhole;
3254+DROP TEMPORARY TABLE tt_archive;
3255+UNLOCK BINLOG;
3256+UNLOCK TABLES;
3257+SELECT @@log_bin;
3258+@@log_bin
3259+1
3260+LOCK BINLOG FOR BACKUP;
3261+INSERT INTO t_innodb VALUES(1);
3262+INSERT INTO t_myisam VALUES(1);
3263+UNLOCK BINLOG;
3264+SET @old_general_log = @@general_log;
3265+SET @old_slow_query_log = @@slow_query_log;
3266+SET @old_log_output = @@log_output;
3267+SET @old_long_query_time = @@SESSION.long_query_time;
3268+SET SESSION long_query_time = 0;
3269+SET GLOBAL log_output = 'TABLE';
3270+SET GLOBAL general_log = ON;
3271+SET GLOBAL slow_query_log = ON;
3272+LOCK TABLES FOR BACKUP;
3273+SELECT 1;
3274+1
3275+1
3276+LOCK BINLOG FOR BACKUP;
3277+SELECT 1;
3278+1
3279+1
3280+UNLOCK BINLOG;
3281+UNLOCK TABLES;
3282+SET SESSION long_query_time = @old_long_query_time;
3283+SET GLOBAL log_output = @old_log_output;
3284+SET GLOBAL slow_query_log = @old_slow_query_log;
3285+SET GLOBAL general_log = @old_general_log;
3286+#-----------------------------------------------------------------------
3287+# Multi-threaded tests
3288+#-----------------------------------------------------------------------
3289+# connection default
3290+CREATE USER user@localhost;
3291+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
3292+LOCK TABLES FOR BACKUP;
3293+# connection con1
3294+SET SESSION lock_wait_timeout = 1;
3295+SET GLOBAL delay_key_write=ALL;
3296+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3297+SET SESSION lock_wait_timeout = DEFAULT;
3298+# connection default
3299+UNLOCK TABLES;
3300+# connection con2
3301+START TRANSACTION;
3302+INSERT INTO t_innodb VALUES(0);
3303+# connection default
3304+SET GLOBAL read_only=1;
3305+# connection con2
3306+COMMIT;
3307+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
3308+INSERT INTO t_innodb VALUES(0);
3309+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
3310+INSERT INTO t_myisam VALUES(0);
3311+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
3312+# connection con1
3313+LOCK TABLES FOR BACKUP;
3314+# connection con2
3315+INSERT INTO t_innodb VALUES(0);
3316+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
3317+INSERT INTO t_myisam VALUES(0);
3318+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
3319+# connection default
3320+SET GLOBAL read_only=0;
3321+# connection con2
3322+SET SESSION lock_wait_timeout = 1;
3323+INSERT INTO t_innodb VALUES(0);
3324+INSERT INTO t_myisam VALUES(0);
3325+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3326+SET SESSION lock_wait_timeout = 0;
3327+Warnings:
3328+Warning 1292 Truncated incorrect lock_wait_timeout value: '0'
3329+# connection default
3330+SET GLOBAL read_only=1;
3331+# connection con2
3332+INSERT INTO t_innodb VALUES(0);
3333+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
3334+INSERT INTO t_myisam VALUES(0);
3335+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
3336+# connection con1
3337+UNLOCK TABLES;
3338+# connection con2
3339+INSERT INTO t_innodb VALUES(0);
3340+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
3341+INSERT INTO t_myisam VALUES(0);
3342+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
3343+# connection default
3344+SET GLOBAL read_only=0;
3345+# connection default
3346+LOCK TABLES FOR BACKUP;
3347+# connection con1
3348+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
3349+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
3350+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
3351+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
3352+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
3353+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
3354+INSERT INTO tt_innodb VALUES(5);
3355+INSERT INTO tt_myisam VALUES(5);
3356+INSERT INTO tt_memory VALUES(5);
3357+INSERT INTO tt_csv VALUES(5);
3358+INSERT INTO tt_blackhole VALUES(5);
3359+INSERT INTO tt_archive VALUES(5);
3360+# connection default
3361+LOCK BINLOG FOR BACKUP;
3362+# connection con1
3363+SET SESSION lock_wait_timeout = 1;
3364+START TRANSACTION;
3365+INSERT INTO tt_myisam VALUES(6);
3366+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3367+INSERT INTO tt_innodb VALUES(6);
3368+SELECT * FROM tt_archive;
3369+a
3370+5
3371+SELECT * FROM tt_blackhole;
3372+a
3373+SELECT * FROM tt_memory;
3374+a
3375+5
3376+SELECT * FROM tt_innodb;
3377+a
3378+5
3379+6
3380+SELECT * FROM tt_myisam;
3381+a
3382+5
3383+6
3384+SELECT * FROM tt_csv;
3385+a
3386+5
3387+COMMIT;
3388+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3389+# connection default
3390+must_be_1
3391+1
3392+UNLOCK BINLOG;
3393+UNLOCK TABLES;
3394+# connection con1
3395+COMMIT;
3396+DROP TEMPORARY TABLE tt_innodb;
3397+DROP TEMPORARY TABLE tt_myisam;
3398+DROP TEMPORARY TABLE tt_memory;
3399+DROP TEMPORARY TABLE tt_csv;
3400+DROP TEMPORARY TABLE tt_blackhole;
3401+DROP TEMPORARY TABLE tt_archive;
3402+# connection default
3403+LOCK BINLOG FOR BACKUP;
3404+# connection con1
3405+SET SESSION lock_wait_timeout = 1;
3406+INSERT INTO t_innodb VALUES(1);
3407+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3408+INSERT INTO t_myisam VALUES(1);
3409+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3410+SET SESSION lock_wait_timeout = DEFAULT;
3411+# connection default
3412+must_be_1
3413+1
3414+UNLOCK BINLOG;
3415+# connection default
3416+LOCK BINLOG FOR BACKUP;
3417+# connection con1
3418+SET SESSION lock_wait_timeout = 1;
3419+DELETE FROM t_innodb;
3420+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3421+INSERT INTO t_innodb VALUES(0);
3422+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3423+UPDATE t_innodb SET a = 1;
3424+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3425+REPLACE INTO t_innodb VALUES(1);
3426+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3427+SELECT * from t_innodb;
3428+a
3429+0
3430+0
3431+0
3432+0
3433+1
3434+1
3435+HANDLER t_innodb OPEN;
3436+HANDLER t_innodb READ a FIRST;
3437+a
3438+0
3439+HANDLER t_innodb CLOSE;
3440+START TRANSACTION;
3441+SELECT * from t_innodb;
3442+a
3443+0
3444+0
3445+0
3446+0
3447+1
3448+1
3449+DELETE FROM t_innodb;
3450+INSERT INTO t_innodb VALUES(0);
3451+UPDATE t_innodb SET a = 1;
3452+REPLACE INTO t_innodb VALUES(1);
3453+COMMIT;
3454+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3455+SELECT * FROM t_innodb;
3456+a
3457+0
3458+0
3459+0
3460+0
3461+1
3462+1
3463+COMMIT;
3464+START TRANSACTION;
3465+INSERT INTO t_myisam VALUES(1);
3466+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3467+COMMIT;
3468+INSERT INTO t_myisam VALUES(0);
3469+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3470+UPDATE t_myisam SET a = 1;
3471+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3472+SELECT * FROM t_myisam;
3473+a
3474+1
3475+1
3476+1
3477+1
3478+1
3479+1
3480+1
3481+HANDLER t_myisam OPEN;
3482+HANDLER t_myisam READ a FIRST;
3483+a
3484+1
3485+HANDLER t_myisam CLOSE;
3486+DELETE FROM t_blackhole;
3487+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3488+INSERT INTO t_blackhole VALUES(0);
3489+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3490+UPDATE t_blackhole SET a = 1;
3491+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3492+REPLACE INTO t_blackhole VALUES(1);
3493+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3494+SELECT * FROM t_blackhole;
3495+a
3496+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
3497+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3498+DROP TABLE tt_innodb;
3499+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3500+DROP TABLE non_existing;
3501+ERROR 42S02: Unknown table 'test.non_existing'
3502+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
3503+ERROR 42S01: Table 't_innodb' already exists
3504+TRUNCATE TABLE t_innodb;
3505+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3506+RENAME TABLE t_innodb TO tmp, tmp to t_innodb;
3507+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3508+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
3509+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3510+ALTER TABLE t_innodb ADD KEY (a);
3511+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3512+CREATE DATABASE test1;
3513+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3514+DROP DATABASE test1;
3515+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3516+CREATE PROCEDURE p1() SELECT 1;
3517+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3518+DROP PROCEDURE p1;
3519+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3520+CREATE VIEW v1 AS SELECT * FROM t_innodb;
3521+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3522+DROP VIEW v1;
3523+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3524+INSERT INTO v_innodb VALUES(1);
3525+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3526+# connection default
3527+must_be_1
3528+1
3529+UNLOCK BINLOG;
3530+# connection con1
3531+SHOW CREATE TABLE t_innodb;
3532+Table Create Table
3533+t_innodb CREATE TABLE `t_innodb` (
3534+ `a` int(11) NOT NULL,
3535+ `b` char(10) DEFAULT NULL,
3536+ KEY `a` (`a`),
3537+ KEY `a_2` (`a`)
3538+) ENGINE=InnoDB DEFAULT CHARSET=latin1
3539+DROP TABLE t_innodb;
3540+CREATE TABLE t_innodb(a INT NOT NULL) ENGINE=InnoDB;
3541+# connection default
3542+LOCK BINLOG FOR BACKUP;
3543+# connection con1
3544+INSERT DELAYED INTO t_myisam VALUES(0);
3545+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3546+# connection default
3547+must_be_1
3548+1
3549+UNLOCK BINLOG;
3550+SELECT * FROm t_myisam;
3551+a
3552+0
3553+1
3554+1
3555+1
3556+1
3557+1
3558+1
3559+1
3560+SET @old_general_log = @@general_log;
3561+SET @old_slow_query_log = @@slow_query_log;
3562+SET @old_log_output = @@log_output;
3563+SET GLOBAL log_output = 'TABLE';
3564+SET GLOBAL general_log = ON;
3565+SET GLOBAL slow_query_log = ON;
3566+LOCK BINLOG FOR BACKUP;
3567+# connection con1
3568+SET @old_long_query_time = @@SESSION.long_query_time;
3569+SET SESSION long_query_time = 0;
3570+SELECT 1;
3571+1
3572+1
3573+# connection default
3574+SELECT 1;
3575+1
3576+1
3577+# connection con1
3578+SET SESSION long_query_time = @old_long_query_time;
3579+# connection default
3580+must_be_1
3581+1
3582+UNLOCK BINLOG;
3583+SET GLOBAL log_output = @old_log_output;
3584+SET GLOBAL slow_query_log = @old_slow_query_log;
3585+SET GLOBAL general_log = @old_general_log;
3586+#-----------------------------------------------------------------------
3587+# Cleanup
3588+#-----------------------------------------------------------------------
3589+DROP USER user@localhost;
3590+DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
3591+DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;
3592+SET GLOBAL binlog_format = @old_binlog_format;
3593
3594=== added file 'mysql-test/r/backup_locks_mysqldump.result'
3595--- mysql-test/r/backup_locks_mysqldump.result 1970-01-01 00:00:00 +0000
3596+++ mysql-test/r/backup_locks_mysqldump.result 2014-02-20 11:26:00 +0000
3597@@ -0,0 +1,143 @@
3598+SHOW VARIABLES LIKE 'have_backup_locks';
3599+Variable_name Value
3600+have_backup_locks YES
3601+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
3602+CREATE TABLE t2 (a INT) ENGINE=MyISAM;
3603+SET @old_general_log = @@general_log;
3604+SET @old_log_output = @@log_output;
3605+TRUNCATE TABLE mysql.general_log;
3606+SET GLOBAL log_output = 'TABLE';
3607+SET GLOBAL general_log = ON;
3608+# Check that --lock-for-backup is converted to --lock-all-tables if
3609+# --single-transaction is not specified
3610+SELECT argument FROM mysql.general_log WHERE argument != '';
3611+argument
3612+SET GLOBAL general_log = ON
3613+root@localhost on
3614+/*!40100 SET @@SQL_MODE='' */
3615+/*!40103 SET TIME_ZONE='+00:00' */
3616+FLUSH TABLES
3617+FLUSH TABLES WITH READ LOCK
3618+SHOW VARIABLES LIKE 'gtid\_mode'
3619+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
3620+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
3621+SHOW VARIABLES LIKE 'ndbinfo\_version'
3622+test
3623+show tables
3624+show table status like 't1'
3625+SET SQL_QUOTE_SHOW_CREATE=1
3626+SET SESSION character_set_results = 'binary'
3627+show create table `t1`
3628+SET SESSION character_set_results = 'utf8'
3629+show fields from `t1`
3630+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
3631+SET SESSION character_set_results = 'binary'
3632+use `test`
3633+select @@collation_database
3634+SHOW TRIGGERS LIKE 't1'
3635+SET SESSION character_set_results = 'utf8'
3636+show table status like 't2'
3637+SET SQL_QUOTE_SHOW_CREATE=1
3638+SET SESSION character_set_results = 'binary'
3639+show create table `t2`
3640+SET SESSION character_set_results = 'utf8'
3641+show fields from `t2`
3642+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
3643+SET SESSION character_set_results = 'binary'
3644+use `test`
3645+select @@collation_database
3646+SHOW TRIGGERS LIKE 't2'
3647+SET SESSION character_set_results = 'utf8'
3648+SELECT argument FROM mysql.general_log WHERE argument != ''
3649+TRUNCATE TABLE mysql.general_log;
3650+# Check that --lock-for-backup --single-transaction uses LOCK TABLES FOR
3651+# BACKUP
3652+SELECT argument FROM mysql.general_log WHERE argument != '';
3653+argument
3654+root@localhost on
3655+/*!40100 SET @@SQL_MODE='' */
3656+/*!40103 SET TIME_ZONE='+00:00' */
3657+SHOW VARIABLES LIKE 'have_backup_locks'
3658+LOCK TABLES FOR BACKUP
3659+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
3660+START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
3661+SHOW VARIABLES LIKE 'gtid\_mode'
3662+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
3663+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
3664+SHOW VARIABLES LIKE 'ndbinfo\_version'
3665+test
3666+show tables
3667+show table status like 't1'
3668+SET SQL_QUOTE_SHOW_CREATE=1
3669+SET SESSION character_set_results = 'binary'
3670+show create table `t1`
3671+SET SESSION character_set_results = 'utf8'
3672+show fields from `t1`
3673+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
3674+SET SESSION character_set_results = 'binary'
3675+use `test`
3676+select @@collation_database
3677+SHOW TRIGGERS LIKE 't1'
3678+SET SESSION character_set_results = 'utf8'
3679+show table status like 't2'
3680+SET SQL_QUOTE_SHOW_CREATE=1
3681+SET SESSION character_set_results = 'binary'
3682+show create table `t2`
3683+SET SESSION character_set_results = 'utf8'
3684+show fields from `t2`
3685+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
3686+SET SESSION character_set_results = 'binary'
3687+use `test`
3688+select @@collation_database
3689+SHOW TRIGGERS LIKE 't2'
3690+SET SESSION character_set_results = 'utf8'
3691+SELECT argument FROM mysql.general_log WHERE argument != ''
3692+TRUNCATE TABLE mysql.general_log;
3693+# Check that --master-data disables --lock-for-backup
3694+SELECT argument FROM mysql.general_log WHERE argument != '';
3695+argument
3696+root@localhost on
3697+/*!40100 SET @@SQL_MODE='' */
3698+/*!40103 SET TIME_ZONE='+00:00' */
3699+SHOW VARIABLES LIKE 'have_backup_locks'
3700+FLUSH /*!40101 LOCAL */ TABLES
3701+FLUSH TABLES WITH READ LOCK
3702+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
3703+START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
3704+SHOW VARIABLES LIKE 'gtid\_mode'
3705+SHOW MASTER STATUS
3706+UNLOCK TABLES
3707+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
3708+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
3709+SHOW VARIABLES LIKE 'ndbinfo\_version'
3710+test
3711+show tables
3712+show table status like 't1'
3713+SET SQL_QUOTE_SHOW_CREATE=1
3714+SET SESSION character_set_results = 'binary'
3715+show create table `t1`
3716+SET SESSION character_set_results = 'utf8'
3717+show fields from `t1`
3718+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
3719+SET SESSION character_set_results = 'binary'
3720+use `test`
3721+select @@collation_database
3722+SHOW TRIGGERS LIKE 't1'
3723+SET SESSION character_set_results = 'utf8'
3724+show table status like 't2'
3725+SET SQL_QUOTE_SHOW_CREATE=1
3726+SET SESSION character_set_results = 'binary'
3727+show create table `t2`
3728+SET SESSION character_set_results = 'utf8'
3729+show fields from `t2`
3730+SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
3731+SET SESSION character_set_results = 'binary'
3732+use `test`
3733+select @@collation_database
3734+SHOW TRIGGERS LIKE 't2'
3735+SET SESSION character_set_results = 'utf8'
3736+SELECT argument FROM mysql.general_log WHERE argument != ''
3737+TRUNCATE TABLE mysql.general_log;
3738+SET GLOBAL log_output = @old_log_output;
3739+SET GLOBAL general_log = @old_general_log;
3740+DROP TABLE t1, t2;
3741
3742=== modified file 'mysql-test/suite/funcs_1/r/storedproc.result'
3743--- mysql-test/suite/funcs_1/r/storedproc.result 2013-05-10 09:39:17 +0000
3744+++ mysql-test/suite/funcs_1/r/storedproc.result 2014-02-20 11:26:00 +0000
3745@@ -5522,7 +5522,9 @@
3746 unlock:BEGIN
3747 SELECT @x;
3748 END//
3749-ERROR 0A000: UNLOCK is not allowed in stored procedures
3750+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
3751+SELECT @x;
3752+END' at line 2
3753 DROP PROCEDURE IF EXISTS sp1;
3754 CREATE PROCEDURE sp1()
3755 unsigned:BEGIN
3756
3757=== modified file 'mysql-test/suite/funcs_1/t/storedproc.test'
3758--- mysql-test/suite/funcs_1/t/storedproc.test 2011-12-09 21:08:37 +0000
3759+++ mysql-test/suite/funcs_1/t/storedproc.test 2014-02-20 11:26:00 +0000
3760@@ -5226,7 +5226,7 @@
3761 DROP PROCEDURE IF EXISTS sp1;
3762
3763 delimiter //;
3764---error ER_SP_BADSTATEMENT
3765+--error ER_PARSE_ERROR
3766 CREATE PROCEDURE sp1()
3767 unlock:BEGIN
3768 SELECT @x;
3769
3770=== added file 'mysql-test/suite/rpl/r/rpl_backup_locks.result'
3771--- mysql-test/suite/rpl/r/rpl_backup_locks.result 1970-01-01 00:00:00 +0000
3772+++ mysql-test/suite/rpl/r/rpl_backup_locks.result 2014-02-20 11:26:00 +0000
3773@@ -0,0 +1,34 @@
3774+include/master-slave.inc
3775+Warnings:
3776+Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
3777+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.
3778+[connection master]
3779+CREATE TABLE t_innodb (a INT) ENGINE=InnoDB;
3780+CREATE TABLE t_myisam (a INT) ENGINE=MyISAM;
3781+STOP SLAVE;
3782+include/wait_for_slave_to_stop.inc
3783+# connection master
3784+INSERT INTO t_innodb VALUES (0);
3785+# connection slave
3786+LOCK TABLES FOR BACKUP;
3787+LOCK BINLOG FOR BACKUP;
3788+START SLAVE;
3789+include/wait_for_slave_to_start.inc
3790+include/check_slave_param.inc [Exec_Master_Log_Pos]
3791+UNLOCK BINLOG;
3792+UNLOCK TABLES;
3793+# connection master
3794+# connection slave
3795+STOP SLAVE;
3796+# connection master
3797+INSERT INTO t_myisam VALUES (0);
3798+# connection slave
3799+LOCK TABLES FOR BACKUP;
3800+START SLAVE;
3801+LOCK BINLOG FOR BACKUP;
3802+UNLOCK TABLES;
3803+include/check_slave_param.inc [Exec_Master_Log_Pos]
3804+UNLOCK BINLOG;
3805+# connection master
3806+DROP TABLE t_innodb, t_myisam;
3807+include/rpl_end.inc
3808
3809=== added file 'mysql-test/suite/rpl/t/rpl_backup_locks.test'
3810--- mysql-test/suite/rpl/t/rpl_backup_locks.test 1970-01-01 00:00:00 +0000
3811+++ mysql-test/suite/rpl/t/rpl_backup_locks.test 2014-02-20 11:26:00 +0000
3812@@ -0,0 +1,95 @@
3813+########################################################################
3814+# Replication-related tests for backup locks
3815+########################################################################
3816+
3817+--source include/have_innodb.inc
3818+--source include/master-slave.inc
3819+
3820+CREATE TABLE t_innodb (a INT) ENGINE=InnoDB;
3821+CREATE TABLE t_myisam (a INT) ENGINE=MyISAM;
3822+
3823+sync_slave_with_master;
3824+
3825+STOP SLAVE;
3826+--source include/wait_for_slave_to_stop.inc
3827+
3828+--connection master
3829+--echo # connection master
3830+
3831+INSERT INTO t_innodb VALUES (0);
3832+
3833+--connection slave
3834+--echo # connection slave
3835+
3836+--let $master_log_pos= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1)
3837+
3838+LOCK TABLES FOR BACKUP;
3839+LOCK BINLOG FOR BACKUP;
3840+
3841+START SLAVE;
3842+--source include/wait_for_slave_to_start.inc
3843+
3844+let $wait_condition=
3845+ SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
3846+ WHERE STATE = "Waiting for binlog lock" AND
3847+ (INFO = "INSERT INTO t_innodb VALUES (0)" OR INFO IS NULL);
3848+--source include/wait_condition.inc
3849+
3850+--let $slave_param= Exec_Master_Log_Pos
3851+--let $slave_param_value= $master_log_pos
3852+--source include/check_slave_param.inc
3853+
3854+UNLOCK BINLOG;
3855+UNLOCK TABLES;
3856+
3857+--connection master
3858+--echo # connection master
3859+sync_slave_with_master;
3860+
3861+
3862+--connection slave
3863+--echo # connection slave
3864+STOP SLAVE;
3865+
3866+--connection master
3867+--echo # connection master
3868+
3869+INSERT INTO t_myisam VALUES (0);
3870+
3871+--connection slave
3872+--echo # connection slave
3873+
3874+--let $master_log_pos= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1)
3875+
3876+LOCK TABLES FOR BACKUP;
3877+
3878+START SLAVE;
3879+
3880+let $wait_condition=
3881+ SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
3882+ WHERE STATE = "Waiting for backup lock" AND
3883+ (INFO = "INSERT INTO t_myisam VALUES (0)" OR INFO IS NULL);
3884+--source include/wait_condition.inc
3885+
3886+LOCK BINLOG FOR BACKUP;
3887+UNLOCK TABLES;
3888+
3889+let $wait_condition=
3890+ SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
3891+ WHERE STATE = "Waiting for binlog lock" AND
3892+ (INFO = "INSERT INTO t_myisam VALUES (0)" OR INFO IS NULL);
3893+--source include/wait_condition.inc
3894+
3895+--let $slave_param= Exec_Master_Log_Pos
3896+--let $slave_param_value= $master_log_pos
3897+--source include/check_slave_param.inc
3898+
3899+UNLOCK BINLOG;
3900+
3901+--connection master
3902+--echo # connection master
3903+
3904+DROP TABLE t_innodb, t_myisam;
3905+sync_slave_with_master;
3906+
3907+--source include/rpl_end.inc
3908
3909=== added file 'mysql-test/suite/sys_vars/r/have_backup_locks_basic.result'
3910--- mysql-test/suite/sys_vars/r/have_backup_locks_basic.result 1970-01-01 00:00:00 +0000
3911+++ mysql-test/suite/sys_vars/r/have_backup_locks_basic.result 2014-02-20 11:26:00 +0000
3912@@ -0,0 +1,11 @@
3913+SELECT @@GLOBAL.have_backup_locks="YES";
3914+@@GLOBAL.have_backup_locks="YES"
3915+1
3916+SELECT @@SESSION.have_backup_locks;
3917+ERROR HY000: Variable 'have_backup_locks' is a GLOBAL variable
3918+SHOW GLOBAL VARIABLES LIKE 'have_backup_locks';
3919+Variable_name Value
3920+have_backup_locks YES
3921+SHOW SESSION VARIABLES LIKE 'have_backup_locks';
3922+Variable_name Value
3923+have_backup_locks YES
3924
3925=== added file 'mysql-test/suite/sys_vars/t/have_backup_locks_basic.test'
3926--- mysql-test/suite/sys_vars/t/have_backup_locks_basic.test 1970-01-01 00:00:00 +0000
3927+++ mysql-test/suite/sys_vars/t/have_backup_locks_basic.test 2014-02-20 11:26:00 +0000
3928@@ -0,0 +1,7 @@
3929+SELECT @@GLOBAL.have_backup_locks="YES";
3930+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
3931+SELECT @@SESSION.have_backup_locks;
3932+
3933+SHOW GLOBAL VARIABLES LIKE 'have_backup_locks';
3934+
3935+SHOW SESSION VARIABLES LIKE 'have_backup_locks';
3936
3937=== added file 'mysql-test/t/backup_locks.test'
3938--- mysql-test/t/backup_locks.test 1970-01-01 00:00:00 +0000
3939+++ mysql-test/t/backup_locks.test 2014-02-20 11:26:00 +0000
3940@@ -0,0 +1,1091 @@
3941+########################################################################
3942+# Tests for LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP
3943+########################################################################
3944+
3945+--source include/have_innodb.inc
3946+--source include/not_embedded.inc
3947+--source include/have_archive.inc
3948+--source include/have_csv.inc
3949+--source include/have_blackhole.inc
3950+
3951+SHOW VARIABLES LIKE 'have_backup_locks';
3952+
3953+--echo #-----------------------------------------------------------------------
3954+--echo # Setup
3955+--echo #-----------------------------------------------------------------------
3956+
3957+# Save the initial number of concurrent sessions.
3958+--source include/count_sessions.inc
3959+
3960+CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
3961+CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
3962+CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
3963+CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
3964+CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
3965+CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;
3966+
3967+CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
3968+CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
3969+CREATE VIEW v_memory AS SELECT * FROM t_memory;
3970+CREATE VIEW v_csv AS SELECT * FROM t_csv;
3971+CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
3972+CREATE VIEW v_archive AS SELECT * FROM t_archive;
3973+
3974+--echo #-----------------------------------------------------------------------
3975+--echo # Basic syntax checks
3976+--echo #-----------------------------------------------------------------------
3977+
3978+--error ER_PARSE_ERROR
3979+LOCK TABLES t_innodb FOR BACKUP;
3980+--error ER_PARSE_ERROR
3981+LOCK TABLES t_innodb READ FOR BACKUP;
3982+--error ER_PARSE_ERROR
3983+LOCK TABLES t_innodb FOR BACKUP READ;
3984+--error ER_PARSE_ERROR
3985+LOCK BINLOG t_innodb FOR BACKUP;
3986+--error ER_PARSE_ERROR
3987+LOCK BINLOG t_innodb READ FOR BACKUP;
3988+--error ER_PARSE_ERROR
3989+LOCK BINLOG t_innodb FOR BACKUP READ;
3990+
3991+LOCK TABLES FOR BACKUP;
3992+UNLOCK TABLES;
3993+
3994+LOCK BINLOG FOR BACKUP;
3995+UNLOCK BINLOG;
3996+
3997+--echo #-----------------------------------------------------------------------
3998+--echo # No backup locks are allowed in stored routines
3999+--echo #-----------------------------------------------------------------------
4000+
4001+delimiter |;
4002+--error ER_SP_BADSTATEMENT
4003+CREATE PROCEDURE p1()
4004+BEGIN
4005+ LOCK TABLES FOR BACKUP;
4006+END|
4007+
4008+--error ER_SP_BADSTATEMENT
4009+CREATE PROCEDURE p2()
4010+BEGIN
4011+ LOCK BINLOG FOR BACKUP;
4012+END|
4013+
4014+--error ER_SP_BADSTATEMENT
4015+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
4016+BEGIN
4017+ LOCK TABLES FOR BACKUP;
4018+ RETURN 1;
4019+END|
4020+
4021+--error ER_SP_BADSTATEMENT
4022+CREATE FUNCTION f2() RETURNS INT DETERMINISTIC
4023+BEGIN
4024+ LOCK BINLOG FOR BACKUP;
4025+ RETURN 1;
4026+END|
4027+
4028+delimiter ;|
4029+
4030+--echo #-----------------------------------------------------------------------
4031+--echo # Single-threaded tests
4032+--echo #-----------------------------------------------------------------------
4033+
4034+SET SESSION lock_wait_timeout=1;
4035+
4036+FLUSH STATUS;
4037+
4038+#
4039+# Multiple TFB locks in the same connection
4040+#
4041+LOCK TABLES FOR BACKUP;
4042+LOCK TABLES FOR BACKUP;
4043+SHOW STATUS LIKE 'Com_lock_tables_for_backup';
4044+
4045+UNLOCK TABLES;
4046+SHOW STATUS LIKE 'Com_unlock_tables';
4047+
4048+#
4049+# Multiple BFB locks in the same connection
4050+#
4051+LOCK BINLOG FOR BACKUP;
4052+LOCK BINLOG FOR BACKUP;
4053+
4054+SHOW STATUS LIKE 'Com_lock_binlog_for_backup';
4055+
4056+UNLOCK BINLOG;
4057+SHOW STATUS LIKE 'Com_unlock_binlog';
4058+
4059+#
4060+# LTFB + DDL / DML in the same connection
4061+#
4062+
4063+LOCK TABLES FOR BACKUP;
4064+
4065+DELETE FROM t_innodb;
4066+INSERT INTO t_innodb VALUES(0);
4067+UPDATE t_innodb SET a = 1;
4068+REPLACE INTO t_innodb VALUES(1);
4069+SELECT * FROM t_innodb;
4070+HANDLER t_innodb OPEN;
4071+HANDLER t_innodb READ a FIRST;
4072+HANDLER t_innodb CLOSE;
4073+
4074+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4075+DELETE FROM t_myisam;
4076+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4077+INSERT INTO t_myisam VALUES(0);
4078+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4079+INSERT DELAYED INTO t_myisam VALUES(0);
4080+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4081+UPDATE t_myisam SET a = 1;
4082+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4083+REPLACE INTO t_myisam VALUES(1);
4084+SELECT * FROM t_myisam;
4085+HANDLER t_myisam OPEN;
4086+HANDLER t_myisam READ a FIRST;
4087+HANDLER t_myisam CLOSE;
4088+
4089+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4090+DELETE FROM t_memory;
4091+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4092+INSERT INTO t_memory VALUES(0);
4093+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4094+INSERT DELAYED INTO t_memory VALUES(0);
4095+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4096+UPDATE t_memory SET a = 1;
4097+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4098+REPLACE INTO t_memory VALUES(1);
4099+SELECT * FROM t_memory;
4100+
4101+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4102+DELETE FROM t_archive;
4103+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4104+INSERT INTO t_archive VALUES(0);
4105+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4106+INSERT DELAYED INTO t_archive VALUES(0);
4107+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4108+UPDATE t_archive SET a = 1;
4109+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4110+REPLACE INTO t_archive VALUES(1);
4111+SELECT * FROM t_archive;
4112+
4113+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4114+DELETE FROM t_csv;
4115+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4116+INSERT INTO t_csv VALUES(0);
4117+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4118+UPDATE t_csv SET a = 1;
4119+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4120+REPLACE INTO t_csv VALUES(1);
4121+SELECT * FROM t_csv;
4122+
4123+DELETE FROM t_blackhole;
4124+INSERT INTO t_blackhole VALUES(0);
4125+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4126+INSERT DELAYED INTO t_blackhole VALUES(0);
4127+UPDATE t_blackhole SET a = 1;
4128+REPLACE INTO t_blackhole VALUES(1);
4129+SELECT * FROM t_blackhole;
4130+
4131+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4132+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
4133+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4134+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
4135+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4136+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
4137+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4138+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
4139+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4140+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
4141+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4142+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
4143+
4144+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4145+DROP TABLE t_innodb;
4146+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4147+DROP TABLE t_myisam;
4148+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4149+DROP TABLE t_memory;
4150+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4151+DROP TABLE t_csv;
4152+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4153+DROP TABLE t_blackhole;
4154+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4155+DROP TABLE t_archive;
4156+
4157+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4158+DROP TABLE non_existing;
4159+
4160+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4161+TRUNCATE TABLE t_innodb;
4162+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4163+TRUNCATE TABLE t_myisam;
4164+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4165+TRUNCATE TABLE t_memory;
4166+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4167+TRUNCATE TABLE t_csv;
4168+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4169+TRUNCATE TABLE t_blackhole;
4170+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4171+TRUNCATE TABLE t_archive;
4172+
4173+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4174+RENAME TABLE t_innodb TO tmp;
4175+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4176+RENAME TABLE t_myisam TO tmp;
4177+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4178+RENAME TABLE t_memory TO tmp;
4179+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4180+RENAME TABLE t_csv TO tmp;
4181+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4182+RENAME TABLE t_blackhole TO tmp;
4183+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4184+RENAME TABLE t_archive TO tmp;
4185+
4186+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4187+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
4188+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4189+ALTER TABLE t_myisam ADD COLUMN b CHAR(10);
4190+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4191+ALTER TABLE t_memory ADD COLUMN b CHAR(10);
4192+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4193+ALTER TABLE t_csv ADD COLUMN b CHAR(10);
4194+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4195+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10);
4196+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4197+ALTER TABLE t_archive ADD COLUMN b CHAR(10);
4198+
4199+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4200+ALTER TABLE t_innodb ADD KEY (a);
4201+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4202+ALTER TABLE t_myisam ADD KEY (a);
4203+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4204+ALTER TABLE t_memory ADD KEY (a);
4205+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4206+ALTER TABLE t_csv ADD KEY (a);
4207+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4208+ALTER TABLE t_blackhole ADD KEY (a);
4209+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4210+ALTER TABLE t_archive ADD KEY (a);
4211+
4212+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4213+DROP DATABASE test;
4214+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4215+CREATE DATABASE test1;
4216+
4217+delimiter |;
4218+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4219+CREATE PROCEDURE p1()
4220+BEGIN
4221+ SELECT 1;
4222+END|
4223+
4224+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4225+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
4226+BEGIN
4227+ RETURN 1;
4228+END|
4229+
4230+delimiter ;|
4231+
4232+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4233+CREATE VIEW v1 AS SELECT * FROM t_innodb;
4234+
4235+INSERT INTO v_innodb VALUES(1);
4236+INSERT INTO v_blackhole VALUES(1);
4237+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4238+INSERT INTO v_myisam VALUES(1);
4239+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4240+INSERT INTO v_csv VALUES(1);
4241+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4242+INSERT INTO v_memory VALUES(1);
4243+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4244+INSERT INTO v_archive VALUES(1);
4245+
4246+UNLOCK TABLES;
4247+
4248+#
4249+# LBFB + DML/DDL in the same connection (and binlog disabled)
4250+#
4251+
4252+LOCK BINLOG FOR BACKUP;
4253+
4254+DELETE FROM t_innodb;
4255+INSERT INTO t_innodb VALUES(0);
4256+UPDATE t_innodb SET a = 1;
4257+REPLACE INTO t_innodb VALUES(1);
4258+SELECT * from t_innodb;
4259+HANDLER t_innodb OPEN;
4260+HANDLER t_innodb READ a FIRST;
4261+HANDLER t_innodb CLOSE;
4262+
4263+DELETE FROM t_myisam;
4264+INSERT INTO t_myisam VALUES(0);
4265+UPDATE t_myisam SET a = 1;
4266+REPLACE INTO t_myisam VALUES(1);
4267+SELECT * from t_myisam;
4268+HANDLER t_myisam OPEN;
4269+HANDLER t_myisam READ a FIRST;
4270+HANDLER t_myisam CLOSE;
4271+
4272+DELETE FROM t_memory;
4273+INSERT INTO t_memory VALUES(0);
4274+UPDATE t_memory SET a = 1;
4275+REPLACE INTO t_memory VALUES(1);
4276+SELECT * from t_memory;
4277+
4278+DELETE FROM t_archive;
4279+INSERT INTO t_archive VALUES(0);
4280+SELECT * from t_archive;
4281+
4282+DELETE FROM t_csv;
4283+INSERT INTO t_csv VALUES(0);
4284+UPDATE t_csv SET a = 1;
4285+REPLACE INTO t_csv VALUES(1);
4286+SELECT * from t_csv;
4287+
4288+DELETE FROM t_blackhole;
4289+INSERT INTO t_blackhole VALUES(0);
4290+UPDATE t_blackhole SET a = 1;
4291+REPLACE INTO t_blackhole VALUES(1);
4292+SELECT * from t_blackhole;
4293+
4294+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
4295+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
4296+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
4297+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
4298+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
4299+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
4300+
4301+DROP TABLE tt_innodb;
4302+DROP TABLE tt_myisam;
4303+DROP TABLE tt_memory;
4304+DROP TABLE tt_csv;
4305+DROP TABLE tt_blackhole;
4306+DROP TABLE tt_archive;
4307+
4308+--error ER_BAD_TABLE_ERROR
4309+DROP TABLE non_existing;
4310+
4311+TRUNCATE TABLE t_innodb;
4312+TRUNCATE TABLE t_myisam;
4313+TRUNCATE TABLE t_memory;
4314+TRUNCATE TABLE t_csv;
4315+TRUNCATE TABLE t_blackhole;
4316+
4317+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
4318+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
4319+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
4320+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
4321+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
4322+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
4323+
4324+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
4325+ALTER TABLE t_innodb DROP COLUMN b;
4326+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
4327+ALTER TABLE t_myisam DROP COLUMN b;
4328+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
4329+ALTER TABLE t_memory DROP COLUMN b;
4330+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
4331+ALTER TABLE t_csv DROP COLUMN b;
4332+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
4333+ALTER TABLE t_blackhole DROP COLUMN b;
4334+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
4335+ALTER TABLE t_archive DROP COLUMN b;
4336+
4337+ALTER TABLE t_innodb ADD KEY tmp (a);
4338+ALTER TABLE t_innodb DROP KEY tmp;
4339+ALTER TABLE t_myisam ADD KEY tmp (a);
4340+ALTER TABLE t_myisam DROP KEY tmp;
4341+ALTER TABLE t_memory ADD KEY tmp (a);
4342+ALTER TABLE t_memory DROP KEY tmp;
4343+ALTER TABLE t_blackhole ADD KEY tmp (a);
4344+ALTER TABLE t_blackhole DROP KEY tmp;
4345+
4346+CREATE DATABASE test1;
4347+DROP DATABASE test1;
4348+
4349+delimiter |;
4350+
4351+CREATE PROCEDURE p1()
4352+BEGIN
4353+ SELECT 1;
4354+END|
4355+
4356+DROP PROCEDURE p1|
4357+
4358+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
4359+BEGIN
4360+ RETURN 1;
4361+END|
4362+
4363+DROP FUNCTION f1|
4364+
4365+delimiter ;|
4366+
4367+CREATE VIEW v1 AS SELECT * FROM t_innodb;
4368+DROP VIEW v1;
4369+
4370+INSERT INTO v_innodb VALUES(1);
4371+INSERT INTO v_blackhole VALUES(1);
4372+INSERT INTO v_myisam VALUES(1);
4373+INSERT INTO v_csv VALUES(1);
4374+INSERT INTO v_memory VALUES(1);
4375+INSERT INTO v_archive VALUES(1);
4376+
4377+UNLOCK BINLOG;
4378+
4379+#
4380+# Test incompatibility with delay_key_write=ALL
4381+#
4382+SELECT @@delay_key_write;
4383+
4384+SET GLOBAL delay_key_write=ALL;
4385+--error ER_OPTION_PREVENTS_STATEMENT
4386+LOCK TABLES FOR BACKUP;
4387+
4388+SET GLOBAL delay_key_write=default;
4389+SELECT @@delay_key_write;
4390+
4391+#
4392+# read-only=1 + backup locks
4393+#
4394+
4395+SET GLOBAL read_only=1;
4396+
4397+INSERT INTO t_innodb VALUES(0);
4398+INSERT INTO t_myisam VALUES(0);
4399+
4400+LOCK TABLES FOR BACKUP;
4401+
4402+INSERT INTO t_innodb VALUES(0);
4403+--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
4404+INSERT INTO t_myisam VALUES(0);
4405+
4406+UNLOCK TABLES;
4407+
4408+INSERT INTO t_innodb VALUES(0);
4409+INSERT INTO t_myisam VALUES(0);
4410+
4411+SET GLOBAL read_only=0;
4412+
4413+#
4414+# Test that temporary tables are not affected by backup locks
4415+#
4416+LOCK TABLES FOR BACKUP;
4417+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
4418+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
4419+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
4420+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
4421+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
4422+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
4423+
4424+INSERT INTO tt_innodb VALUES(5);
4425+INSERT INTO tt_myisam VALUES(5);
4426+INSERT INTO tt_memory VALUES(5);
4427+INSERT INTO tt_csv VALUES(5);
4428+INSERT INTO tt_blackhole VALUES(5);
4429+INSERT INTO tt_archive VALUES(5);
4430+
4431+LOCK BINLOG FOR BACKUP;
4432+
4433+START TRANSACTION;
4434+SELECT * FROM tt_archive;
4435+SELECT * FROM tt_blackhole;
4436+SELECT * FROM tt_memory;
4437+SELECT * FROM tt_innodb;
4438+SELECT * FROM tt_myisam;
4439+INSERT INTO tt_innodb VALUES(6);
4440+INSERT INTO tt_myisam VALUES(6);
4441+INSERT INTO tt_memory VALUES(6);
4442+INSERT INTO tt_csv VALUES(6);
4443+INSERT INTO tt_blackhole VALUES(6);
4444+INSERT INTO tt_archive VALUES(6);
4445+COMMIT;
4446+
4447+SELECT * FROM tt_archive;
4448+SELECT * FROM tt_blackhole;
4449+SELECT * FROM tt_memory;
4450+SELECT * FROM tt_innodb;
4451+SELECT * FROM tt_myisam;
4452+
4453+DROP TEMPORARY TABLE tt_innodb;
4454+DROP TEMPORARY TABLE tt_myisam;
4455+DROP TEMPORARY TABLE tt_memory;
4456+DROP TEMPORARY TABLE tt_csv;
4457+DROP TEMPORARY TABLE tt_blackhole;
4458+DROP TEMPORARY TABLE tt_archive;
4459+
4460+UNLOCK BINLOG;
4461+UNLOCK TABLES;
4462+
4463+#
4464+# test that AUTOCOMMIT updates succeed with a BINLOG lock in the same connection with binlog disabled
4465+#
4466+
4467+SELECT @@log_bin;
4468+
4469+LOCK BINLOG FOR BACKUP;
4470+
4471+INSERT INTO t_innodb VALUES(1);
4472+INSERT INTO t_myisam VALUES(1);
4473+
4474+UNLOCK BINLOG;
4475+
4476+#
4477+# Log tables
4478+#
4479+
4480+SET @old_general_log = @@general_log;
4481+SET @old_slow_query_log = @@slow_query_log;
4482+SET @old_log_output = @@log_output;
4483+SET @old_long_query_time = @@SESSION.long_query_time;
4484+
4485+SET SESSION long_query_time = 0;
4486+SET GLOBAL log_output = 'TABLE';
4487+SET GLOBAL general_log = ON;
4488+SET GLOBAL slow_query_log = ON;
4489+
4490+LOCK TABLES FOR BACKUP;
4491+
4492+SELECT 1;
4493+
4494+LOCK BINLOG FOR BACKUP;
4495+
4496+SELECT 1;
4497+
4498+UNLOCK BINLOG;
4499+
4500+UNLOCK TABLES;
4501+
4502+SET SESSION long_query_time = @old_long_query_time;
4503+SET GLOBAL log_output = @old_log_output;
4504+SET GLOBAL slow_query_log = @old_slow_query_log;
4505+SET GLOBAL general_log = @old_general_log;
4506+
4507+SET SESSION lock_wait_timeout=default;
4508+
4509+
4510+--echo #-----------------------------------------------------------------------
4511+--echo # Multi-threaded tests
4512+--echo #-----------------------------------------------------------------------
4513+
4514+--echo # connection default
4515+SET GLOBAL lock_wait_timeout=1;
4516+
4517+CREATE USER user@localhost;
4518+GRANT ALL PRIVILEGES ON test.* TO user@localhost;
4519+
4520+--connect(con1,localhost,root,,)
4521+
4522+--connect(con2,localhost,user,,)
4523+
4524+--connection default
4525+LOCK TABLES FOR BACKUP;
4526+
4527+--connection con1
4528+--echo # connection con1
4529+--error ER_LOCK_WAIT_TIMEOUT
4530+SET GLOBAL delay_key_write=ALL;
4531+
4532+--connection default
4533+--echo # connection default
4534+UNLOCK TABLES;
4535+
4536+#
4537+# read-only=1 + backup locks
4538+#
4539+
4540+--connection con2
4541+--echo # connection con2
4542+
4543+START TRANSACTION;
4544+INSERT INTO t_innodb VALUES(0);
4545+
4546+--connection default
4547+--echo # connection default
4548+
4549+SET GLOBAL read_only=1;
4550+
4551+--connection con2
4552+--echo # connection con2
4553+
4554+--error ER_OPTION_PREVENTS_STATEMENT
4555+COMMIT;
4556+
4557+--error ER_OPTION_PREVENTS_STATEMENT
4558+INSERT INTO t_innodb VALUES(0);
4559+--error ER_OPTION_PREVENTS_STATEMENT
4560+INSERT INTO t_myisam VALUES(0);
4561+
4562+--connection con1
4563+--echo # connection con1
4564+
4565+LOCK TABLES FOR BACKUP;
4566+
4567+--connection con2
4568+--echo # connection con2
4569+
4570+--error ER_OPTION_PREVENTS_STATEMENT
4571+INSERT INTO t_innodb VALUES(0);
4572+--error ER_OPTION_PREVENTS_STATEMENT
4573+INSERT INTO t_myisam VALUES(0);
4574+
4575+--connection default
4576+--echo # connection default
4577+
4578+SET GLOBAL read_only=0;
4579+
4580+--connection con2
4581+--echo # connection con2
4582+INSERT INTO t_innodb VALUES(0);
4583+--error ER_LOCK_WAIT_TIMEOUT
4584+INSERT INTO t_myisam VALUES(0);
4585+
4586+--connection default
4587+--echo # connection default
4588+
4589+SET GLOBAL read_only=1;
4590+
4591+--connection con2
4592+--echo # connection con2
4593+
4594+--error ER_OPTION_PREVENTS_STATEMENT
4595+INSERT INTO t_innodb VALUES(0);
4596+--error ER_OPTION_PREVENTS_STATEMENT
4597+INSERT INTO t_myisam VALUES(0);
4598+
4599+--connection con1
4600+--echo # connection con1
4601+
4602+UNLOCK TABLES;
4603+
4604+--connection con2
4605+--echo # connection con2
4606+
4607+--error ER_OPTION_PREVENTS_STATEMENT
4608+INSERT INTO t_innodb VALUES(0);
4609+--error ER_OPTION_PREVENTS_STATEMENT
4610+INSERT INTO t_myisam VALUES(0);
4611+
4612+--connection default
4613+--echo # connection default
4614+
4615+SET GLOBAL read_only=0;
4616+
4617+#
4618+# Test that temporary tables are not affected by backup locks
4619+#
4620+--connection default
4621+--echo # connection default
4622+LOCK TABLES FOR BACKUP;
4623+
4624+--connection con1
4625+--echo # connection con1
4626+
4627+CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
4628+CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
4629+CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
4630+CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
4631+CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
4632+CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
4633+
4634+INSERT INTO tt_innodb VALUES(5);
4635+INSERT INTO tt_myisam VALUES(5);
4636+INSERT INTO tt_memory VALUES(5);
4637+INSERT INTO tt_csv VALUES(5);
4638+INSERT INTO tt_blackhole VALUES(5);
4639+INSERT INTO tt_archive VALUES(5);
4640+
4641+--connection default
4642+--echo # connection default
4643+LOCK BINLOG FOR BACKUP;
4644+
4645+--connection con1
4646+--echo # connection con1
4647+
4648+START TRANSACTION;
4649+SELECT * FROM tt_archive;
4650+SELECT * FROM tt_blackhole;
4651+SELECT * FROM tt_memory;
4652+SELECT * FROM tt_innodb;
4653+SELECT * FROM tt_myisam;
4654+INSERT INTO tt_innodb VALUES(6);
4655+INSERT INTO tt_myisam VALUES(6);
4656+INSERT INTO tt_memory VALUES(6);
4657+INSERT INTO tt_csv VALUES(6);
4658+INSERT INTO tt_blackhole VALUES(6);
4659+INSERT INTO tt_archive VALUES(6);
4660+COMMIT;
4661+
4662+SELECT * FROM tt_archive;
4663+SELECT * FROM tt_blackhole;
4664+SELECT * FROM tt_memory;
4665+SELECT * FROM tt_innodb;
4666+SELECT * FROM tt_myisam;
4667+
4668+DROP TEMPORARY TABLE tt_innodb;
4669+DROP TEMPORARY TABLE tt_myisam;
4670+DROP TEMPORARY TABLE tt_memory;
4671+DROP TEMPORARY TABLE tt_csv;
4672+DROP TEMPORARY TABLE tt_blackhole;
4673+DROP TEMPORARY TABLE tt_archive;
4674+
4675+--connection default
4676+--echo # connection default
4677+
4678+UNLOCK BINLOG;
4679+UNLOCK TABLES;
4680+
4681+LOCK BINLOG FOR BACKUP;
4682+
4683+--connection con1
4684+--echo # connection con1
4685+
4686+#
4687+# test that AUTOCOMMIT updates are not blocked with a BINLOG lock in another
4688+# connection with binlog disabled
4689+#
4690+
4691+SELECT @@log_bin;
4692+
4693+INSERT INTO t_innodb VALUES(1);
4694+INSERT INTO t_myisam VALUES(1);
4695+
4696+--connection default
4697+--echo # connection default
4698+
4699+UNLOCK BINLOG;
4700+
4701+#
4702+# LTFB + DDL / DML in another connection
4703+#
4704+
4705+--connection default
4706+--echo # connection default
4707+
4708+LOCK TABLES FOR BACKUP;
4709+
4710+--connection con1
4711+--echo # connection con1
4712+
4713+DELETE FROM t_innodb;
4714+INSERT INTO t_innodb VALUES(0);
4715+UPDATE t_innodb SET a = 1;
4716+REPLACE INTO t_innodb VALUES(1);
4717+SELECT * from t_innodb;
4718+HANDLER t_innodb OPEN;
4719+HANDLER t_innodb READ a FIRST;
4720+HANDLER t_innodb CLOSE;
4721+
4722+--error ER_LOCK_WAIT_TIMEOUT
4723+INSERT INTO t_myisam VALUES(0);
4724+--error ER_LOCK_WAIT_TIMEOUT
4725+INSERT DELAYED INTO t_myisam VALUES(0);
4726+SELECT * FROM t_myisam;
4727+HANDLER t_myisam OPEN;
4728+HANDLER t_myisam READ a FIRST;
4729+HANDLER t_myisam CLOSE;
4730+
4731+--error ER_LOCK_WAIT_TIMEOUT
4732+INSERT INTO t_memory VALUES(0);
4733+SELECT * FROM t_memory;
4734+
4735+--error ER_LOCK_WAIT_TIMEOUT
4736+INSERT INTO t_archive VALUES(0);
4737+SELECT * FROM t_archive;
4738+
4739+--error ER_LOCK_WAIT_TIMEOUT
4740+INSERT INTO t_csv VALUES(0);
4741+SELECT * FROM t_csv;
4742+
4743+DELETE FROM t_blackhole;
4744+INSERT INTO t_blackhole VALUES(0);
4745+UPDATE t_blackhole SET a = 1;
4746+REPLACE INTO t_blackhole VALUES(1);
4747+SELECT * FROM t_blackhole;
4748+
4749+--error ER_LOCK_WAIT_TIMEOUT
4750+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
4751+
4752+--error ER_LOCK_WAIT_TIMEOUT
4753+DROP TABLE t_innodb;
4754+
4755+--error ER_LOCK_WAIT_TIMEOUT
4756+DROP TABLE non_existing;
4757+
4758+--error ER_LOCK_WAIT_TIMEOUT
4759+TRUNCATE TABLE t_innodb;
4760+
4761+--error ER_LOCK_WAIT_TIMEOUT
4762+RENAME TABLE t_innodb TO tmp;
4763+
4764+--error ER_LOCK_WAIT_TIMEOUT
4765+ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
4766+
4767+--error ER_LOCK_WAIT_TIMEOUT
4768+ALTER TABLE t_innodb ADD KEY (a);
4769+
4770+--error ER_LOCK_WAIT_TIMEOUT
4771+DROP DATABASE test;
4772+--error ER_LOCK_WAIT_TIMEOUT
4773+CREATE DATABASE test1;
4774+
4775+# The server overrides lock_wait_timeout to 1 year when opening system
4776+# tables. So ER_LOCK_WAIT_TIMEOUT can't be used here.
4777+
4778+--let $id=`SELECT CONNECTION_ID()`
4779+
4780+--send CREATE PROCEDURE p1() SELECT 1
4781+
4782+--connection default
4783+--echo # connection default
4784+
4785+let $wait_condition=
4786+ SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
4787+ WHERE STATE = "Waiting for backup lock" AND
4788+ INFO = "CREATE PROCEDURE p1() SELECT 1";
4789+--source include/wait_condition.inc
4790+
4791+--disable_query_log
4792+--echo KILL QUERY #;
4793+--eval KILL QUERY $id
4794+--enable_query_log
4795+
4796+--connection con1
4797+--echo # connection con1
4798+
4799+--error ER_QUERY_INTERRUPTED
4800+--reap
4801+
4802+--send CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 1
4803+
4804+--connection default
4805+--echo # connection default
4806+
4807+let $wait_condition=
4808+ SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
4809+ WHERE STATE = "Waiting for backup lock" AND
4810+ INFO = "CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 1";
4811+--source include/wait_condition.inc
4812+
4813+--disable_query_log
4814+--echo KILL QUERY #;
4815+--eval KILL QUERY $id
4816+--enable_query_log
4817+
4818+--connection con1
4819+--echo # connection con1
4820+
4821+--error ER_QUERY_INTERRUPTED
4822+--reap
4823+
4824+--error ER_LOCK_WAIT_TIMEOUT
4825+CREATE VIEW v1 AS SELECT * FROM t_innodb;
4826+
4827+INSERT INTO v_innodb VALUES(1);
4828+INSERT INTO v_blackhole VALUES(1);
4829+--error ER_LOCK_WAIT_TIMEOUT
4830+INSERT INTO v_myisam VALUES(1);
4831+
4832+--connection default
4833+--echo # connection default
4834+
4835+UNLOCK TABLES;
4836+
4837+#
4838+# LBFB + DML/DDL in another connection (and binlog disabled)
4839+#
4840+
4841+LOCK BINLOG FOR BACKUP;
4842+
4843+--connection con1
4844+--echo # connection con1
4845+
4846+DELETE FROM t_innodb;
4847+INSERT INTO t_innodb VALUES(0);
4848+UPDATE t_innodb SET a = 1;
4849+REPLACE INTO t_innodb VALUES(1);
4850+SELECT * from t_innodb;
4851+HANDLER t_innodb OPEN;
4852+HANDLER t_innodb READ a FIRST;
4853+HANDLER t_innodb CLOSE;
4854+
4855+DELETE FROM t_myisam;
4856+INSERT INTO t_myisam VALUES(0);
4857+UPDATE t_myisam SET a = 1;
4858+REPLACE INTO t_myisam VALUES(1);
4859+SELECT * from t_myisam;
4860+HANDLER t_myisam OPEN;
4861+HANDLER t_myisam READ a FIRST;
4862+HANDLER t_myisam CLOSE;
4863+
4864+DELETE FROM t_memory;
4865+INSERT INTO t_memory VALUES(0);
4866+UPDATE t_memory SET a = 1;
4867+REPLACE INTO t_memory VALUES(1);
4868+SELECT * from t_memory;
4869+
4870+INSERT INTO t_archive VALUES(0);
4871+SELECT * from t_archive;
4872+
4873+DELETE FROM t_csv;
4874+INSERT INTO t_csv VALUES(0);
4875+UPDATE t_csv SET a = 1;
4876+REPLACE INTO t_csv VALUES(1);
4877+SELECT * from t_csv;
4878+
4879+DELETE FROM t_blackhole;
4880+INSERT INTO t_blackhole VALUES(0);
4881+UPDATE t_blackhole SET a = 1;
4882+REPLACE INTO t_blackhole VALUES(1);
4883+SELECT * from t_blackhole;
4884+
4885+CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
4886+CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
4887+CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
4888+CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
4889+CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
4890+CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;
4891+
4892+DROP TABLE tt_innodb;
4893+DROP TABLE tt_myisam;
4894+DROP TABLE tt_memory;
4895+DROP TABLE tt_csv;
4896+DROP TABLE tt_blackhole;
4897+DROP TABLE tt_archive;
4898+
4899+--error ER_BAD_TABLE_ERROR
4900+DROP TABLE non_existing;
4901+
4902+TRUNCATE TABLE t_innodb;
4903+TRUNCATE TABLE t_myisam;
4904+TRUNCATE TABLE t_memory;
4905+TRUNCATE TABLE t_csv;
4906+TRUNCATE TABLE t_blackhole;
4907+
4908+RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
4909+RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
4910+RENAME TABLE t_memory TO tmp, tmp TO t_memory;
4911+RENAME TABLE t_csv TO tmp, tmp TO t_csv;
4912+RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
4913+RENAME TABLE t_archive TO tmp, tmp TO t_archive;
4914+
4915+ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
4916+ALTER TABLE t_innodb DROP COLUMN b;
4917+ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
4918+ALTER TABLE t_myisam DROP COLUMN b;
4919+ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
4920+ALTER TABLE t_memory DROP COLUMN b;
4921+ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
4922+ALTER TABLE t_csv DROP COLUMN b;
4923+ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
4924+ALTER TABLE t_blackhole DROP COLUMN b;
4925+ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
4926+ALTER TABLE t_archive DROP COLUMN b;
4927+
4928+ALTER TABLE t_innodb ADD KEY tmp (a);
4929+ALTER TABLE t_innodb DROP KEY tmp;
4930+ALTER TABLE t_myisam ADD KEY tmp (a);
4931+ALTER TABLE t_myisam DROP KEY tmp;
4932+ALTER TABLE t_memory ADD KEY tmp (a);
4933+ALTER TABLE t_memory DROP KEY tmp;
4934+ALTER TABLE t_blackhole ADD KEY tmp (a);
4935+ALTER TABLE t_blackhole DROP KEY tmp;
4936+
4937+CREATE DATABASE test1;
4938+DROP DATABASE test1;
4939+
4940+delimiter |;
4941+
4942+CREATE PROCEDURE p1()
4943+BEGIN
4944+ SELECT 1;
4945+END|
4946+
4947+DROP PROCEDURE p1|
4948+
4949+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
4950+BEGIN
4951+ RETURN 1;
4952+END|
4953+
4954+DROP FUNCTION f1|
4955+
4956+delimiter ;|
4957+
4958+CREATE VIEW v1 AS SELECT * FROM t_innodb;
4959+DROP VIEW v1;
4960+
4961+INSERT INTO v_innodb VALUES(1);
4962+INSERT INTO v_blackhole VALUES(1);
4963+INSERT INTO v_myisam VALUES(1);
4964+INSERT INTO v_csv VALUES(1);
4965+INSERT INTO v_memory VALUES(1);
4966+INSERT INTO v_archive VALUES(1);
4967+
4968+--connection default
4969+--echo # connection default
4970+
4971+UNLOCK BINLOG;
4972+
4973+#
4974+# Log tables
4975+#
4976+
4977+SET @old_general_log = @@general_log;
4978+SET @old_slow_query_log = @@slow_query_log;
4979+SET @old_log_output = @@log_output;
4980+
4981+SET GLOBAL log_output = 'TABLE';
4982+SET GLOBAL general_log = ON;
4983+SET GLOBAL slow_query_log = ON;
4984+
4985+LOCK TABLES FOR BACKUP;
4986+
4987+--connection con1
4988+--echo # connection con1
4989+
4990+SET @old_long_query_time = @@SESSION.long_query_time;
4991+SET SESSION long_query_time = 0;
4992+
4993+SELECT 1;
4994+
4995+--connection default
4996+--echo # connection default
4997+
4998+LOCK BINLOG FOR BACKUP;
4999+
5000+SELECT 1;
The diff has been truncated for viewing.

Subscribers

People subscribed via source and target branches