uploading mysql database to webhost error???????

[squeakstar]

hi everyone,

i'm attempting to transfer a mysql database from my pc to my web host but receive the following error when i try to import to my real website....

 

 

Error

SQL query:

 

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 ) COLLATE latin1_general_ci NOT NULL default '',

`entry_company` varchar( 32 ) COLLATE latin1_general_ci default NULL ,

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

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

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

`entry_suburb` varchar( 32 ) COLLATE latin1_general_ci default NULL ,

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

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

`entry_state` varchar( 32 ) COLLATE latin1_general_ci 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` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci AUTO_INCREMENT =3

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 'collate latin1_general_ci NOT NULL default '',

`entry_company

 

the version phpmyadmin i'm using to export is 2.6.3-pl1 and the importing to, online version is running 2.6.4-pl2 .

 

also the version of mysql i built my store around was 4.1.13 and the online version is running mysql 4.0.25

 

i had recently added the tweaks to the php files from the latest release also of OsCommerce, and they seemed to work fine on my home pc xampp installation... just thought i'd mention if thats any help

 

eek - i hope someone can make sense of this error and offer some advice as to how i can correct it please?

 

thansk for reading :thumbsup:

[Guest]

Make a backup first.

 

Try removing "COLLATE latin1_general_ci " from each line, search and replace will do it using notepad. Then try again.

[squeakstar]

hi java, thanks for the prompt response!

 

i did that and it seemed to be more succsessful, although i got this message....

 

Error

SQL query:

 

-- phpMyAdmin SQL Dump

-- version 2.6.3-pl1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Jan 17, 2006 at 08:27 PM

-- Server version: 4.1.13

-- PHP Version: 5.0.4

--

-- Database: `mysite_new`

--

-- --------------------------------------------------------

--

-- Table structure for table `address_book`

--

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 ) COLLATE latin1_general_ci 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` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci AUTO_INCREMENT =3

 

comparing the two database tables my original homebrew dev site has two tables for addresses:

 

address_book

address_format

 

whilst my new online installation has only address_format

 

eek i'm stuck and scared i'll have to re-type and configure 180 flippin items.... :(

[Guest]

It should be;

 

-

-- Table structure for table `address_book`

--

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` )

) ENGINE = MYISAM AUTO_INCREMENT =3

[squeakstar]

aha then it passes onto...

 

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` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci AUTO_INCREMENT =6

 

i take it i have to sift through and change that last line or so to what you previously mentioned, in each table section?

 

please confirm and i'll give that a shot - i appreciate this dude!

 

whats going wrong in a nutshell by the way

[Guest]

What is wrong is your desktop MYSQL version adds the "latin1" to CHARSET and COLLATE. I don't know where the setting is to change that but if from windows "start menu" and go to "search". Then browse to the folder where you local web server is. Then search the files and text for the word "latin1" you might be able to change that to match your online server.

[squeakstar]

aah right, well thanks for now - its nearly bedtime so thats my first task for tomorrow edit that sqlo file then the settings.....

 

i'll let you know how i get on :thumbsup: many thanks

[Guest]

"Search and replace", it should only take 1 minute to change the whole DB, just back it up and then open it in notepad.

[squeakstar]

hey java! (or anyone else that may help)

 

did that, piece of cake, now i get this error concerning banners in phpmyadmin when i run the import...

 

Error

SQL query:

 

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 COLLATE latin1_general_ci,

`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` )

) ENGINE = MYISAM AUTO_INCREMENT =2

 

the new database may actually have uploaded but with this error; when i now visit my shop front i get this error in my web browser...

 

1146 - Table 'efbjcerp_postermafia.banners' doesn't exist

 

select banners_id, date_scheduled from banners where date_scheduled != ''

 

[TEP STOP]

 

i note in the sql error this cropping up again text COLLATE latin1_general_ci i'm gonna try strippin that out now in the mean time....

 

grrr snarl... thanks for yer help in advance anyone and everyone ;)

[squeakstar]

update everyone....

 

kept finding further instances of variations of the characteset errors i was getting... but finally got it uploaded and working - so far anyway.

 

i compared the original web-host's installation of my mysql databse against my tweaked windows database everytime an error was presented in phpmyadmin; each time it came up with an error i replaced that line with a reference to the charcterset error from the default to mine, eventually erradicating all instances of it with the correct required syntax.

 

many thanks Java for the help, very much appreciated!

 

matty