baidu / uid-generator

UniqueID generator
Apache License 2.0
5.46k stars 1.55k forks source link

Invalid default value for 'CREATED' #1

Closed poppop0911 closed 7 years ago

poppop0911 commented 7 years ago

When Step 2: Create table WORKER_NODE, Invalid default value for 'CREATED' issures

CURRENT_TIMESTAMP is only acceptable on TIMESTAMP fields. DATETIME fields must be left either with a null default value, or no default value at all - default values must be a constant value, not the result of an expression.

relevant docs: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

baozhi commented 7 years ago

@poppop0911 Thank you for your feedback.

The column 'CREATED' is declared as TIMESTAMP. Please check the create table script: https://github.com/baidu/uid-generator#step-2-create-table-worker_node https://github.com/baidu/uid-generator/blob/master/src/main/scripts/WORKER_NODE.sql

If the script above still does not work, try the sql generated by show create table WORKER_NODE

CREATE TABLE `WORKER_NODE` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'auto increment id',
  `HOST_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT 'host name',
  `PORT` varchar(64) COLLATE utf8_bin NOT NULL COMMENT 'port',
  `TYPE` int(11) NOT NULL COMMENT 'node type: ACTUAL or CONTAINER',
  `LAUNCH_DATE` date NOT NULL COMMENT 'launch date',
  `MODIFIED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modified time',
  `CREATED` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'created time',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='DB WorkerID Assigner for UID Generator'
poppop0911 commented 7 years ago

The possible reason is that CREATED requires a default value MYSQL server-system-variables --explicit_defaults_for_timestamp default Yes By testing the following statement is ok

 CREATE TABLE `WORKER_NODE` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'auto increment id',
  `HOST_NAME` varchar(64) NOT NULL COMMENT 'host name',
  `PORT` varchar(64) NOT NULL COMMENT 'port',
  `TYPE` int(11) NOT NULL COMMENT 'node type: ACTUAL or CONTAINER',
  `LAUNCH_DATE` date NOT NULL COMMENT 'launch date',
  `MODIFIED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modified time',
  `CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='DB WorkerID Assigner for UID Generator'