RPM.pm INSTALLDATE format not always accepted by MySQL

[Cameron Owen]

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:date}, which uses the current locale's date format for strftime %c. In the en_US.utf8 locale, this results in a date string like this:

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://dev.mysql.com/doc/refman/5.7/en/datetime.html):

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/Agent/Backend/OS/Generic/Packaging/RPM.pm to modify the date string to "YYYY-MM-DD HH:MM:SS" results in correct dates in the softwares table, and subsequent correct date display in the "All softwares" view.

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

[mortheres (mortheres)]

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/~ocsinventory-dev/ocsinventory-unix-agent/stable-2.1/revision/1167

It will be included in final OCS 2.1 release :) :).

Kind regards,

--
Guillaume