Cannot Access Products in Cart

[joe.davila]

I'm getting the following error when I try to open any product in my store:

1054 Unknown column 'tax_class_id' in 'field list'
in:
[select tax_class_id, tax_class_title from tax_class order by tax_class_title]

I can't access any products to make changes to them.


We have been running our store for over 1 year and have all the tax classes, zones, etc set up and this has never been an issue for us. This is the first time we are experiencing this, but are not sure what caused this.

I've added the fields (tax_class_id, tax_class_title) in the sql statement to the tax_class table. After doing this I was able to at least access the products, but the Tax Class doesn't work correctly. The net and gross amounts aren't updating correctly, when I select Taxable Product the Gross Amount doesn't update.

Any help on this would be GREATLY appreciated.

[DrByte]

Sounds like one or more of your database tables is seriously damaged.
The next (unpleasant, laborious) task is to audit the structure of all your database tables and see what's different.
Then deal with whether important records are missing.
Then deal with figuring out who changed things and why.
Has your site been hacked? Maybe you should treat it as though you've been hacked and go through all the hack-recovery steps until you can prove that you were *not* hacked. Otherwise you won't know for sure.
Related articles:
http://www.zen-cart.com/forum/showthread.php?t=130161
http://www.zen-cart.com/forum/showthread.php?t=131115
http://www.zen-cart.com/wiki/index.p...ing_From_Hacks

[joe.davila]

That's what I was afraid of. Yesterday I noticed that there were 321 tables in my production zen cart MySQL database, there are normally only 98 tables. When I try to access any other database in phpMyAdmin I don't get any tables to appear, yet the Admin UI on my test site works just fine to update categories/products. I wanted to look at the tables in my test environment to see if there were different fields in the tax_class table. This would be to rule out the possibility of changes to code that now reference new database table fields that may not have been present in older versions of the cart.

If I were to attempt a database recovery process, how can I ensure that the data entered after the day we decide to roll back to makes it back into the recovered database?

[joe.davila]

Another thing I should mention is that the online store still calculates the correct tax for customer orders. I've tested that I can still successfully place an order and all seems okay there. This seems to be isolated to the admin console.

my site is www.mermagica.com

[DrByte]

If I were to attempt a database recovery process, how can I ensure that the data entered after the day we decide to roll back to makes it back into the recovered database?

That requires careful and experienced understanding of the data stored in all the tables. It's EXTREMELY time-consuming and intricate, and NOT recommended unless you already understand the issues at stake.
Essentially, you'd need to preserve all the records, and manually merge them back in, before ANY new transactions happen.
I recommend hiring an experienced professional if you intend to go that route.

[DrByte]

Yesterday I noticed that there were 321 tables in my production zen cart MySQL database, there are normally only 98 tables.

I suggest you start by figuring out what all those extra tables are for.

Another thing I should mention is that the online store still calculates the correct tax for customer orders. I've tested that I can still successfully place an order and all seems okay there. This seems to be isolated to the admin console.

Are your admin and storefront pointed to the same database via the configure.php files?

[DrByte]

What happened when you did the comparisons and patching I recommended earlier?

[joe.davila]

I'm all patched up.

I haven't looked into the extra tables yet, but I am going to drop the existing tax_class table then recreate and reload it with a backup prior to the change.

Both the configure.php files (in store\includes and store\admin\includes point to the same database.

[joe.davila]

When I try to run this sql code I get the following error:

SQL query:

--
-- Table structure for table `tax_class`
--
DROP TABLE IF EXISTS `tax_class` ;

MySQL said: Documentation
#1217 - Cannot delete or update a parent row: a foreign key constraint fails


What is the best way to proceed? Is there a way I can get a data dictionary to show me the primary key dependencies among the tables in zen cart?

Alternatively I could just export all the data from the existing tables of orders, customers, etc (all activity that's happened since 12/1/09). And then restore my database back to 12/1/09 and reload only the additional data. Is there an easy way to do this with scripts or tools?

[joe.davila]

The SQL...

--
-- Table structure for table `tax_class`
--

DROP TABLE IF EXISTS `tax_class`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tax_class` (
`tax_class_id` int(11) NOT NULL auto_increment,
`tax_class_title` varchar(32) NOT NULL default '',
`tax_class_description` varchar(255) NOT NULL default '',
`last_modified` datetime default NULL,
`date_added` datetime NOT NULL default '0001-01-01 00:00:00',
PRIMARY KEY (`tax_class_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tax_class`
--

LOCK TABLES `tax_class` WRITE;
/*!40000 ALTER TABLE `tax_class` DISABLE KEYS */;
INSERT INTO `tax_class` (`tax_class_id`, `tax_class_title`, `tax_class_description`, `last_modified`, `date_added`) VALUES (1,'Taxable Goods','The following types of products are included: non-food, services, etc',NULL,'2008-08-04 22:57:31');
/*!40000 ALTER TABLE `tax_class` ENABLE KEYS */;
UNLOCK TABLES;