skip to Main Content

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.

2

Answers


  1. CURRENT_TIMESTAMP is not good

    Try this:

    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(2),
      `status` smallint(2) NOT NULL DEFAULT '1',
      PRIMARY KEY (`policy_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search