Database upgrade fails when upgrading my 1.5.3 to 1.5.6

[strelitzia]

Site is currently 1.5.3
When trying to upgrade to 1.5.6 I get the following error message after attempting database upgrade

"Could not update to version 1.5.6. We detect that you currently have v1.5.4, and must perform the updates to get to version 1.5.5 first."

This is despite the following appearing directly above that error

"Upgrade to version 1.5.5 completed."

Database has no prefix. Server running PHP 7.2.10 and MySQL 5.7.23

[strelitzia]

Log file contents after attempted update are as follows:

---------------
Dec 29 2018 20:59 -- upgradeException
INSERT INTO countries (countries_id, countries_name, countries_iso_code_2, countries_iso_code_3, address_format_id) VALUES (247,'Curaçao','CW','CUW','1'); 1062: Duplicate entry '247' for key 'PRIMARY'

---------------
Dec 29 2018 20:59 -- upgradeException
INSERT INTO countries (countries_id, countries_name, countries_iso_code_2, countries_iso_code_3, address_format_id) VALUES (248,'Sint Maarten (Dutch part)','SX','SXM','1'); 1062: Duplicate entry '248' for key 'PRIMARY'

---------------
Dec 29 2018 20:59 -- upgradeException
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Prev/Next Navigation Page Links (Mobile)', 'MAX_DISPLAY_PAGE_LINKS_MOBILE', '3', 'Number of numbered pagination links to display on Mobile devices (assuming your template supports mobile-specific settings)', '3', '3', now()); Cannot insert configuration_key "MAX_DISPLAY_PAGE_LINKS_MOBILE" because it already exists /Applications/MAMP/htdocs/annabelleswigs/zc_install/sql/updates/mysql_upgrade_zencart_155.sql

---------------
Dec 29 2018 20:59 -- upgradeException
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Currency Exchange Rate: Primary Source', 'CURRENCY_SERVER_PRIMARY', 'ecb', 'Where to request external currency updates from (Primary source)<br><br>Additional sources can be installed via plugins.', '1', '55', 'zen_cfg_pull_down_exchange_rate_sources(', now()); Cannot insert configuration_key "CURRENCY_SERVER_PRIMARY" because it already exists /Applications/MAMP/htdocs/annabelleswigs/zc_install/sql/updates/mysql_upgrade_zencart_155.sql

---------------
Dec 29 2018 20:59 -- upgradeException
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Currency Exchange Rate: Secondary Source', 'CURRENCY_SERVER_BACKUP', 'boc', 'Where to request external currency updates from (Secondary source)<br><br>Additional sources can be installed via plugins.', '1', '55', 'zen_cfg_pull_down_exchange_rate_sources(', now()); Cannot insert configuration_key "CURRENCY_SERVER_BACKUP" because it already exists /Applications/MAMP/htdocs/annabelleswigs/zc_install/sql/updates/mysql_upgrade_zencart_155.sql

---------------
Dec 29 2018 20:59 -- upgradeException
ALTER TABLE orders MODIFY shipping_method VARCHAR(255) NOT NULL DEFAULT ''; 1406: Data too long for column 'shipping_method' at row 14626


And in a separate log, the following:

[29-Dec-2018 20:59:12 Europe/Paris] MySQL error 1062 encountered during zc_install:
Duplicate entry '247' for key 'PRIMARY'
INSERT INTO countries (countries_id, countries_name, countries_iso_code_2, countries_iso_code_3, address_format_id) VALUES (247,'Curaçao','CW','CUW','1');
---------------


[29-Dec-2018 20:59:12 Europe/Paris] MySQL error 1062 encountered during zc_install:
Duplicate entry '248' for key 'PRIMARY'
INSERT INTO countries (countries_id, countries_name, countries_iso_code_2, countries_iso_code_3, address_format_id) VALUES (248,'Sint Maarten (Dutch part)','SX','SXM','1');
---------------


[29-Dec-2018 20:59:13 Europe/Paris] MySQL error 1406 encountered during zc_install:
Data too long for column 'shipping_method' at row 14626
ALTER TABLE orders MODIFY shipping_method VARCHAR(255) NOT NULL DEFAULT '';
---------------

[DrByte]

ALTER TABLE orders MODIFY shipping_method VARCHAR(255) NOT NULL DEFAULT '';
1406: Data too long for column 'shipping_method' at row 14626

Record 14626 in your orders table has a REALLY long value in it, specifically, it's longer than the 255-character limit that we're trying to extend the field to be.
This long data most likely got inserted into your database before your MySQL was upgraded to 5.7 which enforces very strict rules.
Either that, or you've previously altered that field to be much longer for some reason of your own, which Zen Cart has no knowledge of and can't anticipate.
255 is already longer than should be needed.

You should go to that record (ie: in phpMyAdmin) and shorten the data in it. Then re-run that ALTER TABLE statement again, by hand (ie: in phpMyAdmin). Then continue to fix any additional records it complains about until it runs without complaint.

[DrByte]

"Could not update to version 1.5.6. We detect that you currently have v1.5.4, and must perform the updates to get to version 1.5.5 first."

That's directly related to the shipping_method field not being set to varchar(255) because that step of the upgrade failed, for reasons explained above.

This is despite the following appearing directly above that error

"Upgrade to version 1.5.5 completed."

Ya, zc_install can only do so much in terms of detecting failures related to unexpected anomalies with existing data.

[strelitzia]

Looking at the orders table, shipping_method is currently set as 'text', so no character limit is imposed.
The content of the particular record highlighted is
"Shipping (Fed-Ex Next Day Delivery \n<br /><em>Week-day delivery only; orders placed after mid-day Thursday will be dispatched Friday for a Monday delivery; orders placed between mid-day Friday-Sunday will be dispatched Monday</em> (&pound;7.75))" which is as a result if the advanced shipper method title.
Don't ask me why the title is so long, I didn't write it! lol
Given that there may well be more the of the same in the orders table, where do i need to edit the installer to set the field update to be greater than the 255 char limit that default ZC wants to impose?

Thanks in advance

[strelitzia]

@Dr Byte

I resolved the char length issue, BUT it still fails to upgrade the database.
Started again with another clone of the 1.5.3 database having edited mysql_upgrade_zencart_155.sql to allow for a 350 char limit

I still see the same error on screen that it needs to upgrade to 1.5.5 before upgrading to 1.5.6

No error logs generated during db upgrade, just

"Dec 29 2018 21:52 -- Version detected in database_upgrade/header_php.php
1.5.3"

I'm stumped now as even without any actual errors reported I still can't update the database.

Suggestions gratefully received!

[mc12345678]

@Dr Byte

I resolved the char length issue, BUT it still fails to upgrade the database.
Started again with another clone of the 1.5.3 database having edited mysql_upgrade_zencart_155.sql to allow for a 350 char limit

I still see the same error on screen that it needs to upgrade to 1.5.5 before upgrading to 1.5.6

No error logs generated during db upgrade, just

"Dec 29 2018 21:52 -- Version detected in database_upgrade/header_php.php
1.5.3"

I'm stumped now as even without any actual errors reported I still can't update the database.

Suggestions gratefully received!

DrBytes said that the field length was part of the version detection. That detection is made from the content of zc_install/includes/systemChecks.yml look at line 303.

Would actually suggest changing the check from 'VARCHAR(255)' to 'text' to support the changes made by Advanced Shipper.

[strelitzia]

Thank you for the information mc12345678

Given that Advanced Shipper is a widely used module, would it not be prudent to add some form of check to see if Advanced Shipper tables exist, and if they do, set the field to text, else set to varchar(255)?

I can see this could well cause issues for a number of people during upgrade.

[mc12345678]

Thank you for the information mc12345678

Given that Advanced Shipper is a widely used module, would it not be prudent to add some form of check to see if Advanced Shipper tables exist, and if they do, set the field to text, else set to varchar(255)?

I can see this could well cause issues for a number of people during upgrade.

Something similar may be possible, but as DrByte said, such an issue had not been identified to the team prior to now.

For discussion and while a good suggestion, from my own experience there are several different factors that can/should be considered when trying to determine the installation status of something.

While I think that the core team would welcome suggestions on ways to handle the needed increase of field length, I would also think that it could well be said that if the cause was that program that they should seek assistance from the associated vendor. While I haven't heavily looked over the code style/control of the checker, I will look to see what can be proposed to the team to maybe support this. At least a check to see if the field is smaller than 255 to then increase it or identify the need to increase it.

[DrByte]

Given that Advanced Shipper is a widely used module, would it not be prudent to add some form of check to see if Advanced Shipper tables exist

With several thousand plugins available, there's no way we can anticipate all the unique requirements of each nor the ways in which they alter core requirements.

Frankly, that upgrade check in v155 has been there since March 2016. I'm surprised nobody's made a big deal about it being a problem since that time.