DC 1054 error, and error in 'Product Restrictions' section

[DrByte]

The upgrade was from 1.3.7, which ran for a long time

The database-portion of the upgrade takes about 1 to 5 seconds to run. Anything more than that means there are some serious problems.
But the script you're talking about is probably doing more than that ... and probably doing it very poorly, if history is any indication.
See below:

it was executed by Cpanel/Fantastico built in script

oh geez -- that's the worst way to do an upgrade ... because it has no knowledge of any of your customizations, and simply blindly replaces your files with new ones. That's why winmerge now shows no differences ... because it wiped out all your customizations and your plugins.

A proper upgrade is done by hand: www.zen-cart.com/upgrade

Now you'll need to reconcile all database structure differences in every table by hand. And then manually fix every record that wasn't properly upgraded. Depending on the size of your database that could take a long time.
Or restore your site from the backup you made before upgrading and start over using correct upgrade steps.

[DrByte]

Please complain to Fantastico directly about the mess they've caused you: https://netenberg.com/#customer.html

[isaiah36]

Well, all the 5 patches were applied with the time stamp [2012-02-24 18:09:51] so within 1 second as you said. I have not bumped into any other evidence of mangling quite honestly, although letting Cpanel do it is undoubtedly the "lazy" way of upgrading. The client is cost sensitive and balks at paying for stuff that can be done for free. She does not pay me a dime for fixing Zen issues, even though we only provide the hosting, email and domain service, not application support. I do it anyway, because she is from the same church organization. :)

Is it possible for you to give me an SQL insert command for this coupon_zone_restriction table, Dr. Byte? I am quite content to leave non-fatal issues alone, so long as she can get on with sales. The GV/DC thing is something the client wants to use. As far as I remember there were no SQL mods in the 3 or 4 add-ons we were using, like Sales Report, USPS Auto-fill, etc. - all add-ons appeared to be working just fine after upgrade, and I just updated them to the latest versions.

[DrByte]

I think I see what your problem is.
If you look at the version history you quoted earlier, you'll see that it completely skipped the v1.3.5-to-v1.3.6 upgrade back in 2007.
That's a pretty serious problem.
I'm surprised you haven't run into problems much sooner.


The following instructions SHOULD NEVER BE FOLLOWED under normal circumstances, as the following IS NOT THE RIGHT WAY TO DO IT. But in your unique case it should probably gain you the most benefit:
- open /zc_install/sql/mysql_upgrade_zencart_135_to_136.sql
- copy the file's contents to your clipboard EXCEPT THE LAST 15 LINES which talk about VERSION UPDATE COMMANDS. Skip that whole last section.
- open your Admin->Tools->Install SQL Patches and paste your clipboard into the big field there, and submit the form.
I REPEAT: THIS IS *NOT* THE RIGHT WAY TO DO AN UPGRADE, SO SHOULD NOT BE USED UNDER NORMAL CIRCUMSTANCES.

For future readers of this post: YOU SHOULD NOT FOLLOW THESE INSTRUCTIONS. YOU DO SO AT YOUR OWN RISK. ANY DAMAGE YOU CAUSE IS YOUR OWN ISSUE. DON'T COME ASKING FOR SUPPORT IF YOU BUST YOUR DATABASE BY DOING THE ABOVE. INSTEAD, RESTORE YOUR DATABASE FROM BACKUP BEFORE REQUESTING SUPPORT.

[isaiah36]

OK, just to make sure, I should get this from zc_install directory of version 1.3.9, right?

[isaiah36]

Also, should I or should I not include the hashmarked bits? Since you are going out of your way to help, can I impose on you one more time? Here is the text of the .sql file you mentioned:

#
# * This SQL script upgrades the core Zen Cart database structure from v1.3.5 to v1.3.6
# *
# * @package Installer
# * @access private
# * @copyright Copyright 2003-2006 Zen Cart Development Team
# * @copyright Portions Copyright 2003 osCommerce
# * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
# * @version $Id: mysql_upgrade_zencart_135_to_136.sql 4795 2006-10-20 17:46:58Z ajeh $
#

## CONFIGURATION TABLE
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\'),');
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 ('Currency Conversion Ratio', 'CURRENCY_UPLIFT_RATIO', '1.05', 'When auto-updating currencies, what "uplift" ratio should be used to calculate the exchange rate used by your store?<br />ie: the bank rate is obtained from the currency-exchange servers; how much extra do you want to charge in order to make up the difference between the bank rate and the consumer rate?<br /><br /><strong>Default: 1.05 </strong><br />This will cause the published bank rate to be multiplied by 1.05 to set the currency rates in your store.', 1, 55, NULL, now(), NULL, NULL);

UPDATE configuration SET sort_order = '19' where configuration_key = 'SHOW_COUNTS_ADMIN';
UPDATE configuration SET configuration_description = 'Defines the method for sending mail.<br /><strong>PHP</strong> is the default, and uses built-in PHP wrappers for processing.<br />Servers running on Windows and MacOS should change this setting to <strong>SMTP</strong>.<br /><br /><strong>SMTPAUTH</strong> should only be used if your server requires SMTP authorization to send messages. You must also configure your SMTPAUTH settings in the appropriate fields in this admin section.<br /><br /><strong>sendmail</strong> is for linux/unix hosts using the sendmail program on the server<br /><strong>"sendmail-f"</strong> is only for servers which require the use of the -f parameter to send mail. This is a security setting often used to prevent spoofing. Will cause errors if your host mailserver is not configured to use it.<br /><br /><strong>Qmail</strong> is used for linux/unix hosts running Qmail as sendmail wrapper at /var/qmail/bin/sendmail.', set_function = 'zen_cfg_select_option(array(\'PHP\', \'sendmail\', \'sendmail-f\', \'smtp\', \'smtpauth\', \'Qmail\'),' where configuration_key = 'EMAIL_TRANSPORT';
UPDATE configuration SET configuration_group_id = '6' where configuration_key = 'ENTRY_EMAIL_ADDRESS_CHECK';
UPDATE configuration SET configuration_title = 'Display Product Add to Cart Button (0=off; 1=on; 2=on with Qty Box per Product)', configuration_description = 'Do you want to display the Add to Cart Button?<br /><br /><strong>NOTE:</strong> Turn OFF Display Multiple Products Qty Box Status to use Option 2 on with Qty Box per Product' WHERE configuration_key= 'PRODUCT_LIST_PRICE_BUY_NOW';
UPDATE configuration SET configuration_title = 'Previous Next - Navigation Includes Category Position' WHERE configuration_key= 'PRODUCT_INFO_CATEGORIES';
UPDATE configuration SET set_function='zen_cfg_pull_down_htmleditors(' WHERE configuration_key='HTML_EDITOR_PREFERENCE';

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Top Subcategory Image Width', 'SUBCATEGORY_IMAGE_TOP_WIDTH', '150', 'The pixel width of Top subcategory images<br />Top subcategory is when the Category contains subcategories', '4', '15', now());
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Top Subcategory Image Height', 'SUBCATEGORY_IMAGE_TOP_HEIGHT', '85', 'The pixel height of Top subcategory images<br />Top subcategory is when the Category contains subcategories', '4', '16', now());

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Show New Products on empty Shopping Cart Page', 'SHOW_SHOPPING_CART_EMPTY_NEW_PRODUCTS', '1', 'Show New Products on empty Shopping Cart Page<br />0= off or set the sort order', '9', '30', 'zen_cfg_select_option(array(\'0\', \'1\', \'2\', \'3\', \'4\'), ', now());
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Show Featured Products on empty Shopping Cart Page', 'SHOW_SHOPPING_CART_EMPTY_FEATURED_PRODUCTS', '2', 'Show Featured Products on empty Shopping Cart Page<br />0= off or set the sort order', '9', '31', 'zen_cfg_select_option(array(\'0\', \'1\', \'2\', \'3\', \'4\'), ', now());
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Show Special Products on empty Shopping Cart Page', 'SHOW_SHOPPING_CART_EMPTY_SPECIALS_PRODUCTS', '3', 'Show Special Products on empty Shopping Cart Page<br />0= off or set the sort order', '9', '32', 'zen_cfg_select_option(array(\'0\', \'1\', \'2\', \'3\', \'4\'), ', now());
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Show Upcoming Products on empty Shopping Cart Page', 'SHOW_SHOPPING_CART_EMPTY_UPCOMING', '4', 'Show Upcoming Products on empty Shopping Cart Page<br />0= off or set the sort order', '9', '33', 'zen_cfg_select_option(array(\'0\', \'1\', \'2\', \'3\', \'4\'), ', now());

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Show SubCategories on Main Page while navigating', 'PRODUCT_LIST_CATEGORY_ROW_STATUS', '1', 'Show Sub-Categories on Main Page while navigating through Categories<br /><br />0= off<br />1= on', '8', '60', 'zen_cfg_select_option(array(\'0\', \'1\'), ', now());
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Mask Upcoming Products from being include as New Products', 'SHOW_NEW_PRODUCTS_UPCOMING_MASKED', '0', 'Do you want to mask Upcoming Products from being included as New Products in Listing, Sideboxes and Centerbox?<br />0= off<br />1= on', '21', '30', 'zen_cfg_select_option(array(\'0\', \'1\'), ', now());

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Meta Tags - Include Product Model in Title', 'META_TAG_INCLUDE_MODEL', '1', 'Do you want to include the Product Model in the Meta Tag Title?<br /><br />0= off 1= on', '18', '69', 'zen_cfg_select_option(array(\'0\', \'1\'), ', now());

#
# Updating table get_terms_to_filter
ALTER TABLE get_terms_to_filter ADD get_term_table varchar(64) NOT NULL;
ALTER TABLE get_terms_to_filter ADD get_term_name_field varchar(64) NOT NULL;
UPDATE get_terms_to_filter SET get_term_table = 'TABLE_MANUFACTURERS', get_term_name_field = 'manufacturers_name' where get_term_name = 'manufacturers_id';
UPDATE 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 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 coupons ADD coupon_zone_restriction INT( 11 ) NOT NULL DEFAULT '0';
ALTER TABLE coupons CHANGE coupon_minimum_order coupon_minimum_order DECIMAL( 15, 4 ) NOT NULL DEFAULT '0.0000';
ALTER TABLE coupons CHANGE coupon_amount coupon_amount DECIMAL( 15, 4 ) NOT NULL DEFAULT '0.0000';
ALTER TABLE coupon_gv_customer CHANGE amount amount DECIMAL( 15, 4 ) NOT NULL DEFAULT '0.0000';
ALTER TABLE coupon_gv_queue CHANGE amount amount DECIMAL( 15, 4 ) NOT NULL DEFAULT '0.0000';

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


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

Can you please make the edit and re-post exactly what I should insert? I am trying to avoid mangling the live server. (I have to admit, this is scrappy work from Fantastico. It is surprising that they have attained such popularity.)

[DrByte]

You can skip any line that starts with a hashmark: #
But the system will ignore those lines anyway.

Yes, in theory that should work. You have the correct section of the file.
The downside is that you might get an error that any one of the statements failed, and might not know which one.
So ... the safer way is to run each line separately. Ya, it's tedious, but shouldn't take you more than 5-10 minutes to paste each line and submit. Then do the next one. Pretty much just ignore any of them that give you an error because that probably means whatever gave you an error was already done by something else (indeed probably was done before the 1.3.5 upgrade and thus the script that attempts to detect which statements need to be run thought the 1.3.6 part was already taken care of).
You're on the right track.

[isaiah36]

OK, I followed the slow and steady method, and logged the results, just in case another hapless soul has been fantas-ticked-off -

1 -2 error "already exists"
3 - 8 processed
9 - 17 error "already exists"
18 -29 processed

Now, all is smooth as butter, page is loading, and 'Restrictions' are fully editable. BUT, when I go to test the GV sent out earlier (before SQL patching) I still get "Invalid Gift Certificate Redemption Code" - I am going to send another GV to myself and test it, post SQL patching.

[isaiah36]

Nope, same error from a freshly generated GV.

[DrByte]

Are ALL the DB_XXXXXX settings identical in both your /includes/configure.php and /your-renamed-admin-folder/includes/configure.php files?