EVOLUTION-MANAGER

Edit File: innodb-alter-timestamp.result

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
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
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
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;