Skip to content

Commit daa4ddd

Browse files
gopshankgopshank
committed
WL#6292 - Make TIMESTAMP columns nullable by default.
This worklog implements new behavior for TIMESTAMP columns, defined by CREATE TABLE, ALTER TABLE and CREATE SELECT commands. This is in effort to make TIMESTAMP columns behavior to be more closer to SQL standard. The change in behavior is as described below, Current behavior: ================= 1) Unlike all the other types, TIMESTAMP columns which are not explicitly specified as NULLable automatically get NOT NULL as attribute. 2) If the first TIMESTAMP column in table is not specified as NULLable and doesn't have explicit DEFAULT or ON UPDATE value specified it automatically gets DEFAULT NOW() ON UPDATE NOW() as attributes. 3) All other TIMESTAMP columns which are not NULLable and don't have explicit default specified get '0' as default value and treated as having explicit default value after that (i.e. if you don't provide explicit value for such a column when inserting into table no warning or error is emitted). New behavior: ============= 1) TIMESTAMP columns which are not explicitly specified as NOT NULL become NULLable. 2) No TIMESTAMP columns get DEFAULT NOW() or ON UPDATE NOW() attributes automatically, without them being explicitly specified. 3) Non-NULLable TIMESTAMP columns without explicit default value treated as having no default value. I.e. warning or error is emitted (depends on sql_mode) if we insert a row without providing and explicit value for such a column. In case when warning is emitted and not an error such a column will still get '0' if no explicit value was specified for it. A new command line option '--explicit_defaults_for_timestamp' is introduced for MySQL server to enable new behavior. The old behavior is kept as default, so that the existing applications assuming old semantics work without any changes. Replication slave applier thread is extended with new interface, such that the slave applies the logs with old behavior, if the logs are generated by master with older version than slave. MySQL system table definitions at scripts/* were updated, such that they work fine with old and new behavior with any change. MTR tests which use TIMESTAMP columns are also updated, such that the test works both in old and new behavior. This should help easy transition to new behavior going forward. This worklog addresses Bug#11762529 and Bug#13344629.
1 parent 048c0b4 commit daa4ddd

File tree

108 files changed

+1188
-774
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

108 files changed

+1188
-774
lines changed

‎mysql-test/extra/rpl_tests/rpl_extra_col_slave.test‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -399,7 +399,8 @@ if (`SELECT UPPER(LEFT($engine_type, 3)) != 'NDB'`)
399399
{
400400
STOP SLAVE;
401401
eval CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5),
402-
d TIMESTAMP,
402+
d TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
403+
ON UPDATE CURRENT_TIMESTAMP,
403404
e INT NOT NULL,
404405
f text not null,
405406
g text,

‎mysql-test/include/ctype_numconv.inc‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1164,7 +1164,7 @@ create table t2 as select concat(a) from t1;
11641164
show create table t2;
11651165
drop table t1, t2;
11661166

1167-
create table t1 (a timestamp);
1167+
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
11681168
insert into t1 values (0);
11691169
insert into t1 values (20010203040506);
11701170
insert into t1 values (19800203040506);
@@ -1476,7 +1476,7 @@ select hex(a) from v1;
14761476
drop table t1;
14771477
drop view v1;
14781478

1479-
create table t1 (a timestamp);
1479+
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
14801480
insert into t1 values (0);
14811481
insert into t1 values (20010203040506);
14821482
insert into t1 values (19800203040506);

‎mysql-test/include/function_defaults.inc‎

Lines changed: 90 additions & 90 deletions
Large diffs are not rendered by default.

‎mysql-test/include/function_defaults_notembedded.inc‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@ SET TIME_ZONE = "+00:00";
77
--echo # 2011-04-19 08:02:40 UTC
88
SET TIMESTAMP = 1303200160.123456;
99

10-
eval CREATE TABLE t1 ( a INT, b $timestamp );
10+
eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT CURRENT_$timestamp ON UPDATE CURRENT_$timestamp);
1111

1212
INSERT DELAYED INTO t1 SET a = 1;
1313
FLUSH TABLE t1;
@@ -29,7 +29,7 @@ DROP TABLE t1;
2929
--echo # 2011-04-19 08:04:01 UTC
3030
SET TIMESTAMP = 1303200241.234567;
3131

32-
eval CREATE TABLE t1 ( a INT, b $timestamp );
32+
eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT CURRENT_$timestamp ON UPDATE CURRENT_$timestamp);
3333

3434
INSERT DELAYED INTO t1 ( a ) VALUES (1);
3535
FLUSH TABLE t1;
@@ -45,7 +45,7 @@ DROP TABLE t1;
4545
--echo # Test of a delayed insert handler servicing two insert operations
4646
--echo # with different sets of active defaults.
4747
--echo #
48-
eval CREATE TABLE t1 ( a INT, b $timestamp );
48+
eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT CURRENT_$timestamp ON UPDATE CURRENT_$timestamp);
4949

5050
--connect(con1, localhost, root,,)
5151
--echo # 2011-04-19 08:04:01 UTC
@@ -80,7 +80,7 @@ DROP TABLE t1;
8080
--echo # Test of early activation of function defaults.
8181
--echo #
8282

83-
eval CREATE TABLE t1 ( a INT, b $timestamp );
83+
eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT CURRENT_$timestamp ON UPDATE CURRENT_$timestamp);
8484

8585
SET TIMESTAMP = 1317235172.987654; # 2011-09-28 18:39:32 UTC
8686
INSERT DELAYED INTO t1 ( a ) VALUES (1), (2), (3);

‎mysql-test/include/mix2.inc‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -500,7 +500,7 @@ drop table t1;
500500
# Test of opening table twice and timestamps
501501
#
502502
set @a:=now();
503-
eval CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=$engine_type;
503+
eval CREATE TABLE t1 (a int not null, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key (a)) engine=$engine_type;
504504
insert into t1 (a) values(1),(2),(3);
505505
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
506506
select a from t1 natural join t1 as t2 where b >= @a order by a;
@@ -727,9 +727,9 @@ eval CREATE TABLE t1 (
727727
cname char(15) NOT NULL default '',
728728
carrier_id smallint(6) NOT NULL default '0',
729729
privacy tinyint(4) NOT NULL default '0',
730-
last_mod_date timestamp NOT NULL,
730+
last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
731731
last_mod_id smallint(6) NOT NULL default '0',
732-
last_app_date timestamp NOT NULL,
732+
last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
733733
last_app_id smallint(6) default '-1',
734734
version smallint(6) NOT NULL default '0',
735735
assigned_scps int(11) default '0',
@@ -746,9 +746,9 @@ eval CREATE TABLE t2 (
746746
cname char(15) NOT NULL default '',
747747
carrier_id smallint(6) NOT NULL default '0',
748748
privacy tinyint(4) NOT NULL default '0',
749-
last_mod_date timestamp NOT NULL,
749+
last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
750750
last_mod_id smallint(6) NOT NULL default '0',
751-
last_app_date timestamp NOT NULL,
751+
last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
752752
last_app_id smallint(6) default '-1',
753753
version smallint(6) NOT NULL default '0',
754754
assigned_scps int(11) default '0',

‎mysql-test/include/mtr_warnings.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -173,6 +173,8 @@ INSERT INTO global_suppressions VALUES
173173

174174
/*It will print a warning if a new UUID of server is generated.*/
175175
("No existing UUID has been found, so we assume that this is the first time that this server has been started.*"),
176+
/*It will print a warning if server is run without --explicit_defaults_for_timestamp.*/
177+
("TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)*"),
176178

177179
/* Added 2009-08-XX after fixing Bug #42408 */
178180

‎mysql-test/include/ps_create.inc‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -33,7 +33,8 @@ eval create table t9
3333
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
3434
c5 integer, c6 bigint, c7 float, c8 double,
3535
c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
36-
c13 date, c14 datetime, c15 timestamp, c16 time,
36+
c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
37+
ON UPDATE CURRENT_TIMESTAMP, c16 time,
3738
c17 year, c18 tinyint, c19 bool, c20 char,
3839
c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
3940
c25 blob, c26 text, c27 mediumblob, c28 mediumtext,

‎mysql-test/include/select.inc‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1803,7 +1803,7 @@ DROP TABLE t1;
18031803
# Test of bug with SUM(CASE...)
18041804
#
18051805

1806-
CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
1806+
CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
18071807
INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);
18081808
CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;
18091809
INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);
@@ -3246,7 +3246,7 @@ DROP TABLE t1;
32463246
#
32473247
# Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
32483248
#
3249-
CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
3249+
CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
32503250
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
32513251
ANALYZE TABLE t1;
32523252

@@ -3815,7 +3815,7 @@ SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
38153815
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
38163816
DROP TABLE t1;
38173817

3818-
CREATE TABLE t1(a TIMESTAMP NOT NULL);
3818+
CREATE TABLE t1(a TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
38193819
INSERT INTO t1 VALUES('2001-01-01');
38203820
SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
38213821
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
@@ -4300,4 +4300,4 @@ CREATE TABLE t1 (a TINYBLOB NOT NULL);
43004300
SELECT a, COUNT(*) FROM t1 WHERE 0;
43014301
DROP TABLE t1;
43024302

4303-
--echo # End of test BUG#13571700
4303+
--echo # End of test BUG#13571700

‎mysql-test/r/create.result‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -458,7 +458,7 @@ a b c d e f g h dd
458458
1 -7 7 2000-01-01 b 2000-01-01 00:00:00 05:04:03 yet another binary data 02:00:00
459459
2 -2 2 1825-12-14 a 2003-01-01 03:02:01 04:03:02 binary data 02:00:00
460460
drop table t1, t2;
461-
create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10));
461+
create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, l datetime, m enum('a','b'), n set('a','b'), o char(10));
462462
create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1;
463463
show create table t2;
464464
Table Create Table
@@ -1819,7 +1819,7 @@ DROP TABLE IF EXISTS t1;
18191819
DROP TABLE IF EXISTS t2;
18201820
DROP TABLE IF EXISTS t3;
18211821

1822-
CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);
1822+
CREATE TABLE t1(c1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00');
18231823

18241824
SET sql_mode = NO_ZERO_DATE;
18251825

@@ -1828,7 +1828,7 @@ Warnings:
18281828
Warning 1264 Out of range value for column 'c2' at row 1
18291829
DROP TABLE t2;
18301830

1831-
CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);
1831+
CREATE TABLE t2(c1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c2 TIMESTAMP NOT NULL);
18321832
ERROR 42000: Invalid default value for 'c2'
18331833

18341834
# -- Check that NULL column still can be created.

‎mysql-test/r/ctype_binary.result‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2075,7 +2075,7 @@ t2 CREATE TABLE `t2` (
20752075
`concat(a)` varbinary(64) DEFAULT NULL
20762076
) ENGINE=MyISAM DEFAULT CHARSET=latin1
20772077
drop table t1, t2;
2078-
create table t1 (a timestamp);
2078+
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
20792079
insert into t1 values (0);
20802080
insert into t1 values (20010203040506);
20812081
insert into t1 values (19800203040506);
@@ -2387,7 +2387,7 @@ hex(a)
23872387
0000000000000001
23882388
drop table t1;
23892389
drop view v1;
2390-
create table t1 (a timestamp);
2390+
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
23912391
insert into t1 values (0);
23922392
insert into t1 values (20010203040506);
23932393
insert into t1 values (19800203040506);

0 commit comments

Comments
 (0)