Bug fix:
- Mirror sessions had their destination_hostgroup incorrectly overwritten
by fast routing rules, causing duplicate query execution to the same hostgroup
- Added check in Query_Processor.cpp to skip fast routing for mirror sessions
Changes:
- lib/Query_Processor.cpp: Wrapped fast routing logic with mirror session check
- test/tap/tests/reg_test_2233_mirror_fast_routing-t.cpp: New regression test
- test/tap/groups/groups.json: Added test to default-g1 test group
- test/tap/tests/Makefile: Added build rule for the new test (commented out)
The fix ensures mirror sessions maintain their assigned destination_hostgroup
and are not affected by fast routing rules.
Extend \d meta-command to support \d tablename for describing specific tables. Implements handlers for all 8 queries in the describe sequence:
- Query 1: pg_class lookup with c.relname OPERATOR (extracts/caches table)
- Query 2: pg_class attributes with c.oid= (table metadata)
- Query 3: pg_attribute with PRAGMA table_info() (column information)
- Queries 4-8: pg_policy, pg_statistic_ext, pg_publication, pg_inherits
(return empty results as SQLite lacks these features)
Add time-series database extension to ProxySQL_Statistics with:
- Three SQLite tables: tsdb_metrics, tsdb_metrics_hour, tsdb_backend_health
- Automatic downsampling from raw to hourly aggregates
- Retention management (7 days raw, 1 year hourly)
- Prometheus metrics sampling
- Backend health monitoring via TCP probes
- SQL query interface with JSON label filtering
- Admin variable integration
Documentation:
- embedded_tsdb_overview.md - Architecture and benefits
- embedded_tsdb_architecture.md - System design and components
- embedded_tsdb_reference.md - Configuration and API reference
- embedded_tsdb_specs.md - Technical specifications
- embedded_tsdb_quickstart.md - Getting started guide
All 4 build combinations pass:
- make debug / make release
- PROXYSQLGENAI=1 make debug / make release
When pkts_recv==0, the code reads 4 bytes expecting a MySQL packet header,
but PROXY protocol starts with 'PROXY ' which gets misinterpreted as a
MySQL header with a huge packet length (0x504f5250 = ~1.3GB).
Add check for 'PROX' prefix before parsing as MySQL header. This prevents
passing an incorrect length parameter to recv() that exceeds the buffer size.
Fixes#5376
Change malloc() to calloc() in get_result_buffer() to zero-initialize
the query digest buffer before use. This prevents stage_2_parsing()
from reading uninitialized memory when processing query digests.
Fixes#5375
Add static initialization PTHREAD_RWLOCK_INITIALIZER to filters_rwlock
in debug.cpp to prevent race condition where proxy_debug() is called
before init_debug_struct() initializes the rwlock.
This fixes Valgrind errors in filter_debug_entry() where threads were
accessing an uninitialized rwlock.
Fix race condition where threads were accessing GloMTH before it was fully
initialized, causing Valgrind errors in get_variable_int() and related
functions.
Added wait loop pattern (while GloMTH==NULL { usleep(50000); }) followed
by usleep(100000) to ensure GloMTH is fully initialized before use:
In lib/MySQL_Monitor.cpp:
- monitor_connect(), monitor_ping(), monitor_read_only()
- monitor_group_replication(), monitor_galera(), monitor_replication_lag()
- monitor_AWS_Aurora_thread_HG(), monitor_aws_aurora()
- init_mysql_thread_struct()
In src/SQLite3_Server.cpp:
- SQLite3_Server_session_handler
In lib/ProxySQL_Admin.cpp:
- admin_session_handler
In lib/ClickHouse_Server.cpp:
- ClickHouse_Server_session_handler
The pattern ensures threads wait for GloMTH initialization during startup,
then check for NULL during shutdown to exit cleanly.
Fix race condition where monitor threads were accessing GloMTH before
it was fully initialized, causing Valgrind errors in get_variable_int().
Added wait loop pattern (while GloMTH==NULL { usleep(50000); }) followed
by usleep(100000) to:
- monitor_ping_thread
- monitor_read_only_thread
- monitor_replication_lag_thread
- monitor_group_replication_thread (also moved check before MySQL_Thread creation)
- monitor_galera_thread (also moved check before MySQL_Thread creation)
This matches the existing pattern in monitor_connect_thread.
Add wait loop for GloMTH initialization before accessing variables.
Other monitor threads (MonitorPing, MonitorReadOnly, etc.) already had
this pattern, but monitor_connect_thread was missing it.
This fixes Valgrind errors about uninitialized values in
get_variable_uint16() when strcasecmp reads from memory that hasn't
been fully initialized yet.
Initialize tmp_charset=0 in MySQL_Data_Stream and PgSQL_Data_Stream
constructors. This field was used uninitialized in
process_pkt_handshake_response() when calling proxy_debug(), causing
Valgrind errors about uninitialized values in vsnprintf.
Initialize fd=-1 and status=0 in MySQL_Data_Stream and PgSQL_Data_Stream
constructors. These fields were not initialized, causing Valgrind
errors when GPFC_Statuses2() checked mybe->server_myds->status and fd.
1. Base_Thread.cpp: Initialize member variables in constructor
- curtime, last_move_to_idle_thread_time, epoll_thread, shutdown, mysql_sessions
were not initialized, causing uninitialized value use in
ProcessAllMyDS_BeforePoll and ProcessAllMyDS_AfterPoll.
2. MySQL_Logger.cpp: Initialize query_ptr, query_len, and buf in MySQL_Event
constructor. These fields were used without initialization, causing
Valgrind errors in JSON serialization and other operations.
Replace _counters{} initialization syntax with explicit memset() to ensure
the _counters array is properly zero-initialized. The _counters{} syntax
may not reliably zero-initialize arrays in all compiler implementations,
causing Valgrind to report use of uninitialized values when
add_and_reset() reads from the array.
Also remove the safety checks added previously since the root cause
is now properly fixed.
1. MySQL_Logger.cpp: Initialize username_len, schemaname_len, and server_len
to 0 in MySQL_Event constructor. These fields were uninitialized and
used in copy constructor and JSON serialization.
2. debug.cpp: Add default member initializers to DebugLogEntry struct
to ensure POD fields are zero-initialized. Move errno save/restore
to after early return checks to avoid potential TLS access issues
before thread is fully initialized.
1. MySQL_Logger.cpp: Add NULL checks for myconn->parent before accessing
address/port. Client connections (frontend) have parent=NULL, causing
uninitialized value reads when logging.
2. debug.cpp: Add NULL check for GloVars.global.gdbg_lvl before accessing
debug level configuration. Prevents use of uninitialized data when
proxy_debug_func is called before init_debug_struct() completes.
3. ProxySQL_Admin.cpp: Add NULL checks for debug filter database fields
before constructing filter strings. Prevents undefined behavior when
constructing std::string from NULL pointers.
Use memset to zero-initialize the entire QueryParserArgs struct instead
of individually setting only digest_text and first_comment to NULL.
This fixes Valgrind errors about use of uninitialised values in
strcasestr() when reading from the uninitialized buf array.
Fixes errors like:
Use of uninitialised value of size 8
at tolower (ctype.c:46)
by strcasestr
by MySQL_Connection::ProcessQueryAndSetStatusFlags(char*)
- Added <pthread.h> to log_utils.h and removed the header from log_utils.cpp
- Pass 0 to reset_time argument in flush_and_rotate inside logger's destructor
- Replace remaining sprintf with snprintf in Admin_FlushVariables.cpp
- Fix indentation in flush_GENERIC_variables__process__database_to_runtime
- Apply same pattern to ProxySQL_Admin.cpp and ProxySQL_Admin_Stats.cpp
- Convert prepare_v2() calls to use unique_ptr RAII pattern
- Remove manual sqlite3_finalize calls (now handled by RAII)
- Update flush_*_variables functions to use new prepare_v2 return pattern
- Apply changes to Admin, Cluster, Monitor, Catalog, and other modules
- Replace sprintf with snprintf in flush_*_variables functions
- Add null value safety with safe_val in variable flushing
- Fix error message to use send_error_msg_to_client instead of send_ok_msg_to_client
- Add coordinated shutdown for PROXYSQL KILL/SHUTDOWN in debug builds
- Track active admin client threads with admin_client_threads_active counter
- Add shutdown checks to prevent new connections during shutdown
- Wait for all admin client threads to finish before cleanup in admin_shutdown()
- Handle accept failures gracefully
Implement handling for psql meta-commands (\dt, \di, \dv, \d, \l) in ProxySQL's PostgreSQL admin interface by intercepting expanded queries and converting them to appropriate SQLite queries.
Supported commands:
- \l, \l+: List databases (maps to PRAGMA DATABASE_LIST)
- \dt [pattern]: List tables with optional pattern matching
- \di [pattern]: List indexes with optional pattern matching
- \dv [pattern]: List views with optional pattern matching
- \d: List all relations (tables, views, indexes, triggers)
Pattern matching supports psql regex syntax like '\dt mytest*' by converting PostgreSQL regex patterns (^(pattern).*$) to SQLite LIKE patterns (pattern%).
Detailed Changes:
- Introduced LogBuffer and LogBufferThreadContext to implement per-thread buffering, reducing lock contention.
- Replaced localtime() with localtime_r() for improved performance and thread safety during timestamp generation.
- Implemented configurable sampling for event logs to reduce I/O overhead.
- Added global configuration variables to control flush size, flush interval, and sampling rate for event and audit logs.
This commit resolves the prometheus metric collision between MySQL and
PostgreSQL Query Cache instances by creating separate metric maps with
protocol labels and using compile-time type trait selection.
Changes:
- Add #include <type_traits> to Query_Cache.cpp
- Create qc_metrics_map_mysql with { "protocol", "mysql" } labels (8 metrics)
- Create qc_metrics_map_pgsql with { "protocol", "pgsql" } labels (8 metrics)
- Modify Query_Cache constructor to use if constexpr with std::is_same_v
to select the appropriate metrics map based on derived type
- Enable GloPgQC metrics export in update_modules_metrics()
This allows MySQL and PostgreSQL query cache metrics to coexist without
collision, following the same pattern as Thread Handler metrics.
Related: #5068, #5069
This commit extends PR #5069 by adding protocol labels to distinguish
MySQL and PostgreSQL metrics in the Thread Handler modules.
Changes:
- Add { "protocol", "mysql" } labels to 65 MySQL_Thread.cpp metrics
- Add { "protocol", "pgsql" } labels to 60 PgSQL_Thread.cpp metrics
- Enable GloPTH (PostgreSQL threads handler) metrics export
- Document Query Cache limitation (template base class collision)
This resolves Issue #5068 by ensuring PostgreSQL prometheus metrics
are properly exported with protocol labels, distinguishing them from
MySQL metrics.
Related: #5068, #5069
The statement1 was prepared but never finalized, causing a memory leak.
Migrated to RAII pattern for automatic cleanup.
Also updates mysql_hostgroup_attributes and mysql_servers_ssl_params
to use RAII for consistency (lines 2231 and 2267), even though those
had manual finalize calls.
Reported-by: CodeRabbit AI
The RAII-managed unique_ptr already handles statement finalization.
Manual finalize calls cause double-free which leads to undefined behavior.
Reported-by: CodeRabbit AI
Move statement2_unique to function scope to prevent use-after-free.
The unique_ptr was declared inside the if (runtime) block but
statement2 (raw pointer) was used in the loop outside that block,
causing the statement to be finalized before use.
Affected functions:
- flush_variables___runtime_to_database (PgSQL variables)
- flush_genai_variables___runtime_to_database (GenAI variables)
- flush_mysql_variables___runtime_to_database (MySQL variables)
Reported-by: CodeRabbit AI
Fix NULL pointer dereference in CopyCmdMatcher that caused ProxySQL
to crash immediately when running any PostgreSQL query on macOS and
FreeBSD systems.
Root cause: copy_cmd_matcher was guarded by #ifdef IDLE_THREADS, but
IDLE_THREADS is explicitly disabled on macOS (__APPLE__) and FreeBSD
in proxy_defines.h. This caused the pointer to be NULL when accessed.
The fix removes #ifdef IDLE_THREADS guards from copy_cmd_matcher since
COPY command detection is a fundamental PostgreSQL feature for fast-
forward mode and has no dependency on idle threads.
Affected systems: macOS, FreeBSD (where IDLE_THREADS is disabled)
Fixes: #5359
- Fixed SAFE_SQLITE3_STEP2 macro errors: renamed 'rc1' to 'rc' in PgSQL_Monitor.cpp
functions (update_connect_table, update_ping_table, update_readonly_table,
maint_monitor_table) to match the macro's expected variable name
- Removed redundant NULL declarations in ProxySQL_Admin_Stats.cpp functions
(stats___pgsql_processlist, stats___pgsql_free_connections,
stats___pgsql_errors, stats___save_pgsql_query_digest_to_sqlite)
- Completed migration of stats___pgsql_errors and
stats___save_pgsql_query_digest_to_sqlite to RAII pattern
Migrated 5 prepare_v2 calls to RAII-based pattern in:
- update_monitor_pgsql_servers(): 2 statements (stmt1, stmt32)
- update_connect_table(): 1 statement (stmt)
- update_ping_table(): 1 statement (stmt)
- update_readonly_table(): 1 statement (stmt)
- maint_monitor_table(): 1 statement (stmt)
Note: Functions shunn_non_resp_srv() and update_srv_latency() were NOT
migrated because they use global thread_local cached statements, which is
a valid use case for the old API.
The RAII pattern ensures automatic cleanup and eliminates manual
sqlite_finalize_statement() calls.
Migrated the template function FlushDigestTableToDisk to use RAII-based
prepare_v2. This function is instantiated for both MySQL and PostgreSQL,
eliminating 8 deprecation warnings (4 instantiations x 2 prepare_v2 calls).
The RAII pattern ensures automatic cleanup and eliminates manual
(*proxy_sqlite3_finalize) calls.
The RAII-based prepare_v2 migration introduced duplicate variable declarations
that caused compilation errors. Removed duplicate 'int rc;' declarations in:
- dump_checksums_values_table()
- save_mysql_users_runtime_to_database()
- save_pgsql_users_runtime_to_database()
The 'int rc' variable was already declared at function scope, so the
duplicate declarations after the structured bindings were redundant.
Migrated:
- save_pgsql_ldap_mapping_runtime_to_database()
- save_mysql_servers_runtime_to_database()
All statements are now automatically managed by RAII smart pointers.
Migrated:
- save_pgsql_users_runtime_to_database()
- save_mysql_ldap_mapping_runtime_to_database()
All statements are now automatically managed by RAII smart pointers.
Migrated:
- dump_checksums_values_table()
- save_mysql_users_runtime_to_database()
All statements are now automatically managed by RAII smart pointers.
Migrated:
- save_mysql_firewall_whitelist_rules_from_runtime()
- save_pgsql_firewall_whitelist_rules_from_runtime()
All statements are now automatically managed by RAII smart pointers.
Migrated the following functions:
- save_mysql_query_rules_fast_routing_from_runtime()
- save_pgsql_query_rules_fast_routing_from_runtime()
- save_mysql_firewall_whitelist_sqli_fingerprints_from_runtime()
- save_pgsql_firewall_whitelist_sqli_fingerprints_from_runtime()
- save_mysql_firewall_whitelist_users_from_runtime()
- save_pgsql_firewall_whitelist_users_from_runtime()
All statements are now automatically managed by RAII smart pointers,
eliminating manual finalize calls and potential memory leaks.
Migrated the following functions:
- populate_monitor_mysql_server_aws_aurora_log()
- populate_monitor_mysql_server_aws_aurora_check_status()
- monitor_ping_process_ready_task_thread()
- monitor_read_only_process_ready_tasks() (with if/else branches for replication_lag)
All statements are now automatically managed by RAII smart pointers,
eliminating manual finalize calls and potential memory leaks.
- Changed from raw pointer to smart pointer-based prepare_v2
- Fixes existing memory leak where statement was never finalized
- RAII ensures automatic cleanup when function exits
- Changed from raw pointer to smart pointer-based prepare_v2
- Use raw pointer aliases to avoid .get() verbosity
- Remove manual finalize - RAII handles cleanup automatically
This commit addresses a use-after-free race condition that could cause
ProxySQL to crash with SIGSEGV during COM_CHANGE_USER timeout scenarios.
Root Cause:
-----------
When a session is in RESETTING_CONNECTION status and the backend
times out during COM_CHANGE_USER, the code calls RequestEnd() to
handle the error. However, RequestEnd() accesses client_myds without
null checks. If the session is marked as unhealthy and deleted by
ProcessAllSessions_Healthy0() while RequestEnd() is still executing,
this results in a use-after-free crash.
The crash is particularly likely during AWS Aurora failures when:
1. Monitor detects Aurora master timeouts
2. Multiple sessions experience connection issues
3. Many client connections are marked unhealthy
4. The high volume of session deletions increases race condition probability
Fix:
----
Added a null pointer check before accessing client_myds in the
unexp_com_pings handling code within RequestEnd().
This is a defensive programming fix that prevents the crash when
client_myds has been freed. A more robust fix would involve proper
session lifecycle management with reference counting.
Fixes: #5355
Migrate 2 test functions from deprecated prepare_v2() to the new RAII-based API:
- ProxySQL_Test___GenerateRandom_mysql_query_rules_fast_routing()
- ProxySQL_Test___MySQL_HostGroups_Manager_generate_many_clusters()
Both are test functions with clear structure - simple cases.
Uses raw pointer aliases to avoid .get() verbosity.
Pattern used:
auto [rc, statement_unique] = admindb->prepare_v2(query);
sqlite3_stmt *statement = statement_unique.get();
// ... use statement directly ...
// No manual finalize - RAII handles it
This addresses 3 of the ~150 deprecated prepare_v2 warnings.
Migrate 4 functions from deprecated prepare_v2() to the new RAII-based API:
- flush_pgsql_variables___runtime_to_database()
- flush_genai_variables___runtime_to_database()
- flush_mysql_variables___runtime_to_database()
- flush_mcp_variables___runtime_to_database()
All early returns happen before prepare_v2(), making these simple cases.
Uses raw pointer aliases to avoid .get() verbosity.
Pattern used:
auto [rc, statement_unique] = db->prepare_v2(query);
sqlite3_stmt *statement = statement_unique.get();
// ... use statement directly ...
// No manual finalize - RAII handles it
This addresses 4 of the ~150 deprecated prepare_v2 warnings.
Migrate insertMysqlEventsIntoDb() function from deprecated prepare_v2()
to the new RAII-based API using raw pointer aliases.
Pattern used:
auto [rc, statement_unique] = db->prepare_v2(query);
sqlite3_stmt *statement = statement_unique.get();
// ... use statement directly ...
// No manual finalize - RAII handles it
This addresses 2 of the ~150 deprecated prepare_v2 warnings.
Migrate 11 functions in MySQL_HostGroups_Manager.cpp from the deprecated
prepare_v2() API to the new RAII-based API. Uses raw pointer aliases to
avoid .get() verbosity throughout the code.
Changes per function:
- servers_add()
- All mysql_* table generation functions
- Group replication, Galera, AWS Aurora hostgroups functions
Pattern used:
auto [rc, statement_unique] = mydb->prepare_v2(query);
sqlite3_stmt *statement = statement_unique.get();
// ... use statement directly, no .get() needed ...
// No manual finalize - RAII handles it
Benefits:
- Automatic cleanup via RAII (no manual finalize needed)
- Exception-safe cleanup on early returns
- Reduced memory leak risk
This addresses ~11 of the ~150 deprecated prepare_v2 warnings.
Found during compiler warning analysis with 'make debug'.
1. Fix write-strings warnings (12 occurrences):
- MySQL_HostGroups_Manager.cpp: Change char* to const char*
for src_status and dst_status variables
- MySQL_Monitor.cpp: Change ConsumerThread constructor
parameter from char thread_name[16] to const char *thread_name
2. Fix unused-but-set-variable warnings (6 occurrences) by
wrapping PROXYSQLGENAI-specific variables in #ifdef blocks:
- stats_mcp_query_tools_counters
- stats_mcp_query_tools_counters_reset
- stats_mcp_query_digest
- stats_mcp_query_digest_reset
- stats_mcp_query_rules
- runtime_mcp_query_rules
These variables were assigned values but appeared unused when
PROXYSQLGENAI was not defined. Now they are only defined and
assigned when PROXYSQLGENAI is enabled.
This eliminates 18 compiler warnings: 12 write-strings and 6
unused-but-set-variable warnings.
Found during compiler warning analysis with 'make debug'.
Fix two HIGH priority signed/unsigned comparison warnings:
1. MySQL_HostGroups_Manager.cpp:1792 - Change loop variable from
'int j' to 'unsigned int j' to match the unsigned comparison
with servers->len.
2. MySQL_Thread.cpp:4357 - Cast int to size_t when comparing with
getBufferSize() which returns size_t.
These changes prevent potential logic errors from negative values
being interpreted as large unsigned values.
Found during compiler warning analysis with 'make debug'.
Fix use-after-free vulnerability where the function frees the 'arg'
pointer at line 2306 but then returns it at line 2323. Change the
return value to NULL instead of the freed pointer.
This fixes a -Wuse-after-free compiler warning and prevents potential
memory corruption.
Found during compiler warning analysis with 'make debug'.
The fix ensures that when the purge checks ref_count_client under write lock, it sees the latest value from concurrent threads holding raw pointers, preventing reading stale data.
This commit fixes a bug where connection cleanup loops were removing
the wrong connection from the pool. The loops checked each connection
by index (i), but when an expired connection was found, they removed
index 0 instead of index i.
This caused:
- Fresh connections at index 0 to be incorrectly deleted
- Expired connections to remain in the pool
Fixed files:
- lib/Base_HostGroups_Manager.cpp:2603 (MySQL)
- lib/MySQL_HostGroups_Manager.cpp:2939 (MySQL)
- lib/PgSQL_HostGroups_Manager.cpp:2782 (PgSQL)
The fix changes `remove(0)` to `remove(i)` to remove the correct
connection.
Related: #5094 (fixes similar issue in drop_all_idle_connections)
This commit addresses critical issues identified in the PR review:
1. Build system redundancy (src/Makefile):
- Remove redundant PROXYSQLGENAI conditionals in linking commands
- The flag doesn't affect linking, so simplify to PROXYSQLCLICKHOUSE only
2. Missing PROXYSQLGENAI guard (lib/Admin_Handler.cpp):
- Add #ifdef PROXYSQLGENAI around MCP VARIABLES DISK commands
- Ensures MCP commands are only available when GenAI is enabled
3. Broken retry logic (lib/LLM_Clients.cpp):
- Remove misleading is_retryable_error() checks that used stale values
- last_curl_code and last_http_code were never updated
- Simplify to retry on empty responses only (documented limitation)
- Fix thread-safety: use thread_local std::mt19937 instead of rand()
4. Resource leak (lib/MySQL_Tool_Handler.cpp):
- Clean up previously created connections on init failure
- Both mysql_init and mysql_real_connect error paths now clean up
5. Race condition (lib/Query_Tool_Handler.cpp):
- Add missing pool_lock in find_connection()
- Prevents race condition when accessing connection_pool
Fixes identified by automated PR review agents.
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>
Merge changes from PR #5318 (RAG ingestion feature) into the new
development branch v4.0_rag_ingest_2.
Changes include:
- RAG ingestion tool (rag_ingest) with chunking and embeddings
- MySQL_Catalog fixes for NULL pointer handling
- MCP server updates for RAG tools
- Comprehensive documentation and test scripts
- Fix NULL pointer dereference in rag_ingest.cpp: use str_or_empty() helper
for all sqlite3_column_text results assigned to std::string
- Fix NULL tags/links crash in MySQL_Catalog.cpp: add null guards before
assigning sqlite3_column_text results to std::string
- Fix missing curl_global_cleanup on error path in rag_ingest.cpp
- Fix std::out_of_range exception in rag_ingest.cpp: wrap std::stoll calls
in try-catch blocks, fall back to string comparison on overflow
- Fix Makefile: Use $(CXXFLAGS) directly for consistency with build philosophy
- Fix MySQL_Catalog: Return proper error JSON instead of empty array on missing query
This commit addresses concerns raised by AI code reviewers (gemini-code-assist,
Copilot, coderabbitai) on the initial security fixes.
Critical fixes:
- Fix lock.release() → lock.unlock() in GenAI_Thread.cpp worker_loop
(lock.release() detaches without unlocking, causing deadlock)
- Add missing early return after schema validation failure in Query_Tool_Handler.cpp
Code quality improvements:
- Improve escape_string() memory management in MySQL_Tool_Handler.cpp:
- Use std::string instead of new[]/delete[] for buffer management
- Check return value of mysql_real_escape_string() for errors
- Remove redundant validation checks in validate_sql_identifier functions
(character class loop already rejects unsafe characters)
- Add backslash escaping to escape_string_literal() for defense-in-depth
- Improve column list validation in MySQL_Tool_Handler sample_rows():
- Replace blacklist approach with proper column identifier parsing
- Allow qualified identifiers (table.column)
- Allow AS aliases (column AS alias)
- No longer rejects legitimate column names containing "JOIN"
These changes improve robustness while maintaining the security posture
of the original SQL injection fixes.
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
This addresses an issue from PR #22 where LoadPlugin() was completely
disabled. The function performs necessary initialization even when no
plugin is loaded (initializes built-in sqlite3 function pointers).
Changes:
- Added `const bool allow_load_plugin = false` flag in LoadPlugin()
- Modified `if (plugin_name)` to `if (plugin_name && allow_load_plugin == true)`
- Re-enabled the LoadPlugin() call in LoadPlugins()
The plugin loading code remains disabled (allow_load_plugin=false) while
the function pointer initialization from built-in SQLite3 now works correctly.
TODO: Revisit plugin loading safety mechanism to allow actual plugin loading.
- Fix comment mismatch: Changed _2 suffix to match actual function name
(mysql_query_digest_and_first_comment, not _2)
- Make get_def_mysql_opts() static to avoid symbol pollution
- Fix NULL first_comment parameter to prevent potential segfault
- Pass valid char* pointer instead of NULL
- Free first_comment if allocated by the function
Address coderabbitai review - implement full JSON escaping for SQL digest:
- Handle backslash (\) and double quote (")
- Handle control characters: newline (\n), carriage return (\r), tab (\t)
- Handle other control characters (U+0000 through U+001F) with \uXXXX escapes
This ensures digest_text in stats_mcp_query_digest is always valid JSON,
preventing parsing errors for consumers of this data.
Fix issues identified by coderabbitai review:
1. Admin_Handler.cpp: Fix typo in strncasecmp for LOAD MCP QUERY RULES
- Line 2365 had "LOAD MCP RULES FROM DISK" instead of "LOAD MCP QUERY RULES FROM DISK"
2. Admin_Handler.cpp: Fix use-after-free and missing client response
- Removed l_free(*ql,*q) which freed *q before caller used it
- Added send_error_msg_to_client calls on all error paths
- Added send_ok_msg_to_client call on success path
- Changed return value from true to false (to match handler pattern)
- Applied to both LOAD MCP QUERY RULES and SAVE MCP QUERY RULES handlers
3. ProxySQL_Admin_Stats.cpp: Fix sprintf SQL injection in stats___mcp_query_rules
- Replaced sprintf with prepared statement using positional parameters
- Changed from char* query with malloc/free to sqlite3_stmt with prepare_v2
- Both columns now bound as parameters (?1, ?2)
Fix compilation errors in the SQL injection fixes:
1. ProxySQL_Admin_Stats.cpp: Use public statsdb->prepare_v2() API
- Changed from direct proxy_sqlite3_prepare_v2() calls with statsdb->db
- statsdb->db is private, must use public prepare_v2(query, &stmt) method
2. Admin_Handler.cpp: Add SPA cast for template function access
- Added ProxySQL_Admin *SPA=(ProxySQL_Admin *)pa; declaration
- Changed all admindb->execute to SPA->admindb->execute
- Removed unused 'error' and 'success' variables
The build now completes successfully.
Fix two SQL injection vulnerabilities identified by coderabbitai in
ProxySQL_Admin_Stats.cpp by converting sprintf/snprintf interpolation
to SQLite prepared statements.
1. stats___mcp_query_digest (lines 2581-2636):
- Changed from sprintf with format string to prepared statement
- digest_text field now properly bound as parameter (was unsafe)
- All 10 columns now use positional parameters (?1-?10)
2. stats___mcp_query_tools_counters (lines 1587-1635):
- Changed from snprintf with unescaped fields to prepared statement
- Fixed incorrect table name logic (was appending _reset incorrectly)
- All 8 columns now use positional parameters (?1-?8)
These changes prevent SQL injection when resultset fields contain
quotes, backslashes, or other SQL metacharacters.
Fix multi-statement execution in Admin_Handler.cpp for MCP query rules.
The previous code built a single SQL string with "DELETE ...; INSERT ..." but
SQLite only executed the first statement.
Changed to execute statements as an explicit transaction:
1. BEGIN
2. DELETE FROM target_table
3. INSERT OR REPLACE INTO target_table SELECT * FROM source_table
4. COMMIT (or ROLLBACK on error)
Applied to both:
- LOAD MCP QUERY RULES FROM DISK/TO MEMORY
- SAVE MCP QUERY RULES TO DISK
Addresses coderabbitai review comment.
Change free(resultset) to delete resultset in
ProxySQL_Admin::load_mcp_query_rules_to_runtime.
SQLite3_result is a C++ class allocated with new, so it must be
deallocated with delete, not free(). Using free() causes undefined
behavior and memory leaks.
Addresses coderabbitai review comment.
Fix two issues in Query_Tool_Handler's execute_query functions:
1. mysql_query() failure path now returns immediately after
return_connection() instead of continuing to process on bad state.
2. Capture affected_rows BEFORE return_connection() to avoid race
condition. Previously, mysql_affected_rows() was called after
return_connection(), potentially accessing a stale connection.
Apply fixes to both execute_query() and execute_query_with_schema().
Addresses coderabbitai review comments.
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.
The execute_parameterized_query function in RAG_Tool_Handler was creating
a prepared statement and binding parameters, but then executing the raw query
string instead of the prepared statement. This completely bypassed the
parameter binding, making the function useless for preventing SQL injection.
Changed to use vector_db->execute_prepared(stmt, ...) to execute the bound
statement instead of the raw query, so the bound parameters are actually used.
Addresses coderabbitai review comment.
Change free(resultset) to delete resultset in Query_Tool_Handler
(extract_schema_name function). SQLite3_result is a C++ class allocated
with new, so it must be deallocated with delete, not free(). Using free()
causes mixed allocator UB.
Addresses coderabbitai review comment.
Add catalog_au AFTER UPDATE trigger in MySQL_Catalog that mirrors the
delete+insert pattern used in catalog_ad/catalog_ai. This keeps the FTS
index current when upserts occur (INSERT OR REPLACE ... ON CONFLICT ...
DO UPDATE), since the UPDATE doesn't trigger INSERT/DELETE triggers.
The trigger first deletes the old entry from catalog_fts then inserts
the new entry, ensuring the full-text search index stays synchronized
with the catalog table.
Addresses coderabbitai review comment.
Add assert(proxy_sqlite3_bind_blob) to the assertion block in
SQLite3DB::LoadPlugin to ensure the symbol is provided by plugins.
Without this, if a plugin fails to provide the symbol, the code will
crash at runtime with no safety check.
proxy_sqlite3_bind_blob is actively used in Anomaly_Detector.cpp:765
to bind embeddings.
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.
- Fixed invalid memory accesses for tables:
+ mcp_query_rules
+ stats_mcp_query_rules
+ stats_mcp_query_digests
- Fixed inactive 'mcp_query_rules' being loaded to runtime.
- Fixed hash computation in 'compute_mcp_digest'.
- Fixed invalid escaping during 'stats_mcp_query_digests' gen.
- Fixed digest generation for MCP arguments:
+ SQL queries are now preserved using
'mysql_query_digest_and_first_comment'.
+ TODO: Options for the tokenizer are right now hardcoded.
- Added initial testing and testing plan for MCP query_(rules/digests).
+ TODO: Test finished on phase8. Timeouts destroy the MCP
connection, leaving it unusable for subsequent queries this should
be fixed for continuing testing.
- TODO: There are several limitations to fix in
'validate_readonly_query'. This reflect in some query hacks in the
testing.
+ 'SELECT' starting with comments (--) gets flagged as non-read.
+ 'SELECT' must have a 'SELECT .* FROM' structure. While common,
simple testing queries many times lack this form.
This commit addresses valid concerns raised by coding agents (Gemini, Copilot, CoderabbitAI):
1. Fix stats_mcp_query_digest naming conflict (ProxySQL_Admin.cpp):
- Made reset and non-reset paths mutually exclusive using else block
- Prevents both flags from being true, matching MySQL pattern
- Ensures reset takes precedence over non-reset
2. Fix INSERT OR REPLACE sync issue (Admin_Handler.cpp):
- Added DELETE before INSERT OR REPLACE in LOAD/SAVE MCP QUERY RULES
- Prevents stale rules from persisting when syncing disk <-> memory
- Ensures deleted source rows are also removed from target
3. Fix integer division truncation for timeout (Query_Tool_Handler.cpp):
- Changed timeout_ms/1000 to (timeout_ms+999)/1000 for ceiling division
- Ensures sub-second timeouts (e.g., 500ms) become at least 1 second
- Prevents zero-second timeouts from causing unexpected behavior
4. Remove confusing comment (Discovery_Schema.cpp):
- Simplified column count comment to be clear and accurate
Note: The re_modifiers parsing code already correctly handles VARCHAR "CASELESS"
to int conversion (lines 2414-2425), so that review comment was already addressed.
- 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>
- Fix incorrect method name for notification
- Handle all notification messages in a generic way
- Respond with HTTP 202 Accepted (no response body)
Signed-off-by: Wazir Ahmed <wazir@proxysql.com>
- Version 2024-11-05 only supports HTTP_SSE as transport.
- ProxySQL's MCP implemenation aligns more with the StreamableHTTP
transport specified in version 2025-06-18.
- Support for SSE in StreamableHTTP transport is optional.
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.
- Fully implemented rag.search_hybrid tool with both fuse and fts_then_vec modes
- Added complete filter support across all search tools (source_ids, source_names, doc_ids, post_type_ids, tags_any, tags_all, created_after, created_before, min_score)
- Implemented proper score normalization (higher is better) for all search modes
- Updated all tool schemas to match blueprint specifications exactly
- Added metadata inclusion in search results
- Implemented Reciprocal Rank Fusion (RRF) scoring for hybrid search
- Enhanced error handling and input validation
- Added debug information for hybrid search ranking
- Updated documentation and created completion summary
This completes the v0 RAG implementation according to the blueprint requirements.
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 LOAD MCP QUERY RULES FROM DISK command
- Add LOAD MCP QUERY RULES TO MEMORY command
- Both commands copy rules from disk.mcp_query_rules to main.mcp_query_rules
This completes the full set of MCP query rules LOAD/SAVE commands,
matching the MySQL query rules pattern.
- Add separate MCP QUERY RULES command block in Admin_Handler
- Fix string length comparison (21 chars for "SAVE/LOAD MCP QUERY RULES ")
- Add handlers for:
- LOAD MCP QUERY RULES TO RUNTIME
- SAVE MCP QUERY RULES TO DISK
- SAVE MCP QUERY RULES TO MEMORY / FROM RUNTIME
- Register mcp_query_rules in disk database (tables_defs_config)
Previously MCP commands were incorrectly nested inside MYSQL/PGSQL
block and could not be reached. Now they have their own conditional
block.
- 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)
The catalog_fts FTS5 virtual table was being created but the search() function was using slow LIKE queries instead of FTS5 MATCH operator.
Changes to lib/MySQL_Catalog.cpp:
- Use FTS5 MATCH with INNER JOIN to catalog_fts when query provided
- Add BM25 relevance ranking (ORDER BY bm25(f) ASC)
- Significant performance improvement: O(log n) vs O(n)
Changes to scripts/mcp/test_catalog.sh:
- Add 8 new FTS5-specific tests (CAT013-CAT020):
- Multi-term search (AND logic)
- Phrase search with quotes
- Boolean operators (OR, NOT)
- Prefix search with wildcards
- Kind and tags filter combinations
- Relevance ranking verification
- Add SSL/HTTP support with auto-detection
- New options: --ssl, --no-ssl, MCP_USE_SSL env var
- Fix endpoint path: /query -> /mcp/querywq
Two bug fixes for the question learning feature:
1. **Fallback to most recent agent_run across all schemas**
- get_last_agent_run_id() now falls back to the most recent agent_run_id
across ALL runs if none exists for the specific run_id
- This allows adding questions even when the current schema's discovery
didn't include an agent run
- Adds logging to show when fallback is used
2. **Fix error message extraction for query_tool_calls logging**
- Fixed bug where error messages weren't being extracted correctly
- The old code checked for result["error"]["message"] but create_error_response
only has result["error"] (no nested "message" field)
- Now correctly extracts result["error"] as a string when present
- This ensures failed tool calls are properly logged with error messages
This fixes the issue where llm.question_template_add would fail with
"No agent run found" even when agent runs exist for other schemas.
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.
When llm.search is called with an empty query (list mode) to retrieve all
available questions, include_objects=true was returning full object schemas
for all related objects, resulting in massive responses that could fill the
LLM's context and cause rejections.
Fix: include_objects now only works when query is non-empty (search mode).
When query is empty (list mode), only question templates are returned
without object details, regardless of include_objects setting.
This makes semantic sense:
- Empty query = "list all questions" → just titles/bodies (compact)
- Non-empty query = "search for specific questions" → full details including
object schemas (for answering the question)
Changes:
- Modified fts_search_llm() to check !query.empty() before fetching objects
- Updated tool schema description to clarify this behavior
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.
Changes:
- fts_search_llm(): Empty query now returns all artifacts (list mode)
- Update llm.search tool: query parameter is now optional
- Tool description mentions empty query lists all artifacts
- Add body field to llm_search results
- Update demo script: Add special case for "What questions can I ask?"
This enables agents to retrieve all pre-defined question templates
when users ask what questions are available, instead of inferring
questions from schema.
- 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
Format column definitions in CREATE TABLE IF NOT EXISTS statements
to have a space before and after each comma (e.g., " , "). This allows
ProxySQL Admin to properly display multi-line table schemas.
Modified files:
- Discovery_Schema.cpp
- MySQL_Catalog.cpp
- AI_Features_Manager.cpp
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
1. Fix error logging to catch ALL tool failures, not just those with
both success and result fields. Previously, error responses like
{"success": false, "error": "..."} without a result field were
silently ignored.
2. Fix llm.domain_set_members to accept both array and JSON string
formats for the members parameter. Some clients send it as a
JSON string, others as a native array.
3. Add detailed error logging for llm.domain_set_members failures,
including what was actually received.
* 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)
Exception handlers now log the full request payload that caused the error,
making debugging much easier.
Changes:
- Move req_body/req_path declarations outside try block so catch handlers can access them
- Log request payload in all exception handlers (parse errors, std::exception, and catch-all)
- Log tool arguments when tool execution fails
Previously, exceptions would only log the error message without context,
making it impossible to reproduce the issue. Now the full payload is logged.
- Add try-catch around handle_jsonrpc_request to catch unexpected exceptions
- Add detailed logging for tool execution success/failure
- Add proper SQLite error checking in create_agent_run with error messages
- Fix json_int/json_double to handle both numbers and numeric strings
The json_int function was throwing exceptions when receiving numeric
strings (e.g., "14" instead of 14) from clients, causing 500 errors.
Now it handles both formats gracefully.
Also added logging so tool failures are visible in logs instead of
being silent 500 errors.
- 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')
Add flush_genai_variables___runtime_to_database() call to the central
location where all modules populate runtime_global_variables table.
This was missing, causing genai-* variables to not appear in
runtime_global_variables.
The flush_genai_variables___database_to_runtime() function was using
hardcoded 'admindb' instead of the 'db' parameter passed to the function.
This caused the function to always query from admindb, ignoring the
actual database parameter.
This fixes the issue where runtime_global_variables table was not being
populated on startup because the query was always hitting the same database
regardless of the parameter.
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.
The flush_genai_variables___database_to_runtime() function was only
setting internal state in GloGATH but not populating the
runtime_global_variables table. This caused variables to appear in
global_variables but not in runtime_global_variables after startup.
Fix: Add call to flush_genai_variables___runtime_to_database() with
runtime=true to populate the runtime table, following the same pattern
used by MCP variables.
Added missing #include for GenAI_Thread.h in AI_Features_Manager.cpp
to resolve the compilation error in debug mode.
Also fixed the remaining reference to variables.ai_features_enabled
which should now use GloGATH->variables.genai_enabled.
This fixes the "make debug" build failure.
This is a critical architectural fix - NL2SQL was making blocking calls
to LLMs which would block the entire MySQL thread. Now NL2SQL uses the
same async socketpair pattern as the GENAI embed/rerank operations.
Changes:
- Added nl2sql operation type to process_json_query() in GenAI module
- Updated NL2SQL handler to construct JSON query and use async GENAI path
- Added extern declaration for GloAI in GenAI_Thread.cpp
- Falls back to synchronous path only on systems without epoll
Architecture:
- Before: NL2SQL: query → blocking nl2sql->convert() → blocks MySQL thread
- After: NL2SQL: query → JSON GENAI request → async socketpair → non-blocking
JSON protocol for NL2SQL:
GENAI: {"type": "nl2sql", "query": "Show customers", "schema": "mydb"}
The NL2SQL result is delivered asynchronously through the existing
GENAI response handler, making the system fully non-blocking.
Related to: https://github.com/ProxySQL/proxysql-vec/pull/13
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
- Fix retry logic to use is_retryable_error function for proper HTTP error handling
- Add exception handling to get_json_int function with try-catch around std::stoi
- Improve validate_numeric_range to use strtol instead of atoi for better error reporting
- Fix Chinese characters in documentation (non-zero -> non-zero)
- Replace placeholder tests with actual comprehensive tests for anomaly detection functionality
- Create new standalone unit test anomaly_detector_unit-t.cpp with 29 tests covering:
* SQL injection pattern detection (12 tests)
* Query normalization (8 tests)
* Risk scoring calculations (5 tests)
* Configuration validation (4 tests)
- All tests pass successfully, providing meaningful validation of core anomaly detection logic
Thanks to gemini-code-assist for the thorough code review and recommendations.
- 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
MCP server was already running. The issue was caused by improper cleanup of
handler objects during reinitialization.
Root cause:
- ProxySQL_MCP_Server destructor deletes mysql_tool_handler
- The old code tried to delete handlers again after deleting the server,
causing double-free corruption
The fix properly handles handler lifecycle during reinitialization:
1. Delete Query_Tool_Handler first (server destructor doesn't clean this)
2. Delete the server (which also deletes MySQL_Tool_Handler via destructor)
3. Delete other handlers (config/admin/cache/observe) created by old server
4. Create new MySQL_Tool_Handler with updated configuration
5. Create new Query_Tool_Handler
6. Create new server (recreates all handlers with new endpoints)
This ensures proper cleanup and prevents double-free issues while allowing
runtime reconfiguration of MySQL connection parameters.
This commit adds comprehensive unit tests for the AI configuration
validation functions used in AI_Features_Manager.
Changes:
- Add test/tap/tests/ai_validation-t.cpp with 61 unit tests
- Test URL format validation (validate_url_format)
- Test API key format validation (validate_api_key_format)
- Test numeric range validation (validate_numeric_range)
- Test provider name validation (validate_provider_name)
- Test edge cases and boundary conditions
The test file is self-contained with its own copies of the validation
functions to avoid complex linking dependencies on libproxysql.
Test Categories:
- URL validation: 15 tests (http://, https:// protocols)
- API key validation: 14 tests (OpenAI, Anthropic formats)
- Numeric range: 13 tests (min/max boundaries)
- Provider name: 8 tests (openai, anthropic)
- Edge cases: 11 tests (NULL handling, long values)
All 61 tests pass successfully.
Part of: Phase 4 of NL2SQL improvement plan
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 validation for AI features configuration variables
to prevent invalid states and improve error messages.
Changes:
- Add validate_url_format(): Checks for http:// or https:// prefix and host part
- Add validate_api_key_format(): Validates API key format, checks for whitespace,
minimum length, and incomplete key patterns (sk- with <20 chars, sk-ant- with <25 chars)
- Add validate_numeric_range(): Validates numeric values are within min/max range
- Add validate_provider_name(): Ensures provider is 'openai' or 'anthropic'
- Update set_variable() to call validation functions before setting values
Validated variables:
- ai_nl2sql_provider: Must be 'openai' or 'anthropic'
- ai_nl2sql_provider_url: Must have http:// or https:// prefix
- ai_nl2sql_provider_key: No whitespace, minimum 10 chars
- ai_nl2sql_cache_similarity_threshold: Range [0, 100]
- ai_nl2sql_timeout_ms: Range [1000, 300000] (1 second to 5 minutes)
- ai_nl2sql_max_cloud_requests_per_hour: Range [1, 10000]
- ai_anomaly_similarity_threshold: Range [0, 100]
- ai_anomaly_risk_threshold: Range [0, 100]
- ai_anomaly_rate_limit: Range [1, 10000]
- ai_vector_dimension: Range [128, 4096]
This prevents misconfigurations and provides clear error messages to users
when invalid values are provided.
Fixes compilation issue by moving validation helper functions before
set_variable() to resolve forward declaration errors.
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.
NL2SQL_Converter improvements:
- Implement get_query_embedding() using GenAI module
- Implement check_vector_cache() with KNN search via sqlite-vec
- Implement store_in_vector_cache() with embedding storage
- All stub methods now fully functional
Anomaly_Detector improvements:
- Implement add_threat_pattern() with embedding generation
- Stores patterns in both main table and virtual vec table
- Returns pattern ID on success, -1 on error
Documentation:
- Add comprehensive VECTOR_FEATURES documentation
- README.md (471 lines): User guide and quick start
- API.md (736 lines): Complete API reference
- ARCHITECTURE.md (358 lines): System architecture
- TESTING.md (767 lines): Testing guide and procedures
This completes the vector features implementation, enabling:
- Semantic similarity caching for NL2SQL queries
- Embedding-based threat pattern detection
- Full CRUD operations for threat patterns
Improve Anomaly_Detector with full threat pattern CRUD operations:
Changes to lib/Anomaly_Detector.cpp:
- Implement list_threat_patterns():
* Returns JSON array of all threat patterns
* Shows pattern_name, pattern_type, query_example, severity, created_at
* Ordered by severity DESC (highest risk first)
- Implement remove_threat_pattern():
* Deletes from both anomaly_patterns and anomaly_patterns_vec tables
* Proper error handling with error messages
* Returns true on success, false on failure
- Improve get_statistics():
* Add threat_patterns_count to statistics
* Add threat_patterns_by_type breakdown
* Shows patterns grouped by type (sql_injection, dos, etc.)
- Add count_by_pattern_type query for categorization
Features:
- Full CRUD operations for threat patterns
- JSON-formatted output for API integration
- Statistics include both counts and categorization
- Proper cleanup of both main and virtual tables
Implemented embedding-based threat pattern detection using GenAI and sqlite-vec:
Changes to lib/Anomaly_Detector.cpp:
- Add GenAI_Thread.h include and GloGATH extern
- Implement get_query_embedding():
* Calls GloGATH->embed_documents() via llama-server
* Normalizes query before embedding for better quality
* Returns std::vector<float> with embedding
- Implement check_embedding_similarity():
* Generates embedding for query if not provided
* Performs sqlite-vec KNN search against anomaly_patterns table
* Uses cosine distance (vec_distance_cosine) for similarity
* Calculates risk score based on severity and distance
* Returns AnomalyResult with pattern details and blocking decision
- Implement add_threat_pattern():
* Generates embedding for threat pattern example
* Stores pattern with embedding in anomaly_patterns table
* Updates anomaly_patterns_vec virtual table for KNN search
* Returns pattern ID on success
Features:
- Semantic similarity detection against known threat patterns
- Configurable similarity threshold (ai_anomaly_similarity_threshold)
- Risk scoring based on pattern severity (1-10) and similarity
- Automatic threat pattern management with vector indexing
- 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
Bug Description:
ProxySQL would deadlock when processing extended query frames where:
1. Many Close Statement messages accumulate responses in PSarrayOUT
2. Total response size exceeds pgsql-threshold_resultset_size
3. A backend operation (Describe/Execute) follows in the same frame
Root Cause:
- Close Statement operations are handled locally by ProxySQL (no backend routing)
- Their CloseComplete responses accumulate in PSarrayOUT
- When threshold_resultset_size is exceeded, ProxySQL stops reading from backend
- Subsequent backend operations (Describe/Execute) need backend responses to complete
- This creates a deadlock: ProxySQL won't read, backend operation can't complete
- Extended query frame never finishes, query times out
The Fix:
When PSarrayOUT exceeds threshold_resultset_size and a backend operation is pending,
ProxySQL now flushes all accumulated data in PSarrayOUT to the client first, then
continues processing backend operations. This breaks the deadlock by clearing the
buffer before attempting to read more data from the backend.
Using bind message to obtain parameter information, rather than determining whether the query is parameterized from the query itself. Multiple parameters are not possible in this case, as PostgreSQL itself rejects multi-parameter pg_cancel_backend() and pg_terminate_backend() and only accepts a single parameter for these functions.
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).
The catalog_search() and catalog_list() methods in MySQL_Catalog.cpp
were manually building JSON strings by concatenating raw TEXT from
SQLite without proper escaping. This caused parse errors when stored
JSON contained quotes, backslashes, or newlines.
Changes:
- MySQL_Catalog.cpp: Use nlohmann::json to build proper nested JSON
in search() and list() methods instead of manual concatenation
- MySQL_Tool_Handler.cpp: Add try-catch for JSON parsing in catalog_get()
- test_catalog.sh: Fix MCP URL path, add jq extraction for MCP protocol
responses, add 3 special character tests (CAT013-CAT015)
Test Results: All 15 catalog tests pass, including new tests that
verify special characters (quotes, backslashes) are preserved.
jesmarcannao
Rene Cannao
Rahim Kanji
Rene Cannao
vramesha
Wazir Ahmed
Javier Jaramago Fernández
Miro Stauder