I've got a table, Where i want both firstmod and lastmod

firstmod must be the timestamp of then your row was produced. lastmod must update its self to the present time.

This is exactly what i've to date..

CREATE  TABLE IF NOT EXISTS `SiteGen_JamesM_Live`.`PAGES` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `USER_ID` INT(11) NOT NULL ,
  `TITLE` VARCHAR(100) NOT NULL ,
  `HTML` LONGTEXT NOT NULL ,
  `FIRSTMOD` TIMESTAMP NOT NULL DEFAULT 0 ,
  `LASTMOD` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (`ID`) )
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = latin1

Which is things i get in exchange:

Executing SQL script in server
ERROR: Error 1067: Invalid default value for 'FIRSTMOD'

I'll risk responding to despite the fact that I am not too acquainted with MySQL.

I suppose the lastmod column ought to be defined:

 `LASTMOD` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP ,

with no DEFAULT specify. It's default can come with the ON UPDATE clause.

EDIT:

Oops, should not risk this stuff. In the manual:

It's not possible to achieve the current timestamp function as the default value for just one column and also the auto-update value for an additional column.

There might be just one column with CURRENT_TIMESTAMP inside a table. You need to choose, whether it will likely be the main one with DEFAULT (on place) a treadmill with ON UPDATE (or have both modifiers, however in one column).

There's a trick to possess both, consider it's undocumented, it is also hard to rely on (really I am unsure whether it still works).

See more here: http://dev.mysql.com/doc/refman/5.5/en/timestamp.html

For just one TIMESTAMP column inside a table, you are able to assign the present timestamp because the default value and also the auto-update value. You'll be able to possess the current timestamp function as the default value for initializing the column, for that auto-update value, or both. It's not possible to achieve the current timestamp function as the default value for just one column and also the auto-update value for an additional column

Like a workaround, you could utilize a BEFORE Place or BEFORE UPDATE trigger to create one of these simple values.

Do this:

`FIRSTMOD` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ,
`LASTMOD` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

after which inside your card inserts

INSERT INTO PAGES (FIRSTMOD, LASTMOD) VALUES (NULL, NULL)

The FIRSTMOD column should obtain a valid timestamp on place and also the LASTMOD should auto update after that.

do this

CREATE TABLE IF NOT EXISTS `PAGES` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `USER_ID` INT(11) NOT NULL ,
  `TITLE` VARCHAR(100) NOT NULL ,
  `HTML` LONGTEXT NOT NULL ,
  `FIRSTMOD` datetime NOT NULL,  
  `LASTMOD` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (`ID`) )
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = latin1 

and hang up a trigger to update FIRSTMOD on place

Create Trigger Insert_PAGES
  After INSERT On PAGES
  For Each Row
  Begin
   set new.FIRSTMOD = now();
  End

This can set time of firstmod = now() once the record is produced and lastmod will update its self to the present time whenever changes are created to the record