Installation Linux MySQL | MariaDB

Translation by Google: [NL] - [FR] - [DE] - [ES] - [IT]

⭐️ sbfspot-config

If you're planning to run SBFspot on Raspberry Pi, you can skip this installation manual and just run sbfspot-config
Run this command on your Pi and follow the self-explaining menu:

sudo bash -c "$(curl -s https://raw.githubusercontent.com/sbfspot/sbfspot-config/master/sbfspot-config)"

It runs as root, so you might want to take a look at the script first.

⭐️ Installation

Update package lists:

sudo apt update

Install required build packages:

sudo apt -y install make g++

Install Bluetooth (required, even with Speedwire):

sudo apt -y --no-install-recommends install bluetooth libbluetooth-dev

Install boost:

• Required libraries only (Recommended):

sudo apt -y install libboost-date-time-dev libboost-system-dev libboost-filesystem-dev libboost-regex-dev

• Complete build system (Optional - not needed if above libraries are installed):

sudo apt -y install libboost-all-dev

Install MariaDB client:

sudo apt -y install mariadb-client libmariadb-dev

Create symlink to satisfy #include <mysql/mysql.h>

sudo ln -s /usr/include/mariadb /usr/include/mysql

Create directories:

cd ~
mkdir smadata
mkdir SBFspot
sudo mkdir /var/log/sbfspot.3
sudo chown -R $USER:$USER /var/log/sbfspot.3

Download sourcecode to your Raspberry Pi:
Take a look here for the latest version and set sbfspot_version variable accordingly:

sbfspot_version=3.9.7
wget –c https://github.com/SBFspot/SBFspot/archive/V$sbfspot_version.tar.gz

Untar the sourcecode:

tar -xvf V$sbfspot_version.tar.gz -C SBFspot --strip-components 1

If you want to build the latest master branch:

sudo apt -y install git
git clone https://github.com/SBFspot/SBFspot.git

Compile SBFspot with MySQL/MariaDB support:

cd ~/SBFspot/SBFspot
make mariadb

Install SBFspot in /usr/local/bin/sbfspot.3:

sudo make install_mariadb

⭐️ Database setup

In below commands, replace [hostname] with the hostname or IP-address of your MySQL/MariaDB server

mariadb --host [hostname] -u root -p < ~/SBFspot/SBFspot/CreateMySQLDB.sql
mariadb --host [hostname] -u root -p < ~/SBFspot/SBFspot/CreateMySQLUser.sql

As a quick test, you can already execute a simple query:
Default password for SBFspotUser is SBFspotPassword

mariadb --host [hostname] -u SBFspotUser -p
MariaDB [(none)]> use SBFspot;
MariaDB [SBFspot]> select * from config;
+---------------+-------+
| Key           | Value |
+---------------+-------+
| SchemaVersion | 2     |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [SBFspot]> exit

⭐️ Configuration

For new installations, copy or rename SBFspot.cfg.default to SBFspot.cfg

cd /usr/local/bin/sbfspot.3
sudo cp SBFspot.default.cfg SBFspot.cfg

Edit the config file:

sudo nano SBFspot.cfg

Following configuration options have to be modified – The others can keep their default values.

• BTAddress : Bluetooth address of device closest to Raspberry Pi (Use hcitool scan to find it)
  If you have more than one inverter, each inverter should have the same NetID > 1 and Config option MIS_Enabled=1

❗ Make sure to disable IP_Address with # in front.

• IP_Address : Fixed IP address for Speedwire connected devices. If you have more than one inverter, enter the comma separated IP-addresses. MIS_Enabled is only used for Bluetooth

❗ Make sure to disable BTaddress with # in front.

• Password : Password for user – default is 0000

• Plantname : Name for your installation

• Latitude and Longitude : co-ordinates of your installation, used for sunrise and sunset calculation. https://www.gps-coordinates.net/maps

• Timezone : See date_time_zonespec.csv for a valid timezone or type cat /etc/timezone

• SQL_Hostname : or of your MySQL/MariaDB server

❗ Following paths are hardcoded for user pi. Change this if required.

• OutputPath : Path to CSV files – default /home/pi/smadata/%Y
• OutputPathEvents : Path to CSV files for inverter events – default /home/pi/smadata/%Y/Events

⭐️ Testing

/usr/local/bin/sbfspot.3/SBFspot -v -finq -nocsv

Now we can check if there is something in the db (don’t forget the ‘;’ at the end of each query):

mariadb --host [hostname] -u SBFspotUser -p
MariaDB [(none)]> use SBFspot;
MariaDB [SBFspot]> select * from vwspotdata;
+---------------------+---------------------+-----------------------+--------------+------------+------+------+------+-------+-------+--------+------+------+------+-------+------+------+--------+------+------+--------+--------+------------+--------+----------+-----------+---------------+------------+-----------+--------+-----------+-------------+
| TimeStamp           | Nearest5min         | Name                  | Type         | Serial     | Pdc1 | Pdc2 | Idc1 | Idc2  | Udc1  | Udc2   | Pac1 | Pac2 | Pac3 | Iac1  | Iac2 | Iac3 | Uac1   | Uac2 | Uac3 | PdcTot | PacTot | Efficiency | EToday | ETotal   | Frequency | OperatingTime | FeedInTime | BT_Signal | Status | GridRelay | Temperature |
+---------------------+---------------------+-----------------------+--------------+------------+------+------+------+-------+-------+--------+------+------+------+-------+------+------+--------+------+------+--------+--------+------------+--------+----------+-----------+---------------+------------+-----------+--------+-----------+-------------+
| 2014-08-26 10:32:08 | 2014-08-26 10:30:00 | SB4000TL              | SB 4000TL-20 | 2100276197 |  127 |   56 | 0.37 | 0.339 | 345.3 | 166.73 |  178 |    0 |    0 | 0.769 |  0.0 |  0.0 | 232.98 | 0.0  |  0.0 |    183 |    178 |        0.0 |    332 | 14969473 |     49.96 |       15967.7 |    15502.0 |   64.7059 | OK     | Closed    |       36.84 |
+---------------------+---------------------+-----------------------+--------------+------------+------+------+------+-------+-------+--------+------+------+------+-------+------+------+--------+------+------+--------+--------+------------+--------+----------+-----------+---------------+------------+-----------+--------+-----------+-------------+
1 row in set (0.004 sec)

MariaDB [SBFspot]> select * from inverters;
+------------+-----------------------+--------------+------------+------------+----------+--------+----------+---------------+------------+--------+-----------+-------------+
| Serial     | Name                  | Type         | SW_Version | TimeStamp  | TotalPac | EToday | ETotal   | OperatingTime | FeedInTime | Status | GridRelay | Temperature |
+------------+-----------------------+--------------+------------+------------+----------+--------+----------+---------------+------------+--------+-----------+-------------+
| 2100276197 | SB4000TL              | SB 4000TL-20 | 03.01.05.R | 1409041928 |      178 |  0.332 |  14969.5 |       15967.7 |    15502.0 | OK     | Closed    |         0.0 |
+------------+-----------------------+--------------+------------+------------+----------+--------+----------+---------------+------------+--------+-----------+-------------+
1 row in set (0.001 sec)

MariaDB [SBFspot]> exit

⭐️ Automation

To collect data, SBFspot should run every 5 minutes from 06:00 till 22:55 and once a day (05:55) for month data and events. To achieve this, create two scripts and cronjobs:

cd /usr/local/bin/sbfspot.3
sudo nano daydata

#!/bin/bash
#
log=/var/log/sbfspot.3/MyPlant_$(date '+%Y%m%d').log
/usr/local/bin/sbfspot.3/SBFspot -v -ad1 -am0 -ae0 >>$log

sudo nano monthdata

#!/bin/bash
#
log=/var/log/sbfspot.3/MyPlant_$(date '+%Y%m').log
/usr/local/bin/sbfspot.3/SBFspot -v -sp0 -ad0 -am1 -ae1 -finq >>$log

Make scripts executable:

sudo chmod +x daydata
sudo chmod +x monthdata

Create cron jobs:

crontab -e

## SBFspot
*/5 6-22 * * * /usr/local/bin/sbfspot.3/daydata
55 05 * * * /usr/local/bin/sbfspot.3/monthdata

SBFspotUploadDaemon with MariaDB

To upload your data to PVoutput you need a (free) account and enable API access in the settings.

⭐️ Installation

Install Curl:

sudo apt -y install libcurl4-openssl-dev

Compile SBFspotUploadDaemon with MySQL/MariaDB support:

cd ~/SBFspot/SBFspotUploadDaemon
make mariadb

Install SBFspotUploadDaemon in /usr/local/bin/sbfspot.3:

sudo make install_mariadb

⭐️ Configuration

For new installations, copy or rename SBFspotUpload.cfg.default to SBFspotUpload.cfg

cd /usr/local/bin/sbfspot.3
sudo cp SBFspotUpload.default.cfg SBFspotUpload.cfg

Edit the config file:

sudo nano SBFspotUpload.cfg

Following configuration options have to be modified.

• PVoutput_SID : Serial number of inverter and PVoutput System ID
  Example: 200212345:4321
  If you have more than one inverter, you'll need a PVoutput System ID for each inverter. Separate all systems with a comma.
  Example: 200212345:4321,200223456:4322
  ❗ Don't use the same System ID for multiple inverters, or data of this system will be overwritten each time.

• PVoutput_Key : Your personal API key

• SQL_Hostname : Network Name or IP-address of your MySQL/MariaDB server

Create a service file:

sudo nano /usr/local/bin/sbfspot.3/SBFspotUpload.service

Add these lines:

[Unit]
Description=SBFspot Upload Daemon
After=mysql.service mariadb.service network.target

[Service]
User=pi
Type=simple
TimeoutStopSec=10
ExecStart=/usr/local/bin/sbfspot.3/SBFspotUploadDaemon
Restart=on-success
RestartSec=10

[Install]
WantedBy=multi-user.target

• When running daemon as a user other than pi, make sure you change the line User=pi accordingly.
• If SBFspotUpload.cfg file is located in another directory, adapt the command line (e.g. -c /etc/SBFspotUpload.cfg)

Enable & start the service:

sudo systemctl enable /usr/local/bin/sbfspot.3/SBFspotUpload.service
sudo systemctl start SBFspotUpload

Check status:

systemctl status SBFspotUpload.service

● SBFspotUpload.service - SBFspot Upload Daemon
   Loaded: loaded (/usr/local/bin/sbfspot.3/SBFspotUpload.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2023-11-09 17:18:46 CET; 12min ago
 Main PID: 31131 (SBFspotUploadDa)
    Tasks: 1 (limit: 877)
   CGroup: /system.slice/SBFspotUpload.service
           └─31131 /usr/local/bin/sbfspot.3/SBFspotUploadDaemon -c /usr/local/bin/sbfspot.3/SBFspotUpload.cfg

Nov 09 17:18:46 raspberrypi systemd[1]: Started SBFspot Upload Daemon.
Nov 09 17:18:46 raspberrypi SBFspotUploadDaemon[31131]: INFO: SBFspotUploadDaemon Version 3.0.2

⭐️ Testing

Check upload daemon logging in syslog:

journalctl -r -u SBFspotUpload.service
-- Logs begin at Sun 2023-11-05 21:02:32 CET, end at Thu 2023-11-09 17:30:05 CET. --
Nov 09 17:24:31 raspberrypi SBFspotUploadDaemon[31131]: INFO: Uploading 3 datapoints, starting with 20231109,15:40,53743548,84 => OK (200)
Nov 09 17:23:31 raspberrypi SBFspotUploadDaemon[31131]: INFO: Uploading 30 datapoints, starting with 20231109,13:10,53742750,576 => OK (200)
Nov 09 17:22:31 raspberrypi SBFspotUploadDaemon[31131]: INFO: Uploading 30 datapoints, starting with 20231109,10:40,53740563,588 => OK (200)
Nov 09 17:21:31 raspberrypi SBFspotUploadDaemon[31131]: INFO: Uploading 30 datapoints, starting with 20231109,08:10,53739796,0 => OK (200)
Nov 09 17:18:46 raspberrypi SBFspotUploadDaemon[31131]: INFO: SBFspotUploadDaemon Version 3.0.2
Nov 09 17:18:46 raspberrypi systemd[1]: Started SBFspot Upload Daemon.

A few minutes later, your PVoutput graphs should be updated.

⭐️ Tweaking

By default, Inverter Temperature (V5) and Uac1 (V6) are uploaded to PVoutput. This can be changed relatively easy by modifying the view used for PVoutput data upload.

CREATE VIEW vwPvoData AS
SELECT dd.Timestamp,
    dd.Name,
    dd.Type,
    dd.Serial,
    dd.TotalYield AS V1,
    dd.Power AS V2,
    cons.EnergyUsed AS V3,
    cons.PowerUsed AS V4,
    spot.Temperature AS V5,
    spot.Uac1 AS V6,
    NULL AS V7,
    NULL AS V8,
    NULL AS V9,
    NULL AS V10,
    NULL AS V11,
    NULL AS V12,
    dd.PVoutput
FROM vwDayData AS dd
    LEFT JOIN vwAvgSpotData AS spot
        ON dd.Serial = spot.Serial AND dd.Timestamp = spot.Nearest5min
    LEFT JOIN vwAvgConsumption AS cons
        ON dd.Timestamp = cons.Nearest5min
ORDER BY dd.Timestamp DESC;

Suppose we want ambient temperature (Weather Underground) instead of inverter temperature and DC voltage instead of AC. In the example below this is achieved by replacing the data for V5 and V6.

mariadb --host [hostname] -D SBFspot -u SBFspotUser -p

Change the view:

ALTER VIEW vwPvoData AS
SELECT dd.Timestamp,
    dd.Name,
    dd.Type,
    dd.Serial,
    dd.TotalYield AS V1,
    dd.Power AS V2,
    cons.EnergyUsed AS V3,
    cons.PowerUsed AS V4,
    NULL AS V5,      -- Don't upload internal inverter temperature 
    spot.Udc1 AS V6, -- DC Voltage String 1 instead of AC mains
    NULL AS V7,
    NULL AS V8,
    NULL AS V9,
    NULL AS V10,
    NULL AS V11,
    NULL AS V12,
    dd.PVoutput
FROM vwDayData AS dd
    LEFT JOIN vwAvgSpotData AS spot
        ON dd.Serial = spot.Serial AND dd.Timestamp = spot.Nearest5min
    LEFT JOIN vwAvgConsumption AS cons
        ON dd.Timestamp = cons.Nearest5min
ORDER BY dd.Timestamp DESC;

❗ You can only change the V5..V12 parameters. V1..V4 are fixed. Also, make sure all V1..V12 are present.

When in PVoutput donation mode, you can make use of V7..V12 (BTW, I'm not affiliated with PVoutput)

The next example shows the query to upload Udc1(V7), Udc2(V8), Pdc1(V9) and Pdc2(V10)

ALTER VIEW vwPvoData AS
SELECT dd.Timestamp,
    dd.Name,
    dd.Type,
    dd.Serial,
    dd.TotalYield AS V1,
    dd.Power AS V2,
    cons.EnergyUsed AS V3,
    cons.PowerUsed AS V4,
    spot.Temperature AS V5,
    spot.Uac1 AS V6,
    spot.Udc1 AS V7,  -- DC Voltage String 1
    spot.Udc2 AS V8,  -- DC Voltage String 2
    spot.Pdc1 AS V9,  -- DC Power String 1
    spot.Pdc2 AS V10, -- DC Power String 2
    NULL AS V11,
    NULL AS V12,
    dd.PVoutput
FROM vwDayData AS dd
    LEFT JOIN vwAvgSpotData AS spot
        ON dd.Serial = spot.Serial AND dd.Timestamp = spot.Nearest5min
    LEFT JOIN vwAvgConsumption AS cons
        ON dd.Timestamp = cons.Nearest5min 
ORDER BY dd.Timestamp DESC; 

⭐️ Join the Team

• Go to the SBFspot Team page on PVoutput
• Click Join or Leave Team link
• Select each system and click Join button

⭐️ Clean up

If everything works fine, delete the source files to recover some diskspace:

cd ~
rm -r SBFspot
rm V$sbfspot_version.tar.gz

❗ When running for a few months, the log directory will get pretty full. Until now I cleaned this manually, but I found a nice command here to automate the cleaning. The line below creates a scheduled task cron at 01:00 to delete all logs older than 1 week:

(crontab -l ; echo '0 1 * * * find /var/log/sbfspot.3/ -name "*.log" -mtime +7 -delete')|crontab -

❗ As from SBFspot V3.9.6, the above cleaning of log files is not needed anymore.
Syslog is used instead. Check with journalctl -r -u SBFspotUpload

⭐️ Remarks

In some circumstances, auto-detection of inverters won't work. If this is the case, you can use a config file for each inverter. Change BTAddress or IP_Address and the plantname in each config and run SBFspot for every inverter.

#SB4000.cfg
BT_address=80:ab:cd:ef:00:01
PlantName=SB4000

#SB3600.cfg
BT_address=80:ab:cd:ef:00:02
PlantName=SB3600

Run SBFspot for every inverter with -cfg argument

SBFspot -v -cfgSB4000.cfg
SBFspot -v -cfgSB3600.cfg

❗ Run SBFspot -? to explore all other arguments

See also:

HowTo add Ambient Temperature to your PVoutput.org graphs
PVoutput User Community
Zonstraal Forum (Dutch)