If this is your first visit, check our documentation. To post, register or login.
Forums / Upgrading to 1.5.x / db upgrade does not fix all incorrect datetime values

db upgrade does not fix all incorrect datetime values

Started by rixstix on 5 Jan 2019, 8:01 PM · Views: 5,462

Results 21 to 27 of 27
10 Jan 2019, 8:47 PM
#21
design75 avatar

design75

Totally Zenned

Join Date:
Dec 2009
Location:
Amersfoort, The Netherlands
Posts:
2,862
Plugin Contributions:
5

db upgrade does not fix all incorrect datetime values

To paste you can also use "ctrl" + "v", on a windows machine.

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

11 Jan 2019, 8:49 AM
#22
dinnages avatar

dinnages

Zen Follower

Join Date:
May 2007
Location:
Brighton/Hove/Sussex
Posts:
146
Plugin Contributions:
0

Re: db upgrade does not fix all incorrect datetime values

Design75:

To paste you can also use "ctrl" + "v", on a windows machine.

Silly me, of course, rarely use that method, only when prompted by a pop up box usually.

Here is the response, of those not with a zero on matched rows.

SQL query:
UPDATE zen_configuration SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null
Matched rows: 1

SQL query:
UPDATE zen_specials SET expires_date = '0001-01-01' WHERE expires_date < '0001-01-01' and expires_date is not null
Matched rows: 4

# MySQL returned an empty result set (i.e. zero rows).

Should mean I can now use the install.php in my root folder of 1.56a I guess

Original store was 1.39:- TransportPostcards.co.uk/shop (redirecting out)
Working on a fresh new store... 1.56a TransportPostcards.co.uk/store
Appreciate Zen-Cart? - Always keep a banner link on your shop

11 Jan 2019, 9:06 AM
#23
design75 avatar

design75

Totally Zenned

Join Date:
Dec 2009
Location:
Amersfoort, The Netherlands
Posts:
2,862
Plugin Contributions:
5

Re: db upgrade does not fix all incorrect datetime values

Yes it looks like you're set to go, but there is no install.php in a vanilla install. You should point your browser to your_domain.com/zc_install

Dinnages:

Silly me, of course, rarely use that method, only when prompted by a pop up box usually.

Here is the response, of those not with a zero on matched rows.

SQL query:
UPDATE zen_configuration SET last_modified = NULL WHERE last_modified < '0001-01-01' and last_modified is not null
Matched rows: 1

SQL query:
UPDATE zen_specials SET expires_date = '0001-01-01' WHERE expires_date < '0001-01-01' and expires_date is not null
Matched rows: 4

MySQL returned an empty result set (i.e. zero rows).

> 
> Should mean I can now use the install.php in my root folder of 1.56a I guess
11 Jan 2019, 9:49 AM
#24
dinnages avatar

dinnages

Zen Follower

Join Date:
May 2007
Location:
Brighton/Hove/Sussex
Posts:
146
Plugin Contributions:
0

Re: db upgrade does not fix all incorrect datetime values

Design75:

Yes it looks like you're set to go, but there is no install.php in a vanilla install. You should point your browser to your_domain.com/zc_install

Sorry that was it: /zc_install
However i am back to the other thread now dates ought to be good.

My own post on the upgrade 139 - 156/156a progress for others is here
https://www.zen-cart.com/showthread....issing-options

Original store was 1.39:- TransportPostcards.co.uk/shop (redirecting out)
Working on a fresh new store... 1.56a TransportPostcards.co.uk/store
Appreciate Zen-Cart? - Always keep a banner link on your shop

25 Apr 2020, 4:46 PM
#25
torvista avatar

torvista

Totally Zenned

Join Date:
Aug 2007
Location:
Gijón, Asturias, Spain
Posts:
2,880
Plugin Contributions:
7

Re: db upgrade does not fix all incorrect datetime values

Note that in the post containing all the queries, eg.

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;

The suggested new value is 0001-01-01 00:00:00.

This is now out of range too, due to changes by Mysql:
https://github.com/zencart/zencart/issues/2397

If you do have values in your database that are out of range, they may stop certain upgrade queries from being carried out.
Certainly 0000-00-00 00:00:00 IS a problem, and 0001-01-01 00:00:00 should be a problem, but have no evidence to show that.

So, search and fix the values and the default for that column.

I had a problem with an extra field I had added that used 0000-00-00 00:00:00 as the default.

I think using all the queries above as

UPDATE admin SET pwd_last_change_date = '1000-01-01 00:00:00' WHERE pwd_last_change_date < '0001-01-01' and
pwd_last_change_date is not null;

is ok.

Steve
github.com/torvista: BackupMySQL, Structured Data, Multiple Copy-Move-Delete, Google reCaptcha, Image Checker, Spanish Language Pack and more...

25 Apr 2020, 4:50 PM
#26
swguy avatar

swguy

Administrator

Join Date:
Feb 2006
Location:
Tampa Bay, Florida
Posts:
10,758
Plugin Contributions:
56

Re: db upgrade does not fix all incorrect datetime values

Should we just use the beginning of the epoch for a base value?

That Software Guy. My Store: Zen Cart Support
Available for hire - See my ad in Services
Plugin Moderator, Documentation Curator, Chief Cook and Bottle-Washer.
Do you benefit from Zen Cart? Then please support the project.

26 Apr 2020, 10:06 AM
#27
torvista avatar

torvista

Totally Zenned

Join Date:
Aug 2007
Location:
Gijón, Asturias, Spain
Posts:
2,880
Plugin Contributions:
7

Re: db upgrade does not fix all incorrect datetime values

I couldn't say.
I think the criteria is firstly to change the 00... to something MySql accepts, that will not break anything that is using it.
I forgot to mention that the default should be null, but code may have to be updated to deal with it.
I like the idea of using the suggested 1000...as it is the bottom of the range.

Steve
github.com/torvista: BackupMySQL, Structured Data, Multiple Copy-Move-Delete, Google reCaptcha, Image Checker, Spanish Language Pack and more...