You most certainly know the ThingSpeak online service that collects data from connected objects and creates Dashboards. ThingSpeak is an open source project whose sources are available on GitHub (project page). Nothing prevents us from installing it on a local machine to create our Dashboard without going through an online service. ThingSpeak was developed with the language Ruby (english page of the project). To write this tutorial, I used a mini PC equipped with an Intel Atom microprocessor running Ubuntu 16.04 LTS.

Prepare the Ubuntu 16.04 LTS server

Before we get into the installation of ThingSpeak, it’s best to make sure that Ubuntu is up to date. Run the following commands. You have the time to go to make you heat a coffee or a The…

sudo apt-get update
sudo apt-get upgrade
sudo apt-get dist-upgrade
sudo apt-get autoremove
sudo apt-get clean

Installing the Ruby Environment

Ruby is the programming language, or rather the development environment used to develop ThingSpeak. To run ThingSpeak, we’ll need Ruby Version Control (RVM) which is the Ruby Command-Line Tool (CLI) that allows you to install, manage libraries (gems including Ruby jargon ). Before you can install RVM, run these three commands:

gpg --keyserver hkp:// --recv-keys D39DC0E3
\curl -sSL | bash -s stable
source ~/.rvm/scripts/rvm

We can now install the version 2.1.0 necessary to run ThingSpeak.

rvm install 2.1.0
rvm use 2.1.0 –default

Installing the MySQL database

ThingSpeak uses the MySQL database to run and store data from connected objects. This is a much more robust database that will store a larger amount of data than the usual sqllite database used in Ruby projects.

sudo apt-get install mysql-server mysql-client libmysqlclient-dev

During the installation, you should have a window asking you to enter your user password (root). If this is not the case, at the end of the installation, launch the MySQL CLI. At startup, MySQL will ask you to create a root password. Type it and then quit the CLI with the exit command.

Install ThingSpeak

The installation is quite simple since it is enough to recover the sources from GitHub. Before you begin, verify that the git command is installed on your system

sudo apt-get install git

Now you can download the sources

git clone

Enter the project directory and run the following commands to install the gem dependencies that are required for the project.

cd thingspeak
gem install bundler
bundle install

Configure the database

It is at this stage that problems can arise. Fortunately, the cnx-software blog worked on the subject and found how to do it.

To begin, let’s make a copy of the default scripts that will be used to configure the tables in the database.

cp config/database.yml.example config/database.yml

It is preferable to change the passwords. ThingSpeak will create three tables: test, development and production. You can define a user and password for each mode of operation.

We now start the configuration of the database

rake db:create

The first one you should encounter concerns the mysql2 connector

$ rake db:create
rake aborted!
Bundler::GemRequireError: There was an error while trying to load the gem 'mysql2'.
Gem Load Error is: uninitialized constant Mysql2::Client::SECURE_CONNECTION

Edit the Gemfile file at the root of the project, specifying the minimum version to use

gem 'mysql2', '~> 0.3.18'

Save with CTRL + X then O.

It is necessary to update the dependencies

bundle update mysql2

Let’s check that everything is good

christophe@atom:~/thingspeak$ bundle show mysql2

Everything is good, you can restart the creation of the database.

$ rake db:create
thingspeak_development already exists
Mysql2::Error: Access denied for user 'thing'@'localhost' to database 'thingspeak_test': CREATE DATABASE `thingspeak_test` DEFAULT CHARACTER SET `utf8` COLLATE `utf8_unicode_ci`

New problem, the base already exists but we do not have the right rights! Let’s open the MySQL shell with the command mysql -u root -p . Once connected, we will create a thingspeak_test table and give it the necessary rights.

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.16-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
| Database               |
| information_schema     |
| mysql                  |
| performance_schema     |
| sys                    |
| thingspeak_development |
5 rows in set (0,05 sec)

mysql> CREATE DATABASE thingspeak_test DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0,00 sec)

mysql> GRANT ALL PRIVILEGES ON thingspeak_test.* TO thing@localhost;
Query OK, 0 rows affected (0,01 sec)

Query OK, 0 rows affected (0,67 sec)

mysql> exit

You can restart the configuration of the tables

christophe@atom:~/thingspeak$ rake db:create
thingspeak_development already exists
thingspeak_test already exists

This time, everything is good!

Let’s go to the schematics

christophe@atom:~/thingspeak$ rake db:schema:load
-- create_table("active_admin_comments", {:force=>true})
rake aborted!

New problem. Again, we can thank our friends at cnx-software who spent time identifying and finding the solution. It seems that the problem comes from the version MySQL 5.7 used by Ubuntu 16.04 which does not allow the null keys. The solution is to create a configuration file config / initializers / abstract_mysql2_adapter.rb containing the following class:

class ActiveRecord::ConnectionAdapters::Mysql2Adapter
  NATIVE_DATABASE_TYPES[:primary_key] = "int(11) auto_increment PRIMARY KEY"

Then, you must add this line in the configuration file config/environment.db

require File.expand_path('../../config/initializers/abstract_mysql2_adapter', __FILE__)

We can restart the creation of schemas

rake db:schema:load

Installation is complete!

Start ThingSpeak

All you have to do is start the server. To stop the server, use the combination CTRL + C.

rails server webrick
=> Booting WEBrick
=> Rails 4.0.5 application starting in development on
=> Run `rails server -h` for more startup options
=> Ctrl-C to shutdown server
[2016-12-09 10:15:36] INFO  WEBrick 1.3.1
[2016-12-09 10:15:36] INFO  ruby 2.1.0 (2013-12-25) [x86_64-linux]
[2016-12-09 10:15:36] INFO  WEBrick::HTTPServer#start: pid=15584 port=3000

Started GET "/" for at 2016-12-09 10:15:47 +0100
Processing by PagesController#home as HTML
Geokit is using the domain: localhost
  Rendered layouts/_flash.html.erb (3.1ms)
  Rendered pages/_contact_form.html.erb (48.0ms)
  Rendered pages/home.html.erb within layouts/home (1364.0ms)
  Rendered layouts/_header.html.erb (2591.1ms)
  Rendered layouts/_nav.html.erb (30.4ms)
  Rendered layouts/_footer.html.erb (2.5ms)
Completed 200 OK in 4164ms (Views: 4096.8ms | ActiveRecord: 0.0ms)

You can access the web interface from a browser at http://localhost:3000 .

installation thingspeak ubuntu 16-04 lts

Update ThingSpeak

To update ThingSpeak, go to the project directory and run the following commands

git pull origin master
bundle install
rake db:migrate

In the next tutorial, we will discover the possibilities offered by this platform.

Subscribe to the weekly newsletter

No spam and no other use will be made of your email. You can unsubscribe anytime.

DIY Projects