# How to Debug Text2SQL Generation in DB-GPT's Auto-Execute Mode: A Complete Guide

> Debug Text2SQL generation in DB-GPT auto-execute mode by enabling verbose logging and tracing the pipeline. Isolate failures in schema retrieval, prompt construction, LLM generation, and output parsing.

- Repository: [eosphoros/DB-GPT](https://github.com/eosphoros-ai/db-gpt)
- Tags: how-to-guide
- Published: 2026-02-23

---

**Enable verbose logging and trace the pipeline through schema retrieval, prompt construction, LLM generation, and output parsing to isolate Text2SQL failures in DB-GPT's auto_execute mode.**

DB-GPT's auto-execute mode automates the full Text2SQL workflow, from natural language understanding to SQL execution against your datasource. When the generated SQL fails, returns empty results, or produces syntax errors, you need a systematic approach to debug Text2SQL generation in DB-GPT's auto_execute mode. This guide walks you through the exact source files and debugging techniques used by the core maintainers.

## Understanding the Auto-Execute Pipeline

The auto-execute flow in `ChatWithDbAutoExecute` follows a strict six-stage pipeline. A failure at any stage cascades downstream, so identifying the exact breakpoint is critical.

| Stage | Component | Key Source File |
|-------|-----------|-----------------|
| **Request Handling** | `/v1/chat/completions` instantiates `ChatWithDbAutoExecute` | [`packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py) |
| **Schema Retrieval** | `generate_input_values` gathers table metadata via `DBSummaryClient` or `Connector.table_simple_info` | [`packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py) |
| **Prompt Construction** | `AppScenePromptTemplateAdapter` builds the final prompt using `RESPONSE_FORMAT_SIMPLE` | [`packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/prompt.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/prompt.py) |
| **LLM Call** | Request routed to the `text2sql_proxyllm` model worker | [`packages/dbgpt-app/src/dbgpt_app/openapi/api_v1/api_v1.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/openapi/api_v1/api_v1.py) |
| **Response Parsing** | `DbChatOutputParser` extracts `sql`, `thoughts`, `display_type` from JSON or raw SQL | [`packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/out_parser.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/out_parser.py) |
| **SQL Execution** | `ChatWithDbAutoExecute.do_action` calls `self.database.run_to_df` | [`packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py) |

## Step-by-Step Debugging Workflow

### Enable Verbose Logging

Set the environment variables before starting the DB-GPT server to capture the full execution trace:

```bash
export DEBUG=True
export LOG_LEVEL=DEBUG

```

With `DEBUG` enabled, the logs in [`chat.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/chat.py) and [`out_parser.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/out_parser.py) emit critical diagnostics:

- `Retrieved table info error: …` – indicates schema fallback to `table_simple_info`.
- `clean prompt response: …` – shows the raw LLM output after stripping markdown fences.
- `parse_view_response error! …` – flags execution-time failures in the view layer.

### Inspect Input Values and Schema Retrieval

The `generate_input_values` method in [`chat.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/chat.py) prepares the context dictionary sent to the LLM. Add explicit logging to verify schema integrity:

```python
input_vals = await chat_instance.generate_input_values()
logger.debug("Text2SQL input values: %s", json.dumps(input_vals, ensure_ascii=False, indent=2))

```

Verify these critical fields:

- **`db_name`** – Must match a registered connector in `ConnectorManager`.
- **`table_info`** – JSON array of tables/columns; check for truncation if `schema_max_tokens` is exceeded.
- **`dialect`** – Database type (e.g., `mysql`, `sqlite`) must match the target engine.
- **`top_k`** – Row limit injected into the prompt to prevent unbounded results.

If `table_info` is empty, check `DBSummaryClient` in [`db_summary_client.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/db_summary_client.py) or the fallback `Connector.table_simple_info` in your datasource connector.

### Verify Prompt Construction

The final prompt is rendered by `AppScenePromptTemplateAdapter` using the template defined in [`prompt.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/prompt.py). Dump the rendered prompt to verify placeholder substitution:

```python
from dbgpt_app.scene.chat_db.auto_execute.prompt import prompt_adapter
logger.debug("Full prompt sent to LLM:\n%s", prompt_adapter.prompt.render(input_vals))

```

Check for:

- Correct injection of `{db_name}`, `{table_info}`, and `{user_input}`.
- Presence of `RESPONSE_FORMAT_SIMPLE` JSON schema, which dictates the expected LLM output structure.

### Confirm LLM Model Registration

The auto-execute mode requires the `text2sql_proxyllm` model worker. Verify registration via the API:

```bash
curl -X GET http://localhost:7860/api/v1/model/types

```

The response must include `"text2sql_proxyllm"`. If absent, start the corresponding LLM worker (OpenAI, Llama, or local proxy) and ensure it registers with the `WorkerManager` under the correct model name.

### Capture Raw LLM Output

Before `DbChatOutputParser` processes the response, capture the raw string to diagnose formatting issues:

```python
raw_out = await llm_client.call(prompt)  # Low-level LLM call

logger.debug("Raw LLM output:\n%s", raw_out)

```

Common anomalies:

- **Pure SQL without JSON** – Parser treats this as `SqlAction(sql, "", "", "")`, which may work for simple queries but loses metadata.
- **Markdown fences** – The parser strips ```sql blocks, but explanatory text outside fences can break JSON parsing.
- **Truncated JSON** – Indicates context length exceeded; reduce `table_info` size or increase model token limit.

### Validate Parser Extraction

Test the parser independently on saved raw output:

```python
from dbgpt_app.scene.chat_db.auto_execute.out_parser import DbChatOutputParser
parser = DbChatOutputParser()
action = parser.parse_prompt_response(raw_out)
logger.debug("Parsed action: %s", action.to_dict())

```

Validation checklist:

- `action.sql` contains a single, syntactically valid statement (verify with `sqlparse`).
- `action.thoughts` explains the query logic (useful for debugging LLM reasoning).
- `action.display` specifies the visualization type (`response_table`, `response_line_chart`, etc.).

If `action.sql` is empty, the parser likely failed to detect JSON or SQL patterns; review the `parse_prompt_response` implementation in `out_parser.py`.

### Test SQL Execution Manually

Isolate execution issues from generation issues by running the parsed SQL directly:

```bash

# For SQLite

sqlite3 /path/to/db.sqlite "$(echo "$SQL")"

# For MySQL

mysql -u user -p -e "$SQL" database_name

```

If manual execution fails, check:

- **Datasource configuration** in `ConnectorManager.get_connector`.
- **Driver compatibility** (e.g., `pymysql`, `psycopg2` installed).
- **Network access** and credentials for remote databases.

### Check View Rendering

If SQL executes successfully but the UI displays errors, inspect `parse_view_response` in `out_parser.py`. Common issues:

- Missing `display_type` defaults to `response_table`, which may not suit the data shape.
- `parse_vector_data_with_pca` requires `scikit-learn`; absence raises ImportError.

Install optional dependencies:

```bash
pip install "scikit-learn>=1.0"

```

Or disable vector chart visualization by removing `"response_vector_chart"` from the `display_type` list in the prompt configuration.

## Key Source Files for Debugging

| File | Purpose | GitHub Link |
|------|---------|-------------|
| `chat.py` | Core auto-execute logic: `ChatWithDbAutoExecute`, `generate_input_values`, `do_action` | [View Source](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py) |
| `prompt.py` | Prompt template construction and `RESPONSE_FORMAT_SIMPLE` JSON schema | [View Source](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/prompt.py) |
| `out_parser.py` | `DbChatOutputParser`, `parse_prompt_response`, `parse_view_response` | [View Source](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/out_parser.py) |
| `api_v1.py` | Model type filtering for `text2sql_proxyllm` | [View Source](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/openapi/api_v1/api_v1.py) |
| `connector_manager.py` | Datasource connector retrieval | [View Source](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-serve/src/dbgpt_serve/datasource/manages/connector_manager.py) |
| `db_summary_client.py` | Table metadata summarization | [View Source](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-serve/src/dbgpt_serve/datasource/service/db_summary_client.py) |
| `config.py` | Configuration constants for schema retrieval | [View Source](https://github.com/eosphoros-ai/DB-GPT/blob/main/packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/config.py) |

## Quick Debugging Code Snippets

Run an isolated auto-execute chat to inspect the pipeline:

```python
import json
from dbgpt_app.scene.chat_db.auto_execute.chat import ChatWithDbAutoExecute
from dbgpt_app.scene.base_chat import ChatParam
from dbgpt import SystemApp
from dbgpt_app.scene.chat_db.auto_execute.out_parser import DbChatOutputParser

sys_app = SystemApp()
chat_param = ChatParam(
    select_param="sqlite_demo",
    current_user_input="How many students scored above 90?",
    model_name="text2sql_proxyllm",
    user_name="developer",
    sys_code="dbgpt",
)
chat = ChatWithDbAutoExecute(chat_param, sys_app)

# Inspect input values

input_vals = await chat.generate_input_values()
print("=== INPUT VALUES ===")
print(json.dumps(input_vals, indent=2, ensure_ascii=False))

# Inspect raw LLM response

response_text = await chat.llm_client.call(chat.prompt.build(input_vals))
print("\n=== RAW LLM RESPONSE ===")
print(response_text)

# Inspect parsed action

parser = DbChatOutputParser()
action = parser.parse_prompt_response(response_text)
print("\n=== PARSED ACTION ===")
print(action.to_dict())

```

Verify the Text2SQL model registration:

```bash
curl -X GET http://localhost:7860/api/v1/model/types | grep text2sql_proxyllm

```

Manually test generated SQL:

```bash

# SQLite example

sqlite3 /path/to/database.sqlite "SELECT * FROM students WHERE score > 90 LIMIT 10"

```

## Summary

- **Enable debug logging** with `DEBUG=True` and `LOG_LEVEL=DEBUG` to expose schema retrieval, prompt rendering, and parser errors in [`chat.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/chat.py) and [`out_parser.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/out_parser.py).
- **Trace the input values** produced by `generate_input_values` to verify that `table_info`, `dialect`, and `db_name` are correctly populated before reaching the LLM.
- **Dump the rendered prompt** from `AppScenePromptTemplateAdapter` to ensure the JSON response schema and user question are properly formatted.
- **Confirm model availability** by checking that `text2sql_proxyllm` appears in the `/api/v1/model/types` endpoint and is registered with the `WorkerManager`.
- **Capture raw LLM output** before `DbChatOutputParser` processes it to detect markdown fences, truncated JSON, or pure SQL responses.
- **Validate parser output** by running `parse_prompt_response` manually to ensure [`action.sql`](https://github.com/eosphoros-ai/DB-GPT/blob/main/action.sql) contains valid, executable SQL.
- **Execute SQL manually** against the target database to isolate datasource configuration issues from generation errors.

## Frequently Asked Questions

### How do I know if the schema retrieval is failing in auto_execute mode?

Check the debug logs for messages containing `Retrieved table info error`. If `DBSummaryClient` fails to generate summaries, the system falls back to `Connector.table_simple_info` in [`connector_manager.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/connector_manager.py). Verify that your datasource connector is properly registered and that the database credentials allow metadata queries.

### Why does the LLM return malformed JSON instead of the expected SQL response?

The `DbChatOutputParser` in [`out_parser.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/out_parser.py) expects either pure SQL or a JSON object matching `RESPONSE_FORMAT_SIMPLE`. If the model returns explanatory text wrapped around JSON or uses markdown code fences inconsistently, parsing fails. Increase prompt specificity in [`prompt.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/prompt.py) or adjust `PROMPT_TEMPERATURE` to 0.1 for more deterministic output.

### How can I test the generated SQL without running the full DB-GPT pipeline?

Extract the [`action.sql`](https://github.com/eosphoros-ai/DB-GPT/blob/main/action.sql) string from the `DbChatOutputParser` output and execute it directly using your database CLI or a Python script with the same connection string used by `ConnectorManager.get_connector`. This isolates whether the issue is SQL generation or datasource connectivity/configuration.

### What should I do if the UI shows an error but the SQL executes correctly in my manual test?

If manual execution succeeds but the UI fails, inspect `parse_view_response` in [`out_parser.py`](https://github.com/eosphoros-ai/DB-GPT/blob/main/out_parser.py). The error likely stems from missing `display_type` metadata or a missing `scikit-learn` dependency required for `parse_vector_data_with_pca`. Install optional dependencies with `pip install "scikit-learn>=1.0"` or disable vector chart visualization in the prompt configuration.