MySQL database error

[cybin]

I just tried to upload my database to my sever though PHPmyadmin. I dumped the file using Navicat. Is there any special ways I should be dumping and then uploading the db?

 

Heres what the server said.

 

SQL-query:

 

 

SET FOREIGN_KEY_CHECKS =0 CREATE TABLE `address_book` (

`address_book_id` int( 11 ) NOT NULL AUTO_INCREMENT ,

`customers_id` int( 11 ) NOT NULL default '0',

`entry_gender` char( 1 ) NOT NULL default '',

`entry_company` varchar( 32 ) default NULL ,

`entry_firstname` varchar( 32 ) NOT NULL default '',

`entry_lastname` varchar( 32 ) NOT NULL default '',

`entry_street_address` varchar( 64 ) NOT NULL default '',

`entry_suburb` varchar( 32 ) default NULL ,

`entry_postcode` varchar( 10 ) NOT NULL default '',

`entry_city` varchar( 32 ) NOT NULL default '',

`entry_state` varchar( 32 ) default NULL ,

`entry_country_id` int( 11 ) NOT NULL default '0',

`entry_zone_id` int( 11 ) NOT NULL default '0',

PRIMARY KEY ( `address_book_id` ) ,

KEY `idx_address_book_customers_id` ( `customers_id` )

) TYPE = MYISAM

 

 

MySQL said:

#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 'CREATE TABLE `address_book` (

??`address_book_id` int(11) NOT N

 

 

Here are the server specs:

 

General server information:

 

Operating system

Linux

 

Service Status

Click to View

 

Kernel version

2.4.21-20.EL

 

Machine Type

i686

 

Apache version

1.3.33 (Unix)

 

PERL version

5.8.0

 

Path to PERL

/usr/bin/perl

 

Path to sendmail

/usr/sbin/sendmail

 

Installed Perl Modules

Click to View

 

PHP version

4.3.8

 

MySQL version

4.0.22-standard

 

cPanel Build

9.9.8-RELEASE 116

 

Theme

cPanel X v2.5.0

[Guest]

try removing this:

 

SET FOREIGN_KEY_CHECKS =0

 

also, if this table exists, you have to drop it first. is there any data in it currently?

 

else do this first:

 

drop table if exists address_book;

[cybin]

Still no good. If I remove SET FOREIGN_KEY.... that line, SQL says no database selected.

 

I don't have any info in address_book yet. If I remove it will Mysql replace it when I upload it?

 

-Cybin

[Camouflage]

Hi,

 

If MySQL said:

#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 'CREATE TABLE `address_book` (

?

Check if there are comments without the #-sign. Maybe the name of database before #phpMyAdmin SQL Dump.

[cybin]

The error seems to be right at the top of the file. I drop the address table and then tried to up load it. Still no good. Heres the first paragraph of the db.

 

SET FOREIGN_KEY_CHECKS = 0

 

CREATE TABLE `address_book` (

`address_book_id` int(11) NOT NULL auto_increment,

`customers_id` int(11) NOT NULL default '0',

`entry_gender` char(1) NOT NULL default '',

`entry_company` varchar(32) default NULL,

`entry_firstname` varchar(32) NOT NULL default '',

`entry_lastname` varchar(32) NOT NULL default '',

`entry_street_address` varchar(64) NOT NULL default '',

`entry_suburb` varchar(32) default NULL,

`entry_postcode` varchar(10) NOT NULL default '',

`entry_city` varchar(32) NOT NULL default '',

`entry_state` varchar(32) default NULL,

`entry_country_id` int(11) NOT NULL default '0',

`entry_zone_id` int(11) NOT NULL default '0',

PRIMARY KEY (`address_book_id`),

KEY `idx_address_book_customers_id` (`customers_id`)

) TYPE=MyISAM;

 

insert into `address_book` values('1','1','m','ACME Inc.','John','Doe','1 Way Street','','12345','NeverNever','','223','12'),

('2','3','m','','asdf','asdf','123 main','','54302','green Bay','','223','64');

 

CREATE TABLE `address_format` (

`address_format_id` int(11) NOT NULL auto_increment,

`address_format` varchar(128) NOT NULL default '',

`address_summary` varchar(48) NOT NULL default '',

PRIMARY KEY (`address_format_id`)

) TYPE=MyISAM;

 

insert into `address_format` values('1','$firstname $lastname$cr$streets$cr$city, $postcode$cr$statecomma$country','$city / $country'),

('2','$firstname $lastname$cr$streets$cr$city, $state $postcode$cr$country','$city, $state / $country'),

('3','$firstname $lastname$cr$streets$cr$city$cr$postcode - $statecomma$country','$state / $country'),

('4','$firstname $lastname$cr$streets$cr$city ($postcode)$cr$country','$postcode / $country'),

('5','$firstname $lastname$cr$streets$cr$postcode $city$cr$country','$city / $country');

 

CREATE TABLE `banners` (

`banners_id` int(11) NOT NULL auto_increment,

`banners_title` varchar(64) NOT NULL default '',

`banners_url` varchar(255) NOT NULL default '',

`banners_image` varchar(64) NOT NULL default '',

`banners_group` varchar(10) NOT NULL default '',

`banners_html_text` text,

`expires_impressions` int(7) default '0',

`expires_date` datetime default NULL,

`date_scheduled` datetime default NULL,

`date_added` datetime NOT NULL default '0000-00-00 00:00:00',

`date_status_change` datetime default NULL,

`status` int(1) NOT NULL default '1',

PRIMARY KEY (`banners_id`)

) TYPE=MyISAM;

[Guest]

what version are you trying to install?

this is in the oscommerce.sql file:

 

DROP TABLE IF EXISTS address_book;
CREATE TABLE address_book (
? address_book_id int NOT NULL auto_increment,
? customers_id int NOT NULL,
? entry_gender char(1) NOT NULL,
? entry_company varchar(32),
? entry_firstname varchar(32) NOT NULL,
? entry_lastname varchar(32) NOT NULL,
? entry_street_address varchar(64) NOT NULL,
? entry_suburb varchar(32),
? entry_postcode varchar(10) NOT NULL,
? entry_city varchar(32) NOT NULL,
? entry_state varchar(32),
? entry_country_id int DEFAULT '0' NOT NULL,
? entry_zone_id int DEFAULT '0' NOT NULL,
? PRIMARY KEY (address_book_id),
? KEY idx_address_book_customers_id (customers_id)
);

[Daemonj]

Try adding a semi-colon at the end of the set foreign keys line.

[cybin]

Um, yea that was it. Added semicolon and the db uploaded.

 

I was doing this all wrong though. I needed to first create a db on the webserver then though phpmysql run a sql statement.

 

I dumped the file though navicat btw, all the ones I dumped though phpmyadmin didn't work.

 

Not sure if it fully worked since I now need to figure out whats up with another error.

 

Thanks,

-Cybin