Could not create new inputs after upgrade

Tommi · 25 April 2019 10:37

Hello,

I have used emoncms years already to log different kind data in my home.
When there was version 8.7 (if i remember right) i last time added new input in emoncms and after that i have updated new version in somepoint. Today i try to update in to 9.9.8 and it’s worked ok like previous versions but then i try to add new input on it…

Any example what is in input API help page not do anything. Those returning ok or success but nothing coming up in inputs list.
Direct curl commands:

    curl 'hxttp://10.10.10.1/emoncms/input/post.json?node=2&json={Return%20temperature%20heat%20pump:44.5}&apikey=XXX'

This work (updating values) as there is already input named “Return temperature heat pump”
but following gives return code “ok” but nothing coming up in input list.

    curl 'hxttp://10.10.10.1/emoncms/input/post.json?node=4&json={Return%20temperature%20heat%20pump:44.5}&apikey=XXX'
    curl 'hxttp://10.10.10.1/emoncms/input/post.json?node=2&json={Return%20temperature%20heat%20pump%20TEST:44.5}&apikey=XXX'

I also downgrade wmoncms to 9.9.7 but it’s working just like newr one.
In sql seems that emoncms3 user right are ok

GRANT ALL PRIVILEGES ON *.* TO 'emoncms3'@'%' IDENTIFIED BY PASSWORD 'XXX' WITH GRANT OPTION

Any suggestion where to look next?

Ps. New user can put only 2 links so i need to edit links.

borpin · 25 April 2019 10:47

Hi @Tommi, I have bumped your trust level so that should be OK now.

Did you just upgrade the existing installation? On the input page, go to API Help (top right) and then click on one of the example links.

It should create an input. Does it?

Tommi · 25 April 2019 11:51

No, it’s not create anything:

http://10.10.10.1/emoncms/input/post?node=emontx&fulljson={"power1":100,"power2":200,"power3":300}

returns: {“success”: true}
And also other examples in that page work same way

But in input page there is not nothing new as you can see from attached picture:

Tommi · 25 April 2019 11:59

borpin · 25 April 2019 12:31

Did you just upgrade an existing system?

How did you do the upgrade?

Why is the database user ‘emoncms3’? That command is to grant permissions not to check them (I think).

What do the first few lines of /var/www/emoncms/settings.php give for the database settings?

Tommi · 25 April 2019 13:15

Yes, I assume that in last 2 year i have only updated emoncms when stable version has released. But i haven’t added any new input on it so i cannot say when that “adding new input” feature stops working.

git checkout tags/9.9.7

and then run database scan from admin pages.

I use in same sql More detailed rights:
MariaDB [(none)]> ±---------------------- | GRANTEE ±---------------------- | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ | ‘emoncms3’@‘%’ database for multiple different program and for me it’s easyer to keep same user name to application what i use as database name’s.
select * from information_schema.user_privileges;
-----±--------------±------------------------±-------------+
| TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
-----±--------------±------------------------±-------------+
| def | SELECT | YES |
| def | INSERT | YES |
| def | UPDATE | YES |
| def | DELETE | YES |
| def | CREATE | YES |
| def | DROP | YES |
| def | RELOAD | YES |
| def | SHUTDOWN | YES |
| def | PROCESS | YES |
| def | FILE | YES |
| def | REFERENCES | YES |
| def | INDEX | YES |
| def | ALTER | YES |
| def | SHOW DATABASES | YES |
| def | SUPER | YES |
| def | CREATE TEMPORARY TABLES | YES |
| def | LOCK TABLES | YES |
| def | EXECUTE | YES |
| def | REPLICATION SLAVE | YES |
| def | REPLICATION CLIENT | YES |
| def | CREATE VIEW | YES |
| def | SHOW VIEW | YES |
| def | CREATE ROUTINE | YES |
| def | ALTER ROUTINE | YES |
| def | CREATE USER | YES |
| def | EVENT | YES |
| def | TRIGGER | YES |
| def | CREATE TABLESPACE | YES |
| def | DELETE VERSIONING ROWS | YES |

Database running on it’s own container so that reason i use @’%’.

[root@prelude emoncms]# cat settings.php
<?php

// Docker Emoncms Settings

//1 #### Mysql database settings
    $server   = $_ENV["MYSQL_HOST"];
    $database = $_ENV["MYSQL_DATABASE"];
    $username = $_ENV["MYSQL_USER"];
    $password = $_ENV["MYSQL_PASSWORD"];
    $port     = "3306";

and:
[root@prelude emoncms-docker]# cat default.docker-env
# Default docker enviroment variables, change in production enviroment

# MySQL database
MYSQL_HOST=db
MYSQL_PORT=3306
MYSQL_DATABASE=emoncms3
MYSQL_USER=emoncms3
MYSQL_PASSWORD=XXX

I also checked is there any new input in DB but there was not any trace of any new input’s what i have tested.

borpin · 25 April 2019 13:22

That may be your problem. Where is this installed? On a Pi, a server, a PC?

[edit]Is it running in it’s own container?

Tommi · 25 April 2019 14:59

Container running X86_64 linux server (fedora) host server. Container is build “FROM php:7.0-apache” with needed dependencies to run emoncms & some other services.
Emoncms part (as well whole ./html directory) is mounted out from docker to “./emoncms-docker/apache_php/html/emoncms” and in this directory i have given that “git checkout tags/9.9.7” command.

An seems that i have right tag.
[root@prelude emoncms]# cat version.txt
9.9.7

borpin · 25 April 2019 15:47

Ah those pesky assumptions…

We are not currently as precise as that in terms of the tags and version numbers The stable branch is the one to checkout before doing anything.

There is a script in the usefulscripts repo to update emoncms by git (update_emoncms.sh). This does assume you are on the right branch (stable).

Try that as often you need to update some of the other installed non-core Modules as well. It works off the stable branch which is the latest version. Hopefully you will be able to login, if you can, go to admin and do a database update. If not you will need to do a manual database update using this script emoncmsdbupdate.php. I’m not exactly sure how you will run that under a docker though.

Tommi · 25 April 2019 19:27

Thank you borpin, let’s see how it’s working:

I run this a second time as i forget to change in to master branch in first time.
root@6cafd902eee6:/var/www/html/emoncms/usefulscripts# ./update_emoncms.sh

Update emoncms....
Already up-to-date.

Update emoncms/app....
Already up-to-date.
On branch master
Your branch is up-to-date with 'origin/master'.
nothing to commit, working tree clean
1.2.1-11-g4dd7450

Update emoncms/dashboard....
Already up-to-date.
On branch master
Your branch is up-to-date with 'origin/master'.
nothing to commit, working tree clean
1.3.3-14-g554eb38

Update emoncms/graph....
Already up-to-date.
On branch master
Your branch is up-to-date with 'origin/master'.
nothing to commit, working tree clean
1.2.3-16-gce6dd74

Restarting docker:
[root@prelude emoncms-docker]# docker-compose restart web

Version shoving on bottom of page 9.9.9-beta

And database update:
2019-04-25%2020_50_17-

and then first example in input api page: Running it → getting succeed message but still nothing on inputs page.

And the i read that you said that i need to use stable branch. so let’s jump to beginning…

[root@prelude emoncms-docker]# docker exec -it emoncms-docker_web_1 /bin/bash
root@6cafd902eee6:/var/www/html# cd emoncms/
root@6cafd902eee6:/var/www/html/emoncms# git checkout stable
Branch stable set up to track remote branch stable from origin.
Switched to a new branch 'stable'
root@6cafd902eee6:/var/www/html/emoncms# git branch
  master
  show
* stable
root@6cafd902eee6:/var/www/html/emoncms# ./usefulscripts/update_emoncms.sh

Update emoncms....
Already up-to-date.

Update emoncms/app....
Already up-to-date.
On branch master
Your branch is up-to-date with 'origin/master'.
nothing to commit, working tree clean
1.2.1-11-g4dd7450

Update emoncms/dashboard....
Already up-to-date.
On branch master
Your branch is up-to-date with 'origin/master'.
nothing to commit, working tree clean
1.3.3-14-g554eb38

Update emoncms/graph....
Already up-to-date.
On branch master
Your branch is up-to-date with 'origin/master'.
nothing to commit, working tree clean
1.2.3-16-gce6dd74

Restarting…and version in bottom of page is: 9.9.8 …doing db update … testing example and still same results. Input page do not show new inputs.

I even made a clean installation with a commands:
git clone GitHub - emoncms/emoncms: Web-app for processing, logging and visualising energy, temperature and other environmental data /var/www/html
git clone GitHub - emoncms/dashboard: dashboard module for emoncms /var/www/html/emoncms/Modules/dashboard
git clone GitHub - emoncms/graph: Versatile graphing module for emoncms /var/www/html/emoncms/Modules/graph
git clone GitHub - emoncms/app: Emoncms App module: application specific dashboards: includes myelectric, mysolarpv, myheatpump and solar + wind app. /var/www/html/emoncms/Modules/app
git clone GitHub - emoncms/usefulscripts: Some useful scripts for administering Emoncms accounts /var/www/html/emoncms/usefulscripts
+ copy and editing default.setting.php
With same results.

My logic says that problem must be in DB side, i try to look it if i found something.

Tommi · 25 April 2019 19:33

Just one comment. I can add and delete tables in mysql with using remote connection and emoncms3 account.

And when i drop emoncms3 database and restart everything then examples and my own inputs works fine. So DB is corrupted someway.
Now i need to figure out how i can save my data what i have gathered over years… any hint?

borpin · 25 April 2019 19:58

Is the data in the sql database or in the data files? As this was an old install, it could be the former. What are these set to in your settings.php file

        'phpfiwa'=>array(
            'datadir' => '/home/pi/data/phpfiwa/'
        ),
        'phpfina'=>array(
            'datadir' => '/home/pi/data/phpfina/'
        ),
        'phptimeseries'=>array(
            'datadir' => '/home/pi/data/phptimeseries/'

If you used phpfina, the data should be there.

You could try the export script emoncms-export.sh which should export everything and see how that works. Possible that exporting and reimporting will clear the corruption.

Anything in the mysql logs?

Tommi · 25 April 2019 20:43

Sadly in this case but i hace all data in mysql tables.

I read emoncms-export.sh and emoncms-import.sh scripts thru and seems that those just make dump/restore from whole database so it could not help in this case as i have checked that mariadb tables are not corrupted. (+ if i read emoncms logs right then it not even try to send anything in to db)

And nothing in mysql logs (i haven’t tried debug logging)

I hope that there is some other way than just put system up and running again with all inputs and then “just” copy data in db from old feeds to new installation. It could be a but painful operation as i have 108 active feeds there.

Anyway, it’s time to do something else and look this again tomorrow with a fresh eyes and brains. Thank you from your help borpin.

borpin · 25 April 2019 20:45

So there is also this mysql backup script mysql_backup_emoncms.sh. There was a thread about restoring this data recently if you search.