How to restore product database

[lacoz]

Hi there,

 

after I moved my store from a windows to linux server, I had a problem to restore my database from a backup file (lot of errors, some tables wanished), so I dropped the entire database and made a new install. Now I am wondering, if it is possible to restore only the categories and products table, so I wouldn´t have to add hundreds of products manually again.

 

Is it possible to pick some part (and if yes which) from an existing spl file and run it in phpMyAdmin?

 

Thanks for any advise.

 

Laco

[Jack_mcs]

Yes, you can do that. There are two category tables and nine product tables that you would need to copy.

[lacoz]

Yes, you can do that. There are two category tables and nine product tables that you would need to copy.

 

Can You please specify what I should do? I have only basic knowledge about this...

 

So I have a db.sql file. What do I have to leave in it and what to delete?

[Jan Zonjee]

So I have a db.sql file. What do I have to leave in it and what to delete?

An sql file is a plain text file. You can view and edit it with a text editor (like the one you are using to edit PHP). There is no real reason why it should it not be possible to edit your existing db.sql file so that restoring it does not give any errors.

 

And yes, you can copy and paste parts of that file to only restore certain tables. A regular osC backup file starts with deleting the existing table (drop table command), then rebuilding the table structure and then inserting values into it. For example to restore the table orders_status you would need this part from a backup file:

 

drop table if exists orders_status;
create table orders_status (
 orders_status_id int(11) default '0' not null ,
 language_id int(11) default '1' not null ,
 orders_status_name varchar(32) not null ,
 public_flag int(11) default '1' ,
 downloads_flag int(11) default '0' ,
 PRIMARY KEY (orders_status_id, language_id),
 KEY idx_orders_status_name (orders_status_name)
);

insert into orders_status (orders_status_id, language_id, orders_status_name, public_flag, downloads_flag) values ('1', '1', 'Pending', '1', '0');
insert into orders_status (orders_status_id, language_id, orders_status_name, public_flag, downloads_flag) values ('1', '2', 'Offen', '1', '0');
insert into orders_status (orders_status_id, language_id, orders_status_name, public_flag, downloads_flag) values ('1', '3', 'Pendiente', '1', '0');
insert into orders_status (orders_status_id, language_id, orders_status_name, public_flag, downloads_flag) values ('2', '1', 'Processing', '1', '1');
insert into orders_status (orders_status_id, language_id, orders_status_name, public_flag, downloads_flag) values ('2', '2', 'In Bearbeitung', '1', '1');
insert into orders_status (orders_status_id, language_id, orders_status_name, public_flag, downloads_flag) values ('2', '3', 'Proceso', '1', '1');
insert into orders_status (orders_status_id, language_id, orders_status_name, public_flag, downloads_flag) values ('3', '1', 'Delivered', '1', '1');
insert into orders_status (orders_status_id, language_id, orders_status_name, public_flag, downloads_flag) values ('3', '2', 'Versendet', '1', '1');
insert into orders_status (orders_status_id, language_id, orders_status_name, public_flag, downloads_flag) values ('3', '3', 'Entregado', '1', '1');

[lacoz]

An sql file is a plain text file. You can view and edit it with a text editor (like the one you are using to edit PHP). There is no real reason why it should it not be possible to edit your existing db.sql file so that restoring it does not give any errors.

 

And yes, you can copy and paste parts of that file to only restore certain tables. A regular osC backup file starts with deleting the existing table (drop table command), then rebuilding the table structure and then inserting values into it. For example to restore the table orders_status you would need this part from a backup file:

 

Ok so if I want to restore only the categories and product tables, then I will delete all other commands except these.

 

Jack said there are 2 tables for categories and 9 for products. Can You please write down for me, which ones?

 

Thanks a lot.

[Jan Zonjee]

Jack said there are 2 tables for categories and 9 for products. Can You please write down for me, which ones?

I prefer not to go down that route. You might have installed contributions, or I might forget a table that is important somewhere down the road.

 

Better tell us what errors you get when you restore your database first and see if we can fix that.

[lacoz]

I prefer not to go down that route. You might have installed contributions, or I might forget a table that is important somewhere down the road.

 

Better tell us what errors you get when you restore your database first and see if we can fix that.

 

The file restored first 46 tables. Then I got some errors regarding the 47. table (I didn´t write them down so I can´t tell You more about the errors) and the rest of the tables wanished. So instead of 52 tables, I had only 46 and the store broke up. Since I dont have sufficient knowledge abou SQL, I decided to drop the entire database and made a new osc install. I also added all the contribution I was using and the only thing missing now are the products. Thats why I want to restore only the categories and product tables :)

[Jan Zonjee]

The file restored first 46 tables. Then I got some errors regarding the 47. table (I didn´t write them down so I can´t tell You more about the errors) and the rest of the tables wanished. So instead of 52 tables, I had only 46 and the store broke up.

No problem doing the restore over and over again. So if you know where the error started you can copy and paste the sql for the 47th table and try to restore that only. See if you get an error.

[lacoz]

No problem doing the restore over and over again. So if you know where the error started you can copy and paste the sql for the 47th table and try to restore that only. See if you get an error.

 

There ist one more thing. Since I made the new install, I added some contributions, that weren´t there before. If I would be able to restore the entire database, would that do some harm to the new contributions?

[Jan Zonjee]

Since I made the new install, I added some contributions, that weren´t there before. If I would be able to restore the entire database, would that do some harm to the new contributions?

If there were sql changes (so changes to the database) then yes. But if your products are not in there yet, I would assume it easy to run those sql changes again?

[lacoz]

If there were sql changes (so changes to the database) then yes. But if your products are not in there yet, I would assume it easy to run those sql changes again?

 

Yes there were also sql changes. Ok I´ll try to run just the 47th table...

[lacoz]

If there were sql changes (so changes to the database) then yes. But if your products are not in there yet, I would assume it easy to run those sql changes again?

 

Hm very strange. Now I tried to run the old sql backup, it didn´t drop the new table (antirobotreg), but stopped by product_description. The error is 1064 in syntax:

 

INSERT INTO products_description( products_id, language_id, products_name, products_description, products_url, products_viewed )

VALUES (

'30', '1', 'Pokladnition (products_id, language_id, products_name, products_description, products_url, products_viewed) values ('152 ', '1 ', 'Debnicts_viewed

)

VALUES (

'246', '1', 'Drevenzcoa', 'Guipuzcoa'

);

 

 

 

I looked into the file and I didn´t find anything like this. The products_descriptions are properly listed from 30 till 297...

[Jan Zonjee]

Hm very strange. Now I tried to run the old sql backup, it didn´t drop the new table (antirobotreg), but stopped by product_description. The error is 1064 in syntax:

 

INSERT INTO products_description( products_id, language_id, products_name, products_description, products_url, products_viewed )

VALUES (

'30', '1', 'Pokladnition (products_id, language_id, products_name, products_description, products_url, products_viewed) values ('152 ', '1 ', 'Debnicts_viewed

)

VALUES (

'246', '1', 'Drevenzcoa', 'Guipuzcoa'

);

 

 

 

I looked into the file and I didn´t find anything like this. The products_descriptions are properly listed from 30 till 297...

You should be able to find that back. It looks like some parts are completely mixed up. A standard osC backup will do an insert statement for each row in the table like:

insert into products_description (products_id, language_id, products_name, products_description, products_url, products_viewed) values ('8', '1', 'A Bug\'s Life', 'Regional Code: 2 (Japan, Europe, Middle East, South Africa).
<br>
Languages: English, Deutsch.
<br>
Subtitles: English, Deutsch, Spanish.
<br>
Audio: Dolby Digital 5.1 / Dobly Surround Stereo.
<br>
Picture Format: 16:9 Wide-Screen.
<br>
Length: (approx) 91 minutes.
<br>
Other: Interactive Menus, Chapter Selection, Subtitles (more languages).', 'www.abugslife.com', '0');

Are you sure the character set that the two databases use is the same. The MySQL database uses UTF-8 as a standard nowadays I think. I have had issues (no serious ones though) when transferring backups from utf-8 to an iso-8859-1 MySQL database.

 

Looks like you used phpMyAdmin for the backup?

[lacoz]

You should be able to find that back. It looks like some parts are completely mixed up. A standard osC backup will do an insert statement for each row in the table like:

insert into products_description (products_id, language_id, products_name, products_description, products_url, products_viewed) values ('8', '1', 'A Bug\'s Life', 'Regional Code: 2 (Japan, Europe, Middle East, South Africa).
<br>
Languages: English, Deutsch.
<br>
Subtitles: English, Deutsch, Spanish.
<br>
Audio: Dolby Digital 5.1 / Dobly Surround Stereo.
<br>
Picture Format: 16:9 Wide-Screen.
<br>
Length: (approx) 91 minutes.
<br>
Other: Interactive Menus, Chapter Selection, Subtitles (more languages).', 'www.abugslife.com', '0');

Are you sure the character set that the two databases use is the same. The MySQL database uses UTF-8 as a standard nowadays I think. I have had issues (no serious ones though) when transferring backups from utf-8 to an iso-8859-1 MySQL database.

 

Looks like you used phpMyAdmin for the backup?

 

 

Yes I used phpMyAdmin. When I log into it, I see the UTF-8 set. Should I change that to iso-8859-1? I have special characters in the product names and descriptions (I am from Europe), so maybe thats why it is mixed up. I also have the <br> tags in the product descriptions, because that was the only way, I was able to devide the text, when describing the products in osc-admin panel :)

[Jan Zonjee]

Yes I used phpMyAdmin. When I log into it, I see the UTF-8 set. Should I change that to iso-8859-1?

No, absolutely not.

I have special characters in the product names and descriptions (I am from Europe), so maybe thats why it is mixed up.

Yes, English is the simplest language in that respect. The character set of the two databases should ideally be the same for a smooth restore. If one is different from the other it can perhaps be fixed with changing the character set of the backup first. For example there is a unix command line "thing" to change utf-8 to for example iso-8859-1 on a Mac. I used that to first convert the character set of a utf-8 backup to a iso-8859-1 to import it again in an (old) MySQL version.

 

I also have the <br> tags in the product descriptions, because that was the only way, I was able to devide the text, when describing the products in osc-admin panel :)

That is no problem for the restore.

[lacoz]

Yes, English is the simplest language in that respect. The character set of the two databases should ideally be the same for a smooth restore. If one is different from the other it can perhaps be fixed with changing the character set of the backup first. For example there is a unix command line "thing" to change utf-8 to for example iso-8859-1 on a Mac. I used that to first convert the character set of a utf-8 backup to a iso-8859-1 to import it again in an (old) MySQL version.

 

Sorry but I didn´t understand much from this :(

 

When I open the sql file, which I have stored on my desktop, it says DEFAULT CHARACTER SET utf8 COLLATE utf8_czech_ci.

So when I logged in the phpMyAdmin, I changed to utf8_czech. I tried to import the entire backup file again. Again with an error, this time it was 1062 duplicate entry and it seems to be mixed up again :(

[lacoz]

I just tried to separate the sql commands for each table and it looks like it is working. So I will try to restore all tables one by one :)

 

Thanks a lot for Your help. You saved me a couple days of work...

[lacoz]

Oh and one more issue...

 

I have added a contribution Flash Carousel, working well, but after I installed the ultimate seo url´s and when the flash carousel is switched on, I get an error:

 

Notice: Undefined variable: cName in .../includes/classes/seo.class.php on line 1006

 

the affected function is

/**
* Function to get the product name. Use evaluated cache, per page cache, or database query in that order of precedent        
* @author Bobby Easland 
* @version 1.1
* @param integer $pID
* @return string Stripped anchor text
*/        
       function get_product_name($pID){
          $result = array();
          if ($this->attributes['SEO_ADD_CPATH_TO_PRODUCT_URLS'] == 'true') {
             $cName = $this->get_all_category_parents($pID, $cName);
          }
               switch(true){
                       case ($this->attributes['USE_SEO_CACHE_GLOBAL'] == 'true' && defined('PRODUCT_NAME_' . $pID)):
                               $this->performance['CACHE_QUERY_SAVINGS']++;
                               $return = (tep_not_null($cName) ? $cName . '-'. constant('PRODUCT_NAME_' . $pID) : constant('PRODUCT_NAME_' . $pID));
                               $this->cache['PRODUCTS'][$pID] = $return;
                               break;
                       case ($this->attributes['USE_SEO_CACHE_GLOBAL'] == 'true' && isset($this->cache['PRODUCTS'][$pID])):
                               $this->performance['CACHE_QUERY_SAVINGS']++;
                               $return = (tep_not_null($cName) ? $cName . '-'. $this->cache['PRODUCTS'][$pID] : $this->cache['PRODUCTS'][$pID]);
                               break;
                       default:
                               $this->performance['NUMBER_QUERIES']++;
                               $sqlCmd = $this->attributes['USE_SEO_HEADER_TAGS'] == 'true' ? 'products_name as pName' : 'products_name as pName';
                               $sql = "SELECT " . $sqlCmd . "
                                         FROM ".TABLE_PRODUCTS_DESCRIPTION." 
                                         WHERE products_id='".(int)$pID."' 
                                         AND language_id='".(int)$this->languages_id."' 
                                         LIMIT 1";
                               $result = $this->DB->FetchArray( $this->DB->Query( $sql ) );

                               $pName = $this->strip( $result['pName'] );
                               $this->cache['PRODUCTS'][$pID] = $pName;
                               $this->performance['QUERIES']['PRODUCTS'][] = $sql;
                               $return = (tep_not_null($cName) ? $cName . '-'.  $pName : $pName);
                               break;                                                                
               } # end switch                
               return $return;
       } # end function

 

 

line 1006 is

$return = (tep_not_null($cName) ? $cName . '-'. constant('PRODUCT_NAME_' . $pID) : constant('PRODUCT_NAME_' . $pID));

 

When the Carousel is turned off, the store loads without errors.

 

Do You have any idea, why?

[Jan Zonjee]

When the Carousel is turned off, the store loads without errors.

 

Do You have any idea, why?

Sorry, no clue. Is there a topic for that contribution? Do you have a well working version? I know there are issues with some versions (FWR Media is more of an expert on seo urls issues...).

[lacoz]

Sorry, no clue. Is there a topic for that contribution? Do you have a well working version? I know there are issues with some versions (FWR Media is more of an expert on seo urls issues...).

 

Ok I´ll try it elswhere. Once again thanks for support with the database :)