Database upgrade fails when upgrading my 1.5.3 to 1.5.6

[DrByte]

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

That's because the upgrade detector is looking for 255, not 350.

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.

You could do that, or just remove lines 299-303.

[mc12345678]

You could do that, or just remove lines 299-303.

Aww, I was thinking of adding a "greater than" like function, actually last thought which I liked was a value in this array type function. Though perhaps both would be of benefit.

The lack of "complaint" about that query? Well, I can say I've seen this problem before and there have been others that have reported a similar issue, though none seemed to publicly relate the lack of upgrade with the shipping_method field... if they did, I don't recall it.

[strelitzia]

Just to let you know, I have had another occurence of this issue, again due to the length of a shipping title. Whilst it may not affect everybody using Advanced Shipper as it really depends on what names they have given the methods when setting it up, clearly it is an issue. Whether the zc devs choose to address it directly by modifying the installer or not is up to them, but if no such modification is to be made, then I would suggest some note in the documentation advising people of the possibility of encountering this error.

[strelitzia]

There is also a problem with the attempt to add additional countries, resulting in a Duplicate entry '247' for key 'PRIMARY' error being logged. The database in question has some additional countries added for Scottish Highlands etc. Given that adding additional countries is not an uncommon occurence, I have to question why the ZC devs have used a strict method of setting the countries_id given that the field is Auto Increment. Surely it would be better to have not assigned the countries_id, instead letting MySQL increment the value as necessary.

[royaldave]

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'

I've had the same issue going from 1.5.4 to 1.5.6a - and the same data 'Curacao' snagged me.

I recently aborted an upgrade from 1.5.4 to 1.5.5 which did NOT have this issue. (Aborted for other reasons obviously).

[royaldave]

Hi

I've attempted upgrading from 1.5.4 to 1.5.6b and am encountering this error again - any recommendations?



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');
---------------



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');
---------------

[lat9]

When you inspect the store's database using phpMyAdmin from your cPanel, what is currently present for countries_id values 247 and 248?

[royaldave]

If you're talking live data (version 1.5.4 prior upgrade) - there is no 247 or 248 - it only goes up to 245.

Otherwise if you mean what's in there after my failed upgrade -

247 | Curaçao | CW | CUW | 1 | 1 |
248 | Sint Maarten (Dutch part) | SX | SXM | 1 | 1

[royaldave]

Actually forget the countries thing - I think it is getting caused because I keep trying to force the upgrade.

My real problem (I think) is this -

---------------
May 31 2019 16:23 -- upgradeException
ALTER TABLE orders MODIFY shipping_method VARCHAR(255) NOT NULL DEFAULT ''; 1292: Incorrect datetime value: '0000-00-00 00:00:00' for column 'order_delivery_date' at row 5150

[lat9]

Actually forget the countries thing - I think it is getting caused because I keep trying to force the upgrade.

My real problem (I think) is this -

---------------
May 31 2019 16:23 -- upgradeException
ALTER TABLE orders MODIFY shipping_method VARCHAR(255) NOT NULL DEFAULT ''; 1292: Incorrect datetime value: '0000-00-00 00:00:00' for column 'order_delivery_date' at row 5150

Yes, that '0000-00-00' value for a datetime field is going to cause issues. You'll need to take a two-pronged approach, since simply updating the database will correct the database, but I'm guessing that the Order Delivery Date plugin might be looking specifically for the zero-date value.

You can correct the database using the following SQL (either the site's admin's Tools->Install SQL Patches or via phpMyAdmin), recognizing that you'll need to include any DB_PREFIX value if you're using phpMyAdmin:

ALTER TABLE orders MODIFY COLUMN order_delivery_date datetime NOT NULL default '0001-01-01 00:00:00';

Be sure to make a database backup first! Then, you can use the DTK to find all occurrences of 0000-00-00 in all files. That will let you know where, if at all, any code is relying on those now-invalid dates.