Fixed: Warning 1287: 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release

I ran SHOW PROCESSLIST in MySQL 9.7 to see who was connected, and the result ended with 1 warning. SHOW WARNINGS named it: Warning 1287, the old implementation behind SHOW PROCESSLIST is deprecated. Querying the Performance Schema's processlist table directly gives the same list, without the warning.

What I Was Doing

I was checking that a .NET Web API had connected to MySQL 9.7.4, running in Docker, as described in How to Connect a .NET C# Web API to a MySQL Database — Part 1. I opened the mysql client inside the container as root, as in How to Connect to MySQL in a Docker Container with the mysql Client, and asked for the list of connections:

mysql>

SHOW PROCESSLIST;

What I Expected

The list of connections, and nothing else.

What I Got

Output

+----+-----------------+------------------+------+---------+------+------------------------+------------------+
| Id | User            | Host             | db   | Command | Time | State                  | Info             |
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost        | NULL | Daemon  |  461 | Waiting on empty queue | NULL             |
| 56 | inventory_app   | 172.19.0.1:59464 | NULL | Sleep   |    2 |                        | NULL             |
| 57 | root            | localhost        | NULL | Query   |    0 | init                   | SHOW PROCESSLIST |
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
3 rows in set, 1 warning (0.001 sec)

The list was there, including the API's connection as inventory_app. But the last line said 3 rows in set, 1 warning.

What I Checked

The mysql client only counts warnings. SHOW WARNINGS, right after the statement, shows them:

mysql>

SHOW WARNINGS;

Output

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

Code 1287 is MySQL's code for a deprecated feature. The message even names the replacement.

Why It Happens

The SHOW PROCESSLIST page of the MySQL 9.7 manual opens with the reason: "The INFORMATION SCHEMA implementation of SHOW PROCESSLIST is deprecated and subject to removal in a future MySQL release. It is recommended to use the Performance Schema implementation of SHOW PROCESSLIST instead."

By default, SHOW PROCESSLIST still uses the old implementation, so every call prints the warning. The manual explains that the old one "iterates across active threads from within the thread manager while holding a global mutex", which slows down busy servers, while the Performance Schema version "does not require a mutex".

The Fix

The processlist table page says the table "can be queried directly". Ask it instead:

mysql>

SELECT * FROM performance_schema.processlist;

Output

+----+-----------------+------------------+------+---------+------+------------------------+----------------------------------------------+------------------+
| ID | USER            | HOST             | DB   | COMMAND | TIME | STATE                  | INFO                                         | EXECUTION_ENGINE |
+----+-----------------+------------------+------+---------+------+------------------------+----------------------------------------------+------------------+
|  5 | event_scheduler | localhost        | NULL | Daemon  |  461 | Waiting on empty queue | NULL                                         | PRIMARY          |
|  7 | system user     |                  | NULL | Daemon  |  461 | Suspending             | NULL                                         | PRIMARY          |
| 56 | inventory_app   | 172.19.0.1:59464 | NULL | Sleep   |    2 |                        | NULL                                         | PRIMARY          |
| 57 | root            | localhost        | NULL | Query   |    0 | executing              | SELECT * FROM performance_schema.processlist | PRIMARY          |
+----+-----------------+------------------+------+---------+------+------------------------+----------------------------------------------+------------------+
4 rows in set (0.006 sec)

After the Fix

4 rows in set, with no warning. The same connections are there, and two things are new:

• A system user thread (ID 7), one of MySQL's own background threads, which the old SHOW PROCESSLIST didn't list.
• An EXECUTION_ENGINE column, which only matters on Oracle's MySQL HeatWave service. The manual says that for MySQL Community Edition Server "the value is always PRIMARY".

Because it's a table, you can filter and choose columns with ordinary SQL, which SHOW PROCESSLIST can't do. This shows only the API's connections:

mysql>

SELECT ID, USER, HOST, DB, COMMAND, TIME
FROM performance_schema.processlist
WHERE USER = 'inventory_app';

What about the setting in the manual?

The manual also describes a server setting, performance_schema_show_processlist, that switches SHOW PROCESSLIST to the new implementation. In MySQL 9.7, its own entry is marked Deprecated: Yes, so I didn't change it. Querying the table needs no setting at all.

Common Mistakes

Mistake	What happens	Do this instead
Ignoring 1 warning in the output	You miss that a statement may stop working in a future release	Run SHOW WARNINGS
Switching on performance_schema_show_processlist	You depend on another deprecated feature	Query performance_schema.processlist
Querying as an account without the PROCESS privilege	You only see your own account's connections	Use an administrator account to see everyone's

Troubleshooting

Symptom	Likely cause	Fix
Only your own connections appear	The account has no PROCESS privilege; the manual says such users "have access to information about their own threads"	Connect as root or another admin

FAQ

Is SHOW PROCESSLIST going away?

The manual says the INFORMATION SCHEMA implementation is "subject to removal in a future MySQL release". The statement itself still works in 9.7; the warning is the advance notice.

What about mysqladmin processlist?

The manual says it uses the SHOW PROCESSLIST statement, so it relies on the same deprecated implementation. When I ran it, it printed the list without mentioning the warning, so the warning is easy to miss there.

Does the query change anything?

No. It only reads a built-in table.

Conclusion

Warning 1287 after SHOW PROCESSLIST is MySQL 9.7's notice that the statement's old implementation is deprecated. SELECT * FROM performance_schema.processlist lists the same connections, plus MySQL's own background thread, without the warning, and you can filter it like any other table.

Additional Resources

• SHOW PROCESSLIST Statement (MySQL 9.7) — the deprecation note and the columns.
• The processlist Table (MySQL 9.7) — the replacement, and the privileges it needs.

Related

• How to Connect a .NET C# Web API to a MySQL Database — Part 1
• How to Connect to MySQL in a Docker Container with the mysql Client
• How to Check if a MySQL Connection Uses TLS