View Issue Details

IDProjectCategoryView StatusLast Update
0001389RackTablesdefaultpublic2017-05-19 12:08
ReporterhackmasterAssigned Toinfrastation 
PrioritylowSeverityminorReproducibilityalways
Status closedResolutionfixed 
Platformx86OSDebian LinuxOS Version
Product Version0.20.8 
Target Version0.20.12Fixed in Version0.20.12 
Summary0001389: Date field has upper date boundry
DescriptionWhen entering a warranty expiration that is dramatically in the future (example: 2109-08-26) the year gets rewritten without any user alert.
Dates this far in the future are common to HP networking components, which have 'lifetime' warranties.
Steps To ReproduceLocate an object with a date field.
Enter a date far into the future, example: "2109-08-26"
Save
The date will now read "2106-02-07"
Additional InformationI have not tested a lower bound for the date field.
TagsNo tags attached.

Activities

simon2

simon2

2015-05-18 23:02

reporter   ~0002829

Last edited: 2015-05-19 06:22

View 2 revisions

The date 2106-02-07 is an unsigned 32bit integer's maximum value, used as a Unix timestamp - 4294967295 seconds since January 1st 1970.

In the RackTables database, the AttributeValue table, the column "uint_value" is type "int(10) unsigned".

If that's right, the database column would need to be a "bigint(20)" or "bigint(20) unsigned" type to store later dates (or dates would need to be stored in a different form entirely).

(Because of this, there is a lower bound for the date field as well.
1970-01-02 is stored, 1970-01-01 and earlier says 'updated successfully', but the field comes back empty).

(This is reproducible on 64-bit CentOS 6 running 64-bit PHP 5.3, and on the RackTables demo interface running RackTables 0.20.10)

infrastation

infrastation

2016-05-19 17:15

administrator   ~0003205

Definitely the conversion error should result in a warning/error message. But would it be reasonable to expect RackTables to handle the dates beyond the range explained in the previous comment?
infrastation

infrastation

2016-06-10 13:14

administrator   ~0003229

As it had been discussed in detail in an earlier comment, the problem is indeed specific to a date outside of the 1970-01-01T00:00:00Z~2106-02-07T06:28:15Z range (in other words, with the UNIX time not in the 0x00000000~0xFFFFFFFF range).

When requested to store such a UNIX time into a 32-bit unsigned integer column, your instance of MySQL seems to truncate the value silently but mine returns an error, which results in a PDO exception:

SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'uint_value' at row 1 (22003)

Commit b204a89 makes the existing date range limit work as expected (that is, to display a meaningful error message and leave the old value intact) before and if the database layer is extended to allow the dates beyond the existing limit shown above. That said, the latter is very unlikely to happen in a near future unless anybody is willing to spend their time on this feature.

To allow for any additional feedback this issue should remain open for a few extra months.
infrastation

infrastation

2016-08-02 00:09

administrator   ~0003419

Closing the issue for now as explained above.
infrastation

infrastation

2017-05-19 12:08

administrator   ~0003621

For history: the server raises an error when the value size exceeds the column type when strict SQL mode is enabled (which tends to be the default in recent MySQL/MariaDB releases).

Issue History

Date Modified Username Field Change
2015-01-08 23:17 hackmaster New Issue
2015-05-18 23:02 simon2 Note Added: 0002829
2015-05-19 06:22 simon2 Note Edited: 0002829 View Revisions
2016-05-19 17:15 infrastation Note Added: 0003205
2016-06-10 13:14 infrastation Note Added: 0003229
2016-06-10 13:14 infrastation Priority normal => low
2016-06-10 13:14 infrastation Status new => acknowledged
2016-06-10 13:14 infrastation Resolution open => suspended
2016-06-10 13:14 infrastation Fixed in Version => 0.20.12
2016-06-10 13:14 infrastation Target Version => 0.20.12
2016-08-02 00:09 infrastation Note Added: 0003419
2016-08-02 00:09 infrastation Assigned To => infrastation
2016-08-02 00:09 infrastation Status acknowledged => closed
2016-08-02 00:09 infrastation Resolution suspended => fixed
2017-05-19 12:08 infrastation Note Added: 0003621