| Bug #119369 | MySQL 8.0 sys.x$innodb_lock_waits has Unnecessary type conversion | ||
|---|---|---|---|
| Submitted: | 12 Nov 9:10 | Modified: | 12 Nov 9:52 |
| Reporter: | wen cai | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Server: SYS Schema | Severity: | S3 (Non-critical) |
| Version: | MySQL 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Nov 9:18]
wen cai
View the corresponding source code
/** This structure represents INFORMATION_SCHEMA.innodb_trx row */
struct i_s_trx_row_t {
trx_id_t trx_id; /*!< transaction identifier */
const char *trx_state; /*!< transaction state from
trx_get_que_state_str() */
std::chrono::system_clock::time_point trx_started; /*!< trx_t::start_time */
const i_s_locks_row_t *requested_lock_row;
/*!< pointer to a row
in innodb_locks if trx
is waiting, or NULL */
/** The value of trx->lock.wait_started */
std::chrono::system_clock::time_point trx_wait_started;
/** The value of TRX_WEIGHT(trx) */
uintmax_t trx_weight;
/** If `first` is `true` then `second` is the value of the
trx->lock.schedule_weight, otherwise the `second` should be ignored and
displayed as NULL to the end user.
(This could be std::optional once we move to C++17) */
std::pair<bool, trx_schedule_weight_t> trx_schedule_weight;
ulint trx_mysql_thread_id; /*!< thd_get_thread_id() */
const char *trx_query; /*!< MySQL statement being
executed in the transaction */
const CHARSET_INFO *trx_query_cs; /*!< the charset of trx_query */
const char *trx_operation_state; /*!< trx_t::op_info */
ulint trx_tables_in_use; /*!< n_mysql_tables_in_use in
trx_t */
ulint trx_tables_locked;
/*!< mysql_n_tables_locked in
trx_t */
ulint trx_lock_structs; /*!< list len of trx_locks in
trx_t */
ulint trx_lock_memory_bytes;
/*!< mem_heap_get_size(
trx->lock_heap) */
ulint trx_rows_locked; /*!< lock_number_of_rows_locked() */
uintmax_t trx_rows_modified; /*!< trx_t::undo_no */
ulint trx_concurrency_tickets;
/*!< n_tickets_to_enter_innodb in
trx_t */
const char *trx_isolation_level;
/*!< isolation_level in trx_t */
bool trx_unique_checks;
/*!< check_unique_secondary in trx_t*/
bool trx_foreign_key_checks;
/*!< check_foreigns in trx_t */
const char *trx_foreign_key_error;
/*!< detailed_error in trx_t */
bool trx_has_search_latch;
/*!< has_search_latch in trx_t */
ulint trx_is_read_only;
/*!< trx_t::read_only */
ulint trx_is_autocommit_non_locking;
/*!< trx_is_autocommit_non_locking(trx)
*/
};
/** Row identifier (DB_ROW_ID, DATA_ROW_ID) */
typedef ib_id_t row_id_t;
/** Transaction identifier (DB_TRX_ID, DATA_TRX_ID) */
typedef ib_id_t trx_id_t;
/** Rollback pointer (DB_ROLL_PTR, DATA_ROLL_PTR) */
typedef ib_id_t roll_ptr_t;
/** Undo number */
typedef ib_id_t undo_no_t;
/** The generic InnoDB system object identifier data type */
typedef uint64_t ib_id_t;
constexpr ib_id_t IB_ID_MAX = std::numeric_limits<uint64_t>::max();
trx_id also bigint type
[12 Nov 9:52]
wen cai
see MySQL 8.4
CREATE ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `x$innodb_lock_waits` (`wait_started`,`wait_age`,`wait_age_secs`,`locked_table`,`locked_table_schema`,`locked_table_name`,`locked_table_partition`,`locked_table_subpartition`,`locked_index`,`locked_type`,`waiting_trx_id`,`waiting_trx_started`,`waiting_trx_age`,`waiting_trx_rows_locked`,`waiting_trx_rows_modified`,`waiting_pid`,`waiting_query`,`waiting_lock_id`,`waiting_lock_mode`,`blocking_trx_id`,`blocking_pid`,`blocking_query`,`blocking_lock_id`,`blocking_lock_mode`,`blocking_trx_started`,`blocking_trx_age`,`blocking_trx_rows_locked`,`blocking_trx_rows_modified`,`sql_kill_blocking_query`,`sql_kill_blocking_connection`) AS select `r`.`trx_wait_started` AS `wait_started`,timediff(now(),`r`.`trx_wait_started`) AS `wait_age`,timestampdiff(SECOND,`r`.`trx_wait_started`,now()) AS `wait_age_secs`,concat(`sys`.`quote_identifier`(`rl`.`OBJECT_SCHEMA`),'.',`sys`.`quote_identifier`(`rl`.`OBJECT_NAME`)) AS `locked_table`,`rl`.`OBJECT_SCHEMA` AS `locked_table_schema`,`rl`.`OBJECT_NAME` AS `locked_table_name`,`rl`.`PARTITION_NAME` AS `locked_table_partition`,`rl`.`SUBPARTITION_NAME` AS `locked_table_subpartition`,`rl`.`INDEX_NAME` AS `locked_index`,`rl`.`LOCK_TYPE` AS `locked_type`,`r`.`trx_id` AS `waiting_trx_id`,`r`.`trx_started` AS `waiting_trx_started`,timediff(now(),`r`.`trx_started`) AS `waiting_trx_age`,`r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,`r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,`r`.`trx_mysql_thread_id` AS `waiting_pid`,`r`.`trx_query` AS `waiting_query`,`rl`.`ENGINE_LOCK_ID` AS `waiting_lock_id`,`rl`.`LOCK_MODE` AS `waiting_lock_mode`,`b`.`trx_id` AS `blocking_trx_id`,`b`.`trx_mysql_thread_id` AS `blocking_pid`,`b`.`trx_query` AS `blocking_query`,`bl`.`ENGINE_LOCK_ID` AS `blocking_lock_id`,`bl`.`LOCK_MODE` AS `blocking_lock_mode`,`b`.`trx_started` AS `blocking_trx_started`,timediff(now(),`b`.`trx_started`) AS `blocking_trx_age`,`b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,`b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,concat('KILL QUERY ',`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_query`,concat('KILL ',`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_connection` from ((((`performance_schema`.`data_lock_waits` `w` join `information_schema`.`INNODB_TRX` `b` on((`b`.`trx_id` = cast(`w`.`BLOCKING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) join `information_schema`.`INNODB_TRX` `r` on((`r`.`trx_id` = cast(`w`.`REQUESTING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) join `performance_schema`.`data_locks` `bl` on(((`bl`.`ENGINE_LOCK_ID` = `w`.`BLOCKING_ENGINE_LOCK_ID`) and (`bl`.`ENGINE` = `w`.`ENGINE`)))) join `performance_schema`.`data_locks` `rl` on(((`rl`.`ENGINE_LOCK_ID` = `w`.`REQUESTING_ENGINE_LOCK_ID`) and (`rl`.`ENGINE` = `w`.`ENGINE`)))) order by `r`.`trx_wait_started`
character_set_client: utf8mb4
Description: show create table sys.x$innodb_lock_waits\G *************************** 1. row *************************** View: x$innodb_lock_waits Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`x$innodb_lock_waits` (`wait_started`,`wait_age`,`wait_age_secs`,`locked_table`,`locked_table_schema`,`locked_table_name`,`locked_table_partition`,`locked_table_subpartition`,`locked_index`,`locked_type`,`waiting_trx_id`,`waiting_trx_started`,`waiting_trx_age`,`waiting_trx_rows_locked`,`waiting_trx_rows_modified`,`waiting_pid`,`waiting_query`,`waiting_lock_id`,`waiting_lock_mode`,`blocking_trx_id`,`blocking_pid`,`blocking_query`,`blocking_lock_id`,`blocking_lock_mode`,`blocking_trx_started`,`blocking_trx_age`,`blocking_trx_rows_locked`,`blocking_trx_rows_modified`,`sql_kill_blocking_query`,`sql_kill_blocking_connection`) AS select `r`.`trx_wait_started` AS `wait_started`,timediff(now(),`r`.`trx_wait_started`) AS `wait_age`,timestampdiff(SECOND,`r`.`trx_wait_started`,now()) AS `wait_age_secs`,concat(`sys`.`quote_identifier`(`rl`.`OBJECT_SCHEMA`),'.',`sys`.`quote_identifier`(`rl`.`OBJECT_NAME`)) AS `locked_table`,`rl`.`OBJECT_SCHEMA` AS `locked_table_schema`,`rl`.`OBJECT_NAME` AS `locked_table_name`,`rl`.`PARTITION_NAME` AS `locked_table_partition`,`rl`.`SUBPARTITION_NAME` AS `locked_table_subpartition`,`rl`.`INDEX_NAME` AS `locked_index`,`rl`.`LOCK_TYPE` AS `locked_type`,`r`.`trx_id` AS `waiting_trx_id`,`r`.`trx_started` AS `waiting_trx_started`,timediff(now(),`r`.`trx_started`) AS `waiting_trx_age`,`r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,`r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,`r`.`trx_mysql_thread_id` AS `waiting_pid`,`r`.`trx_query` AS `waiting_query`,`rl`.`ENGINE_LOCK_ID` AS `waiting_lock_id`,`rl`.`LOCK_MODE` AS `waiting_lock_mode`,`b`.`trx_id` AS `blocking_trx_id`,`b`.`trx_mysql_thread_id` AS `blocking_pid`,`b`.`trx_query` AS `blocking_query`,`bl`.`ENGINE_LOCK_ID` AS `blocking_lock_id`,`bl`.`LOCK_MODE` AS `blocking_lock_mode`,`b`.`trx_started` AS `blocking_trx_started`,timediff(now(),`b`.`trx_started`) AS `blocking_trx_age`,`b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,`b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,concat('KILL QUERY ',`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_query`,concat('KILL ',`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_connection` from ((((`data_lock_waits` `w` join `information_schema`.`INNODB_TRX` `b` on((`b`.`trx_id` = cast(`w`.`BLOCKING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) join `information_schema`.`INNODB_TRX` `r` on((`r`.`trx_id` = cast(`w`.`REQUESTING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) join `data_locks` `bl` on(((`bl`.`ENGINE_LOCK_ID` = `w`.`BLOCKING_ENGINE_LOCK_ID`) and (`bl`.`ENGINE` = `w`.`ENGINE`)))) join `data_locks` `rl` on(((`rl`.`ENGINE_LOCK_ID` = `w`.`REQUESTING_ENGINE_LOCK_ID`) and (`rl`.`ENGINE` = `w`.`ENGINE`)))) order by `r`.`trx_wait_started` character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ### CREATE TEMPORARY TABLE `INNODB_TRX` ( `trx_id` bigint unsigned NOT NULL DEFAULT '0', `trx_state` varchar(13) NOT NULL DEFAULT '', `trx_started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `trx_requested_lock_id` varchar(126) DEFAULT NULL, `trx_wait_started` datetime DEFAULT NULL, `trx_weight` bigint unsigned NOT NULL DEFAULT '0', `trx_mysql_thread_id` bigint unsigned NOT NULL DEFAULT '0', `trx_query` varchar(1024) DEFAULT NULL, `trx_operation_state` varchar(64) DEFAULT NULL, `trx_tables_in_use` bigint unsigned NOT NULL DEFAULT '0', `trx_tables_locked` bigint unsigned NOT NULL DEFAULT '0', `trx_lock_structs` bigint unsigned NOT NULL DEFAULT '0', `trx_lock_memory_bytes` bigint unsigned NOT NULL DEFAULT '0', `trx_rows_locked` bigint unsigned NOT NULL DEFAULT '0', `trx_rows_modified` bigint unsigned NOT NULL DEFAULT '0', `trx_concurrency_tickets` bigint unsigned NOT NULL DEFAULT '0', `trx_isolation_level` varchar(16) NOT NULL DEFAULT '', `trx_unique_checks` int NOT NULL DEFAULT '0', `trx_foreign_key_checks` int NOT NULL DEFAULT '0', `trx_last_foreign_key_error` varchar(256) DEFAULT NULL, `trx_adaptive_hash_latched` int NOT NULL DEFAULT '0', `trx_adaptive_hash_timeout` bigint unsigned NOT NULL DEFAULT '0', `trx_is_read_only` int NOT NULL DEFAULT '0', `trx_autocommit_non_locking` int NOT NULL DEFAULT '0', `trx_schedule_weight` bigint unsigned DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3; ### CREATE TABLE `data_lock_waits` ( `ENGINE` varchar(32) NOT NULL, `REQUESTING_ENGINE_LOCK_ID` varchar(128) NOT NULL, `REQUESTING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL, `REQUESTING_THREAD_ID` bigint unsigned DEFAULT NULL, `REQUESTING_EVENT_ID` bigint unsigned DEFAULT NULL, `REQUESTING_OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL, `BLOCKING_ENGINE_LOCK_ID` varchar(128) NOT NULL, `BLOCKING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL, `BLOCKING_THREAD_ID` bigint unsigned DEFAULT NULL, `BLOCKING_EVENT_ID` bigint unsigned DEFAULT NULL, `BLOCKING_OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL, PRIMARY KEY (`REQUESTING_ENGINE_LOCK_ID`,`BLOCKING_ENGINE_LOCK_ID`,`ENGINE`), KEY `REQUESTING_ENGINE_LOCK_ID` (`REQUESTING_ENGINE_LOCK_ID`,`ENGINE`), KEY `BLOCKING_ENGINE_LOCK_ID` (`BLOCKING_ENGINE_LOCK_ID`,`ENGINE`), KEY `REQUESTING_ENGINE_TRANSACTION_ID` (`REQUESTING_ENGINE_TRANSACTION_ID`,`ENGINE`), KEY `BLOCKING_ENGINE_TRANSACTION_ID` (`BLOCKING_ENGINE_TRANSACTION_ID`,`ENGINE`), KEY `REQUESTING_THREAD_ID` (`REQUESTING_THREAD_ID`,`REQUESTING_EVENT_ID`), KEY `BLOCKING_THREAD_ID` (`BLOCKING_THREAD_ID`,`BLOCKING_EVENT_ID`) ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; #These three fields are of consistent type `trx_id` bigint unsigned NOT NULL DEFAULT '0' `REQUESTING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL `BLOCKING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL Why convert to the char type, especially when the INNODB_TRX table character set remains utf8mb3? ((((`data_lock_waits` `w` join `information_schema`.`INNODB_TRX` `b` on((`b`.`trx_id` = cast(`w`.`BLOCKING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) join `information_schema`.`INNODB_TRX` `r` on((`r`.`trx_id` = cast(`w`.`REQUESTING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) How to repeat: I found MySQL 5.7 trx_id type varchar(18) ,This seems to be for compatibility purposes.However, during the upgrade process, system tables are updated, and in theory, these types have already been converted. 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 I am convinced it is who forgot to modify the view statement. Suggested fix: on((`r`.`trx_id` = `w`.`REQUESTING_ENGINE_TRANSACTION_ID`