Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Question

I always get the error:

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

DROP TABLE IF EXISTS `characters`;
CREATE TABLE `characters`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accId` int(11) NOT NULL,
  `charId` int(11) NOT NULL,
  `charType` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  `exp` int(11) NOT NULL,
  `fame` int(11) NOT NULL,
  `items` varchar(128) NOT NULL,
  `hp` int(11) NOT NULL,
  `mp` int(11) NOT NULL,
  `stats` varchar(64) NOT NULL DEFAULT '"1,2,3,4,5,6,7,8"',
  `dead` tinyint(1) NOT NULL DEFAULT '0',
  `tex1` int(11) NOT NULL DEFAULT '0',
  `tex2` int(11) NOT NULL DEFAULT '0',
  `pet` int(11) NOT NULL DEFAULT '0',
  `fameStats` varchar(128) NOT NULL DEFAULT 'eNoVytkRgCAMRdGH4IIbgmsPdmNVNmZf5n7kzM0kksLjJN2V4b30vcHK1YYam9hCxxqh5zpQI0wwQ4IFMhRYYeNjpw444YIfA3kDIA',
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deathTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `totalFame` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

This is my first time using MySQL stuff. The reason I am using it because I downloaded a source for a game with server and this is needed.

spencer7593 spencer7593 · Accepted Answer · 2015-06-03T22:46:54

The problem is isolated to these two lines

  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deathTime`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

To get rid of the error, you need to modify one of those lines to remove the DEFAULT CURRENT_TIMESTAMP. MySQL only allows one timestamp column to be defined that way. For example:

  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deathTime`  timestamp NOT NULL,

Because the deathTime column is defined as NOT NULL and doesn't have a default, you'll need to supply a value for deathTime whenever you insert a row. You could get the current date and time assigned to that column for an INSERT using a BEFORE INSERT trigger.

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

2 Answers