db upgrade does not fix all incorrect datetime values

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

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

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

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

Thanks for reporting this!

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;

DrByte:

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.

carlwhat:

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.

carlwhat:

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

carlwhat:

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',

DrByte:

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

carlwhat:

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

> **carlwhat:**
>
> it seems that not being in strict mode (or something like that) allowed me to add data in that format
True.

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

DrByte:

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)

Thanks DrByte,
Zero logfile errors.

drop tables
import db from backup
run cleanup from within phpMyAdmin
run zc_install as update
no logfiles generated

when I inserted the code at line 252 of mysql_upgrade_zencart_155.sql
and ran the zc_install as update, there were 2 error files generated.

That's when I just started over and ran the cleanup from within phpMyAdmin.
Didn't look to see what/if I fouled up in my paste/save to the mysql_upgrade_zencart_155.sql
Probably overlooked an overzealous mouse grab in the copy/paste.

Okay just tried this on my issues.....right now iam able to put the store down for maintenance and all my items are there just gonna keep testing looking around and see so it is prob. worth putting somewhere in the 1.5.6a sql db update file

Robert

Thank you very much :D

ok, i love sql.... to a point...

my experience and tests. first off, when i get to here:

/index.php?main_page=database_upgrade

and it asks to confirm the steps to upgrade, should there a button to confirm? or does one just press enter? i have not looked to much further into what is on that page, as i am getting caught up into the db upgrade. but if there is supposed to be a button, then something is wrong on my browser, if there is no confirmation button, i think we should add one.

i have tried modifying this file:

zc_install/sql/updates/mysql_upgrade_zencart_156.sql

to include all of the db fixes above; i was not able to get it to work. however, if i manually ran everything prior to doing the upgrade it worked fine.

that said, do we need to look at standardizing all our datetimes? or is it bound to create more confusion? for example, in the code above:

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;

this is caused by the structure:

products_date_added datetime [0001-01-01 00:00:00]
products_last_modified datetime NULL
products_date_available datetime NULL

not sure if the effort is worth it or not. or if there is a reason why the schema is different.

finally i LOVE the link that @design75 added. thank you. i have not played around with it yet. but i will! as i said above, i love sql... to a point...

best.

carlwhat:

and it asks to confirm the steps to upgrade, should there a button to confirm?Yes, there's a button to click to proceed with applying the updates required.

carlwhat:

that said, do we need to look at standardizing all our datetimes? or is it bound to create more confusion?
That's a completely separate topic. It requires changing how the PHP code interacts with the data.
Maybe for v2.

DrByte:

Wanna try running this cleanup?
(ie: run it manually before running zc_install,

All the above thread is way above my head, my weakest skill point, fiddling with MySql
So do I put the copy paste from that post at myphpadmin into the database there?
Does it therefore sort out the date issues before possibly failing on a install.php run.

This will be my fourth run, three times install.php with 156, now with 156a since 27/12/18, which filezilla is doing right now.

@Dinnages,
In one of your other posts I saw that you are using a non-blank db table-prefix (yours is "zen_"), so that means you can't just copy/paste the above SQL into phpMyAdmin. You'd need to modify all the table names.

However, if you have access to your old live store's admin, you could paste it into Admin->Tools->SqlPatches and run it there. Then take a new backup of your db and use that for testing your upgrade.

Or, you could fiddle with the text above and change every "UPDATE foo SET" to "UPDATE zen_foo SET" at the beginning of each line, where "foo" is the tablename that's already there in the text above. You're just adding "zen_" to each one.

Thank you DrByte for that, wasnt going ahead until I could see which way to go next.

The zen_ prefix has been there since the first ever cart I installed, I would not have been aware of adding that personally, & I would always go by dafult options.

I dont think its wise for me to play with 1.39 as the PHP seemed to get the shop confused, the data kept getting lost when I edited products and things before I moved the PHP up to 7.1 for Wordpress, & now for 1.56 and anyway, I would like not to mess the #2 database, I have only been using copies of that as in #3 #3a etc. So I have had a backup all along.

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;

You mean

UPDATE zen_admin SET

My own post on the upgrade 139 - 156/156a progress for others is here
https://www.zen-cart.com/showthread.php?224871-upgraded-1-39-to-1-56-admin-missing-options

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;

would become:

UPDATE zen_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;

Just the one change.

Then wash, rinse, repeat, for each line.

Then run in phpMyAdmin.

DrByte:

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;

would become:

UPDATE zen_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;

Just the one change.

Then wash, rinse, repeat, for each line.

Then run in phpMyAdmin.

Ha I LOL on your methodology for explaining in very simple terms, what many might see, but others like me do not, and it is best to ask to be sure what we do with things like this then go semi-blindly ahead and mess up a perfectly good database and have to start all over again.
So looks like its only at the start of each line.

Will try that edit soon, by copying the code to a notepad and go from there, etc. Could be there soon :-)
Never made much out of sales but its a long term thing.

Well have my amended file, ready to copy and paste into the second tab which states Run SQL query...
However right click does not allow a paste option, only forward/back page arrows, "Save Page as" etc.

Or am I in the wrong place?

UPDATE zen_admin SET pwd_last_change_date...