=== modified file 'mysql-test/suite/innodb/r/innodb_information_schema.result' --- mysql-test/suite/innodb/r/innodb_information_schema.result revid:sunny.bains@oracle.com-20100430113812-56x04cowg2g5ka3p +++ mysql-test/suite/innodb/r/innodb_information_schema.result 2010-04-30 17:44:50 +0000 @@ -21,3 +21,34 @@ "test"."t_max" 2 "test"."t_min" 2 "test"."`t'\""_str" 10 +Field Type Null Key Default Extra +trx_id varchar(18) NO +trx_state varchar(13) NO +trx_started datetime NO 0000-00-00 00:00:00 +trx_requested_lock_id varchar(81) YES NULL +trx_wait_started datetime YES NULL +trx_weight bigint(21) unsigned NO 0 +trx_mysql_thread_id bigint(21) unsigned NO 0 +trx_query varchar(1024) YES NULL +trx_operation_state varchar(64) YES NULL +trx_tables_in_use bigint(21) unsigned NO 0 +trx_tables_locked bigint(21) unsigned NO 0 +trx_lock_structs bigint(21) unsigned NO 0 +trx_lock_memory_bytes bigint(21) unsigned NO 0 +trx_rows_locked bigint(21) unsigned NO 0 +trx_rows_modified bigint(21) unsigned NO 0 +trx_concurrency_tickets bigint(21) unsigned NO 0 +trx_isolation_level varchar(16) NO +trx_unique_checks int(1) NO 0 +trx_foreign_key_checks int(1) NO 0 +trx_last_foreign_key_error varchar(256) YES NULL +trx_apative_hash_latched int(1) NO 0 +trx_adaptive_hash_timeout bigint(21) unsigned NO 0 +trx_state trx_weight trx_tables_in_use trx_tables_locked trx_rows_locked trx_rows_modified trx_concurrency_tickets trx_isolation_level +RUNNING 4 0 0 7 1 0 REPEATABLE READ +trx_isolation_level trx_unique_checks trx_foreign_key_checks +REPEATABLE READ 1 1 +trx_isolation_level trx_unique_checks trx_foreign_key_checks +SERIALIZABLE 0 0 +trx_state trx_isolation_level trx_last_foreign_key_error +RUNNING SERIALIZABLE `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c02`) REFERENCES `t1` (`c01`) === modified file 'mysql-test/suite/innodb/t/innodb_information_schema.test' --- mysql-test/suite/innodb/t/innodb_information_schema.test revid:sunny.bains@oracle.com-20100430113812-56x04cowg2g5ka3p +++ mysql-test/suite/innodb/t/innodb_information_schema.test 2010-04-30 17:44:25 +0000 @@ -147,3 +147,107 @@ -- disconnect con_verify_innodb_locks DROP TABLE t_min, t_max, ```t'\"_str`; + +# We need to allow time here for the connections to clean up +--sleep 0.1 + +# +# Test that transaction data is correctly "visualized" in +# INFORMATION_SCHEMA.INNODB_TRX +# + + +-- enable_result_log +DESCRIBE INFORMATION_SCHEMA.INNODB_TRX; +-- disable_result_log + +-- disable_warnings +DROP TABLE IF EXISTS t1; +-- enable_warnings + +let $table_def = +( + c01 INT, + c02 INT, + PRIMARY KEY (c01) +); + +-- eval CREATE TABLE t1 $table_def; +INSERT INTO t1 VALUES +(1,2),(2,4),(3,6),(4,8); + +let $table_def = +( + c01 INT, + c02 INT, + PRIMARY KEY (c01), + FOREIGN KEY fk1 (c02) REFERENCES t1 (c01) +); + +--eval CREATE TABLE t2 $table_def; +INSERT INTO t2 VALUES +(1,1),(2,2),(3,3); + +-- connect (con_trx,localhost,root,,) +-- connect (con_verify_innodb_trx,localhost,root,,) + +-- connection con_trx +SET autocommit=0; +INSERT INTO t1 VALUES (5,10); +SELECT * FROM t1 FOR UPDATE; + +-- sleep 0.1 +-- enable_result_log +-- connection con_verify_innodb_trx +SELECT trx_state, trx_weight, trx_tables_in_use, trx_tables_locked, +trx_rows_locked, trx_rows_modified, trx_concurrency_tickets, +trx_isolation_level +FROM INFORMATION_SCHEMA.INNODB_TRX; + +-- disable_result_log + +# Change from some defaults and check they are shown properly +-- enable_result_log +-- connection con_verify_innodb_trx +SELECT trx_isolation_level, trx_unique_checks, trx_foreign_key_checks +FROM INFORMATION_SCHEMA.INNODB_TRX; +-- disable_result_log + +-- connection con_trx +ROLLBACK; +SET FOREIGN_KEY_CHECKS = 0; +SET UNIQUE_CHECKS = 0; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; +BEGIN; +INSERT INTO t1 VALUES (6,12); + +--sleep 0.1 +-- enable_result_log +-- connection con_verify_innodb_trx +SELECT trx_isolation_level, trx_unique_checks, trx_foreign_key_checks +FROM INFORMATION_SCHEMA.INNODB_TRX; +-- disable_result_log + +# Force a foreign key error for trx_last_foreign_key_error +-- connection con_trx +ROLLBACK; +SET FOREIGN_KEY_CHECKS = 1; +SET UNIQUE_CHECKS = 1; +BEGIN; +-- error 1452 +INSERT INTO t2 VALUES (4,10); + +--sleep 0.1 +-- enable_result_log +-- connection con_verify_innodb_trx +SELECT trx_state, trx_isolation_level, trx_last_foreign_key_error +FROM INFORMATION_SCHEMA.INNODB_TRX; +-- disable_result_log + +-- connection default + +-- disconnect con_trx +-- disconnect con_verify_innodb_trx + +DROP TABLE t2; +DROP TABLE t1; === modified file 'storage/innobase/handler/ha_innodb.cc' --- storage/innobase/handler/ha_innodb.cc revid:sunny.bains@oracle.com-20100430113812-56x04cowg2g5ka3p +++ storage/innobase/handler/ha_innodb.cc 2010-04-30 12:56:36 +0000 @@ -8853,7 +8853,7 @@ { trx_t* trx; static const char truncated_msg[] = "... truncated...\n"; - const long MAX_STATUS_SIZE = 64000; + const long MAX_STATUS_SIZE = 1048576; ulint trx_list_start = ULINT_UNDEFINED; ulint trx_list_end = ULINT_UNDEFINED; === modified file 'storage/innobase/handler/i_s.cc' --- storage/innobase/handler/i_s.cc revid:sunny.bains@oracle.com-20100430113812-56x04cowg2g5ka3p +++ storage/innobase/handler/i_s.cc 2010-04-30 17:39:31 +0000 @@ -293,6 +293,132 @@ STRUCT_FLD(old_name, ""), STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, +#define IDX_TRX_OPERATION 8 + {STRUCT_FLD(field_name, "trx_operation_state"), + STRUCT_FLD(field_length, 64), + STRUCT_FLD(field_type, MYSQL_TYPE_STRING), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_MAYBE_NULL), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_TABLES_USED 9 + {STRUCT_FLD(field_name, "trx_tables_in_use"), + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS), + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_TABLES_LOCKED 10 + {STRUCT_FLD(field_name, "trx_tables_locked"), + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS), + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_LOCK_STRUCTS 11 + {STRUCT_FLD(field_name, "trx_lock_structs"), + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS), + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_LOCK_MEMORY 12 + {STRUCT_FLD(field_name, "trx_lock_memory_bytes"), + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS), + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_ROW_LOCKS 13 + {STRUCT_FLD(field_name, "trx_rows_locked"), + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS), + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_UNDO 14 + {STRUCT_FLD(field_name, "trx_rows_modified"), + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS), + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_CONNCURRENCY_TICKETS 15 + {STRUCT_FLD(field_name, "trx_concurrency_tickets"), + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS), + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_ISOLATION_LEVEL 16 + {STRUCT_FLD(field_name, "trx_isolation_level"), + STRUCT_FLD(field_length, 16), + STRUCT_FLD(field_type, MYSQL_TYPE_STRING), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, 0), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_UNIQUE_CHECKS 17 + {STRUCT_FLD(field_name, "trx_unique_checks"), + STRUCT_FLD(field_length, 1), + STRUCT_FLD(field_type, MYSQL_TYPE_LONG), + STRUCT_FLD(value, 1), + STRUCT_FLD(field_flags, 0), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_FOREIGN_CHECKS 18 + {STRUCT_FLD(field_name, "trx_foreign_key_checks"), + STRUCT_FLD(field_length, 1), + STRUCT_FLD(field_type, MYSQL_TYPE_LONG), + STRUCT_FLD(value, 1), + STRUCT_FLD(field_flags, 0), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_LAST_ERROR 19 + {STRUCT_FLD(field_name, "trx_last_foreign_key_error"), + STRUCT_FLD(field_length, 256), + STRUCT_FLD(field_type, MYSQL_TYPE_STRING), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_MAYBE_NULL), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_HAS_SEARCH_LATCH 20 + {STRUCT_FLD(field_name, "trx_apative_hash_latched"), + STRUCT_FLD(field_length, 1), + STRUCT_FLD(field_type, MYSQL_TYPE_LONG), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, 0), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + +#define IDX_TRX_SEARCH_LATCH_TIMEOUT 21 + {STRUCT_FLD(field_name, "trx_adaptive_hash_timeout"), + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS), + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG), + STRUCT_FLD(value, 0), + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED), + STRUCT_FLD(old_name, ""), + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)}, + END_OF_ST_FIELD_INFO }; @@ -375,6 +501,62 @@ OK(field_store_string(fields[IDX_TRX_QUERY], row->trx_query)); + /* trx_operation_state */ + OK(field_store_string(fields[IDX_TRX_OPERATION], + row->trx_operation_state)); + + /* trx_tables_in_use */ + OK(fields[IDX_TRX_TABLES_USED]->store((longlong) row->trx_tables_in_use, + true)); + + /* trx_tables_in_use */ + OK(fields[IDX_TRX_TABLES_LOCKED]->store((longlong) row->trx_tables_in_use, + true)); + + /* trx_lock_structs */ + OK(fields[IDX_TRX_LOCK_STRUCTS]->store((longlong) row->trx_lock_structs, + true)); + + /* trx_lock_memory_bytes */ + OK(fields[IDX_TRX_LOCK_MEMORY]->store((longlong) row->trx_lock_memory_bytes, + true)); + + /* trx_rows_locked */ + OK(fields[IDX_TRX_ROW_LOCKS]->store((longlong) row->trx_rows_locked, + true)); + + /* trx_rows_modified */ + OK(fields[IDX_TRX_UNDO]->store((longlong) row->trx_rows_modified, + true)); + + /* trx_concurrency_tickets */ + OK(fields[IDX_TRX_CONNCURRENCY_TICKETS]->store((longlong) row->trx_concurrency_tickets, + true)); + + /* trx_isolation_level */ + OK(field_store_string(fields[IDX_TRX_ISOLATION_LEVEL], + row->trx_isolation_level)); + + /* trx_unique_checks */ + OK(fields[IDX_TRX_UNIQUE_CHECKS]->store( + (bool) row->trx_unique_checks)); + + /* trx_foreign_key_checks */ + OK(fields[IDX_TRX_FOREIGN_CHECKS]->store( + (bool) row->trx_foreign_key_checks)); + + /* trx_last_foreign_key_error */ + OK(field_store_string(fields[IDX_TRX_LAST_ERROR], + row->trx_foreign_key_error)); + + /* trx_apative_hash_latched */ + OK(fields[IDX_TRX_HAS_SEARCH_LATCH]->store( + (bool) row->trx_has_search_latch)); + + /* trx_adaptive_hash_timeout */ + OK(fields[IDX_TRX_SEARCH_LATCH_TIMEOUT]->store((longlong) row->trx_search_latch_timeout, + true)); + OK(schema_table_store_record(thd, table)); } === modified file 'storage/innobase/include/trx0i_s.h' --- storage/innobase/include/trx0i_s.h revid:sunny.bains@oracle.com-20100430113812-56x04cowg2g5ka3p +++ storage/innobase/include/trx0i_s.h 2010-04-30 14:56:53 +0000 @@ -44,6 +44,14 @@ i_s_trx_row_t::trx_query */ #define TRX_I_S_TRX_QUERY_MAX_LEN 1024 +/** The maximum length of a string that can be stored in +i_s_trx_row_t::trx_operation_state */ +#define TRX_I_S_TRX_OP_STATE_MAX_LEN 64 + +/** the maximum length of a string that can be stored in +i_s_trx_row_t::trx_foreign_key_error */ +#define TRX_I_S_TRX_FK_ERROR_MAX_LEN 256 + /** A row of INFORMATION_SCHEMA.innodb_locks */ typedef struct i_s_locks_row_struct i_s_locks_row_t; /** A row of INFORMATION_SCHEMA.innodb_trx */ @@ -110,6 +118,32 @@ /*!< thd_get_thread_id() */ const char* trx_query; /*!< MySQL statement being executed in the transaction */ + const char* trx_operation_state; /*!< trx_struct::op_info */ + ulint trx_tables_in_use; + /*!< trx_struct::n_mysql_tables_in_use */ + ulint trx_tables_locked; + /*!< trx_struct::mysql_n_tables_locked */ + ullint trx_lock_structs; + /*!< UT_LIST_GET_LEN(trx_struct::trx_locks) */ + ullint trx_lock_memory_bytes; + /*!< mem_heap_get_size(trx->lock_heap) */ + ullint trx_rows_locked; + /*!< lock_number_of_rows_locked() */ + ullint trx_rows_modified; /*!< trx_struct::undo_no */ + ulint trx_concurrency_tickets; + /*!< trx_struct::n_tickets_to_enter_innodb */ + const char* trx_isolation_level; + /*!< trx_struct::isolation_level */ + unsigned trx_unique_checks; + /*!< trx_struct::check_unique_secondary */ + unsigned trx_foreign_key_checks; + /*!< trx_struct::check_foreigns */ + const char* trx_foreign_key_error; + /*!< trx_struct::detailed_error */ + unsigned trx_has_search_latch; + /*!< trx_struct::has_search_latch */ + ulint trx_search_latch_timeout; + /*!< trx_struct::search_latch_timeout */ }; /** This structure represents INFORMATION_SCHEMA.innodb_lock_waits row */ === modified file 'storage/innobase/trx/trx0i_s.c' --- storage/innobase/trx/trx0i_s.c revid:sunny.bains@oracle.com-20100430113812-56x04cowg2g5ka3p +++ storage/innobase/trx/trx0i_s.c 2010-04-30 15:13:10 +0000 @@ -427,7 +427,7 @@ /*=========*/ i_s_trx_row_t* row, /*!< out: result object that's filled */ - const trx_t* trx, /*!< in: transaction to + trx_t* trx, /*!< in: transaction to get data from */ const i_s_locks_row_t* requested_lock_row,/*!< in: pointer to the corresponding row in @@ -499,6 +499,108 @@ row->trx_query = NULL; } + if (trx->op_info != NULL && trx->op_info != "") { + + if (strlen(trx->op_info) + > TRX_I_S_TRX_OP_STATE_MAX_LEN) { + + char op_state[TRX_I_S_TRX_OP_STATE_MAX_LEN + 1]; + + memcpy(op_state, trx->op_info, + TRX_I_S_TRX_OP_STATE_MAX_LEN); + op_state[TRX_I_S_TRX_OP_STATE_MAX_LEN] = '\0'; + + row->trx_operation_state = ha_storage_put_memlim( + cache->storage, op_state, + TRX_I_S_TRX_OP_STATE_MAX_LEN + 1, + MAX_ALLOWED_FOR_STORAGE(cache)); + } else { + + row->trx_operation_state = ha_storage_put_str_memlim( + cache->storage, trx->op_info, + MAX_ALLOWED_FOR_STORAGE(cache)); + } + + if (row->trx_operation_state == NULL) { + + return(FALSE); + } + } else { + + row->trx_operation_state = NULL; + } + + row->trx_tables_in_use = (ullint) trx->n_mysql_tables_in_use; + + row->trx_tables_locked = (ullint) trx->mysql_n_tables_locked; + + row->trx_lock_structs = (ullint) UT_LIST_GET_LEN(trx->trx_locks); + + row->trx_lock_memory_bytes = (ullint) mem_heap_get_size(trx->lock_heap); + + row->trx_rows_locked = (ullint) lock_number_of_rows_locked(trx); + + row->trx_rows_modified = (ullint) ut_conv_dulint_to_longlong(trx->undo_no); + + row->trx_concurrency_tickets = (ullint) trx->n_tickets_to_enter_innodb; + + switch (trx->isolation_level){ + case TRX_ISO_READ_UNCOMMITTED: + row->trx_isolation_level = "READ UNCOMMITTED"; + break; + case TRX_ISO_READ_COMMITTED: + row->trx_isolation_level = "READ COMMITTED"; + break; + case TRX_ISO_REPEATABLE_READ: + row->trx_isolation_level = "REPEATABLE READ"; + break; + case TRX_ISO_SERIALIZABLE: + row->trx_isolation_level = "SERIALIZABLE"; + break; + /* Should not happen as TRX_ISO_READ_COMMITTED is default */ + default: + row->trx_isolation_level = "UNKNOWN"; + } + + row->trx_unique_checks = (bool) trx->check_unique_secondary; + + row->trx_foreign_key_checks = (bool) trx->check_foreigns; + + if (trx->detailed_error != NULL && trx->detailed_error != "") { + + if (strlen(trx->detailed_error) + > TRX_I_S_TRX_FK_ERROR_MAX_LEN) { + + char fk_error[TRX_I_S_TRX_FK_ERROR_MAX_LEN + 1]; + + memcpy(fk_error, trx->detailed_error, + TRX_I_S_TRX_FK_ERROR_MAX_LEN); + fk_error[TRX_I_S_TRX_FK_ERROR_MAX_LEN] = '\0'; + + row->trx_foreign_key_error = ha_storage_put_memlim( + cache->storage, fk_error, + TRX_I_S_TRX_FK_ERROR_MAX_LEN + 1, + MAX_ALLOWED_FOR_STORAGE(cache)); + } else { + + row->trx_foreign_key_error = ha_storage_put_str_memlim( + cache->storage, trx->detailed_error, + MAX_ALLOWED_FOR_STORAGE(cache)); + } + + if (row->trx_foreign_key_error == NULL) { + + return(FALSE); + } + } else { + + row->trx_foreign_key_error = NULL; + } + + row->trx_has_search_latch = (bool) trx->has_search_latch; + + row->trx_search_latch_timeout = (ullint) trx->search_latch_timeout; + return(TRUE); }