Load restapi_routes from config into runtime on startup

[Copilot]

restapi_routes defined in proxysql.cnf were parsed inconsistently and did not support the config shape used in deployment manifests, which left runtime_restapi_routes empty after startup. This change aligns REST API route loading with the expected config format and startup behavior used by other config-backed sections.

• Config parsing

  • Accepts restapi_routes as a config section, while preserving support for the legacy restapi section.
  • Allows config-defined REST API routes to omit id, matching the common SQL workaround and letting SQLite assign it.

• Runtime startup behavior

  • Makes config-file route definitions usable at startup by ensuring they are inserted into restapi_routes in the shape expected by the existing runtime load path.

• Safety hardening in the touched path

  • Escapes route string fields before inserting into SQLite.
  • Adds allocation checks and bounded formatting in the config import path.

• Coverage

  • Extends config-load validation coverage to include LOAD RESTAPI FROM CONFIG.
  • Adds a startup-focused TAP test that launches a secondary ProxySQL instance from config and verifies runtime_restapi_routes is populated.

Example config now supported directly:

restapi_routes=
(
    {
        active=1
        timeout_ms=5000
        method="GET"
        uri="healthz"
        script="/etc/proxysql/probe.bash"
        comment="health check"
    }
)

With this format, the route is loaded into restapi_routes from config and becomes available in runtime_restapi_routes on startup.

Warning

Firewall rules blocked me from connecting to one or more addresses (expand for details)

I tried to connect to the following addresses, but was blocked by firewall rules:

• cdn.mysql.com
  • Triggering command: /usr/bin/curl curl -C - -O -s REDACTED (dns block)
  • Triggering command: /usr/bin/wget wget -nc -q REDACTED (dns block)

If you need me to access, download, or install something from one of these locations, you can either:

• Configure Actions setup steps to set up my environment, which run before the firewall is enabled
• Add the appropriate URLs or hosts to the custom allowlist in this repository's Copilot coding agent settings (admins only)

Original prompt

---

This section details on the original issue you should resolve

<issue_title>Feature request: Load restapi_routes from config file</issue_title>
<issue_description>## Summary

restapi_routes defined in the ProxySQL config file (proxysql.cnf) are parsed but NOT loaded into runtime on startup. This requires users to manually execute INSERT INTO restapi_routes + LOAD RESTAPI TO RUNTIME via the admin interface after ProxySQL starts.

Other config sections like mysql_query_rules, mysql_servers, mysql_users, mysql_aws_aurora_hostgroups, and scheduler are all automatically loaded from the config file to runtime on startup. It would be consistent and convenient if restapi_routes followed the same pattern.

Steps to Reproduce

1. Add restapi_routes section to proxysql.cnf:

restapi_routes=
(
    {
        active=1
        timeout_ms=5000
        method="GET"
        uri="healthz"
        script="/etc/proxysql/probe.bash"
        comment="health check"
    }
)

2. Start ProxySQL with --reload:

proxysql --config /etc/proxysql/proxysql.cnf -f --reload

3. Check runtime:

SELECT * FROM runtime_restapi_routes;
-- Returns empty result set

4. The REST API endpoint returns:

{"error":"The script for method [GET] and route [/sync/healthz] was not found. Rows count returned [0]"}

Expected Behavior

restapi_routes defined in the config file should be automatically loaded into runtime on startup, just like mysql_query_rules, scheduler, and other config sections.

Current Workaround

Execute SQL via admin interface after ProxySQL starts:

INSERT INTO restapi_routes (active, timeout_ms, method, uri, script, comment)
VALUES (1, 5000, 'GET', 'healthz', '/etc/proxysql/probe.bash', 'health check');
LOAD RESTAPI TO RUNTIME;

Environment

• ProxySQL version: 3.0.6
• OS: Kubernetes (proxysql/proxysql:3.0.6 Docker image)
• Config: Using --reload flag

Use Case

In Kubernetes deployments, health checks rely on REST API routes. Having to use an init script to register routes adds complexity to the deployment. Declarative config would simplify Helm charts and reduce startup failure modes.</issue_description>

<agent_instructions>We need to implement this feature. Also include extensive testing</agent_instructions>

Comments on the Issue (you are @copilot in this section)

• Fixes Feature request: Load restapi_routes from config file #5466

---

💬 Send tasks to Copilot coding agent from Slack and Teams to turn conversations into code. Copilot posts an update in your thread when it's finished.

Summary by CodeRabbit

• New Features

  • REST API routes now load from restapi_routes section with fallback to restapi for backward compatibility.
  • Routes can now be defined with optional identifier fields.

• Improvements

  • Enhanced validation and sanitization of REST API route parameters.
  • Improved error handling during configuration loading with proper cleanup on failures.

• Tests

  • Added comprehensive test coverage for REST API configuration loading and startup behavior.

[renecannao]

@CodeRabbit review

[coderabbitai]

✅ Actions performed

Review triggered.

Note: CodeRabbit is an incremental review system and does not re-review already reviewed commits. This command is applicable only when automatic reviews are paused.

[coderabbitai]

Important

Review skipped

Bot user detected.

To trigger a single review, invoke the @coderabbitai review command.

⚙️ Run configuration

Configuration used: Organization UI

Review profile: CHILL

Plan: Pro

Run ID: 445507d1-0102-412a-ae55-4fac3c7061ae

You can disable this status message by setting the reviews.review_status to false in the CodeRabbit configuration file.

Use the checkbox below for a quick retry:

• 🔍 Trigger review

📝 Walkthrough

Walkthrough

These changes implement automatic loading of REST API routes from the ProxySQL config file to runtime on startup. The feature supports optional id fields in route definitions and adds string sanitization to escape single quotes in route parameters.

Changes

Cohort / File(s)	Summary
REST API Config Loading lib/ProxySQL_Config.cpp	Modified Read_Restapi_from_configfile() to load routes from restapi_routes table with fallback to restapi, support optional id fields via conditional SQL templates, sanitize string parameters (method, uri, script, comment) by escaping single quotes, add memory allocation error handling, and properly size query buffers for escaped strings.
Config Validation Tests test/tap/tests/test_load_from_config_validation-t.cpp	Extended validation tests to include restapi_routes section in config, added cleanup step for existing rows, and verification that routes load correctly via LOAD RESTAPI FROM CONFIG followed by SELECT * FROM restapi_routes.
Startup Integration Test test/tap/tests/test_load_restapi_from_config_startup-t.cpp	New integration test that verifies REST API routes load from config on ProxySQL startup, validates route fields (active, timeout, method, URI, script, comment) in both restapi_routes and runtime_restapi_routes tables, and confirms proper shutdown behavior.

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~20 minutes

Possibly related PRs

• Protocol encoding/decoding and utility unit tests (Phase 2.5) #5488: Adds unit tests for escape_string_single_quotes(), which is the string sanitization utility introduced and used in the main config loading logic of this PR.

Poem

🐰 Routes hop from config files with glee,
No SQL scripts needed, they load for free!
With quotes all escaped, our strings stand tall,
REST API health checks answer the call! ✨

🚥 Pre-merge checks | ✅ 4 | ❌ 1

❌ Failed checks (1 warning)

Check name	Status	Explanation	Resolution
Docstring Coverage	⚠️ Warning	Docstring coverage is 0.00% which is insufficient. The required threshold is 80.00%.	Write docstrings for the functions missing them to satisfy the coverage threshold.

✅ Passed checks (4 passed)

Check name	Status	Explanation
Description Check	✅ Passed	Check skipped - CodeRabbit’s high-level summary is enabled.
Title check	✅ Passed	The title clearly summarizes the main change: loading restapi_routes from config into runtime on startup, which is the primary objective of this PR.
Linked Issues check	✅ Passed	The PR fully addresses issue #5466 by implementing automatic loading of restapi_routes from config on startup, including support for routes with optional id and proper string escaping.
Out of Scope Changes check	✅ Passed	All code changes directly support the stated objective of loading restapi_routes from config into runtime; no unrelated modifications detected.

_{✏️ Tip: You can configure your own custom pre-merge checks in the settings.}

✨ Finishing Touches

🧪 Generate unit tests (beta)

• Create PR with unit tests
• Commit unit tests in branch copilot/feature-load-restapi-routes-config

---

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

• X
• Mastodon
• Reddit
• LinkedIn

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

[sonarqubecloud]

Quality Gate failed

Failed conditions
7 Security Hotspots

See analysis details on SonarQube Cloud

[Copilot]

Pull request overview

Aligns REST API route loading with the config formats used in deployments so that restapi_routes defined in proxysql.cnf are inserted into restapi_routes on startup and therefore show up in runtime_restapi_routes without requiring manual admin SQL.

Changes:

• Update RESTAPI config read/write to prefer restapi_routes (while still accepting legacy restapi) and allow omitting id.
• Harden config import by validating timeout_ms, escaping string fields before SQLite insertion, and adding allocation checks/bounded formatting.
• Add TAP coverage for LOAD RESTAPI FROM CONFIG validation and for startup population of runtime_restapi_routes.

Reviewed changes

Copilot reviewed 4 out of 4 changed files in this pull request and generated 1 comment.

File	Description
lib/ProxySQL_Config.cpp	Reads restapi_routes/restapi from config and writes routes back out as restapi_routes; adds escaping and validation during import.
test/tap/tests/test_load_restapi_from_config_startup-t.cpp	New startup-focused TAP test that launches a secondary ProxySQL from a generated config and asserts runtime_restapi_routes is populated.
test/tap/tests/test_load_from_config_validation-t.cpp	Extends existing config-load validation coverage to include LOAD RESTAPI FROM CONFIG.
test/tap/groups/groups.json	Registers the new startup TAP test in existing TAP groups.

---

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

[Copilot]

Allowing config routes without id means LOAD RESTAPI FROM CONFIG (and startup with --reload + persistent datadir) will INSERT new rows every time, because restapi_routes has no UNIQUE constraint besides the autoincrement id. This can accumulate duplicate (method, uri) entries and break REST API routing (the runtime lookup expects exactly 1 matching row). Consider making config import idempotent when id is omitted (e.g., clear restapi_routes before importing, or upsert by (method, uri) via a deterministic id / UNIQUE index).

Suggested change

	admindb->execute("PRAGMA foreign_keys = OFF");
	admindb->execute("PRAGMA foreign_keys = OFF");
	// Make import idempotent: clear existing routes so repeated LOAD RESTAPI FROM CONFIG
	// (or reload on startup) does not accumulate duplicate entries.
	admindb->execute("DELETE FROM restapi_routes");