Database upgrade fails when upgrading my 1.5.3 to 1.5.6

[mc12345678]

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

And that was posted about on the forum associated with order delivery date a few months ago in post 478 of the Order Delivery Date module.

[swguy]

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

Patch for this problem:
https://github.com/zencart/zencart/pull/2382

[mc12345678]

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.

Actually the current default insert for the Order Delivery Date plugin is sufficient. It was that at an earlier date, the code "forced" a date value of '0000-00-00' into the database by use of date functions that would return that value instead of either NULL or '0001-01-01 00:00:00'. Further, currently the code actually will display no data in the associated column for a null value, but will display '0001-01-01' if used as a default. The additional display of such data (one for every record) becomes difficult to interpret on screen and yet there may be a reason for that value to appear in some records. Anyways, the correct fully formed sql statement to place the field in the condition expected by the plugin for the order_delivery_date field is:

  ALTER TABLE orders MODIFY COLUMN order_delivery_date datetime DEFAULT NULL;

and to ensure using the latest version of the software moving forwards.

[mc12345678]

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

Patch for this problem:
https://github.com/zencart/zencart/pull/2382

This "problem" is not show stopping although a log file is generated. If the particular countries_id was previously populated with an alternate country, yes it would remain incorrect when compared to a base install; however, the upgrade continues beyond encountering this issue. From the comments in the upgrade sql, this "problem" is forced to account for other installers that have neglected to maintain the core ZC database records as intended and to allow a store owner that is now starting to install/upgrade to the correct code set to continue successfully forwards.