1

I'm trying to make a table that has a "created" timestamp and an "updated" timestamp (and have MySQL insert values in there automatically for me). It's doable, see: http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/.

As a first step, I copied the example table from the reference site and modeled it in Workbench. I then did a forward engineer and tested the resulting SQL in phpMyAdmin. Everything worked. Here was the SQL:

-- -----------------------------------------------------
-- Table `test_table`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `test_table` ;

CREATE  TABLE IF NOT EXISTS `test_table` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `stamp_created` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00' ,
  `stamp_updated` TIMESTAMP NULL DEFAULT now() on update now() ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

After verifying that the idea works, I then implemented the concept in one of my actual tables. The resulting SQL was this:

-- -----------------------------------------------------
-- Table `user_login`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `user_login` ;

CREATE  TABLE IF NOT EXISTS `user_login` (
  `user_login_id` INT NOT NULL AUTO_INCREMENT ,
  `user_id` INT NOT NULL ,
  `hashed_password` CHAR(255) NULL ,
  `provider_id` CHAR(255) NULL ,
  `provider_name` CHAR(255) NULL ,
  `unverified_email_address` CHAR(255) NULL ,
  `verification_code_email_address` CHAR(255) NULL ,
  `verification_code_password_change` CHAR(255) NULL ,
  `verified_email_address` CHAR(255) NULL ,
  `stamp_created` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00' ,
  `stamp_updated` TIMESTAMP NULL DEFAULT now() on update now() ,
  `stamp_deleted` TIMESTAMP NULL ,
  PRIMARY KEY (`user_login_id`) ,
  INDEX `user_login_user_id` (`user_id` ASC) ,
  UNIQUE INDEX `verified_email_address_UNIQUE` (`verified_email_address` ASC) ,
  UNIQUE INDEX `unverified_email_address_UNIQUE` (`unverified_email_address` ASC) ,
  CONSTRAINT `user_login_user_id`
    FOREIGN KEY (`user_id` )
    REFERENCES `user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

When I execute the SQL, I get the following error:

#1067 - Invalid default value for 'stamp_created'

Anyone see what's causing my problem?

5
  • I had to remove the FK restraint(for obvious reasons), but it ran fine via MySQL Query Browser for me... peculiar. Even more peculiar after reading the link in bensiu's answer. Commented Nov 24, 2011 at 3:23
  • When I run the SQL, it doesn't complain about the FK constraint (I do not have "user" table in my DB -- it's basically empty). I'm not even getting to that error. I guess SQL stops on first error, which must be this stamp_created problem. Weird to me is that it looks exactly the same as in the sample table. Commented Nov 24, 2011 at 3:30
  • protocol_version = 10, version = 5.1.36-community-log. yours? Commented Nov 24, 2011 at 3:39
  • I'm on 5.5.8. Also, can we see the results from SELECT @@sql_mode;? Commented Nov 24, 2011 at 3:39
  • SELECT @@sql_mode; returned 0 results Commented Nov 24, 2011 at 3:41

1 Answer 1

1
stamp_created TIMESTAMP NULL DEFAULT 0,

or

stamp_created TIMESTAMP NULL DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,

depending on your needs, however in your case logical should be:

  stamp_created TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

Sign up to request clarification or add additional context in comments.

3 Comments

bensui, why does it have to be 0? It was '0000-00-00 00:00:00' in the first table -- and that worked fine.
By the way, I tried your suggestion to use 0 as the default. Same error.
bensui, if I only had one timestamp column, I could do stamp_created TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP. But I have more than one timestamp column and I'm trying to default them to the current_timestamp. MySQL doesn't allow that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.