MySQL query error #1064?

Question

I'm trying to import my old database but this gave me some errors what makes it impossible im also searching on google for 30 minuts and i can't find any solution?

SQL-query:


CREATE TABLE `UG_blogs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `catid` int(11) NOT NULL,
  `ownerid` int(11) NOT NULL,
  `content` text NOT NULL,
  `date` datetime NOT NULL DEFAULT TIMESTAMP,
  PRIMARY KEY (`id`), KEY id(`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;


MySQL meldt: Documentatie

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '
  PRIMARY KEY (`id`), KEY id(`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT C' at line 6

Gordon Linoff · Accepted Answer · 2015-09-26 23:12:53Z

2

The correct default value is CURRENT_TIMESTAMP:

CREATE TABLE `UG_blogs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `catid` int(11) NOT NULL,
  `ownerid` int(11) NOT NULL,
  `content` text NOT NULL,
  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY id(`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

The SQL Fiddle is here.

EDIT:

You must be using an old-ish version of MySQL (okay, not that old, just pre-5.6). Well, you can't default a datetime value (without a trigger), so you have to live with a TIMESTAMP value and learn to love the timestamp functions:

CREATE TABLE `UG_blogs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `catid` int(11) NOT NULL,
  `ownerid` int(11) NOT NULL,
  `content` text NOT NULL,
  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY id(`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

edited Sep 26, 2015 at 23:12

answered Sep 26, 2015 at 23:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Menno van Hout Over a year ago

That gave me the following error: #1067 - Invalid default value for 'date'

Menno van Hout Over a year ago

I use: 5.5.44-MariaDB But ill give it a shot

Gordon Linoff Over a year ago

@MennovanHout . . . MariaDB is (basically) MySQL, so a 5.6 or later version should support datetime with a default.

Hotdin Gurning · Accepted Answer · 2015-09-26 23:21:12Z

0

First solution : change datatype into timestamp like this

CREATE TABLE `UG_blogs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `catid` int(11) NOT NULL,
  `ownerid` int(11) NOT NULL,
  `content` text NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY id(`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

The problem is : The Timestamp data type has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07.

So the second solution is, don't change datetime datatype into timestamp. But you need a trigger to set default value.

answered Sep 26, 2015 at 23:21

Hotdin Gurning

1,8193 gold badges16 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL query error #1064?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related