Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_sequence.inc
3
4 CREATE TABLE t1 (i1 INT UNSIGNED NULL DEFAULT 42) ENGINE=innodb;
5 INSERT INTO t1 VALUES(NULL);
6 --enable_info
7 --error WARN_DATA_TRUNCATED
8 ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
9 ALGORITHM=INPLACE;
10 --error WARN_DATA_TRUNCATED
11 ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
12 ALGORITHM=COPY;
13 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
14 ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
15 ADD PRIMARY KEY(id), ALGORITHM=INPLACE;
16
17 # ALTER IGNORE cannot create unique or primary key with ALGORITHM=INPLACE.
18 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
19 ALTER IGNORE TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
20 # Try the same with ALTER TABLE, using non-strict sql_mode.
21 --disable_info
22 SET @old_sql_mode = @@sql_mode;
23 SET sql_mode = '';
24 --enable_info
25 ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
26 --disable_info
27 SET sql_mode = @old_sql_mode;
28 --enable_info
29 ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT;
30 --disable_info
31 SELECT * FROM t1;
32 SHOW CREATE TABLE t1;
33 DROP TABLE t1;
34
35 CREATE TABLE t1 (i1 INT UNSIGNED NOT NULL, d1 TIMESTAMP NULL) ENGINE=InnoDB;
36
37 SHOW CREATE TABLE t1;
38
39 INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
40 select * from t1;
41 set sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE';
42 ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NULL DEFAULT '2017-05-08 16:23:45',
43 ALGORITHM=INPLACE;
44 SELECT DISTINCT d1 FROM t1;
45 ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NULL DEFAULT '2017-05-08 16:32:45',
46 ALGORITHM=COPY;
47 SELECT DISTINCT d1 FROM t1;
48 --enable_info
49 ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP DEFAULT '2017-05-08 16:32:54';
50 --disable_info
51 SELECT COUNT(DISTINCT d1),COUNT(d1),COUNT(*) FROM t1;
52 drop table t1;
53
54 CREATE TABLE t1 (
55 `i1` INT(10) UNSIGNED NOT NULL,
56 `d1` TIMESTAMP NULL DEFAULT NULL
57 ) ENGINE=innodb;
58 INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
59 --enable_info
60 ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
61 ALTER TABLE t1 ADD COLUMN w1 varchar(20) NULL DEFAULT USER();
62 ALTER TABLE t1 CHANGE w1 u1 varchar(30) NULL DEFAULT substr(USER(),1);
63 --disable_info
64 SELECT u1, COUNT(DISTINCT d1) FROM t1 GROUP BY u1;
65
66 --enable_info
67 ALTER TABLE t1 ADD COLUMN d2 TIMESTAMP DEFAULT '2017-05-08 16:23:45',
68 LOCK=NONE;
69 --error ER_ALTER_OPERATION_NOT_SUPPORTED
70 ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE, LOCK=NONE;
71 --error ER_ALTER_OPERATION_NOT_SUPPORTED
72 ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE;
73 ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1;
74 --disable_info
75 SELECT d1-d3, d2 FROM t1;
76 SHOW CREATE TABLE t1;
77 --enable_info
78 ALTER TABLE t1 ADD COLUMN d4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
79 --disable_info
80 SELECT COUNT(DISTINCT d4),COUNT(d4),COUNT(*) FROM t1;
81 SELECT DISTINCT (CURRENT_TIMESTAMP()-d4) <= 60 FROM t1;
82 DROP TABLE t1;
83
84 # MDEV-19611 INPLACE ALTER does not fail on bad implicit default value
85
86 # Empty-table
87 CREATE TABLE t1(f1 int) ENGINE=InnoDB;
88 INSERT INTO t1 SELECT * FROM seq_1_to_4096;
89 connect(purge_control,localhost,root,,);
90 START TRANSACTION WITH CONSISTENT SNAPSHOT;
91
92 connection default;
93 DELETE FROM t1;
94 SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';
95 ALTER TABLE t1 ADD f2 DATE NOT NULL, ALGORITHM=INPLACE;
96
97 # Non-empty table
98 INSERT INTO t1 VALUES (1, now());
99 --error ER_ALTER_OPERATION_NOT_SUPPORTED
100 ALTER TABLE t1 ADD f3 DATE NOT NULL, ALGORITHM=INPLACE;
101 DROP TABLE t1;
102 disconnect purge_control;
103
104 --echo #
105 --echo # MDEV-26458 SIGSEGV in innobase_table_is_empty() on ALTER TABLE
106 --echo #
107 CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB;
108 ALTER TABLE t DISCARD TABLESPACE;
109 SET sql_mode='NO_ZERO_DATE';
110 ALTER TABLE t ADD c DATE NOT NULL;
111 SET sql_mode=DEFAULT;
112 DROP TABLE t;
113
114 --echo # End of 10.3 tests