Advertisement:

Author Topic: SQL import error 1064  (Read 5045 times)

Offline quoddy

  • Semi-Newbie
  • *
  • Posts: 26
SQL import error 1064
« on: October 14, 2013, 10:42:31 AM »
I have SMF 2.0 RC1 running via MAMP on my MacBookPro.   Others want to access it so I backed up via "Forum Maintenance" w all three boxes checked.

New SMF 2.0.5 on Bluehost and used PHPMyAdmin to import gzipped database.

Quote
Error
SQL query:

CREATE TABLE  `smf_admin_info_files` (

id_file TINYINT( 4 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
filename TINYTEXT NOT NULL ,
path TINYTEXT NOT NULL ,
parameters TINYTEXT NOT NULL ,
data TEXT NOT NULL ,
filetype TINYTEXT NOT NULL ,
PRIMARY KEY ( id_file ) ,
KEY filename( filename( 30 ) )
) TYPE = MYISAM ;

MySQL said: Documentation

#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' at line 10

Can anyone offer some insight into what is causing the problem?

Offline margarett

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,761
  • Gender: Male
Re: SQL import error 1064
« Reply #1 on: October 14, 2013, 10:49:06 AM »
Actually you can't do it like that.

You have to convert to 2.0.5 BEFORE you import it to your host.
Second, please use phpmyadmin to dump your database.

Now, about that error... What MySQL versions do you run, either on your server and your local machine?
If you dump via phpmyadmin, there are some export compatibility options that might help with that, not sure though...
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

Quote
Over 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 58,746
  • Gender: Male
    • Kindred-999 on GitHub
Re: SQL import error 1064
« Reply #2 on: October 14, 2013, 12:25:10 PM »
actually, you don't have to upgrade first.

He could do the db import, upload settings.php from the old site and correct the urls and paths...
then upload the large upgrade package and run upgrade,php
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline margarett

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,761
  • Gender: Male
Re: SQL import error 1064
« Reply #3 on: October 14, 2013, 12:32:20 PM »
I find it more "safe" to do it in localhost :P
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

Quote
Over 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Offline quoddy

  • Semi-Newbie
  • *
  • Posts: 26
Re: SQL import error 1064
« Reply #4 on: October 14, 2013, 04:10:37 PM »
My local machine is a MacBookPro (OSX 10.8.4) running MAMP
   MySQL   5.5.9
   PHPMyAdmin   3.3.9.2
   PHP  5.3.6

Online server is Bluehost.com  (Linux 2.6.32-20130307.60.9.bh6.x86_64)
http://headache.quoddy.ca
   MySQL   5.5.34-log
   PHPMyAdmin   3.4.11.1
   PHP   5.2.17

Offline vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 21,846
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: SQL import error 1064
« Reply #5 on: October 14, 2013, 04:13:07 PM »
Also I think the error is due to TYPE = MYISAM
TYPE no longer supported.

You need to replace TYPE= to Engine=
In order to work in the file.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline quoddy

  • Semi-Newbie
  • *
  • Posts: 26
Re: SQL import error 1064
« Reply #6 on: October 14, 2013, 07:08:53 PM »
I have Exported from laptop using PHPMyAdmin.

Logged into the BlueHost server PHPMyAdmin and attempted to Import the file.

Quote
Error
SQL query:

--
-- Database: `smf1`
--
-- --------------------------------------------------------
--
-- Table structure for table `smf_admin_info_files`
--
CREATE TABLE  `smf_admin_info_files` (

 `id_file` TINYINT( 4 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `filename` TINYTEXT NOT NULL ,
 `path` TINYTEXT NOT NULL ,
 `parameters` TINYTEXT NOT NULL ,
 `data` TEXT NOT NULL ,
 `filetype` TINYTEXT NOT NULL ,
PRIMARY KEY (  `id_file` ) ,
KEY  `filename` (  `filename` ( 30 ) )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =8;

MySQL said: Documentation

#1050 - Table 'smf_admin_info_files' already exists

Well of course "smf_admin_info_files" already exists.   I installed SMF and a shell database is created.   PHPMyAdmin shows the table structure exists but the data is what I am trying to move.   How do I indicate to PHPMyAdmin that I am importing a populated database to an empty database with the same table structure?   I don't need to recreate the tables that are already in place.

and VBGamer45,  I notice that "Type = MyISAM" is now indicated as "ENGINE = MYISAM".

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,982
    • StoryBB/StoryBB on GitHub
Re: SQL import error 1064
« Reply #7 on: October 14, 2013, 07:09:52 PM »
You're not really supposed to perform an import into an existing install as already indicated above ;)
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline quoddy

  • Semi-Newbie
  • *
  • Posts: 26
Re: SQL import error 1064
« Reply #8 on: October 14, 2013, 07:50:44 PM »
Got it.

Deleted database that install had created and then created an empty database with same name.  Import worked without errors.

I have some default theme directory issues but the data seems to be there.   Should have that fixed tonight.

Thanks everyone for the help.

Offline Bigguy

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,995
  • Gender: Male
  • Be nice, or else....
    • smfbigguy on GitHub
    • What's Ur Beef
Re: SQL import error 1064
« Reply #9 on: October 14, 2013, 09:58:16 PM »