> For clean Markdown of any page, append .md to the page URL. > For a complete documentation index, see https://developers.alephant.io/llms.txt. > For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://developers.alephant.io/_mcp/server. # MySQL > Configure the MySQL connector to read structured knowledge from approved databases, tables, and SQL queries. The MySQL connector turns approved relational rows into searchable documents. It supports two indexing modes: * **Automatic discovery** reads accessible base tables in the selected database, lets administrators select tables and columns, and maps rows into documents. * **Custom SQL** runs a controlled `SELECT` or `WITH` query and maps returned columns into document ID, title, searchable content, metadata, and update time. Use a dedicated read-only account and expose only curated tables or views. Do not connect a broad production account to a database that contains unapproved personal data, credentials, tokens, or operational secrets. ## What You Need | Item | Requirement | | ------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Network route | AIvis workers must be able to reach the MySQL host and port. Use private networking, a database proxy, or an allowlisted egress IP. | | Credential | A dedicated MySQL account with `SELECT` only on approved tables or views. | | Data scope | Prefer sanitized views for sensitive tables, joins, aggregates, or field renaming. | | Stable identity | Use a primary key, a non-null unique key, or a stable `id_column` in Custom SQL. | | Update field | Prefer a `datetime` or `timestamp` column such as `updated_at` for incremental sync. | | Transport security | The current MySQL credential form exposes host, port, database, username, and password only. If your database requires client TLS options, place the database behind an approved private endpoint or proxy before enforcing that policy for this connector. | ## Create a Read-Only MySQL Account Run grants from a database administrator account. Adjust host pattern, database, table, and view names to match your environment. ```sql CREATE USER 'aivis_reader'@'10.0.%' IDENTIFIED BY '' WITH MAX_USER_CONNECTIONS 3; GRANT SELECT ON knowledge.product_catalog_view TO 'aivis_reader'@'10.0.%'; GRANT SELECT ON knowledge.faq_article_view TO 'aivis_reader'@'10.0.%'; SHOW GRANTS FOR 'aivis_reader'@'10.0.%'; ``` MySQL supports account-level TLS requirements such as `REQUIRE SSL`. Enable them for this account only when your AIvis deployment path provides compatible MySQL TLS handling, for example through an approved private database proxy. Create views when the source tables contain sensitive columns: ```sql CREATE VIEW knowledge.product_catalog_view AS SELECT id, name, status, public_summary, category, updated_at FROM product_catalog WHERE searchable = 1; GRANT SELECT ON knowledge.product_catalog_view TO 'aivis_reader'@'10.0.%'; ``` Use database-level grants only for a database that is already curated for AIvis indexing: ```sql GRANT SELECT ON aivis_public.* TO 'aivis_reader'@'10.0.%'; ``` ## Create the Credential | AIvis Field | Recommended Value | Notes | | ----------- | ---------------------------------------------------- | ------------------------------------------------------------- | | Host | MySQL host name or private endpoint. | Do not include protocol prefixes such as `mysql://`. | | Port | `3306` unless your deployment uses a custom port. | The UI defaults to `3306`. | | Database | The database containing approved tables or views. | Automatic discovery reads base tables in this database. | | Username | Dedicated read-only account, such as `aivis_reader`. | Avoid root, owner, migration, or application writer accounts. | | Password | Generated password for the read-only account. | Rotate it through your normal credential process. | The connector uses `utf8mb4` when reading MySQL rows and sets a 10-second connection timeout. ## Choose an Indexing Mode | Mode | Use When | Boundary | | ------------------- | ------------------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------- | | Automatic discovery | You want AIvis to discover accessible base tables in one database and let an admin select tables and fields. | It discovers base tables, not arbitrary joins or views. Use Custom SQL for views, joins, aggregates, or renamed fields. | | Custom SQL | You need a curated view, join, filter, projection, or stable row shape. | The query must be a single `SELECT` or `WITH` statement and return one row per document. | ### Automatic Discovery Automatic discovery reads accessible base tables in the selected database and ignores MySQL system schemas such as `information_schema`, `mysql`, `performance_schema`, and `sys`. In the schema tree: * Select only tables and fields that are approved for indexing. * Binary, geometry, and blob-like columns are not indexed. * AIvis uses primary keys first, then non-null unique indexes, then a row hash when no stable key exists. * Tables without a stable key can create new document IDs when selected values change; prefer adding a primary key or using Custom SQL with an explicit ID. * The default title field is inferred from columns such as `title`, `name`, `subject`, or `label`; you can override it in table settings. * The update field is inferred from datetime columns such as `updated_at`, `updated`, `modified_at`, `modified`, or `last_modified`; no update field means the table requires full scans. ## Custom SQL Requirements The connector wraps your query as a subquery and validates returned columns. Keep the SQL read-only and deterministic. | Field | Requirement | | ----------------- | ------------------------------------------------------------------------------------------------- | | SQL Query | A single `SELECT` or `WITH` query. Do not include a trailing semicolon or multiple statements. | | ID Column | A non-empty unique value per row. Cast compound keys to text when needed. | | Title Column | A human-readable title used as the document name. | | Content Columns | One or more columns whose values become searchable document text. | | Metadata Columns | Optional fields stored as metadata for filtering or debugging. | | Updated At Column | Optional `datetime` or `timestamp` column used to poll only rows updated in each sync window. | | Batch Size | Number of rows fetched per batch. The UI default is `16`; increase only after testing query cost. | Example: ```sql SELECT CAST(p.id AS CHAR) AS doc_id, p.name AS title, CONCAT_WS( '\n', CONCAT('Status: ', p.status), CONCAT('Category: ', p.category), CONCAT('Summary: ', p.public_summary) ) AS body, p.category, p.updated_at FROM knowledge.product_catalog_view AS p WHERE p.searchable = 1 ``` For this query, set: | AIvis Field | Value | | ----------------- | ------------ | | ID Column | `doc_id` | | Title Column | `title` | | Content Columns | `body` | | Metadata Columns | `category` | | Updated At Column | `updated_at` | ## Production Safety The connector opens MySQL reads in a read-only transaction and sets `max_execution_time` during reads. That protects AIvis from writing data, but it does not replace database-side controls. Before production: * Use a replica, analytics database, or curated view layer when possible. * Add indexes that support your Custom SQL filters and update window. * Avoid `SELECT *` in Custom SQL; explicitly return only approved columns. * Avoid long-running joins against hot OLTP tables. * Keep batch size modest until query plans and sync duration are measured. * Restrict the MySQL account host pattern to the AIvis network path rather than `%`. * Do not enforce account-level MySQL TLS requirements until the deployed connection path supports them; use private networking or an approved proxy for protected routes. * Verify that query plans do not create heavy locks, full scans on large tables, or excessive temporary tables. ## Verification 1. Confirm the credential connects with the read-only account. 2. In Automatic discovery, confirm the expected database tables and fields appear. 3. In Custom SQL, validate that the query returns every configured column. 4. Run an initial sync and inspect row count, indexed document count, and failures. 5. Search for representative records by title and content. 6. Confirm unselected tables, fields, and sensitive columns are absent from search. 7. Update one test row and confirm the same document updates when an update field is configured. 8. Test as a user outside the connector audience and confirm restricted documents do not appear. ## Troubleshooting | Symptom | Likely Cause | Resolution | | ------------------------------- | ------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------- | | Authentication failed | Wrong account, password, database, host, or port. | Test from the deployment network with the same account and rotate the password if needed. | | Permission denied | Missing `SELECT` on the selected table or view. | Grant the minimum missing `SELECT` privilege to the read-only account. | | No tables discovered | The account cannot see any base tables, or only views were granted. | Grant selected base tables for Automatic discovery, or use Custom SQL for views. | | Custom SQL rejected | Query is blank, has multiple statements, ends with a semicolon plus extra text, or does not start with `SELECT` / `WITH`. | Use one read-only query and return explicit aliases for mapped columns. | | Missing column error | The query does not return the configured ID, title, content, metadata, or update column. | Add aliases or update field mapping. | | Invalid update field | The update column is not a MySQL `datetime` or `timestamp` value. | Use a datetime-compatible field, or leave update field empty and accept full scans. | | Duplicate or changing documents | No stable identity exists, or the configured ID changes over time. | Use primary keys, non-null unique keys, or a stable `id_column`. | | Sync times out | Query cost exceeds `max_execution_time` or batch size is too high. | Add indexes, reduce scope, lower batch size, or move indexing to a replica/view. | | TLS-required connection fails | The server account or database proxy requires TLS options not exposed by the current credential form. | Use a private endpoint/proxy that terminates or handles the required TLS path, or adjust deployment support before enforcing account-level TLS. | ## Related Official Documentation * [MySQL `CREATE USER`](https://dev.mysql.com/doc/refman/8.4/en/create-user.html) * [MySQL `GRANT`](https://dev.mysql.com/doc/refman/8.4/en/grant.html) * [MySQL encrypted connections](https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html)