I have two raspberry pi with almost identical setup. On both I have a systemctl service and a timer which run every 15 seconds. The job calls a go script, which reads out the system temperature and creates a log entry in my mariadb database. The database is running on one of the raspberries. Therefore the only difference in the two go scripts are the hostname of the database and an identifier variable so I can see in the database from which the database entry is coming from. The rest is identical.
The problem is that raspberry1 produces more database entries than raspberry2. When I start with a fresh database with zero entries. Then 1 have about 120 entries from raspberry1 and about 70 entries from raspberrie two. The error continues. After more time I get for example 1000 entries and 700 entries.
Does anyone know where the differences could coming from? Maybe when both jobs want to create a new entry in the mariadb at the same time? But I'm almost sure that this is handled somehow by mariadb.
Here are the details:
FanLog.service
[Unit]
Description=Lüfter Temperatur Logger Service
After=network.target
[Service]
ExecStart=/root/go/temperatur_logger
WorkingDirectory=/root/go
StandardOutput=append:/var/log/TempLogger.log
StandardError=append:/var/log/TempLogger_error.log
User=root
FanLog.timer
[Unit]
Description=Fanlogger Timer
[Timer]
# OnBootSec=15min
# Wochentage-dd-mm hh:mm:ss
# OnCalendar=*-*-* *:05:00
# OnUnitActiveSec=1min
# OnCalendar=minutely
OnCalendar=*-*-* *:*:0/15
# OnCalendar=*-*-* *:0/0:20
[Install]
WantedBy=timers.target
The output of "systemctl status FanLog.timer" seems to be okay. I can see at maximum the 15 seconds down to a few ms and then again 15 seconds if I repeat.
⚡ root@DeskPi /etc/systemd/system systemctl status FanLog.timer
● FanLog.timer - Fanlogger Timer
Loaded: loaded (/etc/systemd/system/FanLog.timer; enabled; vendor preset: enabled)
Active: active (waiting) since Mon 2021-09-20 06:07:22 CEST; 39min ago
Trigger: Mon 2021-09-20 06:46:45 CEST; 13s left
Sep 20 06:07:22 DeskPi systemd[1]: Stopping Fanlogger Timer.
Sep 20 06:07:22 DeskPi systemd[1]: Started Fanlogger Timer.
temperatur_logger.go
package main
import (
"context"
"database/sql"
"fmt"
"log"
"os"
"os/exec"
"time"
"github.com/stianeikeland/go-rpio"
_ "github.com/go-sql-driver/mysql"
)
const (
username = "TempLogger"
password = "XXXXXXXXXXXXX"
hostname = "192.168.1.20:3306" // Different on other device
dbname = "TempLog"
table = "TempLog"
DeviceName = "OctoPi" // Different on other device
)
var (
// Use mcu pin 24, corresponds to GPIO3 on the pi
pin = rpio.Pin(24)
)
func add(x int, y int) int {
return x + y
}
func get_temp(temperatur string) string {
var outputlaenge int
var aktuelle_temperatur string
// Befehl
cmd := exec.Command("sudo", "/usr/bin/vcgencmd", "measure_temp")
// run command
if output, err := cmd.Output(); err != nil {
fmt.Println("Error", err)
} else {
outputlaenge = len(output)
aktuelle_temperatur = string(output[outputlaenge-7 : outputlaenge-3])
// fmt.Println(string(output[outputlaenge-7 : outputlaenge-3]))
}
return aktuelle_temperatur
}
func dsn(dbName string) string {
return fmt.Sprintf("%s:%s@tcp(%s)/%s", username, password, hostname, dbName)
}
func dbConnection() (*sql.DB, error) {
db, err := sql.Open("mysql", dsn(""))
if err != nil {
log.Printf("Error %s when opening DB\n", err)
return nil, err
}
defer db.Close()
ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
defer cancelfunc()
if err != nil {
log.Printf("Error %s when creating DB\n", err)
return nil, err
}
db.Close()
db, err = sql.Open("mysql", dsn(dbname))
if err != nil {
log.Printf("Error %s when opening DB", err)
return nil, err
}
db.SetMaxOpenConns(20)
db.SetMaxIdleConns(20)
db.SetConnMaxLifetime(time.Minute * 5)
ctx, cancelfunc = context.WithTimeout(context.Background(), 5*time.Second)
defer cancelfunc()
err = db.PingContext(ctx)
if err != nil {
log.Printf("Errors %s pinging DB", err)
return nil, err
}
log.Printf("Connected to DB %s successfully\n", dbname)
return db, nil
}
type temperatur_eintrag struct {
DeviceName string
record_date string
temperature string
fan bool
}
func insert(db *sql.DB, p temperatur_eintrag) error {
query := "INSERT INTO " + table + "(device_name, record_date, temperature, fan) VALUES (?, ?, ?, ?)"
ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
defer cancelfunc()
stmt, err := db.PrepareContext(ctx, query)
if err != nil {
log.Printf("Error %s when preparing SQL statement", err)
return err
}
defer stmt.Close()
res, err := stmt.ExecContext(ctx, p.DeviceName, p.record_date, p.temperature, p.fan)
if err != nil {
log.Printf("Error %s when inserting row into products table", err)
return err
}
rows, err := res.RowsAffected()
if err != nil {
log.Printf("Error %s when finding rows affected", err)
return err
}
log.Printf("%d log entry created ", rows)
return nil
}
func main() {
currentTime := time.Now()
var tempi string
var myFan bool
db, err := dbConnection()
if err != nil {
log.Printf("Error %s when getting db connection", err)
return
}
defer db.Close()
log.Printf("Successfully connected to database")
// Open and map memory to access gpio, check for errors
if err := rpio.Open(); err != nil {
fmt.Println(err)
os.Exit(1)
}
// Unmap gpio memory when done
defer rpio.Close()
// pin.PullUp()
if pin.Read() == 1 {
fmt.Println("Pin High")
myFan = true
} else {
fmt.Println("Pin Low")
myFan = false
}
p := temperatur_eintrag{
DeviceName: DeviceName,
record_date: currentTime.Format("2006-01-02 15:04:05"),
temperature: get_temp(tempi),
fan: myFan,
}
err = insert(db, p)
if err != nil {
log.Printf("Insert failed with error %s", err)
return
}
}
I have found the solution:
One of these two options made it work:
Persistent=true
AccuracySec=2sec
Not sure which one exactly as I didn't have tested more.
FanLog.timer looks now so:
[Unit]
Description=Fanlogger Timer
[Timer]
OnCalendar=*:*:0/10
Persistent=true
AccuracySec=2sec
[Install]
WantedBy=timers.target