CODEWITHSUNDEEP
mysql
Menno van Hout
Menno van Hout

Reputation: 39

MySQL query error #1064?

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

Upvotes: 0

Views: 3617

Answers (2)

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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;

Upvotes: 2

Related Questions