phpmyadmin Question posted in
The official documentation can be found here.

Why is sql not letting me create this table? – Phpmyadmin

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.

Tags:

2

Answers


  1. 0

    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. 0

    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