MySQL Syntax Error on Table Creation

4
votes

I am trying to use this query in a Filemaker -> MySQL transition script. The table creation steps are taken directly from a phpMyAdmin export, and I added in the DROP statements.

DROP TABLE IF EXISTS artifacts; 

CREATE TABLE IF NOT EXISTS `artifacts` 
  ( 
     `aid`                       INT(11) UNSIGNED NOT NULL auto_increment, 
     `accession number`          TEXT NOT NULL, 
     `name`                      TEXT NOT NULL, 
     `period 1`                  TEXT NOT NULL, 
     `period 3 date`             TEXT NOT NULL, 
     `visual description`        TEXT NOT NULL, 
     `religion 1`                TEXT NOT NULL, 
     `dimen 1 number`            DECIMAL(10, 2) NOT NULL, 
     `dimen 2 number`            DECIMAL(10, 2) NOT NULL, 
     `dimen 3 number`            DECIMAL(10, 2) NOT NULL, 
     `dimen 1 type`              TEXT NOT NULL, 
     `dimen 2 type`              TEXT NOT NULL, 
     `dimen 3 type`              TEXT NOT NULL, 
     `materials 2`               TEXT NOT NULL, 
     `manufacturing processes 2` TEXT NOT NULL, 
     `weight`                    INT(11) NOT NULL, 
     `measuring remarks`         TEXT NOT NULL, 
     `munsell color information` TEXT NOT NULL, 
     `reproduction`              TEXT NOT NULL, 
     `reproduction notes`        TEXT NOT NULL, 
     `published description`     TEXT NOT NULL, 
     `scholarly notes`           TEXT NOT NULL, 
     `bibliography`              TEXT NOT NULL, 
     `comparanda`                TEXT NOT NULL, 
     `exhibit label`             TEXT NOT NULL, 
     `artist`                    TEXT NOT NULL, 
     `spurlock loc 3`            TEXT NOT NULL, 
     `archaeological data`       TEXT NOT NULL, 
     `credit line`               TEXT NOT NULL, 
     `provenance`                TEXT NOT NULL, 
     `museum dedication`         TEXT NOT NULL, 
     `spurlock status`           TEXT NOT NULL, 
     `public description`        TEXT NOT NULL, 
     `working set 5 wb`          TEXT NOT NULL, 
     `image source`              TEXT NOT NULL, 
     `cm mec ma`                 TEXT NOT NULL, 
     `webprivate`                TEXT NOT NULL, 
     `spurlock loc 2`            TEXT NOT NULL, 
     `hiresimagecheck`           TEXT NOT NULL, 
     PRIMARY KEY (`aid`), 
     FULLTEXT KEY `name` (`name`), 
     FULLTEXT KEY `accession number` (`accession number`, `name`, `period 1`, 
     `visual description`, `materials 2`, `published description`, `artist`, 
     `credit line`), 
     FULLTEXT KEY `accession number_2` (`accession number`), 
     FULLTEXT KEY `visual description` (`visual description`), 
     FULLTEXT KEY `published description` (`published description`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS culture; 

CREATE TABLE IF NOT EXISTS `culture` 
  ( 
     `cid`     INT(10) UNSIGNED NOT NULL auto_increment, 
     `culture` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`cid`), 
     UNIQUE KEY `culture` (`culture`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS geocity; 

CREATE TABLE IF NOT EXISTS `geocity` 
  ( 
     `gid`  INT(10) UNSIGNED NOT NULL auto_increment, 
     `city` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `city` (`city`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS geocontinent; 

CREATE TABLE IF NOT EXISTS `geocontinent` 
  ( 
     `gid`       INT(10) UNSIGNED NOT NULL auto_increment, 
     `continent` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `continent` (`continent`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS geocountry; 

CREATE TABLE IF NOT EXISTS `geocountry` 
  ( 
     `gid`     INT(10) UNSIGNED NOT NULL auto_increment, 
     `country` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `country` (`country`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS geolocality; 

CREATE TABLE IF NOT EXISTS `geolocality` 
  ( 
     `gid`      INT(10) UNSIGNED NOT NULL auto_increment, 
     `locality` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `locality` (`locality`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS georegion; 

CREATE TABLE IF NOT EXISTS `georegion` 
  ( 
     `gid`    INT(10) UNSIGNED NOT NULL auto_increment, 
     `region` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `region` (`region`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS nomcategory; 

CREATE TABLE IF NOT EXISTS `nomcategory` 
  ( 
     `nid`      INT(10) UNSIGNED NOT NULL auto_increment, 
     `category` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`nid`), 
     UNIQUE KEY `category` (`category`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS nomclassification; 

CREATE TABLE IF NOT EXISTS `nomclassification` 
  ( 
     `nid`            INT(10) UNSIGNED NOT NULL auto_increment, 
     `classification` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`nid`), 
     UNIQUE KEY `classification` (`classification`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS nomsubclassification; 

CREATE TABLE IF NOT EXISTS `nomsubclassification` 
  ( 
     `nid`               INT(10) UNSIGNED NOT NULL auto_increment, 
     `subclassification` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`nid`), 
     UNIQUE KEY `subclassification` (`subclassification`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1;

However, when I attempt to execute this query, I get:

MySQL Error: 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 'CREATE TABLE IF NOT EXISTS `artifacts` 
  ( 
     `aid`                       IN' at line 3

The syntax all looks good to me, can anyone spot the error? The only SQL formatter that I use reports no errors.

2
mysqlsyntax
i am used to NUMBER not INT, and VARCHAR not TEXT - with sizes... – Randy
I tried your script in my MySQL server, it works fine. – Tchoupi
Your script works fine. SEE HERE @ SQLFiddle – John Woo

2 Answers

7
votes

Your script works fine. The problem is either the way you are executing it (client will expect query by query not the whole script) or you have changed your delimiter to something else than ;

DELIMITER ;
-1
votes
  1. Does your username have permission to create tables?

  2. did you indicate which database to use? I usually do

    USE databasename;

CREATE TABLE tablename (
name_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 (etc....)
);

etc.