db upgrade does not fix all incorrect datetime values

[RixStix]

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.

[Robert Cooper]

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

[carlwhat]

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.

[DrByte]

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.

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.

[Dinnages]

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.

[DrByte]

@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.

[Dinnages]

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....issing-options

[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.

[Dinnages]

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.

[Dinnages]

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...