Database import problems

[Jaden]

Hi Zenners,

I have just updated to version 1.3.7 from the previous version because of password issues. I have backed up my database onto my pc (example_text.sql 1.36Kb).
I can not get it to upload to my new site. Here is my procedures (I am a bit sql ileterate!)

In phpMyAdmin on my hosts server (cpanel),
- File to import (browse to example_text.sql)
- Character set - utf8 (??)
Partial import
Allow interrupt of import in case script detects it is close to time limit. This might be good way to import large files, however it can break transactions.

Number of records(queries) to skip from start

Format of imported file SQL


SQL optionsSQL compatibility modeNONEANSIDB2MAXDBMYSQL323MYSQL40MSSQLORACLEPOSTGRESQLTRADITIONAL



Now select GO........

Then this error after a short time.....

Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 20, 2007 at 07:38 PM
-- Server version: 5.0.27
-- PHP Version: 4.4.2
--
-- Database: `example_text`
--
-- --------------------------------------------------------
--
-- 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_zen` ( `customers_id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =8;



MySQL said:
#1050 - Table 'address_book' already exists

???????????????????????????????????????????????????????????

Thats where I am at. But I am really trying to get all my products and their pictures back. Does all this sit in the database backup I did? If it only has things such as emails and address I will not worry about it and reenter all my products again.

Please note example_text is a made up name.

Thanks
Jamie

[TouranMan]

Sounds like it's trying to import you data over the top of the sample data. i.e. all the tables already exist.

Your best best is to drop all the tables in the database that the new install created so you have an empty Db. Then try the import and it'll create all the necessary tables and add the data.

Alternatively, when you backed up your database, you get the option (checkbox IIRC) to DROP tables if they already exist and this puts all the necessary commands into the SQL file.

Hope this helps.

[Website Rob]

Doing import/export can be confusing and the easiest way is to make sure the following boxes are checked, when doing an Export in phpMyAdmin.

Structure
Add DROP TABLE
Add IF NOT EXISTS
Add AUTO_INCREMENT value
Enclose table and field names with backquotes

Your current error msg. is telling you that a Table cannot be imported because it already exists. By using the "Add DROP TABLE" when you export, it will delete the TABLE for you, if one exists, when you do an import and allow things to go smoothly.

[Jaden]

Thanks Website Rob & TournMan. But now it is all gone!

I did the following
Structure
Add DROP TABLE
Add IF NOT EXISTS
Add AUTO_INCREMENT value
Enclose table and field names with backquotes

And now my web site just has a database error box!

Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 20, 2007 at 07:38 PM
-- Server version: 5.0.27
-- PHP Version: 4.4.2
--
-- Database: `example_text`
--
-- --------------------------------------------------------
--
-- 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_zen` ( `customers_id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =8;



MySQL said:
#1050 - Table 'address_book' already exists

So I will now reload zen cart and start again.

I will keep your answers on file for the next upgrade! Hopefully the next time I will know a bit more.

Thanks again.

Jamie

[Jaden]

Ok now it is worse!

I dleted the whole web site (in my www folder). And uploaded via ftp a backup I did of the whole site. Now I get the there seems to be a problem with our database (nddbc.html) page!!!!
From what I can see on past forums it seems to be a database user name or password missmatch?? I deleted the database and tried to upload the saved database from my PC again but get the error

Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 20, 2007 at 07:38 PM
-- Server version: 5.0.27
-- PHP Version: 4.4.2
--
-- Database: `example_text`
--
-- --------------------------------------------------------
--
-- 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_zen` ( `customers_id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =8;



MySQL said:
#1046 - No database selected

through phpMyAdmin cpanel.
For some reason my www folder was deleted (my fingures may to be blamed!) but I see that the public_html folder has the same files. Is it OK that I have recreated the www folder and is there something I have to enabble with it??

I have taken to the idea that all is lost, so how can I get the site back up?? www.austime.net

Thanks for your patience.

Jamie

[Jaden]

WOW ITS BACK !!!!!!

Here is what I did.....

- I deleted the daabase in PhpAdmin
- Renamed it and deleted the user and reinstalled the user name and password (as previous posts)
- When I did a database import I deselected Partial import!! I think this is where my problem was.

Well all is well again and I am very much more the wiser. This just proves what a great community the Zenners are.
Thanks again from down under (I wake while you sleep and vise versa!)

[TouranMan]

Everytime I view this page I get a popup box asking me to log into your cPanel Jaden!! Something is wrong on here! Big security risk IMO, admin know what's going on?

[Jaden]

Yes I am back to my original problem where my admin login does not work! But my server hosst are resetting that for me (I hope).

I need to do some fixes from in there. As well I have to link all my images up, but I recon this is not too hard and I am glad that all the info is back.

But I suppose cpanel is totally different to my admin? Did you try www.austime.net

[Website Rob]

Perhaps a Mod could edit this thread remove the previous URL posting of your phpMyAdmin login?

As to your last solution, although I had seen a previous reference to using a 'partial' import, had not realized you were still tring doing to one. Partial imports are more complicated than a 'full' one.

As to your ZenCart Admin login page, seems to be loading just fine. If you are still having problems, no need to post the URL, just details on the problem.