SUCCESS!! ThingSpeak working on Debian 10

[TheRealBrommer]

Premise: I want to get good use out of my Raspberry Pi zero with a waveshare ethernet hat (i.e. NO wireless) which currently run 1. a thingSpeak server for my aquarium & Weather station + some test logging 2. Owncloud setup

I recently got myself a Pimoroni Pirate Audio Lineout hat. This works fine on a new raspberry Pi zero 2WH. I use that one with Volumio so I can use the excellent (new) plugin from AxLED for the OLED. Works with Spotify etc, really neat.

Caveat: 1. ThingSpeak needs to run on Wheezy... yes that old, but works great mind you for my above setup 2. Pirate Audio code is written for Buster..... + Volumio installs as an OS

So i wanted to know if i could combine everything under one OS. Initially I ran into all kinds of issues, all seem due to the mismatch in OS's.

Spoiler: 1. I have tried this with a Jessie version of Volumio & i can get the ThingSpeak server installed & working. However updating Volumio took me back to Buster wiping the Jessie setup (changes repositories to legacy) 2. Using this Buster Volumio setup I have managed to install the ThingSpeak server and it is running assigning a different port than 3000 as Volumio is using it, but it does work. Quite a few hurdles but i tried to keep track of how i did it + all the issues i came across (see final recipe below)

What i learned doing this suggested to me that i should be able to get this working on a newer (Buster) based Debian OS because due to the proper install through RVM (Ruby Version Manager) I figured i should be able to install this older version based setup using the latest RVM framework. This should then be able to install a constrained (older) set of an app (i.e. thingspeak), but with a newer database setup.

So going through the motions i tried to keep track of what i did to achieve this. Hence the following recipe, with hopefully enough info, for someone else interested in this the do this as well.

The Recipe:

sudo apt-get update

sudo apt purge ruby

Install MySQL. The thingspeak code wants/needs this. The newer OSs have MariaDB, so by installing as "default-mysql", mariadb will still be installed HOWEVER with symbolic links for you to be able to type in mysql commands instead of mariadb commands: sudo apt-get -y install build-essential default-mysql-server default-mysql-client default-libmysqlclient-dev libxml2-dev libxslt-dev

gpg --keyserver hkp://pool.sks-keyservers.net --recv-keys 409B6B1796C275462A1703113804BB82D39DC0E3 7D2BAF1CF37B13E2069D6956105BD0E739499BDB This didn't work for me so i did: command curl -sSL https://rvm.io/mpapis.asc | gpg --import - command curl -sSL https://rvm.io/pkuczynski.asc | gpg --import -

Get RVM: curl -L https://get.rvm.io | bash -s stable

source ~/.rvm/scripts/rvm rvm install ruby 2.1.0 gem install rubygems-update -v 2.7.11 (note i don't use sudo, so anyone can run it)

Now need to make sure it uses this version: update_rubygems

git clone https://github.com/iobridge/thingspeak.git cd thingspeak

Change in Gemfile: gem 'mysql2', '~> 0.3.18'
bundle update mysql2

gem install tzinfo -v 1.2.2 gem install tzinfo-data -v 1.2013.8

nano Gemfile.lock change libv8 to version 3.16.14.5 (which is for arm6) change myslq2 to version 0.3.21

gem install bundler -v 1.17.3 bundle install cp config/database.yml.example config/database.yml

------------------- database stuff ---- seems needed for creation of the file /config/environment.db I ignored errors about user "thing" being missing

------------------- database stuff ---- seems needed for creation of /config/environment.db I ignored errors about user "thing" being missing

mysql -u root -p enter: show databases; enter: CREATE DATABASE thingspeak_test DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; enter: GRANT ALL PRIVILEGES ON thingspeak_test.* TO 'thing'@'localhost'; enter: FLUSH PRIVILEGES; enter: exit

nano config/initializers/abstract_mysql2_adapter.rb

I changed directories to get the right subdirectory as else i got an empty file. enter:

config/initializers/abstract_mysql2_adapter.rb

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

cd config nano environment.db enter at the END: require File.expand_path('../../config/initializers/abstract_mysql2_adapter', FILE)

As i got some error running "rake db:create" i had fix something else:

cd /home cd .rvm

do a "ls" and make sure you end up in a directory something like "ruby-2.1.0@thingspeak" keep doing this until you're in "gems/mysql2-0.3.21/lib/mysql2/":

nano client.rb

At the line Line 12 :connect_flags => REMEMBER_OPTIONS | LONG_PASSWORD | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION,

Change like this: :connect_flags => REMEMBER_OPTIONS | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION,

cd ~/thingspeak rake db:create (i ignored any errors) rake db:schema:load

rails server (this will run on port 3000, i got a in-use complaint because of Volumio)

So i'm running it on a different port: rails s -p 4000

Give it a few minutes. Then i went to http://xxx.xxx.xxx.xxx:4000/

SUCCESS !!! It's running

References: 1. https://www.cnx-software.com/2016/12/07/how-to-install-thingspeak-in-ubuntu-16-04/ 2. https://www.teracomsystems.com/blog/how-to-install-local-thingspeak-server/?cn-reloaded=1 3. https://github.com/iobridge/thingspeak 4. https://vitux.com/installing-and-configuring-ruby-on-rails-on-debian-10/ 5. https://rvm.io/rvm/install

[maximeh2]

on step rvm install ruby 2.1.0 Error running 'requirements_debian_libs_install bison libssl1.0-dev', please read /home/pi/.rvm/log/1669896744_ruby-2.1.0/package_install_bison_libssl1.0-dev.log Requirements installation failed with status: 100.

thanks for advance

[TheRealBrommer]

Yes some libraries have moved hands, i.e. some links won't work (anymore). Note the system i setup in January (as per above) is still going strong (when obviously the links still worked). So to make this work it will need some more looking at to sort out. I ran into something similar to you when i tried to install it in a VM on Proxmox.

Be aware though there is a great alternative !

You can take any linux distro (my absolute favorite is BunsenLabs as it i super light-weight + based on debian) and install Docker. Then go to Docker hub and get one of those ready-to-go Thingspeak containers. They come with simple install instructions. Once installed they're ready to go. Super, super easy ! I used the one that has >10k+ downloads, can't remember the name though. Just do a search.

It works exactly as the system above. I think, from memory, it might even be based on the same method.