Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

HELP! - Database corruption issues


Itchy Feet

Recommended Posts

Hi there,

A couple of days ago the site I am working on suffered some database issues. It would appear that connections to the database were not closed properly by the OSCommerce script, causing errors and leading to the corruption of data. As a consequence I have lost info on 27 products and there is one category that has been lost as well.

I really need to know how I can re-associate products to the lost category? If I create the category again and all the sub-categories, how do I go about re-associating all the products that were once a part of those categories to the categories once again without having to re-input each product? I really don't want to re-input them all since they are still on the database and because there are hundreds of them, and the majority of the products are still on the database, but just aren't associated to any category and so are not available to customers on visiting the site.

Unfortunately the backups on the server were also corrupted and so I cannot restore the database through the normal means.

Can anyone help me???

Thanks in advance for your help.

Chris King

Link to comment
Share on other sites

Use your phpMyAdmin to investigate the categories tables and find the id of the lost category. Then create a new one and change the id manually to the id of the lost one. After that the products assignment should work again if there were no other corrupted data related to the category/subcategory assignment.

Link to comment
Share on other sites

Hi there,

Thank-you for your advice. I have looked at the database and it would appear that all the sub-categories have also disappeared, so I assume I simply do the same thing - create all the subcategories and then change the id? It that right?

Cheers,

Chris

Link to comment
Share on other sites

Thank-you...and yes, I will indeed start making backups regularly - I thought I could rely on the host, but clearly not.

Just as a matter of interest, does anyone know how this may have occurred? Why did OSCommerce not close the connections properly? How do I ensure that this doesn't happen again (beyond making back-ups)?

Thanks for all your help.

Chris

Link to comment
Share on other sites

Multiple factors could be responsible and I am not sure how have we narrowed this down to OSC not closing connection.

For a proper root cause - we need to check what was being done, when this happened, and why did so many tables get affected. Most of the time you would be doing Select query ( and thus only reading data) instead of add / update tables ... and thus should not be the cause.

 

How reliable is your host, and do you have a sql log / error log to check which query took long / timed out or remained hung ...

 

Questions but no definite answers from this part of the world at this stage.

 

Cheers

Gaurav

Best Regards,
Gaurav

Link to comment
Share on other sites

Hi there,

I will look into the sql log, and try and dig a little deeper. The host is professional and reliable, but as to whether they have communicated accurately as to the cause I do not know.

I have actually found an old sql file I had downloaded locally. Is it correct in saying that if I cut the necessary 'insert' commands for all the categories, category descriptions and used phpMyAdmin to run the sql commands that that will do the job? Is it just a case of uploading a document full of :

 

insert into categories (categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status) values ('676', '', '22', '55', '2006-05-02 16:10:06', '2007-01-21 15:49:02', '0');

 

or is there more to it than that??

Thanks for all your help.

Chris

Link to comment
Share on other sites

Hi there,

I will look into the sql log, and try and dig a little deeper. The host is professional and reliable, but as to whether they have communicated accurately as to the cause I do not know.

I have actually found an old sql file I had downloaded locally. Is it correct in saying that if I cut the necessary 'insert' commands for all the categories, category descriptions and used phpMyAdmin to run the sql commands that that will do the job? Is it just a case of uploading a document full of :

 

insert into categories (categories_id, categories_image, parent_id, sort_order, date_added, last_modified, categories_status) values ('676', '', '22', '55', '2006-05-02 16:10:06', '2007-01-21 15:49:02', '0');

 

or is there more to it than that??

Thanks for all your help.

Chris

If this is SQL dump.. you can use phpmyadmin to run these queries and restore data... you may run into issues with primary key violation (like 676 - already defined ) and when you insert, primary key with auto_increment feature would try to assign a new key ... if you have complete data in these log files, I would advice manually editing and modifying few items that have a problem. if entire table is blank - run the queries you have found in your archive.

 

Cheers

Gaurav

Best Regards,
Gaurav

Link to comment
Share on other sites

If this is SQL dump.. you can use phpmyadmin to run these queries and restore data... you may run into issues with primary key violation (like 676 - already defined ) and when you insert, primary key with auto_increment feature would try to assign a new key ... if you have complete data in these log files, I would advice manually editing and modifying few items that have a problem. if entire table is blank - run the queries you have found in your archive.

 

Cheers

Gaurav

 

Hi there,

I have successfully inserted the categories and category descriptions into the database (just taking those categories that were missing and inserting them into the table) but they are still not coming up on the admin section of OSCommerce, and consequently the main site. Why is that? the information is now there and so surely OSCommerce should be reading it??

Cheers,

Chris

Link to comment
Share on other sites

Hi there,

Ok, so I have got the categories sorted, but despite the products still being in the database they are not being associated with the categories. I will look at the product files, but it is pretty crazy just how much has been affected by this.

Cheers,

Chris

Link to comment
Share on other sites

I have successfully inserted the categories and category descriptions into the database (just taking those categories that were missing and inserting them into the table) but they are still not coming up on the admin section of OSCommerce, and consequently the main site. Why is that? the information is now there and so surely OSCommerce should be reading it??

Yes - if data is restored osc (query) should display it. If it is not - there may be a problem in relationship for one product between tables. Try to place debug info (echo SQL query) and run it in phpmyadmin to check what is being retrieved from the database. If product doesn't show up in phpmyadmin query - you would have to see which all tables is the data being pulled from, and then check them to ensure all values are - as they should be (specially primary key's etc)

 

Hope that helps,

Gaurav

Best Regards,
Gaurav

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...