/usr/lib/mysql-testsuite/r/backup

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