percona-server-test-5.6 5.6.22-rel71.0-0ubuntu4 / usr / lib / mysql-testsuite / t / backup_locks.test

########################################################################
# Tests for LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP
########################################################################

--source include/have_innodb.inc
--source include/not_embedded.inc
--source include/have_archive.inc
--source include/have_csv.inc
--source include/have_blackhole.inc

SHOW VARIABLES LIKE 'have_backup_locks';

--echo #-----------------------------------------------------------------------
--echo # Setup
--echo #-----------------------------------------------------------------------

# Save the initial number of concurrent sessions.
--source include/count_sessions.inc

CREATE TABLE t_innodb(a INT NOT NULL, KEY (a)) ENGINE=InnoDB;
CREATE TABLE t_myisam(a INT NOT NULL, KEY (a)) ENGINE=MyISAM;
CREATE TABLE t_memory(a INT NOT NULL, KEY (a)) ENGINE=MEMORY;
CREATE TABLE t_csv(a INT NOT NULL) ENGINE=CSV;
CREATE TABLE t_blackhole(a INT NOT NULL, KEY (a)) ENGINE=BLACKHOLE;
CREATE TABLE t_archive(a INT NOT NULL) ENGINE=ARCHIVE;

CREATE VIEW v_innodb AS SELECT * FROM t_innodb;
CREATE VIEW v_myisam AS SELECT * FROM t_myisam;
CREATE VIEW v_memory AS SELECT * FROM t_memory;
CREATE VIEW v_csv AS SELECT * FROM t_csv;
CREATE VIEW v_blackhole AS SELECT * FROM t_blackhole;
CREATE VIEW v_archive AS SELECT * FROM t_archive;

--echo #-----------------------------------------------------------------------
--echo # Basic syntax checks
--echo #-----------------------------------------------------------------------

--error ER_PARSE_ERROR
LOCK TABLES t_innodb FOR BACKUP;
--error ER_PARSE_ERROR
LOCK TABLES t_innodb READ FOR BACKUP;
--error ER_PARSE_ERROR
LOCK TABLES t_innodb FOR BACKUP READ;
--error ER_PARSE_ERROR
LOCK BINLOG t_innodb FOR BACKUP;
--error ER_PARSE_ERROR
LOCK BINLOG t_innodb READ FOR BACKUP;
--error ER_PARSE_ERROR
LOCK BINLOG t_innodb FOR BACKUP READ;

LOCK TABLES FOR BACKUP;
UNLOCK TABLES;

LOCK BINLOG FOR BACKUP;
UNLOCK BINLOG;

--echo #-----------------------------------------------------------------------
--echo # No backup locks are allowed in stored routines
--echo #-----------------------------------------------------------------------

delimiter |;
--error ER_SP_BADSTATEMENT
CREATE PROCEDURE p1()
BEGIN
  LOCK TABLES FOR BACKUP;
END|

--error ER_SP_BADSTATEMENT
CREATE PROCEDURE p2()
BEGIN
  LOCK BINLOG FOR BACKUP;
END|

--error ER_SP_BADSTATEMENT
CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
BEGIN
  LOCK TABLES FOR BACKUP;
  RETURN 1;
END|

--error ER_SP_BADSTATEMENT
CREATE FUNCTION f2() RETURNS INT DETERMINISTIC
BEGIN
  LOCK BINLOG FOR BACKUP;
  RETURN 1;
END|

delimiter ;|

--echo #-----------------------------------------------------------------------
--echo # Single-threaded tests
--echo #-----------------------------------------------------------------------

SET SESSION lock_wait_timeout=1;

FLUSH STATUS;

#
# Multiple TFB locks in the same connection
#
LOCK TABLES FOR BACKUP;
LOCK TABLES FOR BACKUP;
SHOW STATUS LIKE 'Com_lock_tables_for_backup';

UNLOCK TABLES;
SHOW STATUS LIKE 'Com_unlock_tables';

#
# Multiple BFB locks in the same connection
#
LOCK BINLOG FOR BACKUP;
LOCK BINLOG FOR BACKUP;

SHOW STATUS LIKE 'Com_lock_binlog_for_backup';

UNLOCK BINLOG;
SHOW STATUS LIKE 'Com_unlock_binlog';

#
# LTFB + DDL / DML in the same connection
#

LOCK TABLES FOR BACKUP;

DELETE FROM t_innodb;
INSERT INTO t_innodb VALUES(0);
UPDATE t_innodb SET a = 1;
REPLACE INTO t_innodb VALUES(1);
SELECT * FROM t_innodb;
HANDLER t_innodb OPEN;
HANDLER t_innodb READ a FIRST;
HANDLER t_innodb CLOSE;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DELETE FROM t_myisam;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT INTO t_myisam VALUES(0);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT DELAYED INTO t_myisam VALUES(0);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
UPDATE t_myisam SET a = 1;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
REPLACE INTO t_myisam VALUES(1);
SELECT * FROM t_myisam;
HANDLER t_myisam OPEN;
HANDLER t_myisam READ a FIRST;
HANDLER t_myisam CLOSE;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DELETE FROM t_memory;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT INTO t_memory VALUES(0);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT DELAYED INTO t_memory VALUES(0);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
UPDATE t_memory SET a = 1;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
REPLACE INTO t_memory VALUES(1);
SELECT * FROM t_memory;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DELETE FROM t_archive;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT INTO t_archive VALUES(0);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT DELAYED INTO t_archive VALUES(0);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
UPDATE t_archive SET a = 1;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
REPLACE INTO t_archive VALUES(1);
SELECT * FROM t_archive;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DELETE FROM t_csv;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT INTO t_csv VALUES(0);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
UPDATE t_csv SET a = 1;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
REPLACE INTO t_csv VALUES(1);
SELECT * FROM t_csv;

DELETE FROM t_blackhole;
INSERT INTO t_blackhole VALUES(0);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT DELAYED INTO t_blackhole VALUES(0);
UPDATE t_blackhole SET a = 1;
REPLACE INTO t_blackhole VALUES(1);
SELECT * FROM t_blackhole;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DROP TABLE t_innodb;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DROP TABLE t_myisam;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DROP TABLE t_memory;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DROP TABLE t_csv;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DROP TABLE t_blackhole;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DROP TABLE t_archive;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DROP TABLE non_existing;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
TRUNCATE TABLE t_innodb;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
TRUNCATE TABLE t_myisam;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
TRUNCATE TABLE t_memory;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
TRUNCATE TABLE t_csv;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
TRUNCATE TABLE t_blackhole;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
TRUNCATE TABLE t_archive;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
RENAME TABLE t_innodb TO tmp;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
RENAME TABLE t_myisam TO tmp;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
RENAME TABLE t_memory TO tmp;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
RENAME TABLE t_csv TO tmp;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
RENAME TABLE t_blackhole TO tmp;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
RENAME TABLE t_archive TO tmp;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_innodb ADD COLUMN b CHAR(10);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_myisam ADD COLUMN b CHAR(10);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_memory ADD COLUMN b CHAR(10);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_csv ADD COLUMN b CHAR(10);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_blackhole ADD COLUMN b CHAR(10);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_archive ADD COLUMN b CHAR(10);

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_innodb ADD KEY (a);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_myisam ADD KEY (a);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_memory ADD KEY (a);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_csv ADD KEY (a);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_blackhole ADD KEY (a);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
ALTER TABLE t_archive ADD KEY (a);

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
DROP DATABASE test;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE DATABASE test1;

delimiter |;
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE PROCEDURE p1()
BEGIN
  SELECT 1;
END|

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
BEGIN
  RETURN 1;
END|

delimiter ;|

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
CREATE VIEW v1 AS SELECT * FROM t_innodb;

INSERT INTO v_innodb VALUES(1);
INSERT INTO v_blackhole VALUES(1);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT INTO v_myisam VALUES(1);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT INTO v_csv VALUES(1);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT INTO v_memory VALUES(1);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT INTO v_archive VALUES(1);

UNLOCK TABLES;

#
# LBFB + DML/DDL in the same connection (and binlog disabled)
#

LOCK BINLOG FOR BACKUP;

DELETE FROM t_innodb;
INSERT INTO t_innodb VALUES(0);
UPDATE t_innodb SET a = 1;
REPLACE INTO t_innodb VALUES(1);
SELECT * from t_innodb;
HANDLER t_innodb OPEN;
HANDLER t_innodb READ a FIRST;
HANDLER t_innodb CLOSE;

DELETE FROM t_myisam;
INSERT INTO t_myisam VALUES(0);
UPDATE t_myisam SET a = 1;
REPLACE INTO t_myisam VALUES(1);
SELECT * from t_myisam;
HANDLER t_myisam OPEN;
HANDLER t_myisam READ a FIRST;
HANDLER t_myisam CLOSE;

DELETE FROM t_memory;
INSERT INTO t_memory VALUES(0);
UPDATE t_memory SET a = 1;
REPLACE INTO t_memory VALUES(1);
SELECT * from t_memory;

DELETE FROM t_archive;
INSERT INTO t_archive VALUES(0);
SELECT * from t_archive;

DELETE FROM t_csv;
INSERT INTO t_csv VALUES(0);
UPDATE t_csv SET a = 1;
REPLACE INTO t_csv VALUES(1);
SELECT * from t_csv;

DELETE FROM t_blackhole;
INSERT INTO t_blackhole VALUES(0);
UPDATE t_blackhole SET a = 1;
REPLACE INTO t_blackhole VALUES(1);
SELECT * from t_blackhole;

CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;

DROP TABLE tt_innodb;
DROP TABLE tt_myisam;
DROP TABLE tt_memory;
DROP TABLE tt_csv;
DROP TABLE tt_blackhole;
DROP TABLE tt_archive;

--error ER_BAD_TABLE_ERROR
DROP TABLE non_existing;

TRUNCATE TABLE t_innodb;
TRUNCATE TABLE t_myisam;
TRUNCATE TABLE t_memory;
TRUNCATE TABLE t_csv;
TRUNCATE TABLE t_blackhole;

RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
RENAME TABLE t_memory TO tmp, tmp TO t_memory;
RENAME TABLE t_csv TO tmp, tmp TO t_csv;
RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
RENAME TABLE t_archive TO tmp, tmp TO t_archive;

ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_innodb DROP COLUMN b;
ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_myisam DROP COLUMN b;
ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_memory DROP COLUMN b;
ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_csv DROP COLUMN b;
ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_blackhole DROP COLUMN b;
ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_archive DROP COLUMN b;

ALTER TABLE t_innodb ADD KEY tmp (a);
ALTER TABLE t_innodb DROP KEY tmp;
ALTER TABLE t_myisam ADD KEY tmp (a);
ALTER TABLE t_myisam DROP KEY tmp;
ALTER TABLE t_memory ADD KEY tmp (a);
ALTER TABLE t_memory DROP KEY tmp;
ALTER TABLE t_blackhole ADD KEY tmp (a);
ALTER TABLE t_blackhole DROP KEY tmp;

CREATE DATABASE test1;
DROP DATABASE test1;

delimiter |;

CREATE PROCEDURE p1()
BEGIN
  SELECT 1;
END|

DROP PROCEDURE p1|

CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
BEGIN
  RETURN 1;
END|

DROP FUNCTION f1|

delimiter ;|

CREATE VIEW v1 AS SELECT * FROM t_innodb;
DROP VIEW v1;

INSERT INTO v_innodb VALUES(1);
INSERT INTO v_blackhole VALUES(1);
INSERT INTO v_myisam VALUES(1);
INSERT INTO v_csv VALUES(1);
INSERT INTO v_memory VALUES(1);
INSERT INTO v_archive VALUES(1);

UNLOCK BINLOG;

#
# Test incompatibility with delay_key_write=ALL
#
SELECT @@delay_key_write;

SET GLOBAL delay_key_write=ALL;
--error ER_OPTION_PREVENTS_STATEMENT
LOCK TABLES FOR BACKUP;

SET GLOBAL delay_key_write=default;
SELECT @@delay_key_write;

#
# read-only=1 + backup locks
#

SET GLOBAL read_only=1;

INSERT INTO t_innodb VALUES(0);
INSERT INTO t_myisam VALUES(0);

LOCK TABLES FOR BACKUP;

INSERT INTO t_innodb VALUES(0);
--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
INSERT INTO t_myisam VALUES(0);

UNLOCK TABLES;

INSERT INTO t_innodb VALUES(0);
INSERT INTO t_myisam VALUES(0);

SET GLOBAL read_only=0;

#
# Test that temporary tables are not affected by backup locks
#
LOCK TABLES FOR BACKUP;
CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;

INSERT INTO tt_innodb VALUES(5);
INSERT INTO tt_myisam VALUES(5);
INSERT INTO tt_memory VALUES(5);
INSERT INTO tt_csv VALUES(5);
INSERT INTO tt_blackhole VALUES(5);
INSERT INTO tt_archive VALUES(5);

LOCK BINLOG FOR BACKUP;

START TRANSACTION;
SELECT * FROM tt_archive;
SELECT * FROM tt_blackhole;
SELECT * FROM tt_memory;
SELECT * FROM tt_innodb;
SELECT * FROM tt_myisam;
INSERT INTO tt_innodb VALUES(6);
INSERT INTO tt_myisam VALUES(6);
INSERT INTO tt_memory VALUES(6);
INSERT INTO tt_csv VALUES(6);
INSERT INTO tt_blackhole VALUES(6);
INSERT INTO tt_archive VALUES(6);
COMMIT;

SELECT * FROM tt_archive;
SELECT * FROM tt_blackhole;
SELECT * FROM tt_memory;
SELECT * FROM tt_innodb;
SELECT * FROM tt_myisam;

DROP TEMPORARY TABLE tt_innodb;
DROP TEMPORARY TABLE tt_myisam;
DROP TEMPORARY TABLE tt_memory;
DROP TEMPORARY TABLE tt_csv;
DROP TEMPORARY TABLE tt_blackhole;
DROP TEMPORARY TABLE tt_archive;

UNLOCK BINLOG;
UNLOCK TABLES;

#
# test that AUTOCOMMIT updates succeed with a BINLOG lock in the same connection with binlog disabled
#

SELECT @@log_bin;

LOCK BINLOG FOR BACKUP;

INSERT INTO t_innodb VALUES(1);
INSERT INTO t_myisam VALUES(1);

UNLOCK BINLOG;

#
# Log tables
#

SET @old_general_log = @@general_log;
SET @old_slow_query_log = @@slow_query_log;
SET @old_log_output = @@log_output;
SET @old_long_query_time = @@SESSION.long_query_time;

SET SESSION long_query_time = 0;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = ON;
SET GLOBAL slow_query_log = ON;

LOCK TABLES FOR BACKUP;

SELECT 1;

LOCK BINLOG FOR BACKUP;

SELECT 1;

UNLOCK BINLOG;

UNLOCK TABLES;

SET SESSION long_query_time = @old_long_query_time;
SET GLOBAL log_output = @old_log_output;
SET GLOBAL slow_query_log = @old_slow_query_log;
SET GLOBAL general_log = @old_general_log;

SET SESSION lock_wait_timeout=default;


--echo #-----------------------------------------------------------------------
--echo # Multi-threaded tests
--echo #-----------------------------------------------------------------------

--echo # connection default
SET GLOBAL lock_wait_timeout=1;

CREATE USER user@localhost;
GRANT ALL PRIVILEGES ON test.* TO user@localhost;

--connect(con1,localhost,root,,)

--connect(con2,localhost,user,,)

--connection default
LOCK TABLES FOR BACKUP;

--connection con1
--echo # connection con1
--error ER_LOCK_WAIT_TIMEOUT
SET GLOBAL delay_key_write=ALL;

--connection default
--echo # connection default
UNLOCK TABLES;

#
# read-only=1 + backup locks
#

--connection con2
--echo # connection con2

START TRANSACTION;
INSERT INTO t_innodb VALUES(0);

--connection default
--echo # connection default

SET GLOBAL read_only=1;

--connection con2
--echo # connection con2

--error ER_OPTION_PREVENTS_STATEMENT
COMMIT;

--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO t_innodb VALUES(0);
--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO t_myisam VALUES(0);

--connection con1
--echo # connection con1

LOCK TABLES FOR BACKUP;

--connection con2
--echo # connection con2

--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO t_innodb VALUES(0);
--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO t_myisam VALUES(0);

--connection default
--echo # connection default

SET GLOBAL read_only=0;

--connection con2
--echo # connection con2
INSERT INTO t_innodb VALUES(0);
--error ER_LOCK_WAIT_TIMEOUT
INSERT INTO t_myisam VALUES(0);

--connection default
--echo # connection default

SET GLOBAL read_only=1;

--connection con2
--echo # connection con2

--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO t_innodb VALUES(0);
--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO t_myisam VALUES(0);

--connection con1
--echo # connection con1

UNLOCK TABLES;

--connection con2
--echo # connection con2

--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO t_innodb VALUES(0);
--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO t_myisam VALUES(0);

--connection default
--echo # connection default

SET GLOBAL read_only=0;

#
# Test that temporary tables are not affected by backup locks
#
--connection default
--echo # connection default
LOCK TABLES FOR BACKUP;

--connection con1
--echo # connection con1

CREATE TEMPORARY TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
CREATE TEMPORARY TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
CREATE TEMPORARY TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
CREATE TEMPORARY TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
CREATE TEMPORARY TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
CREATE TEMPORARY TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;

INSERT INTO tt_innodb VALUES(5);
INSERT INTO tt_myisam VALUES(5);
INSERT INTO tt_memory VALUES(5);
INSERT INTO tt_csv VALUES(5);
INSERT INTO tt_blackhole VALUES(5);
INSERT INTO tt_archive VALUES(5);

--connection default
--echo # connection default
LOCK BINLOG FOR BACKUP;

--connection con1
--echo # connection con1

START TRANSACTION;
SELECT * FROM tt_archive;
SELECT * FROM tt_blackhole;
SELECT * FROM tt_memory;
SELECT * FROM tt_innodb;
SELECT * FROM tt_myisam;
INSERT INTO tt_innodb VALUES(6);
INSERT INTO tt_myisam VALUES(6);
INSERT INTO tt_memory VALUES(6);
INSERT INTO tt_csv VALUES(6);
INSERT INTO tt_blackhole VALUES(6);
INSERT INTO tt_archive VALUES(6);
COMMIT;

SELECT * FROM tt_archive;
SELECT * FROM tt_blackhole;
SELECT * FROM tt_memory;
SELECT * FROM tt_innodb;
SELECT * FROM tt_myisam;

DROP TEMPORARY TABLE tt_innodb;
DROP TEMPORARY TABLE tt_myisam;
DROP TEMPORARY TABLE tt_memory;
DROP TEMPORARY TABLE tt_csv;
DROP TEMPORARY TABLE tt_blackhole;
DROP TEMPORARY TABLE tt_archive;

--connection default
--echo # connection default

UNLOCK BINLOG;
UNLOCK TABLES;

LOCK BINLOG FOR BACKUP;

--connection con1
--echo # connection con1

#
# test that AUTOCOMMIT updates are not blocked with a BINLOG lock in another
# connection with binlog disabled
#

SELECT @@log_bin;

INSERT INTO t_innodb VALUES(1);
INSERT INTO t_myisam VALUES(1);

--connection default
--echo # connection default

UNLOCK BINLOG;

#
# LTFB + DDL / DML in another connection
#

--connection default
--echo # connection default

LOCK TABLES FOR BACKUP;

--connection con1
--echo # connection con1

DELETE FROM t_innodb;
INSERT INTO t_innodb VALUES(0);
UPDATE t_innodb SET a = 1;
REPLACE INTO t_innodb VALUES(1);
SELECT * from t_innodb;
HANDLER t_innodb OPEN;
HANDLER t_innodb READ a FIRST;
HANDLER t_innodb CLOSE;

--error ER_LOCK_WAIT_TIMEOUT
INSERT INTO t_myisam VALUES(0);
--error ER_LOCK_WAIT_TIMEOUT
INSERT DELAYED INTO t_myisam VALUES(0);
SELECT * FROM t_myisam;
HANDLER t_myisam OPEN;
HANDLER t_myisam READ a FIRST;
HANDLER t_myisam CLOSE;

--error ER_LOCK_WAIT_TIMEOUT
INSERT INTO t_memory VALUES(0);
SELECT * FROM t_memory;

--error ER_LOCK_WAIT_TIMEOUT
INSERT INTO t_archive VALUES(0);
SELECT * FROM t_archive;

--error ER_LOCK_WAIT_TIMEOUT
INSERT INTO t_csv VALUES(0);
SELECT * FROM t_csv;

DELETE FROM t_blackhole;
INSERT INTO t_blackhole VALUES(0);
UPDATE t_blackhole SET a = 1;
REPLACE INTO t_blackhole VALUES(1);
SELECT * FROM t_blackhole;

--error ER_LOCK_WAIT_TIMEOUT
CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;

--error ER_LOCK_WAIT_TIMEOUT
DROP TABLE t_innodb;

--error ER_LOCK_WAIT_TIMEOUT
DROP TABLE non_existing;

--error ER_LOCK_WAIT_TIMEOUT
TRUNCATE TABLE t_innodb;

--error ER_LOCK_WAIT_TIMEOUT
RENAME TABLE t_innodb TO tmp;

--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE t_innodb ADD COLUMN b CHAR(10);

--error ER_LOCK_WAIT_TIMEOUT
ALTER TABLE t_innodb ADD KEY (a);

--error ER_LOCK_WAIT_TIMEOUT
DROP DATABASE test;
--error ER_LOCK_WAIT_TIMEOUT
CREATE DATABASE test1;

# The server overrides lock_wait_timeout to 1 year when opening system
# tables. So ER_LOCK_WAIT_TIMEOUT can't be used here.

--let $id=`SELECT CONNECTION_ID()`

--send CREATE PROCEDURE p1() SELECT 1

--connection default
--echo # connection default

let $wait_condition=
    SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
    WHERE STATE = "Waiting for backup lock" AND
    INFO = "CREATE PROCEDURE p1() SELECT 1";
--source include/wait_condition.inc

--disable_query_log
--echo KILL QUERY #;
--eval KILL QUERY $id
--enable_query_log

--connection con1
--echo # connection con1

--error ER_QUERY_INTERRUPTED
--reap

--send CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 1

--connection default
--echo # connection default

let $wait_condition=
    SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
    WHERE STATE = "Waiting for backup lock" AND
    INFO = "CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 1";
--source include/wait_condition.inc

--disable_query_log
--echo KILL QUERY #;
--eval KILL QUERY $id
--enable_query_log

--connection con1
--echo # connection con1

--error ER_QUERY_INTERRUPTED
--reap

--error ER_LOCK_WAIT_TIMEOUT
CREATE VIEW v1 AS SELECT * FROM t_innodb;

INSERT INTO v_innodb VALUES(1);
INSERT INTO v_blackhole VALUES(1);
--error ER_LOCK_WAIT_TIMEOUT
INSERT INTO v_myisam VALUES(1);

--connection default
--echo # connection default

UNLOCK TABLES;

#
# LBFB + DML/DDL in another connection (and binlog disabled)
#

LOCK BINLOG FOR BACKUP;

--connection con1
--echo # connection con1

DELETE FROM t_innodb;
INSERT INTO t_innodb VALUES(0);
UPDATE t_innodb SET a = 1;
REPLACE INTO t_innodb VALUES(1);
SELECT * from t_innodb;
HANDLER t_innodb OPEN;
HANDLER t_innodb READ a FIRST;
HANDLER t_innodb CLOSE;

DELETE FROM t_myisam;
INSERT INTO t_myisam VALUES(0);
UPDATE t_myisam SET a = 1;
REPLACE INTO t_myisam VALUES(1);
SELECT * from t_myisam;
HANDLER t_myisam OPEN;
HANDLER t_myisam READ a FIRST;
HANDLER t_myisam CLOSE;

DELETE FROM t_memory;
INSERT INTO t_memory VALUES(0);
UPDATE t_memory SET a = 1;
REPLACE INTO t_memory VALUES(1);
SELECT * from t_memory;

INSERT INTO t_archive VALUES(0);
SELECT * from t_archive;

DELETE FROM t_csv;
INSERT INTO t_csv VALUES(0);
UPDATE t_csv SET a = 1;
REPLACE INTO t_csv VALUES(1);
SELECT * from t_csv;

DELETE FROM t_blackhole;
INSERT INTO t_blackhole VALUES(0);
UPDATE t_blackhole SET a = 1;
REPLACE INTO t_blackhole VALUES(1);
SELECT * from t_blackhole;

CREATE TABLE tt_innodb(a INT NOT NULL) ENGINE=InnoDB;
CREATE TABLE tt_myisam(a INT NOT NULL) ENGINE=MyISAM;
CREATE TABLE tt_memory(a INT NOT NULL) ENGINE=MEMORY;
CREATE TABLE tt_csv(a INT NOT NULL) ENGINE=CSV;
CREATE TABLE tt_blackhole(a INT NOT NULL) ENGINE=BLACKHOLE;
CREATE TABLE tt_archive(a INT NOT NULL) ENGINE=ARCHIVE;

DROP TABLE tt_innodb;
DROP TABLE tt_myisam;
DROP TABLE tt_memory;
DROP TABLE tt_csv;
DROP TABLE tt_blackhole;
DROP TABLE tt_archive;

--error ER_BAD_TABLE_ERROR
DROP TABLE non_existing;

TRUNCATE TABLE t_innodb;
TRUNCATE TABLE t_myisam;
TRUNCATE TABLE t_memory;
TRUNCATE TABLE t_csv;
TRUNCATE TABLE t_blackhole;

RENAME TABLE t_innodb TO tmp, tmp TO t_innodb;
RENAME TABLE t_myisam TO tmp, tmp TO t_myisam;
RENAME TABLE t_memory TO tmp, tmp TO t_memory;
RENAME TABLE t_csv TO tmp, tmp TO t_csv;
RENAME TABLE t_blackhole TO tmp, tmp TO t_blackhole;
RENAME TABLE t_archive TO tmp, tmp TO t_archive;

ALTER TABLE t_innodb ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_innodb DROP COLUMN b;
ALTER TABLE t_myisam ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_myisam DROP COLUMN b;
ALTER TABLE t_memory ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_memory DROP COLUMN b;
ALTER TABLE t_csv ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_csv DROP COLUMN b;
ALTER TABLE t_blackhole ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_blackhole DROP COLUMN b;
ALTER TABLE t_archive ADD COLUMN b CHAR(10) NOT NULL;
ALTER TABLE t_archive DROP COLUMN b;

ALTER TABLE t_innodb ADD KEY tmp (a);
ALTER TABLE t_innodb DROP KEY tmp;
ALTER TABLE t_myisam ADD KEY tmp (a);
ALTER TABLE t_myisam DROP KEY tmp;
ALTER TABLE t_memory ADD KEY tmp (a);
ALTER TABLE t_memory DROP KEY tmp;
ALTER TABLE t_blackhole ADD KEY tmp (a);
ALTER TABLE t_blackhole DROP KEY tmp;

CREATE DATABASE test1;
DROP DATABASE test1;

delimiter |;

CREATE PROCEDURE p1()
BEGIN
  SELECT 1;
END|

DROP PROCEDURE p1|

CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
BEGIN
  RETURN 1;
END|

DROP FUNCTION f1|

delimiter ;|

CREATE VIEW v1 AS SELECT * FROM t_innodb;
DROP VIEW v1;

INSERT INTO v_innodb VALUES(1);
INSERT INTO v_blackhole VALUES(1);
INSERT INTO v_myisam VALUES(1);
INSERT INTO v_csv VALUES(1);
INSERT INTO v_memory VALUES(1);
INSERT INTO v_archive VALUES(1);

--connection default
--echo # connection default

UNLOCK BINLOG;

#
# Log tables
#

SET @old_general_log = @@general_log;
SET @old_slow_query_log = @@slow_query_log;
SET @old_log_output = @@log_output;

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = ON;
SET GLOBAL slow_query_log = ON;

LOCK TABLES FOR BACKUP;

--connection con1
--echo # connection con1

SET @old_long_query_time = @@SESSION.long_query_time;
SET SESSION long_query_time = 0;

SELECT 1;

--connection default
--echo # connection default

LOCK BINLOG FOR BACKUP;

SELECT 1;

--connection con1
--echo # connection con1

SET SESSION long_query_time = @old_long_query_time;

--connection default
--echo # connection default

UNLOCK BINLOG;

UNLOCK TABLES;

SET GLOBAL log_output = @old_log_output;
SET GLOBAL slow_query_log = @old_slow_query_log;
SET GLOBAL general_log = @old_general_log;

--echo ########################################################################
--echo # Bug #1360064: LOCK TABLES FOR BACKUP should be incompatible with LOCK
--echo #               TABLES, FLUSH TABLES ... WITH READ LOCK and FLUSH TABLES
--echo #               ... FOR EXPORT in the same connection
--echo ########################################################################

CREATE TABLE t1(a INT) ENGINE=InnoDB;

LOCK TABLES t1 READ;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
LOCK TABLES FOR BACKUP;

UNLOCK TABLES;

LOCK TABLES t1 WRITE;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
LOCK TABLES FOR BACKUP;

UNLOCK TABLES;

FLUSH TABLES t1 FOR EXPORT;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
LOCK TABLES FOR BACKUP;

UNLOCK TABLES;

FLUSH TABLES t1 WITH READ LOCK;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
LOCK TABLES FOR BACKUP;

UNLOCK TABLES;

LOCK TABLES FOR BACKUP;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
LOCK TABLE t1 READ;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
LOCK TABLE t1 WRITE;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
FLUSH TABLES t1 FOR EXPORT;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
FLUSH TABLES t1 WITH READ LOCK;

--error ER_CANT_EXECUTE_WITH_BACKUP_LOCK
FLUSH TABLES WITH READ LOCK;

UNLOCK TABLES;

DROP TABLE t1;

--echo #-----------------------------------------------------------------------
--echo # Cleanup
--echo #-----------------------------------------------------------------------

SET GLOBAL lock_wait_timeout=default;
DROP USER user@localhost;

--disconnect con1
--disconnect con2

--source include/wait_until_count_sessions.inc

DROP VIEW v_innodb, v_myisam, v_memory, v_csv, v_blackhole, v_archive;
DROP TABLE t_innodb, t_myisam, t_memory, t_csv, t_blackhole, t_archive;