Archived Forum

i would also like to convert to mysql so i can edit more easy in the future....

i know there is a fix to ntpd but i would like to be  able to edit the data also

damm my water feed is screwed.... it starts at 1970... and only one datapoint today....

given that i run off a harddrive and dont care about the wear are there an easy to follow way to convert from timeseries to mysql?

Hello Boelle, Whats the size of the datafile for the feed? You could write a script to convert the feed to mysql, there's information on the format of the feeds here: https://github.com/openenergymonitor/documentation/tree/master/BuildingB... that might help

upps... each feed have grown to 6mb.... have about 12 of them but only 2 of them are gone bad

what i could live with is if i could add a value to all datapoints after a given point where the 1970 issue causes them to go negative

but really a guide on how to convert to mysql would be awesome... i know you like to promote low write version but i'm running the full version, i just miss mysql from the setup

EDIT: Powered by openenergymonitor.org | v8.3.6

EDIT2: would adding MysqlTimeSeries.php to the place add mysql back as feedengine?

if so yes what is the right folder? are there any other settings i would have to change? i see in some documentation that settings.php needs a change to say use mysql as default engine... but there is no line at all with default engine in the first place

Sorry I can't help you with the feed conversion, but rather than finding a way to easily "correct" the effects of something that shouldn't of happened and could still reoccur again at anytime may not be the best way forward.

If you are running on a hdd there is no reason to use the low-write SD card, Trystan "promotes" the low-write version as a solution for users that want to run on an SD card which is not ideal and has weaknesses and therefore workarounds. You should not be troubled by the issues caused by the workarounds if you do not have the situation they "workaround".

However if you do wish to run the read-only image there is a fix for the cause of the "1970" issue as previously discussed (http://openenergymonitor.org/emon/node/5961) 

There are times when you need to reboot in a controlled way and providing you remember to do certain things you may be ok, but power grid outages, tripped circuit breakers etc are all real life scenarios that your setup should be able to cope with without corrupting your data.

Paul

i do not use the low write.. i run the full version.... 

and yes power out can happen.... 

that is why i want mysql back so i can correct data if they get in the system the wrong way... it must be possible..

just getting mysql back is one thing... then i can always wait and convert the data

EDIT: btw... i'm setting up a new pi to work as a NTP server... maybe i should really use that one for emoncms and start from fresh and transfer the data... just so everyone know what i run etc.... ????

i do not use the low write.. i run the full version.... 

You may be running the master branch (full version) of emoncms but the 1970 issue only occurs due to the changes made to the SD image to make it read-only. If you install the read-only image to a hdd it it still has all the read-only characteristics despite changing the emoncms branch.

i'm setting up a new pi to work as a NTP server

I assume you mean "with a NTP server"? which is exactly what your existing Pi is doing, but during boot up until a NTP server is contactable the "stored time" is used and because of the location of that "stored time" on the read-only image it is not accessible and therefore resets to "0" until an NTP server is contact able. Adding another NTP server will have no effect during boot up.

start from fresh and transfer the data

I think that could be a wise choice. if you want the full version on a hdd use a standard raspbian image and not the read-only SD image.

Paul

I assume you mean "with a NTP server"? 

No as an NTP server... i will attach a GPS module to it via I2C bus with an active antenna outside

But the magic Q.... does the full version have mysql in there as feedengine? oh well i guess i will ask how and all that stuff when i get to it... now i just need to make sure that it starts networking an NTP as the first thing before anything else.

No as an NTP server... i will attach a GPS module to it via I2C bus with an active antenna outside

Interesting project, this will give you very accurate timekeeping whenever the internet is unavailable (as long as it's not too cloudy) but just to be clear, I hope you are not doing it for the wrong reasons, it will not resolve the "1970" issue! The ethernet, wi-fi and I²C are not immediately available when the Pi is started, When the "1970" issue occurs the Pi isn't even ready enough to follow a symlink to another partition which is why the problem occurs.

Just as I hope you are not trying to use SQL for the wrong reasons, some users prefer to use SQL for a specific reason, but putting right errors that are easily prevented isn't usually one of them. You can by all means use SQL if you prefer, but the OEM project is moving away from SQL for recording mainstream data and any new development may not include SQL.

that is one of the reasons for setting up the NTP server part... but i will also join pool.ntp.org so others can make easy use of it.

i might connect the GPS via an usb-serial cable, not sure yet but the module have both options.

right now i'm trying to figure how to use the ntp-wait command so time can be set at boot before logging starts...

my reasons for SQL are so that i can edit the data and export them easy etc... if i can just get it in now i'm happy... the current emoncms is ok for me so dont need updates after its running.

excluding sql.. well ok but it that is like dictating people what to use... it would have been ok with me if it was there but not updated... but removing it completely without keeping the version that has SQL would push me away...

but i guess i would always try and extract the stuff in the sd card version that has SQL... (or did the older sd card version not run on sql exclusive?)

EDIT: my major reason for SQL is simple that its what i feel most comfortable with. It allows me to do stuff with the data. and i can find many more examples on how to change/manipulate the data with SQL than with any other system... also another reason is that i should have the choice what feedengine to use... my thought is why not just leave it in there and let the user decide?

i might connect the GPS via an usb-serial cable, not sure yet but the module have both options.

It will still not be available during the Pi's initial boot sequence.

right now i'm trying to figure how to use the ntp-wait command so time can be set at boot before logging starts...

I think you will find that this will just delay starting emoncms and/or emonhub (depending on if and how you configure it) the Pi will still boot up with a 1970 timestamp until the NTP is available. You are not changing how it starts up but when it fully starts up.

excluding sql.. well ok but it that is like dictating people what to use... 

Seems a little extreme, All this software is made open-source so that you can do what you want with it... for free! The developers will do what they do in the interest of the project and the majority of users, if you want something different. no problem, there's loads of info and help on hand for you to achieve that. 

I have not suggested that SQL will excluded or even not supported, nor am I promoting any particular data engine, I have merely explained that the OEM project has it's own data engines and any future development will most likely be for it's own data engines and not necessarily guaranteed to be compatible with SQL..

oki doki... but then might i ask how i can make sure network is up before it does anything else...

and remember i'm not an expoert... i'm a noob... i have googled far for this, been on irc chats... no where i can find a step by step guide on how to change boot order so that i'm sure that i load network first and then ntp next... and then we can start thinkering about loading emoncms stuff

how i can make sure network is up before it does anything else...

You can't, well not easily anyway. You are straying into rewriting the raspbian OS. The Pi doesn't have a clock, so it is designed to get an approximate time from the file system just for the purpose of getting up and running, it doesn't care what the time value is. it just needs a time reference point to count from so it can boot up, which is why it's ok with 0 (1970) it just counts from there.

Without this timekeeping it won't load any modules or drivers etc, it will never get a network connection or look for a NTP signal or start any software.

Most of the time we (OEM'ers) keep the Pi's running 24/7 and the only time the Pi is not running is momentarily during a reboot, when it boots back up it uses the time last saved as an approximate time to allow it to start the services it needs to correct the time.

This correction normally occurs pretty soon after starting the Pi as we normally have a permanent internet connection.

So the time is slightly wrong for a very short period of time and no-one has a problem.

If you do not have a network connection (or RTC or GPS) available when NTP tries to update it will continue to use the slightly wrong time it got from file (I guess you can use ntp-wait here until the time is corrected)

If the Pi was powered down for a week, the "missing time" would be significant enough for the use of ntp-wait or a RTC because the temporary time would be 1 week out. but as we don't switch our Pi's off this is unlikely to happen.

However! since the "SD image" is read-only one of 2 things happens, either the time cannot be saved to file because it's read-only and the "temporary time" is the last time the partition was mounted as writeable (rpi-rw) possibly the install date, Or if the "saved time" moved to a separate partition so it was writable, then at boot time the partition containing the saved file will not be accessible via the symlink and that will result in a temporary timestamp of "0".

Bottom line is "stock" raspbian is built to cope with this and is only a problem if your pi is dormont for a long time or has no internet connection and if you need to run read-only then "ntp-backup" allows the current time to be saved to the original "accessible at boot time" location despite being read-only.

So if you use standard raspbian - no issue, if you use read-only raspbian with ntp-backup installed - no problem .But anything else (raspbian) - problems can and probably will occur.

That's all ! the Pi just needs a relatively recent time for the moments before the network is available. this isn't a ntp, wifi, network emonhub emoncms or raspbian thing it's a raspberry pi thing.

Paul

i have thought about it a bit... also i have other plans for the same pi... 

first thought is to not start emonhub like normal, but via s scrip check if time seems ok, and then if time is say somewhere past 1/1-2015 i start emonhub

does that sound more like a plan?

my other plan for the same pi is to run it as a stratum1 ntp server, i'm allready wireing up a gps module to a usb-serial cable... but that is a side project... 

EDIT: so far on this 2nd pi i have to play with i'm running the standard image... so i guess the 1970 issues is more less a problem anyway... but still a bit of sanity check does not hurt i guess and i can always check the ok date as the month's passes... but if i should have a as full version of emoncms what guide is the best one? there are a few and i have always been confused as wich one to follow. still i would like to add sql to the setup... once i first get it going i dont plan to upgrade anyway... 

by standard image i mean the one from ras pi org and after booting that image i used this guide to more or less reduce whats on there to the minimum http://www.cnx-software.com/2012/07/31/84-mb-minimal-raspbian-armhf-image-for-raspberry-pi/

on http://openenergymonitor.org/emon/node/9998 you point to http://openenergymonitor.org/emon/node/5092... it just says to put the image on both SD and HDD... but what image? the one from ras pi org ? other wise i can follow the guide ok.. i guess i only need to go down as far in the first post  that the serial port is made free for radio module? after that i go to https://github.com/emoncms/emoncms/blob/master/docs/LinuxInstall.md to install the full version of emoncms?

first thought is to not start emonhub like normal, but via s scrip check if time seems ok, and then if time is say somewhere past 1/1-2015 i start emonhub

​WHY ??????

The standard raspbian image wll save the time once every hour, how can you possibly confirm if that is working by checking if the time is "this year".

Why are you trying to fix something that is not broken? It works perfectly well! Just install it and use it, don't try and change it, that's what creates the problems.

If you have a need that demands a change that's another thing eg if you want read-only (which you don't) then install ntp-backup. but if you do not need to change it why on earth would you change something that works, for no apparent reason?

by standard image i mean the one from ras pi org

yes the latest raspbian image 

after booting that image i used this guide to more or less reduce whats on there to the minimum http://www.cnx-software.com/2012/07/31/84-mb-minimal-raspbian-armhf-image-for-raspberry-pi/

But Why ? I can only echo what I've already said about unnecessary changes, the 1st paragraph from that article reads

Many embedded systems applications do not require a desktop environment or graphical interface on the screen (e.g. server), and you may want to only install the files you really need on the SD card either to reserve as much space as possible for data and/or your program or to reduce costs.

It's not embedded, it's not on a sd card, you're not short of space if using a hdd and there is no cost ! this article was written 30mths ago and raspbian has been revised several times since then, plus numerous updates. What do you have to gain? You can potentially cause yourself numerous headaches to save a few Mb it doesn't make sense!

The article may be very good if you need a minimal install, I'm not judging the article, but you do not need a minimal install. 

You only cross a road if you need to get to the other side, getting knocked down is always a possibility and a chance you may choose to take, but if you get knocked down when you didn't need to cross, that's foolish! 

Here are the links given to another user todayhttp://openenergymonitor.org/emon/node/9998 regards installing emoncms on a hdd. You can and probably will, do something completely different, which is fine as long as when you do choose to do something differently you are prepared for different results.

Short Answer as i'm about to go out the door...

Reason for slimming.... i want to create an backup image just before live data enters the system so i want to get out as much unneeded stuff, purely for the size of an image. i know i will run off hdd but my images will be on sd card or an usb stick i store off site (basement storage in another building).

I might remove stuff that is needed later on... but then i will just have to add that  when time comes.

My thoughts on waiting starting up Emonhub are the simple ones that i wanted to be more than 100% sure we got the correct time before we start logging... given that i will not reboot this very often i would not mind waiting 1-5 mins after reboot before logging starts..

anyone have to go for an job interview... have loaded fresh image and created a google doc so i know what i have done and dont get lost so to speak, link is just for ref if i ask for more help and you want to know what i have done: https://docs.google.com/document/d/1iOM2_-0SlzrL6oO1bNJmoOx-B8EIwXAy5zZJVlqMlos/edit#

Finally came arround to install emoncms on my new pi, with new job starting at 5am there has not been much time during the day to do anything.

but i did manage to get a Stratum1 time server going on it, a voice chat server etc... look at google doc link above

now following this guide: https://github.com/emoncms/emoncms/blob/master/docs/LinuxInstall.md

will update this one as i get along

EDIT: Q... what is redis used for? 

getting along... now some more Q's:

1) will adding MysqlTimeSeries.php from https://github.com/emoncms/emoncms/tree/master/Modules/feed/engine give me mysql as a feedengine option?

2) how do i transfer data from one pi to the other? 

3) can i do 2) even if data formats are not the same or does it take some extra work?

think i can answer 1)... please confirm me:

got it from here: http://openenergymonitor.org/emon/node/5744

'Modules/input/process_model.php' needs to be edited (add 'ENGINE::MYSQL' to '$list[1] = array(_("Log to feed").....' ).

Modules/input/Views/input_view.php' the same change needs to be done.
Under '<span class="add-on feed-engine-lable">Feed engine: </span>', the new option for MYSQL needs to be added as follows '<option value=0 >MySQL TimeSeries (MYSQL)</option>'.

what i'm not quite sure of is if it should be option 1 or 0... mysql is the first on the list... and fiwa i know is no 6... so if i count 0...1...2 fiwa becomes no 6... so yess my logic says it has to be 0

so what i did was these 2 changes... transfered the mysql from the live pi to get dashboard etc over... 

now of course there was some bogus nodes in there and i took one of them just fo create a feed as test... yep the feed shows up in phpmyadmin... 2 colloums... time and date... so far so good right?

----------------------------

now i could just move the expansion board and create feeds to see it works.... but how do i get my old data converted? from phptimeseries to mysql.... can i ask the live pi upload to emoncms.org but spec my 2nd pi i'm building on? or will that just transfer the data in same format?

i found out that i need to recreate the MYI and frm files... any one knows how to do that? are the MYD files simply time and data pairs?

i know this is semi bumping but... if anyone have knowledge on how to convert the phptimeseries to real mysql then please let me know

EDIT:

could it be that its possible to export to csv on the old pi and import in to mysql on the new one?

only trouble i found is that i can only use log to feed with mysql... i cant use it if i want to do power to kwh to convert my watt's to kwh... i can do that with phptimeseries thou... is this just a file i need to change?

EDIT2: Figured that i could do an CSV export from the old pi and import the data on to the new pi, almost at the finish line... now i just need to be able to use mysql for the processing... most important to me is Power to kwh and accumulator

EDIT3: to overcome the issue that i cant use power to kwh i figured i could use the number of pulses from the electricity meter and just multiply it. my meter flashes 10000 for each kwh so i thought multiply the number of flashes by 0.0001. for some strange reason this does not work, the value comes out as a flat 0 and not 0.0007 for 7 flashes... how can that be?

If you are seeing "0.00" on the inputs or feeds pages the chances are the value is correct (0.0007) but is rounded when displayed. to test this create another feed "x1000test" an see if multiplying the "0.00" by 1000 is 0.00 or 0.70.

Paul

will do, but planned power out in 15 min

utility comp is doing maintaince on high tension part of grid

any idea why i cant use power to kwh and accumulator when i use mysql as feed engine?

Anti Bump EDIT: that worked... with 11 flashes between each reading i got a feed value of 1.10 when i multiplied by 1000.... now to check what mysql says about the 2 feeds....

BTW.... HUraaaaay email notification works :-D

EDIT2: yep... mysql says 0.0011 kwh every 4 sec... i guess i can live with that... but how precise is that compared to use power to kwh processin? better or worse?

EDIT3: csv export from old pi worked somewhat... on the old pi i enter emoncms and take my watt feed... i know i have data from 11 nov so i pick that as start date and yesterday as end date... but it complains about max size... i then do one month at a time but i get lines that look like this:

1417635073 902.00
1417635080 1 074.00
1417635086 1 071.00
1417635093 1 087.00
1417635098 918.00

also each of the 3 files i end up with end in this: 

<br />
<b>Fatal error</b>:  Maximum execution time of 30 seconds exceeded in <b>/var/www/emoncms/Modules/feed/engine/PHPTimeSeries.php</b> on line <b>248</b><br />

is this a fault?

I'm guessing the "comma separated variables" are being incorrectly read because of a "thousands separator".

hehe,,, the direct output from the emoncms cvs export, opened in notepad, also the error at the end of the files troubles me... are there other ways i could transfer the data maybe?

If you open the csv files in a spreadsheet eg excel you should be able to easily alter the files you have already downloaded. by concatenating columns B and C into a new column D, then copy and "special paste values" in column D, delete B & C before saving.

the problem is that the csv export is not complete...  it ends with 

<br />
<b>Fatal error</b>:  Maximum execution time of 30 seconds exceeded in <b>/var/www/emoncms/Modules/feed/engine/PHPTimeSeries.php</b> on line <b>248</b><br />

last thing i checked before bed time last night was how much data i actually got out... and even if i picked a full month i only got a few hours... 

the old database said 6mb and the csv was a bit below 200kb

EDIT: Fixed the data issue... i copied over the feed files from the old pi to the new one... then i took one of the frm and MYI files allready there since i opted to use MYSQL.. and then ran 
chown -R mysql:mysql on the 3 files... now the old feed shows up in my sql and i can export them from mysql and import them again... i guess there is a smarter way to tell it to move the data but well my thought will work at least

EDIT2: that did not work so well, but export to csv and import in to the new feed does work flawless, teh bigger the file the more timeouts thou... 

EDIT3: a way arround the timeout thing.. simply increase the idle time in phpmyadmin to 5 hours before a new login is needed, and processsion time to the same... one month worth of data can be done in about 3 hours if interval is 4 sec.

left is only to figure why i cant pick power to kwh and accumulator when i pick mysql. the first i use for convert watt to kwh but i can live with processing the flashes from the meter. 

but the last one i use to log my water usage.... have to test if i reliable can store the number of pulses there too

got it up and running... only 2 issues left.... 

still power to kwh and accumulator are not an option when i use mysql.... can i change that somehow?

anyone that can tell my why power to kwh and accumulator is not an option when i pick mysql as feed engine?

could i change a file or 2 to get them as options with mysql?

no one knows?