breakpo: 2024

Mysql show processlist of a specific user - filtering processlist

SELECT * FROM information_schema.processlist WHERE User LIKE 'username';

The way I use go (golang) sql/mysql driver to create connection

I use go for web development and I have noticed I have a few problems with database connections to mysql using go-sql-driver/mysql.

Database connections created and then never closed. I reached a point of 4,096 simultaneous connections and my mysql server have reached max-connections which reached a denial of service.

I had to play a bit with some tweaking of the code and this is what I have reached:

db.SetMaxOpenConns(200) // this works occasionally

db.SetMaxIdleConns(30) // this does not work but I keep it anyway

db.SetConnMaxLifetime(time.Second * 30) // this also does not work but I keep it

db.Exec("SET SESSION wait_timeout = 30") // this works

db.Exec("SET SESSION interactive_timeout = 30") // and also this one

Using the above directive I was able to have the number of connections under control. Please note that sometimes I still get more than 200 connections even though I set the maximum to 200, and sometimes it keeps the max 200.

SetMaxIdleConns and SetConnMaxLifetime - did not have any affect what so ever.

Again, it might be that I am not fully aware of the configuration or the right usage.

Here below is my full function to get a connection to the database with go mysql driver:


  package main

import (
	"database/sql"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql" // Register the driver
)

var db *sql.DB = nil // global connection

func GetConnection(whereFrom string) (*sql.DB, error) {
	slog.Info(" * UC:", "connectIndex", connectIndex, "from", whereFrom)

	var err error

	if !isConnectionAlive(db) {
		slog.Error("20250225.01152 connection GetConnection error: connection is not alive")
		db, err = createSimpleConnection()
		if err != nil {
			slog.Error("20250225.01144b connection GetConnection error receiving database", "err", err)
			return nil, err
		}
	}

	return db, nil
}

// create a real connection to the database
func createSimpleConnection() (*sql.DB, error) {
	var err error

	db, err := sql.Open("mysql", "user:password@tcp(IP:3306)/databasename?charset=utf8mb3")
	if err != nil {
		log.Fatal("error 20241212.01314 createConnection error: " + err.Error())
		return nil, err
	}

	db.SetMaxOpenConns(200)                 // Set max open connections
	db.SetMaxIdleConns(30)                  // Set max idle connections
	db.SetConnMaxLifetime(time.Second * 30) // Set the max lifetime of a connection

	// Setting session-specific parameters
	_, err = db.Exec("SET SESSION max_execution_time = 30000") // seconds = milliseconds * 1000
	if err != nil {
		slog.Error("20241212.01641 set wait_timeout", "err", err)
		return nil, err
	}
	
	_, err = db.Exec("SET SESSION wait_timeout = 30") // seconds
	if err != nil {
		log.Fatal("error 20241212.01641 set wait_timeout error: " + err.Error())
		return nil, err
	}

	_, err = db.Exec("SET SESSION interactive_timeout = 30") // seconds
	if err != nil {
		log.Fatal("error 20241212.01642 set interactive_timeout error: " + err.Error())
		return nil, err
	}

	return db, nil
}

func isConnectionAlive(db *sql.DB) bool {
	if db == nil {
		return false
	}

	err := db.Ping()
	return err == nil
}

Poor man's DVR - a linux bash script to capture images from the camera and upload them to a remote server

I wanted to create a very easy "DVR" script for my raspberry PI machine that will take a shot from the camera and upload the photos to a remote server so I can see what is happening in my living room.

I created this simple bash Linux command line script to activate the camera, save the photo, add a timestamp and upload it to a remote server.

It is running every minute - capturing 5 photos from the webcam every 9 seconds and uploads them to the server using scp.

Here is the shell script for a poor man's DVR:

#!/bin/bash

# repeat 5 times
for i in {1..5}; do
    # the file name with a datetime stamp
    filename=webcam_$(date +%Y%m%d-%H%M%S).jpg

    # capture the image from the camera
    ffmpeg -f video4linux2 -input_format mjpeg -i /dev/video0 -vframes 1 -frames:v 1 /mnt/ramdisk/$filename

    # wait
    sleep 9
done

# create a latest.jpg image
cp /mnt/ramdisk/$filename /mnt/ramdisk/latest.jpg

# add the time stamp
convert /mnt/ramdisk/latest.jpg -gravity SouthEast -pointsize 22 -fill white -annotate +30+30 $filename /mnt/ramdisk/latest.jpg

# upload the images
scp /mnt/ramdisk/*.jpg remote@remote.server://directory

# delete the images
rm /mnt/ramdisk/*.jpg

Caddy webserver to prevent access from external machines on the network

How to protect your local caddy installation by preventing other machines outside of your localhost and local computer from accessing your device.

Create rule:

(block_outcoming) {

       @denied not client_ip 127.0.0.1 ::1
        handle @denied {
                respond "No access"
        }
}

Then inside your website configuration just add

import block_outcoming

E.g.:

:80 {

import block_outcoming

root * /web/html

}

Accessing Windows Share (Samba) From Linux (XFCE) using Thunar

In order to access samba (windows share) from Linux XFCE using Thunar file browser you need to:

Install samba client

sudo apt install smbclient

Run:

sudo modprobe cifs

Open thunar and access the share:

smb://IPADDRESS/sharename

e.g.:

smb://192.168.1.1/photos

Type in the username / password in the prompt window

Some helpful information:

https://forum.manjaro.org/t/thunar-how-do-i-access-folders-shared-on-windows-computers/116649/9

Caddy web server configuration for rewrite with multiple variables parameters

http://localwebsite {
        # Set this path to your site's directory.
        #root * /usr/share/caddy
        root * /var/www/website

       #makes /course/id/name.html to /course.php?id=id&name=name
        @zxp {
                #path_regexp myregex ^/course/(\d+)/([%A-Za-z0-9_-]+)\.html$
                path_regexp myregex ^/course/([^/]+)/([^/]+)\.html$
        }
        rewrite @zxp /course.php?id={re.myregex.1}&name={re.myregex.2}

        # rewrite /course/([^/]+)/([^/]+)\.html /course.php?id={1}&name={2}
        # rewrite /course/(\d+)/([A-Za-z0-9_-]+)\.html /course.php?id=$1&name=$2
        # rewrite /course/(\w+)/(\w+)\.html /course.php?id={1}&name={2}

        #rewrite /course {
        #       r ^/(\w+)/(\w+)\.html$
        #       to /course.php?id={1}&name={2}
        #}

        # Enable the static file server.
        file_server

        # Another common task is to set up a reverse proxy:
        # reverse_proxy localhost:8080

        # Or serve a PHP site through php-fpm:
        # php_fastcgi localhost:9000
        php_fastcgi unix//run/php/php8.1-fpm.sock
}

breakpo