Debian Codegraph @

mysql-test/suite/innodb/t/innodb-alter-table.test

raw
1 --source include/innodb_page_size.inc
2 --source include/have_partition.inc
3 # This test is slow on buildbot.
4 --source include/big_test.inc
5
6 #
7 # MMDEV-8386: MariaDB creates very big tmp file and hangs on xtradb
8 #
9 drop database if exists moodle19;
10 create database moodle19;
11 use moodle19;
12
13 CREATE TABLE `mdl_course_modules` (
14 `id` bigint(10) NOT NULL AUTO_INCREMENT,
15 `course` bigint(10) NOT NULL DEFAULT '0',
16 `module` bigint(10) NOT NULL DEFAULT '0',
17 `instance` bigint(10) NOT NULL DEFAULT '0',
18 `section` bigint(10) NOT NULL DEFAULT '0',
19 `idnumber` varchar(100) DEFAULT NULL,
20 `added` bigint(10) NOT NULL DEFAULT '0',
21 `delay` varchar(10) NOT NULL DEFAULT '0',
22 `score` smallint(4) NOT NULL DEFAULT '0',
23 `indent` mediumint(5) NOT NULL DEFAULT '0',
24 `visible` tinyint(1) NOT NULL DEFAULT '1',
25 `checkboxesforprereqs` tinyint(1) NOT NULL DEFAULT '0',
26 `stylewhencomplete` varchar(200) DEFAULT '',
27 `checkboxforcomplete` tinyint(1) NOT NULL DEFAULT '0',
28 `stylewhenlocked` varchar(200) DEFAULT 'locked',
29 `visiblewhenlocked` tinyint(1) NOT NULL DEFAULT '1',
30 `visibleold` tinyint(1) NOT NULL DEFAULT '1',
31 `groupmode` smallint(4) NOT NULL DEFAULT '0',
32 `groupingid` bigint(10) NOT NULL DEFAULT '0',
33 `groupmembersonly` smallint(4) NOT NULL DEFAULT '0',
34 `completion` tinyint(1) NOT NULL DEFAULT '0',
35 `completiongradeitemnumber` bigint(10) DEFAULT NULL,
36 `completionview` tinyint(1) NOT NULL DEFAULT '0',
37 `completionexpected` bigint(10) NOT NULL DEFAULT '0',
38 `availablefrom` bigint(10) NOT NULL DEFAULT '0',
39 `availableuntil` bigint(10) NOT NULL DEFAULT '0',
40 `showavailability` tinyint(1) NOT NULL DEFAULT '0',
41 `showdescription` tinyint(1) NOT NULL DEFAULT '0',
42 PRIMARY KEY (`id`),
43 KEY `mdl_courmodu_vis_ix` (`visible`),
44 KEY `mdl_courmodu_cou_ix` (`course`),
45 KEY `mdl_courmodu_mod_ix` (`module`),
46 KEY `mdl_courmodu_ins_ix` (`instance`),
47 KEY `mdl_courmodu_idncou_ix` (`idnumber`,`course`),
48 KEY `mdl_courmodu_gro_ix` (`groupingid`)
49 ) ENGINE=InnoDB AUTO_INCREMENT=447023 DEFAULT CHARSET=utf8 COMMENT='course_modules table retrofitted from MySQL';
50
51 let $num= 2701;
52 --disable_query_log
53 --echo # Inserting $num rows into the table...
54 while ($num)
55 {
56 eval INSERT INTO mdl_course_modules VALUES ($num,4,5,5,24,NULL,1141569781,'',0,0,1,0,'',0,'locked',1,1,0,0,0,0,NULL,0,0,0,0,0,0);
57 dec $num;
58 }
59 --enable_query_log
60 ALTER TABLE moodle19.mdl_course_modules ADD stefantest LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci AFTER showdescription;
61
62 drop database moodle19;
63
64 #
65 # Mdev-9469: Incorrect key file on alter table
66 #
67 use test;
68
69 CREATE TABLE `w_findispmon05u` (
70 `atpkey` INT(11) NOT NULL DEFAULT '0',
71 `atzo05` INT(11) NULL DEFAULT NULL,
72 `pos` BIGINT(21) NULL DEFAULT NULL,
73 `f5BnvB` INT(9) NULL DEFAULT NULL,
74 `f5atbvb` INT(11) NULL DEFAULT NULL,
75 `f5atbwmg` INT(11) NULL DEFAULT NULL,
76 `f5pBneu` BIGINT(12) NULL DEFAULT NULL,
77 `atbwdt` INT(11) NULL DEFAULT NULL,
78 `atbwzt` INT(11) NULL DEFAULT NULL,
79 `atbart` VARCHAR(10) NULL DEFAULT NULL
80 )
81 COLLATE='utf8_general_ci'
82 ENGINE=InnoDB;
83 ALTER TABLE `w_findispmon05u`
84 CHANGE COLUMN `atpkey` `f5atpkey` INT(11) NOT NULL DEFAULT '0' FIRST,
85 CHANGE COLUMN `atzo05` `f5atzo05` INT(11) NULL DEFAULT NULL AFTER `f5atpkey`,
86 CHANGE COLUMN `atbwdt` `f5atbwdt` INT(11) NULL DEFAULT NULL AFTER `f5pBneu`,
87 CHANGE COLUMN `atbwzt` `f5atbwzt` INT(11) NULL DEFAULT NULL AFTER `f5atbwdt`,
88 CHANGE COLUMN `atbart` `f5atbart` VARCHAR(10) NULL DEFAULT NULL AFTER `f5atbwzt`,
89 ADD INDEX `atpkey` (`f5atpkey`),
90 ADD INDEX `inatkey` (`f5atzo05`, `pos`),
91 ADD INDEX `pos` (`pos`, `f5atzo05`);
92
93 SHOW WARNINGS;
94 SHOW CREATE TABLE `w_findispmon05u`;
95
96 DROP TABLE `w_findispmon05u`;
97
98 CREATE TABLE t (
99 a INT NOT NULL,
100 b INT NOT NULL,
101 PRIMARY KEY (a)
102 ) ENGINE=INNODB;
103
104 ALTER TABLE t
105 CHANGE COLUMN b c INT NOT NULL,
106 ADD UNIQUE INDEX (c);
107
108 SHOW WARNINGS;
109 SHOW CREATE TABLE t;
110
111 # this should fail
112 --error 1072
113 ALTER TABLE t
114 CHANGE COLUMN c b INT NOT NULL,
115 ADD UNIQUE INDEX (c);
116
117 DROP TABLE t;
118
119 #
120 # Check Foreign Keys
121 #
122 CREATE TABLE parent (
123 a INT NOT NULL,
124 b INT NOT NULL,
125 PRIMARY KEY (a)
126 ) ENGINE=INNODB;
127
128 CREATE TABLE child (
129 a INT NOT NULL,
130 b INT NOT NULL,
131 PRIMARY KEY (a)
132 ) ENGINE=INNODB;
133
134 ALTER TABLE child
135 CHANGE COLUMN b c INT NOT NULL,
136 ADD FOREIGN KEY (c) REFERENCES parent(a);
137
138 SHOW WARNINGS;
139
140 SHOW CREATE TABLE child;
141
142 DROP TABLE child, parent;
143
144 #
145 # MDEV-10535: ALTER TABLE causes standalone/wsrep cluster crash
146 #
147 CREATE TABLE IF NOT EXISTS ticket (
148 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
149 mask VARCHAR(16) DEFAULT '' NOT NULL,
150 subject VARCHAR(255) DEFAULT '' NOT NULL,
151 is_closed TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL,
152 is_deleted TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL,
153 team_id INT UNSIGNED DEFAULT 0 NOT NULL,
154 category_id INT UNSIGNED DEFAULT 0 NOT NULL,
155 first_message_id INT UNSIGNED DEFAULT 0 NOT NULL,
156 created_date INT UNSIGNED,
157 updated_date INT UNSIGNED,
158 due_date INT UNSIGNED,
159 first_wrote_address_id INT UNSIGNED NOT NULL DEFAULT 0,
160 last_wrote_address_id INT UNSIGNED NOT NULL DEFAULT 0,
161 spam_score DECIMAL(4,4) NOT NULL DEFAULT 0,
162 spam_training VARCHAR(1) NOT NULL DEFAULT '',
163 interesting_words VARCHAR(255) NOT NULL DEFAULT '',
164 next_action VARCHAR(255) NOT NULL DEFAULT '',
165 PRIMARY KEY (id)
166 ) ENGINE=InnoDB;
167
168 ALTER TABLE ticket
169 CHANGE COLUMN team_id group_id INT UNSIGNED NOT NULL DEFAULT 0,
170 CHANGE COLUMN category_id bucket_id INT UNSIGNED NOT NULL DEFAULT 0,
171 ADD COLUMN org_id INT UNSIGNED NOT NULL DEFAULT 0,
172 ADD INDEX org_id (org_id);
173
174 SHOW CREATE TABLE ticket;
175
176 DROP TABLE ticket;
177
178 #
179 # MDEV-13838: Wrong result after altering a partitioned table
180 #
181
182 CREATE TABLE t (
183 id bigint(20) unsigned NOT NULL auto_increment,
184 d date NOT NULL,
185 a bigint(20) unsigned NOT NULL,
186 b smallint(5) unsigned DEFAULT NULL,
187 PRIMARY KEY (id,d)
188 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=2
189 PARTITION BY RANGE COLUMNS(d)
190 (
191 PARTITION p20170914 VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB,
192 PARTITION p99991231 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
193
194 insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
195 insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
196
197 replace into t(d,a,b) select '2017-09-15',rand()*10000,rand()*10 from t t1, t t2, t t3, t t4;
198
199 select count(*) from t where d ='2017-09-15';
200
201 ALTER TABLE t CHANGE b c smallint(5) unsigned , ADD KEY idx_d_a (d, a);
202 SHOW CREATE TABLE t;
203 analyze table t;
204
205 select count(*) from t where d ='2017-09-15';
206 select count(*) from t force index(primary) where d ='2017-09-15';
207
208 DROP TABLE t;