Archived Forum

Improving on this a bit I've wrapped up the above 1) export and 2) import steps as two scripts that are available here:

https://github.com/emoncms/usefulscripts/tree/master/emonpi-migrate

The emonpi-export.sh script creates a compressed archive with the mysql dump, phpfina, phptimeseries, emonhub.conf and emoncms.conf. It also stops the feedwriter before starting and restarts it again at the end to ensure the data files are copied while not being written to.

The emonpi-import.sh script extracts the files to their correct locations and imports the mysql dump it stops all the services before starting, performs a redis-cli flush and restarts the services again at the end.

To use these:

1. SSH into existing emonpi/emonbase
2. Enable write mode: rpi-rw
3. git clone https://github.com/emoncms/usefulscripts.git
4. sh usefulscripts/emonpi-migrate/emonpi-export.sh

Copy the resultant backup.tar.gz which will be created in /home/pi/data to your computer.

Create the new SD card with the new image. Either copy the backup.tar.gz manually onto the data partition prior to powering up the pi with an sd card reader or copy it over using ssh then:

1. SSH into emonpi/emonbase with new image
2. Enable write mode: rpi-rw
3. git clone https://github.com/emoncms/usefulscripts.git
4. sh usefulscripts/emonpi-migrate/emonpi-import.sh

These sound like a great addition, how involved would it be to have the "export" script included in emoncms, in php so that it could be triggered from  button in admin or better still via an api? that would make a great back-up tool, especially if cron could use the api say once a week or so.

And if an a sql dump be done for a single account by user or apikey it would make the emoncms.org to local transition so much easier too

Paul

Hello Paul, same thoughts here, I've added it in ran in much the same way as the emonpi updater.

To get this working you need the usefulscripts repository in the home directory: /home/pi

and the cron entry:

 * * * * * /home/pi/usefulscripts/emonpi-migrate/emonpi-export-wrapper.sh >> /home/pi/data/emonpibackup.log 2>&1

Is there a technical spec for the microSD card for the emonPi (Raspberry Pi 2 model B??)?  I seem to remember a thread recommendation for a high endurance (1,000,000 writes) SD card but I cannot locate it.  What about speed class rating?  or microSD, microSDHC, microSDXC?

And is there a "how to" for formatting the new SD card for the emonPi?  This is how the current emonPi SD card is setup.

​pi@emonpi ~ $ df -Th
Filesystem      Type      Size  Used Avail Use% Mounted on
rootfs          rootfs    2.7G  2.4G  121M  96% /
/dev/root       ext4      2.7G  2.4G  121M  96% /
devtmpfs        devtmpfs  460M     0  460M   0% /dev
tmpfs           tmpfs      93M  272K   93M   1% /run
tmpfs           tmpfs     5.0M     0  5.0M   0% /run/lock
tmpfs           tmpfs     186M     0  186M   0% /run/shm
tmpfs           tmpfs      30M   44K   30M   1% /tmp
tmpfs           tmpfs      30M   10M   21M  34% /var/log
/dev/mmcblk0p1  vfat       56M   19M   37M  34% /boot
/dev/mmcblk0p3  ext2      788M  117M  632M  16% /home/pi/data

It looks like there is a boot partition of type vfat, a /home/pi/data partition of ext2 and everything else on type ext4. How are all of these partitioned & formatted and then emonSD-22Dec2015.img.zip image added to the new SD card?

I would recommend using the new pre-built SD card image. Flashing  the image (.img) to the SD card will set up all three partitions. Any (4GB min) card will work. We don't use a particularly high spec card since mostly the ratings are for speed of writing and that is not really an issue for us. We just go with a standard good quality branded SD card. The partition setup of the new card is pretty much the same as previous one. The partition setup is listed on the new image forum thread post. 

Ugh!  I need a few hints!  I flashed the emonSD-22Dec2015.img.zip image to a new 8 GB SD card.  And I used the above emonPi export and import tool for backup and migration.  All seemed to be OK except I have no Nodes data and no updates.  And there is no info in the emoncms.log.  I restarted the sudo service emoncms-nodes-service restart (and a few other services) and did a paper-clip restart of the emonPi.  The emonhub.log looks like this:

2015-12-24 05:08:28,340 INFO     MQTT       Unexpected disconnection
2015-12-24 05:08:28,466 INFO     MQTT       Connecting to MQTT Server
2015-12-24 05:08:28,471 WARNING  MQTT       Connection refused - not authorised
2015-12-24 05:08:28,472 DEBUG    MQTT       CONACK => Return code: 5
2015-12-24 05:08:28,473 INFO     MQTT       Unexpected disconnection
2015-12-24 05:08:28,574 INFO     MQTT       Connecting to MQTT Server
2015-12-24 05:08:28,579 WARNING  MQTT       Connection refused - not authorised

​How do I troubleshoot this?

Ah, I see what's happened. You have done everything correct. 

What's happened is the migration script has copied your emonhub.conf config file. This is the file you can edit in the EmonHub tab of local Emoncms. The new image has MQTT authentication turned on by default. You need to  add mqtt_user and mqtt_passwd into the file see: https://github.com/openenergymonitor/emonhub/blob/emon-pi/conf/emonhub.conf#L69

If you have not made any changes to the emonhub.conf file, you could just copy over the default:

cp /home/pi/emonhub/emonhub.conf /home/pi/data/emonhub.conf

I've updated the emonpi-import script to save the emonhub.conf from the backup to old.emonhub.conf and then use the clean emonhub.conf as Glyn suggested as default.

It works!  Thank you!

In the copy command cp /home/pi/emonhub/emonhub.conf /home/pi/data/emonhub.conf there is no emonhub.conf file located in the /home/pi/emonhub directory.  So I grabbed the file you referenced at https://github.com/openenergymonitor/emonhub/blob/emon-pi/conf/emonhub.conf and copied it to the /home/pi/data directory.  That works and now I just need to add in my changes.

A few more issues:

1)  I cannot save any changes to the emonhub.conf via the EmonHub Config Editor.  I can edit the emoncms.log file at /home/pi/data/emoncms.log and all works OK.

2)  There is no information in the emoncms.log.  I am not sure what the permissions should be...

pi@emonpi:/var/log$ ls -al emoncms.log

-rw-rw-rw- 1 root root 0 Dec 24 16:17 emoncms.log

3)  the Feeds webpage (http://192.168.40.230/emoncms/feed/list) is always blank.  it just displays this:

4)  the Dashboards are all blank (except for one). I cleared out the browser cache and tried a few different browsers. The MyElectric app looks A-OK.  I read thru Paul's **Dashboards missing**, including its link, but it did not seem to help.

How do I troubleshoot these items?

I read thru Paul's **Dashboards missing**, including its link, but it did not seem to help.

My post only applies to installations or updates that were made via git. From reading the above, I believe that you installed via an image file.

Paul

yes.  The "emonSD-22Dec2015.img.zip" image file.

Could it be related to this post?

The dashboard module was removed on the 18th Dec, and I see that your SD card image is dated 22 Dec. I'm just wondering if the image file was rebuilt including those changes??

Are you able to trigger a software update from the emonPi, as that bug was sorted earlier today.

Paul

Thanks Jon for the spot, I've updated the script to point to the right place for emonhub.conf

"Are you able to trigger a software update from the emonPi, as that bug was sorted earlier today."

Yes.  I did a emonpi update at 16:43:25 GMT today and it looks like it found a change:

git pull /var/www/emoncms/Modules/dashboard
From https://github.com/emoncms/dashboard
   b321f20..9d5188f  master     -> origin/master
Updating b321f20..9d5188f
Fast-forward
 dashboard_model.php | 6 ++++--
 1 file changed, 4 insertions(+), 2 deletions(-)

I'll try the http://SERVERIP/emoncms/admin/db?apply=true and see what happens.

I typed in http://192.168.40.230/emoncms/admin/db?apply=true and that did not change anything.

Some things started working and some things I'm not sure how to troubleshoot. The big issues are:

1. There is no information in the emoncms.log  it is completely blank and at zero bytes.
2. the Dashboards are all blank (except for one). I cleared out the browser cache and tried a few different browsers.

I read something about a dashboard module being removed in the current version...  is this something I need to reinstall?

I cannot save any changes to the emonhub.conf via the EmonHub Config Editor.  I can edit the emoncms.log file at /home/pi/data/emoncms.log.  So there is a work-around.

Luckily the Feeds webpage (http://192.168.40.230/emoncms/feed/list) started displaying the correct info.  I am not sure if it was because of the http://192.168.40.230/emoncms/admin/db?apply=true command or if the Feeds had to go thru an re-calc something.  Either way it seemed to take a few hours to work.

Hello Jon, yes the dashboard module needs to be reinstalled. To reinstall, run this once you've connected to the pi via SSH. See http://openenergymonitor.org/tools/realescapestring The latest update to emoncms makes a couple of changes to the emoncms login implementation and you may find emoncms presenting you with the error "Incorrect password, if you're sure it's correct try clearing your browser cache." If you're using passwords with special characters, the way emoncms now handles these has changed. I have created a small tool that applies the mysql real escape string modification, that has now been removed from emoncms, so that it's possible to login again; after which you can change your password in emoncms. The tool is here and runs locally in your browser using javascript, and so is safe, nothing is sent to a remote server. You can check it for yourself with a test string and the browser's network tab.

Note: The reason for this change is that using mysql real escape string is not best practice and is being deprecated and removed from php in v7.0.0. Using prepared statements is recommended instead, which is part of the reason for this change now. It also helps ensure that the string length is not expanded beyond the maximum set.

If the above does not solve the problem you may also need to update your emoncms database. To do this without being logged in, add the line

$updatelogin = true; to settings.php

Then run:

http://localhost/emoncms/admin/db?apply=true"

cd /var/www/emoncms/Modules && git clone https://github.com/emoncms/dashboard.git

To make emonhub.conf editable, try:

sudo chown pi:www-data /home/pi/data/emonhub.conf
sudo chmod 664 /home/pi/data/emonhub.conf

The dashboards are installed (from the new modularised dashboard git repo) on the 22nd Dec image. I migrated over my home emonpi to the 22nd Dec image using the migrate tool and my dashboards all migrated over and seem to work fine so far. Is the issue your dashboards did not migrate or is it that you cannot create a new dashboard?

Emoncms log now only shows warning messages, if you don't have any messages in the log than that means you have no errors! Emoncms log level can be changed in /var/www/emoncms/settings.php

Trystan - I'm not sure I understand the "mysql real escape string" or what I should be entering to that webpage.   We can set this aside for now since I know all of my IDs & passwords an my IDs & passwords (and other names are all fairly simple).  Also the URL above looks like this: 

http://The%20latest%20update%20to%20emoncms%20makes%20a%20couple%20of%20...  

instead of of this: 

http://openenergymonitor.org/tools/realescapestring 

I did try the http://localhost/emoncms/admin/db?apply=true yesterday and I am not sure if it helped on not.

Glyn - Only one Dashboard migrated (a simple one). 

None of the MultiGraphs made the migration.  They all look like this:

Yes, I can create new Dashboards with new MultiGraphs.  I cannot use or modify and use old MultiGraphs.

I just ran the command " sudo service mysql status" and came across these error messages: 

pi@emonpi:/var/log$ sudo service mysql status
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: active (running) since Thu 2015-12-24 16:17:20 GMT; 2 days ago
  Process: 486 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysql.service
           ├─ 730 /bin/sh /usr/bin/mysqld_safe
           ├─1156 /usr/sbin/mysqld --basedir=/usr --datadir=/home/pi/data/mysql --plugin-dir=/usr/...
           └─1157 logger -t mysqld -p daemon.error

Dec 24 16:17:20 emonpi mysql[486]: Starting MySQL database server: mysqld . . ..
Dec 24 16:17:20 emonpi mysql[486]: Checking for tables which need an upgrade, are corrupt or were
Dec 24 16:17:20 emonpi mysql[486]: not closed cleanly..
Dec 24 16:17:20 emonpi systemd[1]: Started LSB: Start and stop the mysql database server daemon.
Dec 24 16:22:56 emonpi mysqld[1157]: 151224 16:22:56 [ERROR] /usr/sbin/mysqld: Table './emoncm...ired
Dec 24 16:22:56 emonpi mysqld[1157]: 151224 16:22:56 [Warning] Checking table:   './emoncms/da...ard'
Dec 24 16:22:56 emonpi mysqld[1157]: 151224 16:22:56 [ERROR] /usr/sbin/mysqld: Table './emoncm...ired
Dec 24 16:22:56 emonpi mysqld[1157]: 151224 16:22:56 [Warning] Checking table:   './emoncms/re...rme'
Dec 24 16:22:56 emonpi mysqld[1157]: 151224 16:22:56 [ERROR] /usr/sbin/mysqld: Table './emoncm...ired
Dec 24 16:22:56 emonpi mysqld[1157]: 151224 16:22:56 [Warning] Checking table:   './emoncms/users'
Hint: Some lines were ellipsized, use -l to show in full.
pi@emonpi:/var/log$

Could this be the Dashboard issues?

v9 introduced a change in the feed naming protocol, breaking the links between the feeds and the multigraphs.

To deal with this, go to 'Extra' > 'Visualisation' > 'Multigraph', and for each of your old multigraphs, reselect the feeds again, copying the format of the old feeds. Then delete the old feed entries before saving the multigraph.

The other links between Multigraphs & Dashboards should still be there.

Could the MYSQL errors above be due to being unable to locate the multigraph links as above?

Paul

Paul - I've updated three MultiGraphs and all three have started working A-OK.  Thank you!

I cannot answer the last line: "Could the MYSQL errors above be due to being unable to locate the multigraph links as above?"  Hopefully that was directed to T & G.

No, I was suggesting, update the multigraphs, then retry

$ sudo service mysql status

...and see if there are any current errors showing.

Paul

Paul - I am still getting some errors...

pi@emonpi:~$ sudo service mysql status
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: active (running) since Sat 2015-12-26 22:17:19 GMT; 2h 28min ago
  Process: 480 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysql.service
           ├─ 731 /bin/sh /usr/bin/mysqld_safe
           ├─1155 /usr/sbin/mysqld --basedir=/usr --datadir=/home/pi/data/mysql --plugin-dir=/usr/lib/mysql/plugin --pid-fi...
           └─1156 logger -t mysqld -p daemon.error

Dec 26 22:17:19 emonpi mysql[480]: Checking for tables which need an upgrade, are corrupt or were
Dec 26 22:17:19 emonpi mysql[480]: not closed cleanly..
Dec 26 22:17:19 emonpi systemd[1]: Started LSB: Start and stop the mysql database server daemon.
Dec 26 22:48:07 emonpi /etc/mysql/debian-start[1292]: Checking for insecure root accounts.
Dec 26 22:48:08 emonpi mysqld[1156]: 151226 22:48:08 [ERROR] /usr/sbin/mysqld: Table './emoncms/dashboard' is marked ...paired
Dec 26 22:48:08 emonpi mysqld[1156]: 151226 22:48:08 [Warning] Checking table:   './emoncms/dashboard'
Dec 26 22:48:08 emonpi mysqld[1156]: 151226 22:48:08 [ERROR] /usr/sbin/mysqld: Table './emoncms/multigraph' is marked...paired
Dec 26 22:48:08 emonpi mysqld[1156]: 151226 22:48:08 [Warning] Checking table:   './emoncms/multigraph'
Dec 26 22:48:08 emonpi mysqld[1156]: 151226 22:48:08 [ERROR] /usr/sbin/mysqld: Table './emoncms/rememberme' is marked...paired
Dec 26 22:48:08 emonpi mysqld[1156]: 151226 22:48:08 [Warning] Checking table:   './emoncms/rememberme'
Hint: Some lines were ellipsized, use -l to show in full.
pi@emonpi:~$ 

MYSQL error 1156 means 'MySQL error code 1156 (ER_NET_PACKETS_OUT_OF_ORDER): Got packets out of order'.

This could still be related to the password issue, can you confirm if you are currently using any special characters in your passwords, such as $ % ^ ?

It's unfortunate that the error is ellipsized as we can't see the full error text. Can you retry, with the SSH window maximised and run with the -l suffix.

sudo service mysql status -l

Paul

Paul - Concerning passwords: I can confirm no special characters.  I am still using the default passwords for everything and my user password is similar to a simple "abc1".  ( I plan to change them once I get things working a little better)

I ran the "sudo service mysql status -l" command and it produced no new info.  The output looks the same as above. I read thru /etc/init.d/mysql file and I am guessing the bash script will only react to these options "start, stop, restart, reload, force-reload, status".

One interesting thing about the error you mentioned "MYSQL error 1156 means 'MySQL error code 1156 (ER_NET_PACKETS_OUT_OF_ORDER): Got packets out of order'."  The emonPi seems to "lockup" fairly easily and I can only restart it by unplugging the power which I hate to do. It has been this way since day one with the Dec 22 image.  A paper-clip shutdown will not work.

After a cold startup if I push the LCD momentary switch more that a few times then the LCD locks up. So I can get to the P1: watts / P2: watts screen and all looks A-OK.  One more push and the screen freezes.

I wonder if I corrupted the mySQL DB?

I decided to start over with a fresh image, SD Card, etc. and I am still getting some odd errors (I understand this is a beta).  Here is one of the items found...

I noticed the log times are in UTC and are no longer my local time of CST.  I set the Timezone on the My Account webpage from UTC to America/Chicago. But that did not change anything.  I went looking for the raspi-config command to change the time zone but I could not locate the command.  Did this change?

Presumably youre using Jessie OS?

If so, as always it's:

sudo raspi-config

​But then you have posted a screenshot of raspi-config timezone setup, so I don't understand your question.

Paul

I am using what is included in the emonSD-22Dec2015.img.zip image.  I think that is Jessie.

Sorry.  My bad for not explaining the pic better.  The pic is an old picture from emonPi / emonBase Setup webpage.

I typed in the sudo raspi-config and I get this response: "sudo: raspi-config: command not found"

Aah right, I believe that the image file is a cut down version of Jessie called Minibian, which I haven't used yet, but it's maybe worth googling it to find out about Minibian & timezones. Otherwise its a question for Glyn.

Paul

Out of interest, I found this link.

Paul

Paul - Thank you!  The dpkg-reconfigure tzdata did the trick!!

As a side note - I am not sure the dpkg-reconfigure locales quite works.  Even though I picked en_US.UTF-8 UTF-8 in the first screen and en.US.UTF-8 in the second screen I would always end up with this response:

Generating locales (this might take a while)...
  en_GB.UTF-8... done
  en_US.UTF-8... done
Generation complete.

Hopefully the time zone change doesn't mess up data storage.

On to the next unusual issue.  The emonPi LCD display will freeze after pushing the LCD momentary switch a few times.  I get to the Power 1: 1000W / Power 2: 2000W and all works OK & I can see the power change as the power changes within my house.  But one more switch push and the LCD is locked.  The sudo service emonPiLCD status states this:

pi@emonpi:~$ sudo service emonPiLCD status
● emonPiLCD.service - LSB: emonPi LCD controller
   Loaded: loaded (/etc/init.d/emonPiLCD)
   Active: active (exited) since Tue 2015-12-22 07:32:54 CST; 6 days ago
  Process: 400 ExecStart=/etc/init.d/emonPiLCD start (code=exited, status=0/SUCCESS)

Dec 22 07:32:53 emonpi systemd[1]: Starting LSB: emonPi LCD controller...
Dec 22 07:32:53 emonpi emonPiLCD[400]: Starting system emonPiLCD daemon:.
Dec 22 07:32:54 emonpi systemd[1]: Started LSB: emonPi LCD controller.

I do not know why the dates and times are different in the above.  And the emonPiLCD log looks like this:

2015-12-28 19:21:35,344 INFO Backlight off
2015-12-28 19:21:35,446 INFO Backlight off
2015-12-28 19:21:35,532 INFO lcd button press 0
2015-12-28 19:21:35,548 INFO Mode button pressed
2015-12-28 19:21:35,550 INFO Page: 0
2015-12-28 19:21:35,551 INFO Data: []
2015-12-28 19:21:42,567 INFO lcd button press 0
2015-12-28 19:21:42,648 INFO Mode button pressed
2015-12-28 19:21:42,649 INFO Page: 1
2015-12-28 19:21:42,650 INFO Data: []
2015-12-28 19:21:48,995 INFO lcd button press 0
2015-12-28 19:21:51,465 INFO lcd button press 0
2015-12-28 19:21:51,535 INFO Mode button pressed
2015-12-28 19:21:51,536 INFO Page: 2
2015-12-28 19:21:51,537 INFO Data: []
2015-12-28 19:21:58,041 INFO lcd button press 0
2015-12-28 19:21:58,115 INFO Mode button pressed
2015-12-28 19:21:58,116 INFO Page: 3
2015-12-28 19:21:58,117 INFO Data: ['2009', '2601', '4610', '246.93', '18.8', '0', '0', '0', '0', '0', '395']
2015-12-28 19:22:03,830 INFO lcd button press 0
2015-12-28 19:22:04,848 INFO lcd button press 0
2015-12-28 19:22:06,000 INFO lcd button press 0

and doing a sudo service emonPiLCD restart causes this in the log:

2015-12-28 19:29:16,630 INFO emonPiLCD Start
2015-12-28 19:29:16,734 INFO I2C LCD Detected on 0x27
2015-12-28 19:29:18,049 INFO Connecting to MQTT Server

And now I am good for three more LCD switch pushes.

Nice work. Yes as you have found raspi-config is not included. How about using tzselect? Seems a bit more user friendly guiding through the process.  

pi@emonpi:~$ tzselect
Please identify a location so that time zone rules can be set correctly.
Please select a continent, ocean, "coord", or "TZ".
 1) Africa
 2) Americas
 3) Antarctica
 4) Arctic Ocean
 5) Asia
 6) Atlantic Ocean
 7) Australia
 8) Europe
 9) Indian Ocean
10) Pacific Ocean
11) coord - I want to use geographical coordinates.
12) TZ - I want to specify the time zone using the Posix TZ format.
#? 8
Please select a country whose clocks agree with yours.
 1) Åland Islands         18) Greece                35) Norway
 2) Albania               19) Guernsey              36) Poland
 3) Andorra               20) Hungary               37) Portugal
 4) Austria               21) Ireland               38) Romania
 5) Belarus               22) Isle of Man           39) Russia
 6) Belgium               23) Italy                 40) San Marino
 7) Bosnia & Herzegovina  24) Jersey                41) Serbia
 8) Britain (UK)          25) Latvia                42) Slovakia
 9) Bulgaria              26) Liechtenstein         43) Slovenia
10) Croatia               27) Lithuania             44) Spain
11) Czech Republic        28) Luxembourg            45) Sweden
12) Denmark               29) Macedonia             46) Switzerland
13) Estonia               30) Malta                 47) Turkey
14) Finland               31) Moldova               48) Ukraine
15) France                32) Monaco                49) Vatican City
16) Germany               33) Montenegro
17) Gibraltar             34) Netherlands
#? 8

The following information has been given:

        Britain (UK)

Therefore TZ='Europe/London' will be used.
Local time is now:      Tue Dec 29 09:10:39 GMT 2015.
Universal Time is now:  Tue Dec 29 09:10:39 UTC 2015.
Is the above information OK?
1) Yes
2) No
#? 1

You can make this change permanent for yourself by appending the line
        TZ='Europe/London'; export TZ
to the file '.profile' in your home directory; then log out and log in again.

Here is that TZ value again, this time on standard output so that you
can use the /usr/bin/tzselect command in shell scripts:
Europe/London

You can add the line to the profile file using text editor $ nano .profile 

Glyn - to me the dpkg-reconfigure tzdata seemed easier to use.

​rpi-rw
sudo dpkg-reconfigure tzdata
    (now pick Geographic area, click OK, select the city or region and click OK)
rpi-ro

The part I did not understand was the reference to the .profile file.  Does the Time Zone info disappear on reboot / reset / restart if "TZ='Europe/London'; export TZ​" does not appear in the .profile file?

After running tzselect the time zone will disappear on reboot unless TZ= is not added to .profile file. Does dpkg-reconfigure tzdata add this line automatically? 

Hi Glyn, "dpkg-reconfigure tzdata" is the command used by raspi-config behind the scenes, it doesn't add anything to ~/.profile but it is a persistent change. I think this method is the better approach as I believe it effects all users rather than just pi.

Paul