Diagnosing MySQL Temporary Table Full Errors with ChatDBA (AI Edition) – Issue 51

What is "One Question One Experiment"?

"One Question One Experiment" is a flagship column of the iKOS open‑source community that pairs each database question with a hands‑on experiment to explain fundamental concepts clearly. Since 2020 it has published 50 issues with over 500,000 reads.

AI Edition Interview

Why was the column paused?

Huang Yan : I was attracted by more exciting things, especially the breakthroughs in large models, which consumed a lot of my time. We integrated AI by building ChatDBA, an intelligent assistant for DBAs, and from issue 51 onward the column is authored by ChatDBA with human experts reviewing the quality.

What new experience does this bring to readers?

Huang Yan : ChatDBA uses a complex architecture beyond simple LLM+RAG, aiming to move away from a pure "search engine" role. It can guide diagnosis, use knowledge more precisely, and adopt a more human‑like reasoning style. We hope AI‑written columns match human‑written ones in correctness and depth, though ChatDBA is still early.

How does the AI version differ from previous ones?

Huang Yan : As ChatDBA evolves, the column will shift from simple fault diagnosis to deep principle analysis, from database‑only to OS and network knowledge, from fact inference to technical speculation and verification, from solving problems to building tools, and from short‑chain to long‑chain reasoning, among other enhancements.

Problem

MySQL logs show many errors – what could be the cause?

Error messages:

2024-04-26 23:27:06 [ERROR] /data/3306/base/5.7.26/bin/mysqld: The table '/data/3306/tmp/#sql_3d157_11' is full
2024-04-27 00:11:04 [ERROR] /data/3306/base/5.7.26/bin/mysqld: The table '/data/3306/tmp/#sql_3d157_1' is full

Experiment

1. Submit the problem to ChatDBA

With ChatDBA, we first let the AI analyze the issue.

ChatDBA suggests possible causes:

Insufficient temporary tablespace configuration.

SQL queries creating many temporary tables.

Disk space shortage or resource limits.

2. ChatDBA assists in troubleshooting

We follow ChatDBA's recommended steps to inspect MySQL parameters.

After running the two commands provided by ChatDBA, we discover that the InnoDB temporary tablespace (ibtmp1) has already consumed 50 GB.

ChatDBA indicates that while the parameter size is likely the root cause, long‑running transactions or queries may also contribute.

3. ChatDBA proposes a solution

ChatDBA recommends expanding the temporary tablespace by adjusting the innodb_temp_data_file_path parameter.

4. Experiment Summary

For the error 2024-04-26 23:27:06 [ERROR] /data/3306/base/5.7.26/bin/mysqld: The table '/data/3306/tmp/#sql_3d157_11' is full , three investigation directions emerged: too small configuration, insufficient disk space, and SQL statements generating many temporary tables.

ChatDBA's approach is to first examine configuration, quickly locate the issue, apply a temporary parameter tweak, and finally pursue deeper root‑cause analysis such as optimizing long‑running SQL.

In this case, heavy long‑running transactions and queries caused high temporary tablespace usage, so while adjusting the parameter provides immediate relief, the ultimate fix requires targeted SQL optimization.

Ask ChatGPT‑4o

We also submitted the same problem to ChatGPT‑4o to compare results.

General large language models tend to give broad answers lacking concrete operational steps, making it harder for users to determine the next actionable move.

What is ChatDBA?

ChatDBA is an intelligent assistant designed for DBAs, capable of fault diagnosis, guidance, and knowledge‑driven reasoning, integrating both common and novel techniques beyond simple retrieval‑augmented generation.