From 3139aa87b4f215418740939cc8d156150c355823 Mon Sep 17 00:00:00 2001 From: Murthy Narkedimilli Date: Mon, 8 Sep 2014 11:33:55 +0200 Subject: Adding patch for security bug 19471516 --- mysql-test/suite/innodb/r/foreign-keys.result | 16 ++++++++++++++++ mysql-test/suite/innodb/t/foreign-keys.test | 26 ++++++++++++++++++++++++++ 2 files changed, 42 insertions(+) create mode 100644 mysql-test/suite/innodb/r/foreign-keys.result create mode 100644 mysql-test/suite/innodb/t/foreign-keys.test (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result new file mode 100644 index 00000000000..be8d27b152c --- /dev/null +++ b/mysql-test/suite/innodb/r/foreign-keys.result @@ -0,0 +1,16 @@ +# +# Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE +# ADD FOREIGN KEY +# +CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT, +PRIMARY KEY (`department_id`)) engine=innodb; +CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT, +`title_reporter_fk` INT, PRIMARY KEY (`title_id`)); +CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)); +ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES +`people` (`people_id`); +ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people` +(`people_id`); +ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people` +(`people_id`); +drop table title, department, people; diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test new file mode 100644 index 00000000000..45642cf28a7 --- /dev/null +++ b/mysql-test/suite/innodb/t/foreign-keys.test @@ -0,0 +1,26 @@ +--source include/have_innodb.inc +--source include/have_debug.inc + +--echo # +--echo # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE +--echo # ADD FOREIGN KEY +--echo # + +CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT, +PRIMARY KEY (`department_id`)) engine=innodb; + +CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT, +`title_reporter_fk` INT, PRIMARY KEY (`title_id`)); + +CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)); + +ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES +`people` (`people_id`); + +ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people` +(`people_id`); + +ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people` +(`people_id`); + +drop table title, department, people; -- cgit v1.2.1 From 7989c62bc0c4b74f866367cc9337444913178a0d Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 7 Oct 2014 10:53:06 +0200 Subject: post-merge fixes --- mysql-test/suite/innodb/r/foreign-keys.result | 4 ++-- mysql-test/suite/innodb/t/foreign-keys.test | 9 +++++++-- 2 files changed, 9 insertions(+), 4 deletions(-) (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result index be8d27b152c..53ddf618244 100644 --- a/mysql-test/suite/innodb/r/foreign-keys.result +++ b/mysql-test/suite/innodb/r/foreign-keys.result @@ -5,8 +5,8 @@ CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT, PRIMARY KEY (`department_id`)) engine=innodb; CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT, -`title_reporter_fk` INT, PRIMARY KEY (`title_id`)); -CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)); +`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb; +CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb; ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES `people` (`people_id`); ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people` diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test index 45642cf28a7..8ee96347208 100644 --- a/mysql-test/suite/innodb/t/foreign-keys.test +++ b/mysql-test/suite/innodb/t/foreign-keys.test @@ -1,6 +1,11 @@ --source include/have_innodb.inc --source include/have_debug.inc +if (`select plugin_auth_version <= "5.5.39-MariaDB-36.0" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not fixed in XtraDB as of 5.5.39-MariaDB-36.0 or earlier +} + --echo # --echo # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE --echo # ADD FOREIGN KEY @@ -10,9 +15,9 @@ CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT, PRIMARY KEY (`department_id`)) engine=innodb; CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT, -`title_reporter_fk` INT, PRIMARY KEY (`title_id`)); +`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb; -CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)); +CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb; ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES `people` (`people_id`); -- cgit v1.2.1 From f09a8ba6a0bff4efed912d6a3a20a3c01320fcba Mon Sep 17 00:00:00 2001 From: Sergey Vojtovich Date: Wed, 15 Oct 2014 12:11:34 +0400 Subject: MDEV-6872 - innodb.innodb fails on PPC64 innodb_buffer_pool_pages_total depends on page size. On Power8 it is 65k compared to 4k on Intel. As we round allocations on page size we may get slightly more memory for buffer pool. --- mysql-test/suite/innodb/t/innodb.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 4d7dbdbd6a6..c36dc1c5f95 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -1333,7 +1333,7 @@ drop table t1; # Test for testable InnoDB status variables. This test # uses previous ones(pages_created, rows_deleted, ...). ---replace_result 511 ok 512 ok 2047 ok 513 ok +--replace_result 511 ok 512 ok 2047 ok 513 ok 515 ok SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total'; SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'; -- cgit v1.2.1 From 848d1166b648d8c409edb98b506e7bf3bfe469e4 Mon Sep 17 00:00:00 2001 From: Kristian Nielsen Date: Wed, 29 Oct 2014 15:10:02 +0100 Subject: Attempt to fix a failure in test case innodb.innodb_information_schema seen occasionally in Buildbot. The test case waits for other threads to complete, but the wait is only 2 seconds. This is likely to sometimes be too little on our heavily loaded buildbot VMs, that can easily stall for more than 2 seconds from time to time. So let's try to increase the timeout (to about 40 seconds) and see if it helps. --- mysql-test/suite/innodb/t/innodb_information_schema.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/t/innodb_information_schema.test b/mysql-test/suite/innodb/t/innodb_information_schema.test index 205344a1cd7..95b436d676c 100644 --- a/mysql-test/suite/innodb/t/innodb_information_schema.test +++ b/mysql-test/suite/innodb/t/innodb_information_schema.test @@ -122,7 +122,7 @@ SELECT * FROM ```t'\"_str` WHERE c1 = '4' FOR UPDATE; # then its contents will never change because the cache from which it is # filled is updated only if it has not been read for 0.1 seconds. See # CACHE_MIN_IDLE_TIME_US in trx/trx0i_s.c. -let $cnt=10; +let $cnt=200; while ($cnt) { let $success=`SELECT COUNT(*) = 14 FROM INFORMATION_SCHEMA.INNODB_LOCKS`; -- cgit v1.2.1 From caeffc7a7d2b259c33a4512caa951fddb369a8b5 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Sun, 26 Oct 2014 07:22:51 +0200 Subject: MDEV-6926: innodb_rows_updated is misleading on slav Merged Facebook commit dd2d11be7aaf3be270e740fb95cbc4eacb52f4d7 authored by Rongrong Zhong from https://github.com/facebook/mysql-5.6 This fixes MySQL Bug #68220 innodb_rows_updated is misleading on slave http://bugs.mysql.com/bug.php?id=68220 Added innodb_system_rows_read/inserted/updated/deleted counters that are the equivalent of innodb_rows_* but that only account for changes made to system databases (mysql, information_schame and preformance_schema). These counters will be used on slaves to differentiated the updates made on system databases from those made on user databases. innodb_rows_* status counters are not updated when innodb_system_rows_* are updated. https://github.com/facebook/mysql-5.6/commit/dd2d11be7aaf3be270e740fb95cbc4eacb52f4d7 --- mysql-test/suite/innodb/r/innodb_monitor.result | 585 ++++++++++++++++++++++++ mysql-test/suite/innodb/t/innodb_monitor.test | 387 ++++++++++++++++ 2 files changed, 972 insertions(+) create mode 100644 mysql-test/suite/innodb/r/innodb_monitor.result create mode 100644 mysql-test/suite/innodb/t/innodb_monitor.test (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/r/innodb_monitor.result b/mysql-test/suite/innodb/r/innodb_monitor.result new file mode 100644 index 00000000000..f8d24f4e6f5 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_monitor.result @@ -0,0 +1,585 @@ +set global innodb_monitor_disable = All; +select name, status from information_schema.innodb_metrics; +name status +metadata_table_handles_opened disabled +metadata_table_handles_closed disabled +metadata_table_reference_count disabled +metadata_mem_pool_size disabled +lock_deadlocks disabled +lock_timeouts disabled +lock_rec_lock_waits disabled +lock_table_lock_waits disabled +lock_rec_lock_requests disabled +lock_rec_lock_created disabled +lock_rec_lock_removed disabled +lock_rec_locks disabled +lock_table_lock_created disabled +lock_table_lock_removed disabled +lock_table_locks disabled +lock_row_lock_current_waits disabled +lock_row_lock_time disabled +lock_row_lock_time_max disabled +lock_row_lock_waits disabled +lock_row_lock_time_avg disabled +buffer_pool_size disabled +buffer_pool_reads disabled +buffer_pool_read_requests disabled +buffer_pool_write_requests disabled +buffer_pool_wait_free disabled +buffer_pool_read_ahead disabled +buffer_pool_read_ahead_evicted disabled +buffer_pool_pages_total disabled +buffer_pool_pages_misc disabled +buffer_pool_pages_data disabled +buffer_pool_bytes_data disabled +buffer_pool_pages_dirty disabled +buffer_pool_bytes_dirty disabled +buffer_pool_pages_free disabled +buffer_pages_created disabled +buffer_pages_written disabled +buffer_pages_read disabled +buffer_data_reads disabled +buffer_data_written disabled +buffer_flush_batch_scanned disabled +buffer_flush_batch_num_scan disabled +buffer_flush_batch_scanned_per_call disabled +buffer_flush_batch_rescan disabled +buffer_flush_batch_total_pages disabled +buffer_flush_batches disabled +buffer_flush_batch_pages disabled +buffer_flush_neighbor_total_pages disabled +buffer_flush_neighbor disabled +buffer_flush_neighbor_pages disabled +buffer_flush_n_to_flush_requested disabled +buffer_flush_avg_page_rate disabled +buffer_flush_lsn_avg_rate disabled +buffer_flush_pct_for_dirty disabled +buffer_flush_pct_for_lsn disabled +buffer_flush_sync_waits disabled +buffer_flush_adaptive_total_pages disabled +buffer_flush_adaptive disabled +buffer_flush_adaptive_pages disabled +buffer_flush_sync_total_pages disabled +buffer_flush_sync disabled +buffer_flush_sync_pages disabled +buffer_flush_background_total_pages disabled +buffer_flush_background disabled +buffer_flush_background_pages disabled +buffer_LRU_batch_scanned disabled +buffer_LRU_batch_num_scan disabled +buffer_LRU_batch_scanned_per_call disabled +buffer_LRU_batch_total_pages disabled +buffer_LRU_batches disabled +buffer_LRU_batch_pages disabled +buffer_LRU_single_flush_scanned disabled +buffer_LRU_single_flush_num_scan disabled +buffer_LRU_single_flush_scanned_per_call disabled +buffer_LRU_single_flush_failure_count disabled +buffer_LRU_get_free_search disabled +buffer_LRU_search_scanned disabled +buffer_LRU_search_num_scan disabled +buffer_LRU_search_scanned_per_call disabled +buffer_LRU_unzip_search_scanned disabled +buffer_LRU_unzip_search_num_scan disabled +buffer_LRU_unzip_search_scanned_per_call disabled +buffer_page_read_index_leaf disabled +buffer_page_read_index_non_leaf disabled +buffer_page_read_index_ibuf_leaf disabled +buffer_page_read_index_ibuf_non_leaf disabled +buffer_page_read_undo_log disabled +buffer_page_read_index_inode disabled +buffer_page_read_ibuf_free_list disabled +buffer_page_read_ibuf_bitmap disabled +buffer_page_read_system_page disabled +buffer_page_read_trx_system disabled +buffer_page_read_fsp_hdr disabled +buffer_page_read_xdes disabled +buffer_page_read_blob disabled +buffer_page_read_zblob disabled +buffer_page_read_zblob2 disabled +buffer_page_read_other disabled +buffer_page_written_index_leaf disabled +buffer_page_written_index_non_leaf disabled +buffer_page_written_index_ibuf_leaf disabled +buffer_page_written_index_ibuf_non_leaf disabled +buffer_page_written_undo_log disabled +buffer_page_written_index_inode disabled +buffer_page_written_ibuf_free_list disabled +buffer_page_written_ibuf_bitmap disabled +buffer_page_written_system_page disabled +buffer_page_written_trx_system disabled +buffer_page_written_fsp_hdr disabled +buffer_page_written_xdes disabled +buffer_page_written_blob disabled +buffer_page_written_zblob disabled +buffer_page_written_zblob2 disabled +buffer_page_written_other disabled +os_data_reads disabled +os_data_writes disabled +os_data_fsyncs disabled +os_pending_reads disabled +os_pending_writes disabled +os_log_bytes_written disabled +os_log_fsyncs disabled +os_log_pending_fsyncs disabled +os_log_pending_writes disabled +trx_rw_commits disabled +trx_ro_commits disabled +trx_nl_ro_commits disabled +trx_commits_insert_update disabled +trx_rollbacks disabled +trx_rollbacks_savepoint disabled +trx_rollback_active disabled +trx_active_transactions disabled +trx_rseg_history_len disabled +trx_undo_slots_used disabled +trx_undo_slots_cached disabled +trx_rseg_current_size disabled +purge_del_mark_records disabled +purge_upd_exist_or_extern_records disabled +purge_invoked disabled +purge_undo_log_pages disabled +purge_dml_delay_usec disabled +purge_stop_count disabled +purge_resume_count disabled +log_checkpoints disabled +log_lsn_last_flush disabled +log_lsn_last_checkpoint disabled +log_lsn_current disabled +log_lsn_checkpoint_age disabled +log_lsn_buf_pool_oldest disabled +log_max_modified_age_async disabled +log_max_modified_age_sync disabled +log_pending_log_writes disabled +log_pending_checkpoint_writes disabled +log_num_log_io disabled +log_waits disabled +log_write_requests disabled +log_writes disabled +compress_pages_compressed disabled +compress_pages_decompressed disabled +compression_pad_increments disabled +compression_pad_decrements disabled +index_page_splits disabled +index_page_merge_attempts disabled +index_page_merge_successful disabled +index_page_reorg_attempts disabled +index_page_reorg_successful disabled +index_page_discards disabled +adaptive_hash_searches disabled +adaptive_hash_searches_btree disabled +adaptive_hash_pages_added disabled +adaptive_hash_pages_removed disabled +adaptive_hash_rows_added disabled +adaptive_hash_rows_removed disabled +adaptive_hash_rows_deleted_no_hash_entry disabled +adaptive_hash_rows_updated disabled +file_num_open_files disabled +ibuf_merges_insert disabled +ibuf_merges_delete_mark disabled +ibuf_merges_delete disabled +ibuf_merges_discard_insert disabled +ibuf_merges_discard_delete_mark disabled +ibuf_merges_discard_delete disabled +ibuf_merges disabled +ibuf_size disabled +innodb_master_thread_sleeps disabled +innodb_activity_count disabled +innodb_master_active_loops disabled +innodb_master_idle_loops disabled +innodb_background_drop_table_usec disabled +innodb_ibuf_merge_usec disabled +innodb_log_flush_usec disabled +innodb_mem_validate_usec disabled +innodb_master_purge_usec disabled +innodb_dict_lru_usec disabled +innodb_checkpoint_usec disabled +innodb_dblwr_writes disabled +innodb_dblwr_pages_written disabled +innodb_page_size disabled +innodb_rwlock_s_spin_waits disabled +innodb_rwlock_x_spin_waits disabled +innodb_rwlock_s_spin_rounds disabled +innodb_rwlock_x_spin_rounds disabled +innodb_rwlock_s_os_waits disabled +innodb_rwlock_x_os_waits disabled +dml_reads disabled +dml_inserts disabled +dml_deletes disabled +dml_updates disabled +dml_system_reads disabled +dml_system_inserts disabled +dml_system_deletes disabled +dml_system_updates disabled +ddl_background_drop_indexes disabled +ddl_background_drop_tables disabled +ddl_online_create_index disabled +ddl_pending_alter_table disabled +icp_attempts disabled +icp_no_match disabled +icp_out_of_range disabled +icp_match disabled +set global innodb_monitor_enable = all; +select name from information_schema.innodb_metrics where status!='enabled'; +name +set global innodb_monitor_enable = aaa; +ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of 'aaa' +set global innodb_monitor_disable = All; +select name from information_schema.innodb_metrics where status!='disabled'; +name +set global innodb_monitor_reset_all = all; +select name from information_schema.innodb_metrics where count!=0; +name +set global innodb_monitor_enable = "%lock%"; +select name from information_schema.innodb_metrics +where status != IF(name like "%lock%", 'enabled', 'disabled'); +name +set global innodb_monitor_disable = "%lock%"; +select name, status from information_schema.innodb_metrics +where name like "%lock%"; +name status +lock_deadlocks disabled +lock_timeouts disabled +lock_rec_lock_waits disabled +lock_table_lock_waits disabled +lock_rec_lock_requests disabled +lock_rec_lock_created disabled +lock_rec_lock_removed disabled +lock_rec_locks disabled +lock_table_lock_created disabled +lock_table_lock_removed disabled +lock_table_locks disabled +lock_row_lock_current_waits disabled +lock_row_lock_time disabled +lock_row_lock_time_max disabled +lock_row_lock_waits disabled +lock_row_lock_time_avg disabled +innodb_rwlock_s_spin_waits disabled +innodb_rwlock_x_spin_waits disabled +innodb_rwlock_s_spin_rounds disabled +innodb_rwlock_x_spin_rounds disabled +innodb_rwlock_s_os_waits disabled +innodb_rwlock_x_os_waits disabled +set global innodb_monitor_enable = "%lock*"; +ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '%lock*' +set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%"; +select name from information_schema.innodb_metrics where status!='enabled'; +name +set global innodb_monitor_disable="%%%%%"; +select name from information_schema.innodb_metrics where status!='disabled'; +name +set global innodb_monitor_enable="%"; +select name from information_schema.innodb_metrics where status!='enabled'; +name +set global innodb_monitor_disable="%_%"; +select name from information_schema.innodb_metrics where status!='disabled'; +name +set global innodb_monitor_enable="log%%%%"; +select name from information_schema.innodb_metrics +where status != IF(name like "log%", 'enabled', 'disabled'); +name +set global innodb_monitor_enable="os_%a_fs_ncs"; +set global innodb_monitor_enable="os%pending%"; +select name, status from information_schema.innodb_metrics +where name like "os%"; +name status +os_data_reads disabled +os_data_writes disabled +os_data_fsyncs enabled +os_pending_reads enabled +os_pending_writes enabled +os_log_bytes_written disabled +os_log_fsyncs disabled +os_log_pending_fsyncs enabled +os_log_pending_writes enabled +set global innodb_monitor_enable=""; +ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' +set global innodb_monitor_enable="_"; +ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '_' +set global innodb_monitor_disable = module_metadata; +set global innodb_monitor_reset_all = module_metadata; +set global innodb_monitor_enable = metadata_table_handles_opened; +create table monitor_test(col int) engine = innodb; +select * from monitor_test; +col +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled +set global innodb_monitor_reset = metadata_table_handles_opened; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 1 NULL 1 NULL NULL 0 enabled +drop table monitor_test; +create table monitor_test(col int) engine = innodb; +select * from monitor_test; +col +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled +set global innodb_monitor_reset_all = metadata_table_handles_opened; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled +set global innodb_monitor_disable = metadata_table_handles_opened; +set global innodb_monitor_reset = metadata_table_handles_opened; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled +drop table monitor_test; +create table monitor_test(col int) engine = innodb; +select * from monitor_test; +col +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled +set global innodb_monitor_reset_all = metadata_table_handles_opened; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_enable = metadata_table_handles_opened; +drop table monitor_test; +create table monitor_test(col int) engine = innodb stats_persistent=0; +select * from monitor_test; +col +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled +set global innodb_monitor_enable = metadata_table_handles_closed; +create index idx on monitor_test(col); +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_closed"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_closed 1 NULL 1 1 NULL 1 enabled +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 2 NULL 2 enabled +metadata_table_handles_closed 1 NULL 1 1 NULL 1 enabled +metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled +metadata_mem_pool_size NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_disable = module_metadata; +set global innodb_monitor_reset = module_metadata; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled +metadata_table_handles_closed 1 NULL 1 NULL NULL 0 disabled +metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled +metadata_mem_pool_size NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_reset_all = module_metadata; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled +metadata_table_handles_closed NULL NULL 0 NULL NULL 0 disabled +metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled +metadata_mem_pool_size NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_enable = module_trx; +begin; +insert into monitor_test values(9); +commit; +begin; +insert into monitor_test values(9); +rollback; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "trx_rollbacks" or name like "trx_active_transactions"; +name max_count min_count count max_count_reset min_count_reset count_reset status +trx_rollbacks 1 NULL 1 1 NULL 1 enabled +trx_active_transactions 1 0 0 1 0 0 enabled +set global innodb_monitor_disable = module_trx; +set global innodb_monitor_enable = module_dml; +insert into monitor_test values(9); +update monitor_test set col = 10 where col = 9; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 4 NULL 4 4 NULL 4 enabled +dml_inserts 1 NULL 1 1 NULL 1 enabled +dml_deletes 0 NULL 0 0 NULL 0 enabled +dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +delete from monitor_test; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 6 NULL 6 6 NULL 6 enabled +dml_inserts 1 NULL 1 1 NULL 1 enabled +dml_deletes 2 NULL 2 2 NULL 2 enabled +dml_updates 2 NULL 2 2 NULL 2 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +set global innodb_monitor_reset = module_dml; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 6 NULL 6 0 NULL 0 enabled +dml_inserts 1 NULL 1 0 NULL 0 enabled +dml_deletes 2 NULL 2 0 NULL 0 enabled +dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +insert into monitor_test values(9); +insert into monitor_test values(1); +delete from monitor_test; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 8 NULL 8 2 NULL 2 enabled +dml_inserts 3 NULL 3 2 NULL 2 enabled +dml_deletes 4 NULL 4 2 NULL 2 enabled +dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +set global innodb_monitor_reset_all = module_dml; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 8 NULL 8 2 NULL 2 enabled +dml_inserts 3 NULL 3 2 NULL 2 enabled +dml_deletes 4 NULL 4 2 NULL 2 enabled +dml_updates 2 NULL 2 0 NULL 0 enabled +dml_system_reads 0 NULL 0 0 NULL 0 enabled +dml_system_inserts 0 NULL 0 0 NULL 0 enabled +dml_system_deletes 0 NULL 0 0 NULL 0 enabled +dml_system_updates 0 NULL 0 0 NULL 0 enabled +set global innodb_monitor_disable = module_dml; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads 8 NULL 8 2 NULL 2 disabled +dml_inserts 3 NULL 3 2 NULL 2 disabled +dml_deletes 4 NULL 4 2 NULL 2 disabled +dml_updates 2 NULL 2 0 NULL 0 disabled +dml_system_reads 0 NULL 0 0 NULL 0 disabled +dml_system_inserts 0 NULL 0 0 NULL 0 disabled +dml_system_deletes 0 NULL 0 0 NULL 0 disabled +dml_system_updates 0 NULL 0 0 NULL 0 disabled +set global innodb_monitor_reset_all = module_dml; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads NULL NULL 0 NULL NULL 0 disabled +dml_inserts NULL NULL 0 NULL NULL 0 disabled +dml_deletes NULL NULL 0 NULL NULL 0 disabled +dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_enable = dml_inserts; +insert into monitor_test values(9); +insert into monitor_test values(1); +delete from monitor_test; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; +name max_count min_count count max_count_reset min_count_reset count_reset status +dml_reads NULL NULL 0 NULL NULL 0 disabled +dml_inserts 2 NULL 2 2 NULL 2 enabled +dml_deletes NULL NULL 0 NULL NULL 0 disabled +dml_updates NULL NULL 0 NULL NULL 0 disabled +dml_system_reads NULL NULL 0 NULL NULL 0 disabled +dml_system_inserts NULL NULL 0 NULL NULL 0 disabled +dml_system_deletes NULL NULL 0 NULL NULL 0 disabled +dml_system_updates NULL NULL 0 NULL NULL 0 disabled +set global innodb_monitor_disable = module_dml; +drop table monitor_test; +set global innodb_monitor_enable = file_num_open_files; +select name, max_count, min_count, count, +max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "file_num_open_files"; +name max_count min_count count max_count_reset min_count_reset count_reset status +file_num_open_files # # # # # # enabled +set global innodb_monitor_disable = file_num_open_files; +set global innodb_monitor_enable = "icp%"; +create table monitor_test(a char(3), b int, c char(2), +primary key (a(1), c(1)), key(b)) engine = innodb; +insert into monitor_test values("13", 2, "aa"); +select a from monitor_test where b < 1 for update; +a +select name, count from information_schema.innodb_metrics +where name like "icp%"; +name count +icp_attempts 1 +icp_no_match 0 +icp_out_of_range 1 +icp_match 0 +select a from monitor_test where b < 3 for update; +a +13 +select name, count from information_schema.innodb_metrics +where name like "icp%"; +name count +icp_attempts 2 +icp_no_match 0 +icp_out_of_range 1 +icp_match 1 +drop table monitor_test; +set global innodb_monitor_disable = all; +set global innodb_monitor_reset_all = all; +select 1 from `information_schema`.`INNODB_METRICS` +where case (1) when (1) then (AVG_COUNT_RESET) else (1) end; +1 +set global innodb_monitor_enable = default; +set global innodb_monitor_disable = default; +set global innodb_monitor_reset = default; +set global innodb_monitor_reset_all = default; diff --git a/mysql-test/suite/innodb/t/innodb_monitor.test b/mysql-test/suite/innodb/t/innodb_monitor.test new file mode 100644 index 00000000000..864e0cae862 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_monitor.test @@ -0,0 +1,387 @@ +# This is the test for Metrics Monitor Table feature. +# Test the metrics monitor system's control system +# and counter accuracy. + +--source include/have_innodb.inc + +set global innodb_monitor_disable = All; + +# Test turn on/off the monitor counter with "all" option +# By default, they will be off +select name, status from information_schema.innodb_metrics; + +# Turn on all monitor counters +set global innodb_monitor_enable = all; + +# status should all change to "enabled" +select name from information_schema.innodb_metrics where status!='enabled'; + +# Test wrong argument to the global configure option +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable = aaa; + +# We require a valid monitor counter/module name. There is no default +# counter name or module. A warning will be printed asking user to +# specify a valid counter name. +#--disable_warnings +#set global innodb_monitor_enable = default; +#--enable_warnings + +# Turn off all monitor counters, option name should be case +# insensitive +set global innodb_monitor_disable = All; + +# status should all change to "disabled" +select name from information_schema.innodb_metrics where status!='disabled'; + +# Reset all counter values +set global innodb_monitor_reset_all = all; + +# count should all change to 0 +select name from information_schema.innodb_metrics where count!=0; + +# Test wildcard match, turn on all counters contain string "lock" +set global innodb_monitor_enable = "%lock%"; + +# All lock related counter should be enabled +select name from information_schema.innodb_metrics +where status != IF(name like "%lock%", 'enabled', 'disabled'); + +# Disable them +set global innodb_monitor_disable = "%lock%"; + +# All lock related counter should be disabled +select name, status from information_schema.innodb_metrics +where name like "%lock%"; + +# No match for "%lock*" +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable = "%lock*"; + +# All counters will be turned on with wildcard match string with all "%" +set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%"; + +select name from information_schema.innodb_metrics where status!='enabled'; + +# Turn off all counters +set global innodb_monitor_disable="%%%%%"; + +select name from information_schema.innodb_metrics where status!='disabled'; + +# One more round testing. All counters will be turned on with +# single wildcard character "%" +set global innodb_monitor_enable="%"; + +select name from information_schema.innodb_metrics where status!='enabled'; + +# Turn off all the counters with "%_%" +set global innodb_monitor_disable="%_%"; + +select name from information_schema.innodb_metrics where status!='disabled'; + +# Turn on all counters start with "log" +set global innodb_monitor_enable="log%%%%"; + +select name from information_schema.innodb_metrics +where status != IF(name like "log%", 'enabled', 'disabled'); + +# Turn on counters "os_data_fsync" with wildcard match "os_%a_fs_ncs", "_" +# is single character wildcard match word +set global innodb_monitor_enable="os_%a_fs_ncs"; + +# Turn on counters whose name contains "os" and "pending" with +# wildcard match "os%pending%" +set global innodb_monitor_enable="os%pending%"; + +select name, status from information_schema.innodb_metrics +where name like "os%"; + +# Empty string is an invalid option +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable=""; + +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_monitor_enable="_"; + +# Reset counters only in "module_metadata" module +set global innodb_monitor_disable = module_metadata; + +set global innodb_monitor_reset_all = module_metadata; + +# Only turn on "table_open" counter +set global innodb_monitor_enable = metadata_table_handles_opened; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb; + +# This will open the monitor_test table +select * from monitor_test; + +# "metadata_table_handles_opened" should increment by 1 +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Reset the counter value while counter is still on (started) +# This will reset value "count_reset" but not +# "count" +set global innodb_monitor_reset = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# re-create table again to increment "metadata_table_handles_opened" again +drop table monitor_test; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb; + +select * from monitor_test; + +# "metadata_table_handles_opened" should increment +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Cannot reset all monitor value while the counter is on +set global innodb_monitor_reset_all = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Turn off the counter "metadata_table_handles_opened" +set global innodb_monitor_disable = metadata_table_handles_opened; + +# Reset the counter value while counter is off (disabled) +set global innodb_monitor_reset = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# re-create table again. Since monitor is off, "metadata_table_handles_opened" +# should not be incremented +drop table monitor_test; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb; + +# "metadata_table_handles_opened" should increment +select * from monitor_test; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Reset all the counters, include those counter *_since_start +set global innodb_monitor_reset_all = metadata_table_handles_opened; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Turn on "table_open" counter again +set global innodb_monitor_enable = metadata_table_handles_opened; + +# Test metadata_table_handles_opened again to see if it is working correctly +# after above round of turning on/off/reset +drop table monitor_test; + +# Create a new table to test "metadata_table_handles_opened" counter +create table monitor_test(col int) engine = innodb stats_persistent=0; + +select * from monitor_test; + +# "metadata_table_handles_opened" should increment +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_opened"; + +# Test counter "metadata_table_handles_closed", +# create index will close the old handle +set global innodb_monitor_enable = metadata_table_handles_closed; + +create index idx on monitor_test(col); + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name = "metadata_table_handles_closed"; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; + +# Reset counters only in "module_metadata" module +set global innodb_monitor_disable = module_metadata; + +set global innodb_monitor_reset = module_metadata; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; + +set global innodb_monitor_reset_all = module_metadata; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "metadata%"; + +# Test Transaction Module +set global innodb_monitor_enable = module_trx; + +begin; +insert into monitor_test values(9); +commit; + +begin; +insert into monitor_test values(9); +rollback; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "trx_rollbacks" or name like "trx_active_transactions"; + +set global innodb_monitor_disable = module_trx; + +# Test DML Module +set global innodb_monitor_enable = module_dml; + +insert into monitor_test values(9); + +update monitor_test set col = 10 where col = 9; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +delete from monitor_test; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status + from information_schema.innodb_metrics + where name like "dml%"; + +# test reset counter while the counter is on +set global innodb_monitor_reset = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# insert/delete some rows after the reset +insert into monitor_test values(9); +insert into monitor_test values(1); + +delete from monitor_test; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# We do not allow reset_all while the counter is on, nothing +# should be reset here +set global innodb_monitor_reset_all = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# Turn off the counter +set global innodb_monitor_disable = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# Reset all counter values +set global innodb_monitor_reset_all = module_dml; + +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +# Open individual counter "dml_inserts" +set global innodb_monitor_enable = dml_inserts; + +insert into monitor_test values(9); +insert into monitor_test values(1); + +delete from monitor_test; + +# Only counter "dml_inserts" should be updated +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "dml%"; + +set global innodb_monitor_disable = module_dml; + +drop table monitor_test; + +set global innodb_monitor_enable = file_num_open_files; + +# Counters are unpredictable when innodb-file-per-table is on +--replace_column 2 # 3 # 4 # 5 # 6 # 7 # +select name, max_count, min_count, count, + max_count_reset, min_count_reset, count_reset, status +from information_schema.innodb_metrics +where name like "file_num_open_files"; + +set global innodb_monitor_disable = file_num_open_files; + +# Test ICP module counters +set global innodb_monitor_enable = "icp%"; + +create table monitor_test(a char(3), b int, c char(2), +primary key (a(1), c(1)), key(b)) engine = innodb; + +insert into monitor_test values("13", 2, "aa"); + +select a from monitor_test where b < 1 for update; + +# should have icp_attempts = 1 and icp_out_of_range = 1 +select name, count from information_schema.innodb_metrics +where name like "icp%"; + +# should have icp_attempts = 2 and icp_match = 1 +select a from monitor_test where b < 3 for update; + +select name, count from information_schema.innodb_metrics +where name like "icp%"; + +drop table monitor_test; + +set global innodb_monitor_disable = all; +set global innodb_monitor_reset_all = all; + +# Test for bug #13966091 +select 1 from `information_schema`.`INNODB_METRICS` +where case (1) when (1) then (AVG_COUNT_RESET) else (1) end; + +-- disable_warnings +set global innodb_monitor_enable = default; +set global innodb_monitor_disable = default; +set global innodb_monitor_reset = default; +set global innodb_monitor_reset_all = default; +-- enable_warnings -- cgit v1.2.1 From 080fdbf937cc01af61c141fb4e1918a3468948ea Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Mon, 3 Nov 2014 15:47:57 +0200 Subject: MDEV-4396: Fix innodb.innodb_bug14676111 test. --- mysql-test/suite/innodb/disabled.def | 2 +- .../suite/innodb/r/innodb_bug14676111.result | 50 ++++++++++++-------- mysql-test/suite/innodb/t/innodb_bug14676111.opt | 1 + mysql-test/suite/innodb/t/innodb_bug14676111.test | 53 +++++++++++++--------- 4 files changed, 65 insertions(+), 41 deletions(-) create mode 100644 mysql-test/suite/innodb/t/innodb_bug14676111.opt (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/disabled.def b/mysql-test/suite/innodb/disabled.def index ad1323d4857..8cae44a3607 100644 --- a/mysql-test/suite/innodb/disabled.def +++ b/mysql-test/suite/innodb/disabled.def @@ -9,4 +9,4 @@ # Do not use any TAB characters for whitespace. # ############################################################################## -innodb_bug14676111: MDEV-4396 + diff --git a/mysql-test/suite/innodb/r/innodb_bug14676111.result b/mysql-test/suite/innodb/r/innodb_bug14676111.result index ebecd1d00cb..c2fdfee5522 100644 --- a/mysql-test/suite/innodb/r/innodb_bug14676111.result +++ b/mysql-test/suite/innodb/r/innodb_bug14676111.result @@ -1,4 +1,6 @@ drop table if exists t1; +call mtr.add_suppression("option 'innodb-purge-threads': unsigned value 0 adjusted to*"); +set global innodb_stats_persistent = false; CREATE TABLE t1 (a int not null primary key) engine=InnoDB; set global innodb_limit_optimistic_insert_debug = 2; insert into t1 values (1); @@ -9,45 +11,55 @@ insert into t1 values (2); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; -DATA_LENGTH / 16384 -10.0000 +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; +CLUST_INDEX_SIZE +10 delete from t1 where a=4; +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; -DATA_LENGTH / 16384 -8.0000 +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; +CLUST_INDEX_SIZE +8 delete from t1 where a=5; +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; -DATA_LENGTH / 16384 -5.0000 -set global innodb_limit_optimistic_insert_debug = 10000; +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; +CLUST_INDEX_SIZE +5 +set global innodb_limit_optimistic_insert_debug = 0; delete from t1 where a=2; +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; -DATA_LENGTH / 16384 -3.0000 +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; +CLUST_INDEX_SIZE +3 insert into t1 values (2); delete from t1 where a=2; +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; -DATA_LENGTH / 16384 -2.0000 +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; +CLUST_INDEX_SIZE +2 insert into t1 values (2); delete from t1 where a=2; +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; -DATA_LENGTH / 16384 -1.0000 +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; +CLUST_INDEX_SIZE +1 drop table t1; diff --git a/mysql-test/suite/innodb/t/innodb_bug14676111.opt b/mysql-test/suite/innodb/t/innodb_bug14676111.opt new file mode 100644 index 00000000000..77945d1e4bb --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug14676111.opt @@ -0,0 +1 @@ +--innodb-sys-tablestats=1 \ No newline at end of file diff --git a/mysql-test/suite/innodb/t/innodb_bug14676111.test b/mysql-test/suite/innodb/t/innodb_bug14676111.test index 41862b8105e..ba04d421fde 100644 --- a/mysql-test/suite/innodb/t/innodb_bug14676111.test +++ b/mysql-test/suite/innodb/t/innodb_bug14676111.test @@ -3,11 +3,6 @@ -- source include/have_innodb.inc -- source include/have_debug.inc -# Note that this test needs to be able to manipulate how/when purge is done -# using @@innodb_limit_optimistic_insert_debug. This does not work with -# background purge threads, so we disabled them in the -master.opt (they are -# off by default in normal 5.5 innodb but on by default in xtradb) - if (`select count(*)=0 from information_schema.global_variables where variable_name = 'INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG'`) { --skip Test requires InnoDB built with UNIV_DEBUG definition. @@ -15,16 +10,20 @@ if (`select count(*)=0 from information_schema.global_variables where variable_n --disable_query_log set @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug; +set @old_innodb_stats_persistent = @@innodb_stats_persistent; +set @old_innodb_undo_logs = @@innodb_undo_logs; +# Limit undo segments for stable progress of purge. +set global innodb_undo_logs = 1; --enable_query_log --disable_warnings drop table if exists t1; --enable_warnings -CREATE TABLE t1 (a int not null primary key) engine=InnoDB; +call mtr.add_suppression("option 'innodb-purge-threads': unsigned value 0 adjusted to*"); -let $wait_condition= - SELECT VARIABLE_VALUE < 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS - WHERE VARIABLE_NAME = 'INNODB_PURGE_TRX_ID_AGE'; +set global innodb_stats_persistent = false; + +CREATE TABLE t1 (a int not null primary key) engine=InnoDB; # # make 4 leveled straight tree @@ -55,10 +54,12 @@ insert into t1 values (2); #(1, 2) (3) (4) (5) analyze table t1; -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; delete from t1 where a=4; ---source include/wait_condition.inc +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; +--source include/wait_innodb_all_purged.inc #deleting 1 record of 2 records don't cause merge artificially. #current tree form # (1, 5) @@ -67,10 +68,12 @@ delete from t1 where a=4; #(1, 2) (3) (5) analyze table t1; -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; delete from t1 where a=5; ---source include/wait_condition.inc +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; +--source include/wait_innodb_all_purged.inc #deleting 1 record of 2 records don't cause merge artificially. #current tree form # (1) @@ -79,16 +82,18 @@ delete from t1 where a=5; #(1, 2) (3) <- merged next analyze table t1; -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; # # cause merge at level 0 # #disable the artificial limitation of records in a page -set global innodb_limit_optimistic_insert_debug = 10000; +set global innodb_limit_optimistic_insert_debug = 0; delete from t1 where a=2; ---source include/wait_condition.inc +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; +--source include/wait_innodb_all_purged.inc #merge page occurs. and lift up occurs. #current tree form # (1) @@ -96,7 +101,7 @@ delete from t1 where a=2; # (1, 3) analyze table t1; -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; insert into t1 values (2); #current tree form @@ -105,13 +110,15 @@ insert into t1 values (2); # (1, 2, 3) delete from t1 where a=2; ---source include/wait_condition.inc +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; +--source include/wait_innodb_all_purged.inc #current tree form # (1) # (1, 3) analyze table t1; -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; insert into t1 values (2); #current tree form @@ -119,15 +126,19 @@ insert into t1 values (2); # (1, 2, 3) <- lift up this level next, because the father is root delete from t1 where a=2; ---source include/wait_condition.inc +set global innodb_purge_stop_now=ON; +set global innodb_purge_run_now=ON; +--source include/wait_innodb_all_purged.inc #current tree form # (1, 3) analyze table t1; -select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1'; +select CLUST_INDEX_SIZE from information_schema.INNODB_SYS_TABLESTATS where NAME = 'test/t1'; drop table t1; --disable_query_log set global innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug; +set global innodb_stats_persistent = @old_innodb_stats_persistent; +set global innodb_undo_logs = @old_innodb_undo_logs; --enable_query_log -- cgit v1.2.1 From ef1ba3b1e6d6030f15ae1bdc36f124ac8da61680 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Mon, 24 Nov 2014 15:26:47 +0200 Subject: MDEV-7164: innodb.innodb-alter-table-disk-full fails in buildbot on Windows Analysis: Test case uses Linux specific error codes. Fix: Can't run test case with Windows currently because requires to inject error to system. --- mysql-test/suite/innodb/t/innodb-alter-table-disk-full.test | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/t/innodb-alter-table-disk-full.test b/mysql-test/suite/innodb/t/innodb-alter-table-disk-full.test index 4e3a7bfdae6..adeb2ef9fd2 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-table-disk-full.test +++ b/mysql-test/suite/innodb/t/innodb-alter-table-disk-full.test @@ -1,5 +1,8 @@ # MDEV-6288: Innodb causes server crash after disk full, then can't ALTER TABLE any more --source include/have_innodb.inc +--source include/not_windows.inc +--source include/not_valgrind.inc +--source include/not_embedded.inc # DEBUG_SYNC must be compiled in. --source include/have_debug_sync.inc -- cgit v1.2.1 From b7cee6251a528f2a35abc825aa8fb30ea1d07f9a Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Mon, 24 Nov 2014 19:37:38 +0200 Subject: MDEV-7168: Tests innodb.innodb_stats_create_table innodb.innodb_stats_drop_locked fail and innodb.innodb_stats_fetch_nonexistent fails in buildbot on Windows Analysis: Problem is that innodb_stats_create_on_corrupted test renames mysql.innodb.index_stats and all the rest are dependend on this table. Fix: After rename back to original, restart mysqld to make sure that table is correct. --- .../suite/innodb/r/innodb_stats_create_on_corrupted.result | 12 ++++++++++++ .../suite/innodb/t/innodb_stats_create_on_corrupted.test | 12 ++++++++++++ 2 files changed, 24 insertions(+) (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/r/innodb_stats_create_on_corrupted.result b/mysql-test/suite/innodb/r/innodb_stats_create_on_corrupted.result index 47c714bb0a6..c351b222496 100644 --- a/mysql-test/suite/innodb/r/innodb_stats_create_on_corrupted.result +++ b/mysql-test/suite/innodb/r/innodb_stats_create_on_corrupted.result @@ -17,4 +17,16 @@ avg_row_length 0 max_data_length 0 index_length 0 ALTER TABLE mysql.innodb_index_stats_ RENAME TO mysql.innodb_index_stats; +SELECT seq_in_index, column_name, cardinality +FROM information_schema.statistics WHERE table_name = 'test_ps_create_on_corrupted' +ORDER BY index_name, seq_in_index; +seq_in_index 1 +column_name a +cardinality 0 +SELECT table_rows, avg_row_length, max_data_length, index_length +FROM information_schema.tables WHERE table_name = 'test_ps_create_on_corrupted'; +table_rows 0 +avg_row_length 0 +max_data_length 0 +index_length 0 DROP TABLE test_ps_create_on_corrupted; diff --git a/mysql-test/suite/innodb/t/innodb_stats_create_on_corrupted.test b/mysql-test/suite/innodb/t/innodb_stats_create_on_corrupted.test index 78c9334f800..de6026a23aa 100644 --- a/mysql-test/suite/innodb/t/innodb_stats_create_on_corrupted.test +++ b/mysql-test/suite/innodb/t/innodb_stats_create_on_corrupted.test @@ -33,4 +33,16 @@ FROM information_schema.tables WHERE table_name = 'test_ps_create_on_corrupted'; # restore the persistent storage ALTER TABLE mysql.innodb_index_stats_ RENAME TO mysql.innodb_index_stats; +--source include/restart_mysqld.inc + +-- vertical_results + +# check again +SELECT seq_in_index, column_name, cardinality +FROM information_schema.statistics WHERE table_name = 'test_ps_create_on_corrupted' +ORDER BY index_name, seq_in_index; + +SELECT table_rows, avg_row_length, max_data_length, index_length +FROM information_schema.tables WHERE table_name = 'test_ps_create_on_corrupted'; + DROP TABLE test_ps_create_on_corrupted; -- cgit v1.2.1 From 876106804edb57eb5f40656c0a21f41174e33ce5 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Mon, 24 Nov 2014 20:25:17 +0200 Subject: MDEV-7169: innodb.innodb_bug14147491 fails in buildbot on Windows Problem is that test could open Microsoft C++ Client Debugger windows with abort exceptin. Lets not try to test this on windows. --- mysql-test/suite/innodb/t/innodb_bug14147491.test | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/t/innodb_bug14147491.test b/mysql-test/suite/innodb/t/innodb_bug14147491.test index 050f7fbdd73..6f0bfca8e1d 100644 --- a/mysql-test/suite/innodb/t/innodb_bug14147491.test +++ b/mysql-test/suite/innodb/t/innodb_bug14147491.test @@ -12,6 +12,9 @@ source include/not_embedded.inc; source include/have_innodb.inc; # Require Debug for SET DEBUG source include/have_debug.inc; +# Test could open crash reporter on Windows +# if compiler set up +source include/not_windows.inc; CALL mtr.add_suppression("InnoDB: Error: Unable to read tablespace .* page no .* into the buffer pool after 100 attempts"); CALL mtr.add_suppression("InnoDB: Warning: database page corruption or a failed"); -- cgit v1.2.1 From 77a6abf31141f227202c78e88c7989e5946fa470 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Mon, 24 Nov 2014 20:35:02 +0200 Subject: MDEV-7183: innodb-wl5522-debug-zip fails in buildbot on Windows Problem is different path separators. Fixed by replacing result. --- mysql-test/suite/innodb/r/innodb-wl5522-debug-zip.result | 6 +++--- mysql-test/suite/innodb/t/innodb-wl5522-debug-zip.test | 11 ++++------- 2 files changed, 7 insertions(+), 10 deletions(-) (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/r/innodb-wl5522-debug-zip.result b/mysql-test/suite/innodb/r/innodb-wl5522-debug-zip.result index a4e44be1c72..0e863f5849e 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5522-debug-zip.result +++ b/mysql-test/suite/innodb/r/innodb-wl5522-debug-zip.result @@ -104,7 +104,7 @@ restore: t1 .ibd and .cfg files SET SESSION debug_dbug="-d,ib_import_reset_space_and_lsn_failure"; SET SESSION debug_dbug="+d,ib_import_open_tablespace_failure"; ALTER TABLE test_wl5522.t1 IMPORT TABLESPACE; -ERROR HY000: Got error 44 'Tablespace not found' from ./test_wl5522/t1.ibd +ERROR HY000: Got error 44 't1.ibd SET SESSION debug_dbug="-d,ib_import_open_tablespace_failure"; restore: t1 .ibd and .cfg files SET SESSION debug_dbug="+d,ib_import_check_bitmap_failure"; @@ -537,7 +537,7 @@ ERROR HY000: Tablespace has been discarded for table 't1' restore: t1 .ibd and .cfg files SET SESSION debug_dbug="+d,fil_space_create_failure"; ALTER TABLE test_wl5522.t1 IMPORT TABLESPACE; -ERROR HY000: Got error 11 'Generic error' from ./test_wl5522/t1.ibd +ERROR HY000: Got error 11 't1.ibd SET SESSION debug_dbug="-d,fil_space_create_failure"; DROP TABLE test_wl5522.t1; unlink: t1.ibd @@ -550,7 +550,7 @@ ERROR HY000: Tablespace has been discarded for table 't1' restore: t1 .ibd and .cfg files SET SESSION debug_dbug="+d,dict_tf_to_fsp_flags_failure"; ALTER TABLE test_wl5522.t1 IMPORT TABLESPACE; -ERROR HY000: Got error 39 'Data structure corruption' from ./test_wl5522/t1.ibd +ERROR HY000: Got error 39 't1.ibd SET SESSION debug_dbug="-d,dict_tf_to_fsp_flags_failure"; DROP TABLE test_wl5522.t1; unlink: t1.ibd diff --git a/mysql-test/suite/innodb/t/innodb-wl5522-debug-zip.test b/mysql-test/suite/innodb/t/innodb-wl5522-debug-zip.test index 1290b9b5bb7..4b03ac008d2 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5522-debug-zip.test +++ b/mysql-test/suite/innodb/t/innodb-wl5522-debug-zip.test @@ -22,7 +22,7 @@ let MYSQLD_DATADIR =`SELECT @@datadir`; let $innodb_file_per_table = `SELECT @@innodb_file_per_table`; let $innodb_file_format = `SELECT @@innodb_file_format`; let $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; -let $pathfix=/: '.*test_wl5522.*t1.ibd'/: 'test_wl5522\\t1.ibd'/; +let $pathfix=/: '.*test_wl5522.*t1.ibd'/: 'test_wl5522_t1.ibd'/; SET GLOBAL innodb_file_per_table = 1; SELECT @@innodb_file_per_table; @@ -233,8 +233,7 @@ SET SESSION debug_dbug="-d,ib_import_reset_space_and_lsn_failure"; # Test failure after attempting a tablespace open SET SESSION debug_dbug="+d,ib_import_open_tablespace_failure"; ---replace_regex /file: '.*t1.ibd'/'t1.ibd'/ - +--replace_regex /'.*[\/\\]/'/ --error ER_GET_ERRMSG ALTER TABLE test_wl5522.t1 IMPORT TABLESPACE; @@ -637,8 +636,7 @@ EOF SET SESSION debug_dbug="+d,fil_space_create_failure"; ---replace_regex $pathfix - +--replace_regex /'.*[\/\\]/'/ --error ER_GET_ERRMSG ALTER TABLE test_wl5522.t1 IMPORT TABLESPACE; @@ -669,8 +667,7 @@ EOF SET SESSION debug_dbug="+d,dict_tf_to_fsp_flags_failure"; ---replace_regex $pathfix - +--replace_regex /'.*[\/\\]/'/ --error ER_GET_ERRMSG ALTER TABLE test_wl5522.t1 IMPORT TABLESPACE; -- cgit v1.2.1 From e5802c38f9fc9329276ac9096c10a8176a283cf9 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Tue, 25 Nov 2014 08:06:41 +0200 Subject: Better comments and add a test case. --- .../suite/innodb/r/innodb-stats-sample.result | 4 ++ mysql-test/suite/innodb/t/innodb-stats-sample.test | 78 ++++++++++++++++++++++ 2 files changed, 82 insertions(+) create mode 100644 mysql-test/suite/innodb/r/innodb-stats-sample.result create mode 100644 mysql-test/suite/innodb/t/innodb-stats-sample.test (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/r/innodb-stats-sample.result b/mysql-test/suite/innodb/r/innodb-stats-sample.result new file mode 100644 index 00000000000..a049a1d82c1 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-stats-sample.result @@ -0,0 +1,4 @@ +Variable_name Value +innodb_stats_sample_pages 1 +Variable_name Value +innodb_stats_traditional OFF diff --git a/mysql-test/suite/innodb/t/innodb-stats-sample.test b/mysql-test/suite/innodb/t/innodb-stats-sample.test new file mode 100644 index 00000000000..35d35bfa382 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-stats-sample.test @@ -0,0 +1,78 @@ +--source include/have_innodb.inc +# +# Test that mysqld does not crash when running ANALYZE TABLE with +# different values of the parameter innodb_stats_sample_pages. +# + +# we care only that the following SQL commands do not produce errors +# and do not crash the server +-- disable_query_log +-- disable_result_log +-- enable_warnings + +let $sample_pages=`select @@innodb_stats_sample_pages`; +let $traditional=`select @@innodb_stats_traditional`; +SET GLOBAL innodb_stats_sample_pages=0; +#use new method to calculate statistics +SET GLOBAL innodb_stats_traditional=0; + +# check that the value has been adjusted to 1 +-- enable_result_log +SHOW VARIABLES LIKE 'innodb_stats_sample_pages'; +SHOW VARIABLES LIKE 'innodb_stats_traditional'; +-- disable_result_log + +CREATE TABLE innodb_analyze ( + a INT, + b INT, + c char(50), + KEY(a), + KEY(b,a) +) ENGINE=InnoDB; + +# test with empty table +ANALYZE TABLE innodb_analyze; + +SET GLOBAL innodb_stats_sample_pages=2; +ANALYZE TABLE innodb_analyze; + +SET GLOBAL innodb_stats_sample_pages=1; +ANALYZE TABLE innodb_analyze; + +SET GLOBAL innodb_stats_sample_pages=8000; +ANALYZE TABLE innodb_analyze; + +delimiter //; +create procedure innodb_insert_proc (repeat_count int) +begin + declare current_num int; + set current_num = 0; + while current_num < repeat_count do + insert into innodb_analyze values(current_num, current_num*100,substring(MD5(RAND()), -44)); + set current_num = current_num + 1; + end while; +end// +delimiter ;// +commit; + +set autocommit=0; +call innodb_insert_proc(7000); +commit; +set autocommit=1; + +SET GLOBAL innodb_stats_sample_pages=1; +ANALYZE TABLE innodb_analyze; + +SET GLOBAL innodb_stats_sample_pages=8; +ANALYZE TABLE innodb_analyze; + +SET GLOBAL innodb_stats_sample_pages=16; +ANALYZE TABLE innodb_analyze; + +SET GLOBAL innodb_stats_sample_pages=8000; +ANALYZE TABLE innodb_analyze; + +DROP PROCEDURE innodb_insert_proc; +DROP TABLE innodb_analyze; +EVAL SET GLOBAL innodb_stats_sample_pages=$sample_pages; +EVAL SET GLOBAL innodb_stats_traditional=$traditional; \ No newline at end of file -- cgit v1.2.1 From f3bdf9d7415bb498a32b42ef9ca10f9ac48a15fe Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Tue, 25 Nov 2014 11:38:01 +0200 Subject: MDEV-7046: MySQL#74480 - Failing assertion: os_file_status(newpath, &exists, &type) after Operating system error number 36 in a file operation. Analysis: os_file_get_status did not handle error ENAMETOOLONG correctly. Fix: Add correct handling for error ENAMETOOLONG. Note that on InnoDB case the error is not passed all the way up to server. That would be bigger rewamp. --- mysql-test/suite/innodb/r/innodb-mdev7046.result | 22 +++++++++++++++++ mysql-test/suite/innodb/t/innodb-mdev7046.test | 31 ++++++++++++++++++++++++ 2 files changed, 53 insertions(+) create mode 100644 mysql-test/suite/innodb/r/innodb-mdev7046.result create mode 100644 mysql-test/suite/innodb/t/innodb-mdev7046.test (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/r/innodb-mdev7046.result b/mysql-test/suite/innodb/r/innodb-mdev7046.result new file mode 100644 index 00000000000..db183346df5 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-mdev7046.result @@ -0,0 +1,22 @@ +call mtr.add_suppression("InnoDB: File ./test/t1*"); +call mtr.add_suppression("nnoDB: Error number*"); +USE test; +create table t1(f1 INT,f2 INT,f3 CHAR (10),primary key(f1,f2)) partition by range(f1) subpartition by hash(f2) subpartitions 2 (partition p1 values less than (0),partition p2 values less than (2),partition p3 values less than (2147483647)); +RENAME TABLE t1 TO `t2_new..............................................end`; +ERROR HY000: Error on rename of 't2_new' (Errcode: 36 "File name too long") +alter table t1 engine=innodb; +RENAME TABLE t1 TO `t2_new..............................................end`; +ERROR HY000: Error on rename of 't2_new' (errno: -1 "Internal error < 0 (Not system error)") +show warnings; +Level Code Message +Error 1025 Error on rename of 't2_new' (errno: -1 "Internal error < 0 (Not system error)") +drop table t1; +DROP DATABASE test; +CREATE DATABASE test; +USE test; +SET @@session.storage_engine=MYISAM; +CREATE TABLE t1(id INT,purchased DATE)PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN MAXVALUE (SUBPARTITION sp0 DATA DIRECTORY='/tmp/not-existing' INDEX DIRECTORY='/tmp/not-existing',SUBPARTITION sp1)); +ERROR HY000: Can't create/write to file '/tmp/not-existing/t1#P#p0#SP#sp0.MYI' (Errcode: 2 "No such file or directory") +CREATE TABLE t1(id INT,purchased DATE)PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN MAXVALUE (SUBPARTITION sp0,SUBPARTITION sp1)); +ALTER TABLE t1 ENGINE=InnoDB; +drop table t1; diff --git a/mysql-test/suite/innodb/t/innodb-mdev7046.test b/mysql-test/suite/innodb/t/innodb-mdev7046.test new file mode 100644 index 00000000000..491b13dea90 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-mdev7046.test @@ -0,0 +1,31 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +# Ignore OS errors +call mtr.add_suppression("InnoDB: File ./test/t1*"); +call mtr.add_suppression("nnoDB: Error number*"); + +# MDEV-7046: MySQL#74480 - Failing assertion: os_file_status(newpath, &exists, &type) +# after Operating system error number 36 in a file operation + +USE test; +create table t1(f1 INT,f2 INT,f3 CHAR (10),primary key(f1,f2)) partition by range(f1) subpartition by hash(f2) subpartitions 2 (partition p1 values less than (0),partition p2 values less than (2),partition p3 values less than (2147483647)); +--replace_regex /'.*t2_new.*'/'t2_new'/ +--error 7 +RENAME TABLE t1 TO `t2_new..............................................end`; +alter table t1 engine=innodb; +--replace_regex /'.*t2_new.*'/'t2_new'/ +--error 1025 +RENAME TABLE t1 TO `t2_new..............................................end`; +--replace_regex /'.*t2_new.*'/'t2_new'/ +show warnings; +drop table t1; + +DROP DATABASE test;CREATE DATABASE test;USE test; +SET @@session.storage_engine=MYISAM; +--error 1 +CREATE TABLE t1(id INT,purchased DATE)PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN MAXVALUE (SUBPARTITION sp0 DATA DIRECTORY='/tmp/not-existing' INDEX DIRECTORY='/tmp/not-existing',SUBPARTITION sp1)); +CREATE TABLE t1(id INT,purchased DATE)PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN MAXVALUE (SUBPARTITION sp0,SUBPARTITION sp1)); +ALTER TABLE t1 ENGINE=InnoDB; + +drop table t1; -- cgit v1.2.1 From 433b28cede5863953a77029f4b276b3fc15b8f48 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Mon, 1 Dec 2014 23:56:36 +0100 Subject: add a proper cleanup to innodb.innodb-mdev7046 test --- mysql-test/suite/innodb/t/innodb-mdev7046.test | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/t/innodb-mdev7046.test b/mysql-test/suite/innodb/t/innodb-mdev7046.test index 491b13dea90..388885d214d 100644 --- a/mysql-test/suite/innodb/t/innodb-mdev7046.test +++ b/mysql-test/suite/innodb/t/innodb-mdev7046.test @@ -29,3 +29,6 @@ CREATE TABLE t1(id INT,purchased DATE)PARTITION BY RANGE(YEAR(purchased)) SUBPAR ALTER TABLE t1 ENGINE=InnoDB; drop table t1; + +let $datadir=`select @@datadir`; +--remove_file $datadir/test/db.opt -- cgit v1.2.1