MariaDB Connection ID

Mon, 2021-11-29 15:09 — Shinguz

The MariaDB Connection ID exists since long ago. So why bother about the Connection ID? Because it is interesting and you can do some interesting things with the Connection ID like tracking statements in your connections and find where they come from your application code.

The MariaDB Connection ID is a strictly monotonic increasing number starting with 1 at server restart:

shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID
CONNECTION_ID(): 2372
shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID
CONNECTION_ID(): 2373
shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID
CONNECTION_ID(): 2374

The MariaDB documentation states [1]:

Returns the connection ID (thread ID) for the connection. Every thread (including events) has an ID that is unique among the set of currently connected clients.

The MariaDB documentation is only partly correct because the thread ID is something different and the term is used ambiguous. See further down.

The maximum number of connections created can be shown with:

SQL> SHOW GLOBAL STATUS LIKE 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 2322  |
+---------------+-------+

But where else can I find the Connection ID?

Processlist

The Connection ID is shown in many different places inside your MariaDB database server. First of all you will find it in the process list:

SQL> SELECT id AS connection_id, user, host, IFNULL(CONCAT(SUBSTR(info, 1, 32), '...'), '') AS query
  FROM information_schema.processlist;
+---------------+-------------+-----------+-------------------------------------+
| connection_id | user        | host      | query                               |
+---------------+-------------+-----------+-------------------------------------+
|          2383 | root        | localhost | SELECT id AS processlist_id, use... |
|             6 | system user |           |                                     |
+---------------+-------------+-----------+-------------------------------------+

Unfortunately it is named there just id so you have to know what it means.

You can easily show what all connections are doing while filtering out your own connection:

SQL> SELECT *
  FROM information_schema.processlist
 WHERE id != CONNECTION_ID();
+------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+
| ID   | USER        | HOST            | DB   | COMMAND   | TIME   | STATE | INFO | TIME_MS       | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID  |
+------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+
| 2398 | app         | localhost:35512 | NULL | Sleep     |      4 |       | NULL |      4771.722 |     0 |         0 |    0.000 |       81784 |           81784 |             0 |    14030 | NULL        | 6946 |
| 2392 | root        | localhost       | NULL | Sleep     |     36 |       | NULL |     36510.867 |     0 |         0 |    0.000 |       81784 |           81784 |             0 |    14021 | NULL        | 3850 |
+------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+

Note: The information_schema.processlist view is a superset of the command SHOW FULL PROCESSLIST and can be used with SQL means. So it has some advantages to retrieve the data from there...

InnoDB Monitor

Also in the InnoDB Monitor you can see the MariaDB Connection ID in at least 3 different places. The InnoDB Monitor is called as follows:

SQL> SHOW ENGINE INNODB STATUS\G

In the following 3 sections you will find the Connection ID:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-06-26 11:05:21 13c0
*** (1) TRANSACTION:
TRANSACTION 17064867, ACTIVE 17 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 2
MySQL thread id 7778, OS thread handle 0xb9c, query id 23386973 10.0.0.6 WPFieldUser updating
Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406250843353122'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170648
67 lock_mode X waiting
Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;;
 1: len 4; hex 8000a4d8; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 17063837, ACTIVE 173 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3112, 22 row lock(s), undo log entries 10
MySQL thread id 7765, OS thread handle 0x13c0, query id 23387033 10.0.0.6 WPFieldUser updating
Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406251613333122'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170638
37 lock_mode X
Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;;
 1: len 4; hex 8000a4d8; asc     ;;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2021-08-19 15:09:19 7fbb6c328700 Transaction:
TRANSACTION 543875059, ACTIVE 0 sec inserting
mysql tables in use 1, locked 14 lock struct(s), heap size 1184,
2 row lock(s), undo log entries 1
MySQL thread id 124441421, OS thread handle 0x7fbb6c328700,
query id 7822461590 192.168.1.42 fronmdual update
INSERT INTO contact (user_id,kontact_id) VALUES (62486, 63130)
Foreign key constraint fails for table `test`.`contact`:
, CONSTRAINT `FK_contact_user_2` FOREIGN KEY (`contact_id`)
REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `contact_id` tuple:
DATA TUPLE: 2 fields;
...
But in parent table `test`.`user`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 41; compact format; info bits 0
...

------------
TRANSACTIONS
------------
Trx id counter 2499
Purge done for trx's n:o < 2486 undo n:o < 0 state: running
History list length 12
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7f70d6b93330), ACTIVE 3 sec
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MariaDB thread id 2398, OS thread handle 140122589112064, query id 14075 localhost 127.0.0.1 app Sending data
select * from test.test
Trx read view will not see trx with id >= 2499, sees < 2499

Unfortunately the Connection ID here is called thread id in all 3 different places. But we know at least which connection is causing Deadlock errors, Foreign Key errors or long running transactions.

`PERFORMANCE_SCHEMA`

`threads` view

In the PERFORMANCE_SCHEMA.threads view you can also find the MariaDB Connection ID, called processlist_id. But here starts confusion again. MySQL introduced a new column thread_id which is mostly used in the PERFORMANCE_SCHEMA and contains the ID of a thread (not a connection):

SQL> SELECT thread_id, processlist_id
  FROM performance_schema.threads
 WHERE processlist_id = CONNECTION_ID();
+-----------+----------------+
| thread_id | processlist_id |
+-----------+----------------+
|      2369 |           2321 |
+-----------+----------------+

The processlist_id can be found also in the views: session_account_connect_attrs and session_connect_attrs in the PERFORMANCE_SCHEMA.

Via the thread_id you can match now your connection to various other views in the PERFORMANCE_SCHEMA: events_stages_*, events_statements_*, events_transactions_*, events_waits_*, memory_summary_by_thread_by_event_name, socket_instances, status_by_thread and user_variables_by_thread

There are related thread_ids in: threads.PARENT_THREAD_ID, metadata_locks.OWNER_THREAD_ID, mutex_instances.LOCKED_BY_THREAD_ID, prepared_statements_instances.OWNER_THREAD_ID, table_handles.OWNER_THREAD_ID and rwlock_instances.WRITE_LOCKED_BY_THREAD_ID.

`sys` Schema

Also in the sys Schema you find the thread_id (but not the connection_id) in the following views: io_by_thread_by_latency, latest_file_io, memory_by_thread_by_current_bytes, processlist, schema_table_lock_waits and session_ssl_status.

MariaDB Log files

MariaDB Error Log file

Also in your MariaDB Error Log file you find the Connection ID. The Connection ID is the 2^nd position in the Log. And sometimes you see the Connection ID also in the error message itself:

2021-11-25 16:22:34 1796 [Warning] Hostname 'chef' does not resolve to '192.168.56.1'.
2021-11-25 16:22:34 1796 [Note] Hostname 'chef' has the following IP addresses:
2021-11-25 16:22:34 1796 [Note]  - 192.168.1.142
2021-11-25 16:22:34 1796 [Warning] Aborted connection 1796 to db: 'unconnected' user: 'unauthenticated' host: '192.168.56.1' (This connection closed normally without authentication)
2021-11-26 16:09:14 2397 [Warning] Access denied for user 'app'@'localhost' (using password: YES)

MariaDB General Query Log

The most important use of the Connection ID I see in the MariaDB General Query Log. Here you can find ALL the queries sent through connections to the database. You can easily search for a specific Connection ID and you will see exactly what a connection does or did:

211108 22:18:02   4568 Connect  fpmmm_agent@localhost on  using TCP/IP
                  4568 Query    SET NAMES utf8
                  4568 Query    SHOW GRANTS
                  4568 Query    SELECT "focmm"
                  4568 Query    SHOW GLOBAL VARIABLES
                  4568 Query    SHOW /*!50000 GLOBAL */ STATUS
                  4568 Quit

MariaDB Slow Query Log

You can also find the Connection ID in the MariaDB Slow Query Log. But here again it is called Thread_id:

# Time: 210729  9:22:57
# User@Host: root[root] @ localhost []
# Thread_id: 34  Schema: test  QC_hit: No
# Query_time: 0.003995  Lock_time: 0.000114  Rows_sent: 2048  Rows_examined: 2048
# Rows_affected: 0  Bytes_sent: 79445
SET timestamp=1627543377;
select * from test;

MariaDB SQL Error Log

Unfortunately the Connection ID is missing in the MariaDB SQL Error Log output:

shell> tail sql_errors.log
2021-11-26 16:58:36 app[app] @ localhost [127.0.0.1] ERROR 1046: No database selected : select * from test limt 10

We opened a feature request for this: SQL Error Log plug-in lacks Connection ID (MDEV-27129).

MariaDB Binary Log

Also in the MariaDB Binary Log the Connection ID is missing.

MariaDB Audit Plugin

But in the MariaDB Audit Plugin we will find again the Connection ID in the 5^th column:

20211126 17:19:01,chef,app,localhost,2477,18407,QUERY,,'SELECT DATABASE()',0
20211126 17:19:01,chef,app,localhost,2477,18409,QUERY,test,'show databases',0
20211126 17:19:01,chef,app,localhost,2477,18410,QUERY,test,'show tables',0
20211126 17:19:02,chef,app,localhost,2477,18423,QUERY,test,'select * from test limt 10',1064
20211126 17:19:05,chef,app,localhost,2477,18424,READ,test,test,
20211126 17:19:05,chef,app,localhost,2477,18424,QUERY,test,'select * from test limit 10',0
20211126 17:19:38,chef,app,localhost,2477,18426,QUERY,test,'select connection_id()',0
20211126 17:19:52,chef,app,localhost,2477,0,DISCONNECT,test,,0

`INFORMATION_SCHEMA`

Also in some INFORMATION_SCHEMA views we will find the Connection ID: In THREAD_POOL_QUEUES.CONNECTION_ID, METADATA_LOCK_INFO.THREAD_ID and INNODB_TRX.trx_mysql_thread_id.

Other related information

Taxonomy upgrade extras:

connection

max_used_connections

general query log

Shinguz's blog
Log in or register to post comments

Languages

Login

You are here

MariaDB Connection ID

Processlist

InnoDB Monitor

`PERFORMANCE_SCHEMA`

`threads` view

`sys` Schema

MariaDB Log files

MariaDB Error Log file

MariaDB General Query Log

MariaDB Slow Query Log

MariaDB SQL Error Log

MariaDB Binary Log

MariaDB Audit Plugin

`INFORMATION_SCHEMA`

Other related topics to MariaDB Connection ID

Thread Cache

Connection Pooling

Pseudo thread ID

Other related information

Navigation

Trademarks

Languages

Login

Search form

You are here

MariaDB Connection ID

Processlist

InnoDB Monitor

PERFORMANCE_SCHEMA

threads view

sys Schema

MariaDB Log files

MariaDB Error Log file

MariaDB General Query Log

MariaDB Slow Query Log

MariaDB SQL Error Log

MariaDB Binary Log

MariaDB Audit Plugin

INFORMATION_SCHEMA

Other related topics to MariaDB Connection ID

Thread Cache

Connection Pooling

Pseudo thread ID

Other related information

Navigation

Trademarks

`PERFORMANCE_SCHEMA`

`threads` view

`sys` Schema

`INFORMATION_SCHEMA`