thingspeak.com is a platform for The Internet of Things. If the free service is not suitable for your application (for example, your devices update more than once every 15 seconds), you might opt to install your own server.
The ThingSpeak source code is open-source and hosted on GitHub, but you may not find the installation as easy as “git clone”. The platform is built using Ruby on Rails, and getting up to speed with Ruby, Gems, Rails, and the permutations and combinations of dependencies and package mangers may be more than you are willing to tackle just so you can do something silly like send a Tweet when the temperature in your living room gets too hot.
Not being a Ruby or Rails expert myself (I have exactly one day of experience as of …. now!) I have condensed the installation of ThingSpeak on an Ubuntu 12.04 LTS server (works on Raspbian and probably other Debian-based distros) into the following:
If that’s still too much for you, you can copy-and-paste this three-line version:
wget http://goo.gl/wS4hBf -O thingspeak-install.sh chmod +x thingspeak-install.sh ./thingspeak-install.sh
During the installation you’ll be asked to set a new root password for MySQL (if not already installed) and again when the ThingSpeak databases and tables are created. When the installation is complete you can access your ThingSpeak server at http://<ip-address>:3000. To shut it down, press Ctrl-C. To launch it again in the future, simply run “rails server”.
Alternatives
The install process (especially compiling ruby from sources) can take quite a while on low-power devices like RaspberryPis or BeagleBone Blacks. Thingspeak has instructions for installing on a clean Ubuntu 12.04 LTS server here, however there are a few tweaks required. Here’s an updated version of Thingspeak’s instructions for installing using rvm instead of sources:
Next Steps
Once your Thingspeak server is installed, why not use the ThingSpeak Java Client to read, write, and analyze your data.
Hi, is it right, that there are no apps available in the server installation?
Thanks!
Regards,
Sascha
Correct. Apps like ThingTweet, React, etc. are not available in the open-source self-hosted server. I think I saw mention in the ThingSpeak forums of some apps being added at some point, but I’m not sure when or how.
Hi Andrew, thanks for sharing your code.
I am trying to install ThingSpeak in a Raspberry Pi and I get an error when I get to rake db:create.
This is the error message, any idea?
Thanks,
Rodrigo
Couldn’t create database for {“adapter”=>”mysql2”, “encoding”=>”utf8”, “reconnect”=>false, “database”=>”thingspeak_development”, “pool”=>5, “username”=>”thing”, “password”=>”speak”, “socket”=>”/var/run/mysqld/mysqld.sock”}, {:charset=>”utf8″, :collation=>”utf8_unicode_ci”}
(If you set the charset manually, make sure you have a matching collation)
Couldn’t create database for {“adapter”=>”mysql2”, “encoding”=>”utf8”, “reconnect”=>false, “database”=>”thingspeak_test”, “pool”=>5, “username”=>”thing”, “password”=>”speak”, “socket”=>”/var/run/mysqld/mysqld.sock”}, {:charset=>”utf8″, :collation=>”utf8_unicode_ci”}
(If you set the charset manually, make sure you have a matching collation)
— create_table(“active_admin_comments”, {:force=>true})
rake aborted!
Mysql2::Error: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/mysql2-0.3.16/lib/mysql2/client.rb:70:in `connect’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/mysql2-0.3.16/lib/mysql2/client.rb:70:in `initialize’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/mysql2_adapter.rb:18:in `new’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/mysql2_adapter.rb:18:in `mysql2_connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:440:in `new_connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:450:in `checkout_new_connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:421:in `acquire_connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:356:in `block in checkout’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:355:in `checkout’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:265:in `block in connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:264:in `connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:546:in `retrieve_connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_handling.rb:79:in `retrieve_connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/connection_handling.rb:53:in `connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/migration.rb:615:in `connection’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/migration.rb:628:in `block in method_missing’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/migration.rb:601:in `block in say_with_time’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/migration.rb:601:in `say_with_time’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/migration.rb:621:in `method_missing’
/home/pi/thingspeak/db/schema.rb:16:in `block in ‘
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/schema.rb:42:in `instance_eval’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/schema.rb:42:in `define’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/schema.rb:62:in `define’
/home/pi/thingspeak/db/schema.rb:14:in `’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activesupport-4.0.5/lib/active_support/dependencies.rb:223:in `load’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activesupport-4.0.5/lib/active_support/dependencies.rb:223:in `block in load’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activesupport-4.0.5/lib/active_support/dependencies.rb:214:in `load_dependency’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activesupport-4.0.5/lib/active_support/dependencies.rb:223:in `load’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/gems/activerecord-4.0.5/lib/active_record/railties/databases.rake:253:in `block (3 levels) in ‘
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/bin/ruby_executable_hooks:15:in `eval’
/home/pi/.rvm/gems/ruby-2.1.0@thingspeak/bin/ruby_executable_hooks:15:in `’
Tasks: TOP => db:schema:load
(See full trace by running task with –trace)
=> Booting Puma
=> Rails 4.0.5 application starting in development on http://0.0.0.0:3000
=> Run `rails server -h` for more startup options
=> Ctrl-C to shutdown server
Puma 2.9.1 starting…
* Min threads: 0, max threads: 16
* Environment: development
* Listening on tcp://0.0.0.0:3000
I think you can safely ignore those errors. For reasons unknown, the installer tries to create three different databases (thingspeak, thingspeak-development, and thingpspeak-test) but only the first one is used.
Did you check to see if the server was up and running?
Hi Andrew, It is running!! Thank you very much!!
I have an extrangre message
pi@raspberrypi ~/thingspeak $ rails server
=> Booting Puma
=> Rails 4.0.5 application starting in development on http://0.0.0.0:3000
=> Run `rails server -h` for more startup options
=> Ctrl-C to shutdown server
Puma 2.9.1 starting…
* Min threads: 0, max threads: 16
* Environment: development
* Listening on tcp://0.0.0.0:3000
But I go to RPI ip:3000 and it is running.
BTW do you know how to have “rails server” running after a reboot?
Best,
Rodri
Forgive the lateness of my reply. My server hasn’t been sending e-mail.
I don’t see any strange messages there – looks like everything is working as expected. The proper way to have the Thingspeak server start automatically on boot is to create an “init script”. The lazy way is to add the following to /etc/rc.local:
su pi -c "(cd ~/thingspeak; rails server -d)"Thanks Andrew, TS is working on Rpi properly!!
Also thanks for the code to start automatically on boot, I’ll try it now.
Best,
Rodri
Hi Andrew, server is running on a Raspberry Pi! Thank you very much!
I don’t know why it shows 0.0.0.0:3000 but it is running on IP:3000
pi@raspberrypi ~/thingspeak $ rails server
=> Booting Puma
=> Rails 4.0.5 application starting in development on http://0.0.0.0:3000
=> Run `rails server -h` for more startup options
=> Ctrl-C to shutdown server
Puma 2.9.1 starting…
* Min threads: 0, max threads: 16
* Environment: development
* Listening on tcp://0.0.0.0:3000
Started GET “/” for 10.95.0.13 at 2014-12-10 11:49:06 +0000
Processing by PagesController#home as HTML
Geokit is using the domain:
Rendered layouts/_flash.html.erb (36.0ms)
Rendered pages/_contact_form.html.erb (455.9ms)
Rendered pages/home.html.erb within layouts/home (1819.7ms)
Rendered layouts/_header.html.erb (21875.7ms)
Rendered layouts/_nav.html.erb (239.8ms)
Rendered layouts/_footer.html.erb (21.7ms)
Completed 200 OK in 25595ms (Views: 24368.0ms | ActiveRecord: 0.0ms)
BTW, do you know how to have “rails server” running after a reboot automatically?
Best,
Rodrigo
Hi Andrew,
I stumbled upon your code and it’s a real treasure – I’m looking for an application to store and track and plot and archive logging data and this is exactly what we need – and hey, this way we can run it locally, so our data does not have to be hosted on an unknown server in an unknown country by unknown people. – it’s magic. Bummer that thingspeak inc doesnt publish their current code as OSS all the time (at least I couldnt find a link to it).
I’ve written some sripts to fill the database, and now I’m looking at analyzing the data, ideally generating plot windows such as minimum(data), maximum(data), average(data), etc. per hour, per day, per week, per month, per quarter and per year. Would either be a highcharts or highstocks chart in a plugin, or perhaps just local private html code pulling charts out of the local thingspeak install.
Now that sort-of all works, I’m moving into ‘production’ mode, and hopefully can get it to work woth apache and passenger, we’ll see – seems more tricky than what phusion writes anyway.
OKne thing – I noted there is an /admin page within the thingspeak code, but it comes out a bit unformatted, and none of the usernames/passwords is accepted, even if I grant them all and any rights to the database. Do you know more about the admin functionality ?
Regards, Otte
Hi Andrew,
When I tried to deploy Thingspeak on Heroku, I met with some errors below.
== 20101111054358 CreateUsers: migrating ======================================
— create_table(:users)
-> 0.0111s
== 20101111054358 CreateUsers: migrated (0.0124s) =============================
== 20101116224140 CreateApiKeys: migrating ====================================
— create_table(:api_keys)
PG::DatatypeMismatch: ERROR: column “write_flag” is of type boolean but default expression is of type integer
HINT: You will need to rewrite or cast the expression.
: CREATE TABLE “api_keys” (“id” serial primary key, “api_key” character varying(16), “device_id” integer, “feed_id” integer, “user_id” integer, “write_flag” boolean DEFAULT 0, “public_flag” boolean DEFAULT 0, “created_at” timestamp, “updated_at” timestamp)
rake aborted!
I even solved this error by changing “0” to “false”, but another different error showed up on other table…
I have no experience with Heroku – this article is aimed at Ubuntu. Did you try the Thingspeak Forum?
Hi Andrew,
I think maybe these errors are caused by ruby version between heroku and yours. I will try your link above. Thanks.
Hi Andrew,
Thanks for your excellent work. I ‘almost’ got it installed but it gives me an error as follows;
nl40@nl40:~/thingspeak$ bundle exec rake db:schema:load
— create_table(“active_admin_comments”, {:force=>true})
rake aborted!
Mysql2::Error: Access denied for user ‘thing’@’localhost’ (using password: YES)
/home/nl40/thingspeak/db/schema.rb:16:in `block in ‘
/home/nl40/thingspeak/db/schema.rb:14:in `’
Tasks: TOP => db:schema:load
(See full trace by running task with –trace)
The system is a fresh install of Ubuntu on an HP NL40.
It seems the database doesn’t have a user named thing?
Can you provide any advice?
Thanks
Hi, I found the problem.
After line 24
cp thingspeak/config/database.yml.example thingspeak/config/database.yml
You need to edit the database.yml and put in your mysql username and password in three places. Save the file then continue the installation. It Now works.
Thanks again
Thanks for the detailed instructions – worked like a charm :)
Hi, I am getting this error while on step “bundle install”
Following are the message in terminal pasted below:
Retrying `git clone ‘git://github.com/moomerman/twitter_oauth.git’ “/home/sharad/.bundle/cache/git/twitter_oauth-e039ad0d724a5ffbd32d6eea42c91cf30fc72c5a” –bare –no-hardlinks –quiet` due to error (3/4): Bundler::Source::Git::GitCommandError Git error: command `git clone ‘git://github.com/moomerman/twitter_oauth.git’ “/home/sharad/.bundle/cache/git/twitter_oauth-e039ad0d724a5ffbd32d6eea42c91cf30fc72c5a” –bare –no-hardlinks –quiet` in directory /home/sharad/thingspeak has failed.fatal: unable to connect to github.com:
github.com[0: 192.30.253.113]: errno=Connection timed out
github.com[1: 192.30.253.112]: errno=Connection timed out
……….
Could anyone help try resolving this issue?