RPM.pm INSTALLDATE format not always accepted by MySQL
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
OCS Inventory: Unified Unix Agent |
Fix Released
|
Medium
|
mortheres |
Bug Description
Symptom:
Dates reported in the "All softwares" view are displayed as "0000-00-00 00:00:00", even though there is a human-readable date string in the XML produced by the agent.
Analysis:
The agent produces
The rpm command format for INSTALLTIME is %{INSTALLTIME:
Fri 19 Jul 2013 11:27:26 PM GMT
Date strings in this format can be seen in the XML generated by the agent.
The "INSTALLDATE" field in the SOFTWARES table is defined as type "datetime". From the MySQL Reference Manual (http://
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. ... Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').
Tracing through the OCS server code shows that the date string as produced by the agent is maintained up to the time the string is written to the softwares table. This suggests that the "Invalid DATE, DATETIME" condition is being triggered.
Modification of Ocsinventory/
Suggested approach:
Remove the ":date" queryformat option from the rpm command, to get INSTALLTIME in epoch format. Convert epoch format to YYYY-MM-DD HH:MM:SS.
Possible code change:
The attached code change works in my environment (en_US.utf8 locale, all system times in GMT).
Changed in ocsinventory-unix-agent: | |
assignee: | nobody → mortheres (mortheres) |
importance: | Undecided → Medium |
Changed in ocsinventory-unix-agent: | |
status: | Fix Committed → Fix Released |
Hi,
Thanks a lot for your patch. It works very well on my Fedora 19 :D :D.
Your patch has been integrated in revision 1167 of ocsinventory- unix-agent/ stable- 2.1 branch: http:// bazaar. launchpad. net/~ocsinvento ry-dev/ ocsinventory- unix-agent/ stable- 2.1/revision/ 1167
It will be included in final OCS 2.1 release :) :).
Kind regards,
--
Guillaume