Search code examples
mysqlsqlmysql-5.5

Why is sql not letting me create this table?

I'm trying to migrate a db from: MySQL Distrib 5.5.60-MariaDB, for Linux (x86_64) to: MySQL 5.5.4, UNIX

I tried importing the db as a zip package and it started throwing errors so now I'm trying to re-create each table one at a time on phpMyAdmin.

The query below is throwing a #1064 Syntax error, and I'm having trouble figuring out the issue.

MySQL Said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2),
  `st' at line 6

I'm looking at line 6, trying to find any reserved words, missing data, typos, and or obsolete commands but no luck. 

CREATE TABLE `tblmoto_auth_policies` (
  `policy_id` int(11) NOT NULL AUTO_INCREMENT,
  `policy_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `policy_desc` text COLLATE utf8_unicode_ci NOT NULL,
  `policy_url` text COLLATE utf8_unicode_ci NOT NULL,
  `date_added` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint(2) NOT NULL DEFAULT '1',
  PRIMARY KEY (`policy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I could use some help. Thanks in advance.

Solution

  • This works in SQL Fiddle:

    CREATE TABLE `tblmoto_auth_policies` (
  `policy_id` int(11) NOT NULL AUTO_INCREMENT,
  `policy_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `policy_desc` text COLLATE utf8_unicode_ci NOT NULL,
  `policy_url` text COLLATE utf8_unicode_ci NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` smallint(2) NOT NULL DEFAULT '1',
  PRIMARY KEY (`policy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    i.e., remove the precision (the (2)) from the definition of the date_added column.

    TIMESTAMP(2) is valid syntax, but not in combination with the DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP auto-initializers.