Cannot Access Products in Cart

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.
:frusty:

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.

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.php/Recovering_From_Hacks

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?

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 https://www.mermagica.com

joe.davila:

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.

joe.davila:

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.

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.
Are your admin and storefront pointed to the same database via the configure.php files?

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

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.

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?

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;

joe.davila:

#1217 - Cannot delete or update a parent row: a foreign key constraint fails
Zen Cart v1.x doesn't have ANY (database-level-enforced) foreign key constraints unless you've added addons to build them.
All the v1.x constraints are enforced at the software (PHP) level.

So, if you're getting that error at the MySQL level, you, or someone, have altered your database to cause it.

Wow! What a day in troubleshooting he**!
I finally figured out that this resulted from a Magento cart installation that should not have been performed on the root directory where Zen Cart already resided. Anyway, I had to delete over 100 Magento tables and fortunately the only tables that shares the exact same name between both cart programs is tax_class and tax_rates. When Magento was accidentally loaded on the same directory with Zen Cart it overwrote these 2 tables.

To fix the issue, I ripped the table structure and data from a backup and reloaded the Zen Cart tables. Wow, what a lesson to learn, this could have turned out WAY worse for me.

DrByte, I appreciate all your help with this.
Case resolved.
:bigups:

LOL - that'll learn you not to go messing around with other inferior applications when you've already got Zen Cart . :blush: :)