1.3.5 to 1.3.6 under Fantasico - how to fix the sql

[Moebiusenterprises]

Once again, upgrading from 1.3.5 to 1.3.6 by using Fantastico's automatic upgrade will fail horridly. Fantastico alters the format in the DB names, and usually manages to forget that fact about 3/4 of the way into the sql upgrade.
Your upgrade will encounter an error when it hits the upgrade of get_terms_to_filter, causing the db not to completely update and giving you that infamous "your db appears to need patching..." error flag.
This is how to "fix" the problem:

1) aquire the 1.3.6 Zip file, learn how it works
2) the sql portion that has failed is a file called msql_upgrade_zencart_135_to_136.sql
3) fantastico adds the prefix "zen_" to the tables in your zencart database. If you attempt to run the scripts as they are in the upgrade, it will fail - because it doesn't know what you're talking about. I've edited the failed part of the scripts so they will work to upgrade your fantastico installation below. The *best* fix is to manually upgrade, but some hosts won't permit this (because sadly there are folk out there without a clue), but it you can't do this yourself, this will get you back up & running.

***********
#
# Updating table get_terms_to_filter
ALTER TABLE zen_get_terms_to_filter ADD get_term_table varchar(64) NOT NULL;
ALTER TABLE zen_get_terms_to_filter ADD get_term_name_field varchar(64) NOT NULL;
UPDATE zen_get_terms_to_filter SET get_term_table = 'TABLE_MANUFACTURERS', get_term_name_field = 'manufacturers_name' where get_term_name = 'manufacturers_id';
UPDATE zen_get_terms_to_filter SET get_term_table = 'TABLE_MUSIC_GENRE', get_term_name_field = 'music_genre_name' where get_term_name = 'music_genre_id';
UPDATE zen_get_terms_to_filter SET get_term_table = 'TABLE_RECORD_COMPANY', get_term_name_field = 'record_company_name' where get_term_name = 'record_company_id';

# Updating coupon-related tables
ALTER TABLE zen_coupons ADD coupon_zone_restriction INT( 11 ) NOT NULL DEFAULT '0';
ALTER TABLE zen_coupons CHANGE coupon_minimum_order coupon_minimum_order DECIMAL( 15, 4 ) NOT NULL DEFAULT '0.0000';
ALTER TABLE zen_coupons CHANGE coupon_amount coupon_amount DECIMAL( 15, 4 ) NOT NULL DEFAULT '0.0000';
ALTER TABLE zen_coupon_gv_customer CHANGE amount amount DECIMAL( 15, 4 ) NOT NULL DEFAULT '0.0000';
ALTER TABLE zen_coupon_gv_queue CHANGE amount amount DECIMAL( 15, 4 ) NOT NULL DEFAULT '0.0000';

# Removing auto-increment from these tables
ALTER TABLE zen_meta_tags_categories_description CHANGE COLUMN categories_id categories_id int(11) NOT NULL;
ALTER TABLE zen_meta_tags_products_description CHANGE COLUMN products_id products_id int(11) NOT NULL;


################################################################


#############

#### VERSION UPDATE COMMANDS
## THE FOLLOWING 2 SECTIONS SHOULD BE THE "LAST" ITEMS IN THE FILE, so that if the upgrade fails prematurely, the version info is not updated.
##The following updates the version HISTORY to store the prior version's info (Essentially "moves" the prior version info from the "project_version" to "project_version_history" table
#NEXT_X_ROWS_AS_ONE_COMMAND:3
INSERT INTO zen_project_version_history (project_version_key, project_version_major, project_version_minor, project_version_patch, project_version_date_applied, project_version_comment)
SELECT project_version_key, project_version_major, project_version_minor, project_version_patch1 as project_version_patch, project_version_date_applied, project_version_comment
FROM zen_project_version;

## Now set to new version
UPDATE zen_project_version SET project_version_major='1', project_version_minor='3.6', project_version_patch1='', project_version_patch1_source='', project_version_patch2='', project_version_patch2_source='', project_version_comment='Version Update 1.3.5->1.3.6', project_version_date_applied=now() WHERE project_version_key = 'Zen-Cart Main';
UPDATE zen_project_version SET project_version_major='1', project_version_minor='3.6', project_version_patch1='', project_version_patch1_source='', project_version_patch2='', project_version_patch2_source='', project_version_comment='Version Update 1.3.5->1.3.6', project_version_date_applied=now() WHERE project_version_key = 'Zen-Cart Database';

[rajaito]

In attempt to upgrade from 1.3.5 to 1.3.7 I received the 1044 error, so I tried this script fix. Now I am getting an error message that says:

Your database appears to need patching to a higher level. See Admin->Tools->Server Information to review patch levels.

Not too sure what I am looking for here. The biggest pain is at the moment I do not have direct database access to see what's going on.

Suggestions?

[afo]

The best advice I can give anyone is STOP USING FANTASTICO TO INSTALL/UPGRADE ZEN CART.

The manual install is comes with instructions, is simple enough and works well.

To fix db mismatches, I suggest downloading the 1.3.7 install, unzipping it on your hard drive and FTPing the zc_install directory to where you have ZC installed. Run the installer for db upgrade only. The sniffer will automatically select the upgrades you need. Follow the instructions from there.

[rajaito]

Thank you afo. I already have attempted to use the auto upgrade feature built into zencart. The sniffer found we needed two upgrades. When proceeding to run the upgrade we got a 1044 error and now we are getting an 1146 error.

1146 Table 'cotton91.zen_zen_get_terms_to_filter' doesn't exist
in:
[show fields from zen_zen_get_terms_to_filter]


I am not sure but maybe zen_zen_ has something to do with it?
regardless, The sniffer now thinks its upgraded, which it might be. but I am still getting the original error:

Your database appears to need patching to a higher level. See Admin->Tools->Server Information to review patch levels.

[afo]

Go into your admin and look under tools->server/version info. You should see something like this.

Zen Cart 1.3.7
Database Patch Level: 1.3.7

What does it say in your case?

[rajaito]

Zen Cart 1.3.7

Database Patch Level: 1.3.5

v1.3.5 [2006-09-11 20:23:39] (Fresh Installation)