Debian Codegraph @

mysql-test/suite/gcol/r/innodb_virtual_debug.result

raw
1 set default_storage_engine=innodb;
2 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
3 SET GLOBAL innodb_stats_persistent = 0;
4 CREATE TABLE `t` (
5 `a` VARCHAR(100),
6 `b` VARCHAR(100),
7 `c` VARCHAR(200) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
8 `h` VARCHAR(10) DEFAULT NULL,
9 `i` int
10 ) ENGINE=InnoDB;
11 INSERT INTO t VALUES (REPEAT('g', 100), REPEAT('x', 10), DEFAULT, "kk", 1);
12 INSERT INTO t VALUES (REPEAT('a', 100), REPEAT('b', 100), DEFAULT, "mm", 2);
13 CREATE INDEX idx ON t(c(100));
14 SET session debug_dbug="+d,ib_alter_add_virtual_fail";
15 ALTER TABLE t ADD COLUMN x VARCHAR(200) GENERATED ALWAYS AS (a) VIRTUAL,
16 ALGORITHM = INPLACE;
17 ERROR 42000: The storage engine InnoDB can't index column `x`
18 ALTER TABLE t DROP COLUMN c, ALGORITHM = INPLACE;
19 ERROR 42000: The storage engine InnoDB can't index column `c`
20 SET session debug_dbug="";
21 DROP TABLE t;
22 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
23 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
24 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
25 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
26 INSERT INTO t VALUES (null, null, DEFAULT, "mx");
27 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
28 CREATE INDEX idx ON t(c);
29 connect con1,localhost,root,,;
30 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
31 update t set a=0 where a = 11;
32 start transaction;
33 update t set a=1 where a = 0;
34 ROLLBACK;
35 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
36 connection default;
37 SELECT c FROM t;
38 c
39 NULL
40 3
41 19
42 29
43 SHOW CREATE TABLE t;
44 Table Create Table
45 t CREATE TABLE `t` (
46 `a` int(11) DEFAULT NULL,
47 `b` int(11) DEFAULT NULL,
48 `c` int(11) GENERATED ALWAYS AS (`a` + `b`) VIRTUAL,
49 `h` varchar(10) DEFAULT NULL,
50 KEY `idx` (`c`)
51 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
52 SELECT * FROM t;
53 a b c h
54 0 3 3 mm
55 18 1 19 mm
56 28 1 29 mm
57 NULL NULL NULL mx
58 ALTER TABLE t FORCE, ALGORITHM=INPLACE, LOCK=NONE;
59 ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED
60 disconnect con1;
61 DROP TABLE t;
62 SET DEBUG_SYNC = 'RESET';
63 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
64 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
65 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
66 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
67 INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
68 CREATE INDEX idx_1 on t(c);
69 SET @saved_dbug = @@SESSION.debug_dbug;
70 SET debug_dbug = '+d,create_index_fail';
71 ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x),
72 ADD INDEX idcx (c,x);
73 ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
74 UPDATE t SET a=a+1;
75 affected rows: 3
76 info: Rows matched: 4 Changed: 3 Warnings: 0
77 ALTER TABLE t ADD INDEX idc(c);
78 ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
79 SET debug_dbug = @saved_dbug;
80 affected rows: 0
81 UPDATE t SET b=b-1;
82 SHOW CREATE TABLE t;
83 Table Create Table
84 t CREATE TABLE `t` (
85 `a` int(11) DEFAULT NULL,
86 `b` int(11) DEFAULT NULL,
87 `c` int(11) GENERATED ALWAYS AS (`a` + `b`) VIRTUAL,
88 `h` varchar(10) DEFAULT NULL,
89 KEY `idx_1` (`c`)
90 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
91 SELECT c FROM t;
92 c
93 NULL
94 14
95 19
96 29
97 DROP TABLE t;
98 SET DEBUG_SYNC = 'RESET';
99 #
100 # Bug#28825718 - ASSERTION FAILURE: TRX0REC.CC:NNN:N_IDX > 0 WHILE DOING REPLACE/INSERT
101 #
102 CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT GENERATED ALWAYS AS(b+1) VIRTUAL) ENGINE=InnoDB;
103 INSERT INTO t1(a, b) VALUES(1, 1);
104 connect con1,localhost,root,,;
105 SET DEBUG_SYNC = 'row_log_apply_after SIGNAL s1 WAIT_FOR s2';
106 SET lock_wait_timeout = 1;
107 ALTER TABLE t1 ADD UNIQUE INDEX(c, b);
108 connection default;
109 SET DEBUG_SYNC = 'now WAIT_FOR s1';
110 SET DEBUG_SYNC = 'row_log_insert_handle SIGNAL s2 WAIT_FOR s3';
111 INSERT INTO t1(a, b) VALUES(2, 2);
112 connection con1;
113 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
114 SET DEBUG_SYNC = 'now SIGNAL s3';
115 disconnect con1;
116 connection default;
117 SET DEBUG_SYNC = 'RESET';
118 ALTER TABLE t1 ADD KEY(b);
119 INSERT INTO t1(a, b) VALUES(3, 3);
120 SELECT * FROM t1;
121 a b c
122 1 1 2
123 2 2 3
124 3 3 4
125 CHECK TABLE t1;
126 Table Op Msg_type Msg_text
127 test.t1 check status OK
128 DROP TABLE t1;
129 #
130 # MDEV-28806 Assertion `flag == 1' failure in
131 # row_build_index_entry_low upon concurrent ALTER and UPDATE
132 #
133 CREATE TABLE t1(a CHAR(8), b INT, c INT AS (b), KEY(a)) ENGINE=InnoDB;
134 INSERT INTO t1(b) VALUES (1),(2);
135 connect con1,localhost,root,,test;
136 SET DEBUG_SYNC="alter_table_inplace_before_lock_upgrade SIGNAL dml_start WAIT_FOR dml_commit";
137 ALTER TABLE t1 ADD KEY ind (c);
138 connection default;
139 SET DEBUG_SYNC="now WAIT_FOR dml_start";
140 UPDATE t1 SET a ='foo';
141 SET DEBUG_SYNC="now SIGNAL dml_commit";
142 connection con1;
143 CHECK TABLE t1;
144 Table Op Msg_type Msg_text
145 test.t1 check status OK
146 DROP TABLE t1;
147 disconnect con1;
148 connection default;
149 SET DEBUG_SYNC=RESET;
150 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;