Projects-plastic/HOTFIX_MARIADB_ERROR.md

# 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:**
```javascript
const stateMsg = {
    topic: "machineStatus",
    payload: {
        machineOnline: true,
        productionStarted: true,
        trackingEnabled: true
    }
};
return [null, msg, null, stateMsg];
```

**After:**
```javascript
// 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:**
```javascript
if (mode === "production-state") {
    const homeMsg = {
        topic: "machineStatus",
        payload: {
            machineOnline: msg.machineOnline ?? true,
            productionStarted: !!msg.productionStarted
        }
    };
    return [null, homeMsg, null, null];
}
```

**After:**
```javascript
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

1. **User clicks START** on Home Template
   → `{ action: "start" }` → Work Order buttons

2. **Work Order buttons** processes START action:
   - Sets `global.set("trackingEnabled", true)`
   - Clears `kpiBuffer`
   - Adds state flags to `msg.payload`:
     ```javascript
     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

3. **Calculate KPIs** calculates KPIs
   → Output 1 → Refresh Trigger

4. **Refresh Trigger** sees `_mode === "production-state"`
   - Returns `[null, msg]`
   → Output 2 → Back to UI (NOT to MariaDB!)

5. **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

6. **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

1. Click START button
   - Should see: `[START] Cleared kpiBuffer for fresh production run`
   - Should NOT see: MariaDB SQL syntax error
   - Button should change to STOP

2. 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 ✅