Page 1 of 1

import SQL file problems

Posted: Wed Jul 27, 2005 6:17 am
by JayBird
Im trying to import my vBulletin database from my local server to my hosting provider server

When i try to import it from the command line i get the following error

ERROR 1064 at 13: 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 'DEFAULT CHARSET=latin1' at line 10

Line 13 is in the code beolow is:

Code: Select all

CREATE TABLE adminhelp (

Code: Select all

DROP TABLE IF EXISTS access;
CREATE TABLE access (
  userid int(10) unsigned NOT NULL default '0',
  forumid smallint(5) unsigned NOT NULL default '0',
  accessmask smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (userid,forumid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




DROP TABLE IF EXISTS adminhelp;
CREATE TABLE adminhelp (
  adminhelpid int(10) unsigned NOT NULL auto_increment,
  script varchar(50) NOT NULL default '',
  action varchar(25) NOT NULL default '',
  optionname varchar(25) NOT NULL default '',
  displayorder smallint(5) unsigned NOT NULL default '1',
  volatile smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (adminhelpid),
  UNIQUE KEY phraseunique (script,action,optionname)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
When i import this on my local machine, it works no problem but not with my hosting provider

Any ideas?!

Posted: Wed Jul 27, 2005 7:34 am
by timvw
You probably have an older (<4.1?) version at of mysql at your provider...

Last phpmyadmin versions have an option to export to a different version..

Posted: Wed Jul 27, 2005 7:55 am
by JayBird
is there a way to export to an older version using the "mysqldump" command?

Posted: Wed Jul 27, 2005 8:07 am
by timvw
I've done it by editting the generated sql

replace ENGINE= for TYPE=
remove DEFAULT CHARSET=latin1

Posted: Wed Jul 27, 2005 8:11 am
by JayBird
Got any suggestions for a good large text file editor for a windows machine.

My SQL file is around 200mb

Thanks

Posted: Wed Jul 27, 2005 10:58 am
by JayBird
this command line does the trick for dumping

Code: Select all

mysqldump --create-options --compatible=mysql40 -u <username> -p <password> db_name > dump_file