Debian Codegraph @

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

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