Database needs patching ?

[cnymike]

The reason on the upgrade screen for these is to insure that any additional tables are written with the correct prefix if there is one.

How many tables do you now have versus before? You do have a backup correct?

Insure that all tables either have a prefix or that none do.

All my tables have the prefix. It looks like the "upgraded" database has an additional two tables

zen_nochex_apc_transactions
zen_nochex_sessions

There is also a lengthy "zen_upgrade_exceptions" table in both old and new databases. The table is much larger in the "upgraded" database however. Is this something I should be worried about?


Michael

--
-- Table structure for table `zen_upgrade_exceptions`
--

DROP TABLE IF EXISTS `zen_upgrade_exceptions`;
CREATE TABLE `zen_upgrade_exceptions` (
`upgrade_exception_id` smallint(5) NOT NULL auto_increment,
`sql_file` varchar(50) default NULL,
`reason` varchar(200) default NULL,
`errordate` datetime default '0001-01-01 00:00:00',
`sqlstatement` text,
PRIMARY KEY (`upgrade_exception_id`)
) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `zen_upgrade_exceptions`
--

LOCK TABLES `zen_upgrade_exceptions` WRITE;
/*!40000 ALTER TABLE `zen_upgrade_exceptions` DISABLE KEYS */;
INSERT INTO `zen_upgrade_exceptions` VALUES (1,'sql/mysql_upgrade_zencart_127_to_130.sql','Cannot drop index idx_categories_parent_id_zen on table categories because it does not exist.','2007-10-02 11:12:27','ALTER TABLE categories DROP INDEX idx_categories_parent_id_zen;'),(2,'sql/mysql_upgrade_zencart_127_to_130.sql','Cannot drop index idx_customer_id_zen on table coupon_gv_customer because it does not exist.','2007-10-02 11:12:27','ALTER TABLE coupon_gv_customer DROP INDEX idx_customer_id_zen;'),(3,'sql/mysql_upgrade_zencart_127_to_130.sql','Cannot drop index idx_paypal_paypal_ipn_id_zen on table paypal because it does not exist.','2007-10-02 11:12:27','ALTER TABLE paypal DROP INDEX idx_paypal_paypal_ipn_id_zen;'),(4,'sql/mysql_upgrade_zencart_127_to_130.sql','Cannot drop index idx_prod_opt_val_id_zen on table products_options_values because it does not exist.','2007-10-02 11:12:27','ALTER TABLE products_options_values DROP INDEX idx_prod_opt_val_id_zen;'),(5,'sql/mysql_upgrade_zencart_130_to_1301.sql','Cannot insert configuration_group_title \"EZ-Pages Settings\" because it already exists','2007-10-02 11:12:27','REPLACE INTO configuration_group VALUES (\'30\', \'EZ-Pages Settings\', \'EZ-Pages Settings\', 30, \'1\');'),(6,'sql/mysql_upgrade_zencart_1301_to_1302.sql','Cannot insert configuration_key \"UPLOAD_PREFIX\" because it already exists','2007-10-02 11:12:27','INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES (\'Upload prefix\', \'UPLOAD_PREFIX\', \'upload_\', \'Prefix used to differentiate between upload options and other options\', 0, NULL, now(), now(), NULL, NULL);'),(7,'sql/mysql_upgrade_zencart_1301_to_1302.sql','Cannot insert configuration_key \"TEXT_PREFIX\" because it already exists','2007-10-02 11:12:27','INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES (\'Text prefix\', \'TEXT_PREFIX\', \'txt_\', \'Prefix used to differentiate between text option values and other option values\', 0, NULL, now(), now(), NULL, NULL);'),(8,'sql/mysql_upgrade_zencart_1302_to_135.sql','Cannot drop index idx_auth_net_id on table authorizenet because it does not exist.','2007-10-02 11:12:28','ALTER TABLE authorizenet DROP INDEX idx_auth_net_id;'),(9,'sql/mysql_upgrade_zencart_136_to_137.sql','Cannot insert configuration_key \"DEFINE_PAGE_NOT_FOUND_STATUS\" because it already exists','2007-10-02 11:12:28','INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) values (\'Define Page-Not-Found Status\', \'DEFINE_PAGE_NOT_FOUND_STATUS\', \'1\', \'Enable the Defined Page-Not-Found Text from define-pages?<br />0= Define Text OFF<br />1= Define Text ON\', \'25\', \'67\', now(), now(), NULL, \'zen_cfg_select_option(array(\\\'0\\\', \\\'1\\\'),\');'),(10,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE txn_type txn_type varchar(40) NOT NULL default \'\';'),(11,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE reason_code reason_code varchar(40) default NULL;'),(12,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE payment_type payment_type varchar(40) NOT NULL default \'\';'),(13,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE payment_status payment_status varchar(32) NOT NULL default \'\';'),(14,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE pending_reason pending_reason varchar(32) default NULL;'),(15,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE invoice invoice varchar(128) default NULL;'),(16,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE payer_business_name payer_business_name varchar(128) default NULL;'),(17,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE address_name address_name varchar(64) default NULL;'),(18,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE address_street address_street varchar(254) default NULL;'),(19,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE address_city address_city varchar(120) default NULL;'),(20,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE address_state address_state varchar(120) default NULL;'),(21,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE payer_email payer_email varchar(128) NOT NULL default \'\';'),(22,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE business business varchar(128) NOT NULL default \'\';'),(23,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE receiver_email receiver_email varchar(128) NOT NULL default \'\';'),(24,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE txn_id txn_id varchar(20) NOT NULL default \'\';'),(25,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE parent_txn_id parent_txn_id varchar(20) default NULL;'),(26,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing ADD COLUMN module_name varchar(40) NOT NULL default \'\' after txn_type;'),(27,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing ADD COLUMN module_mode varchar(40) NOT NULL default \'\' after module_name;'),(28,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE paypal_testing CHANGE COLUMN zen_order_id order_id int(11) NOT NULL default \'0\';'),(29,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot ALTER or INSERT/REPLACE into table linkpoint_api because it does not exist. CHECK PREFIXES!','2008-02-09 15:08:03','ALTER TABLE linkpoint_api CHANGE COLUMN zen_order_id order_id int(11) NOT NULL default \'0\';'),(30,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot insert configuration_key \"SHOW_SHOPPING_CART_COMBINED\" because it already exists','2008-02-09 15:10:01','INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES (\'Show Notice of Combining Shopping Cart on Login\', \'SHOW_SHOPPING_CART_COMBINED\', \'1\', \'When a customer logs in and has a previously stored shopping cart, the products are combined with the existing shopping cart.<br /><br />Do you wish to display a Notice to the customer?<br /><br />0= OFF, do not display a notice<br />1= Yes show notice and go to shopping cart<br />2= Yes show notice, but do not go to shopping cart\', \'9\', \'35\', \'zen_cfg_select_option(array(\\\'0\\\', \\\'1\\\', \\\'2\\\'), \', now());'),(31,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot insert configuration_key \"ORDER_COMMENTS_INVOICE\" because it already exists','2008-02-09 15:10:01','INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES (\'Display Order Comments on Admin Invoice\', \'ORDER_COMMENTS_INVOICE\', \'1\', \'Do you want to display the Order Comments on the Admin Invoice?<br />0= OFF<br />1= First Comment by Customer only<br />2= All Comments for the Order\', 7, 25, now(), NULL, \'zen_cfg_select_option(array(\'\'0\'\', \'\'1\'\', \'\'2\'\'), \');'),(32,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot insert configuration_key \"ORDER_COMMENTS_PACKING_SLIP\" because it already exists','2008-02-09 15:10:01','INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES (\'Display Order Comments on Admin Packing Slip\', \'ORDER_COMMENTS_PACKING_SLIP\', \'1\', \'Do you want to display the Order Comments on the Admin Packing Slip?<br />0= OFF<br />1= First Comment by Customer only<br />2= All Comments for the Order\', 7, 26, now(), NULL, \'zen_cfg_select_option(array(\'\'0\'\', \'\'1\'\', \'\'2\'\'), \');'),(33,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot insert configuration_key \"SHOW_SHOPPING_CART_COMBINED\" because it already exists','2008-02-09 15:11:20','INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES (\'Show Notice of Combining Shopping Cart on Login\', \'SHOW_SHOPPING_CART_COMBINED\', \'1\', \'When a customer logs in and has a previously stored shopping cart, the products are combined with the existing shopping cart.<br /><br />Do you wish to display a Notice to the customer?<br /><br />0= OFF, do not display a notice<br />1= Yes show notice and go to shopping cart<br />2= Yes show notice, but do not go to shopping cart\', \'9\', \'35\', \'zen_cfg_select_option(array(\\\'0\\\', \\\'1\\\', \\\'2\\\'), \', now());'),(34,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot insert configuration_key \"ORDER_COMMENTS_INVOICE\" because it already exists','2008-02-09 15:11:20','INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES (\'Display Order Comments on Admin Invoice\', \'ORDER_COMMENTS_INVOICE\', \'1\', \'Do you want to display the Order Comments on the Admin Invoice?<br />0= OFF<br />1= First Comment by Customer only<br />2= All Comments for the Order\', 7, 25, now(), NULL, \'zen_cfg_select_option(array(\'\'0\'\', \'\'1\'\', \'\'2\'\'), \');'),(35,'sql/mysql_upgrade_zencart_137_to_138.sql','Cannot insert configuration_key \"ORDER_COMMENTS_PACKING_SLIP\" because it already exists','2008-02-09 15:11:20','INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES (\'Display Order Comments on Admin Packing Slip\', \'ORDER_COMMENTS_PACKING_SLIP\', \'1\', \'Do you want to display the Order Comments on the Admin Packing Slip?<br />0= OFF<br />1= First Comment by Customer only<br />2= All Comments for the Order\', 7, 26, now(), NULL, \'zen_cfg_select_option(array(\'\'0\'\', \'\'1\'\', \'\'2\'\'), \');');
/*!40000 ALTER TABLE `zen_upgrade_exceptions` ENABLE KEYS */;
UNLOCK TABLES;

[kobra]

Does your shop work?

[cnymike]

I have placed a test order and it seems to work. I don't use PayPal so I haven't tested the functionality of that. But regardless if it works for my "simple" test, I'm not confident it will work if I ever need to use PayPal in the future.

It looks to me like the database got screwed up during my upgrade process from a very old version of ZC. I had to go through multiple database upgrades to bring it current and it seems that somehow the database lost some tables somewhere along the upgrade path.

But my store seems to be working.

I'm thinking I should backup the current database.
Create a new empty database
Create a new temporary directory into which I re-install ZC and will therefor also have a brand new database created containing all tables.
Import my backup database into the new database
Check to see if everything looks OK and if so, then backup new database.
Dump my possibly corrupted database in my original ZC installation and import the newly recreated database. If it works, I'd then delete my temporary ZC installation and go about my day whistling while I work.

Does this sound prudent?

[cnymike]

Based on the zen_upgrades_exceptions table, it looks like the table zen_paypal_testing is missing from my previous database, thus during the upgrade all the errors concerning the zen_paypal_testing missing were generated.

When I created the new installation of ZC with new database and then imported by old database into it, the zen_paypal_testing tables was now there.

Should this basically have "fixed" the problem seeing as everything else seems to work as far as I can tell?

I don't know how this happened, all I want is to ensure I have a healthy database.

[kobra]

When I created the new installation of ZC with new database and then imported by old database into it, the zen_paypal_testing tables was now there.

I think you got it!!

[cnymike]

OK, one last question, how would I go about clearing the zen_database_exceptions table? If possible I would like to clear out the errors listed here so that in future database upgrades, it will be easier to read.

[cnymike]

I think all I need to do is EMPTY the table zen_upgrade_exceptions using phpMyAdmin.

But I just wanted to make sure before I do it.

[DrByte]

Yes, you can "empty" or "truncate" the upgrade_exceptions table.

[cnymike]

Awesome. thank you.

Michael