MySQL Connector

Connect to MySQL and MariaDB databases for natural language queries. ZenSearch discovers your schema and lets users ask questions in plain English — the AI generates and executes SQL queries automatically.

Overview

The MySQL connector is a database connector — it does not ingest documents. Instead, it enables real-time natural language to SQL queries through ZenSearch's AI agent tools. When a user asks a question, the AI agent:

1. Discovers relevant tables and columns from the schema
2. Generates a SQL query
3. Validates it for safety (read-only, no destructive operations)
4. Executes it against your database
5. Synthesizes a natural language answer from the results

Supported Databases

• MySQL 5.7+ and 8.x
• MariaDB 10.x+

Prerequisites

• MySQL or MariaDB database accessible from ZenSearch
• A database user with read-only access
• Network connectivity between ZenSearch and the database (firewall rules, VPN, etc.)

Configuration Reference

Setting	Type	Required	Description
Host	string	Yes	Database hostname or IP address
Port	integer	Yes	Database port (default: 3306)
Database	string	Yes	Database name to connect to
Username	string	Yes	Database user
Password	string	Yes	User password
TLS Mode	string	No	TLS encryption mode: disabled, preferred, required, or skip-verify
Charset	string	No	Character set (default: utf8mb4)
Include Tables	array	No	Specific tables to include (empty = all tables)
Exclude Tables	array	No	Tables to exclude from schema discovery
Include Views	boolean	No	Include database views in schema discovery
Max Rows Per Query	integer	No	Maximum rows returned per query (0–10,000)
Query Timeout	integer	No	Query timeout in seconds (0–300)
Sample Row Count	integer	No	Number of sample rows to fetch for schema understanding
Refresh Interval	string	No	How often to refresh the schema cache

TLS Modes

Mode	Description
disabled	No encryption (not recommended for production)
preferred	Use TLS if available, fall back to unencrypted
required	Require TLS — fail if not available
skip-verify	Use TLS but skip certificate verification (testing only)

Setup Steps

1. Add Connector: Navigate to Knowledge → Add Data Source → MySQL
2. Enter Connection Details: Host, port, and database name
3. Provide Credentials: Username and password for your read-only user
4. Configure TLS: Select the appropriate TLS mode for your environment
5. Filter Tables (optional): Specify which tables to include or exclude
6. Test & Create: Verify the connection and save

How Natural Language Queries Work

Once connected, users can ask questions like:

• "What were our top 10 customers by revenue last quarter?"
• "How many orders were placed in January?"
• "Show me all products with inventory below 50 units"

The AI agent uses the discovered schema (table names, column names, data types, and sample values) to generate accurate SQL. If a query fails, the error is fed back to the AI for automatic correction.

Query Safety

All generated queries are validated before execution:

• Blocked operations: DROP, DELETE, INSERT, UPDATE, TRUNCATE, ALTER, CREATE, GRANT, REVOKE
• Read-only enforcement: Only SELECT queries are permitted
• Row limits: Configurable maximum rows prevent runaway queries
• Timeout protection: Queries are terminated if they exceed the configured timeout

Creating a Read-Only User

-- Create a dedicated user for ZenSearch
CREATE USER 'zensearch'@'%' IDENTIFIED BY 'your_secure_password';

-- Grant read-only access to specific database
GRANT SELECT ON your_database.* TO 'zensearch'@'%';

-- Apply changes
FLUSH PRIVILEGES;

For MariaDB, the syntax is identical.

Best Practices

1. Use a dedicated read-only user — Never connect with admin or write-capable credentials
2. Enable TLS for production — Use required mode for encrypted connections
3. Add table and column comments — The AI uses comments to understand your schema better, improving query accuracy:

   ALTER TABLE orders COMMENT = 'Customer purchase orders with line items';
   ALTER TABLE orders MODIFY COLUMN status VARCHAR(50) COMMENT 'Order status: pending, shipped, delivered, cancelled';

4. Limit to necessary tables — Use include_tables or exclude_tables to expose only relevant tables
5. Set reasonable timeouts — Configure query_timeout_seconds to prevent long-running analytical queries from blocking resources
6. Use sample rows — Configure sample_row_count to help the AI understand data patterns and column value formats

Troubleshooting

Access denied

• Verify the username and password are correct
• Check that the user has SELECT privileges on the target database
• Ensure the user is allowed to connect from ZenSearch's IP address ('zensearch'@'%' allows all hosts)

Connection timeout

• Verify network connectivity between ZenSearch and the MySQL server
• Check firewall rules allow traffic on the configured port (default: 3306)
• For cloud databases, verify the database is configured to accept external connections

SSL/TLS errors

• If using required mode, ensure the MySQL server has TLS configured
• For self-signed certificates, use skip-verify during testing (not recommended for production)
• Verify the TLS certificate chain is valid and not expired

Query returns no results

• Check that the table contains data
• Verify the user has SELECT access to the specific table
• Try rephrasing the question to be more specific

Related

• PostgreSQL
• ClickHouse
• MS SQL Server