Community
OpenEnergyMonitor

OpenEnergyMonitor Community

Testing EmonCMS upgrade fails when connecting to the database

Hi.

I have an EmonCMS installation working ok since 2016.
At the beginning I made regular updates, but then I made some changes of my own to some visualizations and got tired of porting them to every new version.

Recently I added some solar panels and have been investing some tome on this topic again and decided it was time to update EmonCMS.

The main problem I’m having is the message “Can’t connect to database, please verify credentials/configuration in settings.ini” but if I use the command line with the same exact auth information it works correctly (the credentials are just for testing so I didn’t bother to hide them):

> mysql -u TESTECMS -pTESTEcms2021 -h localhost -D EmonCMS2
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
> 
> Welcome to the MariaDB monitor.  Commands end with ; or \g.
> Your MariaDB connection id is 12110276
> Server version: 5.5.56-MariaDB MariaDB Server
> 
> Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
> 
> MariaDB [EmonCMS2]> show tables;
> +--------------------+
> | Tables_in_EmonCMS2 |
> +--------------------+
> | app_config         |
> | dashboard          |
> | device             |
> | feed_172           |
> | feed_174           |
> | feed_176           |
> | feed_178           |
> | feed_180           |
> | feed_184           |
> | feed_193           |
> | feed_27            |
> | feed_28            |
> | feeds              |
> | input              |
> | multigraph         |
> | rememberme         |
> | schedule           |
> | users              |
> +--------------------+
> 18 rows in set (0.00 sec)
> 
> MariaDB [EmonCMS2]>


> [sql]
> server   = "localhost"
> database = "EmonCMS2"
> username = "TESTECMS"
> password = "TESTEcms2021"
> port     = 3306

Another thing is that the setting.ini is missing this option “‘phpfiwa’=>array(
‘datadir’ =>”, I can only find the option for phpfina and phptimeseries.

PS: I still have the original EmonCMS working in another virtual web folder and with other database.

Cheers.

Edit - formatted text - Moderator

Hi there.

I managed to update the database by creating a new one, a new user with the same credentials and then hot-switching to the old database. For my surprise the user keep logged in and I was able to update the database.
But, for sure there are some things wrong with the present version of EmonCMS.

  • What do I do with 49 “PHPFIWA (No longer supported)” feeds I have?
  • Why do I get “406 Not Acceptable - URI not acceptable. Graph cannot respond to “19”.
    Try another link from the menu.” for every feed I click, even non PHPFIWA ones?
  • Where are the dashboards?
  • The “$updatelogin = true;” does nothing and I didn’t found information about using it on settings.ini instead of settings.php

It seems I will keep my version from 2016 because the update doesn’t seem to be worth it.

Cheers.

Hello @cidirome

Is this a custom install on a home linux box? or an emonSD based system?

A couple of quick fixes:

  • Graph error: You need to install the graph module in emoncms/Modules
  • Dashboard error: You need to install the dashboard module in emoncms/Modules

If you are using linux have git installed and have emoncms installed in /var/www/emoncms the steps are:

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

Check that the database is up to date under the admin updates page.

Next you need to run this script to convert the phpfiwa data to phpfina, make sure that you keep a backup of the old phpfiwa folder. usefulscripts/phpfiwa_to_phpfina.php at master · emoncms/usefulscripts · GitHub

Hi.

Thank you for you reply.

Presently, this is not the same Linux system where I started testing the upgrade, but the problems were the same up to not being able to upgrade the database.
I have an EmonPi with very changed (little of it is original) firmware (and hardware) by me, but it sends the data to a linux server on the network.

I will try the modules and FIWA conversion when.I have time.

I was under the impression that FIWA was better, why was it deprecated?

Cheers.

It turns out averaging on the fly without the complexity and additional disk writes caused by phpfiwa was possible with the simpler Phpfina engine. The naming is now confusing really as Phpfina does actually support averaging… it just doesn’t store the averages to disk

Hi there.

Thank you for your reply.

I managed to install the graphs and dashboard modules, but the dashboard was not working at the beginning… turns out I had to run the Update database option (I though of it while I was already writing this).

Now, I’m not being able to convert the FIWA feeds:

/opt/remi/php74/root/bin/php ../usefulscripts/convertdata/phpfiwa_to_phpfina.php
---------------------------------------------------
PHPFIWA to PHPFINA conversion script
---------------------------------------------------
Is your setup a standard emonpi or emonbase? (y/n): n
Please enter root directory of your emoncms installation (i.e /var/www/emoncms): /var/www/emoncms/emoncms
There are 49 feeds to convert, would you like to continue? (y/n): y
14 Consumo EDP W
- Create a new feed or replace? (enter 1:new, 2:replace) 2
error opening phpfiwa meta file to read

Any idea of what the problem is?
When I first got this error I knew that some errors occurred while copying the files because I didn’t stop the web server on the running EmonCMS, then I stopped it to copy the files, but the result was the same. (There are 4Gb of FIWA files in my old server)

Cheers.

Hello @cidirome the issue may be a file permission issue. Can you run the script using sudo?

e.g:

sudo phpfiwa_to_phpfina.php

Hi.

The script is being already run as root, not need to sudo, I thought about that and even changed file permissions.

When I have time I will try to understand what the php script is doing to see where it is failling.

Cheers.

Hi.

I converted the FIWA to FINA in the old server with the old script from the 2016 installation successfully.

It took about 45 minutes to do the job (3.9Gb of fiwa files).

Tomorrow I will try to migrate all of it to the new server.

Cheers.

1 Like

Thanks for the update @cidirome great to hear that step worked!

Hi there.
I hit a stump again, I can’t understand what is happening now, some higher power doesn’t want me to upgrade.
Look at this picture, it is from my old server:


As you can see there aren’t any FIWA feeds anymore.
Then I copied the phpfina and phptimeseries folders to the new server (leaving the old fiwa folder behind), dumped the database from the old server and uploaded it to the new server (using phpmyadmin). I’m sure that the server is using the correct database, but this is what I get when opening the new server (after upgrading the database):

It seems to be using the database I used for testing a few days ago, and I don’t understand where is it getting the old data from (I even deleted other databases I had and renamed the correct one just to be sure)…

There must be something here I’m missing: is there any data stored elsewhere that could be left from the testing I made?

Update, just for testing:

  • I deleted the feeds table from the database and I can still see all the feed in the EmonCMS Page;
  • To double check, I changed some texts in the Multi-Graph table and the changes reflect in the EmonCMS;

As we say in Portuguese “Tenho a cabeça em águas de bacalhau!” - “My head is in codfish waters!”

Cheers.

Hi.
To avoid editing my last post, I found that all the data I encounter in a clean installation seems to come from the REDIS system.
I don’t know anything about REDIS, but It seems that I need to somehow clean its database (if that name is even correct) when the FINA files are changed.
While I was already writing this I thought about the solution… thinking… There should be an option in the admin section to reset the REDIS system … and brain-blink, I went to search for that idea and there it was:

It seems that now I will be able to upgrade.

Wish me luck.

Cheers.