diff options
54 files changed, 677 insertions, 169 deletions
diff --git a/include/mysqld_error.h b/include/mysqld_error.h index 77164e637cb..6ae6111a8bb 100644 --- a/include/mysqld_error.h +++ b/include/mysqld_error.h @@ -309,4 +309,6 @@ #define ER_OPTION_PREVENTS_STATEMENT 1290 #define ER_DUPLICATED_VALUE_IN_TYPE 1291 #define ER_TRUNCATED_WRONG_VALUE 1292 -#define ER_ERROR_MESSAGES 293 +#define ER_TOO_MUCH_AUTO_TIMESTAMP_COLS 1293 +#define ER_INVALID_ON_UPDATE 1294 +#define ER_ERROR_MESSAGES 295 diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 2405c34b9fb..ce72c353d20 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -44,6 +44,12 @@ create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ERROR 42000: Incorrect table name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int); ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long +create table test (a datetime default now()); +ERROR 42000: Invalid default value for 'a' +create table test (a datetime on update now()); +ERROR HY000: Invalid ON UPDATE clause for 'a' field +create table test (a int default 100 auto_increment); +ERROR 42000: Invalid default value for 'a' create table 1ea10 (1a20 int,1e int); insert into 1ea10 values(1,1); select 1ea10.1a20,1e+ 1e+10 from 1ea10; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 3693d92693f..5a5eb025353 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -225,7 +225,7 @@ t1 CREATE TABLE `t1` ( `empty_char` char(0) default NULL, `type_char` char(2) default NULL, `type_varchar` varchar(10) default NULL, - `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/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index d6d9aa1546b..d53ace261bf 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -103,7 +103,7 @@ tables_priv CREATE TABLE `tables_priv` ( `User` char(16) character set latin1 collate latin1_bin NOT NULL default '', `Table_name` char(64) character set latin1 collate latin1_bin NOT NULL default '', `Grantor` char(77) NOT NULL default '', - `Timestamp` timestamp 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') NOT NULL default '', `Column_priv` set('Select','Insert','Update','References') NOT NULL default '', PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`), @@ -117,7 +117,7 @@ columns_priv CREATE TABLE `columns_priv` ( `User` char(16) character set latin1 collate latin1_bin NOT NULL default '', `Table_name` char(64) character set latin1 collate latin1_bin NOT NULL default '', `Column_name` char(64) character set latin1 collate latin1_bin NOT NULL default '', - `Timestamp` timestamp NOT NULL, + `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `Column_priv` set('Select','Insert','Update','References') NOT NULL default '', PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Column privileges' diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 4a3206005dd..8d80f342c1c 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -54,7 +54,7 @@ ushort smallint(5) unsigned zerofill NULL MUL 00000 select,insert,update,refer umedium mediumint(8) unsigned NULL MUL 0 select,insert,update,references ulong int(11) unsigned NULL MUL 0 select,insert,update,references ulonglong bigint(13) unsigned NULL MUL 0 select,insert,update,references -time_stamp timestamp NULL YES NULL select,insert,update,references +time_stamp timestamp NULL YES CURRENT_TIMESTAMP select,insert,update,references date_field date NULL YES NULL select,insert,update,references time_field time NULL YES NULL select,insert,update,references date_time datetime NULL YES NULL select,insert,update,references @@ -222,7 +222,7 @@ ushort smallint(5) unsigned zerofill NULL 00000 select,insert,update,referenc umedium mediumint(8) unsigned NULL MUL 0 select,insert,update,references ulong int(11) unsigned NULL MUL 0 select,insert,update,references ulonglong bigint(13) unsigned NULL MUL 0 select,insert,update,references -time_stamp timestamp NULL YES NULL select,insert,update,references +time_stamp timestamp NULL YES CURRENT_TIMESTAMP select,insert,update,references date_field varchar(10) latin1_swedish_ci YES NULL select,insert,update,references time_field time NULL YES NULL select,insert,update,references date_time datetime NULL YES NULL select,insert,update,references @@ -248,7 +248,7 @@ ushort smallint(5) unsigned zerofill NULL 00000 select,insert,update,referenc umedium mediumint(8) unsigned NULL 0 select,insert,update,references ulong int(11) unsigned NULL 0 select,insert,update,references ulonglong bigint(13) unsigned NULL 0 select,insert,update,references -time_stamp timestamp NULL YES NULL select,insert,update,references +time_stamp timestamp NULL YES 0000-00-00 00:00:00 select,insert,update,references date_field varchar(10) latin1_swedish_ci YES NULL select,insert,update,references time_field time NULL YES NULL select,insert,update,references date_time datetime NULL YES NULL select,insert,update,references diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 72640a4a802..976b3e72385 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -128,48 +128,206 @@ t2 t4 t6 t8 t10 t12 t14 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 drop table t1; -create table t1 (t1 timestamp default '2003-01-01 00:00:00', -t2 timestamp default '2003-01-01 00:00:00'); -set TIMESTAMP=1000000000; -insert into t1 values(); +create table t1 (t1 timestamp, t2 timestamp default now()); +ERROR HY000: Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause +create table t1 (t1 timestamp, t2 timestamp on update now()); +ERROR HY000: Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause +create table t1 (t1 timestamp, t2 timestamp default now() on update now()); +ERROR HY000: Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause +create table t1 (t1 timestamp default now(), t2 timestamp on update now()); +ERROR HY000: Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause +create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now()); +ERROR HY000: Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause +create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp); +SET TIMESTAMP=1000000000; +insert into t1 values (); +SET TIMESTAMP=1000000001; +update t1 set t2=now(); +SET TIMESTAMP=1000000002; +insert into t1 (t1,t3) values (default, default); select * from t1; -t1 t2 -2001-09-09 04:46:40 2003-01-01 00:00:00 +t1 t2 t3 +2003-01-01 00:00:00 2001-09-09 04:46:41 0000-00-00 00:00:00 +2003-01-01 00:00:00 NULL 0000-00-00 00:00:00 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `t1` timestamp NOT NULL, - `t2` timestamp NOT NULL default '2003-01-01 00:00:00' + `t1` timestamp NOT NULL default '2003-01-01 00:00:00', + `t2` datetime default NULL, + `t3` timestamp NOT NULL default '0000-00-00 00:00:00' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp YES NULL -t2 timestamp YES 2003-01-01 00:00:00 -show columns from t1 like 't2'; +t1 timestamp YES 2003-01-01 00:00:00 +t2 datetime YES NULL +t3 timestamp YES 0000-00-00 00:00:00 +drop table t1; +create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp); +SET TIMESTAMP=1000000002; +insert into t1 values (); +SET TIMESTAMP=1000000003; +update t1 set t2=now(); +SET TIMESTAMP=1000000003; +insert into t1 (t1,t3) values (default, default); +select * from t1; +t1 t2 t3 +2001-09-09 04:46:42 2001-09-09 04:46:43 0000-00-00 00:00:00 +2001-09-09 04:46:43 NULL 0000-00-00 00:00:00 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t1` timestamp NOT NULL default CURRENT_TIMESTAMP, + `t2` datetime default NULL, + `t3` timestamp NOT NULL default '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show columns from t1; Field Type Null Key Default Extra -t2 timestamp YES 2003-01-01 00:00:00 -create table t2 (select * from t1); -show create table t2; +t1 timestamp YES CURRENT_TIMESTAMP +t2 datetime YES NULL +t3 timestamp YES 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); +SET TIMESTAMP=1000000004; +insert into t1 values (); +select * from t1; +t1 t2 +2003-01-01 00:00:00 NULL +SET TIMESTAMP=1000000005; +update t1 set t2=now(); +SET TIMESTAMP=1000000005; +insert into t1 (t1) values (default); +select * from t1; +t1 t2 +2001-09-09 04:46:45 2001-09-09 04:46:45 +2003-01-01 00:00:00 NULL +show create table t1; Table Create Table -t2 CREATE TABLE `t2` ( - `t1` timestamp NOT NULL, - `t2` timestamp NOT NULL default '2003-01-01 00:00:00' +t1 CREATE TABLE `t1` ( + `t1` timestamp NOT NULL default '2003-01-01 00:00:00' on update CURRENT_TIMESTAMP, + `t2` datetime default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -alter table t1 add column t0 timestamp first; +show columns from t1; +Field Type Null Key Default Extra +t1 timestamp YES 2003-01-01 00:00:00 +t2 datetime YES NULL +drop table t1; +create table t1 (t1 timestamp default now() on update now(), t2 datetime); +SET TIMESTAMP=1000000006; +insert into t1 values (); +select * from t1; +t1 t2 +2001-09-09 04:46:46 NULL +SET TIMESTAMP=1000000007; +update t1 set t2=now(); +SET TIMESTAMP=1000000007; +insert into t1 (t1) values (default); +select * from t1; +t1 t2 +2001-09-09 04:46:47 2001-09-09 04:46:47 +2001-09-09 04:46:47 NULL show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `t0` timestamp NOT NULL, - `t1` timestamp NOT NULL default '2003-01-01 00:00:00', - `t2` timestamp NOT NULL default '2003-01-01 00:00:00' + `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `t2` datetime default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop table t1,t2; -create table t1 (ts1 timestamp, ts2 timestamp); -set TIMESTAMP=1000000000; +show columns from t1; +Field Type Null Key Default Extra +t1 timestamp YES CURRENT_TIMESTAMP +t2 datetime YES NULL +drop table t1; +create table t1 (t1 timestamp, t2 datetime, t3 timestamp); +SET TIMESTAMP=1000000007; insert into t1 values (); -insert into t1 values (DEFAULT, DEFAULT); select * from t1; -ts1 ts2 -2001-09-09 04:46:40 0000-00-00 00:00:00 -2001-09-09 04:46:40 0000-00-00 00:00:00 +t1 t2 t3 +2001-09-09 04:46:47 NULL 0000-00-00 00:00:00 +SET TIMESTAMP=1000000008; +update t1 set t2=now(); +SET TIMESTAMP=1000000008; +insert into t1 (t1,t3) values (default, default); +select * from t1; +t1 t2 t3 +2001-09-09 04:46:48 2001-09-09 04:46:48 0000-00-00 00:00:00 +2001-09-09 04:46:48 NULL 0000-00-00 00:00:00 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `t2` datetime default NULL, + `t3` timestamp NOT NULL default '0000-00-00 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show columns from t1; +Field Type Null Key Default Extra +t1 timestamp YES CURRENT_TIMESTAMP +t2 datetime YES NULL +t3 timestamp YES 0000-00-00 00:00:00 +drop table t1; +create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime); +SET TIMESTAMP=1000000009; +insert into t1 values (); +select * from t1; +t1 t2 +2001-09-09 04:46:49 NULL +SET TIMESTAMP=1000000010; +update t1 set t2=now(); +SET TIMESTAMP=1000000011; +insert into t1 (t1) values (default); +select * from t1; +t1 t2 +2001-09-09 04:46:50 2001-09-09 04:46:50 +2001-09-09 04:46:51 NULL +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `t2` datetime default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show columns from t1; +Field Type Null Key Default Extra +t1 timestamp YES CURRENT_TIMESTAMP +t2 datetime YES NULL +delete from t1; +insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00'); +SET TIMESTAMP=1000000012; +update t1 set t1= '2004-04-02 00:00:00'; +select * from t1; +t1 t2 +2004-04-02 00:00:00 2004-04-01 00:00:00 +update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00'; +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); +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; +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); +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; +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); +insert into t1 values (1, '2004-04-01 00:00:00', 10); +SET TIMESTAMP=1000000015; +replace into t1 set pk = 1, bulk= 20; +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); +insert into t1 values ('2004-04-01 00:00:00'); +SET TIMESTAMP=1000000016; +alter table t1 add i int default 10; +select * from t1; +t1 i +2004-04-01 00:00:00 10 drop table t1; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 0d9b0ffcb6b..4bd92994530 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -49,6 +49,16 @@ create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int); # +# Some wrong defaults, so these creates should fail too +# +--error 1067 +create table test (a datetime default now()); +--error 1294 +create table test (a datetime on update now()); +--error 1067 +create table test (a int default 100 auto_increment); + +# # test of dummy table names # diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index 425e038956e..72633f9ef7d 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -76,35 +76,146 @@ select * from t1; drop table t1; # -# Bug #1885, bug #2539. -# Not perfect but still sensible attitude towards defaults for TIMESTAMP -# We will ignore default value for first TIMESTAMP column. +# Test for TIMESTAMP column with default now() and on update now() clauses # -create table t1 (t1 timestamp default '2003-01-01 00:00:00', - t2 timestamp default '2003-01-01 00:00:00'); -set TIMESTAMP=1000000000; -insert into t1 values(); + +# These statements should fail. +--error 1293 +create table t1 (t1 timestamp, t2 timestamp default now()); +--error 1293 +create table t1 (t1 timestamp, t2 timestamp on update now()); +--error 1293 +create table t1 (t1 timestamp, t2 timestamp default now() on update now()); +--error 1293 +create table t1 (t1 timestamp default now(), t2 timestamp on update now()); +--error 1293 +create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now()); + +# 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); +SET TIMESTAMP=1000000000; +insert into t1 values (); +SET TIMESTAMP=1000000001; +update t1 set t2=now(); +SET TIMESTAMP=1000000002; +insert into t1 (t1,t3) values (default, default); select * from t1; show create table t1; show columns from t1; -show columns from t1 like 't2'; -create table t2 (select * from t1); -show create table t2; +drop table t1; -# Ugly, but we can't do anything about this in 4.0 -alter table t1 add column t0 timestamp first; +create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp); +SET TIMESTAMP=1000000002; +insert into t1 values (); +SET TIMESTAMP=1000000003; +update t1 set t2=now(); +SET TIMESTAMP=1000000003; +insert into t1 (t1,t3) values (default, default); +select * from t1; show create table t1; +show columns from t1; +drop table t1; -drop table t1,t2; +create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime); +SET TIMESTAMP=1000000004; +insert into t1 values (); +select * from t1; +SET TIMESTAMP=1000000005; +update t1 set t2=now(); +SET TIMESTAMP=1000000005; +insert into t1 (t1) values (default); +select * from t1; +show create table t1; +show columns from t1; +drop table t1; -# -# Test for bug 2464, DEFAULT keyword in INSERT statement should return -# default value for column. -# +create table t1 (t1 timestamp default now() on update now(), t2 datetime); +SET TIMESTAMP=1000000006; +insert into t1 values (); +select * from t1; +SET TIMESTAMP=1000000007; +update t1 set t2=now(); +SET TIMESTAMP=1000000007; +insert into t1 (t1) values (default); +select * from t1; +show create table t1; +show columns from t1; +drop table t1; + +create table t1 (t1 timestamp, t2 datetime, t3 timestamp); +SET TIMESTAMP=1000000007; +insert into t1 values (); +select * from t1; +SET TIMESTAMP=1000000008; +update t1 set t2=now(); +SET TIMESTAMP=1000000008; +insert into t1 (t1,t3) values (default, default); +select * from t1; +show create table t1; +show columns from t1; +drop table t1; -create table t1 (ts1 timestamp, ts2 timestamp); -set TIMESTAMP=1000000000; +# 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); +SET TIMESTAMP=1000000009; insert into t1 values (); -insert into t1 values (DEFAULT, DEFAULT); +select * from t1; +SET TIMESTAMP=1000000010; +update t1 set t2=now(); +SET TIMESTAMP=1000000011; +insert into t1 (t1) values (default); +select * from t1; +show create table t1; +show columns from t1; +delete from t1; + +# +# Let us test some cases when auto-set should be disabled or influence +# on server behavior in some other way. +# + +# Update statement that explicitly sets field should not auto-set it. +insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00'); +SET TIMESTAMP=1000000012; +update t1 set t1= '2004-04-02 00:00:00'; +select * from t1; +# The same for multi updates +update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00'; +select * from t1; +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); +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); +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); +insert into t1 values (1, '2004-04-01 00:00:00', 10); +SET TIMESTAMP=1000000015; +replace into t1 set pk = 1, bulk= 20; +select * from t1; +drop table t1; + +# Let us test alter now +create table t1 (t1 timestamp 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; +select * from t1; +drop table t1; + diff --git a/sql/field.cc b/sql/field.cc index 89c6464c5f0..238d5e36147 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -2842,11 +2842,50 @@ void Field_double::sql_type(String &res) const } -/**************************************************************************** -** timestamp -** The first timestamp in the table is automaticly updated -** by handler.cc. The form->timestamp points at the automatic timestamp. -****************************************************************************/ +/* + TIMESTAMP type. + Holds datetime values in range from 1970-01-01 00:00:01 UTC to + 2038-01-01 00:00:00 UTC stored as number of seconds since Unix + Epoch in UTC. + + Up to one of timestamps columns in the table can be automatically + set on row update and/or have NOW() as default value. + TABLE::timestamp_field points to Field object for such timestamp with + auto-set-on-update. TABLE::time_stamp holds offset in record + 1 for this + field, and is used by handler code which performs updates required. + + Actually SQL-99 says that we should allow niladic functions (like NOW()) + as defaults for any field. Current limitations (only NOW() and only + for one TIMESTAMP field) are because of restricted binary .frm format + and should go away in the future. + + Also because of this limitation of binary .frm format we use 5 different + unireg_check values with TIMESTAMP field to distinguish various cases of + DEFAULT or ON UPDATE values. These values are: + + TIMESTAMP_OLD_FIELD - old timestamp, if there was not any fields with + auto-set-on-update (or now() as default) in this table before, then this + field has NOW() as default and is updated when row changes, else it is + field which has 0 as default value and is not automaitcally updated. + TIMESTAMP_DN_FIELD - field with NOW() as default but not set on update + automatically (TIMESTAMP DEFAULT NOW()) + TIMESTAMP_UN_FIELD - field which is set on update automatically but has not + NOW() as default (but it may has 0 or some other const timestamp as + default) (TIMESTAMP ON UPDATE NOW()). + TIMESTAMP_DNUN_FIELD - field which has now() as default and is auto-set on + update. (TIMESTAMP DEFAULT NOW() ON UPDATE NOW()) + NONE - field which is not auto-set on update with some other than NOW() + default value (TIMESTAMP DEFAULT 0). + + Note that TIMESTAMP_OLD_FIELD's are never created explicitly now, they are + left only for preserving ability to read old tables. Such fields replaced + with their newer analogs in CREATE TABLE and in SHOW CREATE TABLE. This is + because we want to prefer NONE unireg_check before TIMESTAMP_OLD_FIELD for + "TIMESTAMP DEFAULT 'Const'" field. (Old timestamps allowed such + specification too but ignored default value for first timestamp, which of + course is non-standard.) In most cases user won't notice any change, only + exception is different behavior of old/new timestamps during ALTER TABLE. + */ Field_timestamp::Field_timestamp(char *ptr_arg, uint32 len_arg, enum utype unireg_check_arg, @@ -2857,15 +2896,37 @@ Field_timestamp::Field_timestamp(char *ptr_arg, uint32 len_arg, unireg_check_arg, field_name_arg, table_arg, cs) { flags|=ZEROFILL_FLAG; /* 4.0 MYD compatibility */ - if (table && !table->timestamp_field) + if (table && !table->timestamp_field && + unireg_check != NONE) { - table->timestamp_field= this; // Automatic timestamp - table->time_stamp=(ulong) (ptr_arg - (char*) table->record[0])+1; + /* This timestamp has auto-update */ + table->timestamp_field= this; flags|=TIMESTAMP_FLAG; } } +/* + Sets TABLE::timestamp_default_now and TABLE::timestamp_on_update_now + members according to unireg type of this TIMESTAMP field. + + SYNOPSIS + Field_timestamp::set_timestamp_offsets() + +*/ +void Field_timestamp::set_timestamp_offsets() +{ + ulong timestamp= (ulong) (ptr - (char*) table->record[0]) + 1; + + DBUG_ASSERT(table->timestamp_field == this && unireg_check != NONE); + + table->timestamp_default_now= + (unireg_check == TIMESTAMP_UN_FIELD)? 0 : timestamp; + table->timestamp_on_update_now= + (unireg_check == TIMESTAMP_DN_FIELD)? 0 : timestamp; +} + + int Field_timestamp::store(const char *from,uint len,CHARSET_INFO *cs) { long tmp=(long) str_to_timestamp(from,len); diff --git a/sql/field.h b/sql/field.h index 27fe3858fe6..258c18257f0 100644 --- a/sql/field.h +++ b/sql/field.h @@ -52,9 +52,18 @@ public: ulong query_id; // For quick test of used fields /* Field is part of the following keys */ key_map key_start,part_of_key,part_of_sortkey; + /* + We use three additional unireg types for TIMESTAMP to overcome limitation + of current binary format of .frm file. We'd like to be able to support + NOW() as default and on update value for such fields but unable to hold + this info anywhere except unireg_check field. This issue will be resolved + in more clean way with transition to new text based .frm format. + See also comment for Field_timestamp::Field_timestamp(). + */ enum utype { NONE,DATE,SHIELD,NOEMPTY,CASEUP,PNR,BGNR,PGNR,YES,NO,REL, CHECK,EMPTY,UNKNOWN_FIELD,CASEDN,NEXT_NUMBER,INTERVAL_FIELD, - BIT_FIELD, TIMESTAMP_FIELD,CAPITALIZE,BLOB_FIELD}; + BIT_FIELD, TIMESTAMP_OLD_FIELD, CAPITALIZE, BLOB_FIELD, + TIMESTAMP_DN_FIELD, TIMESTAMP_UN_FIELD, TIMESTAMP_DNUN_FIELD}; enum geometry_type { GEOM_GEOMETRY = 0, GEOM_POINT = 1, GEOM_LINESTRING = 2, GEOM_POLYGON = 3, @@ -644,7 +653,8 @@ public: void set_time(); virtual void set_default() { - if (table->timestamp_field == this) + if (table->timestamp_field == this && + unireg_check != TIMESTAMP_UN_FIELD) set_time(); else Field::set_default(); @@ -662,6 +672,7 @@ public: bool get_date(TIME *ltime,uint fuzzydate); bool get_time(TIME *ltime); field_cast_enum field_cast_type() { return FIELD_CAST_TIMESTAMP; } + void set_timestamp_offsets(); }; diff --git a/sql/ha_berkeley.cc b/sql/ha_berkeley.cc index 612a9c33b45..f13261ef52d 100644 --- a/sql/ha_berkeley.cc +++ b/sql/ha_berkeley.cc @@ -826,8 +826,8 @@ int ha_berkeley::write_row(byte * record) DBUG_ENTER("write_row"); statistic_increment(ha_write_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(record+table->time_stamp-1); + if (table->timestamp_default_now) + update_timestamp(record+table->timestamp_default_now-1); if (table->next_number_field && record == table->record[0]) update_auto_increment(); if ((error=pack_row(&row, record,1))) @@ -1073,8 +1073,8 @@ int ha_berkeley::update_row(const byte * old_row, byte * new_row) LINT_INIT(error); statistic_increment(ha_update_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(new_row+table->time_stamp-1); + if (table->timestamp_on_update_now) + update_timestamp(new_row+table->timestamp_on_update_now-1); if (hidden_primary_key) { diff --git a/sql/ha_heap.cc b/sql/ha_heap.cc index c84f0da0d25..94105fb9409 100644 --- a/sql/ha_heap.cc +++ b/sql/ha_heap.cc @@ -65,8 +65,8 @@ int ha_heap::close(void) int ha_heap::write_row(byte * buf) { statistic_increment(ha_write_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(buf+table->time_stamp-1); + if (table->timestamp_default_now) + update_timestamp(buf+table->timestamp_default_now-1); if (table->next_number_field && buf == table->record[0]) update_auto_increment(); return heap_write(file,buf); @@ -75,8 +75,8 @@ int ha_heap::write_row(byte * buf) int ha_heap::update_row(const byte * old_data, byte * new_data) { statistic_increment(ha_update_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(new_data+table->time_stamp-1); + if (table->timestamp_on_update_now) + update_timestamp(new_data+table->timestamp_on_update_now-1); return heap_update(file,old_data,new_data); } diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc index 5d3d2a2fb4f..307bd13885c 100644 --- a/sql/ha_innodb.cc +++ b/sql/ha_innodb.cc @@ -2137,9 +2137,8 @@ ha_innobase::write_row( statistic_increment(ha_write_count, &LOCK_status); - if (table->time_stamp) { - update_timestamp(record + table->time_stamp - 1); - } + if (table->timestamp_default_now) + update_timestamp(record + table->timestamp_default_now - 1); if (last_query_id != user_thd->query_id) { prebuilt->sql_stat_start = TRUE; @@ -2510,9 +2509,8 @@ ha_innobase::update_row( ut_ad(prebuilt->trx == (trx_t*) current_thd->transaction.all.innobase_tid); - if (table->time_stamp) { - update_timestamp(new_row + table->time_stamp - 1); - } + if (table->timestamp_on_update_now) + update_timestamp(new_row + table->timestamp_on_update_now - 1); if (last_query_id != user_thd->query_id) { prebuilt->sql_stat_start = TRUE; diff --git a/sql/ha_isam.cc b/sql/ha_isam.cc index 299d6caaf43..52fea2f7a15 100644 --- a/sql/ha_isam.cc +++ b/sql/ha_isam.cc @@ -70,8 +70,8 @@ uint ha_isam::min_record_length(uint options) const int ha_isam::write_row(byte * buf) { statistic_increment(ha_write_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(buf+table->time_stamp-1); + if (table->timestamp_default_now) + update_timestamp(buf+table->timestamp_default_now-1); if (table->next_number_field && buf == table->record[0]) update_auto_increment(); return !nisam_write(file,buf) ? 0 : my_errno ? my_errno : -1; @@ -80,8 +80,8 @@ int ha_isam::write_row(byte * buf) int ha_isam::update_row(const byte * old_data, byte * new_data) { statistic_increment(ha_update_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(new_data+table->time_stamp-1); + if (table->timestamp_on_update_now) + update_timestamp(new_data+table->timestamp_on_update_now-1); return !nisam_update(file,old_data,new_data) ? 0 : my_errno ? my_errno : -1; } diff --git a/sql/ha_isammrg.cc b/sql/ha_isammrg.cc index 9915c182e26..8f7056a15fa 100644 --- a/sql/ha_isammrg.cc +++ b/sql/ha_isammrg.cc @@ -78,8 +78,8 @@ int ha_isammrg::write_row(byte * buf) int ha_isammrg::update_row(const byte * old_data, byte * new_data) { statistic_increment(ha_update_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(new_data+table->time_stamp-1); + if (table->timestamp_on_update_now) + update_timestamp(new_data+table->timestamp_on_update_now-1); return !mrg_update(file,old_data,new_data) ? 0 : my_errno ? my_errno : -1; } diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc index a2a5f040081..cc8f84c4b00 100644 --- a/sql/ha_myisam.cc +++ b/sql/ha_myisam.cc @@ -253,9 +253,8 @@ int ha_myisam::write_row(byte * buf) statistic_increment(ha_write_count,&LOCK_status); /* If we have a timestamp column, update it to the current time */ - - if (table->time_stamp) - update_timestamp(buf+table->time_stamp-1); + if (table->timestamp_default_now) + update_timestamp(buf+table->timestamp_default_now-1); /* If we have an auto_increment column and we are writing a changed row @@ -937,8 +936,8 @@ bool ha_myisam::is_crashed() const int ha_myisam::update_row(const byte * old_data, byte * new_data) { statistic_increment(ha_update_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(new_data+table->time_stamp-1); + if (table->timestamp_on_update_now) + update_timestamp(new_data+table->timestamp_on_update_now-1); return mi_update(file,old_data,new_data); } diff --git a/sql/ha_myisammrg.cc b/sql/ha_myisammrg.cc index b62c347a7bd..7c36f6c6e0e 100644 --- a/sql/ha_myisammrg.cc +++ b/sql/ha_myisammrg.cc @@ -82,8 +82,8 @@ int ha_myisammrg::close(void) int ha_myisammrg::write_row(byte * buf) { statistic_increment(ha_write_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(buf+table->time_stamp-1); + if (table->timestamp_default_now) + update_timestamp(buf+table->timestamp_default_now-1); if (table->next_number_field && buf == table->record[0]) update_auto_increment(); return myrg_write(file,buf); @@ -92,8 +92,8 @@ int ha_myisammrg::write_row(byte * buf) int ha_myisammrg::update_row(const byte * old_data, byte * new_data) { statistic_increment(ha_update_count,&LOCK_status); - if (table->time_stamp) - update_timestamp(new_data+table->time_stamp-1); + if (table->timestamp_on_update_now) + update_timestamp(new_data+table->timestamp_on_update_now); return myrg_update(file,old_data,new_data); } diff --git a/sql/item_func.h b/sql/item_func.h index 4142498af6c..39c0a47ed7c 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -47,7 +47,7 @@ public: SP_CONTAINS_FUNC,SP_OVERLAPS_FUNC, SP_STARTPOINT,SP_ENDPOINT,SP_EXTERIORRING, SP_POINTN,SP_GEOMETRYN,SP_INTERIORRINGN, - NOT_FUNC, NOT_ALL_FUNC}; + NOT_FUNC, NOT_ALL_FUNC, NOW_FUNC}; enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL }; enum Type type() const { return FUNC_ITEM; } virtual enum Functype functype() const { return UNKNOWN_FUNC; } diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index bfe7795500e..f00eb93e0e5 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -469,6 +469,7 @@ public: Item_func_now_local(Item *a) :Item_func_now(a) {} const char *func_name() const { return "now"; } void store_now_in_tm(time_t now, struct tm *now_tm); + virtual enum Functype functype() const { return NOW_FUNC; } }; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index a979ef137ae..7a5ae167442 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -633,7 +633,7 @@ void set_item_name(Item *item,char *pos,uint length); bool add_field_to_list(THD *thd, char *field_name, enum enum_field_types type, char *length, char *decimal, uint type_modifier, - Item *default_value, + Item *default_value, Item *on_update_value, LEX_STRING *comment, char *change, TYPELIB *interval,CHARSET_INFO *cs, uint uint_geom_type); diff --git a/sql/share/czech/errmsg.txt b/sql/share/czech/errmsg.txt index 21dd24c540b..339c693bb38 100644 --- a/sql/share/czech/errmsg.txt +++ b/sql/share/czech/errmsg.txt @@ -305,3 +305,5 @@ character-set=latin2 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/danish/errmsg.txt b/sql/share/danish/errmsg.txt index 94a2091ed1d..9c870ecb67c 100644 --- a/sql/share/danish/errmsg.txt +++ b/sql/share/danish/errmsg.txt @@ -299,3 +299,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/dutch/errmsg.txt b/sql/share/dutch/errmsg.txt index 967f5c0ece0..18f11253fb5 100644 --- a/sql/share/dutch/errmsg.txt +++ b/sql/share/dutch/errmsg.txt @@ -307,3 +307,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt index fd2e97754b5..2d7f82e36ef 100644 --- a/sql/share/english/errmsg.txt +++ b/sql/share/english/errmsg.txt @@ -296,3 +296,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/estonian/errmsg.txt b/sql/share/estonian/errmsg.txt index 350dff5e4f8..c96f5d1e40c 100644 --- a/sql/share/estonian/errmsg.txt +++ b/sql/share/estonian/errmsg.txt @@ -301,3 +301,5 @@ character-set=latin7 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/french/errmsg.txt b/sql/share/french/errmsg.txt index cba2b2e4264..bb5f48cab56 100644 --- a/sql/share/french/errmsg.txt +++ b/sql/share/french/errmsg.txt @@ -296,3 +296,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt index cc107f80f93..ccc9eace98e 100644 --- a/sql/share/german/errmsg.txt +++ b/sql/share/german/errmsg.txt @@ -308,3 +308,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/greek/errmsg.txt b/sql/share/greek/errmsg.txt index b6a2e9a699c..ff49f887d95 100644 --- a/sql/share/greek/errmsg.txt +++ b/sql/share/greek/errmsg.txt @@ -296,3 +296,5 @@ character-set=greek "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/hungarian/errmsg.txt b/sql/share/hungarian/errmsg.txt index 80c840af081..63925213da2 100644 --- a/sql/share/hungarian/errmsg.txt +++ b/sql/share/hungarian/errmsg.txt @@ -298,3 +298,5 @@ character-set=latin2 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/italian/errmsg.txt b/sql/share/italian/errmsg.txt index e499d1b663c..76e572fd71b 100644 --- a/sql/share/italian/errmsg.txt +++ b/sql/share/italian/errmsg.txt @@ -296,3 +296,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/japanese/errmsg.txt b/sql/share/japanese/errmsg.txt index 75ee30abad9..f7df183269d 100644 --- a/sql/share/japanese/errmsg.txt +++ b/sql/share/japanese/errmsg.txt @@ -298,3 +298,5 @@ character-set=ujis "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/korean/errmsg.txt b/sql/share/korean/errmsg.txt index 43b34cc65dc..e63a5d64c2b 100644 --- a/sql/share/korean/errmsg.txt +++ b/sql/share/korean/errmsg.txt @@ -296,3 +296,5 @@ character-set=euckr "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/norwegian-ny/errmsg.txt b/sql/share/norwegian-ny/errmsg.txt index 49b6577c16a..2d28db39a3c 100644 --- a/sql/share/norwegian-ny/errmsg.txt +++ b/sql/share/norwegian-ny/errmsg.txt @@ -298,3 +298,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/norwegian/errmsg.txt b/sql/share/norwegian/errmsg.txt index bdfcd2dd819..231bcc89822 100644 --- a/sql/share/norwegian/errmsg.txt +++ b/sql/share/norwegian/errmsg.txt @@ -298,3 +298,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/polish/errmsg.txt b/sql/share/polish/errmsg.txt index 8bb089b03c9..0b5269cce80 100644 --- a/sql/share/polish/errmsg.txt +++ b/sql/share/polish/errmsg.txt @@ -300,3 +300,5 @@ character-set=latin2 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/portuguese/errmsg.txt b/sql/share/portuguese/errmsg.txt index 3ccbb48eede..3abe13a04ba 100644 --- a/sql/share/portuguese/errmsg.txt +++ b/sql/share/portuguese/errmsg.txt @@ -297,3 +297,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/romanian/errmsg.txt b/sql/share/romanian/errmsg.txt index 92b347ebe7e..3e1993d8018 100644 --- a/sql/share/romanian/errmsg.txt +++ b/sql/share/romanian/errmsg.txt @@ -300,3 +300,5 @@ character-set=latin2 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt index 25d2c0bb049..943a64ad711 100644 --- a/sql/share/russian/errmsg.txt +++ b/sql/share/russian/errmsg.txt @@ -298,3 +298,5 @@ character-set=koi8r "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/serbian/errmsg.txt b/sql/share/serbian/errmsg.txt index 3c94ec65060..74cc1c9f2e1 100644 --- a/sql/share/serbian/errmsg.txt +++ b/sql/share/serbian/errmsg.txt @@ -290,3 +290,5 @@ character-set=cp1250 "The MySQL server is running with the %s option so it cannot execute this statement" "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/slovak/errmsg.txt b/sql/share/slovak/errmsg.txt index 7889c1edaec..79fe55c1ff1 100644 --- a/sql/share/slovak/errmsg.txt +++ b/sql/share/slovak/errmsg.txt @@ -304,3 +304,5 @@ character-set=latin2 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/spanish/errmsg.txt b/sql/share/spanish/errmsg.txt index db62b0e6cd4..08bcd5933c4 100644 --- a/sql/share/spanish/errmsg.txt +++ b/sql/share/spanish/errmsg.txt @@ -298,3 +298,5 @@ character-set=latin1 "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/swedish/errmsg.txt b/sql/share/swedish/errmsg.txt index 4d069e01b1f..c400c5813f5 100644 --- a/sql/share/swedish/errmsg.txt +++ b/sql/share/swedish/errmsg.txt @@ -293,6 +293,7 @@ character-set=latin1 "Tabel %-.100s använd med '%s' är inte uppdateringsbar", "'%s' är inte aktiverad; För att aktivera detta måste du bygga om MySQL med '%s' definerad", "MySQL är started i --skip-grant-tables mod. Pga av detta kan du inte använda detta program", - "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt index c2d5def0646..e61e479aa66 100644 --- a/sql/share/ukrainian/errmsg.txt +++ b/sql/share/ukrainian/errmsg.txt @@ -301,3 +301,5 @@ character-set=koi8u "The MySQL server is running with the %s option so it cannot execute this statement", "Column '%-.100s' has duplicated value '%-.64s' in %s" "Truncated wrong %-.32s value: '%-.128s'" +"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" +"Invalid ON UPDATE clause for '%-.64s' field", diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 7fc97a14ffd..f3bf0a15745 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -939,6 +939,8 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name, table->status=STATUS_NO_RECORD; table->keys_in_use_for_query= table->keys_in_use; table->used_keys= table->keys_for_keyread; + if (table->timestamp_field) + table->timestamp_field->set_timestamp_offsets(); DBUG_ASSERT(table->key_read == 0); DBUG_RETURN(table); } diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 9f30617e62f..f2764a6f1c0 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -42,9 +42,9 @@ static void unlink_blobs(register TABLE *table); #define DELAYED_LOG_BIN 2 /* - Check if insert fields are correct - Updates table->time_stamp to point to timestamp field or 0, depending on - if timestamp should be updated or not. + Check if insert fields are correct. + Sets table->timestamp_default_now/on_update_now to 0 o leaves it to point + to timestamp field, depending on if timestamp should be updated or not. */ int @@ -65,7 +65,7 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields, check_grant_all_columns(thd,INSERT_ACL,table)) return -1; #endif - table->time_stamp=0; // This is saved by caller + table->timestamp_default_now= table->timestamp_on_update_now= 0; } else { // Part field list @@ -97,10 +97,9 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields, my_error(ER_FIELD_SPECIFIED_TWICE,MYF(0), thd->dupp_field->field_name); return -1; } - table->time_stamp=0; if (table->timestamp_field && // Don't set timestamp if used - table->timestamp_field->query_id != thd->query_id) - table->time_stamp= table->timestamp_field->offset()+1; + table->timestamp_field->query_id == thd->query_id) + table->timestamp_default_now= table->timestamp_on_update_now= 0; } // For the values we need select_priv #ifndef NO_EMBEDDED_ACCESS_CHECKS @@ -551,9 +550,12 @@ int write_record(TABLE *table,COPY_INFO *info) an INSERT or DELETE(s) + INSERT; FOREIGN KEY checks in InnoDB do not function in the defined way if we allow MySQL to convert the latter operation internally to an UPDATE. + We also should not perform this conversion if we have + timestamp field with ON UPDATE which is different from DEFAULT. */ if (last_uniq_key(table,key_nr) && - !table->file->referenced_by_foreign_key()) + !table->file->referenced_by_foreign_key() && + table->timestamp_default_now == table->timestamp_on_update_now) { if ((error=table->file->update_row(table->record[1], table->record[0]))) @@ -629,7 +631,8 @@ public: bool query_start_used,last_insert_id_used,insert_id_used; int log_query; ulonglong last_insert_id; - ulong time_stamp; + ulong timestamp_default_now; + ulong timestamp_on_update_now; uint query_length; delayed_row(enum_duplicates dup_arg, int log_query_arg) @@ -920,9 +923,10 @@ TABLE *delayed_insert::get_local_table(THD* client_thd) if (table->timestamp_field) { /* Restore offset as this may have been reset in handle_inserts */ - copy->time_stamp=table->timestamp_field->offset()+1; copy->timestamp_field= (Field_timestamp*) copy->field[table->timestamp_field_offset]; + copy->timestamp_field->unireg_check= table->timestamp_field->unireg_check; + copy->timestamp_field->set_timestamp_offsets(); } /* _rowid is not used with delayed insert */ @@ -973,7 +977,8 @@ static int write_delayed(THD *thd,TABLE *table,enum_duplicates duplic, row->last_insert_id_used= thd->last_insert_id_used; row->insert_id_used= thd->insert_id_used; row->last_insert_id= thd->last_insert_id; - row->time_stamp= table->time_stamp; + row->timestamp_default_now= table->timestamp_default_now; + row->timestamp_on_update_now= table->timestamp_on_update_now; di->rows.push_back(row); di->stacked_inserts++; @@ -1307,7 +1312,8 @@ bool delayed_insert::handle_inserts(void) thd.last_insert_id=row->last_insert_id; thd.last_insert_id_used=row->last_insert_id_used; thd.insert_id_used=row->insert_id_used; - table->time_stamp=row->time_stamp; + table->timestamp_default_now= row->timestamp_default_now; + table->timestamp_on_update_now= row->timestamp_on_update_now; info.handle_duplicates= row->dup; if (info.handle_duplicates == DUP_IGNORE || @@ -1597,11 +1603,9 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u) /* First field to copy */ field=table->field+table->fields - values.elements; - if (table->timestamp_field) // Don't set timestamp if used - { - table->timestamp_field->set_time(); - table->time_stamp=0; // This should be saved - } + /* Don't set timestamp if used */ + table->timestamp_default_now= table->timestamp_on_update_now= 0; + table->next_number_field=table->found_next_number_field; restore_record(table,default_values); // Get empty record diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b9d85a23011..c86c7d4a81d 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -532,7 +532,7 @@ typedef struct st_lex String *wild; sql_exchange *exchange; select_result *result; - Item *default_value; + Item *default_value, *on_update_value; LEX_STRING *comment, name_and_length; LEX_USER *grant_user; gptr yacc_yyss,yacc_yyvs; diff --git a/sql/sql_load.cc b/sql/sql_load.cc index 58a9b9c588d..e3b32f694da 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -274,9 +274,9 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, if (!(error=test(read_info.error))) { - uint save_time_stamp=table->time_stamp; if (use_timestamp) - table->time_stamp=0; + table->timestamp_default_now= table->timestamp_on_update_now= 0; + table->next_number_field=table->found_next_number_field; VOID(table->file->extra_opt(HA_EXTRA_WRITE_CACHE, thd->variables.read_buff_size)); @@ -297,7 +297,6 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, if (table->file->activate_all_index(thd)) error=1; /* purecov: inspected */ table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); - table->time_stamp=save_time_stamp; table->next_number_field=0; } if (file >= 0) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 68ef195cdc4..aa3e40e1df8 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4001,7 +4001,8 @@ bool mysql_test_parse_for_slave(THD *thd, char *inBuf, uint length) bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, char *length, char *decimals, uint type_modifier, - Item *default_value, LEX_STRING *comment, + Item *default_value, Item *on_update_value, + LEX_STRING *comment, char *change, TYPELIB *interval, CHARSET_INFO *cs, uint uint_geom_type) { @@ -4033,15 +4034,21 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, if (default_value) { - /* + /* Default value should be literal => basic constants => no need fix_fields() - - We allow specifying value for first TIMESTAMP column - altough it is silently ignored. This should be fixed in 4.1 - (by proper warning or real support for default values) + + We allow only one function as part of default value - + NOW() as default for TIMESTAMP type. */ - if (default_value->type() == Item::NULL_ITEM) + if (default_value->type() == Item::FUNC_ITEM && + !(((Item_func*)default_value)->functype() == Item_func::NOW_FUNC && + type == FIELD_TYPE_TIMESTAMP)) + { + net_printf(thd, ER_INVALID_DEFAULT, field_name); + DBUG_RETURN(1); + } + else if (default_value->type() == Item::NULL_ITEM) { default_value=0; if ((type_modifier & (NOT_NULL_FLAG | AUTO_INCREMENT_FLAG)) == @@ -4057,6 +4064,13 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, DBUG_RETURN(1); } } + + if (on_update_value && type != FIELD_TYPE_TIMESTAMP) + { + net_printf(thd, ER_INVALID_ON_UPDATE, field_name); + DBUG_RETURN(1); + } + if (!(new_field=new create_field())) DBUG_RETURN(1); new_field->field=0; @@ -4220,6 +4234,34 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, new_field->length= min(new_field->length,14); /* purecov: inspected */ } new_field->flags|= ZEROFILL_FLAG | UNSIGNED_FLAG | NOT_NULL_FLAG; + if (default_value) + { + /* Grammar allows only NOW() value for ON UPDATE clause */ + if (default_value->type() == Item::FUNC_ITEM && + ((Item_func*)default_value)->functype() == Item_func::NOW_FUNC) + { + new_field->unireg_check= (on_update_value?Field::TIMESTAMP_DNUN_FIELD: + Field::TIMESTAMP_DN_FIELD); + /* + We don't need default value any longer moreover it is dangerous. + Everything handled by unireg_check further. + */ + new_field->def= 0; + } + else + new_field->unireg_check= (on_update_value?Field::TIMESTAMP_UN_FIELD: + Field::NONE); + } + else + { + /* + We are setting TIMESTAMP_OLD_FIELD here only temporary, we will + replace this value by TIMESTAMP_DNUN_FIELD or NONE later when + information about all TIMESTAMP fields in table will be availiable. + */ + new_field->unireg_check= on_update_value?Field::TIMESTAMP_UN_FIELD: + Field::TIMESTAMP_OLD_FIELD; + } break; case FIELD_TYPE_DATE: // Old date type if (protocol_version != PROTOCOL_VERSION-1) diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 30adc06fbd0..f93004976f2 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -699,7 +699,6 @@ mysqld_show_fields(THD *thd, TABLE_LIST *table_list,const char *wild, uint flags=field->flags; String type(tmp,sizeof(tmp), system_charset_info); uint col_access; - bool null_default_value=0; protocol->prepare_for_resend(); protocol->store(field->field_name, system_charset_info); @@ -723,20 +722,24 @@ mysqld_show_fields(THD *thd, TABLE_LIST *table_list,const char *wild, (field->flags & MULTIPLE_KEY_FLAG) ? "MUL":""); protocol->store((char*) pos, system_charset_info); - /* - We handle first TIMESTAMP column in special way because its - default value is ignored and current timestamp used instead. - */ - if (table->timestamp_field == field || - field->unireg_check == Field::NEXT_NUMBER) - null_default_value=1; - if (!null_default_value && !field->is_null()) + if (table->timestamp_field == field && + field->unireg_check != Field::TIMESTAMP_UN_FIELD) + { + /* + We have NOW() as default value but we use CURRENT_TIMESTAMP form + because it is more SQL standard comatible + */ + protocol->store("CURRENT_TIMESTAMP", system_charset_info); + } + else if (field->unireg_check != Field::NEXT_NUMBER && + !field->is_null()) { // Not null by default type.set(tmp, sizeof(tmp), field->charset()); field->val_str(&type,&type); protocol->store(type.ptr(),type.length(),type.charset()); } - else if (field->maybe_null() || null_default_value) + else if (field->unireg_check == Field::NEXT_NUMBER || + field->maybe_null()) protocol->store_null(); // Null as default else protocol->store("",0, system_charset_info); // empty string @@ -1232,6 +1235,7 @@ store_create_info(THD *thd, TABLE *table, String *packet) for (ptr=table->field ; (field= *ptr); ptr++) { bool has_default; + bool has_now_default; uint flags = field->flags; if (ptr != table->field) @@ -1268,14 +1272,25 @@ store_create_info(THD *thd, TABLE *table, String *packet) if (flags & NOT_NULL_FLAG) packet->append(" NOT NULL", 9); + + /* + Again we are using CURRENT_TIMESTAMP instead of NOW becaus eit is + more standard + */ + has_now_default= table->timestamp_field == field && + field->unireg_check != Field::TIMESTAMP_UN_FIELD; + has_default= (field->type() != FIELD_TYPE_BLOB && - table->timestamp_field != field && - field->unireg_check != Field::NEXT_NUMBER); + field->unireg_check != Field::NEXT_NUMBER && + !((foreign_db_mode || limited_mysql_mode) && + has_now_default)); if (has_default) { packet->append(" default ", 9); - if (!field->is_null()) + if (has_now_default) + packet->append("CURRENT_TIMESTAMP",17); + else if (!field->is_null()) { // Not null by default type.set(tmp, sizeof(tmp), field->charset()); field->val_str(&type,&type); @@ -1296,6 +1311,11 @@ store_create_info(THD *thd, TABLE *table, String *packet) packet->append(tmp,0); } + if (!foreign_db_mode && !limited_mysql_mode && + table->timestamp_field == field && + field->unireg_check != Field::TIMESTAMP_DN_FIELD) + packet->append(" on update CURRENT_TIMESTAMP",28); + if (field->unireg_check == Field::NEXT_NUMBER && !foreign_db_mode) packet->append(" auto_increment", 15 ); diff --git a/sql/sql_table.cc b/sql/sql_table.cc index db6f9043ec4..81286cea227 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -427,6 +427,7 @@ int mysql_create_table(THD *thd,const char *db, const char *table_name, KEY *key_info,*key_info_buffer; KEY_PART_INFO *key_part_info; int auto_increment=0; + int timestamps= 0, timestamps_with_niladic= 0; handler *file; int field_no,dup_no; enum db_type new_db_type; @@ -621,8 +622,22 @@ int mysql_create_table(THD *thd,const char *db, const char *table_name, sql_field->pack_flag=f_settype((uint) sql_field->sql_type); break; case FIELD_TYPE_TIMESTAMP: - sql_field->unireg_check=Field::TIMESTAMP_FIELD; - /* fall through */ + /* We should replace old TIMESTAMP fields with their newer analogs */ + if (sql_field->unireg_check == Field::TIMESTAMP_OLD_FIELD) + { + if (!timestamps) + { + sql_field->unireg_check= Field::TIMESTAMP_DNUN_FIELD; + ++timestamps_with_niladic; + } + else + sql_field->unireg_check= Field::NONE; + } + else if(sql_field->unireg_check != Field::NONE) + ++timestamps_with_niladic; + + ++timestamps; + /* fall-through */ default: sql_field->pack_flag=(FIELDFLAG_NUMBER | (sql_field->flags & UNSIGNED_FLAG ? 0 : @@ -640,6 +655,11 @@ int mysql_create_table(THD *thd,const char *db, const char *table_name, auto_increment++; pos+=sql_field->pack_length; } + if (timestamps_with_niladic > 1) + { + my_error(ER_TOO_MUCH_AUTO_TIMESTAMP_COLS,MYF(0)); + DBUG_RETURN(-1); + } if (auto_increment > 1) { my_error(ER_WRONG_AUTO_KEY,MYF(0)); @@ -2100,10 +2120,9 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, char tmp_name[80],old_name[32],new_name_buff[FN_REFLEN]; char new_alias_buff[FN_REFLEN], *table_name, *db, *new_alias, *alias; char index_file[FN_REFLEN], data_file[FN_REFLEN]; - bool use_timestamp=0; ha_rows copied,deleted; ulonglong next_insert_id; - uint save_time_stamp,db_create_options, used_fields; + uint db_create_options, used_fields; enum db_type old_db_type,new_db_type; DBUG_ENTER("mysql_alter_table"); @@ -2321,8 +2340,6 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, if (def) { // Field is changed def->field=field; - if (def->sql_type == FIELD_TYPE_TIMESTAMP) - use_timestamp=1; if (!def->after) { create_list.push_back(def); @@ -2332,9 +2349,6 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, else { // Use old field value create_list.push_back(def=new create_field(field,field)); - if (def->sql_type == FIELD_TYPE_TIMESTAMP) - use_timestamp=1; - alter_it.rewind(); // Change default if ALTER Alter_column *alter; while ((alter=alter_it++)) @@ -2587,9 +2601,13 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, goto err; } - save_time_stamp=new_table->time_stamp; - if (use_timestamp) - new_table->time_stamp=0; + + /* + We don't want update TIMESTAMP fields during ALTER TABLE + and copy_data_between_tables uses only write_row() for new_table so + don't need to set up timestamp_on_update_now member. + */ + new_table->timestamp_default_now= 0; new_table->next_number_field=new_table->found_next_number_field; thd->count_cuted_fields= CHECK_FIELD_WARN; // calc cuted fields thd->cuted_fields=0L; @@ -2602,7 +2620,6 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, order_num, order, &copied, &deleted); thd->last_insert_id=next_insert_id; // Needed for correct log thd->count_cuted_fields= CHECK_FIELD_IGNORE; - new_table->time_stamp=save_time_stamp; if (table->tmp_table) { diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 6242a03867b..1a9906be0cc 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -120,7 +120,6 @@ int mysql_update(THD *thd, { timestamp_query_id=table->timestamp_field->query_id; table->timestamp_field->query_id=thd->query_id-1; - table->time_stamp= table->timestamp_field->offset() +1; } /* Check the fields we are going to modify */ @@ -133,7 +132,7 @@ int mysql_update(THD *thd, { // Don't set timestamp column if this is modified if (table->timestamp_field->query_id == thd->query_id) - table->time_stamp=0; + table->timestamp_on_update_now= 0; else table->timestamp_field->query_id=timestamp_query_id; } @@ -486,13 +485,11 @@ int mysql_multi_update(THD *thd, /* We only need SELECT privilege for columns in the values list */ table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege); - if (table->timestamp_field) - { - table->time_stamp=0; - // Only set timestamp column if this is not modified - if (table->timestamp_field->query_id != thd->query_id) - table->time_stamp= table->timestamp_field->offset() +1; - } + // Only set timestamp column if this is not modified + if (table->timestamp_field && + table->timestamp_field->query_id == thd->query_id) + table->timestamp_on_update_now= 0; + if (tl->derived) derived_tables|= table->map; } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 845f0d42f26..2d713ef5f5e 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -629,7 +629,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); using_list expr_or_default set_expr_or_default interval_expr param_marker singlerow_subselect singlerow_subselect_init exists_subselect exists_subselect_init geometry_function - signed_literal + signed_literal now_or_signed_literal %type <item_num> NUM_literal @@ -1226,7 +1226,7 @@ field_spec: { LEX *lex=Lex; lex->length=lex->dec=0; lex->type=0; lex->interval=0; - lex->default_value=0; + lex->default_value= lex->on_update_value= 0; lex->comment=0; lex->charset=NULL; } @@ -1236,7 +1236,8 @@ field_spec: if (add_field_to_list(lex->thd, $1.str, (enum enum_field_types) $3, lex->length,lex->dec,lex->type, - lex->default_value, lex->comment, + lex->default_value, lex->on_update_value, + lex->comment, lex->change,lex->interval,lex->charset, lex->uint_geom_type)) YYABORT; @@ -1432,7 +1433,9 @@ opt_attribute_list: attribute: NULL_SYM { Lex->type&= ~ NOT_NULL_FLAG; } | NOT NULL_SYM { Lex->type|= NOT_NULL_FLAG; } - | DEFAULT signed_literal { Lex->default_value=$2; } + | DEFAULT now_or_signed_literal { Lex->default_value=$2; } + | ON UPDATE_SYM NOW_SYM optional_braces + { Lex->on_update_value= new Item_func_now_local(); } | AUTO_INC { Lex->type|= AUTO_INCREMENT_FLAG | NOT_NULL_FLAG; } | SERIAL_SYM DEFAULT VALUE_SYM { Lex->type|= AUTO_INCREMENT_FLAG | NOT_NULL_FLAG | UNIQUE_FLAG; } @@ -1456,6 +1459,11 @@ attribute: } ; +now_or_signed_literal: + NOW_SYM optional_braces { $$= new Item_func_now_local(); } + | signed_literal { $$=$1; } + ; + charset: CHAR_SYM SET {} | CHARSET {} @@ -1725,7 +1733,7 @@ alter_list_item: { LEX *lex=Lex; lex->length=lex->dec=0; lex->type=0; lex->interval=0; - lex->default_value=0; + lex->default_value= lex->on_update_value= 0; lex->comment=0; lex->charset= NULL; lex->simple_alter=0; @@ -1736,7 +1744,8 @@ alter_list_item: if (add_field_to_list(lex->thd,$3.str, (enum enum_field_types) $5, lex->length,lex->dec,lex->type, - lex->default_value, lex->comment, + lex->default_value, lex->on_update_value, + lex->comment, $3.str, lex->interval, lex->charset, lex->uint_geom_type)) YYABORT; diff --git a/sql/table.h b/sql/table.h index 039e3ded9f3..537e31a202a 100644 --- a/sql/table.h +++ b/sql/table.h @@ -97,8 +97,20 @@ struct st_table { uint raid_type,raid_chunks; uint status; /* Used by postfix.. */ uint system; /* Set if system record */ - ulong time_stamp; /* Set to offset+1 of record */ + + /* + These two members hold offset in record + 1 for TIMESTAMP field + with NOW() as default value or/and with ON UPDATE NOW() option. + If 0 then such field is absent in this table or auto-set for default + or/and on update should be temporaly disabled for some reason. + These values is setup to offset value for each statement in open_table() + and turned off in statement processing code (see mysql_update as example). + */ + ulong timestamp_default_now; + ulong timestamp_on_update_now; + /* Index of auto-updated TIMESTAMP field in field array */ uint timestamp_field_offset; + uint next_number_index; uint blob_ptr_size; /* 4 or 8 */ uint next_number_key_offset; diff --git a/sql/unireg.cc b/sql/unireg.cc index 6f127b57f64..0e4b449c6a3 100644 --- a/sql/unireg.cc +++ b/sql/unireg.cc @@ -354,8 +354,12 @@ static bool pack_header(uchar *forminfo, enum db_type table_type, MTYP_NOEMPTY_BIT); no_empty++; } - if ((MTYP_TYPENR(field->unireg_check) == Field::TIMESTAMP_FIELD || - f_packtype(field->pack_flag) == (int) FIELD_TYPE_TIMESTAMP) && + /* + We mark first TIMESTAMP field with NOW() in DEFAULT or ON UPDATE + as auto-update field. + */ + if (field->sql_type == FIELD_TYPE_TIMESTAMP && + MTYP_TYPENR(field->unireg_check) != Field::NONE && !time_stamp_pos) time_stamp_pos=(int) field->offset+1; length=field->pack_length; |