Setting program_name in mysql session_connect_attrs allows you to see the name of the program connected

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)