Using GenAI with a MySQL 8.0 Production Environment

README.md

A practical tutorial for generating AI results from your production 8.0 environment

See the blog Using GenAI directly in the database. A practical example using MySQL 8.0 for how we use a VillageSQL replica with the vsql-ai extension to summarize your support ticket data.

Example Support Ticket Schema

generate mermaid markdown for these tables.

erDiagram
    support_ticket {
        BIGINT ticket_id PK
        VARCHAR subject
        VARCHAR requester_name
        VARCHAR requester_email
        ENUM status
        ENUM priority
        VARCHAR assignee
        TIMESTAMP created_at
        TIMESTAMP updated_at
    }

    support_ticket_summary {
        BIGINT ticket_id PK,FK
        TEXT summary
        TIMESTAMP created_at
        TIMESTAMP updated_at
    }

    support_ticket_message {
        BIGINT message_id PK
        BIGINT ticket_id FK
        ENUM sender_type
        VARCHAR sender_name
        TEXT body
        TIMESTAMP created_at
    }

    support_ticket ||--|| support_ticket_summary : "has"
    support_ticket ||--o{ support_ticket_message : "has"

Loading

For demonstration purposes we will create a MySQL 8.0 replication topology using dbdeployer.

ARCH=$(uname -m)
MAJOR_VERSION="8.0"
MAJOR_VERSION_CLEAN=$(tr -d '.' <<< ${MAJOR_VERSION})
dbdeployer downloads list --version=${MAJOR_VERSION} --arch ${ARCH} --flavor mysql
DOWNLOAD=$(dbdeployer downloads list --version=${MAJOR_VERSION} --arch ${ARCH} --flavor mysql | tail -1 | awk '{print $1'})
dbdeployer downloads get ${DOWNLOAD}
dbdeployer unpack ${DOWNLOAD}
MINOR_VERSION=$(cd ~/opt/mysql/; ls -d ${MAJOR_VERSION}* | head -1)
dbdeployer deploy replication --gtid --sandbox-directory demo${MAJOR_VERSION_CLEAN} ${MINOR_VERSION}
cd ~/sandboxes/demo${MAJOR_VERSION_CLEAN}
master/use

Setup Test Schema and Data

mysql>  source support-ticket-tables.sql
mysql>  source support-ticket-data.sql

Setup of VillageSQL

On a separate server, or for demonstration purposes on your local machine, install

• VillageSQL
• vsql-ai extension.

For this demo I am using Gemini, however you can use Claude, OpenAI, or a local LLM such as Ollama.

Configure VillageSQL are replica

Running a default VillageSQL setup, and with dbdeployer installed locally you need to configure replication with:

SET GLOBAL GTID_MODE=OFF_PERMISSIVE;
SET GLOBAL GTID_MODE=ON_PERMISSIVE;
SET GLOBAL ENFORCE_GTID_CONSISTENCY=ON;
SET GLOBAL GTID_MODE=ON;
CHANGE REPLICATION SOURCE TO SOURCE_HOST='127.0.0.1', SOURCE_PORT=22435, SOURCE_USER="rsandbox", SOURCE_PASSWORD="rsandbox",  SOURCE_AUTO_POSITION=1;
SHOW WARNINGS;
START REPLICA;
SHOW REPLICA STATUS\G

Appendix A - List of downloads for given version/arch/flavor

$ dbdeployer downloads list --version=${MAJOR_VERSION} --arch ${ARCH} --flavor mysql

 mysql-8.0.30-macos12-arm64.tar.gz                   Darwin-arm64     8.0.30   mysql         176 MB
 mysql-8.0.31-macos12-arm64.tar.gz                   Darwin-arm64     8.0.31   mysql         181 MB
 mysql-8.0.32-macos13-arm64.tar.gz                   Darwin-arm64     8.0.32   mysql         183 MB
 mysql-8.0.33-macos13-arm64.tar.gz                   Darwin-arm64     8.0.33   mysql         186 MB
 mysql-8.0.34-macos13-arm64.tar.gz                   Darwin-arm64     8.0.34   mysql         184 MB

example_output.md

villagesql> SELECT * FROM support_ticket_summary LIMIT 1\G
*************************** 1. row ***************************
 ticket_id: 1
   summary: Here is a summary of the support ticket regarding the installation, configuration, and port customization of Ollama on macOS and Linux.

---

### **Ticket Summary**

*   **Customer Goal:** Install Ollama to run local LLMs on macOS and a Linux server, and resolve a port conflict by changing Ollama's default port (11434).
*   **Status:** Resolved successfully.

---

### **Key Solutions Provided**

#### **1. macOS Installation & Setup**
*   **Installation:** Can be downloaded via the official installer (DMG) at [ollama.com/download](https://ollama.com/download) or installed via Homebrew (`brew install ollama`).
*   **Verification & Running:**
    *   Verify with `ollama --version`.
    *   Run a model using `ollama run llama3.2`.
    *   The default API runs locally at `http://localhost:11434`.

#### **2. Linux Installation & Setup**
*   **Installation:** Installed using the official one-line script:
    `curl -fsSL https://ollama.com/install.sh | sh`
*   **Service Management:** Managed via systemd (`sudo systemctl enable --now ollama`).
*   **GPU Support:** Ollama automatically detects and utilizes NVIDIA (CUDA) and AMD (ROCm) GPUs if the appropriate drivers are installed.

#### **3. Resolving Port Conflicts (Changing the default port from 11434)**
The port can be changed by setting the `OLLAMA_HOST` environment variable (e.g., to port `11500`):

*   **Temporary (Current Shell):**
    `export OLLAMA_HOST=127.0.0.1:11500` (Use `0.0.0.0:11500` to bind to all interfaces).
*   **Persistent macOS Configuration:**
    Run `launchctl setenv OLLAMA_HOST "127.0.0.1:11500"` and relaunch the Ollama application.
*   **Persistent Linux Configuration (systemd):**
    Use `sudo systemctl edit ollama` to add the environment variable:
    ```ini
    [Service]
    Environment="OLLAMA_HOST=0.0.0.0:11500"
    ```
    Followed by a service reload and restart:
    `sudo systemctl daemon-reload && sudo systemctl restart ollama`
*   **Running Clients on Custom Port:** Clients must point to the new port either by exporting the variable or prefixing the command (e.g., `OLLAMA_HOST=127.0.0.1:11500 ollama run llama3.2`).
created_at: 2026-06-14 16:35:47
updated_at: 2026-06-14 16:35:47
1 row in set (0.00 sec)

support-ticket-data.sql

INSERT INTO support_ticket (subject, requester_name, requester_email, status, priority, assignee)
VALUES ('How to install and set up Ollama on macOS', 'Jane Doe', 'jane.doe@example.com', 'open', 'normal', 'support_agent');

SET @tid = LAST_INSERT_ID();

INSERT INTO support_ticket_message (ticket_id, sender_type, sender_name, body) VALUES
(@tid, 'requester', 'Jane Doe',
'Hi, I just got a new Mac and I want to run local LLMs. Can you tell me how to install and set up Ollama on macOS?'),

(@tid, 'agent', 'support_agent',
'Sure! Here is how to install and set up Ollama on a Mac:

1. Download the installer
   Go to https://ollama.com/download and download the macOS version, or use Homebrew:
     brew install ollama

2. Install
   If you downloaded the .dmg, open it and drag Ollama into your Applications folder. Launch it once so the background service starts. With Homebrew the binary is installed automatically.

3. Verify the install
   Open Terminal and run:
     ollama --version

4. Pull and run a model
     ollama run llama3.2
   The first run downloads the model; subsequent runs start instantly.

5. Confirm the service is running
   Ollama serves an API at http://localhost:11434 by default. Test it with:
     curl http://localhost:11434/api/tags

That is it - you now have a local LLM running on your Mac. Let me know if you hit any issues!'),

(@tid, 'requester', 'Jane Doe',
'That worked great, thanks! Quick follow-up - I also have a Linux server. How do I install Ollama there?'),

(@tid, 'agent', 'support_agent',
'Happy to help with Linux:

1. Install via the official script
   The simplest method is the one-line installer:
     curl -fsSL https://ollama.com/install.sh | sh
   This installs the ollama binary and sets up a systemd service automatically on most distributions.

2. Verify the install
     ollama --version

3. Manage the service
   On systems with systemd:
     sudo systemctl status ollama
     sudo systemctl enable --now ollama
   This ensures Ollama starts on boot and is running now.

4. Pull and run a model
     ollama run llama3.2

5. (Optional) GPU support
   If you have an NVIDIA GPU, install the appropriate CUDA drivers - Ollama will detect and use the GPU automatically. For AMD GPUs, ROCm-supported cards work as well.

6. Confirm the API
     curl http://localhost:11434/api/tags

Let me know if you need help with a specific distribution.'),

(@tid, 'requester', 'Jane Doe',
'Perfect. One more thing - port 11434 conflicts with another service on my machine. How do I run Ollama on a different port?'),

(@tid, 'agent', 'support_agent',
'You can change the port using the OLLAMA_HOST environment variable.

Temporary (current shell only):
  export OLLAMA_HOST=127.0.0.1:11500
  ollama serve
All client commands in that same shell will then talk to the new port. To bind on all interfaces use 0.0.0.0:11500 instead.

macOS (persistent):
  launchctl setenv OLLAMA_HOST "127.0.0.1:11500"
Then quit and relaunch the Ollama app.

Linux with systemd (persistent):
  sudo systemctl edit ollama
Add the following:
  [Service]
  Environment="OLLAMA_HOST=0.0.0.0:11500"
Then reload and restart:
  sudo systemctl daemon-reload
  sudo systemctl restart ollama

Pointing the client at the new port:
  export OLLAMA_HOST=127.0.0.1:11500
  ollama run llama3.2
Or per-command without exporting:
  OLLAMA_HOST=127.0.0.1:11500 ollama list

Verify:
  curl http://127.0.0.1:11500/api/tags

That will get Ollama off the default 11434 and onto your chosen port.');

INSERT INTO support_ticket (subject, requester_name, requester_email, status, priority, assignee)
VALUES ('Help testing the Ollama API on macOS', 'Tom Reyes', 'tom.reyes@example.com', 'open', 'normal', 'support_agent');

SET @tid = LAST_INSERT_ID();

INSERT INTO support_ticket_message (ticket_id, sender_type, sender_name, body) VALUES
(@tid, 'requester', 'Tom Reyes',
'Hey, I have Ollama installed and running on my Mac. How can I test the API directly? I want to send a prompt and get a response back without using the ollama run command.'),

(@tid, 'agent', 'support_agent',
'Good question! Ollama exposes a REST API at http://localhost:11434 by default. Here are a few ways to test it from Terminal.

1. Check that the server is up and see installed models:
     curl http://localhost:11434/api/tags

2. Send a generate request (single prompt):
     curl http://localhost:11434/api/generate -d ''{
       "model": "llama3.2",
       "prompt": "Why is the sky blue?",
       "stream": false
     }''

Setting "stream": false returns one complete JSON object instead of a token-by-token stream. Make sure you have pulled the model first with: ollama pull llama3.2

Give that a try and let me know what you get back.'),

(@tid, 'requester', 'Tom Reyes',
'That worked! The response came back as JSON. But the output had a bunch of extra fields like total_duration and eval_count. I just want the text of the answer. How do I pull out only that?'),

(@tid, 'agent', 'support_agent',
'You can pipe the response into jq to extract just the text. The generate endpoint returns the answer in the "response" field:

     curl -s http://localhost:11434/api/generate -d ''{
       "model": "llama3.2",
       "prompt": "Why is the sky blue?",
       "stream": false
     }'' | jq -r ''.response''

The -s flag silences the progress meter, and jq -r prints the raw string without quotes. If you do not have jq, install it with: brew install jq'),

(@tid, 'requester', 'Tom Reyes',
'Nice, that is exactly what I needed. Last thing - is there a chat endpoint too? I want to send a conversation with multiple messages, not just a single prompt.'),

(@tid, 'agent', 'support_agent',
'Yes, use the /api/chat endpoint. It takes a messages array with roles, so you can pass a full conversation:

     curl -s http://localhost:11434/api/chat -d ''{
       "model": "llama3.2",
       "messages": [
         { "role": "user", "content": "Hello, who won the World Cup in 2018?" },
         { "role": "assistant", "content": "France won the 2018 FIFA World Cup." },
         { "role": "user", "content": "Who did they beat in the final?" }
       ],
       "stream": false
     }'' | jq -r ''.message.content''

Note the response text lives in .message.content for the chat endpoint (versus .response for generate). To continue the conversation, append the assistant''s reply to the messages array and send it again. Let me know if you want a small shell script that keeps the history for you.'),

(@tid, 'requester', 'Tom Reyes',
'This is great, thanks for all the help! I think I have everything I need for now.');

UPDATE support_ticket SET status = 'resolved' WHERE ticket_id = @tid;

INSERT INTO support_ticket (subject, requester_name, requester_email, status, priority, assignee)
VALUES ('Getting and validating a Claude (Anthropic) API key', 'Maria Chen', 'maria.chen@example.com', 'open', 'normal', 'support_agent');

SET @tid = LAST_INSERT_ID();

INSERT INTO support_ticket_message (ticket_id, sender_type, sender_name, body) VALUES
(@tid, 'requester', 'Maria Chen',
'Hi, I want to start building with the Claude API. Where do I get an API key, and how do I check that it actually works before I wire it into my app?'),

(@tid, 'agent', 'support_agent',
'Welcome! Here is how to get a key and validate it.

1. Create an account
   Go to https://console.anthropic.com and sign up (or log in).

2. Generate an API key
   In the Console, open Settings -> API Keys, click "Create Key", give it a name, and copy the value immediately. Anthropic keys look like sk-ant-api03-... and are only shown once, so store it somewhere safe. If you lose it, just create a new one and revoke the old.

3. Store it as an environment variable
   Avoid hardcoding the key in source. On macOS/Linux:
     export ANTHROPIC_API_KEY="sk-ant-api03-your-key-here"
   Add that line to your ~/.bashrc (or ~/.zshrc) to make it persistent.

4. Validate it with a quick request
   The simplest check is a minimal call to the Messages endpoint:

     curl https://api.anthropic.com/v1/messages \
       -H "x-api-key: $ANTHROPIC_API_KEY" \
       -H "anthropic-version: 2023-06-01" \
       -H "content-type: application/json" \
       -d ''{
         "model": "claude-sonnet-4-6",
         "max_tokens": 64,
         "messages": [
           { "role": "user", "content": "Reply with just the word: pong" }
         ]
       }''

   If the key is valid you get back a JSON object containing a content array with the model''s reply. If the key is bad you will get an HTTP 401 with an authentication_error.

Note the three required headers: x-api-key (your key), anthropic-version (use 2023-06-01), and content-type. Let me know how it goes!'),

(@tid, 'requester', 'Maria Chen',
'Got a response back, so the key works. I would rather not parse raw JSON by hand though - is there a cleaner way to just confirm success and see the text?'),

(@tid, 'agent', 'support_agent',
'Two good options.

Option A - pipe curl through jq to extract just the text. For the Messages API the reply lives in .content[0].text:

     curl -s https://api.anthropic.com/v1/messages \
       -H "x-api-key: $ANTHROPIC_API_KEY" \
       -H "anthropic-version: 2023-06-01" \
       -H "content-type: application/json" \
       -d ''{
         "model": "claude-sonnet-4-6",
         "max_tokens": 64,
         "messages": [ { "role": "user", "content": "Reply with just the word: pong" } ]
       }'' | jq -r ''.content[0].text''

   Install jq with: brew install jq (macOS) or sudo apt install jq (Linux).

Option B - use the official Python SDK, which reads ANTHROPIC_API_KEY automatically:

     pip install anthropic

     #!/usr/bin/env python
     import anthropic

     client = anthropic.Anthropic()  # picks up ANTHROPIC_API_KEY from the environment

     message = client.messages.create(
         model="claude-sonnet-4-6",
         max_tokens=64,
         messages=[{"role": "user", "content": "Reply with just the word: pong"}],
     )
     print(message.content[0].text)

   If that prints the reply with no auth error, your key and environment are set up correctly.'),

(@tid, 'requester', 'Maria Chen',
'The Python version worked perfectly. Last question - if I want to confirm the key is valid without spending tokens on a full generation, is there a lighter-weight way to check?'),

(@tid, 'agent', 'support_agent',
'Yes - use the token-counting endpoint. It validates your key and request without actually generating a response (and so does not consume output tokens):

     curl -s https://api.anthropic.com/v1/messages/count_tokens \
       -H "x-api-key: $ANTHROPIC_API_KEY" \
       -H "anthropic-version: 2023-06-01" \
       -H "content-type: application/json" \
       -d ''{
         "model": "claude-sonnet-4-6",
         "messages": [ { "role": "user", "content": "ping" } ]
       }''

A 200 response with an input_tokens count means your key authenticates correctly. A 401 means the key is wrong or not set. This is a nice cheap health check to run in CI or at app startup. Happy building!');

UPDATE support_ticket SET status = 'resolved' WHERE ticket_id = @tid;

support-ticket-summary.sql

SET @GEMINI_API_KEY='<paste key here>';
SET SESSION group_concat_max_len = 1000000;
INSERT INTO support_ticket_summary (ticket_id, summary)
SELECT ticket_id, ai_prompt('google', 'gemini-3.5-flash', @GEMINI_API_KEY, CONCAT('Summarize support ticket information of ',GROUP_CONCAT(body)))
FROM support_ticket_message
WHERE ticket_id NOT IN (select ticket_id FROM support_ticket_summary)
GROUP BY ticket_id;

SELECT * from support_ticket_summary;

support-ticket-tables.sql

DROP SCHEMA IF EXISTS aidemo;
CREATE SCHEMA aidemo;
USE aidemo;

CREATE TABLE support_ticket (
  ticket_id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  subject        VARCHAR(255) NOT NULL,
  requester_name VARCHAR(100) NOT NULL,
  requester_email VARCHAR(255) NOT NULL,
  status         ENUM('open','pending','resolved','closed') NOT NULL DEFAULT 'open',
  priority       ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  assignee       VARCHAR(100) DEFAULT NULL,
  created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (ticket_id),
  KEY idx_status (status),
  KEY idx_requester_email (requester_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE support_ticket_summary (
  ticket_id   BIGINT UNSIGNED NOT NULL,
  summary     TEXT NOT NULL,
  created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (ticket_id),
  CONSTRAINT fk_message_support FOREIGN KEY (ticket_id)
    REFERENCES support_ticket (ticket_id) ON DELETE CASCADE
);

CREATE TABLE support_ticket_message (
  message_id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ticket_id   BIGINT UNSIGNED NOT NULL,
  sender_type ENUM('requester','agent') NOT NULL,
  sender_name VARCHAR(100) NOT NULL,
  body        TEXT NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (message_id),
  KEY idx_ticket_id (ticket_id),
  CONSTRAINT fk_message_ticket FOREIGN KEY (ticket_id)
    REFERENCES support_ticket (ticket_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ticket-1.md

Ticket Summary

• Customer Goal: Install Ollama to run local LLMs on macOS and a Linux server, and resolve a port conflict by changing Ollama's default port (11434).
• Status: Resolved successfully.

---

Key Solutions Provided

1. macOS Installation & Setup

• Installation: Can be downloaded via the official installer (DMG) at ollama.com/download or installed via Homebrew (brew install ollama).
• Verification & Running:
  • Verify with ollama --version.
  • Run a model using ollama run llama3.2.
  • The default API runs locally at http://localhost:11434.

2. Linux Installation & Setup

• Installation: Installed using the official one-line script: curl -fsSL https://ollama.com/install.sh | sh
• Service Management: Managed via systemd (sudo systemctl enable --now ollama).
• GPU Support: Ollama automatically detects and utilizes NVIDIA (CUDA) and AMD (ROCm) GPUs if the appropriate drivers are installed.

3. Resolving Port Conflicts (Changing the default port from 11434)

The port can be changed by setting the OLLAMA_HOST environment variable (e.g., to port 11500):

• Temporary (Current Shell): export OLLAMA_HOST=127.0.0.1:11500 (Use 0.0.0.0:11500 to bind to all interfaces).
• Persistent macOS Configuration: Run launchctl setenv OLLAMA_HOST "127.0.0.1:11500" and relaunch the Ollama application.
• Persistent Linux Configuration (systemd): Use sudo systemctl edit ollama to add the environment variable:

  [Service]
  Environment="OLLAMA_HOST=0.0.0.0:11500"

  Followed by a service reload and restart: sudo systemctl daemon-reload && sudo systemctl restart ollama
• Running Clients on Custom Port: Clients must point to the new port either by exporting the variable or prefixing the command (e.g., OLLAMA_HOST=127.0.0.1:11500 ollama run llama3.2).