mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
0706c01b88
don't disable query/result log unless the output is unstable. and even then don't, but replace away unstable parts.
227 lines
7.3 KiB
Text
227 lines
7.3 KiB
Text
SET default_storage_engine=InnoDB;
|
||
CREATE TABLE t_min (
|
||
c01 TINYINT,
|
||
c02 TINYINT UNSIGNED,
|
||
c03 SMALLINT,
|
||
c04 SMALLINT UNSIGNED,
|
||
c05 MEDIUMINT,
|
||
c06 MEDIUMINT UNSIGNED,
|
||
c07 INT,
|
||
c08 INT UNSIGNED,
|
||
c09 BIGINT,
|
||
c10 BIGINT UNSIGNED,
|
||
PRIMARY KEY(c01, c02, c03, c04, c05, c06, c07, c08, c09, c10)
|
||
);;
|
||
INSERT INTO t_min VALUES
|
||
(-128, 0,
|
||
-32768, 0,
|
||
-8388608, 0,
|
||
-2147483648, 0,
|
||
-9223372036854775808, 0);
|
||
CREATE TABLE t_max (
|
||
c01 TINYINT,
|
||
c02 TINYINT UNSIGNED,
|
||
c03 SMALLINT,
|
||
c04 SMALLINT UNSIGNED,
|
||
c05 MEDIUMINT,
|
||
c06 MEDIUMINT UNSIGNED,
|
||
c07 INT,
|
||
c08 INT UNSIGNED,
|
||
c09 BIGINT,
|
||
c10 BIGINT UNSIGNED,
|
||
PRIMARY KEY(c01, c02, c03, c04, c05, c06, c07, c08, c09, c10)
|
||
);;
|
||
INSERT INTO t_max VALUES
|
||
(127, 255,
|
||
32767, 65535,
|
||
8388607, 16777215,
|
||
2147483647, 4294967295,
|
||
9223372036854775807, 18446744073709551615);
|
||
CREATE TABLE ```t'\"_str` (
|
||
c1 VARCHAR(32),
|
||
c2 VARCHAR(32),
|
||
c3 VARCHAR(32),
|
||
c4 VARCHAR(32),
|
||
c5 VARCHAR(32),
|
||
c6 VARCHAR(32),
|
||
c7 VARCHAR(32),
|
||
PRIMARY KEY(c1, c2, c3, c4, c5, c6, c7)
|
||
);
|
||
INSERT INTO ```t'\"_str` VALUES
|
||
('1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''');
|
||
INSERT INTO ```t'\"_str` VALUES
|
||
('2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""');
|
||
INSERT INTO ```t'\"_str` VALUES
|
||
('3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\');
|
||
INSERT INTO ```t'\"_str` VALUES
|
||
('4', 'abc', 0x00616263, 0x61626300, 0x61006263, 0x6100626300, 0x610062630000);
|
||
connect con_lock,localhost,root,,;
|
||
connect con_min_trylock,localhost,root,,;
|
||
connect con_max_trylock,localhost,root,,;
|
||
connect con_str_insert_supremum,localhost,root,,;
|
||
connect con_str_lock_row1,localhost,root,,;
|
||
connect con_str_lock_row2,localhost,root,,;
|
||
connect con_str_lock_row3,localhost,root,,;
|
||
connect con_str_lock_row4,localhost,root,,;
|
||
connect con_verify_innodb_locks,localhost,root,,;
|
||
connection con_lock;
|
||
SET autocommit=0;
|
||
SELECT * FROM t_min FOR UPDATE;
|
||
c01 c02 c03 c04 c05 c06 c07 c08 c09 c10
|
||
-128 0 -32768 0 -8388608 0 -2147483648 0 -9223372036854775808 0
|
||
SELECT * FROM t_max FOR UPDATE;
|
||
c01 c02 c03 c04 c05 c06 c07 c08 c09 c10
|
||
127 255 32767 65535 8388607 16777215 2147483647 4294967295 9223372036854775807 18446744073709551615
|
||
SELECT * FROM ```t'\"_str` FOR UPDATE;
|
||
c1 c2 c3 c4 c5 c6 c7
|
||
1 abc 'abc abc' a'bc a'bc' 'abc''
|
||
2 abc "abc abc" a"bc a"bc" "abc""
|
||
3 abc \abc abc\ a\bc a\bc\ \abc\\
|
||
4 abc abc abc a bc a bc a bc
|
||
connection con_min_trylock;
|
||
SELECT * FROM t_min FOR UPDATE;
|
||
connection con_max_trylock;
|
||
SELECT * FROM t_max FOR UPDATE;
|
||
connection con_str_insert_supremum;
|
||
INSERT INTO ```t'\"_str` VALUES
|
||
('z', 'z', 'z', 'z', 'z', 'z', 'z');
|
||
connection con_str_lock_row1;
|
||
SELECT * FROM ```t'\"_str` WHERE c1 = '1' FOR UPDATE;
|
||
connection con_str_lock_row2;
|
||
SELECT * FROM ```t'\"_str` WHERE c1 = '2' FOR UPDATE;
|
||
connection con_str_lock_row3;
|
||
SELECT * FROM ```t'\"_str` WHERE c1 = '3' FOR UPDATE;
|
||
connection con_str_lock_row4;
|
||
SELECT * FROM ```t'\"_str` WHERE c1 = '4' FOR UPDATE;
|
||
connection con_verify_innodb_locks;
|
||
SELECT lock_mode, lock_type, lock_table, lock_index, lock_rec, lock_data
|
||
FROM INFORMATION_SCHEMA.INNODB_LOCKS ORDER BY lock_data;
|
||
lock_mode lock_type lock_table lock_index lock_rec lock_data
|
||
X RECORD `test`.```t'\"_str` PRIMARY 1 #
|
||
X RECORD `test`.```t'\"_str` PRIMARY 1 #
|
||
X RECORD `test`.```t'\"_str` PRIMARY 2 #
|
||
X RECORD `test`.```t'\"_str` PRIMARY 2 #
|
||
X RECORD `test`.```t'\"_str` PRIMARY 3 #
|
||
X RECORD `test`.```t'\"_str` PRIMARY 3 #
|
||
X RECORD `test`.```t'\"_str` PRIMARY 4 #
|
||
X RECORD `test`.```t'\"_str` PRIMARY 4 #
|
||
X RECORD `test`.```t'\"_str` PRIMARY 5 #
|
||
X RECORD `test`.```t'\"_str` PRIMARY 5 #
|
||
X RECORD `test`.`t_max` PRIMARY 2 #
|
||
X RECORD `test`.`t_max` PRIMARY 2 #
|
||
X RECORD `test`.`t_min` PRIMARY 2 #
|
||
X RECORD `test`.`t_min` PRIMARY 2 #
|
||
SELECT lock_table,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
|
||
GROUP BY lock_table;
|
||
lock_table COUNT(*)
|
||
`test`.`t_max` 2
|
||
`test`.`t_min` 2
|
||
`test`.```t'\"_str` 10
|
||
set @save_sql_mode = @@sql_mode;
|
||
SET SQL_MODE='ANSI_QUOTES';
|
||
SELECT lock_table,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
|
||
GROUP BY lock_table;
|
||
lock_table COUNT(*)
|
||
"test"."t_max" 2
|
||
"test"."t_min" 2
|
||
"test"."`t'\""_str" 10
|
||
SET @@sql_mode=@save_sql_mode;
|
||
connection default;
|
||
disconnect con_lock;
|
||
disconnect con_min_trylock;
|
||
disconnect con_max_trylock;
|
||
disconnect con_str_insert_supremum;
|
||
disconnect con_str_lock_row1;
|
||
disconnect con_str_lock_row2;
|
||
disconnect con_str_lock_row3;
|
||
disconnect con_str_lock_row4;
|
||
disconnect con_verify_innodb_locks;
|
||
DROP TABLE t_min, t_max, ```t'\"_str`;
|
||
DESCRIBE INFORMATION_SCHEMA.INNODB_TRX;
|
||
Field Type Null Key Default Extra
|
||
trx_id bigint(21) unsigned NO NULL
|
||
trx_state varchar(13) NO NULL
|
||
trx_started datetime NO NULL
|
||
trx_requested_lock_id varchar(81) YES NULL
|
||
trx_wait_started datetime YES NULL
|
||
trx_weight bigint(21) unsigned NO NULL
|
||
trx_mysql_thread_id bigint(21) unsigned NO NULL
|
||
trx_query varchar(1024) YES NULL
|
||
trx_operation_state varchar(64) YES NULL
|
||
trx_tables_in_use bigint(21) unsigned NO NULL
|
||
trx_tables_locked bigint(21) unsigned NO NULL
|
||
trx_lock_structs bigint(21) unsigned NO NULL
|
||
trx_lock_memory_bytes bigint(21) unsigned NO NULL
|
||
trx_rows_locked bigint(21) unsigned NO NULL
|
||
trx_rows_modified bigint(21) unsigned NO NULL
|
||
trx_concurrency_tickets bigint(21) unsigned NO NULL
|
||
trx_isolation_level enum('READ UNCOMMITTED','READ COMMITTED','REPEATABLE READ','SERIALIZABLE') NO NULL
|
||
trx_unique_checks int(1) NO NULL
|
||
trx_foreign_key_checks int(1) NO NULL
|
||
trx_last_foreign_key_error varchar(256) YES NULL
|
||
trx_is_read_only int(1) NO NULL
|
||
trx_autocommit_non_locking int(1) NO NULL
|
||
DROP TABLE IF EXISTS t1;
|
||
CREATE TABLE t1 (
|
||
c01 INT,
|
||
c02 INT,
|
||
PRIMARY KEY (c01)
|
||
) ENGINE = InnoDB;
|
||
INSERT INTO t1 VALUES
|
||
(1,2),(2,4),(3,6),(4,8);
|
||
CREATE TABLE t2 (
|
||
c01 INT,
|
||
c02 INT,
|
||
PRIMARY KEY (c01),
|
||
FOREIGN KEY fk1 (c02) REFERENCES t1 (c01)
|
||
) ENGINE = InnoDB;
|
||
INSERT INTO t2 VALUES
|
||
(1,1),(2,2),(3,3);
|
||
connect con_trx,localhost,root,,;
|
||
connect con_verify_innodb_trx,localhost,root,,;
|
||
connection con_trx;
|
||
SET autocommit=0;
|
||
INSERT INTO t1 VALUES (5,10);
|
||
SELECT * FROM t1 FOR UPDATE;
|
||
c01 c02
|
||
1 2
|
||
2 4
|
||
3 6
|
||
4 8
|
||
5 10
|
||
connection con_verify_innodb_trx;
|
||
SELECT trx_state, trx_weight, trx_tables_in_use, trx_tables_locked,
|
||
trx_rows_locked, trx_rows_modified, trx_concurrency_tickets,
|
||
trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
|
||
FROM INFORMATION_SCHEMA.INNODB_TRX;
|
||
trx_state trx_weight trx_tables_in_use trx_tables_locked trx_rows_locked trx_rows_modified trx_concurrency_tickets trx_isolation_level trx_unique_checks trx_foreign_key_checks
|
||
RUNNING 3 0 1 6 1 0 REPEATABLE READ 1 1
|
||
connection con_trx;
|
||
ROLLBACK;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
SET UNIQUE_CHECKS = 0;
|
||
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
|
||
BEGIN;
|
||
INSERT INTO t1 VALUES (6,12);
|
||
connection con_verify_innodb_trx;
|
||
SELECT trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
|
||
FROM INFORMATION_SCHEMA.INNODB_TRX;
|
||
trx_isolation_level trx_unique_checks trx_foreign_key_checks
|
||
SERIALIZABLE 0 0
|
||
connection con_trx;
|
||
ROLLBACK;
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
SET UNIQUE_CHECKS = 1;
|
||
BEGIN;
|
||
INSERT INTO t2 VALUES (4,10);
|
||
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c02`) REFERENCES `t1` (`c01`))
|
||
connection con_verify_innodb_trx;
|
||
SELECT trx_state, trx_isolation_level, trx_last_foreign_key_error
|
||
FROM INFORMATION_SCHEMA.INNODB_TRX;
|
||
trx_state trx_isolation_level trx_last_foreign_key_error
|
||
RUNNING REPEATABLE READ `test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c02`) REFERENCES `t1` (`c01`)
|
||
connection default;
|
||
disconnect con_trx;
|
||
disconnect con_verify_innodb_trx;
|
||
DROP TABLE t2;
|
||
DROP TABLE t1;
|