EmonCMS 9.2 migration data corruption

After migrating to 9.2 from 8.5 I noticed after a day or so that daily phptimeseries feeds were not updating anymore. Digging in logs I came to notice that a lot of PHP errors were being generated around timezone issues.

The reason for this was that some user still had an old timezone entry in the database (ie. -1, 2 etc. instead of a proper php timezone entry like 'Europe/Amsterdam'.)

Is it an idea to check for that in the user_model.php code (function get_timezone($userid)) catching the exception and defaulting to a proper timezone

 

Furthermore, my phptimeseries data seems corrupted now, will the recover script still work with emoncms 9.2?

 

Somehow the data seems to be there, when I use the simplezoom widget the daily data is visible, when using the zoom widget only some daily data from 2012..... any idea?

 

Paul Reed's picture

Re: EmonCMS 9.2 migration data corruption

still had an old timezone entry in the database...
An interesting point! I'll flag that up as an issue to be looked at.

Furthermore, my phptimeseries data seems corrupted now...
I don't think that this can be rectified easily. Emoncms has evolved in v9 to interpret time zones differently to the previous version, which has meant that it gathers 'daily feed data' possibly within a different time-frame, resulting in it not being captured. The data is still there - but it's not displayed.

I've discussed this with Trystan & Chaveiro previously and remains unresolved.

Paul

firefox7518's picture

Re: EmonCMS 9.2 migration data corruption

arjen, this is also exactly my issue since i migrated to 9.1. I asked for help here: http://openenergymonitor.org/emon/node/11566

Can you check if it is the same for you when looking on the data in the edit section of the visualization? 

I had almost 100 accounts on it and 500 inverters sending data to it. Of course I did not thought that it could be that harmful to the data so I only changed the timezone for my valid clients and left the others as they are. Your find could be actually the reason for this strange behavior we have.

If this is true it would be maybe great to update the table to default UTC timezone for every account to be save?

arjen's picture

Re: EmonCMS 9.2 migration data corruption

@Paul

>An interesting point! I'll flag that up as an issue to be looked at.

I saw it on github, thanks!

 

But it gets stranger, the new day here has begun and the data is visible again. I thought I've seen the same thing last morning but now I can confirm this behavior. But I cannot explain why.... I will check again during the day.

All seems fine now.

>I don't think that this can be rectified easily. 

On a test domain I placed a backup of the data from before the migration to 9.2 but with the current emoncms 9.2 build. Same behavior, so I don't think the data is really corrupt.

I will put the complete old build including recent data back to see what happens.

arjen's picture

Re: EmonCMS 9.2 migration data corruption

It's now 2:25 and all (!) data seems to be there...... ???

firefox7518's picture

Re: EmonCMS 9.2 migration data corruption

Check daily again. My Zoom widget shows every day different Total kWh values! One day I have a total of 5800kWh and the other day it's 11800kWh! On these days it goes back to April where I started the feed.

As checked in the visual edit daily functionality the data seems to be there and complete but somehow the timezones are acting up?

arjen's picture

Re: EmonCMS 9.2 migration data corruption

Ok, I placed an older (8.4.0) version of emoncms on the server against the current data, using the vis edit daily of this version I suddenly see an extra datapoint on the day I did the migration to 9.2. This datapoint is offset by -1 hour. 

I can select the datapoint and click on delete but this does only set the datapoint to zero. Is there a way to really delete this specific datapoint?

 

and since the time has passed 12:00 the data is skewed again.

 

arjen's picture

Re: EmonCMS 9.2 migration data corruption

Ok, found a way to delete the datapoint using a modified version of the recovery script found in the "usefulscripts" repo

For those that need it and know the time entry of the faulty datapoint change the following lines in common.php:

$dp = unpack("x/Itime/fvalue",$d);
fwrite($fhw, pack("CIf",249,$dp['time'],$dp['value']));

to:

$dp = unpack("x/Itime/fvalue",$d);
if ($dp['time']==TIMEVALUE) {  //replace TIMEVALUE by something as 1448751600
    echo "Time entry found and skipping\n";
    }
else {
    fwrite($fhw, pack("CIf",249,$dp['time'],$dp['value']));
    }

 

Data still not visible in the new version of emoncms though. Maybe there is some other issues that is casuing the data to be displayed incorrectly.

 

Update:

 

When I change the following line in zoom.php

get_feed_data_async(vis_feed_kwh_data_callback,null,kwhd,start,end,3600*24,1,1);

to:

get_feed_data_async(vis_feed_kwh_data_callback,null,kwhd,start,end,3600*24,1,0); (limit interval to 0 / false)

the data gets visible again. This is logical as the phptimeseries engine then just bluntly feeds all available entries back in the data array.

Somehow this piece of code in the phptimeseries engine is causing an undesirable effect.

if ($limitinterval)
{
$diff = abs($dptime-$time); 
if ($diff<($interval/2)) {  
$value = $array['value'];
}

not sure yet why tho

In the phptimeseries DB files I can see that during the last year the time entry (epoch) jumps two times from daily at 22:00 to daily at 21:00, the dates match DST time changes so that's probably correct. But after the update to 9.2 the entries are at 23:00.

Could this be the reason that the zoom graph has issues showing the data? If so, it might be an idea to write a script based on the recovery script that takes feed_id and timezone as input and resets all datapoints to the proper time?

If this is the case I can try to write such a script.

arjen's picture

Re: EmonCMS 9.2 migration data corruption

Paul,

 

I think I have a solution. The visualisation widgets use the current time als input to get data within a certain time frame. This can cause the phptimeseries engine to 'miss' data entries if the option "$limitinterval" is set to true as in the code to get the data it is being checked whether the found data is within given interval/2.

I think the best solution for this is to first seek for the first data item in the given timeframe and then 'sync' the entry's time value to be the start time entry for the further search. This puts the start of the interval search at an real data item and also speeds up the data gathering if the data set on disk is smaller than the given timeframe. 

Items found after this and still not fit within interval/2 can then be discarded with confidence.

I will put up a pull request to implement this.

firefox7518's picture

Re: EmonCMS 9.2 migration data corruption

I tried your git hub fix. Funny thing is that it shows the data from April to October when the DST happened. After that the data will not be shown :-) 

arjen's picture

Re: EmonCMS 9.2 migration data corruption

Strange, then probably the first data entry falls outside the interval/2 range. What happens if you place in the following function of phptimeseries.php

public function get_data($feedid,$start,$end,$interval,$skipmissing,$limitinterval)

 

at the top of the function the follwing line:

$limitinterval = 0;

So it becomes:

public function get_data($feedid,$start,$end,$interval,$skipmissing,$limitinterval)
{

$limitinterval = 0;
$start = intval($start/1000);

[...etc]

?

chaveiro's picture

Re: EmonCMS 9.2 migration data corruption

I've dig into phpTimeSeries engine code and i saw something i didn't notice before.

The engine format expects 9 bytes per data point.

But the way of writing this 9 bytes with fwrite($fh, pack("CIf",249,$time,$value)) , expecting 1 + 4 + 4 bytes for 249char, time and value respectively.

The problem is that pack format l and f for time and value is platform/os dependent.
Meaning that real size in some platforms could take 17 bytes (1+8+8) or 13 bytes (1+8+4 or 1+4+8) but the engine expects only 9bytes.

From PHP documentation:
C - unsigned char
- unsigned integer (machine dependent size and byte order)
f - float (machine dependent size and representation)

So i would not recommend using phpTimeSeries engine if you are not sure what sizes your platform / OS 32vs64bits / PHP version will use, because anything different from 9bytes will break the feed data.

 

Update:
Regarding that limitinterval on get_data, i'm not sure why it was implemented, because the engine has no interval limits(each data point time is saved as passed), maybe Trystan can give an insight of what was the initial idea for having it.

Paul Reed's picture

Re: EmonCMS 9.2 migration data corruption

What are the implications for correcting this (complex) issue, if corrected would it affect historical data?

Paul

 

chaveiro's picture

Re: EmonCMS 9.2 migration data corruption

All time-value pairs have a preceding '249' char so they are easy to locate, if everything is ok the char must happen every 9bytes offset.

If a particular deploy used any other size than 9bytes, then the data points will be there but with garbage in between, as there is some auto correction code on post metode that sets the file size to multiple o 9 if it detects an error like that.

If the OS was updated latter and the size changed mean time, they can be recovered also, detecting when the format changed by looking for that 249 char offsets.

I never used this engine so didn't test the behaviour. My previous post is just a warning of what can happen.

The raspberry pi is running a 32bits OS right? If so there should not be any problem.

 

arjen's picture

Re: EmonCMS 9.2 migration data corruption

>The problem is that pack format l and f for time and value is platform/os dependent.

That is correct, and there are issues reported with that. But it looks that for I and f no 64bits support is implemented (yet?). 

From the php website:

"PHP 5.6.3 The "q", "Q", "J" and "P" codes were added to enable working with 64-bit numbers."

and:

"Even though in a 64-bit architecure intval(6123456789) = 6123456789, and sprintf('%b', 5000000000) = 100101010000001011111001000000000 
pack will not treat anything passed to it as 64-bit."

 

I'm running on a 64bit CPU, with 64bit Debian and with a PHP version that supports 64bit int. To be sure I checked the phptimeseries DB files and the entries are 9 bytes long. So it looks safe (for now).

Regarding the limitinterval, the root cause of issues around not displaying certain values and hops in data entries comes from writing the time values corrected for timezone. The code for posting data first gets the current time, corrects it for timezone and then writes it to disk.

That happens here in process_processlist.php:

public function kwhinc_to_kwhd($feedid, $time_now, $value)
{
[..]
$current_slot = $this->getstartday($time_now);

[...]

$this->feed->update_data($feedid, $time_now, $current_slot, $new_kwh);

[...]

Why not write time always in UTC and then let the timezone interpretation be done when retrieving the data?

If this cannot be fixed due to existing data, there are a few options to patch. One is the PR I did. You could also change all the references in de vis modules from:

get_feed_data_async(vis_feed_kwh_data_callback,null,kwhd,start,end,3600*24,1,1);

to default:

get_feed_data_async(vis_feed_kwh_data_callback,null,kwhd,start,end,3600*24,1,0);

To skip the limitinterval code in phptimeseries engine completely.

You could also 'loosen' the limitinterval check in phptimeseries engine:

if ($limitinterval)
{
$diff = abs($dptime-$time);
if ($diff<($interval/2)) {
$value = $array['value'];
}

 

to:

if ($limitinterval)
{
$diff = abs($dptime-$time);
if ($diff<$interval) {
$value = $array['value'];
}

Or skip the diff check all together and let the only difference be that the $limitinterval options lines up al the results neatly to the interval time and without that option use de DB time.

 

Then it will be like this:

if ($limitinterval)
{
$value = $array['value'];
} else {
$value = $array['value'];
$atime = $array['time'];
}

 

chaveiro's picture

Re: EmonCMS 9.2 migration data corruption

> Why not write time always in UTC and then let the timezone interpretation be done when retrieving the data?

It is indeed saved to engine in UTC.
Midnight of user post can be different for each user and/or time of the year. (DST changes).

So that getstartday() return the utc timestamp when its midnight at the user timezone for the passed date. Confusing? :)

I would just use your last option for limit interval. Can you test if fixes all the issues you are having?

arjen's picture

Re: EmonCMS 9.2 migration data corruption

>So that getstartday() return the utc timestamp when its midnight at the user timezone for the passed date. Confusing? :)

 

That was confusing indeed :) I thought that timenow was changed by applying user timezone, but PHP doc indeed states that this is not the case.

 

The last option seems to have a side effect, I get the last value in the DB displayed twice.

 

This works without any issues:

if ($limitinterval)
{
$diff = abs($dptime-$time);
if ($diff<$interval) {
$value = $array['value'];
}

 

update:

I changed the PR on github to reflect the above.

http://openenergymonitor.org/emon/node/11882
also describes the same issue and the current PR seems to solve it.

blaal02's picture

Re: EmonCMS 9.2 migration data corruption

This fixed my kWh/d multigraph too.

Thanks!

Sandy

efidi13's picture

Re: EmonCMS 9.2 migration data corruption

Hello, I applied the fix proposed by arjen in PR 414 and now everything works as expected.

Without his fix the zoom visualization was totally unusable (I'm using Emoncms 9.3).

The only thing I don't understand is why it seems that so few people encountered this problem.

BTW thank you arjen!

Regards,

Federico.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.