1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
CREATE TABLE t1 (i1 INT UNSIGNED NULL DEFAULT 42) ENGINE=innodb;
INSERT INTO t1 VALUES(NULL);
ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
ALGORITHM=INPLACE;
ERROR 01000: Data truncated for column 'i1' at row 1
ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
ALGORITHM=COPY;
ERROR 01000: Data truncated for column 'i1' at row 1
ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY(id), ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
ALTER IGNORE TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Creating unique indexes with IGNORE requires COPY algorithm to remove duplicate rows. Try ALGORITHM=COPY
SET @old_sql_mode = @@sql_mode;
SET sql_mode = '';
ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 1
Warnings:
Warning 1265 Data truncated for column 'i1' at row 1
SET sql_mode = @old_sql_mode;
ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT;
affected rows: 1
info: Records: 1 Duplicates: 0 Warnings: 0
SELECT * FROM t1;
id
42
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE t1;
CREATE TABLE t1 (i1 INT UNSIGNED NOT NULL, d1 TIMESTAMP NULL) ENGINE=InnoDB;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i1` int(10) unsigned NOT NULL,
`d1` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
select * from t1;
i1 d1
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
set sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE';
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NULL DEFAULT '2017-05-08 16:23:45',
ALGORITHM=INPLACE;
SELECT DISTINCT d1 FROM t1;
d1
NULL
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NULL DEFAULT '2017-05-08 16:32:45',
ALGORITHM=COPY;
SELECT DISTINCT d1 FROM t1;
d1
NULL
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP DEFAULT '2017-05-08 16:32:54';
affected rows: 5
info: Records: 5 Duplicates: 0 Warnings: 0
# Note: NULL was changed to CURRENT_TIMESTAMP(),
# not the specified constant DEFAULT value!
SELECT COUNT(DISTINCT d1),COUNT(d1),COUNT(*) FROM t1;
COUNT(DISTINCT d1) COUNT(d1) COUNT(*)
1 5 5
SELECT DISTINCT (CURRENT_TIMESTAMP()-d1) <= 60 FROM t1;
(CURRENT_TIMESTAMP()-d1) <= 60
1
drop table t1;
CREATE TABLE t1 (
`i1` INT(10) UNSIGNED NOT NULL,
`d1` TIMESTAMP NULL DEFAULT NULL
) ENGINE=innodb;
INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
affected rows: 5
info: Records: 5 Duplicates: 0 Warnings: 0
ALTER TABLE t1 ADD COLUMN w1 varchar(20) NULL DEFAULT USER();
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE t1 CHANGE w1 u1 varchar(30) NULL DEFAULT substr(USER(),1);
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 0
SELECT u1, COUNT(DISTINCT d1) FROM t1 GROUP BY u1;
u1 COUNT(DISTINCT d1)
root@localhost 1
ALTER TABLE t1 ADD COLUMN d2 TIMESTAMP DEFAULT '2017-05-08 16:23:45',
LOCK=NONE;
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, LOCK=NONE;
ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1;
affected rows: 5
info: Records: 5 Duplicates: 0 Warnings: 0
SELECT d1-d3, d2 FROM t1;
d1-d3 d2
0 2017-05-08 16:23:45
0 2017-05-08 16:23:45
0 2017-05-08 16:23:45
0 2017-05-08 16:23:45
0 2017-05-08 16:23:45
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i1` int(10) unsigned NOT NULL,
`d1` timestamp NOT NULL DEFAULT current_timestamp(),
`u1` varchar(30) DEFAULT substr(user(),1),
`d2` timestamp NOT NULL DEFAULT '2017-05-08 16:23:45',
`d3` timestamp NOT NULL DEFAULT `d1`
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE t1 ADD COLUMN d4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 0
SELECT COUNT(DISTINCT d4),COUNT(d4),COUNT(*) FROM t1;
COUNT(DISTINCT d4) COUNT(d4) COUNT(*)
1 5 5
SELECT DISTINCT (CURRENT_TIMESTAMP()-d4) <= 60 FROM t1;
(CURRENT_TIMESTAMP()-d4) <= 60
1
DROP TABLE t1;
CREATE TABLE t1(f1 int) ENGINE=InnoDB;
INSERT INTO t1 SELECT * FROM seq_1_to_4096;
connect purge_control,localhost,root,,;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connection default;
DELETE FROM t1;
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';
ALTER TABLE t1 ADD f2 DATE NOT NULL, ALGORITHM=INPLACE;
INSERT INTO t1 VALUES (1, now());
Warnings:
Note 1265 Data truncated for column 'f2' at row 1
ALTER TABLE t1 ADD f3 DATE NOT NULL, ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
DROP TABLE t1;
disconnect purge_control;
#
# MDEV-26458 SIGSEGV in innobase_table_is_empty() on ALTER TABLE
#
CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB;
ALTER TABLE t DISCARD TABLESPACE;
SET sql_mode='NO_ZERO_DATE';
ALTER TABLE t ADD c DATE NOT NULL;
SET sql_mode=DEFAULT;
DROP TABLE t;
# End of 10.3 tests
|
