Fwd: DWH cannot gain hourly history data from db because of the bloody time problem.

---------- Forwarded message ---------- From: 张亚琪 <zhangyingyun001@gmail.com> Date: 2014-11-16 20:04 GMT+08:00 Subject: Re: [ovirt-devel] DWH cannot gain hourly history data from db because of the bloody time problem. To: Yaniv Dary <ydary@redhat.com> hi, Yaniv. Thank you for your reply. However, I think that there is no sync problem in my setup, because the engine, dwh and reports are in the same machine. As a new, I am not familiar with UTM time. And please see the attachment. Looking forward your reply. Thanks a lot. 2014-11-16 7:31 GMT+08:00 Yaniv Dary <ydary@redhat.com>:
Are all your servers sync to the same ntp and in UTM time? Can you attach logs?
Yaniv
------------------------------
*From: *"张亚琪" <zhangyingyun001@gmail.com> *To: *Devel@ovirt.org *Sent: *Friday, November 14, 2014 11:22:38 AM *Subject: *[ovirt-devel] DWH cannot gain hourly history data from db because of the bloody time problem.
hi everybody,
Recently, I have tested oVirt Reports. And I encountered a problem about some reports that cannot show data. And then I setup the DWH development environment. I found maybe this reason for missing data is the AggregationToHourly3.5. When data from datacenter_samples_history were inserted to datacenter_hourly_history, u will find nothing happened. Because the select sql before inserting data reads:
"SELECT history_id,
history_datetime,
datacenter_id,
datacenter_status,
minutes_in_status,
datacenter_configuration_version
FROM datacenter_samples_history
WHERE *history_datetime >= '"+context.lastHourAggr+*"'
AND history_datetime < '"+TalendDate.addDate(context.lastHourAggr,
1,"HH")+"'
ORDER BY history_datetime,
datacenter_id,
datacenter_status"
And then I queried the table of datacenter_samples_history & history_configuration (has the field of lastHourAggr) in the db of ovirt_engine_history. The results are as follows:
ovirt_engine_history=# select * from history_configuration;
var_name | var_value | var_datetime
-------------------+-----------+------------------------
MinimalETLVersion | 3.5.0 |
default_language | en_US |
firstSync | false | 2014-10-13 19:42:00+08
lastDayAggr | | 2014-11-14 00:00:00+08
* lastHourAggr | | *2014-*11-15* 06:00:00+08
HourlyAggFailed | false |
(6 rows)
ovirt_engine_history=# select *history_datetime* from
datacenter_samples_history;
history_datetime
----------------------------
2014-*11-13* 03:07:00.23+08
2014-11-13 03:08:00.238+08
2014-11-13 03:09:00.229+08
2014-11-13 03:10:00.221+08
2014-11-13 03:11:00.229+08
2014-11-13 03:12:00.221+08
2014-11-13 03:13:00.237+08
2014-11-13 03:14:00.22+08
2014-11-13 03:15:00.221+08
2014-11-13 03:16:00.238+08
2014-11-13 03:17:00.238+08
Obviously, history_datetime < lastHourAggr , the data will never be inserted to the datacenter_hourly_history. And the place where I bold is the root cause of the error. Then , I try to update the *lastHourAggr *in the table of history_configuration. Reports works successfully. However, the lastHourAggr will change to 2014-11-15 afterwards. But u know Today is 2014-11-14 ! I have no idea about why the value of lastHourAggr is 2014-11-15. Would u help me solve this problem. Thanks a lot !
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel

Hi, UTC time is the coordinated Universal Time, and we recommend syncing the machines according to it, so the data will not be affected when changing the clock from summer time to winter time and back. All reports can be shown in the local time you choose in the reports portal. But still, this should not cause issues with hourly reports not showing. Please attach all logs. Should be located at: /var/log/ovirt-engine /var/log/ovirt-engine-dwh /var/log/ovirt-engine-reports Also please let us know what ovirt 3.5 version you are using: rpm -qa |grep ovirt For ovirt-engine-dwh, ovirt-engine-reports and ovirt-engine. Last, please let us now what timezone is set currently for your machine. Best regards, --- Shirly Radco BI Software Engineer Red Hat Israel Ltd. ----- Original Message -----
From: "张亚琪" <zhangyingyun001@gmail.com> To: Devel@ovirt.org Sent: Monday, November 17, 2014 3:51:07 AM Subject: [ovirt-devel] Fwd: DWH cannot gain hourly history data from db because of the bloody time problem.
---------- Forwarded message ---------- From: 张亚琪 < zhangyingyun001@gmail.com > Date: 2014-11-16 20:04 GMT+08:00 Subject: Re: [ovirt-devel] DWH cannot gain hourly history data from db because of the bloody time problem. To: Yaniv Dary < ydary@redhat.com >
hi, Yaniv. Thank you for your reply. However, I think that there is no sync problem in my setup, because the engine, dwh and reports are in the same machine. As a new, I am not familiar with UTM time. And please see the attachment. Looking forward your reply. Thanks a lot.
2014-11-16 7:31 GMT+08:00 Yaniv Dary < ydary@redhat.com > :
Are all your servers sync to the same ntp and in UTM time? Can you attach logs?
Yaniv
From: "张亚琪" < zhangyingyun001@gmail.com > To: Devel@ovirt.org Sent: Friday, November 14, 2014 11:22:38 AM Subject: [ovirt-devel] DWH cannot gain hourly history data from db because of the bloody time problem.
hi everybody, Recently, I have tested oVirt Reports. And I encountered a problem about some reports that cannot show data. And then I setup the DWH development environment. I found maybe this reason for missing data is the AggregationToHourly3.5. When data from datacenter_samples_history were inserted to datacenter_hourly_history, u will find nothing happened. Because the select sql before inserting data reads:
"SELECT history_id,
history_datetime,
datacenter_id,
datacenter_status,
minutes_in_status,
datacenter_configuration_version
FROM datacenter_samples_history
WHERE history_datetime >= '"+context.lastHourAggr+ "'
AND history_datetime < '"+TalendDate.addDate(context.lastHourAggr, 1,"HH")+"'
ORDER BY history_datetime,
datacenter_id,
datacenter_status"
And then I queried the table of datacenter_samples_history & history_configuration (has the field of lastHourAggr) in the db of ovirt_engine_history. The results are as follows:
ovirt_engine_history=# select * from history_configuration;
var_name | var_value | var_datetime
-------------------+-----------+------------------------
MinimalETLVersion | 3.5.0 |
default_language | en_US |
firstSync | false | 2014-10-13 19:42:00+08
lastDayAggr | | 2014-11-14 00:00:00+08
lastHourAggr | | 2014- 11-15 06:00:00+08
HourlyAggFailed | false |
(6 rows)
ovirt_engine_history=# select history_datetime from datacenter_samples_history;
history_datetime
----------------------------
2014- 11-13 03:07:00.23+08
2014-11-13 03:08:00.238+08
2014-11-13 03:09:00.229+08
2014-11-13 03:10:00.221+08
2014-11-13 03:11:00.229+08
2014-11-13 03:12:00.221+08
2014-11-13 03:13:00.237+08
2014-11-13 03:14:00.22+08
2014-11-13 03:15:00.221+08
2014-11-13 03:16:00.238+08
2014-11-13 03:17:00.238+08
Obviously, history_datetime < lastHourAggr , the data will never be inserted to the datacenter_hourly_history. And the place where I bold is the root cause of the error. Then , I try to update the lastHourAggr in the table of history_configuration. Reports works successfully. However, the lastHourAggr will change to 2014-11-15 afterwards. But u know Today is 2014-11-14 ! I have no idea about why the value of lastHourAggr is 2014-11-15. Would u help me solve this problem. Thanks a lot !
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
participants (2)
-
Shirly Radco
-
张亚琪