Home > Linux, Testing, Ubuntu > How to Install ThingSpeak in Ubuntu 16.04

How to Install ThingSpeak in Ubuntu 16.04

Last week-end I installed ESPurna open source firmware with MQTT server on Sonoff POW WiFi switch, and the next step is find a way to draw power consumption charts in some web based interface. We could do this in the IoT cloud with services like Xively or ThingSpeak, but since one of the goals of replacing the default firmware was not to rely on a proprietary cloud based solution, I decided to find a way to draw those chart in a local server, and it so happens that ThingSpeak is also open source with the code available on Github. Hardware platforms like NanoPi NEO / NEO Air, or Orange Pi Zero boards appear to be particularly well suited for the task of running an MQTT broker and Thingspeak, but at first I wanted to install ThingSpeak in my own Ubuntu 16.04 computer to have a try.

Click to Enlarge

Click to Enlarge

As you can see from the screenshot above I manage to do it, but it requires a bit more efforts than expected, as the project has not been updated since 2015, and does not work out of the box with the latest operating system.

I used various resources on the web including the instructions on Github, as well as this ThingSpeak script for Ubuntu 14.04, and a few other resources.

First we have to make sure Ubuntu 16.04 is fully upgraded:


Ubuntu 16.04 comes with Ruby 2.3, but we need the older Ruby 2.1.0 version for ThingSpeak, so let’s uninstall ruby to avoid conflicts:


Now we can install dependencies, Ruby 2.1.0, and Bundle:


Once this is done, we can get ThingSpeak source code and install it:


This looked successful so I moved on to database configuration:


It’s recommended to change the username and password in config/database.yml for test, development and production databases with your own for security purpose. Once it’s done, let’s try to create the databases:


Sadly it starts with an error:


So I checked mysql2 version and upgraded it to see if it would fix the issue:


The previous error is gone, but only to be replaced by a new one…

Finally, I found out (can’t find where anymore) that I had to edit Gemfile in ThingSpeak directory, and add an older version to mysql2:


Let’s update mysql2, and try to create the databases again:


Damn a permission error. I could not find a proper fix, so at this point the title of the post should possibly become “How NOT to install Thingspeak in Ubuntu 16.04”, as although it will work, the steps below makes the installation insecure since I simply give full databases’ access to thing user. But that will do since I’ll only use it in my LAN, and maybe somebody will point to a secure solution to the issue.

[Update: Thanks to Arthur, I’ve got a more secure solution . I’ve left both insecure and secure workaround for reference, but obviously you should use the secure one, especially it’s not hard]

Insecure (don’t use it, I just left it to show what you should not do):


Secure way (strongly recommended):


This time I can create the databases for Thingspeak:


So now we can go to the next step to load the database with some data required by Thingspeak to work:


Great! Yet another error:


After spending a while for a solution I eventually found it in Rails Github with the reason being that MySQL 5.7 used in Ubuntu 16.04 does ot allows for NULL key.

We’ll need to create config/initializers/abstract_mysql2_adapter.rb file with:


Then we need add the following line at the end of config/environment.db


and run the command again:


Success! Finally…

The final step is to start the server:


Now start your web browser and you can access your local Thingspeak installation @ http://localhost:3000.
I’ll now have to study a little more about Thingspeak, install MQTT, as well as one of the MQTT to Thingspeak bridges available on the web, and see if I can plot power consumption data there.

  1. Arthur Frayn
    December 8th, 2016 at 01:22 | #1

    GRANT ALL PRIVILEGES ON *.* TO ‘thing’@’localhost’ WITH GRANT OPTION

    Surely a very dangerous thing to do and most unwise to advise others to do likewise? (Newbies who read technical columns like this just cut n paste “whatever” and do not worry about security implications until their system has been cracked.)

    The *.* means “all databases” + “all tables”

    A good way to proceed for setting up databases with a privileged user for just that database is

    1) first create the database for the “application” in this case ThingCloud or whatever name the install script wants

    CREATE DATABASE IF NOT EXISTS ‘ThingCloud’ CHARACTER SET utf8 COLLATE utf8_bin;

    2) second create the database privileged user with some nifty password

    CREATE USER ‘thingcloud’@’localhost’ IDENTIFIED BY ‘si1V3r-L1N1ng-XA9842’;

    3) grant privileges to that user on all tables in the application’s database (just ThingCloud — not all databases)

    GRANT ALL PRIVILEGES ON ‘ThingCloud’.* TO ‘thingcloud’@’localhost’;

    FLUSH PRIVILEGES;

    Repeat as appropriate for all your other custom installations requiring a database (ownCloud, piwigo etc).

  2. December 8th, 2016 at 10:49 | #2

    @Arthur Frayn
    Thanks. I ran out of time yesterday I did not look into this. I’ve now updated the post based on your instructions, and tested the full steps in Virtualbox.

  3. December 8th, 2016 at 22:16 | #3

    Hello, I’m blocked in :

    “bundle install ” as it seems that git://github.com/gregbell/active_admin.git no more exist !

    Have you the same problem ?

    Regards,
    Edouard

  4. December 9th, 2016 at 09:10 | #4

    @Edouard
    I never had this problem. Activeadmin is installed properly on my system:

    The repo is still there @ https://github.com/activeadmin/activeadmin

    and I can get it with git clone

    Could it be a temporary network problem?

  5. December 9th, 2016 at 15:50 | #5

    Thanks for your answer : it was a proxy pb which was blocking “git” protocol.

    So I use this command before “bundle install”:

    “git config –global url.”https://”.insteadOf git://”

    and after that it was OK.

    Thank for your how-to.

    Edouard

  6. DurandA
    December 13th, 2016 at 02:30 | #6

    Did they stop updating the opensource version of ThingSpeak? The repo has not commit since July 2015 but thingspeak.com has plenty of recent news.

  7. December 13th, 2016 at 09:17 | #7

    @DurandA
    Correct, and I explained that’s the reason why it’s such a pain to install on a recent OS in the post. So I’m not sure this is such a good idea to use this software longer term.

  8. December 18th, 2016 at 18:25 | #8

    hi, was not be able to do this on debian, i did differently, and it all seems to work (tried on dietpi and raspbian)… details here:
    https://gist.github.com/fragolinux/a09a5ab762f6c2c44f603ac263de9639

  9. December 19th, 2016 at 09:11 | #9

    @mrshark
    Nice. I notice you used the “insecure method” to create the database though.

  10. December 22nd, 2016 at 05:03 | #10

    @cnxsoft
    thanks, can you check now if it’s ok? Thanks

  11. December 22nd, 2016 at 09:31 | #11

    @mrshark
    It looks OK now.

  1. No trackbacks yet.