patternsqlMinor

Is it possible to select internal InnoDB columns?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

columnsinnodbpossibleinternalselect

Problem

I have read that InnoDB uses a few fields internally for all rows, such as the hidden columns DB_TRX_ID and DB_ROW_ID. Is there any easy way to see these values, such as within a select statement?

Solution

Unfortunately, there are no metadata available for that info. The two fields you are asking for

DB_TRX_ID

DB_ROW_ID

are only visible from a textual viewpoint in SHOW ENGINE INNODB STATUS\G

You can learn more about it from the MySQL 5.1 Documentation

MySQL 5.5+ offers some metadata for live transactions

mysql> use information_schema
Database changed
mysql> select version(),database();
+-----------+--------------------+
| version() | database()         |
+-----------+--------------------+
| 5.5.9-log | information_schema |
+-----------+--------------------+
1 row in set (0.00 sec)

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
+----------------------------------------+
7 rows in set (0.00 sec)

mysql>

MySQL 5.6 offers even more:

mysql> use information_schema
Database changed
mysql> select version(),database();
+------------+--------------------+
| version()  | database()         |
+------------+--------------------+
| 5.6.10-log | information_schema |
+------------+--------------------+
1 row in set (0.00 sec)

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_LOCKS                           |
| INNODB_TRX                             |
| INNODB_SYS_DATAFILES                   |
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  |
| INNODB_CMP                             |
| INNODB_FT_BEING_DELETED                |
| INNODB_CMP_RESET                       |
| INNODB_CMP_PER_INDEX                   |
| INNODB_CMPMEM_RESET                    |
| INNODB_FT_DELETED                      |
| INNODB_BUFFER_PAGE_LRU                 |
| INNODB_FT_INSERTED                     |
| INNODB_CMPMEM                          |
| INNODB_SYS_INDEXES                     |
| INNODB_SYS_TABLES                      |
| INNODB_SYS_FIELDS                      |
| INNODB_CMP_PER_INDEX_RESET             |
| INNODB_BUFFER_PAGE                     |
| INNODB_FT_DEFAULT_STOPWORD             |
| INNODB_FT_INDEX_TABLE                  |
| INNODB_FT_INDEX_CACHE                  |
| INNODB_SYS_TABLESPACES                 |
| INNODB_METRICS                         |
| INNODB_SYS_FOREIGN_COLS                |
| INNODB_FT_CONFIG                       |
| INNODB_BUFFER_POOL_STATS               |
| INNODB_SYS_COLUMNS                     |
| INNODB_SYS_FOREIGN                     |
+----------------------------------------+
29 rows in set (0.00 sec)

mysql>

Keep in mind that these are living, breathing entities. Do no bother run a COUNT(*). By the time you do, queries have disappeared and other queries have materialized.

Here is a sample:

```
mysql> select * from information_schema.INNODB_TRX\G
1. row
trx_id: 24E6E0FC5
trx_state: RUNNING
trx_started: 2013-04-22 16:34:54
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 20275587
trx_query: (SELECT
GROUP_CONCAT( CONCAT_WS('','{\"type\":\"',report_stats.type,'\",\"date\":',report_stats.date,',\"leadID\":',report_stats.leadID,',\"vehID\":',report_stats.vehID,',\"contactID\":',report_stats.contactID,'}') SEPARATOR '::') customers,
COUNT(*) total, CONCAT_WS('',sales.id,'^^',sales.first,' ',sales.last) sales
FROM
D6195.report_stats
LEFT JOIN D6195.report_stats self ON report_stats.leadID=self.leadID AND report_stats.vehID=self.vehID
JOIN D6195.leads ON report_stats.leadID=leads.id
LEFT JOIN icar.sales ON sales.id=leads.salesID
WHERE
report_stats.date BETWEEN 1364788800 AND 1367294400 AND
report_stats.type IN ('Internet Lead') AND FROM_UNIXTIME(report_stats.date,'%k') BETWEEN 9 AND 18 AND self.type='First Response' GROUP BY CONCAT_WS('',sales.id,'^^',sales.first,' ',sales.last) ASC WITH ROLLUP)
trx_operation_state: NULL
trx_tables_in_use: 4
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 376
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1

Code Snippets

mysql> use information_schema
Database changed
mysql> select version(),database();
+-----------+--------------------+
| version() | database()         |
+-----------+--------------------+
| 5.5.9-log | information_schema |
+-----------+--------------------+
1 row in set (0.00 sec)

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
+----------------------------------------+
7 rows in set (0.00 sec)

mysql>

mysql> use information_schema
Database changed
mysql> select version(),database();
+------------+--------------------+
| version()  | database()         |
+------------+--------------------+
| 5.6.10-log | information_schema |
+------------+--------------------+
1 row in set (0.00 sec)

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_LOCKS                           |
| INNODB_TRX                             |
| INNODB_SYS_DATAFILES                   |
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  |
| INNODB_CMP                             |
| INNODB_FT_BEING_DELETED                |
| INNODB_CMP_RESET                       |
| INNODB_CMP_PER_INDEX                   |
| INNODB_CMPMEM_RESET                    |
| INNODB_FT_DELETED                      |
| INNODB_BUFFER_PAGE_LRU                 |
| INNODB_FT_INSERTED                     |
| INNODB_CMPMEM                          |
| INNODB_SYS_INDEXES                     |
| INNODB_SYS_TABLES                      |
| INNODB_SYS_FIELDS                      |
| INNODB_CMP_PER_INDEX_RESET             |
| INNODB_BUFFER_PAGE                     |
| INNODB_FT_DEFAULT_STOPWORD             |
| INNODB_FT_INDEX_TABLE                  |
| INNODB_FT_INDEX_CACHE                  |
| INNODB_SYS_TABLESPACES                 |
| INNODB_METRICS                         |
| INNODB_SYS_FOREIGN_COLS                |
| INNODB_FT_CONFIG                       |
| INNODB_BUFFER_POOL_STATS               |
| INNODB_SYS_COLUMNS                     |
| INNODB_SYS_FOREIGN                     |
+----------------------------------------+
29 rows in set (0.00 sec)

mysql>

mysql> select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 24E6E0FC5
                 trx_state: RUNNING
               trx_started: 2013-04-22 16:34:54
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 20275587
                 trx_query: (SELECT
                                        GROUP_CONCAT( CONCAT_WS('','{\"type\":\"',report_stats.type,'\",\"date\":',report_stats.date,',\"leadID\":',report_stats.leadID,',\"vehID\":',report_stats.vehID,',\"contactID\":',report_stats.contactID,'}') SEPARATOR '::') customers,
                                        COUNT(*) total, CONCAT_WS('',sales.id,'^^',sales.first,' ',sales.last) sales
                                FROM
                                        D6195.report_stats
                                        LEFT JOIN D6195.report_stats self ON report_stats.leadID=self.leadID AND report_stats.vehID=self.vehID
JOIN D6195.leads ON report_stats.leadID=leads.id
LEFT JOIN icar.sales  ON sales.id=leads.salesID
                                WHERE
                                        report_stats.date BETWEEN 1364788800  AND 1367294400 AND
                                        report_stats.type IN ('Internet Lead') AND FROM_UNIXTIME(report_stats.date,'%k') BETWEEN 9 AND 18 AND self.type='First Response'  GROUP BY CONCAT_WS('',sales.id,'^^',sales.first,' ',sales.last) ASC WITH ROLLUP)
       trx_operation_state: NULL
         trx_tables_in_use: 4
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 6850
*************************** 2. row ***************************
                    trx_id: 24E6E0ECD
                 trx_state: RUNNING
               trx_started: 2013-04-22 16:34:54
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 20275545
                 trx_query: SELECT * FROM
                        (
                        SELECT
                                actionQue.id  scheduled_action_id,
                                actionQue.date contact_due_date,
                                actions.description,
                                IF(actions.callscript = 0,null,actions.callscript) phone_script_id,
                                actions.priority,
                                leads.id customer_id,
                                CONCAT_WS('',leads.first,' ',leads.last)  customer_full_name,
                                IF((leads.dPhone IS NULL AND leads.ePhone IS NULL AND leads.fax IS NULL) OR leads.noCall='true',0,1) is

*************************** 1. row ***************************
INNODB_LOCKS
CREATE TEMPORARY TABLE `INNODB_LOCKS` (
  `lock_id` varchar(81) NOT NULL DEFAULT '',
  `lock_trx_id` varchar(18) NOT NULL DEFAULT '',
  `lock_mode` varchar(32) NOT NULL DEFAULT '',
  `lock_type` varchar(32) NOT NULL DEFAULT '',
  `lock_table` varchar(1024) NOT NULL DEFAULT '',
  `lock_index` varchar(1024) DEFAULT NULL,
  `lock_space` bigint(21) unsigned DEFAULT NULL,
  `lock_page` bigint(21) unsigned DEFAULT NULL,
  `lock_rec` bigint(21) unsigned DEFAULT NULL,
  `lock_data` varchar(8192) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_TRX
CREATE TEMPORARY TABLE `INNODB_TRX` (
  `trx_id` varchar(18) NOT NULL DEFAULT '',
  `trx_state` varchar(13) NOT NULL DEFAULT '',
  `trx_started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `trx_requested_lock_id` varchar(81) DEFAULT NULL,
  `trx_wait_started` datetime DEFAULT NULL,
  `trx_weight` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_mysql_thread_id` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_query` varchar(1024) DEFAULT NULL,
  `trx_operation_state` varchar(64) DEFAULT NULL,
  `trx_tables_in_use` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_tables_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_structs` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_memory_bytes` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_modified` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_concurrency_tickets` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_isolation_level` varchar(16) NOT NULL DEFAULT '',
  `trx_unique_checks` int(1) NOT NULL DEFAULT '0',
  `trx_foreign_key_checks` int(1) NOT NULL DEFAULT '0',
  `trx_last_foreign_key_error` varchar(256) DEFAULT NULL,
  `trx_adaptive_hash_latched` int(1) NOT NULL DEFAULT '0',
  `trx_adaptive_hash_timeout` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_is_read_only` int(1) NOT NULL DEFAULT '0',
  `trx_autocommit_non_locking` int(1) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_DATAFILES
CREATE TEMPORARY TABLE `INNODB_SYS_DATAFILES` (
  `SPACE` int(11) unsigned NOT NULL DEFAULT '0',
  `PATH` varchar(4000) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_LOCK_WAITS
CREATE TEMPORARY TABLE `INNODB_LOCK_WAITS` (
  `requesting_trx_id` varchar(18) NOT NULL DEFAULT '',
  `requested_lock_id` varchar(81) NOT NULL DEFAULT '',
  `blocking_trx_id` varchar(18) NOT NULL DEFAULT '',
  `blocking_lock_id` varchar(81) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_TABLESTATS
CREATE TEMPORARY TABLE `INNODB_SYS_TABLESTATS` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(19

Context

StackExchange Database Administrators Q#40644, answer score: 3

Revisions (0)

No revisions yet.