[Help] OpenHAB Exec Rule Fails to Run MySQL Command

milo · February 16, 2025, 8:26am

Problem Description

I’m trying to create a JavaScript rule in OpenHAB that runs every day at 1 AM to delete old records from a MySQL database. The rule executes the following command:

mysql --batch -B -u MYUSER -p'MYPASSWORD' -D 'MYDATABASE' -e "DELETE FROM 'MYITEM' WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY;"

The command works fine when run manually in the terminal. However, when OpenHAB executes it using actions.Exec.executeCommandLine(), the output is null and the deletion does not happen.

My JavaScript Rule

rules.JSRule({
    name: "clean MySQL at 1 AM",
    description: "Deletes records from the day before yesterday at 1 AM",
    triggers: [
        triggers.GenericCronTrigger("0/10 * * * * ?") // Runs at 1:00 AM daily
    ],
    execute: (event) => {
        let command = mysql --batch -B -u MYUSER -p'MYPASSWORD' -D 'MYDATABASE' -e "DELETE FROM 'MYITEM' WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY;";
        actions.Log.logInfo("MySQL Cleanup", "Executing MySQL delete command...");
        let result = actions.Exec.executeCommandLine(time.Duration.ofSeconds(300), command); // Timeout set to 5 seconds
        actions.Log.logInfo("MySQL Cleanup", "Command output: " + result);
    },
  tags: ["MySQL CleanUp at 1 am"],
id: "clean MySQL"
});

What I’ve Tried

Running the command manually in the terminal → Works fine
Checking OpenHAB logs (tail -f /var/log/openhab/openhab.log) → Command output is null [rg.openhab.core.io.net.exec.ExecUtil] - Failed to execute commandLine '[mysql --batch -B -u MYUSER -p'MYPASSWORD' -D 'MYDATABASE' -e "DELETE FROM 'MYITEM' WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY;"]'
added to exec.whitelist → Still fails
Using the full path /usr/bin/mysql → Still fails

Questions for the Community

Why does actions.Exec.executeCommandLine() return null when executing MySQL?
Does OpenHAB require any special permissions for MySQL?
Would it be better to use a different approach (e.g., a cron job outside OpenHAB)?

Any help would be greatly appreciated!

If anyone has successfully run MySQL commands from an OpenHAB rule, I’d love to hear how you did it!

pacive · February 16, 2025, 8:41am

The command variable should be an array of strings with the arguments:

let command = [ "mysql", "--batch", "-B", "-u", "MYUSER", "-p", "'MYPASSWORD'", "-D", "'MYDATABASE'", "-e", "DELETE FROM 'MYITEM' WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY;" ];

It may work to have it as a single string as well, but the way you have it written now I’m surprised that the rule doesn’t throw an error as it would try to interpret it as code.

milo · February 16, 2025, 9:07am

okay changed it but still

Command output: null

jimtng · February 16, 2025, 9:17am

show your updated script

milo · February 16, 2025, 9:20am

rules.JSRule({
    name: "clean MySQL at 1 AM",
    description: "Deletes records from the day before yesterday at 1 AM",
    triggers: [
        triggers.GenericCronTrigger("0/10 * * * * ? *") // Runs at 1:00 AM daily
    ],

    execute: (event) => {
        let command = [ "mysql", "--batch", "-v", "-u", "'MYUSER'", "-p", "'MYPASSWORD'", "-D", "'MYDATABASE'", "-e", "DELETE FROM item0321 WHERE DATE(time) = CURDATE() - INTERVAL 2 D>
        actions.Log.logInfo("MySQL Cleanup", "Executing MySQL delete command...");
        let result = actions.Exec.executeCommandLine(command); // Timeout set to 5 seconds
        actions.Log.logInfo("MySQL Cleanup", "Command output: " + result);
    },
  tags: ["MySQL CleanUp at 1 am"],
id: "clean MySQL"
});

jimtng · February 16, 2025, 9:34am

Try this

rules.JSRule({
    name: "clean MySQL at 1 AM",
    description: "Deletes records from the day before yesterday at 1 AM",
    triggers: [
        triggers.GenericCronTrigger("0/10 * * * * ? *") // Runs at 1:00 AM daily
    ],

    execute: (event) => {
        const user = "MYUSER";
        const password = "MYPASSWORD"; // Do not add extra single quotes unless that's literally a part of your password
        const database = "MYDATABASE"; 
        let command = [ "mysql", "--batch", "-v", "-u", user, "-p", password, "-D", database, "-e", "DELETE FROM item0321 WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY" ];
        actions.Log.logInfo("MySQL Cleanup", "Executing MySQL delete command...");
        let result = actions.Exec.executeCommandLine(time.Duration.ofSeconds(5000), command); // Timeout set to 5 seconds
        actions.Log.logInfo("MySQL Cleanup", "Command output: " + result);
    },
    tags: ["MySQL CleanUp at 1 am"], // this is a bad tag
    id: "clean MySQL" // this is a very bad id. It should be /[A-Za-z0-9_]+/
});

milo · February 16, 2025, 9:47am

same

2025-02-16 10:47:20.644 [INFO ] [nhab.core.model.script.MySQL Cleanup] - Executing MySQL delete command...
2025-02-16 10:47:20.654 [INFO ] [nhab.core.model.script.MySQL Cleanup] - Command output: null

rlkoshak · February 17, 2025, 3:53pm

Under what user?

executeCommandLine doesn’t use the whitelist.

Can user openhab run the command? Use sudo -u openhab mysql ... to run mysql as user openhab.

Try sudo -u openhab mysql to test it out. Build up your command and make sure that user openhab can do what it needs to do.

Probably. I think you can configure MySQL itself to do stuff like this.

But it should work from a rule as well.

It’s possible this isn’t going to work. That will end up passing a single argument consisting of an array to executeCommandLine. I’m not sure it will split each element of the array to a separate function argument in the call to executeCommandLine. But that’s easy enough to test out.

Furthermore, you’ve dropped the Duration in the call to executeCommandLine so this becomes a fire and forget call with no capture of the output from the command. result is always going to be null without the Duration to have executeCommandLine wait for the command to exit or time out.

Finally, I assume the let command = line got truncated. If it’s as written it’s an incomplete line. You are missing closing quotes and closing ] at a minimum. Make sure to look at that and make sure it exactly matches what @pacive posted above.

So first try the command using sudo -u openhab to verify the command can be run by user openhab.

Next, fix the command array so that it’s complete assuming the code wasn’t truncated when you pasted it to the forum.

let result = actions.Exec.executeCommandLine(time.Duration.ofSeconds(5), command);

Finally, if that doesn’t work, try

let result = actions.Exec.executeCommandLine(time.Duration.ofSeconds(5),  "mysql", "--batch", "-B", "-u", "MYUSER", "-p", "'MYPASSWORD'", "-D", "'MYDATABASE'", "-e", "DELETE FROM 'MYITEM' WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY;");

milo · February 17, 2025, 4:41pm

Hello,

user openhab of course.

sudo -u openhab mysql --batch -v -u user -ppassword -D database -e "DELETE FROM item0321 WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY;"
--------------
DELETE FROM item0321 WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY
--------------

this

let result = actions.Exec.executeCommandLine(time.Duration.ofSeconds(5), command);

leads to an timeout, already tested

2025-02-17 17:48:10.028 [INFO ] [nhab.core.model.script.MySQL Cleanup] - Executing MySQL delete command...
2025-02-17 17:48:13.064 [WARN ] [rg.openhab.core.io.net.exec.ExecUtil] - Timeout occurred when executing commandLine '[mysql, --batch, -v, -u, openhabian, -p, 0p3nhab, -D, openHab, -e, DELETE FROM item0321 WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY]'
2025-02-17 17:48:13.066 [INFO ] [nhab.core.model.script.MySQL Cleanup] - Command output: null

this is same result

let result = actions.Exec.executeCommandLine(time.Duration.ofSeconds(5),  "mysql", "--batch", "-B", "-u", "MYUSER", "-p", "'MYPASSWORD'", "-D", "'MYDATABASE'", "-e", "DELETE FROM 'MYITEM' WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY;");

jimtng · February 17, 2025, 8:04pm

Ok there should be no separation between -p and the password

rules.JSRule({
    name: "clean MySQL at 1 AM",
    description: "Deletes records from the day before yesterday at 1 AM",
    triggers: [
        triggers.GenericCronTrigger("0/10 * * * * ? *") // Runs at 1:00 AM daily
    ],

    execute: (event) => {
        const user = "MYUSER";
        const password = "MYPASSWORD"; // Do not add extra single quotes unless that's literally a part of your password
        const database = "MYDATABASE"; 
        let command = [ "mysql", "--batch", "-v", "-u", user, "-p" + password, "-D", database, "-e", "DELETE FROM item0321 WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY" ];
        actions.Log.logInfo("MySQL Cleanup", "Executing MySQL delete command...");
        let result = actions.Exec.executeCommandLine(time.Duration.ofSeconds(5000), command); // Timeout set to 5 seconds
        actions.Log.logInfo("MySQL Cleanup", "Command output: " + result);
    },
    tags: ["MySQL CleanUp at 1 am"], // this is a bad tag
    id: "clean MySQL" // this is a very bad id. It should be /[A-Za-z0-9_]+/
});

milo · February 17, 2025, 8:08pm

still the same

2025-02-17 21:07:00.733 [INFO ] [nhab.core.model.script.MySQL Cleanup] - Executing MySQL delete command...
2025-02-17 21:07:00.742 [INFO ] [nhab.core.model.script.MySQL Cleanup] - Command output: null

rules.JSRule({
    name: "clean MySQL at 1 AM",
    description: "Deletes records from the day before yesterday at 1 AM",
    triggers: [
        triggers.GenericCronTrigger("0/10 * * * * ? *") // Runs at 1:00 AM daily
    ],

    execute: (event) => {
        const user = "user";
        const password = "password"; // Do not add extra single quotes unless that's literally a part of your password
        const database = "database"; 
        let command = [ "mysql", "--batch", "-v", "-u", user, "-p" + password, "-D", database, "-e", "DELETE FROM item0321 WHERE DATE(time) = CURDATE() - INTERVAL 2 DAY" ];
        actions.Log.logInfo("MySQL Cleanup", "Executing MySQL delete command...");
        let result = actions.Exec.executeCommandLine(command); // Timeout set to 5 seconds
        actions.Log.logInfo("MySQL Cleanup", "Command output: " + result);
    },
    tags: ["MySQL CleanUp at 1 am"], // this is a bad tag
    id: "clean MySQL" // this is a very bad id. It should be /[A-Za-z0-9_]+/
});

jimtng · February 17, 2025, 8:12pm

You didn’t copy it exactly. You’ve omitted the duration

milo · February 17, 2025, 8:16pm

now its running…thanks a lot

jimtng · February 17, 2025, 8:24pm

Sorry please change the duration to 5 or 10 seconds not 5000. I got it mixed up with milliseconds

deswong · February 23, 2025, 6:52am

I have something similar - but I just used a MySQL Event to do so, this way it simplified things and I didn’t have to worry about Openhab managing this, and the database could do it and take as long as it needed to (as it varied depending on amount of data).

https://dev.mysql.com/doc/refman/8.4/en/events-overview.html#:~:text=MySQL%20Events%20are%20tasks%20that,a%20specific%20date%20and%20time.