Debian Codegraph @

mysql-test/suite/innodb/r/innodb-alter-table.result

raw
1 drop database if exists moodle19;
2 Warnings:
3 Note 1008 Can't drop database 'moodle19'; database doesn't exist
4 create database moodle19;
5 use moodle19;
6 CREATE TABLE `mdl_course_modules` (
7 `id` bigint(10) NOT NULL AUTO_INCREMENT,
8 `course` bigint(10) NOT NULL DEFAULT '0',
9 `module` bigint(10) NOT NULL DEFAULT '0',
10 `instance` bigint(10) NOT NULL DEFAULT '0',
11 `section` bigint(10) NOT NULL DEFAULT '0',
12 `idnumber` varchar(100) DEFAULT NULL,
13 `added` bigint(10) NOT NULL DEFAULT '0',
14 `delay` varchar(10) NOT NULL DEFAULT '0',
15 `score` smallint(4) NOT NULL DEFAULT '0',
16 `indent` mediumint(5) NOT NULL DEFAULT '0',
17 `visible` tinyint(1) NOT NULL DEFAULT '1',
18 `checkboxesforprereqs` tinyint(1) NOT NULL DEFAULT '0',
19 `stylewhencomplete` varchar(200) DEFAULT '',
20 `checkboxforcomplete` tinyint(1) NOT NULL DEFAULT '0',
21 `stylewhenlocked` varchar(200) DEFAULT 'locked',
22 `visiblewhenlocked` tinyint(1) NOT NULL DEFAULT '1',
23 `visibleold` tinyint(1) NOT NULL DEFAULT '1',
24 `groupmode` smallint(4) NOT NULL DEFAULT '0',
25 `groupingid` bigint(10) NOT NULL DEFAULT '0',
26 `groupmembersonly` smallint(4) NOT NULL DEFAULT '0',
27 `completion` tinyint(1) NOT NULL DEFAULT '0',
28 `completiongradeitemnumber` bigint(10) DEFAULT NULL,
29 `completionview` tinyint(1) NOT NULL DEFAULT '0',
30 `completionexpected` bigint(10) NOT NULL DEFAULT '0',
31 `availablefrom` bigint(10) NOT NULL DEFAULT '0',
32 `availableuntil` bigint(10) NOT NULL DEFAULT '0',
33 `showavailability` tinyint(1) NOT NULL DEFAULT '0',
34 `showdescription` tinyint(1) NOT NULL DEFAULT '0',
35 PRIMARY KEY (`id`),
36 KEY `mdl_courmodu_vis_ix` (`visible`),
37 KEY `mdl_courmodu_cou_ix` (`course`),
38 KEY `mdl_courmodu_mod_ix` (`module`),
39 KEY `mdl_courmodu_ins_ix` (`instance`),
40 KEY `mdl_courmodu_idncou_ix` (`idnumber`,`course`),
41 KEY `mdl_courmodu_gro_ix` (`groupingid`)
42 ) ENGINE=InnoDB AUTO_INCREMENT=447023 DEFAULT CHARSET=utf8 COMMENT='course_modules table retrofitted from MySQL';
43 # Inserting 2701 rows into the table...
44 ALTER TABLE moodle19.mdl_course_modules ADD stefantest LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci AFTER showdescription;
45 drop database moodle19;
46 use test;
47 CREATE TABLE `w_findispmon05u` (
48 `atpkey` INT(11) NOT NULL DEFAULT '0',
49 `atzo05` INT(11) NULL DEFAULT NULL,
50 `pos` BIGINT(21) NULL DEFAULT NULL,
51 `f5BnvB` INT(9) NULL DEFAULT NULL,
52 `f5atbvb` INT(11) NULL DEFAULT NULL,
53 `f5atbwmg` INT(11) NULL DEFAULT NULL,
54 `f5pBneu` BIGINT(12) NULL DEFAULT NULL,
55 `atbwdt` INT(11) NULL DEFAULT NULL,
56 `atbwzt` INT(11) NULL DEFAULT NULL,
57 `atbart` VARCHAR(10) NULL DEFAULT NULL
58 )
59 COLLATE='utf8_general_ci'
60 ENGINE=InnoDB;
61 ALTER TABLE `w_findispmon05u`
62 CHANGE COLUMN `atpkey` `f5atpkey` INT(11) NOT NULL DEFAULT '0' FIRST,
63 CHANGE COLUMN `atzo05` `f5atzo05` INT(11) NULL DEFAULT NULL AFTER `f5atpkey`,
64 CHANGE COLUMN `atbwdt` `f5atbwdt` INT(11) NULL DEFAULT NULL AFTER `f5pBneu`,
65 CHANGE COLUMN `atbwzt` `f5atbwzt` INT(11) NULL DEFAULT NULL AFTER `f5atbwdt`,
66 CHANGE COLUMN `atbart` `f5atbart` VARCHAR(10) NULL DEFAULT NULL AFTER `f5atbwzt`,
67 ADD INDEX `atpkey` (`f5atpkey`),
68 ADD INDEX `inatkey` (`f5atzo05`, `pos`),
69 ADD INDEX `pos` (`pos`, `f5atzo05`);
70 SHOW WARNINGS;
71 Level Code Message
72 SHOW CREATE TABLE `w_findispmon05u`;
73 Table Create Table
74 w_findispmon05u CREATE TABLE `w_findispmon05u` (
75 `f5atpkey` int(11) NOT NULL DEFAULT 0,
76 `f5atzo05` int(11) DEFAULT NULL,
77 `pos` bigint(21) DEFAULT NULL,
78 `f5BnvB` int(9) DEFAULT NULL,
79 `f5atbvb` int(11) DEFAULT NULL,
80 `f5atbwmg` int(11) DEFAULT NULL,
81 `f5pBneu` bigint(12) DEFAULT NULL,
82 `f5atbwdt` int(11) DEFAULT NULL,
83 `f5atbwzt` int(11) DEFAULT NULL,
84 `f5atbart` varchar(10) DEFAULT NULL,
85 KEY `atpkey` (`f5atpkey`),
86 KEY `inatkey` (`f5atzo05`,`pos`),
87 KEY `pos` (`pos`,`f5atzo05`)
88 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
89 DROP TABLE `w_findispmon05u`;
90 CREATE TABLE t (
91 a INT NOT NULL,
92 b INT NOT NULL,
93 PRIMARY KEY (a)
94 ) ENGINE=INNODB;
95 ALTER TABLE t
96 CHANGE COLUMN b c INT NOT NULL,
97 ADD UNIQUE INDEX (c);
98 SHOW WARNINGS;
99 Level Code Message
100 SHOW CREATE TABLE t;
101 Table Create Table
102 t CREATE TABLE `t` (
103 `a` int(11) NOT NULL,
104 `c` int(11) NOT NULL,
105 PRIMARY KEY (`a`),
106 UNIQUE KEY `c` (`c`)
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
108 ALTER TABLE t
109 CHANGE COLUMN c b INT NOT NULL,
110 ADD UNIQUE INDEX (c);
111 ERROR 42000: Key column 'c' doesn't exist in table
112 DROP TABLE t;
113 CREATE TABLE parent (
114 a INT NOT NULL,
115 b INT NOT NULL,
116 PRIMARY KEY (a)
117 ) ENGINE=INNODB;
118 CREATE TABLE child (
119 a INT NOT NULL,
120 b INT NOT NULL,
121 PRIMARY KEY (a)
122 ) ENGINE=INNODB;
123 ALTER TABLE child
124 CHANGE COLUMN b c INT NOT NULL,
125 ADD FOREIGN KEY (c) REFERENCES parent(a);
126 SHOW WARNINGS;
127 Level Code Message
128 SHOW CREATE TABLE child;
129 Table Create Table
130 child CREATE TABLE `child` (
131 `a` int(11) NOT NULL,
132 `c` int(11) NOT NULL,
133 PRIMARY KEY (`a`),
134 KEY `c` (`c`),
135 CONSTRAINT `child_ibfk_1` FOREIGN KEY (`c`) REFERENCES `parent` (`a`)
136 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
137 DROP TABLE child, parent;
138 CREATE TABLE IF NOT EXISTS ticket (
139 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
140 mask VARCHAR(16) DEFAULT '' NOT NULL,
141 subject VARCHAR(255) DEFAULT '' NOT NULL,
142 is_closed TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL,
143 is_deleted TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL,
144 team_id INT UNSIGNED DEFAULT 0 NOT NULL,
145 category_id INT UNSIGNED DEFAULT 0 NOT NULL,
146 first_message_id INT UNSIGNED DEFAULT 0 NOT NULL,
147 created_date INT UNSIGNED,
148 updated_date INT UNSIGNED,
149 due_date INT UNSIGNED,
150 first_wrote_address_id INT UNSIGNED NOT NULL DEFAULT 0,
151 last_wrote_address_id INT UNSIGNED NOT NULL DEFAULT 0,
152 spam_score DECIMAL(4,4) NOT NULL DEFAULT 0,
153 spam_training VARCHAR(1) NOT NULL DEFAULT '',
154 interesting_words VARCHAR(255) NOT NULL DEFAULT '',
155 next_action VARCHAR(255) NOT NULL DEFAULT '',
156 PRIMARY KEY (id)
157 ) ENGINE=InnoDB;
158 ALTER TABLE ticket
159 CHANGE COLUMN team_id group_id INT UNSIGNED NOT NULL DEFAULT 0,
160 CHANGE COLUMN category_id bucket_id INT UNSIGNED NOT NULL DEFAULT 0,
161 ADD COLUMN org_id INT UNSIGNED NOT NULL DEFAULT 0,
162 ADD INDEX org_id (org_id);
163 SHOW CREATE TABLE ticket;
164 Table Create Table
165 ticket CREATE TABLE `ticket` (
166 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
167 `mask` varchar(16) NOT NULL DEFAULT '',
168 `subject` varchar(255) NOT NULL DEFAULT '',
169 `is_closed` tinyint(1) unsigned NOT NULL DEFAULT 0,
170 `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
171 `group_id` int(10) unsigned NOT NULL DEFAULT 0,
172 `bucket_id` int(10) unsigned NOT NULL DEFAULT 0,
173 `first_message_id` int(10) unsigned NOT NULL DEFAULT 0,
174 `created_date` int(10) unsigned DEFAULT NULL,
175 `updated_date` int(10) unsigned DEFAULT NULL,
176 `due_date` int(10) unsigned DEFAULT NULL,
177 `first_wrote_address_id` int(10) unsigned NOT NULL DEFAULT 0,
178 `last_wrote_address_id` int(10) unsigned NOT NULL DEFAULT 0,
179 `spam_score` decimal(4,4) NOT NULL DEFAULT 0.0000,
180 `spam_training` varchar(1) NOT NULL DEFAULT '',
181 `interesting_words` varchar(255) NOT NULL DEFAULT '',
182 `next_action` varchar(255) NOT NULL DEFAULT '',
183 `org_id` int(10) unsigned NOT NULL DEFAULT 0,
184 PRIMARY KEY (`id`),
185 KEY `org_id` (`org_id`)
186 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
187 DROP TABLE ticket;
188 CREATE TABLE t (
189 id bigint(20) unsigned NOT NULL auto_increment,
190 d date NOT NULL,
191 a bigint(20) unsigned NOT NULL,
192 b smallint(5) unsigned DEFAULT NULL,
193 PRIMARY KEY (id,d)
194 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=2
195 PARTITION BY RANGE COLUMNS(d)
196 (
197 PARTITION p20170914 VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB,
198 PARTITION p99991231 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
199 insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
200 insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
201 replace into t(d,a,b) select '2017-09-15',rand()*10000,rand()*10 from t t1, t t2, t t3, t t4;
202 select count(*) from t where d ='2017-09-15';
203 count(*)
204 18
205 ALTER TABLE t CHANGE b c smallint(5) unsigned , ADD KEY idx_d_a (d, a);
206 SHOW CREATE TABLE t;
207 Table Create Table
208 t CREATE TABLE `t` (
209 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
210 `d` date NOT NULL,
211 `a` bigint(20) unsigned NOT NULL,
212 `c` smallint(5) unsigned DEFAULT NULL,
213 PRIMARY KEY (`id`,`d`),
214 KEY `idx_d_a` (`d`,`a`)
215 ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=2
216 PARTITION BY RANGE COLUMNS(`d`)
217 (PARTITION `p20170914` VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB,
218 PARTITION `p99991231` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
219 analyze table t;
220 Table Op Msg_type Msg_text
221 test.t analyze status Engine-independent statistics collected
222 test.t analyze status OK
223 select count(*) from t where d ='2017-09-15';
224 count(*)
225 18
226 select count(*) from t force index(primary) where d ='2017-09-15';
227 count(*)
228 18
229 DROP TABLE t;