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
|
--source include/have_innodb.inc
--source include/have_sequence.inc
CREATE TABLE t1 (i1 INT UNSIGNED NULL DEFAULT 42) ENGINE=innodb;
INSERT INTO t1 VALUES(NULL);
--enable_info
--error WARN_DATA_TRUNCATED
ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
ALGORITHM=INPLACE;
--error WARN_DATA_TRUNCATED
ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
ALGORITHM=COPY;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY(id), ALGORITHM=INPLACE;
# ALTER IGNORE cannot create unique or primary key with ALGORITHM=INPLACE.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER IGNORE TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
# Try the same with ALTER TABLE, using non-strict sql_mode.
--disable_info
SET @old_sql_mode = @@sql_mode;
SET sql_mode = '';
--enable_info
ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
--disable_info
SET sql_mode = @old_sql_mode;
--enable_info
ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT;
--disable_info
SELECT * FROM t1;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (i1 INT UNSIGNED NOT NULL, d1 TIMESTAMP NULL) ENGINE=InnoDB;
SHOW CREATE TABLE t1;
INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
select * from t1;
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;
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NULL DEFAULT '2017-05-08 16:32:45',
ALGORITHM=COPY;
SELECT DISTINCT d1 FROM t1;
--enable_info
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP DEFAULT '2017-05-08 16:32:54';
--disable_info
--echo # Note: NULL was changed to CURRENT_TIMESTAMP(),
--echo # not the specified constant DEFAULT value!
SELECT COUNT(DISTINCT d1),COUNT(d1),COUNT(*) FROM t1;
SELECT DISTINCT (CURRENT_TIMESTAMP()-d1) <= 60 FROM t1;
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);
--enable_info
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE t1 ADD COLUMN w1 varchar(20) NULL DEFAULT USER();
ALTER TABLE t1 CHANGE w1 u1 varchar(30) NULL DEFAULT substr(USER(),1);
--disable_info
SELECT u1, COUNT(DISTINCT d1) FROM t1 GROUP BY u1;
--enable_info
ALTER TABLE t1 ADD COLUMN d2 TIMESTAMP DEFAULT '2017-05-08 16:23:45',
LOCK=NONE;
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, LOCK=NONE;
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE;
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1;
--disable_info
SELECT d1-d3, d2 FROM t1;
SHOW CREATE TABLE t1;
--enable_info
ALTER TABLE t1 ADD COLUMN d4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
--disable_info
SELECT COUNT(DISTINCT d4),COUNT(d4),COUNT(*) FROM t1;
SELECT DISTINCT (CURRENT_TIMESTAMP()-d4) <= 60 FROM t1;
DROP TABLE t1;
# MDEV-19611 INPLACE ALTER does not fail on bad implicit default value
# Empty-table
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;
# Non-empty table
INSERT INTO t1 VALUES (1, now());
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t1 ADD f3 DATE NOT NULL, ALGORITHM=INPLACE;
DROP TABLE t1;
disconnect purge_control;
|
