How to Install ThingSpeak in Ubuntu 16.04

Orange Pi Development Boards

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.

Support CNX Software - Donate via PayPal or become a Patron on Patreon

11
Leave a Reply

avatar
11 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
5 Comment authors
mrsharkDurandAEdouardcnxsoftArthur Frayn Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Arthur Frayn
Guest
Arthur Frayn

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

Edouard
Guest

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

Edouard
Guest

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

DurandA
Guest
DurandA

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.

mrshark
Guest

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

mrshark
Guest

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