Hotfix: MariaDB SQL Syntax Error

Date: November 27, 2025

Problem

When clicking START button on work order, MariaDB error occurred:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'machineStatus' at line 1

Root Cause

In Phase 3.3 implementation, we added a stateMsg object with topic: "machineStatus" to track the START/STOP button state. This message was being sent to output 4 of the "Work Order buttons" node, which connects to MariaDB.

MariaDB tried to execute msg.topic as a SQL query, but "machineStatus" is not valid SQL, causing the error.

Architecture Flow

The correct message flow is:

Work Order buttons
  ├─ Output 1 → Base64
  ├─ Output 2 → MariaDB + Calculate KPIs
  ├─ Output 3 → MariaDB + Calculate KPIs
  └─ Output 4 → MariaDB ❌ (This was receiving stateMsg)

Calculate KPIs
  ├─ Output 1 → Refresh Trigger + Record KPI History
  └─ (planned Output 2 for dual-path - not wired yet)

Refresh Trigger
  ├─ Output 1 → MariaDB (for SQL queries)
  └─ Output 2 → Back to UI

Back to UI
  └─ Output 1 → link out 3 → Home Template

Solution

Change 1: Remove stateMsg from Work Order buttons output 4

File: Work Order buttons function node

Before:

const stateMsg = {
    topic: "machineStatus",
    payload: {
        machineOnline: true,
        productionStarted: true,
        trackingEnabled: true
    }
};
return [null, msg, null, stateMsg];

After:

// Add state info to msg.payload instead
msg.payload.trackingEnabled = true;
msg.payload.productionStarted = true;
msg.payload.machineOnline = true;

return [null, msg, null, null];

Why: Avoids sending non-SQL message to MariaDB output. State flags now embedded in the main message that flows through Calculate KPIs → Refresh Trigger → Back to UI.

Change 2: Update Back to UI to extract trackingEnabled

File: Back to UI function node

Before:

if (mode === "production-state") {
    const homeMsg = {
        topic: "machineStatus",
        payload: {
            machineOnline: msg.machineOnline ?? true,
            productionStarted: !!msg.productionStarted
        }
    };
    return [null, homeMsg, null, null];
}

After:

if (mode === "production-state") {
    const homeMsg = {
        topic: "machineStatus",
        payload: {
            machineOnline: msg.machineOnline ?? true,
            productionStarted: !!msg.productionStarted,
            trackingEnabled: msg.payload?.trackingEnabled ?? msg.trackingEnabled ?? false
        }
    };
    return [null, homeMsg, null, null];
}

Why: Extracts trackingEnabled from the incoming message and includes it in the machineStatus message sent to Home Template.

Message Flow After Fix

User clicks START on Home Template → { action: "start" } → Work Order buttons
Work Order buttons processes START action:
- Sets global.set("trackingEnabled", true)
- Clears kpiBuffer
- Adds state flags to msg.payload:
```
msg.payload.trackingEnabled = true
msg.payload.productionStarted = true
msg.payload.machineOnline = true
```
- Sets msg._mode = "production-state"
- Returns [null, msg, null, null] → Output 2 → Calculate KPIs
Calculate KPIs calculates KPIs → Output 1 → Refresh Trigger
Refresh Trigger sees _mode === "production-state"
- Returns [null, msg] → Output 2 → Back to UI (NOT to MariaDB!)
Back to UI sees mode === "production-state"
- Extracts state flags
- Creates homeMsg with topic: "machineStatus"
- Returns [null, homeMsg, null, null] → Output 1 → link out 3 → Home Template
Home Template receives message with topic: "machineStatus"
- Updates scope.isProductionRunning = msg.payload.trackingEnabled
- Button changes from START to STOP ✅

No More Errors

✅ MariaDB only receives messages with valid SQL in msg.topic ✅ State messages (machineStatus) go only to Home Template ✅ START/STOP button state syncs correctly ✅ Buffer clearing on START still works

Testing

Click START button
- Should see: [START] Cleared kpiBuffer for fresh production run
- Should NOT see: MariaDB SQL syntax error
- Button should change to STOP
Production should start
- KPIs should update continuously
- No errors in debug panel

Files Modified

flows.json (2 nodes updated)
- Work Order buttons (9bbd4fade968036d)
- Back to UI (function node in Back to UI flow)

Status: HOTFIX COMPLETE ✅

4.7 KiB Raw Permalink Blame History