diff options
111 files changed, 844 insertions, 219 deletions
diff --git a/mysql-test/extra/binlog_tests/mysqlbinlog_row_engine.inc b/mysql-test/extra/binlog_tests/mysqlbinlog_row_engine.inc index c072d503d00..1cc850091fe 100644 --- a/mysql-test/extra/binlog_tests/mysqlbinlog_row_engine.inc +++ b/mysql-test/extra/binlog_tests/mysqlbinlog_row_engine.inc @@ -77,7 +77,7 @@ eval CREATE TABLE t1 ( # c28 DATE, c29 DATETIME, - c30 TIMESTAMP, + c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c31 TIME, c32 YEAR, # diff --git a/mysql-test/extra/rpl_tests/rpl_extra_col_slave.test b/mysql-test/extra/rpl_tests/rpl_extra_col_slave.test index 9b5a552c327..47d75c0d0b2 100644 --- a/mysql-test/extra/rpl_tests/rpl_extra_col_slave.test +++ b/mysql-test/extra/rpl_tests/rpl_extra_col_slave.test @@ -398,7 +398,8 @@ sync_slave_with_master; STOP SLAVE; RESET SLAVE; eval CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), - d TIMESTAMP, + d TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + ON UPDATE CURRENT_TIMESTAMP, e INT NOT NULL, f text not null, g text, diff --git a/mysql-test/include/ctype_numconv.inc b/mysql-test/include/ctype_numconv.inc index 9e59f54a8b3..1d675a5db6f 100644 --- a/mysql-test/include/ctype_numconv.inc +++ b/mysql-test/include/ctype_numconv.inc @@ -1166,7 +1166,7 @@ create table t2 as select concat(a) from t1; show create table t2; drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -1478,7 +1478,7 @@ select hex(a) from v1; drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); diff --git a/mysql-test/include/mix2.inc b/mysql-test/include/mix2.inc index d6becb2e37e..cdc39f8407f 100644 --- a/mysql-test/include/mix2.inc +++ b/mysql-test/include/mix2.inc @@ -500,7 +500,7 @@ drop table t1; # Test of opening table twice and timestamps # set @a:=now(); -eval CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=$engine_type; +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; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; select a from t1 natural join t1 as t2 where b >= @a order by a; @@ -727,9 +727,9 @@ eval CREATE TABLE t1 ( cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', - last_mod_date timestamp NOT NULL, + last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_mod_id smallint(6) NOT NULL default '0', - last_app_date timestamp NOT NULL, + last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', @@ -746,9 +746,9 @@ eval CREATE TABLE t2 ( cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', - last_mod_date timestamp NOT NULL, + last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_mod_id smallint(6) NOT NULL default '0', - last_app_date timestamp NOT NULL, + last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', diff --git a/mysql-test/include/mtr_warnings.sql b/mysql-test/include/mtr_warnings.sql index bc310fe0719..1ee0658204d 100644 --- a/mysql-test/include/mtr_warnings.sql +++ b/mysql-test/include/mtr_warnings.sql @@ -165,6 +165,9 @@ INSERT INTO global_suppressions VALUES ("The slave I.O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master."), + /*It will print a warning if server is run without --explicit_defaults_for_timestamp.*/ + ("TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)*"), + /* Added 2009-08-XX after fixing Bug #42408 */ ("Although a path was specified for the .* option, log tables are used"), diff --git a/mysql-test/include/ps_create.inc b/mysql-test/include/ps_create.inc index b2a6fc4b920..91528e2a9a1 100644 --- a/mysql-test/include/ps_create.inc +++ b/mysql-test/include/ps_create.inc @@ -33,7 +33,8 @@ eval create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), - c13 date, c14 datetime, c15 timestamp, c16 time, + c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP + ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/include/type_hrtime.inc b/mysql-test/include/type_hrtime.inc index 5d847d72195..071dc7b161d 100644 --- a/mysql-test/include/type_hrtime.inc +++ b/mysql-test/include/type_hrtime.inc @@ -25,7 +25,12 @@ alter table t1 engine=innodb; select * from t1 order by a; select * from t1 order by a+0; drop table t1; -eval create table t1 (a $type(4)) engine=innodb; +let attr=; +if ($type == timestamp) +{ + let attr=NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4); +} +eval create table t1 (a $type(4)$attr) engine=innodb; insert t1 values ('2010-12-11 01:02:03.456789'); select * from t1; select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456'; @@ -93,7 +98,12 @@ drop table t1, t2; # # SP # -eval create table t1 (a $type(6), b $type(6)); +let attr=; +if ($type == timestamp) +{ + let attr=NOT NULL DEFAULT '0000-00-00 00:00:00.000000'; +} +eval create table t1 (a $type(6)$attr, b $type(6)$attr); eval create procedure foo(x $type, y $type(4)) insert into t1 values (x, y); call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); select * from t1; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index e5d3e67b3da..e5b3acacec8 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -494,7 +494,7 @@ a b c d e f g h dd 1 -7 7 2000-01-01 b 2000-01-01 00:00:00 05:04:03 yet another binary data 02:00:00 2 -2 2 1825-12-14 a 2003-01-01 03:02:01 04:03:02 binary data 02:00:00 drop table t1, t2; -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)); +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)); 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; show create table t2; Table Create Table @@ -1972,14 +1972,14 @@ DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; -CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP); +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'); SET sql_mode = NO_ZERO_DATE; CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0); ERROR 42000: Invalid default value for 'c2' -CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP); +CREATE TABLE t2(c1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c2 TIMESTAMP NOT NULL); ERROR 42000: Invalid default value for 'c2' # -- Check that NULL column still can be created. diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index 2dbf783ff27..ac79a158943 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -2076,7 +2076,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -2388,7 +2388,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index c69a56b3b9c..08a840f7749 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -2470,7 +2470,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -2782,7 +2782,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index 2d2b3473d57..e518dbb07e0 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -2752,7 +2752,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -3064,7 +3064,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 803ca9a048b..22c149c95ba 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -3685,7 +3685,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -3997,7 +3997,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index ef72df7f9c7..66db7df555f 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -4527,7 +4527,7 @@ t2 CREATE TABLE `t2` ( `concat(a)` varbinary(64) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); @@ -4839,7 +4839,7 @@ hex(a) 0000000000000001 drop table t1; drop view v1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0); insert into t1 values (20010203040506); insert into t1 values (19800203040506); diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index b5546202a6e..59be24dbddc 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -20,7 +20,7 @@ insert into t1 values (0,"mysql a"); insert into t1 values (0,"r1manic"); insert into t1 values (0,"r1man"); drop table t1; -create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello", i int); +create table t1 (a int not null auto_increment, primary key (a), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c char(10) default "hello", i int); insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default); select a,t>0,c,i from t1; a t>0 c i diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index bc4d69e72eb..9b3dcedf7be 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -418,7 +418,7 @@ create table t1 ( c1 int, c2 char(12), c3 varchar(123), -c4 timestamp, +c4 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, index (c1), index i1 (c1), index i2 (c2), diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index fcaeb3359f0..ffdab244f31 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -8,7 +8,7 @@ def hello 253 5 5 N 1 31 8 def NULL 6 0 0 Y 32896 0 63 1 1.0 -1 hello NULL 1 1.0 -1 hello NULL -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)); +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)); select * from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 a a 1 4 0 Y 32768 0 63 @@ -239,7 +239,7 @@ dcol_uns double unsigned, # date/time types date_col date, time_col time, -timestamp_col timestamp, +timestamp_col timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, year_col year, datetime_col datetime, # string types diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index e824db8928a..bf706b1445d 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -608,7 +608,7 @@ update t1 set sca_pic="test" where sca_pic is null; delete from t1 where sca_code='pd'; drop table t1; set @a:=now(); -CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=MyISAM; +CREATE TABLE t1 (a int not null, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key (a)) engine=MyISAM; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; a @@ -843,9 +843,9 @@ number bigint(20) NOT NULL default '0', cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', -last_mod_date timestamp NOT NULL, +last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_mod_id smallint(6) NOT NULL default '0', -last_app_date timestamp NOT NULL, +last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', @@ -862,9 +862,9 @@ number bigint(20) NOT NULL default '0', cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', -last_mod_date timestamp NOT NULL, +last_mod_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_mod_id smallint(6) NOT NULL default '0', -last_app_date timestamp NOT NULL, +last_app_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index b0265119eef..c680339536b 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -181,8 +181,8 @@ ERROR HY000: You are using safe update mode and you tried to update a table with set sql_safe_updates=0; drop table t1,t2; set timestamp=1038401397; -create table t1 (n int(10) not null primary key, d int(10), t timestamp); -create table t2 (n int(10) not null primary key, d int(10), t timestamp); +create table t1 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); +create table t2 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values(1,1,NULL); insert into t2 values(1,10,NULL),(2,20,NULL); set timestamp=1038000000; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index ca668d135aa..9e90662b87d 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -197,6 +197,11 @@ The following options may be given as the first argument: If non-zero, binary logs will be purged after expire_logs_days days; possible purges happen at startup and at binary log rotation + --explicit-defaults-for-timestamp + This option causes CREATE TABLE to create all TIMESTAMP + columns as NULL with DEFAULT NULL attribute, Without this + option, TIMESTAMP columns are NOT NULL and have implicit + DEFAULT clauses. The old behavior is deprecated. --external-locking Use system (external) locking (disabled by default). With this option enabled you can run myisamchk to test (not repair) tables while the MySQL server is running. @@ -1179,6 +1184,7 @@ enforce-storage-engine (No default value) event-scheduler OFF expensive-subquery-limit 100 expire-logs-days 0 +explicit-defaults-for-timestamp FALSE external-locking FALSE extra-max-connections 1 extra-port 0 diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index ae4377533cc..bbece88bb98 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -2891,7 +2891,7 @@ drop table t1; # Bug#13052 mysqldump timestamp reloads broken # drop table if exists t1; -create table t1 (`d` timestamp, unique (`d`)); +create table t1 (`d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, unique (`d`)); set time_zone='+00:00'; insert into t1 values ('2003-10-25 22:00:00'),('2003-10-25 23:00:00'); select * from t1; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 2ed38dc5c11..08485eb95aa 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -17,7 +17,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 194d61aaaf8..bdd8040f584 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -10,7 +10,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index c9e7d042508..9d478acdccd 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -10,7 +10,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 5549446d85e..d1483df63b0 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -11,7 +11,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 varchar(100), c24 varchar(100), c25 varchar(100), c26 varchar(100), c27 varchar(100), c28 varchar(100), diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 059d4e6e6ab..c42ab154fa3 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -12,7 +12,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -32,7 +33,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -52,7 +54,7 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -3406,7 +3408,7 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 97ab9173c2d..9dbf6e0c46b 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2111,7 +2111,7 @@ INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 1 as rnd1 from t1 where rand() > 2; rnd1 DROP TABLE t1; -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; +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; 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); 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; INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); @@ -3742,7 +3742,7 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where DROP TABLE t1; -CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts)); +CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); ANALYZE TABLE t1; Table Op Msg_type Msg_text @@ -4482,7 +4482,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select '2001-01-01' AS `a` from dual where 1 DROP TABLE t1; -CREATE TABLE t1(a TIMESTAMP NOT NULL); +CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES('2001-01-01'); SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; a diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 601fcc116b7..81e52929881 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -2122,7 +2122,7 @@ INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 1 as rnd1 from t1 where rand() > 2; rnd1 DROP TABLE t1; -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; +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; 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); 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; INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); @@ -3753,7 +3753,7 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where DROP TABLE t1; -CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts)); +CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); ANALYZE TABLE t1; Table Op Msg_type Msg_text @@ -4493,7 +4493,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select '2001-01-01' AS `a` from dual where 1 DROP TABLE t1; -CREATE TABLE t1(a TIMESTAMP NOT NULL); +CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES('2001-01-01'); SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; a diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 97ab9173c2d..9dbf6e0c46b 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -2111,7 +2111,7 @@ INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 1 as rnd1 from t1 where rand() > 2; rnd1 DROP TABLE t1; -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; +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; 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); 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; INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); @@ -3742,7 +3742,7 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where DROP TABLE t1; -CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts)); +CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); ANALYZE TABLE t1; Table Op Msg_type Msg_text @@ -4482,7 +4482,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select '2001-01-01' AS `a` from dual where 1 DROP TABLE t1; -CREATE TABLE t1(a TIMESTAMP NOT NULL); +CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES('2001-01-01'); SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; a diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index c281650ecf5..6d1cbb21ac7 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -316,7 +316,7 @@ type_numeric numeric(5,2), empty_char char(0), type_char char(2), type_varchar varchar(10), -type_timestamp timestamp not null, +type_timestamp timestamp not null default current_timestamp on update current_timestamp, type_date date not null default '0000-00-00', type_time time not null default '00:00:00', type_datetime datetime not null default '0000-00-00 00:00:00', diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index 4fdac6b9cea..d71748bd9b1 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -144,7 +144,7 @@ ERROR 42000: Variable 'sql_mode' can't be set to the value of 'NULL' set session sql_mode=ansi; create table t1 (f1 integer auto_increment primary key, -f2 timestamp default current_timestamp on update current_timestamp); +f2 timestamp not null default current_timestamp on update current_timestamp); show create table t1; Table Create Table t1 CREATE TABLE "t1" ( diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 66a4225a710..9dcd5975411 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1267,7 +1267,7 @@ d 2000-10-01 drop table t1; set @@sql_mode='traditional'; -create table t1(a int, b timestamp); +create table t1(a int, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); alter table t1 add primary key(a); show create table t1; Table Create Table @@ -1277,7 +1277,7 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; -create table t1(a int, b timestamp default 20050102030405); +create table t1(a int, b timestamp not null default 20050102030405); alter table t1 add primary key(a); show create table t1; Table Create Table diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 011855ca2d1..a05ba88d175 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -57,7 +57,7 @@ select * from t1; t 0000-00-00 00:00:00 drop table t1; -CREATE TABLE t1 (a timestamp, b date, c time, d datetime); +CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b date, c time, d datetime); insert into t1 (b,c,d) values(now(),curtime(),now()); Warnings: Note 1265 Data truncated for column 'b' at row 1 @@ -229,7 +229,7 @@ ERROR 42000: Invalid default value for 'da' create table t1 (t time default '916:00:00 a'); ERROR 42000: Invalid default value for 't' set @@sql_mode= @org_mode; -create table t1 (f1 date, f2 datetime, f3 timestamp); +create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1(f1) values(curdate()); select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1; curdate() < now() f1 < now() cast(f1 as date) < now() @@ -266,7 +266,7 @@ f1 f2 f1 > f2 f1 = f2 f1 < f2 2001-04-15 2001-04-15 00:00:00 0 1 0 2001-05-20 2001-05-20 01:01:01 0 0 1 drop table t1; -create table t1 (f1 date, f2 datetime, f3 timestamp); +create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01'); insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01'); insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01'); diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 7f42c6d343e..c998c28ebd9 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -15,7 +15,7 @@ ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL, umedium mediumint(8) unsigned DEFAULT '0' NOT NULL, ulong int(11) unsigned DEFAULT '0' NOT NULL, ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL, -time_stamp timestamp, +time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, date_field date, time_field time, date_time datetime, diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 95e89e22f2b..fcdef627ded 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -1,6 +1,6 @@ drop table if exists t1,t2; set time_zone="+03:00"; -CREATE TABLE t1 (a int, t timestamp); +CREATE TABLE t1 (a int, t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE t2 (a int, t datetime); SET TIMESTAMP=1234; insert into t1 values(1,NULL); @@ -27,7 +27,7 @@ a t 9 1970-01-01 03:20:38 drop table t1,t2; SET TIMESTAMP=1234; -CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id)); +CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id)); INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00"); SELECT stamp FROM t1 WHERE id="myKey"; stamp @@ -41,13 +41,13 @@ SELECT stamp FROM t1 WHERE id="myKey"; stamp 1999-04-02 00:00:00 drop table t1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (now()); select date_format(a,"%Y %y"),year(a),year(now()) from t1; date_format(a,"%Y %y") year(a) year(now()) 1970 70 1970 1970 drop table t1; -create table t1 (ix timestamp); +create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000); select ix+0 from t1; ix+0 @@ -69,7 +69,7 @@ ix+0 19990630232922 19990601000000 drop table t1; -CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp); +CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959); INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000); INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959); @@ -97,7 +97,7 @@ date date_time time_stamp 2005-01-01 2005-01-01 00:00:00 2005-01-01 00:00:00 2030-01-01 2030-01-01 00:00:00 2030-01-01 00:00:00 drop table t1; -create table t1 (ix timestamp); +create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000); Warnings: Warning 1265 Data truncated for column 'ix' at row 2 @@ -157,7 +157,7 @@ create table t1 (t1 timestamp default now(), t2 timestamp on update now()); drop table t1; create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now()); drop table t1; -create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp); +create table t1 (t1 timestamp not null default '2003-01-01 00:00:00', t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000000; insert into t1 values (); SET TIMESTAMP=1000000001; @@ -181,7 +181,7 @@ t1 timestamp NO 2003-01-01 00:00:00 t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; -create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp); +create table t1 (t1 timestamp not null default now(), t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000002; insert into t1 values (); SET TIMESTAMP=1000000003; @@ -205,7 +205,7 @@ t1 timestamp NO CURRENT_TIMESTAMP t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; -create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime); +create table t1 (t1 timestamp not null default '2003-01-01 00:00:00' on update now(), t2 datetime); SET TIMESTAMP=1000000004; insert into t1 values (); select * from t1; @@ -230,7 +230,7 @@ Field Type Null Key Default Extra t1 timestamp NO 2003-01-01 00:00:00 on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; -create table t1 (t1 timestamp default now() on update now(), t2 datetime); +create table t1 (t1 timestamp not null default now() on update now(), t2 datetime); SET TIMESTAMP=1000000006; insert into t1 values (); select * from t1; @@ -255,7 +255,7 @@ Field Type Null Key Default Extra t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; -create table t1 (t1 timestamp, t2 datetime, t3 timestamp); +create table t1 (t1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000007; insert into t1 values (); select * from t1; @@ -282,7 +282,7 @@ t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; -create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime); +create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp, t2 datetime); SET TIMESTAMP=1000000009; insert into t1 values (); select * from t1; @@ -318,7 +318,7 @@ select * from t1; t1 t2 2004-04-03 00:00:00 2004-04-01 00:00:00 drop table t1; -create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int); +create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int); insert into t1 values (1, '2004-04-01 00:00:00', 10); SET TIMESTAMP=1000000013; replace into t1 set pk = 1, bulk= 20; @@ -326,7 +326,7 @@ select * from t1; pk t1 bulk 1 2001-09-09 04:46:53 20 drop table t1; -create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int); +create table t1 (pk int primary key, t1 timestamp not null default '2003-01-01 00:00:00' on update current_timestamp, bulk int); insert into t1 values (1, '2004-04-01 00:00:00', 10); SET TIMESTAMP=1000000014; replace into t1 set pk = 1, bulk= 20; @@ -334,7 +334,7 @@ select * from t1; pk t1 bulk 1 2003-01-01 00:00:00 20 drop table t1; -create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int); +create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int); insert into t1 values (1, '2004-04-01 00:00:00', 10); SET TIMESTAMP=1000000015; replace into t1 set pk = 1, bulk= 20; @@ -342,7 +342,7 @@ select * from t1; pk t1 bulk 1 2001-09-09 04:46:55 20 drop table t1; -create table t1 (t1 timestamp default current_timestamp on update current_timestamp); +create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp); insert into t1 values ('2004-04-01 00:00:00'); SET TIMESTAMP=1000000016; alter table t1 add i int default 10; @@ -397,13 +397,13 @@ drop table t1; create table t1 (a bigint, b bigint); insert into t1 values (NULL, NULL), (20030101000000, 20030102000000); set timestamp=1000000019; -alter table t1 modify a timestamp, modify b timestamp; +alter table t1 modify a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modify b timestamp NOT NULL DEFAULT '0000-00-00 00:00:0'; select * from t1; a b 2001-09-09 04:46:59 2001-09-09 04:46:59 2003-01-01 00:00:00 2003-01-02 00:00:00 drop table t1; -create table t1 (a char(2), t timestamp); +create table t1 (a char(2), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'), ('b', '2004-02-01 00:00:00'); select max(t) from t1 group by a; @@ -421,7 +421,7 @@ t1 CREATE TABLE "t1" ( ) set sql_mode=''; drop table t1; -create table t1 (a int auto_increment primary key, b int, c timestamp); +create table t1 (a int auto_increment primary key, b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'), (2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03'); select * from t1; @@ -486,8 +486,8 @@ is_nullable NO drop table t1; CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, -f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -f3 TIMESTAMP); +f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, +f3 TIMESTAMP NOT NULL default '0000-00-00 00:00:00'); INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00"); INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL); INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL)); @@ -507,7 +507,7 @@ End of 5.0 tests # Bug #55779: select does not work properly in mysql server # Version "5.1.42 SUSE MySQL RPM" # -CREATE TABLE t1 (a TIMESTAMP, KEY (a)); +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY (a)); INSERT INTO t1 VALUES ('2000-01-01 00:00:00'), ('2000-01-01 00:00:00'), ('2000-01-01 00:00:01'), ('2000-01-01 00:00:01'); SELECT a FROM t1 WHERE a >= 20000101000000; @@ -604,7 +604,7 @@ Warning 1292 Incorrect datetime value: 'abc' Bug#50888 valgrind warnings in Field_timestamp::val_str SET TIMESTAMP=0; -CREATE TABLE t1(a timestamp); +CREATE TABLE t1(a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES ('2008-02-23 09:23:45'), ('2010-03-05 11:08:02'); FLUSH TABLES t1; SELECT MAX(a) FROM t1; diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result index 21e72b98146..573ebbeb709 100644 --- a/mysql-test/r/type_timestamp_hires.result +++ b/mysql-test/r/type_timestamp_hires.result @@ -50,7 +50,7 @@ a 2010-12-11 03:04:05.789 2010-12-11 15:47:11.123 drop table t1; -create table t1 (a timestamp(4)) engine=innodb; +create table t1 (a timestamp(4)NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4)) engine=innodb; insert t1 values ('2010-12-11 01:02:03.456789'); select * from t1; a @@ -216,7 +216,7 @@ decimal5_f4_timestamp 2010-11-12 11:14:17.7654 bigint_f5_timestamp 2010-11-12 11:14:17.00000 varchar_f6_timestamp 2010-11-12 11:14:17.765432 drop table t1, t2; -create table t1 (a timestamp(6), b timestamp(6)); +create table t1 (a timestamp(6)NOT NULL DEFAULT '0000-00-00 00:00:00.000000', b timestamp(6)NOT NULL DEFAULT '0000-00-00 00:00:00.000000'); create procedure foo(x timestamp, y timestamp(4)) insert into t1 values (x, y); call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); select * from t1; @@ -266,7 +266,7 @@ drop table t1, t2; SET timestamp=DEFAULT; set time_zone='+03:00'; set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456; -create table t1 (a timestamp(5)); +create table t1 (a timestamp(5) DEFAULT CURRENT_TIMESTAMP); insert t1 values (); select * from t1; a diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index d3498c27f13..4711b8983e3 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1342,7 +1342,7 @@ id 5 99 drop table t1; -create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp, f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text); +create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text); create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1; show create table t2; Table Create Table diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result index da48f4b2641..5a8278e898c 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result @@ -107,7 +107,7 @@ CREATE TABLE t1 (c29 DATETIME); INSERT INTO t1 VALUES ('2001-02-03 10:20:30'); DELETE FROM t1 WHERE c29='2001-02-03 10:20:30'; DROP TABLE t1; -CREATE TABLE t1 (c30 TIMESTAMP); +CREATE TABLE t1 (c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES ('2001-02-03 10:20:30'); DELETE FROM t1 WHERE c30='2001-02-03 10:20:30'; DROP TABLE t1; @@ -1675,7 +1675,7 @@ DROP TABLE `t1` /* generated by server */ # at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 SET TIMESTAMP=1000000000/*!*/; -CREATE TABLE t1 (c30 TIMESTAMP) +CREATE TABLE t1 (c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) /*!*/; # at # #010909 4:46:40 server id 1 end_log_pos # GTID 0-1-98 diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result index a5e4d39eb5f..76be60fba18 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result @@ -50,7 +50,7 @@ c27 DECIMAL ZEROFILL, # c28 DATE, c29 DATETIME, -c30 TIMESTAMP, +c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c31 TIME, c32 YEAR, # @@ -2306,7 +2306,7 @@ c27 DECIMAL ZEROFILL, # c28 DATE, c29 DATETIME, -c30 TIMESTAMP, +c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c31 TIME, c32 YEAR, # diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result index 43c6d9c51b7..72184c4a4f5 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result @@ -50,7 +50,7 @@ c27 DECIMAL ZEROFILL, # c28 DATE, c29 DATETIME, -c30 TIMESTAMP, +c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c31 TIME, c32 YEAR, # @@ -2306,7 +2306,7 @@ c27 DECIMAL ZEROFILL, # c28 DATE, c29 DATETIME, -c30 TIMESTAMP, +c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c31 TIME, c32 YEAR, # diff --git a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test index 9609a9af384..0c94d968338 100644 --- a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test +++ b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test @@ -142,7 +142,7 @@ INSERT INTO t1 VALUES ('2001-02-03 10:20:30'); DELETE FROM t1 WHERE c29='2001-02-03 10:20:30'; DROP TABLE t1; -CREATE TABLE t1 (c30 TIMESTAMP); +CREATE TABLE t1 (c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES ('2001-02-03 10:20:30'); DELETE FROM t1 WHERE c30='2001-02-03 10:20:30'; DROP TABLE t1; diff --git a/mysql-test/suite/funcs_1/include/innodb_tb4.inc b/mysql-test/suite/funcs_1/include/innodb_tb4.inc index 104b0e763be..b3e94fce80e 100644 --- a/mysql-test/suite/funcs_1/include/innodb_tb4.inc +++ b/mysql-test/suite/funcs_1/include/innodb_tb4.inc @@ -49,7 +49,7 @@ f217 float(53) unsigned zerofill, f218 date, f219 time, f220 datetime, -f221 timestamp, +f221 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, f222 year, f223 year(3), f224 year(4), diff --git a/mysql-test/suite/funcs_1/include/memory_tb4.inc b/mysql-test/suite/funcs_1/include/memory_tb4.inc index d3bab92af3d..3a4fc861f28 100644 --- a/mysql-test/suite/funcs_1/include/memory_tb4.inc +++ b/mysql-test/suite/funcs_1/include/memory_tb4.inc @@ -49,7 +49,7 @@ f217 float(53) unsigned zerofill, f218 date, f219 time, f220 datetime, -f221 timestamp, +f221 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, f222 year, f223 year(3), f224 year(4), diff --git a/mysql-test/suite/funcs_1/include/myisam_tb4.inc b/mysql-test/suite/funcs_1/include/myisam_tb4.inc index 23fa9af45bb..da934e6d658 100644 --- a/mysql-test/suite/funcs_1/include/myisam_tb4.inc +++ b/mysql-test/suite/funcs_1/include/myisam_tb4.inc @@ -49,7 +49,7 @@ f217 float(53) unsigned zerofill, f218 date, f219 time, f220 datetime, -f221 timestamp, +f221 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, f222 year, f223 year(3), f224 year(4), diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result index de30240b288..1e604cb1378 100644 --- a/mysql-test/suite/funcs_1/r/innodb_func_view.result +++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result @@ -13,7 +13,7 @@ ALTER TABLE t1_values ADD my_binary_30 BINARY(30); ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000); ALTER TABLE t1_values ADD my_datetime DATETIME; ALTER TABLE t1_values ADD my_date DATE; -ALTER TABLE t1_values ADD ts_dummy TIMESTAMP; +ALTER TABLE t1_values ADD ts_dummy TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; ALTER TABLE t1_values ADD my_timestamp TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30'; ALTER TABLE t1_values ADD my_time TIME; ALTER TABLE t1_values ADD my_year YEAR; diff --git a/mysql-test/suite/funcs_1/r/is_columns_innodb.result b/mysql-test/suite/funcs_1/r/is_columns_innodb.result index 085017fb529..fd8989c7667 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_innodb.result +++ b/mysql-test/suite/funcs_1/r/is_columns_innodb.result @@ -250,7 +250,7 @@ f217 float(53) unsigned zerofill, f218 date, f219 time, f220 datetime, -f221 timestamp, +f221 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, f222 year, f223 year(3), f224 year(4), diff --git a/mysql-test/suite/funcs_1/r/is_columns_memory.result b/mysql-test/suite/funcs_1/r/is_columns_memory.result index 7521a9857a6..674abfaefab 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_memory.result +++ b/mysql-test/suite/funcs_1/r/is_columns_memory.result @@ -240,7 +240,7 @@ f217 float(53) unsigned zerofill, f218 date, f219 time, f220 datetime, -f221 timestamp, +f221 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, f222 year, f223 year(3), f224 year(4), diff --git a/mysql-test/suite/funcs_1/r/is_columns_myisam.result b/mysql-test/suite/funcs_1/r/is_columns_myisam.result index 6c2586cc3c1..6ce5f1bd8a0 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_myisam.result +++ b/mysql-test/suite/funcs_1/r/is_columns_myisam.result @@ -262,7 +262,7 @@ f217 float(53) unsigned zerofill, f218 date, f219 time, f220 datetime, -f221 timestamp, +f221 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, f222 year, f223 year(3), f224 year(4), diff --git a/mysql-test/suite/funcs_1/r/is_columns_myisam_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_myisam_embedded.result index 150469a4a2f..d89459a88ac 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_myisam_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_myisam_embedded.result @@ -262,7 +262,7 @@ f217 float(53) unsigned zerofill, f218 date, f219 time, f220 datetime, -f221 timestamp, +f221 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, f222 year, f223 year(3), f224 year(4), diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result index f8b6dc66d8a..a37213fdcaa 100644 --- a/mysql-test/suite/funcs_1/r/memory_func_view.result +++ b/mysql-test/suite/funcs_1/r/memory_func_view.result @@ -14,7 +14,7 @@ ALTER TABLE t1_values ADD my_binary_30 BINARY(30); ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000); ALTER TABLE t1_values ADD my_datetime DATETIME; ALTER TABLE t1_values ADD my_date DATE; -ALTER TABLE t1_values ADD ts_dummy TIMESTAMP; +ALTER TABLE t1_values ADD ts_dummy TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; ALTER TABLE t1_values ADD my_timestamp TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30'; ALTER TABLE t1_values ADD my_time TIME; ALTER TABLE t1_values ADD my_year YEAR; diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result index f8b6dc66d8a..a37213fdcaa 100644 --- a/mysql-test/suite/funcs_1/r/myisam_func_view.result +++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result @@ -14,7 +14,7 @@ ALTER TABLE t1_values ADD my_binary_30 BINARY(30); ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000); ALTER TABLE t1_values ADD my_datetime DATETIME; ALTER TABLE t1_values ADD my_date DATE; -ALTER TABLE t1_values ADD ts_dummy TIMESTAMP; +ALTER TABLE t1_values ADD ts_dummy TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; ALTER TABLE t1_values ADD my_timestamp TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30'; ALTER TABLE t1_values ADD my_time TIME; ALTER TABLE t1_values ADD my_year YEAR; diff --git a/mysql-test/suite/funcs_1/views/func_view.inc b/mysql-test/suite/funcs_1/views/func_view.inc index 5bd30472ec3..64ba69a02bd 100644 --- a/mysql-test/suite/funcs_1/views/func_view.inc +++ b/mysql-test/suite/funcs_1/views/func_view.inc @@ -209,7 +209,7 @@ ALTER TABLE t1_values ADD my_binary_30 BINARY(30); ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000); ALTER TABLE t1_values ADD my_datetime DATETIME; ALTER TABLE t1_values ADD my_date DATE; -ALTER TABLE t1_values ADD ts_dummy TIMESTAMP; +ALTER TABLE t1_values ADD ts_dummy TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; ALTER TABLE t1_values ADD my_timestamp TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30'; ALTER TABLE t1_values ADD my_time TIME; ALTER TABLE t1_values ADD my_year YEAR; diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index 34ed2ef3110..3813cfcda14 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -719,7 +719,8 @@ update t1 set sca_pic="test" where sca_pic is null; delete from t1 where sca_code='pd'; drop table t1; set @a:=now(); -CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb; +CREATE TABLE t1 (a int not null, b timestamp not null default +current_timestamp on update current_timestamp, primary key (a)) engine=innodb; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; a diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index bef13f5f418..2e7306c8e29 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -457,7 +457,8 @@ drop table t1; # Test of opening table twice and timestamps # set @a:=now(); -CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb; +CREATE TABLE t1 (a int not null, b timestamp not null default +current_timestamp on update current_timestamp, primary key (a)) engine=innodb; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; select a from t1 natural join t1 as t2 where b >= @a order by a; diff --git a/mysql-test/suite/maria/ps_maria.result b/mysql-test/suite/maria/ps_maria.result index 573d61fc6bf..9625a4386e2 100644 --- a/mysql-test/suite/maria/ps_maria.result +++ b/mysql-test/suite/maria/ps_maria.result @@ -10,7 +10,8 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp, c16 time, +c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/suite/parts/inc/partition_timestamp.inc b/mysql-test/suite/parts/inc/partition_timestamp.inc index 47db67f04a5..fb1bf391999 100644 --- a/mysql-test/suite/parts/inc/partition_timestamp.inc +++ b/mysql-test/suite/parts/inc/partition_timestamp.inc @@ -1,4 +1,4 @@ -eval create table t1 (a timestamp not null, primary key(a)) engine=$engine +eval create table t1 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine=$engine partition by key (a) ( partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, @@ -12,7 +12,7 @@ delete from t1 where a=19801014030300; select * from t1; drop table t1; -eval create table t2 (a timestamp not null, primary key(a)) engine=$engine +eval create table t2 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine=$engine partition by key (a) partitions 12; show create table t2; insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16'); diff --git a/mysql-test/suite/parts/r/partition_datetime_innodb.result b/mysql-test/suite/parts/r/partition_datetime_innodb.result index 195263b8564..0a5e9775c36 100644 --- a/mysql-test/suite/parts/r/partition_datetime_innodb.result +++ b/mysql-test/suite/parts/r/partition_datetime_innodb.result @@ -1,4 +1,4 @@ -create table t1 (a timestamp not null, primary key(a)) engine='InnoDB' +create table t1 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine='InnoDB' partition by key (a) ( partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, @@ -32,7 +32,7 @@ a 2000-06-15 23:59:00 2020-12-31 12:10:30 drop table t1; -create table t2 (a timestamp not null, primary key(a)) engine='InnoDB' +create table t2 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine='InnoDB' partition by key (a) partitions 12; show create table t2; Table Create Table diff --git a/mysql-test/suite/parts/r/partition_datetime_myisam.result b/mysql-test/suite/parts/r/partition_datetime_myisam.result index c715e0890bb..217fe9ace1d 100644 --- a/mysql-test/suite/parts/r/partition_datetime_myisam.result +++ b/mysql-test/suite/parts/r/partition_datetime_myisam.result @@ -1,4 +1,4 @@ -create table t1 (a timestamp not null, primary key(a)) engine='MyISAM' +create table t1 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine='MyISAM' partition by key (a) ( partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, @@ -32,7 +32,7 @@ a 2000-06-15 23:59:00 2020-12-31 12:10:30 drop table t1; -create table t2 (a timestamp not null, primary key(a)) engine='MyISAM' +create table t2 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine='MyISAM' partition by key (a) partitions 12; show create table t2; Table Create Table diff --git a/mysql-test/suite/rpl/include/hrtime.inc b/mysql-test/suite/rpl/include/hrtime.inc index b49bf0c7792..84af425c909 100644 --- a/mysql-test/suite/rpl/include/hrtime.inc +++ b/mysql-test/suite/rpl/include/hrtime.inc @@ -3,7 +3,7 @@ set time_zone='+03:00'; set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456; -create table t1 (a timestamp(4), b varchar(100), c datetime(2)); +create table t1 (a timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b varchar(100), c datetime(2)); insert t1 (b,c) values (now(6), now(6)); diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result b/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result index 80d69eae6db..c24020eef87 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result @@ -210,7 +210,8 @@ DROP TABLE t8; STOP SLAVE; RESET SLAVE; CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), -d TIMESTAMP, +d TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, e INT NOT NULL, f text not null, g text, diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result b/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result index 73c2a237f05..a8710f0de5b 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result @@ -210,7 +210,8 @@ DROP TABLE t8; STOP SLAVE; RESET SLAVE; CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), -d TIMESTAMP, +d TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP, e INT NOT NULL, f text not null, g text, diff --git a/mysql-test/suite/rpl/r/rpl_hrtime.result b/mysql-test/suite/rpl/r/rpl_hrtime.result index 3b29d339739..697cc7aa7fd 100644 --- a/mysql-test/suite/rpl/r/rpl_hrtime.result +++ b/mysql-test/suite/rpl/r/rpl_hrtime.result @@ -2,7 +2,7 @@ include/master-slave.inc [connection master] set time_zone='+03:00'; set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456; -create table t1 (a timestamp(4), b varchar(100), c datetime(2)); +create table t1 (a timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b varchar(100), c datetime(2)); insert t1 (b,c) values (now(6), now(6)); insert t1 values ('2010-10-10 10:10:10.101010','2010-10-10 10:10:10.101010','2010-10-10 10:10:10.101010'); set timestamp=unix_timestamp('2022-02-02 02:02:02') + 0.654321; @@ -41,7 +41,7 @@ SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/ SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; -create table t1 (a timestamp(4), b varchar(100), c datetime(2)) +create table t1 (a timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b varchar(100), c datetime(2)) /*!*/; BEGIN /*!*/; diff --git a/mysql-test/suite/rpl/r/rpl_hrtime_row.result b/mysql-test/suite/rpl/r/rpl_hrtime_row.result index aaf8b1c2466..458002b0b79 100644 --- a/mysql-test/suite/rpl/r/rpl_hrtime_row.result +++ b/mysql-test/suite/rpl/r/rpl_hrtime_row.result @@ -2,7 +2,7 @@ include/master-slave.inc [connection master] set time_zone='+03:00'; set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456; -create table t1 (a timestamp(4), b varchar(100), c datetime(2)); +create table t1 (a timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b varchar(100), c datetime(2)); insert t1 (b,c) values (now(6), now(6)); insert t1 values ('2010-10-10 10:10:10.101010','2010-10-10 10:10:10.101010','2010-10-10 10:10:10.101010'); set timestamp=unix_timestamp('2022-02-02 02:02:02') + 0.654321; diff --git a/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result b/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result index b02164ac61b..6f685c35901 100644 --- a/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result +++ b/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result @@ -2,16 +2,19 @@ include/master-slave.inc [connection master] use test; CREATE TABLE test.regular_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, -dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, +dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON +UPDATE CURRENT_TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id)) ENGINE='innodb'; CREATE TABLE test.bykey_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, -dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, +dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE +CURRENT_TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id)) ENGINE='innodb' PARTITION BY KEY(id) partitions 5; CREATE TABLE test.byrange_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, -dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, +dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE +CURRENT_TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id)) ENGINE='innodb' PARTITION BY RANGE(id) diff --git a/mysql-test/suite/rpl/r/rpl_multi_engine.result b/mysql-test/suite/rpl/r/rpl_multi_engine.result index 7815fa88c93..34b3789df5f 100644 --- a/mysql-test/suite/rpl/r/rpl_multi_engine.result +++ b/mysql-test/suite/rpl/r/rpl_multi_engine.result @@ -3,7 +3,8 @@ include/master-slave.inc drop table if exists t1; CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT -0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id)); +0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY(id)); alter table t1 engine=myisam; show create table t1; Table Create Table diff --git a/mysql-test/suite/rpl/t/rpl_innodb_bug28430.test b/mysql-test/suite/rpl/t/rpl_innodb_bug28430.test index afc0c2cbd4b..12698263da4 100644 --- a/mysql-test/suite/rpl/t/rpl_innodb_bug28430.test +++ b/mysql-test/suite/rpl/t/rpl_innodb_bug28430.test @@ -14,18 +14,21 @@ let $engine_type= 'innodb'; use test; eval CREATE TABLE test.regular_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, - dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, + dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON + UPDATE CURRENT_TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id)) ENGINE=$engine_type; eval CREATE TABLE test.bykey_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, - dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, + dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE + CURRENT_TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id)) ENGINE=$engine_type PARTITION BY KEY(id) partitions 5; eval CREATE TABLE test.byrange_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, - dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, + dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE + CURRENT_TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id)) ENGINE=$engine_type PARTITION BY RANGE(id) diff --git a/mysql-test/suite/rpl/t/rpl_multi_engine.test b/mysql-test/suite/rpl/t/rpl_multi_engine.test index 27ba3b3648e..704c09355aa 100644 --- a/mysql-test/suite/rpl/t/rpl_multi_engine.test +++ b/mysql-test/suite/rpl/t/rpl_multi_engine.test @@ -13,7 +13,8 @@ drop table if exists t1; CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT -0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id)); +0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY(id)); sync_slave_with_master; diff --git a/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc b/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc new file mode 100644 index 00000000000..4cf3914e60a --- /dev/null +++ b/mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc @@ -0,0 +1,99 @@ +CREATE TABLE t1 (a TIMESTAMP); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +if (`SELECT @@explicit_defaults_for_timestamp=0`) +{ + --error ER_INVALID_DEFAULT + CREATE TABLE t1 (a TIMESTAMP DEFAULT NULL); +} + +if (`SELECT @@explicit_defaults_for_timestamp=1`) +{ + CREATE TABLE t1 (a TIMESTAMP DEFAULT NULL); + SHOW CREATE TABLE t1; + DROP TABLE t1; +} + +CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP DEFAULT '2001-01-01 10:20:30'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT NULL); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '2001-01-01 10:20:30'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +CREATE TABLE t1 (a TIMESTAMP) AS SELECT 1 AS i; +SHOW CREATE TABLE t1; +CREATE TABLE t2 (b TIMESTAMP) AS SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +ALTER TABLE t1 ADD b TIMESTAMP; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_basic.result b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_basic.result new file mode 100644 index 00000000000..d9a9a2909ce --- /dev/null +++ b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_basic.result @@ -0,0 +1,21 @@ +select @@global.explicit_defaults_for_timestamp; +@@global.explicit_defaults_for_timestamp +0 +select @@session.explicit_defaults_for_timestamp; +ERROR HY000: Variable 'explicit_defaults_for_timestamp' is a GLOBAL variable +show global variables like 'explicit_defaults_for_timestamp'; +Variable_name Value +explicit_defaults_for_timestamp OFF +show session variables like 'explicit_defaults_for_timestamp'; +Variable_name Value +explicit_defaults_for_timestamp OFF +select * from information_schema.global_variables where variable_name='explicit_defaults_for_timestamp'; +VARIABLE_NAME VARIABLE_VALUE +EXPLICIT_DEFAULTS_FOR_TIMESTAMP OFF +select * from information_schema.session_variables where variable_name='explicit_defaults_for_timestamp'; +VARIABLE_NAME VARIABLE_VALUE +EXPLICIT_DEFAULTS_FOR_TIMESTAMP OFF +set global explicit_defaults_for_timestamp=true; +ERROR HY000: Variable 'explicit_defaults_for_timestamp' is a read only variable +set session explicit_defaults_for_timestamp=true; +ERROR HY000: Variable 'explicit_defaults_for_timestamp' is a read only variable diff --git a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result new file mode 100644 index 00000000000..cdf612e6db8 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result @@ -0,0 +1,175 @@ +CREATE TABLE t1 (a TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP DEFAULT NULL); +ERROR 42000: Invalid default value for 'a' +CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP DEFAULT '2001-01-01 10:20:30'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT '2001-01-01 10:20:30' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '2001-01-01 10:20:30'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT '2001-01-01 10:20:30' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT '2001-01-01 10:20:30' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP) AS SELECT 1 AS i; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `i` int(1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE TABLE t2 (b TIMESTAMP) AS SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a INT); +ALTER TABLE t1 ADD b TIMESTAMP; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result new file mode 100644 index 00000000000..1c42da57bfc --- /dev/null +++ b/mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_on.result @@ -0,0 +1,180 @@ +CREATE TABLE t1 (a TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP DEFAULT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP DEFAULT '2001-01-01 10:20:30'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT '2001-01-01 10:20:30' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '2001-01-01 10:20:30'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT '2001-01-01 10:20:30' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT '2001-01-01 10:20:30' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL, + `b` timestamp NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL, + `b` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL, + `b` timestamp NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL, + `b` timestamp NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL, + `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL, + `b` timestamp NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL, + `b` timestamp NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL, + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP) AS SELECT 1 AS i; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NULL DEFAULT NULL, + `i` int(1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE TABLE t2 (b TIMESTAMP) AS SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `b` timestamp NULL DEFAULT NULL, + `a` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a INT); +ALTER TABLE t1 ADD b TIMESTAMP; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 1f914394c26..4ba7c06373c 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -793,6 +793,20 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME EXPLICIT_DEFAULTS_FOR_TIMESTAMP +SESSION_VALUE NULL +GLOBAL_VALUE OFF +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE OFF +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE BOOLEAN +VARIABLE_COMMENT This option causes CREATE TABLE to create all TIMESTAMP columns as NULL with DEFAULT NULL attribute, Without this option, TIMESTAMP columns are NOT NULL and have implicit DEFAULT clauses. The old behavior is deprecated. +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST OFF,ON +READ_ONLY YES +COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME EXTERNAL_USER SESSION_VALUE GLOBAL_VALUE NULL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 71b8790a3c1..a562a668a97 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -821,6 +821,20 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME EXPLICIT_DEFAULTS_FOR_TIMESTAMP +SESSION_VALUE NULL +GLOBAL_VALUE OFF +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE OFF +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE BOOLEAN +VARIABLE_COMMENT This option causes CREATE TABLE to create all TIMESTAMP columns as NULL with DEFAULT NULL attribute, Without this option, TIMESTAMP columns are NOT NULL and have implicit DEFAULT clauses. The old behavior is deprecated. +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST OFF,ON +READ_ONLY YES +COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME EXTERNAL_USER SESSION_VALUE GLOBAL_VALUE NULL diff --git a/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_basic.test b/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_basic.test new file mode 100644 index 00000000000..da451aec610 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_basic.test @@ -0,0 +1,26 @@ +# +# show the global and session values; +# +select @@global.explicit_defaults_for_timestamp; + +# +# Test that it's a global-only variable +# +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.explicit_defaults_for_timestamp; + +show global variables like 'explicit_defaults_for_timestamp'; +show session variables like 'explicit_defaults_for_timestamp'; + +--disable_warnings +select * from information_schema.global_variables where variable_name='explicit_defaults_for_timestamp'; +select * from information_schema.session_variables where variable_name='explicit_defaults_for_timestamp'; +--enable_warnings + +# +# show that it's read-only +# +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set global explicit_defaults_for_timestamp=true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set session explicit_defaults_for_timestamp=true; diff --git a/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_off.test b/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_off.test new file mode 100644 index 00000000000..2159b7cb6a8 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_off.test @@ -0,0 +1,8 @@ +--disable_query_log +if (`SELECT @@explicit_defaults_for_timestamp`) +{ + Skip Need explicit-defaults-for-timestamp=off; +} +--enable_query_log + +--source inc/explicit_defaults_for_timestamp.inc diff --git a/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_on-master.opt b/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_on-master.opt new file mode 100644 index 00000000000..b84d51e2ad1 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_on-master.opt @@ -0,0 +1 @@ +--explicit-defaults-for-timestamp=on diff --git a/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_on.test b/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_on.test new file mode 100644 index 00000000000..5c03eaa28de --- /dev/null +++ b/mysql-test/suite/sys_vars/t/explicit_defaults_for_timestamp_on.test @@ -0,0 +1,8 @@ +--disable_query_log +if (!`SELECT @@explicit_defaults_for_timestamp`) +{ + Skip Need explicit-defaults-for-timestamp=on; +} +--enable_query_log + +--source inc/explicit_defaults_for_timestamp.inc diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index e68adf80073..5cd55af831b 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -183,7 +183,7 @@ a b c 0 1 y,n drop table t1,t2; CREATE TABLE t1 ( -ts TIMESTAMP, +ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL ) ENGINE=MyISAM; INSERT INTO t1 (tsv) VALUES (DEFAULT); diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test index 38f86702d70..717dd7775fa 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -183,7 +183,7 @@ drop table t1,t2; # CREATE TABLE t1 ( - ts TIMESTAMP, + ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL ) ENGINE=MyISAM; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 30c93f58e2f..0470e8f3d9d 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -401,7 +401,7 @@ explain t2; select * from t2; drop table t1, t2; -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)); +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)); 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; show create table t2; drop table t1,t2; @@ -1436,7 +1436,7 @@ DROP TABLE IF EXISTS t3; --echo -CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP); +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'); --echo @@ -1448,7 +1448,7 @@ CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0); --echo --error ER_INVALID_DEFAULT -CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP); +CREATE TABLE t2(c1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c2 TIMESTAMP NOT NULL); --echo --echo # -- Check that NULL column still can be created. diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index ff8396fd7fd..2d95dffa17a 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -30,7 +30,7 @@ drop table t1; # Test insert syntax # -create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello", i int); +create table t1 (a int not null auto_increment, primary key (a), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c char(10) default "hello", i int); insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default); select a,t>0,c,i from t1; truncate table t1; diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index 355c5ef53a4..e2697effeb6 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -395,7 +395,7 @@ create table t1 ( c1 int, c2 char(12), c3 varchar(123), - c4 timestamp, + c4 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, index (c1), index i1 (c1), index i2 (c2), diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index a859f39e51d..f5ed65cf7e2 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -15,7 +15,7 @@ drop table if exists t1,t2; select 1, 1.0, -1, "hello", NULL; -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)); +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)); select * from t1; select a b, b c from t1 as t2; drop table t1; @@ -176,7 +176,7 @@ dcol_uns double unsigned, # date/time types date_col date, time_col time, -timestamp_col timestamp, +timestamp_col timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, year_col year, datetime_col datetime, # string types diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 8c620ed2ce4..d54c2ab7c55 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -195,8 +195,8 @@ UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; set sql_safe_updates=0; drop table t1,t2; set timestamp=1038401397; -create table t1 (n int(10) not null primary key, d int(10), t timestamp); -create table t2 (n int(10) not null primary key, d int(10), t timestamp); +create table t1 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); +create table t2 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values(1,1,NULL); insert into t2 values(1,10,NULL),(2,20,NULL); set timestamp=1038000000; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index c3267ad7a40..11652b3bedf 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1080,7 +1080,7 @@ drop table t1; drop table if exists t1; --enable_warnings -create table t1 (`d` timestamp, unique (`d`)); +create table t1 (`d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, unique (`d`)); set time_zone='+00:00'; insert into t1 values ('2003-10-25 22:00:00'),('2003-10-25 23:00:00'); # results should show two different time values diff --git a/mysql-test/t/ps_4heap.test b/mysql-test/t/ps_4heap.test index f16d4599a74..2c0b64b1503 100644 --- a/mysql-test/t/ps_4heap.test +++ b/mysql-test/t/ps_4heap.test @@ -31,7 +31,8 @@ eval create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), - c13 date, c14 datetime, c15 timestamp, c16 time, + c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP + ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 varchar(100), c24 varchar(100), c25 varchar(100), c26 varchar(100), c27 varchar(100), c28 varchar(100), diff --git a/mysql-test/t/ps_5merge.test b/mysql-test/t/ps_5merge.test index e6ce9bf42d3..adef6b51f0e 100644 --- a/mysql-test/t/ps_5merge.test +++ b/mysql-test/t/ps_5merge.test @@ -31,7 +31,7 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), - c13 date, c14 datetime, c15 timestamp, c16 time, + c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -62,7 +62,7 @@ create table t9 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), - c13 date, c14 datetime, c15 timestamp, c16 time, + c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c16 time, c17 year, c18 tinyint, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 2d75dc48f73..b207db042e6 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1814,7 +1814,7 @@ DROP TABLE t1; # Test of bug with SUM(CASE...) # -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; +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; 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); 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; INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); @@ -3260,7 +3260,7 @@ DROP TABLE t1; # # Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison # -CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts)); +CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); ANALYZE TABLE t1; @@ -3832,7 +3832,7 @@ SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; DROP TABLE t1; -CREATE TABLE t1(a TIMESTAMP NOT NULL); +CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES('2001-01-01'); SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index d7bfb3a973e..d9aed952fce 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -170,7 +170,7 @@ type_numeric numeric(5,2), empty_char char(0), type_char char(2), type_varchar varchar(10), -type_timestamp timestamp not null, +type_timestamp timestamp not null default current_timestamp on update current_timestamp, type_date date not null default '0000-00-00', type_time time not null default '00:00:00', type_datetime datetime not null default '0000-00-00 00:00:00', diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index b46da77554a..869bf45dbf3 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -93,7 +93,7 @@ set @@SQL_MODE=NULL; set session sql_mode=ansi; create table t1 (f1 integer auto_increment primary key, - f2 timestamp default current_timestamp on update current_timestamp); + f2 timestamp not null default current_timestamp on update current_timestamp); show create table t1; set session sql_mode=no_field_options; show create table t1; diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index b1d36447e00..93b31499bef 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -1132,11 +1132,11 @@ drop table t1; # set @@sql_mode='traditional'; -create table t1(a int, b timestamp); +create table t1(a int, b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); alter table t1 add primary key(a); show create table t1; drop table t1; -create table t1(a int, b timestamp default 20050102030405); +create table t1(a int, b timestamp not null default 20050102030405); alter table t1 add primary key(a); show create table t1; drop table t1; diff --git a/mysql-test/t/system_mysql_db_fix50117.test b/mysql-test/t/system_mysql_db_fix50117.test index 2b01934edea..dcc765ae132 100644 --- a/mysql-test/t/system_mysql_db_fix50117.test +++ b/mysql-test/t/system_mysql_db_fix50117.test @@ -45,9 +45,9 @@ CREATE TABLE IF NOT EXISTS plugin ( name char(64) binary DEFAULT '' NOT NULL, dl CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table'; -CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp, Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Table privileges'; +CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Table privileges'; -CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Column privileges'; +CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Column privileges'; CREATE TABLE IF NOT EXISTS help_topic ( help_topic_id int unsigned not null, name char(64) not null, help_category_id smallint unsigned not null, description text not null, example text not null, url char(128) not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help topics'; @@ -77,12 +77,12 @@ CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsign CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; -CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp, modified timestamp, sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures'; +CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures'; -CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; +CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; -CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL, modified TIMESTAMP NOT NULL, last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'; +CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'; # Run the mysql_fix_privilege_tables.sql using "mysql --force" --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index b417c298837..9d049d0c6e7 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -32,7 +32,7 @@ drop table t1; # Test insert of now() and curtime() # -CREATE TABLE t1 (a timestamp, b date, c time, d datetime); +CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b date, c time, d datetime); insert into t1 (b,c,d) values(now(),curtime(),now()); select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1; drop table t1; @@ -182,7 +182,7 @@ if (`SELECT CURTIME() > SEC_TO_TIME(24 * 3600 - 5)`) # So a sleep time of 5 seconds brings us between '00:00:01' and '00:00:04'. --real_sleep 5 } -create table t1 (f1 date, f2 datetime, f3 timestamp); +create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1(f1) values(curdate()); select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1; delete from t1; @@ -201,7 +201,7 @@ drop table t1; # # Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function. # -create table t1 (f1 date, f2 datetime, f3 timestamp); +create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01'); insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01'); insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01'); diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test index 4a897c1e440..44b0835fcae 100644 --- a/mysql-test/t/type_ranges.test +++ b/mysql-test/t/type_ranges.test @@ -22,7 +22,7 @@ CREATE TABLE t1 ( umedium mediumint(8) unsigned DEFAULT '0' NOT NULL, ulong int(11) unsigned DEFAULT '0' NOT NULL, ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL, - time_stamp timestamp, + time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, date_field date, time_field time, date_time datetime, diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index 97b95d66077..6188ed6b4d6 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -9,7 +9,7 @@ drop table if exists t1,t2; # Set timezone to GMT-3, to make it possible to use "interval 3 hour" set time_zone="+03:00"; -CREATE TABLE t1 (a int, t timestamp); +CREATE TABLE t1 (a int, t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE t2 (a int, t datetime); SET TIMESTAMP=1234; insert into t1 values(1,NULL); @@ -27,7 +27,7 @@ select * from t1; drop table t1,t2; SET TIMESTAMP=1234; -CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id)); +CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id)); INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00"); SELECT stamp FROM t1 WHERE id="myKey"; UPDATE t1 SET value="my value" WHERE id="myKey"; @@ -36,12 +36,12 @@ UPDATE t1 SET id="myKey" WHERE value="my value"; SELECT stamp FROM t1 WHERE id="myKey"; drop table t1; -create table t1 (a timestamp); +create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (now()); select date_format(a,"%Y %y"),year(a),year(now()) from t1; drop table t1; -create table t1 (ix timestamp); +create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000); select ix+0 from t1; truncate table t1; @@ -49,7 +49,7 @@ insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),( select ix+0 from t1; drop table t1; -CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp); +CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959); INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000); INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959); @@ -71,7 +71,7 @@ drop table t1; # Let us check if we properly treat wrong datetimes and produce proper warnings # (for both strings and numbers) # -create table t1 (ix timestamp); +create table t1 (ix timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000); select ix+0 from t1; truncate table t1; @@ -100,7 +100,7 @@ drop table t1; # Let us test TIMESTAMP auto-update behaviour # Also we will test behaviour of TIMESTAMP field in SHOW CREATE TABLE and # behaviour of DEFAULT literal for such fields -create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp); +create table t1 (t1 timestamp not null default '2003-01-01 00:00:00', t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000000; insert into t1 values (); SET TIMESTAMP=1000000001; @@ -112,7 +112,7 @@ show create table t1; show columns from t1; drop table t1; -create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp); +create table t1 (t1 timestamp not null default now(), t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000002; insert into t1 values (); SET TIMESTAMP=1000000003; @@ -124,7 +124,7 @@ show create table t1; show columns from t1; drop table t1; -create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime); +create table t1 (t1 timestamp not null default '2003-01-01 00:00:00' on update now(), t2 datetime); SET TIMESTAMP=1000000004; insert into t1 values (); select * from t1; @@ -137,7 +137,7 @@ show create table t1; show columns from t1; drop table t1; -create table t1 (t1 timestamp default now() on update now(), t2 datetime); +create table t1 (t1 timestamp not null default now() on update now(), t2 datetime); SET TIMESTAMP=1000000006; insert into t1 values (); select * from t1; @@ -150,7 +150,7 @@ show create table t1; show columns from t1; drop table t1; -create table t1 (t1 timestamp, t2 datetime, t3 timestamp); +create table t1 (t1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000007; insert into t1 values (); select * from t1; @@ -166,7 +166,7 @@ drop table t1; # Let us test if CURRENT_TIMESTAMP also works well as default value # (Of course NOW and CURRENT_TIMESTAMP are same for parser but still just # for demonstartion.) -create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime); +create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp, t2 datetime); SET TIMESTAMP=1000000009; insert into t1 values (); select * from t1; @@ -196,21 +196,21 @@ drop table t1; # Now let us test replace it should behave exactly like delete+insert # Case where optimization is possible DEFAULT = ON UPDATE -create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int); +create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int); insert into t1 values (1, '2004-04-01 00:00:00', 10); SET TIMESTAMP=1000000013; replace into t1 set pk = 1, bulk= 20; select * from t1; drop table t1; # Case in which there should not be optimisation -create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int); +create table t1 (pk int primary key, t1 timestamp not null default '2003-01-01 00:00:00' on update current_timestamp, bulk int); insert into t1 values (1, '2004-04-01 00:00:00', 10); SET TIMESTAMP=1000000014; replace into t1 set pk = 1, bulk= 20; select * from t1; drop table t1; # Other similar case -create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int); +create table t1 (pk int primary key, t1 timestamp not null default current_timestamp on update current_timestamp, bulk int); insert into t1 values (1, '2004-04-01 00:00:00', 10); SET TIMESTAMP=1000000015; replace into t1 set pk = 1, bulk= 20; @@ -218,7 +218,7 @@ select * from t1; drop table t1; # Let us test alter now -create table t1 (t1 timestamp default current_timestamp on update current_timestamp); +create table t1 (t1 timestamp not null default current_timestamp on update current_timestamp); insert into t1 values ('2004-04-01 00:00:00'); SET TIMESTAMP=1000000016; alter table t1 add i int default 10; @@ -266,7 +266,7 @@ drop table t1; create table t1 (a bigint, b bigint); insert into t1 values (NULL, NULL), (20030101000000, 20030102000000); set timestamp=1000000019; -alter table t1 modify a timestamp, modify b timestamp; +alter table t1 modify a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modify b timestamp NOT NULL DEFAULT '0000-00-00 00:00:0'; select * from t1; drop table t1; @@ -274,7 +274,7 @@ drop table t1; # Test for bug #4131, TIMESTAMP columns missing minutes and seconds when # using GROUP BY in @@new=1 mode. # -create table t1 (a char(2), t timestamp); +create table t1 (a char(2), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'), ('b', '2004-02-01 00:00:00'); select max(t) from t1 group by a; @@ -295,7 +295,7 @@ drop table t1; # # Bug#7806 - insert on duplicate key and auto-update of timestamp # -create table t1 (a int auto_increment primary key, b int, c timestamp); +create table t1 (a int auto_increment primary key, b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'), (2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03'); select * from t1; @@ -333,8 +333,8 @@ drop table t1; # CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, - f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - f3 TIMESTAMP); + f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + f3 TIMESTAMP NOT NULL default '0000-00-00 00:00:00'); INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00"); INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL); INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL)); @@ -351,7 +351,7 @@ DROP TABLE t1; --echo # Version "5.1.42 SUSE MySQL RPM" --echo # -CREATE TABLE t1 (a TIMESTAMP, KEY (a)); +CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY (a)); INSERT INTO t1 VALUES ('2000-01-01 00:00:00'), ('2000-01-01 00:00:00'), ('2000-01-01 00:00:01'), ('2000-01-01 00:00:01'); @@ -421,7 +421,7 @@ SELECT UNIX_TIMESTAMP('abc'); --echo SET TIMESTAMP=0; -CREATE TABLE t1(a timestamp); +CREATE TABLE t1(a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t1 VALUES ('2008-02-23 09:23:45'), ('2010-03-05 11:08:02'); FLUSH TABLES t1; SELECT MAX(a) FROM t1; diff --git a/mysql-test/t/type_timestamp_hires.test b/mysql-test/t/type_timestamp_hires.test index 17a2c3e1f1f..0a49056294f 100644 --- a/mysql-test/t/type_timestamp_hires.test +++ b/mysql-test/t/type_timestamp_hires.test @@ -5,7 +5,7 @@ let type=timestamp; set time_zone='+03:00'; set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456; -create table t1 (a timestamp(5)); +create table t1 (a timestamp(5) DEFAULT CURRENT_TIMESTAMP); # # CREATE ... DEFAULT NOW(X) # diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index b8b040b0d0d..87f88570c3b 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -847,7 +847,7 @@ drop table t1; # # Bug#12185: Data type aggregation may produce wrong result # -create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp, f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text); +create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text); create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1; show create table t2; drop table t1, t2; diff --git a/sql/field.cc b/sql/field.cc index bacab1f79f4..5ba155d1315 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -10017,13 +10017,22 @@ bool Create_field::check(THD *thd) /* Set NO_DEFAULT_VALUE_FLAG if this field doesn't have a default value and - it is NOT NULL, not an AUTO_INCREMENT field and not a TIMESTAMP. + it is NOT NULL, not an AUTO_INCREMENT field. We need to do this check here and in mysql_create_prepare_table() as sp_head::fill_field_definition() calls this function. */ - if (!def && unireg_check == Field::NONE && - (flags & NOT_NULL_FLAG) && !is_timestamp_type(sql_type)) - flags|= NO_DEFAULT_VALUE_FLAG; + if (!def && unireg_check == Field::NONE && (flags & NOT_NULL_FLAG)) + { + /* + TIMESTAMP columns get implicit DEFAULT value when + explicit_defaults_for_timestamp is not set. + */ + if (opt_explicit_defaults_for_timestamp || + !is_timestamp_type(sql_type)) + { + flags|= NO_DEFAULT_VALUE_FLAG; + } + } if (!(flags & BLOB_FLAG) && ((length > max_field_charlength && diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 8e11b4b6caf..d953a606fb9 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -438,6 +438,7 @@ my_bool opt_show_slave_auth_info; my_bool opt_log_slave_updates= 0; my_bool opt_replicate_annotate_row_events= 0; my_bool opt_mysql56_temporal_format=0, strict_password_validation= 1; +my_bool opt_explicit_defaults_for_timestamp= 0; char *opt_slave_skip_errors; /* @@ -7583,7 +7584,6 @@ struct my_option my_long_options[]= MYSQL_COMPATIBILITY_OPTION("log-bin-use-v1-row-events"), MYSQL_TO_BE_IMPLEMENTED_OPTION("default-authentication-plugin"), MYSQL_COMPATIBILITY_OPTION("binlog-max-flush-queue-time"), - MYSQL_TO_BE_IMPLEMENTED_OPTION("explicit-defaults-for-timestamp"), MYSQL_COMPATIBILITY_OPTION("master-info-repository"), MYSQL_COMPATIBILITY_OPTION("relay-log-info-repository"), MYSQL_SUGGEST_ANALOG_OPTION("binlog-rows-query-log-events", "--binlog-annotate-row-events"), @@ -9371,6 +9371,16 @@ static int get_options(int *argc_ptr, char ***argv_ptr) global_system_variables.max_allowed_packet); } + /* + TIMESTAMP columns get implicit DEFAULT values when + --explicit_defaults_for_timestamp is not set. + */ + if (!opt_help && !opt_explicit_defaults_for_timestamp) + sql_print_warning("TIMESTAMP with implicit DEFAULT value is deprecated. " + "Please use --explicit_defaults_for_timestamp server " + "option (see documentation for more details)."); + + if (log_error_file_ptr != disabled_my_option) opt_error_log= 1; else diff --git a/sql/mysqld.h b/sql/mysqld.h index bf1af3afb94..103c334cd08 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -787,6 +787,7 @@ extern my_bool opt_stack_trace; extern my_bool opt_expect_abort; extern my_bool opt_slave_sql_verify_checksum; extern my_bool opt_mysql56_temporal_format, strict_password_validation; +extern my_bool opt_explicit_defaults_for_timestamp; extern ulong binlog_checksum_options; extern bool max_user_connections_checking; extern ulong opt_binlog_dbug_fsync_sleep; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 1c4452eaf97..01f7bdb6b68 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -3932,7 +3932,8 @@ static TABLE *create_table_from_items(THD *thd, tmp_table.maybe_null= 0; tmp_table.in_use= thd; - promote_first_timestamp_column(&alter_info->create_list); + if (!opt_explicit_defaults_for_timestamp) + promote_first_timestamp_column(&alter_info->create_list); while ((item=it++)) { diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 61120970ecb..d3a72533ec8 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4995,7 +4995,9 @@ bool mysql_create_table(THD *thd, TABLE_LIST *create_table, else create_table_mode= C_ASSISTED_DISCOVERY; - promote_first_timestamp_column(&alter_info->create_list); + if (!opt_explicit_defaults_for_timestamp) + promote_first_timestamp_column(&alter_info->create_list); + if (mysql_create_table_no_lock(thd, db, table_name, create_info, alter_info, &is_trans, create_table_mode) > 0) { @@ -8534,7 +8536,9 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name, } set_table_default_charset(thd, create_info, alter_ctx.db); - promote_first_timestamp_column(&alter_info->create_list); + + if (!opt_explicit_defaults_for_timestamp) + promote_first_timestamp_column(&alter_info->create_list); #ifdef WITH_PARTITION_STORAGE_ENGINE if (fast_alter_partition) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 29a46e21fbf..082a7fa299f 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -6359,8 +6359,10 @@ field_type: { /* Unlike other types TIMESTAMP fields are NOT NULL by default. + Unless --explicit-defaults-for-timestamp is given. */ - Lex->last_field->flags|= NOT_NULL_FLAG; + if (!opt_explicit_defaults_for_timestamp) + Lex->last_field->flags|= NOT_NULL_FLAG; $$= opt_mysql56_temporal_format ? MYSQL_TYPE_TIMESTAMP2 : MYSQL_TYPE_TIMESTAMP; } diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index d52316a8fab..682d8039478 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -542,6 +542,17 @@ static Sys_var_mybool Sys_binlog_direct( CMD_LINE(OPT_ARG), DEFAULT(FALSE), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(binlog_direct_check)); + +static Sys_var_mybool Sys_explicit_defaults_for_timestamp( + "explicit_defaults_for_timestamp", + "This option causes CREATE TABLE to create all TIMESTAMP columns " + "as NULL with DEFAULT NULL attribute, Without this option, " + "TIMESTAMP columns are NOT NULL and have implicit DEFAULT clauses. " + "The old behavior is deprecated.", + READ_ONLY GLOBAL_VAR(opt_explicit_defaults_for_timestamp), + CMD_LINE(OPT_ARG), DEFAULT(FALSE), NO_MUTEX_GUARD, NOT_IN_BINLOG); + + static Sys_var_ulonglong Sys_bulk_insert_buff_size( "bulk_insert_buffer_size", "Size of tree cache used in bulk " "insert optimisation. Note that this is a limit per thread!", diff --git a/storage/connect/mysql-test/connect/r/mysql_new.result b/storage/connect/mysql-test/connect/r/mysql_new.result index 327afa34446..309d69abe7e 100644 --- a/storage/connect/mysql-test/connect/r/mysql_new.result +++ b/storage/connect/mysql-test/connect/r/mysql_new.result @@ -178,7 +178,7 @@ DROP TABLE t1; # # Testing temporal data types # -CREATE TABLE t1 (a date, b datetime, c time, d timestamp, e year); +CREATE TABLE t1 (a date, b datetime, c time, d timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, e year); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/storage/connect/mysql-test/connect/t/mysql_new.test b/storage/connect/mysql-test/connect/t/mysql_new.test index c93f0407ca4..de9cae7b87b 100644 --- a/storage/connect/mysql-test/connect/t/mysql_new.test +++ b/storage/connect/mysql-test/connect/t/mysql_new.test @@ -304,7 +304,7 @@ DROP TABLE t1; --echo # Testing temporal data types --echo # -CREATE TABLE t1 (a date, b datetime, c time, d timestamp, e year); +CREATE TABLE t1 (a date, b datetime, c time, d timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, e year); SHOW CREATE TABLE t1; INSERT INTO t1 VALUES('2003-05-27 10:45:23','2003-05-27 10:45:23','2003-05-27 10:45:23','2003-05-27 10:45:23','2003-05-27 10:45:23'); SELECT * FROM t1; diff --git a/storage/test_sql_discovery/mysql-test/sql_discovery/simple.result b/storage/test_sql_discovery/mysql-test/sql_discovery/simple.result index ec87efea11c..94f3bd1ed87 100644 --- a/storage/test_sql_discovery/mysql-test/sql_discovery/simple.result +++ b/storage/test_sql_discovery/mysql-test/sql_discovery/simple.result @@ -106,7 +106,7 @@ drop table t1; set @@test_sql_discovery_statement='t1: create table t1 ( a int not null default 5 primary key, - b timestamp, + b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c tinyblob, d decimal(5,2), e varchar(30) character set ascii, diff --git a/storage/test_sql_discovery/mysql-test/sql_discovery/simple.test b/storage/test_sql_discovery/mysql-test/sql_discovery/simple.test index 2b5364c9982..90eea753a38 100644 --- a/storage/test_sql_discovery/mysql-test/sql_discovery/simple.test +++ b/storage/test_sql_discovery/mysql-test/sql_discovery/simple.test @@ -99,7 +99,7 @@ drop table t1; set @@test_sql_discovery_statement='t1: create table t1 ( a int not null default 5 primary key, - b timestamp, + b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c tinyblob, d decimal(5,2), e varchar(30) character set ascii, diff --git a/storage/tokudb/mysql-test/tokudb/r/type_datetime.result b/storage/tokudb/mysql-test/tokudb/r/type_datetime.result index 77364b90f9d..678be1655e4 100644 --- a/storage/tokudb/mysql-test/tokudb/r/type_datetime.result +++ b/storage/tokudb/mysql-test/tokudb/r/type_datetime.result @@ -59,7 +59,7 @@ select * from t1; t 0000-00-00 00:00:00 drop table t1; -CREATE TABLE t1 (a timestamp, b date, c time, d datetime); +CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, b date, c time, d datetime); insert into t1 (b,c,d) values(now(),curtime(),now()); Warnings: Note 1265 Data truncated for column 'b' at row 1 diff --git a/storage/tokudb/mysql-test/tokudb/r/type_timestamp.result b/storage/tokudb/mysql-test/tokudb/r/type_timestamp.result index 9154c84d5a7..c412620173c 100644 --- a/storage/tokudb/mysql-test/tokudb/r/type_timestamp.result +++ b/storage/tokudb/mysql-test/tokudb/r/type_timestamp.result @@ -1,7 +1,7 @@ SET DEFAULT_STORAGE_ENGINE='tokudb'; drop table if exists t1,t2; set time_zone="+03:00"; -CREATE TABLE t1 (a int, t timestamp); +CREATE TABLE t1 (a int, t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP); CREATE TABLE t2 (a int, t datetime); SET TIMESTAMP=1234; insert into t1 values(1,NULL); @@ -178,7 +178,7 @@ create table t1 (t1 timestamp default now(), t2 timestamp on update now()); drop table t1; create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now()); drop table t1; -create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp); +create table t1 (t1 timestamp NOT NULL DEFAULT '2003-01-01 00:00:00', t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000000; insert into t1 values (); SET TIMESTAMP=1000000001; @@ -202,7 +202,7 @@ t1 timestamp NO 2003-01-01 00:00:00 t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; -create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp); +create table t1 (t1 timestamp NOT NULL DEFAULT now(), t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000002; insert into t1 values (); SET TIMESTAMP=1000000003; @@ -226,7 +226,7 @@ t1 timestamp NO CURRENT_TIMESTAMP t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; -create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime); +create table t1 (t1 timestamp NOT NULL DEFAULT '2003-01-01 00:00:00' on update now(), t2 datetime); SET TIMESTAMP=1000000004; insert into t1 values (); select * from t1; @@ -251,7 +251,7 @@ Field Type Null Key Default Extra t1 timestamp NO 2003-01-01 00:00:00 on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; -create table t1 (t1 timestamp default now() on update now(), t2 datetime); +create table t1 (t1 timestamp NOT NULL DEFAULT now() on update now(), t2 datetime); SET TIMESTAMP=1000000006; insert into t1 values (); select * from t1; @@ -276,7 +276,7 @@ Field Type Null Key Default Extra t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; -create table t1 (t1 timestamp, t2 datetime, t3 timestamp); +create table t1 (t1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000007; insert into t1 values (); select * from t1; @@ -303,7 +303,7 @@ t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; -create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime); +create table t1 (t1 timestamp NOT NULL DEFAULT current_timestamp on update current_timestamp, t2 datetime); SET TIMESTAMP=1000000009; insert into t1 values (); select * from t1; @@ -418,7 +418,7 @@ drop table t1; create table t1 (a bigint, b bigint); insert into t1 values (NULL, NULL), (20030101000000, 20030102000000); set timestamp=1000000019; -alter table t1 modify a timestamp, modify b timestamp; +alter table t1 modify a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, modify b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP; select * from t1; a b 2001-09-09 04:46:59 2001-09-09 04:46:59 @@ -442,7 +442,7 @@ t1 CREATE TABLE "t1" ( ) set sql_mode=''; drop table t1; -create table t1 (a int auto_increment primary key, b int, c timestamp); +create table t1 (a int auto_increment primary key, b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'), (2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03'); select * from t1; diff --git a/storage/tokudb/mysql-test/tokudb/t/type_datetime.test b/storage/tokudb/mysql-test/tokudb/t/type_datetime.test index 0dd489e3d5c..1a6b2ed7142 100644 --- a/storage/tokudb/mysql-test/tokudb/t/type_datetime.test +++ b/storage/tokudb/mysql-test/tokudb/t/type_datetime.test @@ -33,7 +33,7 @@ drop table t1; # Test insert of now() and curtime() # -CREATE TABLE t1 (a timestamp, b date, c time, d datetime); +CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, b date, c time, d datetime); insert into t1 (b,c,d) values(now(),curtime(),now()); select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1; drop table t1; diff --git a/storage/tokudb/mysql-test/tokudb/t/type_timestamp.test b/storage/tokudb/mysql-test/tokudb/t/type_timestamp.test index 99d759d64bc..031825cfbff 100644 --- a/storage/tokudb/mysql-test/tokudb/t/type_timestamp.test +++ b/storage/tokudb/mysql-test/tokudb/t/type_timestamp.test @@ -10,7 +10,7 @@ drop table if exists t1,t2; # Set timezone to GMT-3, to make it possible to use "interval 3 hour" set time_zone="+03:00"; -CREATE TABLE t1 (a int, t timestamp); +CREATE TABLE t1 (a int, t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP); CREATE TABLE t2 (a int, t datetime); SET TIMESTAMP=1234; insert into t1 values(1,NULL); @@ -116,7 +116,7 @@ drop table t1; # Let us test TIMESTAMP auto-update behaviour # Also we will test behaviour of TIMESTAMP field in SHOW CREATE TABLE and # behaviour of DEFAULT literal for such fields -create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp); +create table t1 (t1 timestamp NOT NULL DEFAULT '2003-01-01 00:00:00', t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000000; insert into t1 values (); SET TIMESTAMP=1000000001; @@ -128,7 +128,7 @@ show create table t1; show columns from t1; drop table t1; -create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp); +create table t1 (t1 timestamp NOT NULL DEFAULT now(), t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000002; insert into t1 values (); SET TIMESTAMP=1000000003; @@ -140,7 +140,7 @@ show create table t1; show columns from t1; drop table t1; -create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime); +create table t1 (t1 timestamp NOT NULL DEFAULT '2003-01-01 00:00:00' on update now(), t2 datetime); SET TIMESTAMP=1000000004; insert into t1 values (); select * from t1; @@ -153,7 +153,7 @@ show create table t1; show columns from t1; drop table t1; -create table t1 (t1 timestamp default now() on update now(), t2 datetime); +create table t1 (t1 timestamp NOT NULL DEFAULT now() on update now(), t2 datetime); SET TIMESTAMP=1000000006; insert into t1 values (); select * from t1; @@ -166,7 +166,7 @@ show create table t1; show columns from t1; drop table t1; -create table t1 (t1 timestamp, t2 datetime, t3 timestamp); +create table t1 (t1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, t2 datetime, t3 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'); SET TIMESTAMP=1000000007; insert into t1 values (); select * from t1; @@ -182,7 +182,7 @@ drop table t1; # Let us test if CURRENT_TIMESTAMP also works well as default value # (Of course NOW and CURRENT_TIMESTAMP are same for parser but still just # for demonstartion.) -create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime); +create table t1 (t1 timestamp NOT NULL DEFAULT current_timestamp on update current_timestamp, t2 datetime); SET TIMESTAMP=1000000009; insert into t1 values (); select * from t1; @@ -282,7 +282,7 @@ drop table t1; create table t1 (a bigint, b bigint); insert into t1 values (NULL, NULL), (20030101000000, 20030102000000); set timestamp=1000000019; -alter table t1 modify a timestamp, modify b timestamp; +alter table t1 modify a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, modify b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP; select * from t1; drop table t1; @@ -311,7 +311,7 @@ drop table t1; # # Bug#7806 - insert on duplicate key and auto-update of timestamp # -create table t1 (a int auto_increment primary key, b int, c timestamp); +create table t1 (a int auto_increment primary key, b int, c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'), (2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03'); select * from t1; |