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.

Share this:
FacebookTwitterHacker NewsSlashdotRedditLinkedInPinterestFlipboardMeWeLineEmailShare

Support CNX Software! Donate via cryptocurrencies, become a Patron on Patreon, or purchase goods on Amazon or Aliexpress

ROCK 5 ITX RK3588 mini-ITX motherboard

11 Replies to “How to Install ThingSpeak in Ubuntu 16.04”


    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


    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’;


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

  2. Hello, I’m blocked in :

    “bundle install ” as it seems that git:// no more exist !

    Have you the same problem ?


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

    The repo is still there @

    and I can get it with git clone

    Could it be a temporary network problem?

  4. 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.


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

  6. @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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Khadas VIM4 SBC
Khadas VIM4 SBC