Debian Codegraph @

mysql-test/suite/innodb/r/innodb_information_schema.result

raw
1 SET default_storage_engine=InnoDB;
2 CREATE TABLE t_min (
3 c01 TINYINT,
4 c02 TINYINT UNSIGNED,
5 c03 SMALLINT,
6 c04 SMALLINT UNSIGNED,
7 c05 MEDIUMINT,
8 c06 MEDIUMINT UNSIGNED,
9 c07 INT,
10 c08 INT UNSIGNED,
11 c09 BIGINT,
12 c10 BIGINT UNSIGNED,
13 PRIMARY KEY(c01, c02, c03, c04, c05, c06, c07, c08, c09, c10)
14 );;
15 INSERT INTO t_min VALUES
16 (-128, 0,
17 -32768, 0,
18 -8388608, 0,
19 -2147483648, 0,
20 -9223372036854775808, 0);
21 CREATE TABLE t_max (
22 c01 TINYINT,
23 c02 TINYINT UNSIGNED,
24 c03 SMALLINT,
25 c04 SMALLINT UNSIGNED,
26 c05 MEDIUMINT,
27 c06 MEDIUMINT UNSIGNED,
28 c07 INT,
29 c08 INT UNSIGNED,
30 c09 BIGINT,
31 c10 BIGINT UNSIGNED,
32 PRIMARY KEY(c01, c02, c03, c04, c05, c06, c07, c08, c09, c10)
33 );;
34 INSERT INTO t_max VALUES
35 (127, 255,
36 32767, 65535,
37 8388607, 16777215,
38 2147483647, 4294967295,
39 9223372036854775807, 18446744073709551615);
40 CREATE TABLE ```t'\"_str` (
41 c1 VARCHAR(32),
42 c2 VARCHAR(32),
43 c3 VARCHAR(32),
44 c4 VARCHAR(32),
45 c5 VARCHAR(32),
46 c6 VARCHAR(32),
47 c7 VARCHAR(32),
48 PRIMARY KEY(c1, c2, c3, c4, c5, c6, c7)
49 );
50 INSERT INTO ```t'\"_str` VALUES
51 ('1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''');
52 INSERT INTO ```t'\"_str` VALUES
53 ('2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""');
54 INSERT INTO ```t'\"_str` VALUES
55 ('3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\');
56 INSERT INTO ```t'\"_str` VALUES
57 ('4', 'abc', 0x00616263, 0x61626300, 0x61006263, 0x6100626300, 0x610062630000);
58 connect con_lock,localhost,root,,;
59 connect con_min_trylock,localhost,root,,;
60 connect con_max_trylock,localhost,root,,;
61 connect con_str_insert_supremum,localhost,root,,;
62 connect con_str_lock_row1,localhost,root,,;
63 connect con_str_lock_row2,localhost,root,,;
64 connect con_str_lock_row3,localhost,root,,;
65 connect con_str_lock_row4,localhost,root,,;
66 connect con_verify_innodb_locks,localhost,root,,;
67 connection con_lock;
68 SET autocommit=0;
69 SELECT * FROM t_min FOR UPDATE;
70 c01 c02 c03 c04 c05 c06 c07 c08 c09 c10
71 -128 0 -32768 0 -8388608 0 -2147483648 0 -9223372036854775808 0
72 SELECT * FROM t_max FOR UPDATE;
73 c01 c02 c03 c04 c05 c06 c07 c08 c09 c10
74 127 255 32767 65535 8388607 16777215 2147483647 4294967295 9223372036854775807 18446744073709551615
75 SELECT * FROM ```t'\"_str` FOR UPDATE;
76 c1 c2 c3 c4 c5 c6 c7
77 1 abc 'abc abc' a'bc a'bc' 'abc''
78 2 abc "abc abc" a"bc a"bc" "abc""
79 3 abc \abc abc\ a\bc a\bc\ \abc\\
80 4 abc abc abc abc abc abc
81 connection con_min_trylock;
82 SELECT * FROM t_min FOR UPDATE;
83 connection con_max_trylock;
84 SELECT * FROM t_max FOR UPDATE;
85 connection con_str_insert_supremum;
86 INSERT INTO ```t'\"_str` VALUES
87 ('z', 'z', 'z', 'z', 'z', 'z', 'z');
88 connection con_str_lock_row1;
89 SELECT * FROM ```t'\"_str` WHERE c1 = '1' FOR UPDATE;
90 connection con_str_lock_row2;
91 SELECT * FROM ```t'\"_str` WHERE c1 = '2' FOR UPDATE;
92 connection con_str_lock_row3;
93 SELECT * FROM ```t'\"_str` WHERE c1 = '3' FOR UPDATE;
94 connection con_str_lock_row4;
95 SELECT * FROM ```t'\"_str` WHERE c1 = '4' FOR UPDATE;
96 connection con_verify_innodb_locks;
97 SELECT lock_mode, lock_type, lock_table, lock_index, lock_rec, lock_data
98 FROM INFORMATION_SCHEMA.INNODB_LOCKS ORDER BY lock_data;
99 lock_mode lock_type lock_table lock_index lock_rec lock_data
100 X RECORD `test`.```t'\"_str` PRIMARY 1 #
101 X RECORD `test`.```t'\"_str` PRIMARY 1 #
102 X RECORD `test`.```t'\"_str` PRIMARY 2 #
103 X RECORD `test`.```t'\"_str` PRIMARY 2 #
104 X RECORD `test`.```t'\"_str` PRIMARY 3 #
105 X RECORD `test`.```t'\"_str` PRIMARY 3 #
106 X RECORD `test`.```t'\"_str` PRIMARY 4 #
107 X RECORD `test`.```t'\"_str` PRIMARY 4 #
108 X RECORD `test`.```t'\"_str` PRIMARY 5 #
109 X RECORD `test`.```t'\"_str` PRIMARY 5 #
110 X RECORD `test`.`t_max` PRIMARY 2 #
111 X RECORD `test`.`t_max` PRIMARY 2 #
112 X RECORD `test`.`t_min` PRIMARY 2 #
113 X RECORD `test`.`t_min` PRIMARY 2 #
114 SELECT lock_table,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
115 GROUP BY lock_table;
116 lock_table COUNT(*)
117 `test`.`t_max` 2
118 `test`.`t_min` 2
119 `test`.```t'\"_str` 10
120 set @save_sql_mode = @@sql_mode;
121 SET SQL_MODE='ANSI_QUOTES';
122 SELECT lock_table,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
123 GROUP BY lock_table;
124 lock_table COUNT(*)
125 "test"."t_max" 2
126 "test"."t_min" 2
127 "test"."`t'\""_str" 10
128 SET @@sql_mode=@save_sql_mode;
129 connection default;
130 disconnect con_lock;
131 disconnect con_min_trylock;
132 disconnect con_max_trylock;
133 disconnect con_str_insert_supremum;
134 disconnect con_str_lock_row1;
135 disconnect con_str_lock_row2;
136 disconnect con_str_lock_row3;
137 disconnect con_str_lock_row4;
138 disconnect con_verify_innodb_locks;
139 DROP TABLE t_min, t_max, ```t'\"_str`;
140 DESCRIBE INFORMATION_SCHEMA.INNODB_TRX;
141 Field Type Null Key Default Extra
142 trx_id bigint(21) unsigned NO NULL
143 trx_state varchar(13) NO NULL
144 trx_started datetime NO NULL
145 trx_requested_lock_id varchar(81) YES NULL
146 trx_wait_started datetime YES NULL
147 trx_weight bigint(21) unsigned NO NULL
148 trx_mysql_thread_id bigint(21) unsigned NO NULL
149 trx_query varchar(1024) YES NULL
150 trx_operation_state varchar(64) YES NULL
151 trx_tables_in_use bigint(21) unsigned NO NULL
152 trx_tables_locked bigint(21) unsigned NO NULL
153 trx_lock_structs bigint(21) unsigned NO NULL
154 trx_lock_memory_bytes bigint(21) unsigned NO NULL
155 trx_rows_locked bigint(21) unsigned NO NULL
156 trx_rows_modified bigint(21) unsigned NO NULL
157 trx_concurrency_tickets bigint(21) unsigned NO NULL
158 trx_isolation_level enum('READ UNCOMMITTED','READ COMMITTED','REPEATABLE READ','SERIALIZABLE') NO NULL
159 trx_unique_checks int(1) NO NULL
160 trx_foreign_key_checks int(1) NO NULL
161 trx_last_foreign_key_error varchar(256) YES NULL
162 trx_is_read_only int(1) NO NULL
163 trx_autocommit_non_locking int(1) NO NULL
164 DROP TABLE IF EXISTS t1;
165 CREATE TABLE t1 (
166 c01 INT,
167 c02 INT,
168 PRIMARY KEY (c01)
169 ) ENGINE = InnoDB;
170 INSERT INTO t1 VALUES
171 (1,2),(2,4),(3,6),(4,8);
172 CREATE TABLE t2 (
173 c01 INT,
174 c02 INT,
175 PRIMARY KEY (c01),
176 FOREIGN KEY fk1 (c02) REFERENCES t1 (c01)
177 ) ENGINE = InnoDB;
178 INSERT INTO t2 VALUES
179 (1,1),(2,2),(3,3);
180 connect con_trx,localhost,root,,;
181 connect con_verify_innodb_trx,localhost,root,,;
182 connection con_trx;
183 SET autocommit=0;
184 INSERT INTO t1 VALUES (5,10);
185 SELECT * FROM t1 FOR UPDATE;
186 c01 c02
187 1 2
188 2 4
189 3 6
190 4 8
191 5 10
192 connection con_verify_innodb_trx;
193 SELECT trx_state, trx_weight, trx_tables_in_use, trx_tables_locked,
194 trx_rows_locked, trx_rows_modified, trx_concurrency_tickets,
195 trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
196 FROM INFORMATION_SCHEMA.INNODB_TRX;
197 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
198 RUNNING 3 0 1 6 1 0 REPEATABLE READ 1 1
199 connection con_trx;
200 ROLLBACK;
201 SET FOREIGN_KEY_CHECKS = 0;
202 SET UNIQUE_CHECKS = 0;
203 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
204 BEGIN;
205 INSERT INTO t1 VALUES (6,12);
206 connection con_verify_innodb_trx;
207 SELECT trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
208 FROM INFORMATION_SCHEMA.INNODB_TRX;
209 trx_isolation_level trx_unique_checks trx_foreign_key_checks
210 SERIALIZABLE 0 0
211 connection con_trx;
212 ROLLBACK;
213 SET FOREIGN_KEY_CHECKS = 1;
214 SET UNIQUE_CHECKS = 1;
215 BEGIN;
216 INSERT INTO t2 VALUES (4,10);
217 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c02`) REFERENCES `t1` (`c01`))
218 connection con_verify_innodb_trx;
219 SELECT trx_state, trx_isolation_level, trx_last_foreign_key_error
220 FROM INFORMATION_SCHEMA.INNODB_TRX;
221 trx_state trx_isolation_level trx_last_foreign_key_error
222 RUNNING REPEATABLE READ `test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c02`) REFERENCES `t1` (`c01`)
223 connection default;
224 disconnect con_trx;
225 disconnect con_verify_innodb_trx;
226 DROP TABLE t2;
227 DROP TABLE t1;