WL#1266 "Separate auto-set logic from TIMESTAMP type."

Final version of patch.

Adds support for specifying of DEFAULT NOW() and/or ON UPDATE NOW()
clauses for TIMESTAMP field definition.
Current implementation allows only one such field per table and
uses several unireg types for storing info about this properties of
field. It should be replaced with better implementation when new
.frm format is introduced.


include/mysqld_error.h:
  Added error codes for case when we have more than one column with NOW()
  in DEFAULT or ON UPDATE clauses and for case when we are using ON UPDATE
  clause with wrong type.
mysql-test/r/create.result:
  Added tests for using of DEFAULT NOW() and ON UPDATE NOW() with
  non-TIMESTAMP fields.
mysql-test/r/show_check.result:
  Updated test results to reflect new default look of TIMESTAMP fields
  in SHOW CREATE TABLE.
mysql-test/r/system_mysql_db.result:
  Updated test results to reflect new default look of TIMESTAMP fields
  in SHOW CREATE TABLE.
mysql-test/r/type_ranges.result:
  Updated test results to reflect new default look of TIMESTAMP fields
  in SHOW COLUMNS.
mysql-test/r/type_timestamp.result:
  Added tests for various DEFAULT and ON UPDATE clauses for TIMESTAMP
  fields definitions.
mysql-test/t/create.test:
  Added tests for using of DEFAULT NOW() and ON UPDATE NOW() with
  non-TIMESTAMP fields.
mysql-test/t/type_timestamp.test:
  Added tests for various DEFAULT and ON UPDATE clauses for TIMESTAMP
  fields definitions.
sql/field.cc:
  Added support for various combinations of DEFAULT and ON UPDATE clauses
  for TIMESTAMP field. 
  
  Setting TABLE::timestamp* members for TIMESTAMP fields with auto-set 
  option taking into account their unireg type (which corresponds to 
  various DEFAULT/ON UPDATE values combinations). Replaced 
  TABLE::time_stamp with TABLE::timestamp_default_now/on_update_now
  couple moved their setup to separate method set_timestamp_offsets(),
  which now is called from  open_table instead of Field_timestamp cons.
sql/field.h:
  Added more unireg types for handling of DEFAULT NOW() and ON UPDATE
  NOW() for TIMESTAMP fields.
  Fixed value corresponding to DEFAULT item for TIMESTAMP field.
sql/ha_berkeley.cc:
  Now TIMESTAMP column with auto-set property could be updated during
  INSERT or/and UPDATE independently.
sql/ha_heap.cc:
  Now TIMESTAMP column with auto-set property could be updated during
  INSERT or/and UPDATE independently.
sql/ha_innodb.cc:
  Now TIMESTAMP column with auto-set property could be updated during
  INSERT or/and UPDATE independently.
sql/ha_isam.cc:
  Now TIMESTAMP column with auto-set property could be updated during
  INSERT or/and UPDATE independently.
sql/ha_isammrg.cc:
  Now TIMESTAMP column with auto-set property could be updated during
  INSERT or/and UPDATE independently.
sql/ha_myisam.cc:
  Now TIMESTAMP column with auto-set property could be updated during
  INSERT or/and UPDATE independently.
sql/ha_myisammrg.cc:
  Now TIMESTAMP column with auto-set property could be updated during
  INSERT or/and UPDATE independently.
sql/item_func.h:
  We need to distinguish NOW() from other function for using in 
  DEFAULT and in ON UPDATE clauses.
sql/item_timefunc.h:
  We need to distinguish NOW() from other function for using in 
  DEFAULT and in ON UPDATE clauses.
sql/mysql_priv.h:
  Added parameter for ON UPDATE value to add_field_to_list() function.
sql/share/czech/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/danish/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/dutch/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/english/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/estonian/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/french/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/german/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/greek/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/hungarian/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/italian/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/japanese/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/korean/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/norwegian-ny/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/norwegian/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/polish/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/portuguese/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/romanian/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/russian/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/serbian/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/slovak/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/spanish/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/swedish/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/share/ukrainian/errmsg.txt:
  Added error messages for case when we have more than one column with
  NOW() (CURRENT_TIMESTAMP is just more standard alias) in DEFAULT or 
  ON UPDATE clauses and for case when we are using ON UPDATE clause with
  wrong type.
sql/sql_base.cc:
  Added setup of TABLE::timestamp_default_now/on_update_now pair
  for each statement to open_table().
sql/sql_insert.cc:
  Using TABLE::timestamp_default_now/on_update_now pair instead of
  old TABLE::time_stamp. Added check for case then REPLACE could not
  be converted to UPDATE because of different DEFAULT/ON UPDATE values
  for TIMESTAMP field.
sql/sql_lex.h:
  Added member for value used in ON UPDATE clause to st_lex.
sql/sql_load.cc:
  Using TABLE::timestamp_default_now/on_update_now pair instead of
  old TABLE::time_stamp. We don't need to restore these members
  since they are set up for each statement in open_table().
sql/sql_parse.cc:
  Added handling of DEFAULT NOW() and ON UPDATE NOW() clauses for
  TIMESTAMP fields to add_field_to_list() function.
sql/sql_show.cc:
  Added support for DEFAULT CURRENT_TIMESTAMP (aka NOW() ) and 
  ON UPDATE CURRENT_TIMESTAMP to SHOW CREATE TABLE and SHOW COLUMNS.
sql/sql_table.cc:
  mysql_create_table() function - added check for number of TIMESTAMP 
    fields with auto-set values and replacing of old style TIMESTAMPs
    with their newer analogs.
  mysql_alter_table(): Using TABLE::timestamp_default_now/on_update_now 
    pair instead of old TABLE::time_stamp. We don't need to restore these
    members since they are set up for each statement in open_table().
sql/sql_update.cc:
  Left only setting of TABLE::timestamp_default_now/on_update_now
  to 0 since they should be already set up in open_table().
sql/sql_yacc.yy:
  Added support for DEFAULT NOW() and ON UPDATE NOW() in field
  definitions.
sql/table.h:
  Replaced TABLE::time_stamp withTABLE::timestamp_default_now/timestamp_on_update_now
  pair which allows to distinguish TIMESTAMP's with various DEFAULT/ON UPDATE
  clauses and optimize checks if TIMESTAMP field should be set to NOW()
  in handlers.
sql/unireg.cc:
  Now we are marking only TIMESTAMP fields with NOW() as default or
  as on update value as special field for unireg.

46 files changed, 335 insertions, 114 deletions

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;