Far dates causes PDOException 22003 [#2016787]

When saving an entity containing a date field with a far value (after 2035), an PDOException occurs :
PDOException: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column ...

It seems the the database column created by adding a date field on an entity is to small (int(11)) to contain the value of far dates.

Comment	File	Size	Author
#15	date-far_dates_causes_pdo_exception-2016787-15.patch	898 bytes	pio.fernandes
#15
#13	date-far_dates_causes_pdo_exception-2016787-13.patch	897 bytes	pio.fernandes
#13
#9	date-n2016787-9.patch	976 bytes	DamienMcKenna
#9	7.x-2.x: PHP 7.1 & MySQL 5.5, D7 47 pass
#9	date-n2016787-9.interdiff.txt	381 bytes	DamienMcKenna
#5	date-far_dates_causes_pdo_exception-2016787-5.patch	1019 bytes	r-mo
#5	7.x-2.x: PHP 5.3 & MySQL 5.5, D7 45 pass
#4	date-far_dates_causes_pdo_exception-2016787-4.patch	987 bytes	r-mo
#4	7.x-2.x: PHP 5.3 & MySQL 5.5, D7 45 pass
#2	date-far_dates_causes_pdo_exception-2016787-2.patch	987 bytes	r-mo
#2	7.x-2.x: PHP 5.3 & MySQL 5.5, D7 45 pass

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

heddn

English

Nicaragua

CreditAttribution: heddn commented 7 February 2014 at 15:36

The largest date that can be stored in a mysql int column is unix timestamp 2147483647 (Tue, 19 Jan 2038 03:14:07 GMT). Probably should catch that at form validation.

Comment #2

r-mo CreditAttribution: r-mo at CTI Digital commented 23 May 2018 at 12:21

Issue summary:	View changes
Status:	Active	» Needs review

File	Size
date-far_dates_causes_pdo_exception-2016787-2.patch	987 bytes
7.x-2.x: PHP 5.3 & MySQL 5.5, D7 45 pass

Any reason not to update schema to use big integer?

Comment #3

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 31 May 2018 at 17:14

Version:	7.x-2.6	» 7.x-2.x-dev
Status:	Needs review	» Needs work

This is a great improvement, thank you.

One suggestions is that the old field_schema may be cached when the update script runs, therefore it might not get the "size => big" setting. As a result I suggest adding $schema['size'] = 'big'; before the two db_change_field() calls.

Comment #4

r-mo CreditAttribution: r-mo at CTI Digital commented 5 June 2018 at 14:51

Status:

Needs work

» Needs review

File	Size
date-far_dates_causes_pdo_exception-2016787-4.patch	987 bytes
7.x-2.x: PHP 5.3 & MySQL 5.5, D7 45 pass

I'd thought of that might be needed but tested the update it seemed to pull in the correct schema without any cache clears. No problem to ensure it's set just in case.

Comment #5

r-mo CreditAttribution: r-mo at CTI Digital commented 5 June 2018 at 14:53

File	Size
date-far_dates_causes_pdo_exception-2016787-5.patch	1019 bytes
7.x-2.x: PHP 5.3 & MySQL 5.5, D7 45 pass

Comment #6

joep.hendrix CreditAttribution: joep.hendrix at CompuBase Internet Solutions commented 17 September 2018 at 16:16

Comment #7

joep.hendrix CreditAttribution: joep.hendrix at CompuBase Internet Solutions commented 7 October 2018 at 10:57

Status:

Needs review

» Reviewed & tested by the community

#5 works as advertised.

Thanks!

Comment #8

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 6 October 2018 at 00:55

Parent issue:

» #2867810: Plan for Date 7.x-2.11 release

I want to manually test this on a site with lots of data, to make sure it doesn't time out, but otherwise this looks pretty good.

Comment #9

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 19 October 2018 at 00:08

File	Size
date-n2016787-9.interdiff.txt	381 bytes
date-n2016787-9.patch	976 bytes
7.x-2.x: PHP 7.1 & MySQL 5.5, D7 47 pass

A quick reroll as update script 7006 was taken by another issue.

Comment #10

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 19 October 2018 at 01:05

Status:

Reviewed & tested by the community

» Fixed

Committed. Thank you all.

Comment #11

19 October 2018 at 01:06

DamienMcKenna committed 355551a on 7.x-2.x authored by r-mo

Issue #2016787 by r-mo, DamienMcKenna, joep.hendrix, heddn, julien_g:...

Comment #12

2 November 2018 at 01:09

Status:

Fixed

» Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

Comment #13

pio.fernandes CreditAttribution: pio.fernandes commented 17 January 2019 at 10:17

File	Size
date-far_dates_causes_pdo_exception-2016787-13.patch	897 bytes

Hi, this patch works, but not entirely for me: In my case there's a column of type varchar (called timezone) that should no be submitted to the db_change_field()

Adding new patch

Comment #14

pio.fernandes CreditAttribution: pio.fernandes commented 17 January 2019 at 13:10

1 file was hidden/shown/deleted

File	Size
date-far_dates_causes_pdo_exception-2016787-13.patch	897 bytes

Comment #15

pio.fernandes CreditAttribution: pio.fernandes commented 17 January 2019 at 13:13

File	Size
date-far_dates_causes_pdo_exception-2016787-15.patch	898 bytes

Comment #16

Steven Jones CreditAttribution: Steven Jones at ComputerMinds commented 4 August 2020 at 14:16

And this update is very likely to cause issues for people using non standard field storage backends, since those tables that the code is trying to change may simply not exist.