Turn LED on/ off when inserting a record into a MySQL database on a Raspberry Pi

Question

I am trying to switch on an LED whenever a record is inserted into a MySQL database.

Currently I am using a Trigger with the MySQL UDF sys_exec() to run a python script, which all works fine, unless i want to turn on a GPIO pin.

Below are 2 python scripts (both identical except for one has the GPIO setup and GPIO output lines commented out).

import RPi.GPIO as GPIO
import time

pin = 21
GPIO.setmode(GPIO.BCM)
GPIO.setwarnings(False)
GPIO.setup(pin, GPIO.OUT)

GPIO.output(pin, GPIO.HIGH)
time.sleep(0.25)
GPIO.output(pin, GPIO.LOW)

This script will run fine when executed from the python Shell, but fails when the trigger executes it, and sys_exec will return 256 (which I believe means it's false?)

import RPi.GPIO as GPIO
import time

pin = 21
GPIO.setmode(GPIO.BCM)
GPIO.setwarnings(False)
# GPIO.setup(pin, GPIO.OUT)

# GPIO.output(pin, GPIO.HIGH)
time.sleep(0.25)
# GPIO.output(pin, GPIO.LOW)

This one runs fine from the trigger.

Here is the code for my trigger:

DELIMITER $$
CREATE TRIGGER test_trig
AFTER INSERT ON LED_TABLE
FOR EACH ROW
    BEGIN
        DECLARE cmd CHAR(255);
        DECLARE result int(10);
        SET cmd = ('python /home/pi/test.py');
        SET result = sys_exec(cmd);
    END;
$$
DELIMITER ;

Does anyone know as to why when I try and activate the GPIO pins from the trigger it will fail?

EDIT:

So the issue was caused by the mysql user not having permissions to use the GPIO pins. I resolved this by adding the mysql user to the gpio user group.

I found the answer here:

https://www.raspberrypi.org/forums/viewtopic.php?f=27&t=16013

For those who may run into this issue in the future, the command you want to run is:

usermod -a -G gpio mysql

Answer 1

I found the Answer,

So the issue was caused by the mysql user not having permissions to use the GPIO pins. I resolved this by adding the mysql user to the gpio user group.

I found the answer here:

https://www.raspberrypi.org/forums/viewtopic.php?f=27&t=16013

For those who may run into this issue in the future, the command you want to run is:

usermod -a -G gpio mysql