数据获取成功但show processlist为空，Go的database/sql查询正常却无进程记录

I’ve run into this exact scenario a few times—your data fetch operations succeed, Go’s database/sql runs queries perfectly, but SHOW PROCESSLIST (especially when targeting a specific host) comes back empty. Let’s walk through the most likely causes and how to fix them.

Common Reasons & Solutions #

1. You’re Missing the PROCESS Privilege #

Most databases (MySQL/MariaDB being the most common here) lock down the PROCESS privilege by default. Without it, SHOW PROCESSLIST will only show your own current connection—and if there’s no active query running at the exact moment you run the command, you’ll get an empty list.

• Check your permissions: Run SHOW GRANTS FOR CURRENT_USER; to see if PROCESS is listed in your privileges.
• Get the privilege added: Ask your DBA to run GRANT PROCESS ON *.* TO 'your_username'@'your_connection_host'; (swap in your actual user and host), then reconnect to the database. That should let you see all active system processes.

2. Timing Is Against You #

SHOW PROCESSLIST is a real-time snapshot—it only shows processes that are actively running (or idle in some cases) at the split second you execute it. If your Go queries are fast, they might finish before you even run the process list command.

• Test with a slow query: Open two sessions. In one, run a long-running query like SELECT SLEEP(10); (MySQL) or SELECT pg_sleep(10); (PostgreSQL). In the other, run SHOW PROCESSLIST (or SELECT * FROM pg_stat_activity; for Postgres) while the sleep query is running. You should see that process pop up in the results.
• Check connection pooling: Go’s database/sql uses connection pools. Idle pooled connections might not show up in the process list unless they’re actively handling a query. You can tweak pool settings to keep connections alive longer if you need to see them, but that’s usually not necessary for debugging.

3. Host Filtering Doesn’t Match the Actual Connection #

If you’re filtering the process list by a specific host (e.g., SHOW PROCESSLIST WHERE Host = '192.168.1.10';), the host string might not match what the database sees. Databases often include the port number in the Host field (like 192.168.1.10:56789) or use localhost instead of an IP if you’re connecting via a socket.

• Find your actual connection host: Run SELECT CURRENT_USER(); in your Go app. This will show you the exact user/host pair the database recognizes (e.g., myuser@192.168.1.10).
• Adjust your filter: Use that exact host string (including port if present) when querying the process list. For example, SHOW PROCESSLIST WHERE Host LIKE '192.168.1.10:%'; to catch any port from that IP.

4. You’re Using the Wrong Command for Your Database #

Different databases have different ways to list active processes:

• MySQL/MariaDB: SHOW FULL PROCESSLIST shows more details than the standard SHOW PROCESSLIST, including idle connections that might be hidden otherwise.
• PostgreSQL: Forget SHOW PROCESSLIST—use SELECT * FROM pg_stat_activity; instead. Make sure you’re not filtering out idle connections with a WHERE state != 'idle' clause unless you specifically want to.
• SQL Server: Use sp_who2 or SELECT * FROM sys.dm_exec_sessions; to get process details.

Quick Validation Step #

1. In your Go app, modify a query to include a delay: for MySQL, SELECT * FROM your_table, SLEEP(5);
2. Run that query, and immediately execute the full process list command (e.g., SHOW FULL PROCESSLIST) from the same user/host your Go app uses.
3. If you see the delayed query in the results, then the issue was either timing or privileges. If not, double-check the host matching logic.

内容的提问来源于stack exchange，提问作者Ajay