New data is not inserted into the eHealth database due to eHealth system message errors

Document ID : KB000047550
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

eHealth is not inserting new polled data to the database.

Windows eHealth server sets incorrect Time Zone when rebooted.

The eHealth system messages contains errors like:

 

Friday, October 14, 2016 09:02:55 PM Internal Error nhiPoller[Net] Pgm nhiPoller[Net]: Expectation for 'Bad' failed (Element <hostName>-GigabitEthernet1/0/13: Sample time difference for 1476450175 and 1476476958 less than deltaTime of 0 in file ../UplRespMgr.C, line 2099). (cu/cuAssert)

 

 

Cause:

When the server where eHealth is installed is rebooted the time zone at the OS level is resetting itself with the wrong Time Zone. In this instance it is set to a time in the future. The same can be seen if set to a time in the past, though this focus on the more common and likely scenario where it is set to the future.

With the wrong time set on the server, when the eHealth nhServer command is run to start the eHealth server process, the first time it adds polled data to the database the time stamp for the data is in the future.

If after this the OS server time is reset to the correct time, when the next poll cycle runs and attempts to have data inserted into the database it complains that the data being inserted is from before the newest data it is has been provided. This triggers the failures observed here.

Being a statistical reporting tool based on time, correct time stamps in the database are critical for the maintenance of correct functionality. 

 


Solution(s):

We can choose from three different options to resolve this problem once it occurs.

A: Restore a valid database save. There will be a data gap between the time the database save was generated and the current time. For database restoration and load instructions please refer to the eHealth Administration User Guide for more details.

B: Let time pass. For example if there is just a one hour time difference, allow one hour to pass. There will be a one or two hour gap in the data in total, but after the server catches up to current time the errors will resolve and it will no longer be an issue.

C: Identify the latest polled data tables with time stamps newer than the current time, drop them, remove their references in related database tables, commit the change to the database, restart the nhServer. Using this solution will result in a working system within a short time, and the data will still show a gap covering the time since the problem was initiated. If uncomfortable making the following database changes on your own, please don't hesitate to open a new case with the Support team, referencing this Knowledge Base article, and we'll gladly help resolve the problem.

 

To accomplish option C complete the following:

1: Stop the nhServer. In Windows open the Services UI and stop the eHealth Server. In Unix systems run the "nhServer stop" command as the $NH_USER.

2: Connect to the SqlPlus prompt. Use KB article TEC1425734 for instructions to connect.

3: List out the latest tables ordered by name using the following command:

select table_name from user_tables where table_name like '%NH_STATS0%' order by table_name; 

Example output tables names will be something like "NH_STATS0_1476449999", where the number at the end of the table is a time stamp in UTC format.

4: Convert the numeric value in the NH_STATS0_* named tables at the end of the output from UTC time stamps to human readable time. For example we might have a list of tables similar to:

NH_STATS0_1476449999
NH_STATS0_1476453599
NH_STATS0_1476457199
NH_STATS0_1476460799
NH_STATS0_1476464399
NH_STATS0_1476467999
NH_STATS0_1476471599
NH_STATS0_1476475199
NH_STATS0_1476478799

A Google search for "Unix Time Conversion" will result in a number of options that will convert the UTC time stamps in the table names to a human readable time stamp. For example if we convert 1476449999 in the NH_STATS0_1476449999 named table, we get a time stamp of:

GMT: Fri, 14 Oct 2016 12:59:59 GMT
Your time zone: 10/14/2016, 8:59:59 AM GMT-4:00 DST

E - In the situation where this was seen recently, where the above NH_STATS0_* table examples came from, the time zone for the eHealth server involved is Perth time (Australia) showing "14-oct-2016 11:25 pm". That is UTC+8. In this example we need to add 8 hours to GMT to get Perth time, or subtract 8 hours from Perth time to to get GMT. An example of a useful time conversion website is http://www.epochconverter.com/.

The goal of this step is to identify the tables we have to remove. The ones we need to remove have a UTC time stamp that represents a time in the future compared to the current time. Using the newest NH_STATS0 tables referenced above as an example we end up with:

NH_STATS0_1476449999 = GMT: Fri, 14 Oct 2016 12:59:59 GMT; +8 hours for Perth = 8:59:59 PM
NH_STATS0_1476453599 = GMT: Fri, 14 Oct 2016 13:59:59 GMT; +8 hours for Perth =9:59 AM 10/14
NH_STATS0_1476457199 = GMT: Fri, 14 Oct 2016 14:59:59 GMT; +8 hours for Perth =10:59 AM 10/14
NH_STATS0_1476460799 = GMT: Fri, 14 Oct 2016 15:59:59 GMT; +8 hours for Perth =11:59 AM 10/14
NH_STATS0_1476464399 = GMT: Fri, 14 Oct 2016 16:59:59 GMT; +8 hours for Perth =0:59 AM 10/15
NH_STATS0_1476467999 = GMT: Fri, 14 Oct 2016 17:59:59 GMT; +8 hours for Perth =1:59 AM 10/15
NH_STATS0_1476471599 = GMT: Fri, 14 Oct 2016 18:59:59 GMT; +8 hours for Perth =2:59 AM 10/15
NH_STATS0_1476475199 = GMT: Fri, 14 Oct 2016 19:59:59 GMT; +8 hours for Perth =3:59 AM 10/15
NH_STATS0_1476478799 = GMT: Fri, 14 Oct 2016 20:59:59 GMT; +8 hours for Perth =4:59 AM 10/15

F - Next we need to drop those tables from the database. After doing so when the server restarts and inserts new data it gets inserted in a proper time sorted order. The command to use is "drop table <tableName>". In this example we would run the following commands against the tables ID'd for removal:

drop table NH_STATS0_1476460799;
drop table NH_STATS0_1476464399;
drop table NH_STATS0_1476467999;
drop table NH_STATS0_1476471599;
drop table NH_STATS0_1476475199;
drop table NH_STATS0_1476478799;

G - Next is removing references for those tables from the NH_STATS_POLL_INFO table in the database. That is done by running the command:

truncate table NH_STATS_POLL_INFO;

H - Lastly we have to clean the NH_RLP_BOUNDARY table, removing any reference to the dropped NH_STATS0.* tables from step F above. To do that we run the command:

delete from NH_RLP_BOUNDARY where MAX_SAMPLE_TIME > <UTC_Stamp_Before_Oldest_Dropped_Table>;

In this example the NH_STATS0_1476460799 table referenced above is the oldest table. In this example we changed the UTC value to be a second earlier than it was (1476460799 becomes 1476460798). Then the following command was run:

delete from NH_RLP_BOUNDARY where MAX_SAMPLE_TIME > 1476460798; 

The result should be that the number of rows deleted matches the number of database tables dropped. In this case we dropped 6 tables and the result of that command returned a message stating that we had "6 rows deleted".

I - Commit the changes to the database using the command "commit", and exit the SqlPlus prompt with the command "exit".

J - The solution is complete and the nhServer can be restarted. In Windows open the Services UI and start the eHealth Server. In Unix systems run the "nhServer start" command as the $NH_USER.