db upgrade does not fix all incorrect datetime values

[RixStix]

156a vanilla install w/demo data
verify basic functionality
drop db tables
import db tables from live 154 site
run zc_install
logfile generated

The db upgrade did modify 1 or 2 dates which were 0000-00-00
There is one that did not get modified; thus causing the error to be logged

Actually, it modified date_added but not last_modified

[05-Jan-2019 11:41:28 America/Los_Angeles] MySQL error 1292 encountered during zc_install:
Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_modified' at row 718
ALTER TABLE configuration ADD val_function text default NULL AFTER set_function;
---------------
[05-Jan-2019 11:41:31 America/Los_Angeles] MySQL error 1292 encountered during zc_install:
Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_modified' at row 718
ALTER TABLE configuration MODIFY configuration_key varchar(180) NOT NULL default '';

[swguy]

Is row 718 a value you added yourself? The value of last_modified should be null or a valid date.

[RixStix]

Not sure it matters. There was a modification to the install to fix the 0000-00-00 in the date_added field. Maybe the same fix could be made to the last_modified fields.

It is
DEFINE_ABOUT_US_STATUS

so I'm gonna guess that it is possibly related to the About_Us plugin

[swguy]

Hmm... the current version of the plugin sets the last modified time to NOW(). Could have been an old version.

Thanks for reporting this!

[DrByte]

Wanna try running this cleanup?
(ie: run it manually before running zc_install, or insert it in the top of the zc_install mysql_upgrade_zencart_YYY.sql file where YYY is the "next" zc version after the one your database data is from)

UPDATE admin SET pwd_last_change_date = '0001-01-01 00:00:00' WHERE pwd_last_change_date < '0001-01-01' and pwd_last_change_date is not null;
UPDATE admin SET last_modified = '0001-01-01 00:00:00' WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE admin SET last_login_date = '0001-01-01 00:00:00' WHERE last_login_date < '0001-01-01' and last_login_date is not null;
UPDATE admin SET last_failed_attempt = '0001-01-01 00:00:00' WHERE last_failed_attempt < '0001-01-01' and last_failed_attempt is not null;
UPDATE admin_activity_log SET access_date = '0001-01-01 00:00:00' WHERE access_date < '0001-01-01' and access_date is not null;
UPDATE banners SET expires_date = NULL WHERE expires_date < '0001-01-01' and expires_date is not null;
UPDATE banners SET date_scheduled = NULL WHERE date_scheduled < '0001-01-01' and date_scheduled is not null;
UPDATE banners SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE banners SET date_status_change = NULL WHERE date_status_change < '0001-01-01' and date_status_change is not null;
UPDATE banners_history SET banners_history_date = '0001-01-01 00:00:00' WHERE banners_history_date < '0001-01-01' and banners_history_date is not null;
UPDATE categories SET date_added = NULL WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE categories SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE configuration SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE configuration SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE coupon_email_track SET date_sent = '0001-01-01 00:00:00' WHERE date_sent < '0001-01-01' and date_sent is not null;
UPDATE coupon_gv_queue SET date_created = '0001-01-01 00:00:00' WHERE date_created < '0001-01-01' and date_created is not null;
UPDATE coupon_redeem_track SET redeem_date = '0001-01-01 00:00:00' WHERE redeem_date < '0001-01-01' and redeem_date is not null;
UPDATE coupons SET coupon_start_date = '0001-01-01 00:00:00' WHERE coupon_start_date < '0001-01-01' and coupon_start_date is not null;
UPDATE coupons SET coupon_expire_date = '0001-01-01 00:00:00' WHERE coupon_expire_date < '0001-01-01' and coupon_expire_date is not null;
UPDATE coupons SET date_created = '0001-01-01 00:00:00' WHERE date_created < '0001-01-01' and date_created is not null;
UPDATE coupons SET date_modified = '0001-01-01 00:00:00' WHERE date_modified < '0001-01-01' and date_modified is not null;
UPDATE currencies SET last_updated = NULL WHERE last_updated < '0001-01-01' and last_updated is not null;
UPDATE customers SET customers_dob = '0001-01-01 00:00:00' WHERE customers_dob < '0001-01-01' and customers_dob is not null;
UPDATE customers_info SET customers_info_date_of_last_logon = NULL WHERE customers_info_date_of_last_logon < '0001-01-01' and customers_info_date_of_last_logon is not null;
UPDATE customers_info SET customers_info_date_account_created = NULL WHERE customers_info_date_account_created < '0001-01-01' and customers_info_date_account_created is not null;
UPDATE customers_info SET customers_info_date_account_last_modified = NULL WHERE customers_info_date_account_last_modified < '0001-01-01' and customers_info_date_account_last_modified is not null;
UPDATE email_archive SET date_sent = '0001-01-01 00:00:00' WHERE date_sent < '0001-01-01' and date_sent is not null;
UPDATE featured SET featured_date_added = NULL WHERE featured_date_added < '0001-01-01' and featured_date_added is not null;
UPDATE featured SET featured_last_modified = NULL WHERE featured_last_modified < '0001-01-01' and featured_last_modified is not null;
UPDATE featured SET expires_date = '0001-01-01' WHERE expires_date < '0001-01-01' and expires_date is not null;
UPDATE featured SET date_status_change = NULL WHERE date_status_change < '0001-01-01' and date_status_change is not null;
UPDATE featured SET featured_date_available = '0001-01-01' WHERE featured_date_available < '0001-01-01' and featured_date_available is not null;
UPDATE geo_zones SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE geo_zones SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE group_pricing SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE group_pricing SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE manufacturers SET date_added = NULL WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE manufacturers SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE manufacturers_info SET date_last_click = NULL WHERE date_last_click < '0001-01-01' and date_last_click is not null;
UPDATE newsletters SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE newsletters SET date_sent = NULL WHERE date_sent < '0001-01-01' and date_sent is not null;
UPDATE orders SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE orders SET date_purchased = NULL WHERE date_purchased < '0001-01-01' and date_purchased is not null;
UPDATE orders SET orders_date_finished = NULL WHERE orders_date_finished < '0001-01-01' and orders_date_finished is not null;
UPDATE orders_status_history SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE paypal SET payment_date = '0001-01-01 00:00:00' WHERE payment_date < '0001-01-01' and payment_date is not null;
UPDATE paypal SET last_modified = '0001-01-01 00:00:00' WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE paypal SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE paypal_payment_status_history SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE paypal_testing SET payment_date = '0001-01-01 00:00:00' WHERE payment_date < '0001-01-01' and payment_date is not null;
UPDATE paypal_testing SET last_modified = '0001-01-01 00:00:00' WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE paypal_testing SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE product_type_layout SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE product_type_layout SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE product_types SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE product_types SET last_modified = '0001-01-01 00:00:00' WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE products SET products_date_added = '0001-01-01 00:00:00' WHERE products_date_added < '0001-01-01' and products_date_added is not null;
UPDATE products SET products_last_modified = NULL WHERE products_last_modified < '0001-01-01' and products_last_modified is not null;
UPDATE products SET products_date_available = NULL WHERE products_date_available < '0001-01-01' and products_date_available is not null;
UPDATE products_notifications SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE project_version SET project_version_date_applied = '0001-01-01 01:01:01' WHERE project_version_date_applied < '0001-01-01' and project_version_date_applied is not null;
UPDATE project_version_history SET project_version_date_applied = '0001-01-01 01:01:01' WHERE project_version_date_applied < '0001-01-01' and project_version_date_applied is not null;
UPDATE reviews SET date_added = NULL WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE reviews SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE salemaker_sales SET sale_date_start = '0001-01-01' WHERE sale_date_start < '0001-01-01' and sale_date_start is not null;
UPDATE salemaker_sales SET sale_date_end = '0001-01-01' WHERE sale_date_end < '0001-01-01' and sale_date_end is not null;
UPDATE salemaker_sales SET sale_date_added = '0001-01-01' WHERE sale_date_added < '0001-01-01' and sale_date_added is not null;
UPDATE salemaker_sales SET sale_date_last_modified = '0001-01-01' WHERE sale_date_last_modified < '0001-01-01' and sale_date_last_modified is not null;
UPDATE salemaker_sales SET sale_date_status_change = '0001-01-01' WHERE sale_date_status_change < '0001-01-01' and sale_date_status_change is not null;
UPDATE specials SET specials_date_added = NULL WHERE specials_date_added < '0001-01-01' and specials_date_added is not null;
UPDATE specials SET specials_last_modified = NULL WHERE specials_last_modified < '0001-01-01' and specials_last_modified is not null;
UPDATE specials SET expires_date = '0001-01-01' WHERE expires_date < '0001-01-01' and expires_date is not null;
UPDATE specials SET date_status_change = NULL WHERE date_status_change < '0001-01-01' and date_status_change is not null;
UPDATE specials SET specials_date_available = '0001-01-01' WHERE specials_date_available < '0001-01-01' and specials_date_available is not null;
UPDATE tax_class SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE tax_class SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE tax_rates SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE tax_rates SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE upgrade_exceptions SET errordate = NULL WHERE errordate < '0001-01-01' and errordate is not null;
UPDATE zones_to_geo_zones SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE zones_to_geo_zones SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE media_clips SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE media_clips SET last_modified = '0001-01-01 00:00:00' WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE media_manager SET last_modified = '0001-01-01 00:00:00' WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE media_manager SET date_added = '0001-01-01 00:00:00' WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE music_genre SET date_added = NULL WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE music_genre SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE record_artists SET date_added = NULL WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE record_artists SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE record_artists_info SET date_last_click = NULL WHERE date_last_click < '0001-01-01' and date_last_click is not null;
UPDATE record_company SET date_added = NULL WHERE date_added < '0001-01-01' and date_added is not null;
UPDATE record_company SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null;
UPDATE record_company_info SET date_last_click = NULL WHERE date_last_click < '0001-01-01' and date_last_click is not null;

[carlwhat]

Wanna try running this cleanup?
(ie: run it manually before running zc_install, or insert it into the zc_install mysql_upgrade_zencart_YYY.sql file where YYY is the "next" zc version after the one your database data is from)

i think putting this into zc_install mysql_upgrade script would be an awesome idea.

in my testing, i had to only update 3 dates similar to the sql statements above. 2 of which were added fields (and would not be caught by the above sql statements), but one would have gotten caught by the update above and prevented the failure of the DB upgrade.

in addition, my test data for v155 also needed the following statement:

ALTER TABLE `products`
  CHANGE `products_date_added` `products_date_added` datetime NULL AFTER `products_virtual`;

i am not sure if this was missed in a previous update or if my testdata some how is out of of sync with what should be defined for a v155 dataset.

hope that makes sense.

[DrByte]

i think putting this into zc_install mysql_upgrade script would be an awesome idea.

Yes, that's the plan. I put it here first to get some testing feedback on databases beyond what I'm using myself.

2 of which were added fields (and would not be caught by the above sql statements)

Yup.

in addition, my test data for v155 also needed the following statement:

ALTER TABLE `products`
  CHANGE `products_date_added` `products_date_added` datetime NULL AFTER `products_virtual`;

i am not sure if this was missed in a previous update or if my testdata some how is out of of sync with what should be defined for a v155 dataset.

A fresh install doesn't use null for that field. Not sure why you're changing it to null (nor why you're not using the keyword 'default' as well).

Since v1.3.8 the schema for products_date_added has been:
products_date_added datetime NOT NULL default '0001-01-01 00:00:00',

[carlwhat]

A fresh install doesn't use null for that field. Not sure why you're changing it to null (nor why you're not using the keyword 'default' as well).

Since v1.3.8 the schema for products_date_added has been:
products_date_added datetime NOT NULL default '0001-01-01 00:00:00',

ok... perhaps that makes sense.

the live data for my sites is defined that way. however, if i were to run:

SELECT `products_id`, `products_date_added`
FROM `products`
WHERE `products_date_added` = 'NULL'

i would get the following:



now the sql "fix" would address this and i would not need to alter the schema.

it seems that not being in strict mode (or something like that) allowed me to add data in that format. and i was attempting to address the errors reported in the logs.

the next test on db conversion, i will run the sql statements first w/o the alter statement and see what happens.

best.my

[DrByte]

SELECT `products_id`, `products_date_added`
FROM `products`
WHERE `products_date_added` = 'NULL'

Strictly speaking your 'NULL' should be just NULL or null, without quotes.

it seems that not being in strict mode (or something like that) allowed me to add data in that format

True.

[Design75]

Maybe this discussion helps in creating an automated script for updating all tables in your database. https://stackoverflow.com/questions/...oss-all-tables
I have not tested it yet, but stumbled across to find a solution.