Upgrading to 1.5.0 to 1.5.6 problem

[OopsIbrickedIT]

Hi,

sorry I did that I accidentally pasted wrong error. I have run the upgrade script several times and it all ways suggests to upgrade from 1.5.5 to 1.5.6

I got several possible causes like this:

-
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_countries ADD status tinyint(1) DEFAULT 1;  1060: Duplicate column name 'status'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_configuration ADD val_function text default NULL AFTER set_function;  1060: Duplicate column name 'val_function'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_products MODIFY products_image varchar(255) default NULL;  1292: Incorrect datetime value: '0200-00-00 09:00:00' for column 'products_date_added' at row 796  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_calc_base TINYINT(1) NOT NULL DEFAULT 0;  1060: Duplicate column name 'coupon_calc_base'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_order_limit INT( 4 ) NOT NULL DEFAULT 0;  1060: Duplicate column name 'coupon_order_limit'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_is_valid_for_sales TINYINT(1) NOT NULL DEFAULT 1;  1060: Duplicate column name 'coupon_is_valid_for_sales'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_product_count TINYINT(1) NOT NULL DEFAULT 0;  1060: Duplicate column name 'coupon_product_count'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders ADD order_weight FLOAT default NULL;  1060: Duplicate column name 'order_weight'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_weight float default NULL;  1060: Duplicate column name 'products_weight'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_virtual tinyint(1) default NULL;  1060: Duplicate column name 'products_virtual'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD product_is_always_free_shipping tinyint(1) default NULL;  1060: Duplicate column name 'product_is_always_free_shipping'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_order_min float default NULL;  1060: Duplicate column name 'products_quantity_order_min'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_order_units float default NULL;  1060: Duplicate column name 'products_quantity_order_units'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_order_max float default NULL;  1060: Duplicate column name 'products_quantity_order_max'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_mixed tinyint(1) default NULL;  1060: Duplicate column name 'products_quantity_mixed'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_mixed_discount_quantity tinyint(1) default NULL;  1060: Duplicate column name 'products_mixed_discount_quantity'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products_download ADD products_attributes_id int(11) default NULL;  1060: Duplicate column name 'products_attributes_id'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_status_history ADD updated_by varchar(45) NOT NULL default '';  1060: Duplicate column name 'updated_by'  

---------------
Apr 01 2019 11:19 -- upgradeException
VALUES ('categoriesProductListing', 'BOX_CATALOG_CATEGORIES_PRODUCTS', 'FILENAME_CATEGORY_PRODUCT_LISTING', '', 'catalog', 'Y', 1);  1062: Duplicate entry 'categoriesProductListing' for key 'page_key'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_customers_basket DROP final_price;  1091: Can't DROP 'final_price'; check that column/key exists  

---------------
Apr 01 2019 11:19 -- upgradeException
LEFT JOIN zen_languages l ON 1;  1054: Unknown column 'e.pages_title' in 'field list'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_ezpages DROP languages_id, DROP pages_title, DROP pages_html_text;  1091: Can't DROP 'languages_id'; check that column/key exists  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_ezpages ADD status_visible int(1) NOT NULL default '0';  1060: Duplicate column name 'status_visible'  

---------------
Apr 01 2019 11:19 -- upgradeException
) ENGINE=InnoDB;  1286: Unknown storage engine 'InnoDB'

and this:

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_countries ADD status tinyint(1) DEFAULT 1;  1060: Duplicate column name 'status'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_configuration ADD val_function text default NULL AFTER set_function;  1060: Duplicate column name 'val_function'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_products MODIFY products_image varchar(255) default NULL;  1292: Incorrect datetime value: '0200-00-00 09:00:00' for column 'products_date_added' at row 796  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_calc_base TINYINT(1) NOT NULL DEFAULT 0;  1060: Duplicate column name 'coupon_calc_base'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_order_limit INT( 4 ) NOT NULL DEFAULT 0;  1060: Duplicate column name 'coupon_order_limit'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_is_valid_for_sales TINYINT(1) NOT NULL DEFAULT 1;  1060: Duplicate column name 'coupon_is_valid_for_sales'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_product_count TINYINT(1) NOT NULL DEFAULT 0;  1060: Duplicate column name 'coupon_product_count'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders ADD order_weight FLOAT default NULL;  1060: Duplicate column name 'order_weight'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_weight float default NULL;  1060: Duplicate column name 'products_weight'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_virtual tinyint(1) default NULL;  1060: Duplicate column name 'products_virtual'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD product_is_always_free_shipping tinyint(1) default NULL;  1060: Duplicate column name 'product_is_always_free_shipping'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_order_min float default NULL;  1060: Duplicate column name 'products_quantity_order_min'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_order_units float default NULL;  1060: Duplicate column name 'products_quantity_order_units'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_order_max float default NULL;  1060: Duplicate column name 'products_quantity_order_max'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_mixed tinyint(1) default NULL;  1060: Duplicate column name 'products_quantity_mixed'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products ADD products_mixed_discount_quantity tinyint(1) default NULL;  1060: Duplicate column name 'products_mixed_discount_quantity'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_products_download ADD products_attributes_id int(11) default NULL;  1060: Duplicate column name 'products_attributes_id'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_orders_status_history ADD updated_by varchar(45) NOT NULL default '';  1060: Duplicate column name 'updated_by'  

---------------
Apr 01 2019 11:19 -- upgradeException
VALUES ('categoriesProductListing', 'BOX_CATALOG_CATEGORIES_PRODUCTS', 'FILENAME_CATEGORY_PRODUCT_LISTING', '', 'catalog', 'Y', 1);  1062: Duplicate entry 'categoriesProductListing' for key 'page_key'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_customers_basket DROP final_price;  1091: Can't DROP 'final_price'; check that column/key exists  

---------------
Apr 01 2019 11:19 -- upgradeException
LEFT JOIN zen_languages l ON 1;  1054: Unknown column 'e.pages_title' in 'field list'  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_ezpages DROP languages_id, DROP pages_title, DROP pages_html_text;  1091: Can't DROP 'languages_id'; check that column/key exists  

---------------
Apr 01 2019 11:19 -- upgradeException
ALTER TABLE zen_ezpages ADD status_visible int(1) NOT NULL default '0';  1060: Duplicate column name 'status_visible'  

---------------
Apr 01 2019 11:19 -- upgradeException
) ENGINE=InnoDB;  1286: Unknown storage engine 'InnoDB'

[OopsIbrickedIT]

While the zc_install for upgrade may have appeared to successfully complete, it has not and it's a known issue with ZC 1.5.6/1.5.6a. If you obtain the zc_install folder from Github: https://github.com/zencart/zencart/tree/v156, then it should allow successful update.

The problem is that there are fields of the date/datetime type that have data such as '0000-00-00' or similar with a time in it. This is no longer acceptable data and is flagged when attempting to modify the table structure where such a date or default exists. The new zc_install process addresses these fields for Zen Cart tables.

Specifically what the debug log is indicating is that in attempting to access the a newly added table for ZC 1.5.6 to address/handle multi-language ez-pages that it could not be found. The zc_install upgrade process is expected to generate that table and the upgrade script includes the direction as well as a new install, but in performing the update, an error was encountered that prevented successful execution of the instruction to create the table.

Thank you mc12345678 I will try it because I all ready modified some but latest upgrade still shows invalid datetimes.

[OopsIbrickedIT]

I now tried the github version installer but I still got datetime error:

upgradeException
ALTER TABLE zen_products MODIFY products_image varchar(255) default NULL;  1292: Incorrect datetime value: '0200-00-00 09:00:00' for column 'products_date_added' at row 796

[mc12345678]

I now tried the github version installer but I still got datetime error:

upgradeException
ALTER TABLE zen_products MODIFY products_image varchar(255) default NULL;  1292: Incorrect datetime value: '0200-00-00 09:00:00' for column 'products_date_added' at row 796

Yeah, a datetime beginning with 0200-00-00 certainly is unexpected. Would suggest executing a sql query such as this if your business has no reason to have such a "historical" date.

UPDATE products SET products_date_added = '0001-01-01 00:00:00' WHERE products_date_added < '0300-00-00' AND products_date_added > '0001-01-01 00:00:00';

If executed in the admin->tools->install sql patches, there should be no change necessary, if executed in phpMyadmin or similar, be sure to include your DB_PREFIX of zen_ when referring to the table to be changed.

Then try again and repeat changes as necessary until no date/datetime errors occur.

[OopsIbrickedIT]

Thank you again mc12345678 PM me your Paypal email. I see you have contributed so much to ZC community I will donate some Pizza money. There are 33800 products in the db currently and everyday new incoming. Changing by hand is not an option. I wonder where this occasional datetime errors come from. Dropped sessions during product updates or what? Hopefully 1.5.6 is more stable.

[OopsIbrickedIT]

Now the date time seems to be solved but new problem arises:

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_countries ADD status tinyint(1) DEFAULT 1;  1060: Duplicate column name 'status'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_configuration ADD val_function text default NULL AFTER set_function;  1060: Duplicate column name 'val_function'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_calc_base TINYINT(1) NOT NULL DEFAULT 0;  1060: Duplicate column name 'coupon_calc_base'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_order_limit INT( 4 ) NOT NULL DEFAULT 0;  1060: Duplicate column name 'coupon_order_limit'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_is_valid_for_sales TINYINT(1) NOT NULL DEFAULT 1;  1060: Duplicate column name 'coupon_is_valid_for_sales'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_coupons ADD coupon_product_count TINYINT(1) NOT NULL DEFAULT 0;  1060: Duplicate column name 'coupon_product_count'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders ADD order_weight FLOAT default NULL;  1060: Duplicate column name 'order_weight'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_products ADD products_weight float default NULL;  1060: Duplicate column name 'products_weight'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_products ADD products_virtual tinyint(1) default NULL;  1060: Duplicate column name 'products_virtual'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_products ADD product_is_always_free_shipping tinyint(1) default NULL;  1060: Duplicate column name 'product_is_always_free_shipping'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_order_min float default NULL;  1060: Duplicate column name 'products_quantity_order_min'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_order_units float default NULL;  1060: Duplicate column name 'products_quantity_order_units'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_order_max float default NULL;  1060: Duplicate column name 'products_quantity_order_max'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_products ADD products_quantity_mixed tinyint(1) default NULL;  1060: Duplicate column name 'products_quantity_mixed'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_products ADD products_mixed_discount_quantity tinyint(1) default NULL;  1060: Duplicate column name 'products_mixed_discount_quantity'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_products_download ADD products_attributes_id int(11) default NULL;  1060: Duplicate column name 'products_attributes_id'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_orders_status_history ADD updated_by varchar(45) NOT NULL default '';  1060: Duplicate column name 'updated_by'  

---------------
Apr 01 2019 12:49 -- upgradeException
VALUES ('categoriesProductListing', 'BOX_CATALOG_CATEGORIES_PRODUCTS', 'FILENAME_CATEGORY_PRODUCT_LISTING', '', 'catalog', 'Y', 1);  1062: Duplicate entry 'categoriesProductListing' for key 'page_key'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_customers_basket DROP final_price;  1091: Can't DROP 'final_price'; check that column/key exists  

---------------
Apr 01 2019 12:49 -- upgradeException
LEFT JOIN zen_languages l ON 1;  1054: Unknown column 'e.pages_title' in 'field list'  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_ezpages DROP languages_id, DROP pages_title, DROP pages_html_text;  1091: Can't DROP 'languages_id'; check that column/key exists  

---------------
Apr 01 2019 12:49 -- upgradeException
ALTER TABLE zen_ezpages ADD status_visible int(1) NOT NULL default '0';  1060: Duplicate column name 'status_visible'  

---------------
Apr 01 2019 12:49 -- upgradeException
) ENGINE=InnoDB;  1286: Unknown storage engine 'InnoDB'

[OopsIbrickedIT]

I suspect is this causing now trouble?:

ALTER TABLE zen_ezpages DROP languages_id, DROP pages_title, DROP pages_html_text;  1091: Can't DROP 'languages_id'; check that column/key exists

[OopsIbrickedIT]

seems there is no languages_id anymore in ezpages. Front end gives following error:

WARNING: An Error occurred, please refresh the page and try again.
WARNING: An Error occurred, please refresh the page and try again

log shows nothing related to it I think just upgrade script complaints about duplicates.

When I run zc_install(from github) it does still try to upgrade from 1.5.5 to 1.5.6.

[mc12345678]

Now the date time seems to be solved but new problem arises:

---------------
Apr 01 2019 12:49 -- upgradeException
) ENGINE=InnoDB;  1286: Unknown storage engine 'InnoDB'

seems there is no languages_id anymore in ezpages. Front end gives following error:

WARNING: An Error occurred, please refresh the page and try again.
WARNING: An Error occurred, please refresh the page and try again

log shows nothing related to it I think just upgrade script complaints about duplicates.

When I run zc_install(from github) it does still try to upgrade from 1.5.5 to 1.5.6.

So two things based on what the logs are saying. One is that before rerunning the zc_install script, should again restore the old database. Executing all of the above items on the database after it has for the most part fully completed is why there are "duplicate" notices and in this case "missing" fields.

The failure though is that the database server being used does not recognize the table type of innodb. Based on some more reviewing this appears to have been chosen for the sessions table to ensure that proper handling is available for collation type utf8mb4. In absence of the database server supporting that table type, may I suggest modifying line 222 of zc_install/sql/updates/

to change from:

#NEXT_X_ROWS_AS_ONE_COMMAND:6
CREATE TABLE sessions (
  sesskey varchar(191) NOT NULL default '',
  expiry int(11) unsigned NOT NULL default 0,
  value mediumblob NOT NULL,
  PRIMARY KEY  (sesskey)
) ENGINE=InnoDB;

to:

#NEXT_X_ROWS_AS_ONE_COMMAND:6
CREATE TABLE sessions (
  sesskey varchar(191) NOT NULL default '',
  expiry int(11) unsigned NOT NULL default 0,
  value mediumblob NOT NULL,
  PRIMARY KEY  (sesskey)
) ENGINE=MyISAM;

or, to add the following beginning at line 223 of the same file:

#NEXT_X_ROWS_AS_ONE_COMMAND:6
CREATE TABLE IF NOT EXISTS sessions (
  sesskey varchar(191) NOT NULL default '',
  expiry int(11) unsigned NOT NULL default 0,
  value mediumblob NOT NULL,
  PRIMARY KEY  (sesskey)
) ENGINE=MyISAM;

In this way, if the first attempt to create the table fails because InnoDB doesn't work, then MyISAM would be used as a backup... Just a thought.

Incorporate whichever above before again going through the process of importing the old database and then executing zc_install.

[OopsIbrickedIT]

Thank you again very much mc. I will try that when I get my test server setted up. Now I use php7.0 environment and decided stick to version 1.5.5f for little time because I do not want to start a new project aka building a template for 1.5.6 from scratch. I do not know foundation and my CSS skills are from 1.0 era

I got it allmost done, the template worked with 1.5.0 but of course not now properly. That is not alarming, more like expected. What is the new dilemma is that categories and foremost the admin shows 403 forbidden now. So I can not login to change the template. I would like to use sheffield blue in testing environment and perhaps tweak that to suite the requirements. So is this an .htaccess problem or perhaps server related I do not know because logs are empty.

So seems I am getting closer but not there yet. Any help would be highly appreciated.