Implementation of Http Integration from the TTS (TTN V3) LoRaWAN Network Server to a Custom MySQL Database.

Overview

As the new thethingsstack continues to mature and achieve the desired robustness for all to benefit — starting with TheThings Industries to the maker and enterprise communities, there has been a challenge of integrating the new TTN V3 to MySQL unlike the predecessor LoRaWAN server of TTN V2 which seemed to be very easy to do. AEQ-web have done a great publication on integrating TTN v2 to MySQL on their blog here. Previously, I have also published a paper on how to achieve http integration from V2 from the LoRaWAN server to Google Firebase here. In this article, I share the integration of the new thethingsstack (TTS) to MySQL through http. This article follows a previous post on Migration from V2 to V3 on TheThingsNetwork shared earlier this year on April 19th. While a lot of engagements on the thethingsnetworks forum still continue under this subject, I just want to share my findings and experience in carrying this out with both the less technical and the technical audiences.

In my experiment, I have used a DHT22 sensor connected to an Uno (helloworld experiment) to demonstrate a case of detecting the surrounding temperature and humidity using the DHT22 sensor. To the setup, I have connected an RN2483 LoRa module (which uses the UART interface) to transmit the detected temperature and humidity values to thethingsstack LoRaWAN server. My setup is as shown in figure 1 here.

Programming the Hardware Setup

Figure 1: Hardware setup with the RN2483 LoRa module

I have programmed this setup with C++ using Platformio and the Arduino library. The code is found on Github can be found on Github through this Link.

Registering the Device on the TTN V3 (TTS)

As the RN2483 is not automatically available (on the section of From The LoRaWAN Device Repository) on the TTS (TTN V3), I have had to register it manually (following the description provided by thethingsindustries on this link). The LoRaWAN version selected is 1.0.4. This is obtained from the device when using the UART serial read using the instructions provided by the Semtech team for the chip documentation here. The command used to obtain this is the basic sys get ver instruction. Note that you can do the registration by setting up a new application or adding the device to an existing application. I am putting screenshots here for adding a new application and device just for the general guideline but it is quite straight forward to do this.

Figure 2: Interface for creating a new application on TTS

Figure 3: Interface for registering your device

Figure 4: Interface for manual registration

Setting up Your MySQL Environment

The details of MySQL, PHP, Apcahe and other related developments are covered broadly online and in many books, therefore under this section, I will just share the direct setup of MySQL on my AWS-based EC2 instance to house the data. In my first step, I installed the Apache web server. I then installed PHP which is traditionally closely allied to the Apache software. Python or Perl can also be used. The PHP version used was PHP7 while the MySQL one was version 8. You can find the installation guidelines on this link. The instructions I used for setting all that up on the Ubuntu-based instance are as shown below:

$ sudo apt install apache2 php5 libapache2-mod-php5

$ sudo apt install mysql-server mysql-client

$ mysql — — version

$ sudo mysql_secure_installation

$ sudo /etc/init.d/mysql start

If login comes with an error and does not allow you to start your MySQL, it meanst that db_users using it have an “auth” configuration under system user credentials. You can check if the root user is set up like this by running the following command:

$ sudo mysql –u root #use sudo the new MySQL installation

The MySQL prompt will begin then you type the following commands:

mysql> USE mysql;

mysql> SELECT USER, HOST, PLUGIN FROM mysql.user;

If the result of this query shows that the root user is using the auth_socket plugin, then you can take the following two approaches to resolve this:

1. You can set the root user to use the mysql-native-password plugin. Or

2. You can create a new db_user (this is more recommended and easier to do).

Therefore, working with option 2, you will do the following:

$ sudo mysql –u root

mysql> USE mysql;

mysql> CREATE USER ‘YOUR_PREFERRED_USERNAME’ @’localhost’ IDENTIFIED BY ‘YOUR_ PREFERRED _PASSWORD’;

mysql> UPDATE USER SET PLUGIN =’auth_socket’ WHERE USER = “YOUR_PREFERRED_USERNAME”;

mysql> FLUSH PRIVILEGES;

mysql> EXIT;

You can then restart your mysql with the following command.

$ sudo service mysql restart

If you prefer to install PhpMyAdmin to easily setup your database, you can still go ahead and use it. I prefer the instructions provided on this link. Otherwise, you could still go ahead and use MySQL queries on your command line.

Writing your PHP Scripts

1. PHP Script for Database access — This can be edited based on your preferences. I have saved my script as DB_config.php which is called in the script insert_dht.php.

Figure 4: PHP Script for database access

2. PHP Script to store data to the dht22 MySQL database from the TTS platform. This script is added under the webhooks section Details of Webhooks and how they work on thethingsstack(TTS) can be found on this link.

Figure 4: Interface for TTS Webhook Integration

Figure 5: PHP script that inserts data from TTS to MySQL DB (this is the script that webhook url points to)

In the design of my MySQL table, I have created five columns — device_id, application_id, received_at, temperature and humidity. This is defined in the insert_data.php script and extracts the real-time data from the live events of the logs on the TTS to the MySQL. One can extend the script if there is a need to store other relevant data appearing in the JSON log of the TTS live events. My table is shown in figure 5 storing the real-time data (at an interval of 2000 ms) from the DHT22 sensor node.

Figure 6: Snippet of my MySQL table receiving real-time data from the DHT22 hardware setup

Conclusions

Integrating thethingsstack (TTS) or TTN V3 in order to store your data in your custom MySQL database just requires you to do the following:

1. Understand how to work with http integration (through webhooks) on the new TTS platform.

2. Properly configure your MySQL environment very securely and able to receive and store data.

3. Write a working PHP script that picks data from the LoRaWAN network server to store in the configured MySQL database.