I am using mysql for quite some time now.
I have noticed that looking at client connections using mysql workbench so an interesting column called "Program". I can see that the program column indicate "MySQL Workbench":
This column can be very useful and help to filter different mysql database connections.
It took me a while to understand how to set up this column data.
So first thing I needed to do is understand where is this column name is actually coming from. I inspected the "SHOW FULL PROCESSLIST" command by mysql - but I did not get any answer there.
I then looked at the query the mysql workbench itself running to get this data "Program" column:
Using the "show details" button in the "client connections" view, show me this:
Copying the query, it looks like that:
SELECT t.PROCESSLIST_ID,IF (NAME = 'thread/sql/event_scheduler','event_scheduler',t.PROCESSLIST_USER) PROCESSLIST_USER,
t.PROCESSLIST_HOST,
t.PROCESSLIST_DB,
t.PROCESSLIST_COMMAND,
t.PROCESSLIST_TIME,
t.PROCESSLIST_STATE
,t.THREAD_ID,
t.TYPE,
t.NAME,
t.PARENT_THREAD_ID,
t.INSTRUMENTED,
t.PROCESSLIST_INFO,
a.ATTR_VALUE
FROM performance_schema.threads t LEFT OUTER JOIN performance_schema.session_connect_attrs a ON t.processlist_id = a.processlist_id AND (a.attr_name IS NULL OR a.attr_name = 'program_name') WHERE t.TYPE <> 'BACKGROUND'
It seems that mysql saves some extra data on the connection within the table performance_schema.session_connect_attrs and specifically the attr_name column.
The performance_schema.session_connect_attrs table, which allows you to store custom key-value pairs associated with a client connection. This is invaluable for identifying and monitoring connections from different applications or parts of your system.
MySQL provides a mechanism for client applications to send a set of key-value pairs (connection attributes) to the server during the connection handshake. These attributes are then exposed in the performance_schema.session_connect_attrs table (and session_account_connect_attrs for the current user's connections).
Commonly used attributes include _client_name, _client_version, _os, _pid, _platform, and program_name. You can also define your own custom attributes. Attribute names starting with an underscore (_) are reserved for internal MySQL use.
Here is how you can us Go (Golang mysql drive) to set this key-value pairs and most important the program_name field which later on views as "Program" inside MySQL Workbench.
user := "your_user"
password := "your_password"
host := "localhost"
port := "3306"
database := "your_database"
// Define your custom connection attributes
connectAttrs := "program_name:MyGoApp,environment:development,module:data_access"
// Construct the DSN string
// The `connectionAttributes` parameter is added to the query string part of the DSN.
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?connectionAttributes=%s",
user, password, host, port, database, connectAttrs)
