CODEWITHSUNDEEP
mysql
Tihomir Mitkov
Tihomir Mitkov

Reputation: 846

Retrieving connection status

Is there a way the status command be called on another connection. Something like:

status 231073

where the number is the ID from SHOW PROCESSLIST. In particular I'm interested in connection/client character sets.

EDIT: By status I mean the command you call from the MySQL shell (not show status)

Upvotes: 0

Views: 81

Answers (1)

Drew
Drew

Reputation: 24959

You could use the following to capture info of your connections, say, at the start of your app or at anytime. For instance max_allowed_packet could change during the client run lifecycle. So, in short, have in as part of your app startup.

Then you have collected info on your client connections, such as character set settings.

Schema:

DROP TABLE IF EXISTS connectionSnapshot;
CREATE TABLE connectionSnapshot
(   -- based off of (more or less) mysql 5.6 show create table information_schema.PROCESSLIST;
    -- 3 columns at least
    -- TODO: compare to same structure on mysql 5.7
    id INT AUTO_INCREMENT PRIMARY KEY,
    connId BIGINT UNSIGNED NOT NULL, 
    user VARCHAR(16) NOT NULL,
    host VARCHAR(64) NOT NULL,
    login VARCHAR(100) NOT NULL,
    -- the followed just dreamt up:
    character_set_client VARCHAR(100) NOT NULL,
    character_set_connection VARCHAR(100) NOT NULL,
    character_set_results VARCHAR(100) NOT NULL,
    collation_connection VARCHAR(100) NOT NULL,
    lc_time_names VARCHAR(100) NOT NULL,
    max_allowed_packet BIGINT NOT NULL,
    time_zone VARCHAR(100) NOT NULL,
    theWhen DATETIME NOT NULL
);

Stored Proc:

DROP PROCEDURE IF EXISTS uspConnectionSnapshotMe;
DELIMITER $$
CREATE PROCEDURE uspConnectionSnapshotMe()
BEGIN
    DECLARE lconnId INT;
    DECLARE luser VARCHAR(16);
    DECLARE lhost VARCHAR(64);
    SELECT connection_id() INTO lconnID;
    SELECT USER,HOST into luser,lhost FROM information_schema.PROCESSLIST WHERE ID=lconnId;

    INSERT connectionSnapshot (connId,user,host,login,character_set_client,character_set_connection,character_set_results,
    collation_connection,lc_time_names,max_allowed_packet,time_zone,theWhen) VALUES 
    (lconnId,luser,lhost,current_user(),@@session.character_set_client,@@session.character_set_connection,
    @@session.character_set_results,@@session.collation_connection,
    @@session.lc_time_names,@@session.max_allowed_packet,@@session.time_zone,now());
END$$
DELIMITER ;
-- ****************************************************************************************

Test:

call uspConnectionSnapshotMe();

Results:

select * from connectionSnapshot;
+----+--------+------+-----------------+----------------+----------------------+--------------------------+-----------------------+----------------------+---------------+--------------------+-----------+---------------------+
| id | connId | user | host            | login          | character_set_client | character_set_connection | character_set_results | collation_connection | lc_time_names | max_allowed_packet | time_zone | theWhen             |
+----+--------+------+-----------------+----------------+----------------------+--------------------------+-----------------------+----------------------+---------------+--------------------+-----------+---------------------+
|  1 |   3825 | root | localhost:4660  | root@localhost | utf8                 | utf8                     | utf8                  | utf8_general_ci      | en_US         |            4194304 | SYSTEM    | 2016-09-08 02:40:18 |
|  2 |  37007 | root | localhost:52071 | root@localhost | utf8                 | utf8                     | cp850                 | utf8_general_ci      | en_US         |            4194304 | SYSTEM    | 2016-09-08 02:44:17 |
+----+--------+------+-----------------+----------------+----------------------+--------------------------+-----------------------+----------------------+---------------+--------------------+-----------+---------------------+

Upvotes: 1

Related Questions