db upgrade does not fix all incorrect datetime values

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

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