import SQL file problems

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

import SQL file problems

Post 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?!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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..
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

is there a way to export to an older version using the "mysqldump" command?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I've done it by editting the generated sql

replace ENGINE= for TYPE=
remove DEFAULT CHARSET=latin1
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Got any suggestions for a good large text file editor for a windows machine.

My SQL file is around 200mb

Thanks
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
Post Reply