This commit fixes compilation errors when building with PROXYSQLGENAI=1:
Header file fixes:
- Add missing #endif /* PROXYSQLGENAI */ before header guards in multiple headers
- Remove #include cpp.h from GenAI headers to avoid circular dependencies
Source file fixes:
- Add #include proxysql.h to GenAI .cpp files that were missing it
- Add #include Static_Harvester.h to Query_Tool_Handler.cpp for forward decl
Build system fixes:
- Remove vec.o from libproxysql.a (it's linked separately in src/Makefile)
- Prevents duplicate symbol errors during linking
Test files:
- Rename MCP test files with .sh suffix to prevent make clean deletion
Both build modes now work:
- make build_lib_debug (without GenAI)
- PROXYSQLGENAI=1 make build_lib_debug (with GenAI)
This commit merges the experimental v4.0 GenAI/MCP features into the stable
v3.0 branch using conditional compilation. All v4.0 features are disabled by
default and only enabled when PROXYSQLGENAI=1 is set at compile time.
Changes:
Build System:
- Modified main Makefile to pass PROXYSQLGENAI flag to sub-makefiles
- Modified deps/Makefile to conditionally build sqlite-vec and sqlite-rembed
- Modified lib/Makefile to add PSQLGA flag and include GenAI object files
- Modified src/Makefile to add PSQLGA flag and conditional linking
Headers (wrapped with #ifdef PROXYSQLGENAI):
- All 20 new GenAI header files in include/
- Modified cpp.h, proxysql_glovars.hpp, proxysql_admin.h
- Modified ProxySQL_Admin_Tables_Definitions.h for GenAI/MCP tables
Source Files:
- All 22 new GenAI source files in lib/ wrapped with #ifdef PROXYSQLGENAI
- Modified src/main.cpp for conditional global variables and init/shutdown
- Modified Admin_Handler.cpp for conditional command handlers
- Modified Admin_Bootstrap.cpp for conditional table registration
- Modified Admin_FlushVariables.cpp for conditional variable flushing
- Modified ProxySQL_Admin.cpp for conditional admin methods
- Modified ProxySQL_Admin_Stats.cpp for conditional MCP stats functions
- Modified proxy_sqlite3_symbols.cpp to always compile (needed by core)
- Modified MySQL_Session.cpp for conditional GenAI function calls
Test Files:
- Renamed test_mcp_query_rules-t to test_mcp_query_rules-t.sh
- Renamed test_mcp_rag_metrics-t to test_mcp_rag_metrics-t.sh
- Modified anomaly_detection-t.cpp for conditional test execution
Usage:
# Build without GenAI (v3.0 mode - default)
make clean && make build_deps -j$(nproc) && make build_lib -j$(nproc) && make build_src -j$(nproc)
# Build with GenAI (v4.0 mode)
make clean && PROXYSQLGENAI=1 make build_deps -j$(nproc) && PROXYSQLGENAI=1 make build_lib -j$(nproc) && PROXYSQLGENAI=1 make build_src -j$(nproc)
- `sqlite-vec` requires that `knn` queries (using the `MATCH` operator for
vector similarity search) must have a `LIMIT` clause at the same query
level as the `MATCH` clause.
- Execute `knn` queries as a subquery and then do `JOIN`s with
`rag_chunks` and `rag_documents`.
Signed-off-by: Wazir Ahmed <wazir@proxysql.com>
This commit addresses critical and important security vulnerabilities found
during comprehensive code review of the Gen AI features merge.
Critical fixes:
- SQL injection vulnerabilities in MySQL_Tool_Handler.cpp:
- Added validate_sql_identifier() for schema/table validation
- Added escape_string() for MySQL string escaping using mysql_real_escape_string
- Fixed list_tables(), describe_table(), sample_rows(), sample_distinct()
- SQL injection vulnerabilities in Query_Tool_Handler.cpp:
- Added validate_sql_identifier_sqlite() for identifier validation
- Added escape_string_literal() for SQLite string escaping
- Fixed list_tables tool and catalog.get_relationships function
- Use-after-free race condition in GenAI_Thread:
- Changed shutdown_ from int to std::atomic<int> for proper memory ordering
- Added additional shutdown check in worker_loop after popping request
Important fixes:
- Buffer overflow risks from sprintf usage:
- Converted all sprintf() calls to snprintf() in GenAI_Thread.cpp
- Converted sprintf() to snprintf() in MySQL_Session.cpp
- Worker loop shutdown race condition:
- Added shutdown check after popping request from queue
- Properly clean up client_fd when shutdown is detected
These fixes ensure:
1. All user input is properly validated before use in SQL queries
2. String values are properly escaped using database-specific escaping
3. Thread-safe shutdown with proper memory ordering guarantees
4. Bounds-safe string formatting to prevent buffer overflows
Add escape_sql_string() helper function that doubles single quotes
to prevent SQL injection when strings are used in SQL string
concatenation. Update harvest_view_definitions to use this function
for view_def, schema_name, and view_name.
This prevents SQL injection in the UPDATE statement that stores
view definitions in the catalog.
Addresses coderabbitai review comment.
Add proxy_sqlite3_enable_load_extension and
proxy_sqlite3_global_stats_row_step to the #else (MAIN_PROXY_SQLITE3)
branch in sqlite3db.h to maintain symmetry with the extern declarations
and prevent ODR violations.
Addresses coderabbitai review comment.
- lib/MySQL_Catalog.cpp: Convert search/list/remove to use SQLite prepared
statements instead of string concatenation for user parameters
- lib/RAG_Tool_Handler.cpp: Add escape_fts_query() function to properly
escape single quotes in FTS5 MATCH clauses; update all FTS and vector
MATCH queries to use escaped values
- lib/Static_Harvester.cpp: Add is_valid_schema_name() validation function
to ensure schema names only contain safe characters (alphanumeric,
underscore, dollar sign) before using in INFORMATION_SCHEMA queries
- lib/Query_Tool_Handler.cpp: Add clarifying comments to validate_readonly_query
explaining the blacklist (quick exit) + whitelist (allowed query types) approach
- Remove backup file lib/Anomaly_Detector.cpp.bak
Addresses gemini-code-assist review comments from PR #26.
Detailed changes:
- Defined UNAME_S in root Makefile to ensure correct platform guards.
- Isolated Darwin-specific OpenSSL detection and version checks.
- Enabled ICU support for PostgreSQL on Darwin.
- Added Darwin guards for MariaDB client zlib patches.
- Restored original Linux behavior for all build scripts.
- Added new Darwin-specific patches for MariaDB client.
- Address SQL injection vulnerabilities by adding input validation and escaping
- Fix configuration variable handling in get_variable and set_variable methods for RAG variables
- Make embedding dimension configurable for rag_vec_chunks table
- Remove code duplication in SQL filter building logic by creating consolidated build_sql_filters function
- Update all search tools (FTS, vector, hybrid) to use consolidated filter building
Issue
-----
- ProxySQL only supports the `tools` feature of the MCP protocol
and does not support features such as `prompts` and `resources`.
- Although ProxySQL expresses this in its `initialize` response,
(server capabilities list contains only the `tools` object),
clients such as Warp Terminal ignore it and continue to send
requests for methods such `prompts/list` and `resources/list`.
- Any response other than `HTTP 200 OK` is treated as an error
and client fails to initialize.
Fix
---
- Handle prompt and resource list requests by returning an empty array.
Signed-off-by: Wazir Ahmed <wazir@proxysql.com>
- Enhanced inline Doxygen comments in RAG_Tool_Handler.h and RAG_Tool_Handler.cpp
- Added detailed parameter descriptions, return values, and cross-references
- Created Doxyfile for documentation generation
- Added documentation summary and guidelines
- Documented all RAG tools with their schemas and usage patterns
- Added security and performance considerations documentation
The RAG subsystem is now fully documented with comprehensive Doxygen comments
that provide clear guidance for developers working with the codebase.
Change function signature from stats___mcp_query_rules(bool reset) to
stats___mcp_query_rules() to match MySQL query rules pattern.
The reset parameter was never used in the function body and MySQL's
stats___mysql_query_rules() has no reset parameter.
- Add ADMIN_SQLITE_TABLE_RUNTIME_MCP_QUERY_RULES schema (17 columns, same as mcp_query_rules)
- Fix STATS_SQLITE_TABLE_MCP_QUERY_RULES to only have rule_id and hits columns
- Add runtime_mcp_query_rules detection and refresh in ProxySQL_Admin
- Implement save_mcp_query_rules_from_runtime(bool _runtime) for both config and runtime tables
- Update get_mcp_query_rules() to return 17 columns (no hits)
- get_stats_mcp_query_rules() returns 2 columns (rule_id, hits)
Mirrors the MySQL query rules pattern:
- mcp_query_rules: config table (17 cols)
- runtime_mcp_query_rules: runtime state (17 cols)
- stats_mcp_query_rules: hit counters (2 cols)
Add ability for the demo agent to learn new questions and add them
to the catalog, making it smarter over time.
Changes:
- Added get_last_agent_run_id() function to Discovery_Schema:
- Queries agent_runs table for the most recent agent_run_id for a run_id
- Returns 0 if no agent runs exist for the schema
- Updated llm.question_template_add handler:
- Made agent_run_id optional (defaults to 0 when not provided)
- When agent_run_id <= 0, auto-fetches last agent_run_id for the schema
- Returns helpful error if no agent run exists for the schema
- Returns agent_run_id in response for visibility
- Updated llm.question_template_add tool schema:
- Moved agent_run_id from required to optional parameters
- Updated description to explain auto-fetch behavior
- Updated demo_agent_claude.sh prompt:
- Added llm.question_template_add to available tools
- Added Step 4: "Learn from Success" to workflow
- Added explicit instruction to ALWAYS LEARN new questions
- Added example showing learning workflow
- Expanded from 4 steps to 5 steps to include learning
Now the demo agent can:
1. Search for existing questions
2. Reuse SQL if a good match exists
3. Generate new SQL if no good match
4. LEARN new questions by adding them to the catalog
5. Present results
This enables continuous learning - the more users interact with it,
the smarter it becomes.
Add optional schema parameter to run_sql_readonly tool that allows queries
to be executed against a specific schema, independent of the default schema
configured in mcp-mysql_schema.
Changes:
- Added current_schema field to MySQLConnection structure to track the
currently selected schema for each connection in the pool
- Added find_connection() helper to find connection wrapper by mysql pointer
- Added execute_query_with_schema() function that:
- Uses mysql_select_db() instead of 'USE schema' SQL statement
- Only calls mysql_select_db() if the requested schema differs from the
current schema (optimization to avoid unnecessary switches)
- Updates current_schema after successful schema switch
- Updated run_sql_readonly handler:
- Extracts optional 'schema' parameter
- Calls execute_query_with_schema() instead of execute_query()
- Returns error response when query fails (instead of success)
- Updated tool schema to document the new 'schema' parameter
This fixes the issue where queries would run against the default schema
(configured in mcp-mysql_schema) instead of the schema being queried,
causing "Table doesn't exist" errors when the default schema differs
from the discovered schema.
Enhance the llm_search MCP tool to return complete question template data
and optionally include full object schemas, reducing the need for additional
MCP calls when answering questions.
Changes:
- Added related_objects column to llm_question_templates table
- Updated add_question_template() to accept and store related_objects JSON array
- Enhanced fts_search_llm() with include_objects parameter:
- LEFT JOIN with llm_question_templates to return example_sql,
related_objects, template_json, and confidence
- When include_objects=true, fetches full object schemas (columns, indexes)
for all related objects in a single batch operation
- Added error checking for SQL execution failures
- Fixed fts_search_llm() get_object() call to pass schema_name and object_name
separately instead of combined object_key
- Updated Query_Tool_Handler:
- Added is_boolean() handling to json_int() helper to properly convert
JSON boolean true/false to int 1/0
- Updated llm.search handler to extract and pass include_objects parameter
- Updated llm.question_template_add to extract and pass related_objects
- Updated tool schemas to document new parameters
This change allows agents to get all necessary schema information in a single
llm_search call instead of making multiple catalog_get_object calls, significantly
reducing MCP call overhead.
- Rename llm_search_log column from \"limit\" to \"lmt\" to avoid SQL reserved keyword
- Add FTS inserts to all LLM artifact upsert functions:
- add_question_template(): index question templates for search
- add_llm_note(): index notes for search
- upsert_llm_summary(): index object summaries for search
- upsert_llm_domain(): index domains for search
- upsert_llm_metric(): index metrics for search
- Remove content='' from fts_llm table to store content directly
- Add <functional> header for std::hash usage
This fixes the bug where llm_search always returned empty results
because the FTS index was never populated.
Add query_tool_calls table to Discovery Schema to track all MCP tool
invocations via the /mcp/query/ endpoint. Logs:
- tool_name: Name of the tool that was called
- schema: Schema name (nullable, empty if not applicable)
- run_id: Run ID from discovery (nullable, 0 if not applicable)
- start_time: Start monotonic time in microseconds
- execution_time: Execution duration in microseconds
- error: Error message (null if success)
Modified files:
- Discovery_Schema.cpp: Added table creation and log_query_tool_call function
- Discovery_Schema.h: Added function declaration
- Query_Tool_Handler.cpp: Added logging after each tool execution
Extend the stats_mcp_query_tools_counters table with timing statistics
(first_seen, last_seen, sum_time, min_time, max_time) following the
same pattern as stats_mysql_query_digest.
All timing values are in microseconds using monotonic_time().
New schema:
- tool VARCHAR
- schema VARCHAR
- count INT
- first_seen INTEGER (microseconds)
- last_seen INTEGER (microseconds)
- sum_time INTEGER (microseconds - total execution time)
- min_time INTEGER (microseconds - minimum execution time)
- max_time INTEGER (microseconds - maximum execution time)
The MCP catalog database is now accessible as the 'mcp_catalog' schema
from the ProxySQL Admin interface, enabling direct SQL queries against
discovered schemas and LLM memories.
Remove the mcp-catalog_path configuration variable and hardcode the catalog
database path to datadir/mcp_catalog.db for stability.
Rationale: The catalog database is session state, not user configuration.
Runtime swapping of the catalog could cause tables to be missed and the
catalog to fail even if it was succeeding a second earlier.
Changes:
- Removed catalog_path from mcp_thread_variables_names array
- Removed mcp_catalog_path from MCP_Thread variables struct
- Removed getter/setter logic for catalog_path
- Hardcoded catalog path to GloVars.datadir/mcp_catalog.db in:
- ProxySQL_MCP_Server.cpp (Query_Tool_Handler initialization)
- Admin_FlushVariables.cpp (MySQL_Tool_Handler reinitialization)
- Updated VARIABLES.md to document the hardcoded path
- Updated configure_mcp.sh to remove catalog_path configuration
- Updated MCP README to remove catalog_path references
Add stats_mcp_query_tools_counters and stats_mcp_query_tools_counters_reset
tables to track MCP query tool usage statistics.
- Added get_tool_usage_stats_resultset() method to Query_Tool_Handler
- Defined table schemas in ProxySQL_Admin_Tables_Definitions.h
- Registered tables in Admin_Bootstrap.cpp
- Added pattern matching in ProxySQL_Admin.cpp
- Added stats___mcp_query_tools_counters() in ProxySQL_Admin_Stats.cpp
- Fixed friend declaration for track_tool_invocation()
- Fixed Discovery_Schema.cpp log_llm_search() to use prepare_v2/finalize
* Add full support for both HTTP and HTTPS modes in MCP server via the mcp_use_ssl configuration variable, enabling plain HTTP for development and HTTPS for production with proper certificate validation
* Server now automatically restarts when SSL mode or port configuration changes, fixing silent configuration failures where changes appeared to succeed but didn't take effect until manual restart.
Features:
- Explicit support for HTTP mode (mcp_use_ssl=false) without SSL certificates
- Explicit support for HTTPS mode (mcp_use_ssl=true) with certificate validation
- Configurable via configure_mcp.sh with --no-ssl or --use-ssl flags
- Settable via admin interface: SET mcp-use_ssl=true/false
- Automatic restart detection for SSL mode changes (HTTP ↔ HTTPS)
- Automatic restart detection for port changes (mcp_port)
- Add mcp_config.example.json for Claude Code MCP configuration
- Fix MCP bridge path in example config (../../proxysql_mcp_stdio_bridge.py)
- Update Two_Phase_Discovery_Implementation.md with correct Phase 1/Phase 2 usage
- Fix Two_Phase_Discovery_Implementation.md DELETE FROM fts_objects to scope to run_id
- Update README.md with two-phase discovery section and multi-agent legacy note
- Create static_harvest.sh bash wrapper for Phase 1
- Create two_phase_discovery.py orchestration script with prompts
- Add --run-id parameter to skip auto-fetch
- Fix RUN_ID placeholder mismatch (<USE_THE_PROVIDED_RUN_ID>)
- Fix catalog path default to mcp_catalog.db
- Add test_catalog.sh to verify catalog tools work
- Fix Discovery_Schema.cpp FTS5 syntax (missing space)
- Remove invalid CREATE INDEX on FTS virtual tables
- Add MCP tool call logging to track tool usage
- Fix Static_Harvester::get_harvest_stats() to accept run_id parameter
- Fix DELETE FROM fts_objects to only delete for specific run_id
- Update system prompts to say DO NOT call discovery.run_static
- Update user prompts to say Phase 1 is already complete
- Add --mcp-only flag to restrict Claude Code to MCP tools only
- Make FTS table failures non-fatal (check if table exists first)
- Add comprehensive documentation for both discovery approaches
- Rename NL2SQL_Converter to LLM_Bridge for generic prompt processing
- Update MySQL protocol handler from /* NL2SQL: */ to /* LLM: */
- Remove SQL-specific fields (sql_query, confidence, tables_used)
- Add GENAI_OP_LLM operation type to GenAI module
- Rename all genai_nl2sql_* variables to genai_llm_*
- Update AI_Features_Manager to use LLM_Bridge
- Deprecate ai_nl2sql_convert MCP tool with error message
- LLM bridge now handles any prompt type via MySQL protocol
This enables generic LLM access (summarization, code generation,
translation, analysis) while preserving infrastructure for future
NL2SQL implementation via Web UI + external agents.
- Add has_variable() method to GenAI_Threads_Handler for variable validation
- Add genai- prefix check in is_valid_global_variable()
- Auto-initialize NL2SQL converter when genai-nl2sql_enabled is set to true at runtime
- Make init_nl2sql() public to allow runtime initialization
- Mask API keys in logs (show only first 2 chars, rest as 'x')
Update flush_genai_variables___database_to_runtime() to match the MCP
pattern exactly:
- Add 'lock' parameter (default true) for flexibility
- Use ProxySQL_Admin's wrlock()/wrunlock() instead of GloGATH's
- Use consistent variable naming (var_name = name + 6 for 'genai-' prefix)
- Follow exact same locking pattern as MCP variables
This fixes the issue where runtime_global_variables table was not being
populated on startup because the locking pattern was incorrect.
This commit fixes a serious design flaw where AI configuration variables
were not integrated with the ProxySQL admin interface. All ai_*
variables have been migrated to the GenAI module as genai-* variables.
Changes:
- Added 21 new genai_* variables to GenAI_Thread.h structure
- Implemented get/set functions for all new variables in GenAI_Thread.cpp
- Removed internal variables struct from AI_Features_Manager
- AI_Features_Manager now reads from GloGATH instead of internal state
- Updated documentation to reference genai-* variables
- Fixed debug.cpp assertion for PROXY_DEBUG_NL2SQL and PROXY_DEBUG_ANOMALY
Variable mapping:
- ai_nl2sql_enabled → genai-nl2sql_enabled
- ai_anomaly_detection_enabled → genai-anomaly_enabled
- ai_features_enabled → genai-enabled
- All other ai_* variables follow the same pattern
The flush functions automatically handle all variables in the
genai_thread_variables_names array, so database persistence
works correctly without additional changes.
Related to: https://github.com/ProxySQL/proxysql-vec/pull/13
- Rename validate_provider_name to validate_provider_format for clarity
- Add null checks and error handling for all strdup() operations
- Enhance error messages with more context and HTTP status codes
- Implement performance monitoring with timing metrics for LLM calls and cache operations
- Add comprehensive test coverage for edge cases, retry scenarios, and performance
- Extend status variables to track performance metrics
- Update MySQL session to report timing information to AI manager
Add comprehensive structured logging for NL2SQL LLM API calls with
request correlation, timing metrics, and detailed error context.
Changes:
- Add request_id field to NL2SQLRequest with UUID-like auto-generation
- Add structured logging macros:
* LOG_LLM_REQUEST: Logs URL, model, prompt length with request ID
* LOG_LLM_RESPONSE: Logs HTTP status, duration_ms, response preview
* LOG_LLM_ERROR: Logs error phase, message, and status code
- Update call_generic_openai() signature to accept req_id parameter
- Update call_generic_anthropic() signature to accept req_id parameter
- Add timing metrics to both LLM call functions using clock_gettime()
- Replace existing debug logging with structured logging macros
- Update convert() to pass request_id to LLM calls
Request IDs are generated as UUID-like strings (e.g., "12345678-9abc-def0-1234-567890abcdef")
and are included in all log messages for correlation. This allows tracking
a single NL2SQL request through all log lines from request to response.
Timing is measured using CLOCK_MONOTONIC for accurate duration tracking
of LLM API calls, reported in milliseconds.
This provides much better debugging capability when troubleshooting
NL2SQL issues, as administrators can now:
- Correlate all log lines for a single request
- See exact timing of LLM API calls
- Identify which phase of processing failed
- Track request/response metrics
Fixes#2 - Add Structured Logging
Add comprehensive SQL validation with confidence scoring based on:
- SQL keyword detection (17 keywords covering DDL/DML/transactions)
- Structural validation (balanced parentheses and quotes)
- SQL injection pattern detection
- Length and quality checks
Confidence scoring:
- Base 0.4 for valid SQL keyword
- +0.15 for balanced parentheses
- +0.15 for balanced quotes
- +0.1 for minimum length
- +0.1 for FROM clause in SELECT statements
- +0.1 for no injection patterns
- -0.3 penalty for injection patterns detected
Low confidence (< 0.5) results are logged with detailed info.
Cache storage threshold updated to 0.5 confidence (from implicit valid_sql).
This improves detection of malformed or potentially malicious SQL
while providing granular confidence scores for downstream use.
Remove Ollama-specific provider code and use only generic OpenAI-compatible
and Anthropic-compatible providers. Ollama is now used via its
OpenAI-compatible endpoint at /v1/chat/completions.
Changes:
- Remove LOCAL_OLLAMA from ModelProvider enum
- Remove ai_nl2sql_ollama_model and ai_nl2sql_ollama_url variables
- Remove call_ollama() function from LLM_Clients.cpp
- Update default configuration to use OpenAI provider with Ollama URL
- Update all documentation to reflect generic-only approach
Configuration:
- ai_nl2sql_provider: 'openai' or 'anthropic' (default: 'openai')
- ai_nl2sql_provider_url: endpoint URL (default: Ollama OpenAI-compatible)
- ai_nl2sql_provider_model: model name
- ai_nl2sql_provider_key: API key (optional for local endpoints)
This simplifies the codebase by removing a separate code path for Ollama
and aligns with the goal of avoiding provider-specific variables.
- Add NL2SQL_Converter with prompt building and model selection
- Add LLM clients for Ollama, OpenAI, Anthropic APIs
- Update Makefile for new source files
- Add AI_Features_Manager coordinator class
- Add AI_Vector_Storage interface (stub)
- Add Anomaly_Detector class (stub for Phase 3)
- Update includes and main initialization
This commit extends the existing pg_cancel_backend() and pg_terminate_backend()
support to work with parameterized queries in the extended query protocol.
While literal PID values were already supported in both simple and extended
query protocols, this enhancement adds support for parameterized queries like
SELECT pg_cancel_backend($1).
- Remove incorrect separate module declarations for pgsql_replication_hostgroups and pgsql_hostgroup_attributes from ProxySQL_Cluster.hpp
- Remove separate module counter enums and atomic variables
- Remove separate module metrics definitions and configurations
- Remove incorrect admin variable implementations for non-distinct modules
- Verify that PostgreSQL tables correctly sync as part of unified pgsql_servers module
- Confirm build succeeds and no remaining references to removed infrastructure
These tables are part of the pgsql_servers module and should not be treated as
separate modules. The PgSQL_HostGroups_Manager::commit_update_checksums_from_tables()
function already correctly includes both tables in the unified checksum computation.
Resolves the architectural error where separate module infrastructure was added
for tables that are not distinct modules.
This commit addresses the remaining issues identified by AI reviews in PR 5234:
- Fixed CLUSTER_QUERY_RUNTIME_PGSQL_SERVERS macro to resolve numeric vs VARCHAR comparison issue
- Verified that PostgreSQL variables are already included in admin_variables_names array
- Fixed inconsistent gating/reset behavior for pgsql diffs when mysql checksum flags are disabled
(added pgsql diffs reset for checksum_mysql_query_rules, checksum_mysql_servers, checksum_mysql_users)
- Initialized atomic counters in ProxySQL_Cluster constructor to value 3
- Added missing get_peer_to_sync_pgsql_variables wrapper function (implementation + header declaration)
- Fixed test issues in test_cluster_sync_pgsql-t.cpp (NULL checks, tuple indexing, test plan)
- Updated PostgreSQL modules documentation to include pgsql_variables
- Standardized SQL string quoting to single quotes consistently
All compilation issues have been resolved and the codebase now has consistent
PostgreSQL cluster synchronization behavior across all modules.
Signed-off-by: Rene <rene@localhost>
- Add missing set_metrics() function to ProxySQL_Node_Entry class
- Fix undefined 'd_query' variable in PostgreSQL variables function
- Remove deprecated PostgreSQL functions integrated into pgsql_servers sync
- Add missing admin variables for PostgreSQL modules
- Fix structural compilation issues in set_checksums function
- PostgreSQL cluster sync now builds successfully for both debug and regular builds
Resolves linking errors and compilation failures that prevented
debug and regular builds from completing successfully.
Complete implementation of PostgreSQL replication hostgroups and hostgroup
attributes cluster synchronization functionality:
- Add CLUSTER_QUERY constants for replication hostgroups and hostgroup attributes
- Add checksum structures to ProxySQL_Node_Entry for tracking changes
- Add configuration variables for sync thresholds and save-to-disk settings
- Implement peer selection functions for both modules
- Implement core pull functions with complete error handling and metrics
- Add sync decision logic to cluster sync loop with conflict detection
- Add comprehensive metrics counters for success/failure tracking
- Integrate both modules into ChecksumModuleInfo array for unified processing
The implementation follows established patterns from other cluster sync modules
while providing PostgreSQL-specific functionality for replication hostgroup
management and hostgroup attribute synchronization.
Resolves critical gap in PostgreSQL cluster synchronization capabilities.
Add complete PostgreSQL variables cluster synchronization including:
- CLUSTER_QUERY_PGSQL_VARIABLES constant definition
- pull_pgsql_variables_from_peer() function implementation
- PostgreSQL variables sync decision logic in cluster sync loop
- Comprehensive error handling and metrics integration
- Runtime loading and save-to-disk support
- Interface filtering for cluster_sync_interfaces=false
This addresses the critical gap where PostgreSQL variables were not
being synchronized between ProxySQL cluster nodes, completing the
PostgreSQL module integration into the unified cluster architecture.
Add PostgreSQL interface variable filtering to match existing MySQL patterns:
- Define CLUSTER_SYNC_INTERFACES_PGSQL macro for 'pgsql-interfaces' variable
- Add PostgreSQL filtering to Admin_FlushVariables.cpp checksum generation
- Add PostgreSQL filtering to ProxySQL_Cluster.cpp sync operations
- Follows established pattern of excluding interface variables when cluster_sync_interfaces=false
This ensures that pgsql-interfaces variable is properly excluded from cluster synchronization
when interface filtering is enabled, maintaining consistency with MySQL and admin modules.
- Add PostgreSQL type handling in pull_global_variables_from_peer()
- Add PostgreSQL peer selection call using get_peer_to_sync_pgsql_variables()
- Integrate PostgreSQL into the data-driven variable sync architecture
This enables automatic peer discovery and selection for PostgreSQL
variables synchronization across cluster nodes.
- Fix SQL string literals in CLUSTER_QUERY_PGSQL_SERVERS_V2 to use single quotes
- Initialize all std::atomic<int> counters in ProxySQL_Cluster constructor to 3
- Add missing cluster_pgsql_variables_diffs_before_sync and cluster_pgsql_variables_save_to_disk admin variables
- Add get_peer_to_sync_pgsql_variables wrapper function for consistency
- Fix copy/paste log text error in set_variable function for mysql_query_rules
- Make PgSQL diffs consistent with MySQL diffs when checksum flags are disabled
- Remove TAP test calls from helper function, move assertions to main
- Fix tuple size mismatch in string_format calls (remove extraneous 12th element)
- Update test plan from 6 to 10 to match actual test count
Resolves all issues identified by gemini-code-assist and coderabbitai review comments.
The crash was caused by incorrect lock ordering. The admin version has:
1. wrlock() (acquire admin lock)
2. Process variables
3. checksum_mutex lock() (acquire checksum lock)
4. flush to runtime + generate checksum
5. checksum_mutex unlock() (release checksum lock)
6. wrunlock() (release admin lock)
The MCP version had the wrong order with the checksum_mutex lock outside
the wrlock/wrunlock region. This also added the missing 'lock' parameter
that exists in the admin version but was missing in MCP.
Changes:
- Added 'lock' parameter to flush_mcp_variables___database_to_runtime()
- Added conditional wrlock()/wrunlock() calls (if lock=true)
- Moved checksum generation inside the wrlock/wrunlock region
- Updated function signature in header file
Added missing documentation for MySQL connection pool implementation:
Header (MySQL_Tool_Handler.h):
- Added MySQLConnection struct documentation with member descriptions
- Added member variable documentation using ///< Doxygen style
Implementation (MySQL_Tool_Handler.cpp):
- Added Doxygen blocks for close() method
- Added Doxygen blocks for init_connection_pool() with detailed behavior
- Added Doxygen blocks for get_connection() with thread-safety notes
- Added Doxygen blocks for return_connection() with reuse behavior
- Added Doxygen blocks for execute_query() with JSON format documentation
All new connection pool methods now have complete @brief, @param, and
@return documentation following Doxygen conventions.
Added built-in connection pool to MySQL_Tool_Handler for direct MySQL
connections to backend servers.
Changes:
- Added MySQLConnection struct with MYSQL* pointer, host, port, in_use flag
- Added connection_pool vector, pool_lock mutex, pool_size counter
- Implemented init_connection_pool() to create MYSQL connections using mysql_init/mysql_real_connect
- Implemented get_connection() and return_connection() with thread-safe locking
- Implemented execute_query() helper method for executing SQL and returning JSON results
- Updated tool methods to use actual MySQL connections:
- list_schemas: Query information_schema.schemata
- list_tables: Query information_schema.tables with metadata
- describe_table: Query columns, primary keys, indexes
- sample_rows: Execute SELECT with LIMIT
- sample_distinct: Execute SELECT DISTINCT with GROUP BY
- run_sql_readonly: Execute validated SELECT queries
- explain_sql: Execute EXPLAIN queries
- Fixed MYSQL forward declaration (use typedef struct st_mysql MYSQL)
The connection pool creates one connection per configured host:port pair
with 5-second timeouts for connect/read/write operations.
- Add MCP variables to load_save_disk_commands map for LOAD/SAVE commands
- Add MCP variable validation in is_valid_global_variable() for SET commands
- Implement has_variable() method in MCP_Threads_Handler
- Add CHECKSUM command handlers for MCP VARIABLES (DISK/MEMORY/MEM)
Test results improved from 28 passed / 16 failed to 49 passed / 3 failed.
Remaining 3 failures are test expectation issues (boolean representation).
Remove unnecessary inheritance from MySQL_Threads_Handler. The MCP module
should be independent and not depend on MySQL/PostgreSQL thread handlers.
Changes:
- MCP_Threads_Handler now manages its own pthread_rwlock_t for synchronization
- Simplified init() signature (removed unused num/stack parameters)
- Added ProxySQL_Main_init_MCP_module() call in main initialization phase
- Include only standard C++ headers (pthread.h, cstring, cstdlib)
Add new MCP module supporting multiple MCP server endpoints over HTTPS
with JSON-RPC 2.0 protocol skeleton. Each endpoint (/mcp/config,
/mcp/observe, /mcp/query, /mcp/admin, /mcp/cache) is a distinct MCP
server with its own authentication configuration.
Features:
- HTTPS server using existing ProxySQL TLS certificates
- JSON-RPC 2.0 skeleton implementation (actual protocol TBD)
- 5 MCP endpoints with per-endpoint auth configuration
- LOAD/SAVE MCP VARIABLES admin commands
- Configuration file support (mcp_variables section)
Implementation follows GenAI module pattern:
- MCP_Threads_Handler: Main module handler with variable management
- ProxySQL_MCP_Server: HTTPS server wrapper using libhttpserver
- MCP_JSONRPC_Resource: Base endpoint class with JSON-RPC skeleton
- Add check_genai_events() function for non-blocking epoll_wait on GenAI response fds
- Integrate GenAI event checking into main handler() WAITING_CLIENT_DATA case
- Add goto handler_again to process multiple GenAI responses in one iteration
The async GenAI architecture is now fully integrated. MySQL threads no longer
block when processing GENAI: queries - they send requests asynchronously via
socketpair and continue processing other queries while GenAI workers handle
the embedding/reranking operations.
- Add GenAI_RequestHeader and GenAI_ResponseHeader protocol structures for socketpair communication
- Implement GenAI listener_loop to read requests from epoll and queue to workers
- Implement GenAI worker_loop to process requests and send responses via socketpair
- Add GenAI_PendingRequest state management to MySQL_Session/Base_Session
- Implement MySQL_Session async handlers: genai_send_async(), handle_genai_response(), genai_cleanup_request()
- Modify MySQL_Session genai handler to use async path when epoll is available
- Initialize GenAI epoll fd in Base_Session::init()
This completes the async architecture that was planned but never fully implemented
(previously had only placeholder comments). The GenAI module now processes
requests asynchronously without blocking MySQL threads.
Move all JSON parsing and operation routing logic from MySQL_Session to
GenAI module. MySQL_Session now simply passes GENAI: queries to the GenAI
module via process_json_query(), which handles everything autonomously.
This simplifies the architecture and achieves better separation of concerns:
- MySQL_Session: Detects GENAI: prefix and forwards to GenAI module
- GenAI module: Handles JSON parsing, operation routing, and result formatting
Changes:
- GenAI_Thread.h: Add GENAI_OP_JSON operation type, json_query field, and
process_json_query() method declaration
- GenAI_Thread.cpp: Implement process_json_query() with embed/rerank support
and document_from_sql framework (stubbed for future MySQL connection handling)
- MySQL_Session.cpp: Simplify genai handler to just call process_json_query()
and parse JSON result (reduces net code by ~215 lines)
This commit refactors the experimental GenAI query syntax to use a single
GENAI: keyword with type-based operations instead of separate EMBED: and RERANK: keywords.
Changes:
- Replace EMBED: and RERANK: detection with unified GENAI: detection
- Merge genai_embedding and genai_rerank handlers into single genai handler
- Add 'type' field to operation JSON ("embed" or "rerank")
- Add 'columns' field for rerank operation (2 or 3, default 3)
- columns=2: Returns only index and score
- columns=3: Returns index, score, and document (default)
Old syntax:
EMBED: ["doc1", "doc2"]
RERANK: {"query": "...", "documents": [...], "top_n": 5}
New syntax:
GENAI: {"type": "embed", "documents": ["doc1", "doc2"]}
GENAI: {"type": "rerank", "query": "...", "documents": [...], "top_n": 5, "columns": 2}
This provides a cleaner, more extensible API for future GenAI operations.
This commit adds experimental support for reranking documents directly
from MySQL queries using a special RERANK: syntax.
Changes:
- Add handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY___genai_rerank()
- Add RERANK: query detection alongside EMBED: detection
- Implement JSON parsing for query, documents array, and optional top_n
- Build resultset with index, score, and document columns
- Use MySQL ERR_Packet for error handling
Query format: RERANK: {"query": "search query", "documents": ["doc1", "doc2", ...], "top_n": 5}
Result format: 1 row per result, 3 columns (index, score, document)
This commit adds experimental support for generating embeddings directly
from MySQL queries using a special EMBED: syntax.
Changes:
- Add MYDS_INTERNAL_GENAI to MySQL_DS_type enum for GenAI connections
- Add handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY___genai_embedding()
- Implement EMBED: query detection and JSON parsing for document arrays
- Build CSV resultset with embeddings (1 row per document, 1 column)
- Add myconn NULL check in MySQL_Thread for INTERNAL_GENAI type
- Add "debug_genai" name to debug module array
- Remove HAVE_LIBCURL checks (libcurl is always statically linked)
- Use static curl header: "curl/curl.h" instead of <curl/curl.h>
- Remove curl_global_cleanup() from GenAI module (should only be in main())
Query format: EMBED: ["doc1", "doc2", ...]
Result format: 1 row per document, 1 column with CSV embeddings
Error handling uses MySQL ERR_Packet instead of resultsets.
This change adds compile-time detection and fallback to poll() on systems
that don't support epoll(), improving portability across different platforms.
Header changes (include/GenAI_Thread.h):
- Make sys/epoll.h include conditional on #ifdef epoll_create1
Implementation changes (lib/GenAI_Thread.cpp):
- Add poll.h include for poll() support
- Add EPOLL_CREATE compatibility macro (epoll_create1 or epoll_create)
- Add #include <poll.h> for poll() support
- Update init() to use pipe() for wakeup when epoll is not available
- Update register_client() to skip epoll_ctl when epoll is not available
- Update unregister_client() to skip epoll_ctl when epoll is not available
- Update listener_loop() to use poll() when epoll is not available
The compile-time detection works by checking if epoll_create1 is defined
(Linux-specific glibc function since 2.9). On systems without epoll, the
code falls back to using poll() with a pipe for wakeup signaling.
Implement a new GenAI module for ProxySQL with basic infrastructure:
- GenAI_Threads_Handler class for managing GenAI module configuration
- Support for genai- prefixed variables in global_variables table
- Dummy variables: genai-var1 (string) and genai-var2 (integer)
- Config file support via genai_variables section
- Flush functions for runtime_to_database and database_to_runtime
- Module lifecycle: initialization at startup, graceful shutdown
- LOAD/SAVE GENAI VARIABLES admin command infrastructure
Core functionality verified:
- Config file loading works
- Variables persist in global_variables table
- Disk save/load via SQL works
- Module initializes and shuts down properly
Related files:
- include/GenAI_Thread.h: New GenAI thread handler class
- lib/GenAI_Thread.cpp: Implementation with dummy variables
- lib/Admin_Handler.cpp: Added GENAI command vectors and handlers
- lib/Admin_FlushVariables.cpp: Added genai flush functions
- lib/ProxySQL_Admin.cpp: Added init_genai_variables() and load_save_disk_commands entry
- include/proxysql_admin.h: Added function declarations
- lib/Makefile: Added GenAI_Thread.oo to build
- src/main.cpp: Added module initialization and cleanup
- src/proxysql.cfg: Added genai_variables configuration section
This commit addresses all review comments from gemini-code-assist on PR #5279:
1. Fixed FLUSH LOGS documentation - clarified that file is reopened for
appending, not truncating, and updated the note about preserving contents
2. Fixed callback documentation - clarified that the callback attaches to
all frontend connections, not just admin connections
3. Updated security warning - focused on passive eavesdropping and offline
decryption as the primary threats
4. Fixed typo: proxyql_ip -> proxysql_ip in tcpdump example
5. Removed misleading @see HPKP link - HPKP is unrelated to NSS Key Log
Format and is a deprecated feature
6. Updated NSS Key Log Format URL to use official MDN link instead of
unofficial mirror
7. Fixed buffer size comment to accurately reflect 256-byte buffer and
254-byte line length validation
8. Clarified fputs comment to emphasize the read lock's role in allowing
concurrent writes from multiple threads
This commit addresses critical issues identified in PR #5276 by
gemini-code-assist's code review, which could undermine the goal of
being allocation-free and cause hangs or silent failures.
Bug 1: Vector Passed by Value (Critical)
------------------------------------------
The function took std::vector<int> excludeFDs by value, causing heap
allocation during the copy operation. This undermines the PR's goal of
avoiding heap allocations after fork() to prevent deadlocks in
multi-threaded programs.
Fix: Change to pass by const reference to avoid heap allocation.
void close_all_non_term_fd(const std::vector<int>& excludeFDs)
Bug 2: Infinite Loop Risk (Critical)
------------------------------------
The loop used unsigned int for the variable while comparing against
rlim_t (unsigned long long). If rlim_cur exceeded UINT_MAX, this would
create an infinite loop.
Fix: Use rlim_t type for the loop variable and cap at INT_MAX.
for (rlim_t fd_rlim = 3; fd_rlim < nlimit.rlim_cur && fd_rlim <= INT_MAX; fd_rlim++)
Bug 3: close_range() Detection Logic (High)
------------------------------------------
The original detection logic had two problems:
1. Executed close_range syscall twice on first successful call
2. Incorrectly cached availability on transient failures (EINTR),
leaving file descriptors open without fallback
Fix: Reordered logic to only cache on success, allow retry on
transient failures. Only cache as "not available" on ENOSYS.
For other errors (EBADF, EINVAL, etc.), don't cache - might be transient.
Files Modified
--------------
- include/proxysql_utils.h
- lib/proxysql_utils.cpp
This commit adds extensive documentation for the ssl_keylog_file feature
(introduced in PR #4236), which enables TLS key logging for debugging
encrypted traffic.
## Background
The ssl_keylog_file variable (exposed as admin-ssl_keylog_file in SQL
interface) allows ProxySQL to write TLS secrets to a file in NSS Key Log
Format. These secrets can be used by tools like Wireshark and tshark to
decrypt and analyze TLS traffic for debugging purposes.
## Changes
### Inline Documentation (Code)
1. include/proxysql_sslkeylog.h (+96 lines)
- File-level documentation explaining the module purpose and security
- Doxygen comments for all 5 public APIs
- Thread-safety annotations
- Parameter descriptions and return values
2. lib/proxysql_sslkeylog.cpp (+136 lines)
- Implementation-level documentation
- Algorithm explanations (double-checked locking, thread safety)
- Reference to NSS Key Log Format specification
3. include/proxysql_admin.h (+19 lines)
- Variable documentation for ssl_keylog_file
- Path handling rules (absolute vs relative)
- Security implications
### Developer Documentation (doc/ssl_keylog/ssl_keylog_developer_guide.md)
Target audience: Developers working on ProxySQL codebase
Contents:
- Variable naming convention (SQL vs config file vs internal)
- Architecture diagrams
- Thread safety model (pthread rwlock)
- NSS Key Log Format specification
- Complete API reference for all public functions
- Integration points in the codebase
- Security considerations and code review checklist
- Testing procedures
### User Documentation (doc/ssl_keylog/ssl_keylog_user_guide.md)
Target audience: End users and system administrators
Contents:
- What is SSL key logging and when to use it
- Variable naming: admin-ssl_keylog_file (SQL) vs ssl_keylog_file (config)
- Step-by-step enable/disable instructions
- Path resolution (absolute vs relative)
- Log rotation procedures
- Production workflow: tcpdump capture → offline analysis
- Wireshark (GUI) integration tutorial
- tshark (command-line) usage examples
- Troubleshooting common issues
- Security best practices
- Quick reference card
## Key Features Documented
1. **Variable Naming Convention**
- SQL interface: SET admin-ssl_keylog_file = '/path';
- Config file: ssl_keylog_file='/path' (in admin_variables section)
- Internal code: ssl_keylog_file
2. **Production Workflow**
- Capture traffic with tcpdump (no GUI on production server)
- Transfer pcap + keylog to analysis system
- Analyze offline with Wireshark (GUI) or tshark (CLI)
3. **tshark Examples**
- Command-line analysis of encrypted traffic
- Filter examples for debugging TLS issues
- JSON export for automated analysis
## Security Notes
The documentation emphasizes that:
- Key log files contain cryptographic secrets that decrypt ALL TLS traffic
- Access must be restricted (permissions 0600)
- Only enable for debugging, never in production
- Securely delete old key log files
## Files Modified
- include/proxysql_admin.h
- include/proxysql_sslkeylog.h
- lib/proxysql_sslkeylog.cpp
## Files Added
- doc/ssl_keylog/ssl_keylog_developer_guide.md
- doc/ssl_keylog/ssl_keylog_user_guide.md
Since ProxySQL 3.0.4, SELECT VERSION() queries were intercepted and returned
ProxySQL's mysql-server_version variable instead of proxying to backends.
This broke SQLAlchemy for MariaDB which expects "MariaDB" in the version
string.
This commit adds a new variable `mysql-select_version_forwarding` with 4 modes:
- 0 = never: Always return ProxySQL's version (3.0.4+ behavior)
- 1 = always: Always proxy to backend (3.0.3 behavior)
- 2 = smart (fallback to 0): Try backend connection, else ProxySQL version
- 3 = smart (fallback to 1): Try backend connection, else proxy (default)
The implementation includes:
- New global variable mysql_thread___select_version_forwarding
- New function get_backend_version_for_hostgroup() to peek at backend
connection versions without removing them from the pool
- Modified SELECT VERSION() handler to support all 4 modes
- ProxySQL backend detection to avoid recursion
Mode 3 (default) ensures SQLAlchemy always gets the real MariaDB version
string while maintaining fast response when connections are available.
* Change MySQL_Monitor_Connection_Pool::put_connection signature to accept MySQL_Monitor_State_Data* instead of raw MYSQL*/port.
* Centralize access to mysql and port via mmsd, reducing parameter mismatch and misuse.
* Improve DEBUG bookkeeping: ensure connections are properly unregistered from the global debug registry with clearer assertions and logs.
* Add consistent proxy_debug messages for connection register/unregister events.
* Simplify server lookup/creation logic when returning connections to the pool.
* Fix ordering of error handling to always unregister before closing connections.
* Minor cleanup: remove unused labels/variables and modernize casts.
* This refactor improves correctness, debuggability, and safety of monitor connection lifecycle management.
Add detailed architectural documentation for PR 5166 session variable tracking:
- High-level architecture overview in session_track_variables struct explaining the 3-phase workflow
- Detailed documentation for handler_rc0_Process_Variables explaining the core processing workflow
- Technical implementation details for MySQL_Connection::get_variables protocol interface
- Configuration logic documentation for handler_again___verify_backend_session_track_variables
- Added inline comments explaining why session tracking is needed and performance considerations
This documentation provides a complete understanding of how MySQL session variable tracking integrates with ProxySQL's existing state machine and leverages MySQL's native session tracking capabilities.
- Add new mysql variable `mysql-session_track_variables`.
- Configure `session_track_system_variables` and `session_track_state_change`
on backend connections if the mysql variable is enabled.
- Utilize notifications from backend servers to capture system variable changes
that cannot be handled by `MySQL_Set_Stmt_Parser`
- Update both client and server variable maps based on backend responses.
- TAP test to verify this patch.
Signed-off-by: Wazir Ahmed <wazir@proxysql.com>
Logging messages now include 'client address', 'session status' and
'data stream status'. Client address is also logged when OK packets are
dispatched, this should help tracking if a client has received the
expected packets or not.
Implements a workaround for the handling of unexpected 'COM_PING'
packets received during query processing, while a resultset is yet being
streamed to the client. Received 'COM_PING' packets are queued in the
form of a counter. This counter is later used to sent the corresponding
number of 'OK' packets to the client after 'MySQL_Session' has finished
processing the current query.
This commit documents:
1. The vacuum_stats() function's purpose, behavior, and the reason why
stats_pgsql_stat_activity is excluded from bulk deletion operations
2. The fact that stats_pgsql_stat_activity is a SQL VIEW (not a table)
and attempting DELETE on it would cause SQLite error:
"cannot modify stats_pgsql_stat_activity because it is a view"
The documentation explains:
- Why TRUNCATE stats_mysql_query_digest triggers vacuum_stats(true)
- Why both MySQL and PostgreSQL tables are cleared regardless of protocol
- How the view is automatically cleared via its underlying table
stats_pgsql_processlist
- The importance of keeping the view excluded from deletion lists
The `cache_empty_result` field in query rules has three possible values:
• -1: Use global setting (`query_cache_stores_empty_result`)
• 0: Do NOT cache empty resultsets, but cache non-empty resultsets
• 1: Always cache resultsets (both empty and non-empty)
Previously, when `cache_empty_result` was set to 0, nothing was cached at all,
even for non-empty resultsets. This prevented users from disabling caching
for empty resultsets while still allowing caching of non-empty resultsets
on a per-rule basis.
Changes:
1. Modified caching logic in MySQL_Session.cpp and PgSQL_Session.cpp to
add the condition `(qpo->cache_empty_result == 0 && MyRS->num_rows)`
(MySQL) and `(qpo->cache_empty_result == 0 && num_rows)` (PgSQL)
to allow caching when cache_empty_result=0 AND result has rows.
2. Added comprehensive Doxygen documentation in query_processor.h explaining
the semantics of cache_empty_result values.
3. Updated Query_Processor.cpp with inline comments explaining the
three possible values.
Now when cache_empty_result is set to 0:
- Empty resultsets (0 rows) are NOT cached
- Non-empty resultsets (>0 rows) ARE cached
- This matches the intended per-rule behavior described in issue #5248.
Fixes: https://github.com/sysown/proxysql/issues/5248
This commit adds detailed Doxygen documentation for:
1. The ProxySQL_Config class - describes its role in configuration management
2. The Read_Global_Variables_from_configfile() method - documents its behavior,
parameters, return value, and the automatic prefix stripping feature
The documentation explains the automatic prefix stripping behavior that handles
cases where users mistakenly include module prefix (e.g., "mysql-") in variable
names within configuration files.
This change introduces PostgreSQL-aware tokenization by adding support for dollar-quoted strings, PostgreSQL’s double-quoted identifiers, and its comment rules. The tokenizer now correctly parses $$…$$ and $tag$…$tag$, treats " as an identifier delimiter in PostgreSQL, disables MySQL-only # comments, and accepts -- as a comment starter without requiring a trailing space. All new behavior is fully isolated behind the dialect flag to avoid impacting MySQL parsing.
Add PostgreSQL dollar-quoted strings
* New parser state: st_dollar_quote_string.
* Recognizes $$ … $$ and $tag$ … $tag$ sequences.
* Tracks opening tag and searches for matching terminator.
* Normalizes entire literal to ?.
* Integrated into get_next_st() and stage_1_parsing().
The get_status_variable() function was only scanning worker threads
but ignoring auxiliary threads (idle threads) where timeout
terminations are detected. This caused the timeout termination
counter to show incorrect/zero values.
- Added idle thread scanning to both overloaded versions of
get_status_variable() function
- Now properly collects metrics from both worker and idle threads
- Fixes the issue where proxysql_mysql_timeout_terminated_connections_total
showed zero despite actual timeout terminations
Resolves the metrics reading issue identified in the previous commits.
Code improvements:
- Extract SESS_TO_SCAN_idle_thread constant to header file for better maintainability
- Replace magic number 128 with named constant in idle_thread_to_kill_idle_sessions()
- Improve code readability and consistency in session scanning logic
Test enhancements:
- Add mysql-poll_timeout configuration for more precise timeout testing
- Reduce test sleep times to 13 seconds for faster test execution
- Add diagnostic messages to clearly show timeout configurations in test output
- Ensure tests properly validate timeout enforcement with precise timing
The changes improve code maintainability and make tests more reliable and faster
while maintaining accurate timeout validation.
- Add wait_timeout member variable declaration to Base_Session class
- Fix constructor initialization to use this->wait_timeout
- Fix assignment in handler to properly scope member variable
- Resolves compilation error for wait_timeout functionality
This commit implements two major architectural improvements to ProxySQL clustering:
Major Changes:
- Data-driven approach eliminates 95 lines of repetitive code in set_checksums()
- Modern C++ atomics replace legacy GCC __sync_* built-ins
- Improved maintainability and performance across cluster synchronization
Code Duplication Elimination:
- Replaced 142 lines of nearly identical if-statements with 47 lines of data-driven code
- Added ChecksumModuleInfo structure with member pointers for unified processing
- Generalized sync message generation using snprintf() templates
- Single loop now handles all 15 cluster modules (MySQL + PostgreSQL)
Atomic Operations Modernization:
- Converted all cluster_*_diffs_before_sync variables from int to std::atomic<int>
- Replaced __sync_fetch_and_add() with .load() for read operations (more efficient)
- Replaced __sync_lock_test_and_set() with direct assignment for write operations
- Updated member pointer types to handle atomic variables correctly
- Ensures thread safety while maintaining identical functionality
Files Modified:
- include/ProxySQL_Cluster.hpp: Added <atomic> include and std::atomic<int> declarations
- lib/ProxySQL_Cluster.cpp: Implemented data-driven set_checksums() and atomic operations
- lib/ProxySQL_Admin.cpp: Updated all cluster variable writes to use atomic operations
Technical Benefits:
- 67% reduction in repetitive code for cluster checksum processing
- Modern C++11 atomic operations with better performance characteristics
- Type safety with proper atomic types instead of compiler built-ins
- Consistent error handling and memory management patterns
- Improved maintainability for adding new cluster modules
Impact:
- Maintains exact same functionality while dramatically improving code quality
- Better performance for read operations (load vs __sync_fetch_and_add)
- Foundation for future threading optimizations
- Cleaner, more maintainable clustering codebase
PROBLEM:
The initial fix used a DDL detection approach which required maintaining a list
of query types that should return 0 affected rows. This approach was brittle
and could miss edge cases like commented queries or complex statements.
SOLUTION:
Instead of detecting DDL queries, use sqlite3_total_changes64() to measure the
actual change count before and after each query execution. The difference between
total_changes before and after represents the true affected rows count for the
current query, regardless of query type.
CHANGES:
- Added proxy_sqlite3_total_changes64 function pointer and initialization
- Rewrote execute_statement() and execute_statement_raw() to use total_changes
difference approach
- This automatically handles all query types (DDL, DML, comments, etc.)
- Added comprehensive TAP test covering INSERT, CREATE, DROP, VACUUM, UPDATE, and
BEGIN operations
BENEFITS:
- More robust and accurate than DDL detection approach
- Handles edge cases like commented queries automatically
- No maintenance overhead for new query types
- Simpler and cleaner implementation
- Still fixes both Admin interface and SQLite3 Server
This approach is mathematically sound: affected_rows = total_changes_after -
total_changes_before, which gives the exact number of rows changed by the current
query execution.
Fixes#4855
- This patch was originally added by commit 0a70fd5 and
reverted by 8d1b5b5, prior to the release of `v3.0.3`.
- The following issues are addressed in this update,
- Fix for `use-after-free` issue which occured during CI test.
- Fix for deadlock issue between `GTID_syncer` and `MySQL_Worker`.
Signed-off-by: Wazir Ahmed <wazir@proxysql.com>
Concurrency and Memory Management
* Lock-Free Ref Counting: Replaced global mutex-protected integer reference counts with `std::atomic<uint32_t>` within `PgSQL_STMT_Global_info`, eliminating lock contention during statement referencing.
* Modern Ownership: Adopted std::shared_ptr<const PgSQL_STMT_Global_info> for global and local storage, providing automatic, thread-safe memory and lifecycle management.
* Memory Optimization: Removed redundant auxiliary maps `global_id_to_stmt_names` and `map_stmt_id_to_info` from local and global statement managers respectively, reducing overall memory overhead.
* Optimized Purging: Statement removal logic was simplified for efficiently identifying and cleaning up unused statements.
Hot Path Performance (`BIND`, `DESCRIBE`, `EXECUTE`)
* Bypassed Global Lookups: Local session maps now store the `shared_ptr` directly, removing the need to acquire the global lock and search the global map during hot path operations.
* Direct Refcount Manipulation: Refcount modification functions now operate directly on the passed statement object, eliminating the overhead of searching the global map to find the object pointer based on statement id.
Safety and Protocol Logic (`PARSE`)
* Efficient Statement Reuse: Implemented a **local fast path** check for the unnamed statement (`""`), allowing immediate reuse of an identical query (same hash) upon re-parse, which bypasses global processing and locks.
Cleanup
* Cleaned up and class rename `PgSQL_STMT_Manager_v14` -> `PgSQL_STMT_Manager`.
Problem: In fast forward mode, ProxySQL forwards packets directly from client
to backend without buffering them. If the backend connection closes
unexpectedly (e.g., due to server crash, network failure, or other issues),
ProxySQL immediately closes the client session. This can result in data loss
because the client may have sent additional data that hasn't been fully
transmitted yet, as ProxySQL does not wait for the output buffers to drain.
Solution: Implement a configurable grace period for session closure in fast
forward mode. When the backend closes unexpectedly, instead of closing the
session immediately, ProxySQL waits for a configurable timeout
(fast_forward_grace_close_ms, default 5000ms) to allow any pending client
output data to be sent. During this grace period:
- If the client output buffers become empty, the session closes gracefully.
- If the timeout expires, the session closes anyway to prevent indefinite
hanging.
Changes:
- Added global variable mysql_thread___fast_forward_grace_close_ms (0-3600000ms)
- Added session flags: backend_closed_in_fast_forward, fast_forward_grace_start_time
- Added data stream flag: defer_close_due_to_fast_forward
- Modified MySQL_Data_Stream::read_from_net() to detect backend EOF and initiate
grace close if client buffers are not empty
- Modified MySQL_Session::handler() FAST_FORWARD case to implement grace close
logic with timeout and buffer checks
- Added extensive inline documentation explaining the feature and its mechanics
This prevents data loss in fast forward scenarios while maintaining bounded
session lifetime.
Previously, the parser always tokenized the full command, even when we only
needed to check whether it was a transaction command. Now, it first extracts
the first word to determine relevance and performs full tokenization only
when necessary.
According to MySQL protocol, variable length strings are encoded using
length encoded integers. For reference, see:
- https://dev.mysql.com/doc/dev/mysql-server/9.4.0/page_protocol_com_stmt_execute.html
- https://dev.mysql.com/doc/dev/mysql-server/9.4.0/page_protocol_basic_dt_integers.html#a_protocol_type_int2
The protocol specifies that values greater than 2^24 (16777216) should
be encoded using '0xFE + 8-byte integer'. Yet, in reality MySQL ignores
the upper section of these 8-byte integers, treating them effectively
like '4-bytes'. For the sake of compatibility this commit changes the
decoding behavior for 'COM_STMT_EXECUTE' to match MySQL one. This
different is subtle but important, since in practice MySQL itself
doesn't use the '8 bytes' from the field. This means that connectors
that are compatible with MySQL could find issues when sending these
packets through ProxySQL (like NodeJS 'mysql2' connector which writes
the 8-bytes as a 4-bytes duplication, motivating these changes),
situation that could result in rejection due to malformed packet
detection (or crashes/invalid handling in the worse case scenario).
The previous decoding function is now renamed into
'mysql_decode_length_ll' to honor MySQL naming 'net_field_length_ll'.
For now, this protocol change is limited to 'COM_STMT_EXECUTE'.
When true, all `min_gtid` query annotations are ignored; see
https://proxysql.com/documentation/query-annotations/ for details.
This is useful on ProxySQL setups with multiple layers, where some
layers mandate GTID-based routing while others don't.
- Add new mysql/pgsql variable `processlist_max_query_length`.
- Min: 1K
- Max: 32M
- Default: 2M
- Truncate current query based on the configuration before inserting into
`stats_*_processlist` tables.
- Refactor/fix code related to other processlist configurations.
1. `session_idle_show_processlist` value was not updated in `ProxySQL_Admin.variables`.
2. Pass processlist config as an argument to `MySQL_Threads_Handler::SQL3_Processlist`
instead of using thread-local variables.
Signed-off-by: Wazir Ahmed <wazir@proxysql.com>
This message is dump with each call to 'process_pkt_handshake_response'
printing the updated context. When the verbosity value for module
'debug_mysql_protocol' is >= 5, the stored and client supplied passwords
will be dumped in HEX format, for values < 5, the passwords will be
masked.
Previously, query cache metrics were shared between MySQL and PostgreSQL,
causing both to reflect the same values when performing cache operations.
This change isolates the metrics for each database type.
- Added `backend_pid` and `backend_state` columns to `stats_pgsql_processlist`
to display PostgreSQL backend process ID and connection state.
- Created `stats_pgsql_stat_activity` view on top of `stats_pgsql_processlist`
with column aliases matching PostgreSQL's `pg_stat_activity` for consistency.
These parameters use capitalized names in PostgreSQL for historical reasons.
ProxySQL now sends them using canonical capitalization to ensure client compatibility.
Add support for PostgreSQL query cancellation and backend termination
features to allow clients to cancel long-running queries and terminate
connections through the standard PostgreSQL protocol.
Features implemented:
- Intercept pg_backend_pid() queries and return ProxySQL session thread ID
- Intercept pg_terminate_backend() to terminate client connections asynchronously
- Intercept pg_cancel_backend() to cancel queries on backend connections
- Support Cancel Request protocol via separate connection with PID and secret key validation
- Return BackendKeyData message on successful authentication with session thread ID and unique cancel secret key
This enables clients to use standard PostgreSQL cancellation mechanisms
(pg_cancel_backend, pg_terminate_backend, and Cancel Request protocol)
while ProxySQL maintains proper session isolation and maps client requests
to appropriate backend connections.
Previously, each extended-query block was terminated with a SYNC,
which caused implicit transactions to commit prematurely. As a result,
earlier write operations (INSERT/UPDATE/DELETE) could not be rolled
back if a later statement in the same sequence failed.
This change switches to libpq pipeline mode and replaces intermediate
SYNC messages with FLUSH, ensuring that all client query frames execute
as part of the same implicit transaction. A final SYNC is still issued
to resynchronize the connection and make it safe for reuse in the pool.
- Add validation methods for `mysql_users`, `pgsql_users`, `mysql_servers`,
`pgsql_servers` and `proxysql_servers`
- Check for duplicates and mandatory fields
- Return descriptive error messages to clients when validation fails
Signed-off-by: Wazir Ahmed <wazir@proxysql.com>
Co-authored-by: takaidohigasi <takaidohigasi@gmail.com>
Previously, deleting `PgSQL_Errors_stats` instances in TUs with only a forward
declaration caused the destructor to be skipped, leaking member allocations.
The fix ensures the full class definition is visible at delete sites.
Previously, Parse and Describe each had their own query result handling
paths. This duplicated a lot of logic and also failed to handle some
cases correctly—for example, Notice messages returned by the server
during extended-protocol queries. Keeping these separate paths would
be hard to maintain and prone to bugs.
The simple-query result handling is already mature, optimized, and
covers all the necessary cases. Reusing it for Parse and Describe
makes behavior consistent across simple and extended query flows,
while also reducing duplicate code.
When a simple query arrives while extended query messages are pending,
we now:
- inject an implicit Sync,
- process all extended query messages,
- then execute the simple query,
- and send ReadyForQuery only after the simple query completes.
RequestEnd was applying state changes (session variable restore,
rollback-to-savepoint, multiplexing toggle for temp tables/sequences)
even when the query failed to execute on the backend connection.
This caused internal state to diverge from the actual backend state.
Fix:
- Add success/failure flag to RequestEnd calls.
- Restrict state-changing logic to Simple Query and Prepared Execute.
- Ensure logic only runs when the query executed successfully on backend.
This keeps internal state aligned with the backend connection state.
PQsendQueryPrepared always emits Bind -> Describe Portal -> Execute, which led
to RowDescription being included in the result set even when the client never
sent a Describe message. This caused clients to receive row descriptions they
did not request.
Changes:
- Skip including RowDescription when the client did not send Describe.
- If the client explicitly sent Describe followed by Execute, continue to
skip redundant execution of Describe but include RowDescription once.
This ensures RowDescription is only sent when requested, aligning behavior
with protocol expectations.
The PQsendQueryPrepared function transmits the sequence BIND ->
DESCRIBE(PORTAL) -> EXECUTE -> SYNC. However, libpq does not indicate
whether the DESCRIBE PORTAL step produces a NoData packet for commands
such as INSERT, DELETE, or UPDATE. In these cases, libpq returns
PGRES_COMMAND_OK, whereas SELECT statements yield PGRES_SINGLE_TUPLE or
PGRES_TUPLES_OK.
This update explicitly appends a NoData packet to the result in order to
provide consistent behavior across query types.
* Fixed a crash occurring during session destruction.
* Query rules will now apply only to the first message in an extended query frame.
* OK message will apply to execute message.
* Query rewrite, error messages, and large packet handling will apply to parse message.
* Added query processing support for the Bind message.
* Added tracking for pg_advisory_lock, with status reset only on pg_advisory_unlock_all
* Implemented support for CREATE SEQUENCE and CREATE TEMP SEQUENCE, with reset on DISCARD SEQUENCES
* Added handling for CREATE TEMP TABLE, with reset triggered by DISCARD TEMP
* Now will actually reset value to server default (represented by
nullptr value), if that parameter is not set in startup parameter.
* Default parameter values (pgsql_default_*) will now be set only for critical parameters.
* Introduced startup parameters in PgSQL_Connection (removed default session parameters from PgSQL_Session)
* Startup parameters are populated during both frontend and backend connection creation
* Parameters provided via connection options are set as startup parameters
* Backend connection parameter handling updated: only critical variables are now set via connection options to prevent interference with DISCARD ALL during connection reset; remaining parameters will be applied using individual SET commands
* Marked functions ref_count_client and ref_count_server as noexcept since standard exceptions are not being handled and application crashes are acceptable in such cases.
René Cannaò
Wazir Ahmed
Javier Jaramago Fernández
Miro Stauder
René Cannaò
Rahim Kanji
Lisandro Pin
Keith Brings