#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    39
    Rep Power
    2

    Import Create Table Error


    Hi All,

    I started to import a SQL DB file that I thought worked in the past, but I get an initial error when IMPORTING it and the SQL DB is blank before the import.

    The error is: #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 'TYPE=MyISAM AUTO_INCREMENT=91 AUTO_INCREMENT=91' at line 8

    and the code from the SQL FILE is:

    CREATE TABLE IF NOT EXISTS `default_setup_category` (
    `category_id` bigint( 20 ) NOT NULL AUTO_INCREMENT ,
    `category_parent` bigint( 20 ) NOT NULL default '0',
    `category_path` text NOT NULL ,
    `category_pathname` text NOT NULL ,
    `category_name` text NOT NULL ,
    PRIMARY KEY ( `category_id` )
    ) TYPE = MYISAM AUTO_INCREMENT =91 AUTO_INCREMENT =91;

    I do leave the SQL COMPATIBILITY MODE set to NONE since there is not a plain MYSQL option under it.

    The dropdown for FORMAT I do have as SQL...

    Anyone familiar with an error of a script that is trying to create a table if it is non-existent??

    Thanks in advance,
    Jay
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    Starting with MySQL 5.1 you have to use "ENGINE" instead of "TYPE".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    39
    Rep Power
    2

    Thanks


    Thanks so much!!!

    I guess I need to do a search & replace and replace TYPE throughout the SQL file.

    Are there any other terms that have changed that this old SQL DB file might be using that I would also need to change... Some call function like this one?

    Jay
  6. #4
  7. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    your CREATE TABLE will work but it could be improved

    will you have fewer than 2 billion categories? if so, use INTEGER instead of BIGINT, and if fewer than 32,000, use SMALLINT

    change your TEXT columns to VARCHAR(255) and increase 255 if you need a longer string
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    39
    Rep Power
    2

    So like this?


    CREATE TABLE IF NOT EXISTS `default_setup_category` (
    `category_id` smallint( 20 ) NOT NULL AUTO_INCREMENT ,
    `category_parent` smallint( 20 ) NOT NULL default '0',
    `category_path` VARCHAR(255) NOT NULL ,
    `category_pathname` VARCHAR(255) NOT NULL ,
    `category_name` VARCHAR(255) NOT NULL ,
    PRIMARY KEY ( `category_id` )
    ) ENGINE = MYISAM AUTO_INCREMENT =91 AUTO_INCREMENT =91;


    Also why are there two AUTO_INCREMENT =91 in the last line???

    If correct above do i also do a search and replace for any bigint( 20 ) and replace with smallint( 20 ) plus another for text then replace with VARCHAR(255) in the entire SQL file?
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by jdsmith8
    Also why are there two AUTO_INCREMENT =91 in the last line???
    no idea

    you need neither of them

    Originally Posted by jdsmith8
    If correct above do i also do a search and replace for any bigint( 20 ) and replace with smallint( 20 ) plus another for text then replace with VARCHAR(255) in the entire SQL file?
    i would not advise this without seeing the other tables

    by the way, you don't need the (20) after the integer datatypes

    and especially you don't need those horrid backticks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    39
    Rep Power
    2

    Thanks


    Ok will just change out this one and the TYPE to ENGINE throughout the SQL file first and then see if it imports correctly.

    And by backticks you mean the `category_id` can be changed to 'category_id' and those can be changed from ` to ' everywhere with no issues if that is the case, right?

    This is from a 2003 SQL file so that explains the issues creating in new MySQL DB on Import then... Are there any online tools that check a SQL DB file before importing and tell you what needs to be changed to modernize the script or highlight the errors?

    Thanks again !

    Jay
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by jdsmith8
    And by backticks you mean the `category_id` can be changed to 'category_id' and those can be changed from ` to ' everywhere with no issues if that is the case, right?
    no, just remove them all

    this should cause no problem whatsoever, but if you get an error, post it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    39
    Rep Power
    2

    That worked!


    Thanks... Changing the TYPE to ENGINE in the file plus removing the ` things did the trick...

    Thanks all and if there is an online tool for a SQL file error checker please let me know so I can bookmark it..

    Jay

IMN logo majestic logo threadwatch logo seochat tools logo