<div dir="ltr"><br><div class="gmail_quote">---------- Forwarded message ----------<br>From: <b class="gmail_sendername">张亚琪</b> <span dir="ltr"><<a href="mailto:zhangyingyun001@gmail.com">zhangyingyun001@gmail.com</a>></span><br>Date: 2014-11-16 20:04 GMT+08:00<br>Subject: Re: [ovirt-devel] DWH cannot gain hourly history data from db because of the bloody time problem.<br>To: Yaniv Dary <<a href="mailto:ydary@redhat.com">ydary@redhat.com</a>><br><br><br><div dir="ltr">hi, <span style="color:rgb(0,0,0);font-family:Arial;font-size:13px">Yaniv. Thank you for your reply. However, I think that there is no </span><span style="color:rgb(0,0,0);font-family:Arial;font-size:13px">sync problem in my setup, because the engine, dwh and reports are in the same machine. As a new, I am not familiar with </span><span style="color:rgb(0,0,0);font-family:Arial;font-size:13px">UTM time. </span><div><span style="color:rgb(0,0,0);font-family:Arial;font-size:13px">And please see the attachment. Looking forward your reply. Thanks a lot.</span></div><div><span style="font-size:13px;font-family:Arial;color:rgb(0,0,0)"><br></span></div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">2014-11-16 7:31 GMT+08:00 Yaniv Dary <span dir="ltr"><<a href="mailto:ydary@redhat.com" target="_blank">ydary@redhat.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="font-family:Arial;font-size:10pt;color:#000000"><div>Are all your servers sync to the same ntp and in UTM time?</div><div>Can you attach logs? </div><div><br></div><div><br></div><div>Yaniv</div><div><br></div><hr><blockquote style="border-left:2px solid #1010ff;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt"><b>From: </b>"张亚琪" <<a href="mailto:zhangyingyun001@gmail.com" target="_blank">zhangyingyun001@gmail.com</a>><br><b>To: </b><a href="mailto:Devel@ovirt.org" target="_blank">Devel@ovirt.org</a><br><b>Sent: </b>Friday, November 14, 2014 11:22:38 AM<br><b>Subject: </b>[ovirt-devel] DWH cannot gain hourly history data from db because of the bloody time problem.<div><div><br><div><br></div><div dir="ltr"><div><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif">hi everybody,</span></div><div><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"> </span></div><div><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"> 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：</span></div><div><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"><br></span></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif">"SELECT history_id,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"><span style="white-space:pre-wrap"><span style="white-space:normal"> </span></span>history_datetime,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"> <span style="white-space:pre-wrap"><span style="white-space:normal"> </span></span>datacenter_id,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"> <span style="white-space:pre-wrap"><span style="white-space:normal"> </span></span>datacenter_status,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"> <span style="white-space:pre-wrap"><span style="white-space:normal"> </span></span>minutes_in_status,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"><span style="white-space:pre-wrap"><span style="white-space:normal"> </span></span>datacenter_configuration_version</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif">FROM datacenter_samples_history</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif">WHERE <b>history_datetime >= '"+context.lastHourAggr+</b>"'</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif">AND history_datetime < '"+TalendDate.addDate(context.lastHourAggr, 1,"HH")+"'</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif">ORDER BY history_datetime, </span></blockquote></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"> datacenter_id, </span></blockquote></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"> <span style="white-space:pre-wrap"><span style="white-space:normal"> datacenter_status"</span></span></span></blockquote></blockquote><div><br></div><div><br></div><div>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:</div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">ovirt_engine_history=# select * from history_configuration;</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> var_name | var_value | var_datetime </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">-------------------+-----------+------------------------</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> MinimalETLVersion | 3.5.0 | </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> default_language | en_US | </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> firstSync | false | 2014-10-13 19:42:00+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> lastDayAggr | | 2014-11-14 00:00:00+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><b> lastHourAggr | | </b>2014-<span color="#ff0000" style="color:#ff0000"><b>11-15</b></span> 06:00:00+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> HourlyAggFailed | false | </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">(6 rows)</blockquote><div><br></div></blockquote><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">ovirt_engine_history=# select <b>history_datetime</b> from datacenter_samples_history;</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> history_datetime </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">----------------------------</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-<b><span color="#ff0000" style="color:#ff0000">11-13</span></b> 03:07:00.23+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:08:00.238+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:09:00.229+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:10:00.221+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:11:00.229+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:12:00.221+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:13:00.237+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:14:00.22+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:15:00.221+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:16:00.238+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> 2014-11-13 03:17:00.238+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><br></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> </blockquote><div><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"><span color="#000000" style="color:#000000"><span style="line-height:24px"><br></span></span></span></div><div><span face="comic sans ms, sans-serif" style="font-family:'comic sans ms',sans-serif"><span color="#000000" style="color:#000000"><span style="line-height:24px">Obviously， history_datetime < lastHourAggr , the data will never be inserted to the </span>datacenter_hourly_history. And the place where I bold <span style="line-height:24px">is the </span><span style="line-height:24px">root cause of the error. Then , I try to update the </span></span><b>lastHourAggr </b>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 ! </span></div></div><br></div></div>_______________________________________________<br>Devel mailing list<br><a href="mailto:Devel@ovirt.org" target="_blank">Devel@ovirt.org</a><br><a href="http://lists.ovirt.org/mailman/listinfo/devel" target="_blank">http://lists.ovirt.org/mailman/listinfo/devel</a></blockquote><div><br></div></div></div></blockquote></div><br></div>
</div></div></div><br></div>