Unable to import MYSQL database

14 Nov 2007, 18:35

#1

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Unable to import MYSQL database

Hi can you help???

i am trying to import my database from 1 shopping cart to my new server and new shopping cart,
i have coied all my files over to the new server and dropped all my tabls on the new MY SQL data base and when importing via, Gzip or zip file i get the below error?

Can you advise what i am doing wrong please as i am sooooooo confused

Error
SQL query:

-- phpMyAdmin SQL Dump
-- version 2.11.0
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 14, 2007 at 06:26 PM
-- Server version: 4.1.22
-- PHP Version: 5.2.3
--
-- Database: `fr201jus_zc1`
--
CREATE DATABASE `fr201jus_zc1` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci
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 'DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci' at line

:dontgetit

14 Nov 2007, 23:39

#2

sirbuck

Zen Follower

Join Date:: Jan 2007
Posts:: 161
Plugin Contributions:: 0

Re: Unable to import MYSQL database

what are you using to import the DB? what did you use to export and what were those settings?

Also, how big is the database file?

14 Nov 2007, 23:43

#3

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Unable to import MYSQL database

And what version of MySQL on the new server?

16 Nov 2007, 17:55

#4

1just4help

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Re: Unable to import MYSQL database

Hi, I am using phpMyAdmin version 4.0

16 Nov 2007, 18:23

#5

1just4help

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Re: Unable to import MYSQL database

the file is 238 kb and i used phpMyAdmin version 4.0 to export to a gzip, zip and a sql file format, i have tried all formats to upload and none work :frusty:

16 Nov 2007, 20:30

#6

sirbuck

Zen Follower

Join Date:: Jan 2007
Posts:: 161
Plugin Contributions:: 0

Re: Unable to import MYSQL database

Why don't you just export that without zipping?

16 Nov 2007, 21:41

#7

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Unable to import MYSQL database

Your server is probably set up to disallow you from creating databases via a .sql import.

So, either edit your .sql file before you attempt to upload it, and put a # before the CREATE DATABASE `fr201jus_zc1` .... line,
or, when you do your export, select your database from the pulldown on the left, so that all your tables are displayed, and *then* click the "Export" tab. This should cause it to skip building the CREATE DATABASE statement.

FAQ: https://www.zen-cart.com/tutorials/index.php?article=103

17 Nov 2007, 09:16

#8

1just4help

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Re: Unable to import MYSQL database

Hi - sorry. i have tried the text only and this has not work either. i have now tried the scond option as i am still very new to all this and do not understand how to edit a sql. database.

The results i recived when uploading the new sql was

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.11.0
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 17, 2007 at 09:11 AM
-- Server version: 4.1.22
-- PHP Version: 5.2.3
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"
MySQL said:

#1193 - Unknown system variable 'SQL_MODE'

sorry i'm sure this task is supposed to be easy:cry:

17 Nov 2007, 09:46

#9

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Unable to import MYSQL database

It seems like you're trying to load a MySQL 4 or 5 database onto a v3.23 server.

You never did indicate what version of MySQL is on your server.

17 Nov 2007, 10:55

#10

1just4help

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Re: Unable to import MYSQL database

Hi, the database that i am using to upload on to is version 4 but i have the option to create a version 5. the database that i am tansferinf from is a version 4

17 Nov 2007, 11:35

#11

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Unable to import MYSQL database

1just4help:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"
MySQL said:

#1193 - Unknown system variable 'SQL_MODE'

It seems according to the MySQL manual that SET SQL_MODE is a valid command since version 4.0

If your server is complaining about that, it suggests that you're running 3.23 instead, which would explain the original error messages you were reporting as well.

17 Nov 2007, 12:45

#12

1just4help

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Re: Unable to import MYSQL database

Hi I am a little confused how that could happen when this is how it is set up to configure databases

You can create 3 databases in total. Please be aware that new databases can only be created using version 4.0 or 5.0.
MySQL 4.0 MySQL 5.0

17 Nov 2007, 17:48

#13

1just4help

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Re: Unable to import MYSQL database

not sure if this is going right but i have a new error.
i have exported the file as a text file and changed the data base name to match my new database? and titled the doc #and then database name?

i am getting this error and was hoping you could advise what it means and what i would need to change. it keeps saying no database selected and i can not figure this out. please please help

SQL query:

--
-- Database: `DB338625`
--
-- --------------------------------------------------------
--
-- Table structure for table `zen_address_book`
--
CREATE TABLE IF NOT EXISTS `zen_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_zen` ( `customers_id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci
MySQL said:

#1046 - No database selected

17 Nov 2007, 18:27

#14

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Unable to import MYSQL database

1just4help:
MySQL said:

#1046 - No database selected

"No database selected" means that MySQL hasn't been told which database to run the SQL statements against. This shouldn't happen using phpMyAdmin because phpMyAdmin requires you to select the database before doing an import.
Perhaps you have a "USE DATABASE" statement in your .sql file that's overriding the normal operation, and needs to be removed.

18 Nov 2007, 10:10

#15

1just4help

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Re: Unable to import MYSQL database

Woo HOO i have y database uploaded, i have my files uploaded.

however i have this message when trying to run the fix_cache_key.php or trying to view y shopping cart

1146 Table 'DB338625.configuration' doesn't exist
in:
[select configuration_key as cfgkey, configuration_value as cfgvalue from configuration]

and this when i try and vew my admin

1146 Table 'DB338625.project_version' doesn't exist
in:
[select * from project_version WHERE project_version_key = 'Zen-Cart Database' ]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

i think i am nearly there and really appreciate all your help

18 Nov 2007, 12:50

#16

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Unable to import MYSQL database

1just4help:

1146 Table 'DB338625.configuration' doesn't exist

1146 Table 'DB338625.project_version' doesn't exist

These mean that your configure.php files have a setting for DB_DATABASE set to 'DB338625' and a DB_PREFIX set to '' (blank), but that the specified database name doesn't contain tables of the names expected.

Perhaps you have the wrong DB_DATABASE, or perhaps your DB_PREFIX doesn't match what was used on the old server you're importing data from. What was DB_PREFIX on the old server's configure.php files? Use the same DB_PREFIX on your new server.

18 Nov 2007, 13:34

#17

1just4help

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Re: Unable to import MYSQL database

thanks for that, yes i imported the name slighty diferent, next obstical is when connecting i have a zen error image saying

Sorry!
There seems to be a problem connecting to our database. Please give us a few minutes to remedy the problem. Thank you.

To verify the site status ... Click here

when i run fix_cache_key

i get a new error

Warning: Cannot modify header information - headers already sent by (output started at /mnt/web5/52/98/51651098/htdocs/fix_cache_key.php:43) in /mnt/web5/52/98/51651098/htdocs/includes/init_includes/init_database.php on line 29

i hope this makes sense??

again thanks for the help??

18 Nov 2007, 18:19

#18

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Unable to import MYSQL database

1just4help:
when connecting i have a zen error image saying

Sorry!
There seems to be a problem connecting to our database. Please give us a few minutes to remedy the problem. Thank you.

To verify the site status ... Click here

This means you still have something incorrect with your database settings.
You'll need to check and double-check the DB_xxxxx settings again.

1just4help:

when i run fix_cache_key

i get a new error

Warning: Cannot modify header information - headers already sent by (output started at /mnt/web5/52/98/51651098/htdocs/fix_cache_key.php:43) in /mnt/web5/52/98/51651098/htdocs/includes/init_includes/init_database.php on line 29

The "headers already sent" warning is normal when running fix_cache_key. The messages AFTER that are more important. Did it say its update was successful?

18 Nov 2007, 18:37

#19

1just4help

New Zenner

Join Date:: Nov 2007
Posts:: 28
Plugin Contributions:: 0

Re: Unable to import MYSQL database

they were the only messages, from the warning messag it stopped on that screen

19 Nov 2007, 07:23

#20

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Unable to import MYSQL database

You're probably missing the rest of the update messages because of your database settings problem.
You'll need to check your DB_xxxxxx settings for accuracy ...