diff options
Diffstat (limited to 'storage/rocksdb/mysql-test/rocksdb/r')
27 files changed, 1037 insertions, 690 deletions
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_not_null.result b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_not_null.result index 64d87b7116d..e5a8d2dd2d3 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_not_null.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_not_null.result @@ -1654,15 +1654,15 @@ CONCAT('', MAX(d1_0)), CONCAT('', MAX(d10_10)), CONCAT('', MAX(d53)), CONCAT('', MAX(d53_10)) FROM t1; -CONCAT('', MAX(f)) 9.999999680285692e37 +CONCAT('', MAX(f)) 1e38 CONCAT('', MAX(d)) 1e81 CONCAT('', MAX(d10_10)) 0.9999999999 CONCAT('', MAX(d1_0)) 9 CONCAT('', MAX(d53)) 100000000000000000000000000000000000000000000000000000 CONCAT('', MAX(d53_10)) 10000000000000000000000000000000000000000000.0000000000 -CONCAT('', MAX(f0)) 9.999999680285692e37 +CONCAT('', MAX(f0)) 1e38 CONCAT('', MAX(f20_3)) 99999998430674940.000 -CONCAT('', MAX(f23_0)) 9.999999680285692e37 +CONCAT('', MAX(f23_0)) 1e38 CONCAT('', MAX(r1_1)) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 9999999999999999999999999999999999999999999999999999999999999.9999, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_null.result b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_null.result index ba651fcbb14..ac87cb02571 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_null.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_null.result @@ -1441,15 +1441,15 @@ CONCAT('', MAX(d1_0)), CONCAT('', MAX(d10_10)), CONCAT('', MAX(d53)), CONCAT('', MAX(d53_10)) FROM t1; -CONCAT('', MAX(f)) 9.999999680285692e37 +CONCAT('', MAX(f)) 1e38 CONCAT('', MAX(d)) 1e81 CONCAT('', MAX(d10_10)) 0.9999999999 CONCAT('', MAX(d1_0)) 9 CONCAT('', MAX(d53)) 100000000000000000000000000000000000000000000000000000 CONCAT('', MAX(d53_10)) 10000000000000000000000000000000000000000000.0000000000 -CONCAT('', MAX(f0)) 9.999999680285692e37 +CONCAT('', MAX(f0)) 1e38 CONCAT('', MAX(f20_3)) 99999998430674940.000 -CONCAT('', MAX(f23_0)) 9.999999680285692e37 +CONCAT('', MAX(f23_0)) 1e38 CONCAT('', MAX(r1_1)) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 9999999999999999999999999999999999999999999999999999999999999.9999, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_unsigned.result b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_unsigned.result index b931743d59a..439f9be9a8a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/col_opt_unsigned.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/col_opt_unsigned.result @@ -328,15 +328,15 @@ CONCAT('', MAX(d1_0)), CONCAT('', MAX(d10_10)), CONCAT('', MAX(d53)), CONCAT('', MAX(d53_10)) FROM t1; -CONCAT('', MAX(f)) 9.999999680285692e37 +CONCAT('', MAX(f)) 1e38 CONCAT('', MAX(d)) 1e81 CONCAT('', MAX(d10_10)) 0.9999999999 CONCAT('', MAX(d1_0)) 9 CONCAT('', MAX(d53)) 100000000000000000000000000000000000000000000000000000 CONCAT('', MAX(d53_10)) 10000000000000000000000000000000000000000000.0000000000 -CONCAT('', MAX(f0)) 9.999999680285692e37 +CONCAT('', MAX(f0)) 1e38 CONCAT('', MAX(f20_3)) 99999998430674940.000 -CONCAT('', MAX(f23_0)) 9.999999680285692e37 +CONCAT('', MAX(f23_0)) 1e38 CONCAT('', MAX(r1_1)) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 9999999999999999999999999999999999999999999999999999999999999.9999, diff --git a/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result b/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result new file mode 100644 index 00000000000..cb31847cdd8 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result @@ -0,0 +1,342 @@ +SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; +trx_id trx_state trx_started trx_requested_lock_id trx_wait_started trx_weight trx_mysql_thread_id trx_query trx_operation_state trx_tables_in_use trx_tables_locked trx_lock_structs trx_lock_memory_bytes trx_rows_locked trx_rows_modified trx_concurrency_tickets trx_isolation_level trx_unique_checks trx_foreign_key_checks trx_last_foreign_key_error trx_is_read_only trx_autocommit_non_locking +SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; +lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data +SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +requesting_trx_id requested_lock_id blocking_trx_id blocking_lock_id +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP; +page_size compress_ops compress_ops_ok compress_time uncompress_ops uncompress_time +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_RESET; +page_size compress_ops compress_ops_ok compress_time uncompress_ops uncompress_time +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX; +database_name table_name index_name compress_ops compress_ops_ok compress_time uncompress_ops uncompress_time +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX_RESET; +database_name table_name index_name compress_ops compress_ops_ok compress_time uncompress_ops uncompress_time +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM; +page_size buffer_pool_instance pages_used pages_free relocation_ops relocation_time +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM_RESET; +page_size buffer_pool_instance pages_used pages_free relocation_ops relocation_time +SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS; +NAME SUBSYSTEM COUNT MAX_COUNT MIN_COUNT AVG_COUNT COUNT_RESET MAX_COUNT_RESET MIN_COUNT_RESET AVG_COUNT_RESET TIME_ENABLED TIME_DISABLED TIME_ELAPSED TIME_RESET STATUS TYPE COMMENT +metadata_table_handles_opened metadata 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of table handles opened +metadata_table_handles_closed metadata 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of table handles closed +metadata_table_reference_count metadata 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Table reference counter +lock_deadlocks lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of deadlocks +lock_timeouts lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of lock timeouts +lock_rec_lock_waits lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times enqueued into record lock wait queue +lock_table_lock_waits lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times enqueued into table lock wait queue +lock_rec_lock_requests lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of record locks requested +lock_rec_lock_created lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of record locks created +lock_rec_lock_removed lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of record locks removed from the lock queue +lock_rec_locks lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Current number of record locks on tables +lock_table_lock_created lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of table locks created +lock_table_lock_removed lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of table locks removed from the lock queue +lock_table_locks lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Current number of table locks on tables +lock_row_lock_current_waits lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of row locks currently being waited for (innodb_row_lock_current_waits) +lock_row_lock_time lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Time spent in acquiring row locks, in milliseconds (innodb_row_lock_time) +lock_row_lock_time_max lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value The maximum time to acquire a row lock, in milliseconds (innodb_row_lock_time_max) +lock_row_lock_waits lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of times a row lock had to be waited for (innodb_row_lock_waits) +lock_row_lock_time_avg lock 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value The average time to acquire a row lock, in milliseconds (innodb_row_lock_time_avg) +buffer_pool_size server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Server buffer pool size (all buffer pools) in bytes +buffer_pool_reads buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of reads directly from disk (innodb_buffer_pool_reads) +buffer_pool_read_requests buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of logical read requests (innodb_buffer_pool_read_requests) +buffer_pool_write_requests buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of write requests (innodb_buffer_pool_write_requests) +buffer_pool_wait_free buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of times waited for free buffer (innodb_buffer_pool_wait_free) +buffer_pool_read_ahead buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of pages read as read ahead (innodb_buffer_pool_read_ahead) +buffer_pool_read_ahead_evicted buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Read-ahead pages evicted without being accessed (innodb_buffer_pool_read_ahead_evicted) +buffer_pool_pages_total buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Total buffer pool size in pages (innodb_buffer_pool_pages_total) +buffer_pool_pages_misc buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Buffer pages for misc use such as row locks or the adaptive hash index (innodb_buffer_pool_pages_misc) +buffer_pool_pages_data buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Buffer pages containing data (innodb_buffer_pool_pages_data) +buffer_pool_bytes_data buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Buffer bytes containing data (innodb_buffer_pool_bytes_data) +buffer_pool_pages_dirty buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Buffer pages currently dirty (innodb_buffer_pool_pages_dirty) +buffer_pool_bytes_dirty buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Buffer bytes currently dirty (innodb_buffer_pool_bytes_dirty) +buffer_pool_pages_free buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Buffer pages currently free (innodb_buffer_pool_pages_free) +buffer_pages_created buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of pages created (innodb_pages_created) +buffer_pages_written buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of pages written (innodb_pages_written) +buffer_index_pages_written buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of index pages written (innodb_index_pages_written) +buffer_non_index_pages_written buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of non index pages written (innodb_non_index_pages_written) +buffer_pages_read buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of pages read (innodb_pages_read) +buffer_index_sec_rec_cluster_reads buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of secondary record reads triggered cluster read +buffer_index_sec_rec_cluster_reads_avoided buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of secondary record reads avoided triggering cluster read +buffer_data_reads buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Amount of data read in bytes (innodb_data_reads) +buffer_data_written buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Amount of data written in bytes (innodb_data_written) +buffer_flush_batch_scanned buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages scanned as part of flush batch +buffer_flush_batch_num_scan buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of times buffer flush list flush is called +buffer_flush_batch_scanned_per_call buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Pages scanned per flush batch scan +buffer_flush_batch_total_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages flushed as part of flush batch +buffer_flush_batches buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of flush batches +buffer_flush_batch_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Pages queued as a flush batch +buffer_flush_neighbor_total_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total neighbors flushed as part of neighbor flush +buffer_flush_neighbor buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of times neighbors flushing is invoked +buffer_flush_neighbor_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Pages queued as a neighbor batch +buffer_flush_n_to_flush_requested buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of pages requested for flushing. +buffer_flush_n_to_flush_by_age buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of pages target by LSN Age for flushing. +buffer_flush_adaptive_avg_time_slot buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Avg time (ms) spent for adaptive flushing recently per slot. +buffer_LRU_batch_flush_avg_time_slot buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Avg time (ms) spent for LRU batch flushing recently per slot. +buffer_flush_adaptive_avg_time_thread buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Avg time (ms) spent for adaptive flushing recently per thread. +buffer_LRU_batch_flush_avg_time_thread buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Avg time (ms) spent for LRU batch flushing recently per thread. +buffer_flush_adaptive_avg_time_est buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Estimated time (ms) spent for adaptive flushing recently. +buffer_LRU_batch_flush_avg_time_est buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Estimated time (ms) spent for LRU batch flushing recently. +buffer_flush_avg_time buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Avg time (ms) spent for flushing recently. +buffer_flush_adaptive_avg_pass buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Numner of adaptive flushes passed during the recent Avg period. +buffer_LRU_batch_flush_avg_pass buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of LRU batch flushes passed during the recent Avg period. +buffer_flush_avg_pass buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of flushes passed during the recent Avg period. +buffer_LRU_get_free_loops buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Total loops in LRU get free. +buffer_LRU_get_free_waits buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Total sleep waits in LRU get free. +buffer_flush_avg_page_rate buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Average number of pages at which flushing is happening +buffer_flush_lsn_avg_rate buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Average redo generation rate +buffer_flush_pct_for_dirty buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Percent of IO capacity used to avoid max dirty page limit +buffer_flush_pct_for_lsn buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Percent of IO capacity used to avoid reusable redo space limit +buffer_flush_sync_waits buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times a wait happens due to sync flushing +buffer_flush_adaptive_total_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages flushed as part of adaptive flushing +buffer_flush_adaptive buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of adaptive batches +buffer_flush_adaptive_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Pages queued as an adaptive batch +buffer_flush_sync_total_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages flushed as part of sync batches +buffer_flush_sync buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of sync batches +buffer_flush_sync_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Pages queued as a sync batch +buffer_flush_background_total_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages flushed as part of background batches +buffer_flush_background buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of background batches +buffer_flush_background_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Pages queued as a background batch +buffer_LRU_batch_scanned buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages scanned as part of LRU batch +buffer_LRU_batch_num_scan buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of times LRU batch is called +buffer_LRU_batch_scanned_per_call buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Pages scanned per LRU batch call +buffer_LRU_batch_flush_total_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages flushed as part of LRU batches +buffer_LRU_batches_flush buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of LRU batches +buffer_LRU_batch_flush_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Pages queued as an LRU batch +buffer_LRU_batch_evict_total_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages evicted as part of LRU batches +buffer_LRU_batches_evict buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of LRU batches +buffer_LRU_batch_evict_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Pages queued as an LRU batch +buffer_LRU_single_flush_scanned buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages scanned as part of single page LRU flush +buffer_LRU_single_flush_num_scan buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of times single page LRU flush is called +buffer_LRU_single_flush_scanned_per_call buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Page scanned per single LRU flush +buffer_LRU_single_flush_failure_count Buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times attempt to flush a single page from LRU failed +buffer_LRU_get_free_search Buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of searches performed for a clean page +buffer_LRU_search_scanned buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages scanned as part of LRU search +buffer_LRU_search_num_scan buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of times LRU search is performed +buffer_LRU_search_scanned_per_call buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Page scanned per single LRU search +buffer_LRU_unzip_search_scanned buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_owner Total pages scanned as part of LRU unzip search +buffer_LRU_unzip_search_num_scan buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Number of times LRU unzip search is performed +buffer_LRU_unzip_search_scanned_per_call buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled set_member Page scanned per single LRU unzip search +buffer_page_read_index_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Index Leaf Pages read +buffer_page_read_index_non_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Index Non-leaf Pages read +buffer_page_read_index_ibuf_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Insert Buffer Index Leaf Pages read +buffer_page_read_index_ibuf_non_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Insert Buffer Index Non-Leaf Pages read +buffer_page_read_undo_log buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Undo Log Pages read +buffer_page_read_index_inode buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Index Inode Pages read +buffer_page_read_ibuf_free_list buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Insert Buffer Free List Pages read +buffer_page_read_ibuf_bitmap buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Insert Buffer Bitmap Pages read +buffer_page_read_system_page buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of System Pages read +buffer_page_read_trx_system buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Transaction System Pages read +buffer_page_read_fsp_hdr buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of File Space Header Pages read +buffer_page_read_xdes buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Extent Descriptor Pages read +buffer_page_read_blob buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Uncompressed BLOB Pages read +buffer_page_read_zblob buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of First Compressed BLOB Pages read +buffer_page_read_zblob2 buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Subsequent Compressed BLOB Pages read +buffer_page_read_other buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of other/unknown (old version of InnoDB) Pages read +buffer_page_written_index_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Index Leaf Pages written +buffer_page_written_index_non_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Index Non-leaf Pages written +buffer_page_written_index_ibuf_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Insert Buffer Index Leaf Pages written +buffer_page_written_index_ibuf_non_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Insert Buffer Index Non-Leaf Pages written +buffer_page_written_undo_log buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Undo Log Pages written +buffer_page_written_index_inode buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Index Inode Pages written +buffer_page_written_ibuf_free_list buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Insert Buffer Free List Pages written +buffer_page_written_ibuf_bitmap buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Insert Buffer Bitmap Pages written +buffer_page_written_system_page buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of System Pages written +buffer_page_written_trx_system buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Transaction System Pages written +buffer_page_written_fsp_hdr buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of File Space Header Pages written +buffer_page_written_xdes buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Extent Descriptor Pages written +buffer_page_written_blob buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Uncompressed BLOB Pages written +buffer_page_written_zblob buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of First Compressed BLOB Pages written +buffer_page_written_zblob2 buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Subsequent Compressed BLOB Pages written +buffer_page_written_other buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of other/unknown (old version InnoDB) Pages written +os_data_reads os 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of reads initiated (innodb_data_reads) +os_data_writes os 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of writes initiated (innodb_data_writes) +os_data_fsyncs os 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of fsync() calls (innodb_data_fsyncs) +os_pending_reads os 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of reads pending +os_pending_writes os 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of writes pending +os_log_bytes_written os 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Bytes of log written (innodb_os_log_written) +os_log_fsyncs os 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of fsync log writes (innodb_os_log_fsyncs) +os_log_pending_fsyncs os 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of pending fsync write (innodb_os_log_pending_fsyncs) +os_log_pending_writes os 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of pending log file writes (innodb_os_log_pending_writes) +trx_rw_commits transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of read-write transactions committed +trx_ro_commits transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of read-only transactions committed +trx_nl_ro_commits transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of non-locking auto-commit read-only transactions committed +trx_commits_insert_update transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of transactions committed with inserts and updates +trx_rollbacks transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of transactions rolled back +trx_rollbacks_savepoint transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of transactions rolled back to savepoint +trx_active_transactions transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of active transactions +trx_rseg_history_len transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Length of the TRX_RSEG_HISTORY list +trx_undo_slots_used transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of undo slots used +trx_undo_slots_cached transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of undo slots cached +trx_rseg_current_size transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Current rollback segment size in pages +purge_del_mark_records purge 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of delete-marked rows purged +purge_upd_exist_or_extern_records purge 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of purges on updates of existing records and updates on delete marked record with externally stored field +purge_invoked purge 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times purge was invoked +purge_undo_log_pages purge 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of undo log pages handled by the purge +purge_dml_delay_usec purge 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Microseconds DML to be delayed due to purge lagging +purge_stop_count purge 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Number of times purge was stopped +purge_resume_count purge 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Number of times purge was resumed +log_checkpoints recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of checkpoints +log_lsn_last_flush recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value LSN of Last flush +log_lsn_last_checkpoint recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value LSN at last checkpoint +log_lsn_current recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Current LSN value +log_lsn_checkpoint_age recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Current LSN value minus LSN at last checkpoint +log_lsn_buf_pool_oldest recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value The oldest modified block LSN in the buffer pool +log_max_modified_age_async recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Maximum LSN difference; when exceeded, start asynchronous preflush +log_max_modified_age_sync recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Maximum LSN difference; when exceeded, start synchronous preflush +log_pending_log_flushes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Pending log flushes +log_pending_checkpoint_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Pending checkpoints +log_num_log_io recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Number of log I/Os +log_waits recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log waits due to small log buffer (innodb_log_waits) +log_write_requests recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log write requests (innodb_log_write_requests) +log_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log writes (innodb_log_writes) +log_padded recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Bytes of log padded for log write ahead +compress_pages_compressed compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of pages compressed +compress_pages_decompressed compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of pages decompressed +compression_pad_increments compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times padding is incremented to avoid compression failures +compression_pad_decrements compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times padding is decremented due to good compressibility +compress_saved compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of bytes saved by page compression +compress_pages_page_compressed compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of pages compressed by page compression +compress_page_compressed_trim_op compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of TRIM operation performed by page compression +compress_pages_page_decompressed compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of pages decompressed by page compression +compress_pages_page_compression_error compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of page compression errors +compress_pages_encrypted compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of pages encrypted +compress_pages_decrypted compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of pages decrypted +index_page_splits index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index page splits +index_page_merge_attempts index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index page merge attempts +index_page_merge_successful index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of successful index page merges +index_page_reorg_attempts index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index page reorganization attempts +index_page_reorg_successful index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of successful index page reorganizations +index_page_discards index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index pages discarded +adaptive_hash_searches adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of successful searches using Adaptive Hash Index +adaptive_hash_searches_btree adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of searches using B-tree on an index search +adaptive_hash_pages_added adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index pages on which the Adaptive Hash Index is built +adaptive_hash_pages_removed adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of index pages whose corresponding Adaptive Hash Index entries were removed +adaptive_hash_rows_added adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Adaptive Hash Index rows added +adaptive_hash_rows_removed adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Adaptive Hash Index rows removed +adaptive_hash_rows_deleted_no_hash_entry adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of rows deleted that did not have corresponding Adaptive Hash Index entries +adaptive_hash_rows_updated adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of Adaptive Hash Index rows updated +file_num_open_files file_system 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Number of files currently open (innodb_num_open_files) +ibuf_merges_insert change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of inserted records merged by change buffering +ibuf_merges_delete_mark change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of deleted records merged by change buffering +ibuf_merges_delete change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of purge records merged by change buffering +ibuf_merges_discard_insert change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of insert merged operations discarded +ibuf_merges_discard_delete_mark change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of deleted merged operations discarded +ibuf_merges_discard_delete change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of purge merged operations discarded +ibuf_merges change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of change buffer merges +ibuf_size change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Change buffer size in pages +innodb_master_thread_sleeps server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times (seconds) master thread sleeps +innodb_activity_count server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Current server activity count +innodb_master_active_loops server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times master thread performs its tasks when server is active +innodb_master_idle_loops server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of times master thread performs its tasks when server is idle +innodb_background_drop_table_usec server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Time (in microseconds) spent to process drop table list +innodb_ibuf_merge_usec server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Time (in microseconds) spent to process change buffer merge +innodb_log_flush_usec server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Time (in microseconds) spent to flush log records +innodb_mem_validate_usec server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Time (in microseconds) spent to do memory validation +innodb_master_purge_usec server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Time (in microseconds) spent by master thread to purge records +innodb_dict_lru_usec server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Time (in microseconds) spent to process DICT LRU list +innodb_dict_lru_count_active server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of tables evicted from DICT LRU list in the active loop +innodb_dict_lru_count_idle server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of tables evicted from DICT LRU list in the idle loop +innodb_checkpoint_usec server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Time (in microseconds) spent by master thread to do checkpoint +innodb_dblwr_writes server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of doublewrite operations that have been performed (innodb_dblwr_writes) +innodb_dblwr_pages_written server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of pages that have been written for doublewrite operations (innodb_dblwr_pages_written) +innodb_page_size server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value InnoDB page size in bytes (innodb_page_size) +innodb_rwlock_s_spin_waits server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rwlock spin waits due to shared latch request +innodb_rwlock_x_spin_waits server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rwlock spin waits due to exclusive latch request +innodb_rwlock_sx_spin_waits server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rwlock spin waits due to sx latch request +innodb_rwlock_s_spin_rounds server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rwlock spin loop rounds due to shared latch request +innodb_rwlock_x_spin_rounds server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rwlock spin loop rounds due to exclusive latch request +innodb_rwlock_sx_spin_rounds server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rwlock spin loop rounds due to sx latch request +innodb_rwlock_s_os_waits server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of OS waits due to shared latch request +innodb_rwlock_x_os_waits server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of OS waits due to exclusive latch request +innodb_rwlock_sx_os_waits server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of OS waits due to sx latch request +dml_reads dml 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rows read +dml_inserts dml 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rows inserted +dml_deletes dml 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rows deleted +dml_updates dml 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of rows updated +dml_system_reads dml 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of system rows read +dml_system_inserts dml 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of system rows inserted +dml_system_deletes dml 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of system rows deleted +dml_system_updates dml 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of system rows updated +ddl_background_drop_indexes ddl 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of indexes waiting to be dropped after failed index creation +ddl_background_drop_tables ddl 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of tables in background drop table list +ddl_online_create_index ddl 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of indexes being created online +ddl_pending_alter_table ddl 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of ALTER TABLE, CREATE INDEX, DROP INDEX in progress +ddl_sort_file_alter_table ddl 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of sort files created during alter table +ddl_log_file_alter_table ddl 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of log files created during alter table +icp_attempts icp 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of attempts for index push-down condition checks +icp_no_match icp 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Index push-down condition does not match +icp_out_of_range icp 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Index push-down condition out of range +icp_match icp 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Index push-down condition matches +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD; +value +a +about +an +are +as +at +be +by +com +de +en +for +from +how +i +in +is +it +la +of +on +or +that +the +this +to +was +what +when +where +who +will +with +und +the +www +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +KEY VALUE +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS; +POOL_ID POOL_SIZE FREE_BUFFERS DATABASE_PAGES OLD_DATABASE_PAGES MODIFIED_DATABASE_PAGES PENDING_DECOMPRESS PENDING_READS PENDING_FLUSH_LRU PENDING_FLUSH_LIST PAGES_MADE_YOUNG PAGES_NOT_MADE_YOUNG PAGES_MADE_YOUNG_RATE PAGES_MADE_NOT_YOUNG_RATE NUMBER_PAGES_READ NUMBER_PAGES_CREATED NUMBER_PAGES_WRITTEN PAGES_READ_RATE PAGES_CREATE_RATE PAGES_WRITTEN_RATE NUMBER_PAGES_GET HIT_RATE YOUNG_MAKE_PER_THOUSAND_GETS NOT_YOUNG_MAKE_PER_THOUSAND_GETS NUMBER_PAGES_READ_AHEAD NUMBER_READ_AHEAD_EVICTED READ_AHEAD_RATE READ_AHEAD_EVICTED_RATE LRU_IO_TOTAL LRU_IO_CURRENT UNCOMPRESS_TOTAL UNCOMPRESS_CURRENT +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE; +POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE PAGE_STATE IO_FIX IS_OLD FREE_PAGE_CLOCK +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU; +POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE COMPRESSED IO_FIX IS_OLD FREE_PAGE_CLOCK +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; +TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS; +TABLE_ID NAME STATS_INITIALIZED NUM_ROWS CLUST_INDEX_SIZE OTHER_INDEX_SIZE MODIFIED_COUNTER AUTOINC REF_COUNT +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES; +INDEX_ID NAME TABLE_ID TYPE N_FIELDS PAGE_NO SPACE MERGE_THRESHOLD +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS; +TABLE_ID NAME POS MTYPE PRTYPE LEN +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS; +INDEX_ID NAME POS +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; +ID FOR_NAME REF_NAME N_COLS TYPE +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; +ID FOR_COL_NAME REF_COL_NAME POS +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES; +SPACE NAME FLAG ROW_FORMAT PAGE_SIZE ZIP_PAGE_SIZE SPACE_TYPE FS_BLOCK_SIZE FILE_SIZE ALLOCATED_SIZE +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES; +SPACE PATH diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues.result index d160b81f10e..84c93481c79 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues.result @@ -1,674 +1 @@ - ------------------------------------------------------------------------ -- Locking issues case 1.1: -- Locking rows that do not exist when using all primary key columns in -- a WHERE clause -- using REPEATABLE READ transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); -INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; -id1 id2 value -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -INSERT INTO t0 VALUES (1,5,0); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 1.1: -- Locking rows that do not exist when using all primary key columns in -- a WHERE clause -- using READ COMMITTED transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); -INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; -id1 id2 value -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -INSERT INTO t0 VALUES (1,5,0); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 1.2: -- Locking rows that do not exist without using all primary key -- columns in a WHERE clause -- using REPEATABLE READ transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); -INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -SELECT * FROM t0 WHERE id1=1 FOR UPDATE; -id1 id2 value -1 1 0 -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -SELECT * FROM t0 WHERE id1=1 AND id2=4 FOR UPDATE; -id1 id2 value -INSERT INTO t0 VALUES (1,5,0); -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 1.2: -- Locking rows that do not exist without using all primary key -- columns in a WHERE clause -- using READ COMMITTED transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); -INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -SELECT * FROM t0 WHERE id1=1 FOR UPDATE; -id1 id2 value -1 1 0 -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -SELECT * FROM t0 WHERE id1=1 AND id2=4 FOR UPDATE; -id1 id2 value -INSERT INTO t0 VALUES (1,5,0); -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 2: -- Rows that are scanned but do not match the WHERE are not locked -- using REPEATABLE READ transaction isolation level unless -- rocksdb_lock_scanned_rows is on ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -SELECT @@global.rocksdb_lock_scanned_rows; -@@global.rocksdb_lock_scanned_rows -0 -CREATE TABLE t0(id INT PRIMARY KEY, value INT); -INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -connection con1; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -id value -2 1 -5 1 -connection con2; -UPDATE t0 SET VALUE=10 WHERE id=1; -UPDATE t0 SET VALUE=10 WHERE id=5; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -connection con1; -UPDATE t0 SET value=100 WHERE id in (4,5) and value>0; -connection con2; -SELECT * FROM t0 WHERE id=4 FOR UPDATE; -id value -4 0 -COMMIT; -SELECT * FROM t0; -id value -1 10 -2 1 -3 0 -4 0 -5 1 -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 2: -- Rows that are scanned but do not match the WHERE are not locked -- using READ COMMITTED transaction isolation level unless -- rocksdb_lock_scanned_rows is on ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -SELECT @@global.rocksdb_lock_scanned_rows; -@@global.rocksdb_lock_scanned_rows -0 -CREATE TABLE t0(id INT PRIMARY KEY, value INT); -INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -connection con1; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -id value -2 1 -5 1 -connection con2; -UPDATE t0 SET VALUE=10 WHERE id=1; -UPDATE t0 SET VALUE=10 WHERE id=5; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -connection con1; -UPDATE t0 SET value=100 WHERE id in (4,5) and value>0; -connection con2; -SELECT * FROM t0 WHERE id=4 FOR UPDATE; -id value -4 0 -COMMIT; -SELECT * FROM t0; -id value -1 10 -2 1 -3 0 -4 0 -5 1 -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 2: -- Rows that are scanned but do not match the WHERE are not locked -- using REPEATABLE READ transaction isolation level unless -- rocksdb_lock_scanned_rows is on ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -SELECT @@global.rocksdb_lock_scanned_rows; -@@global.rocksdb_lock_scanned_rows -0 -SET GLOBAL rocksdb_lock_scanned_rows=ON; -CREATE TABLE t0(id INT PRIMARY KEY, value INT); -INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -connection con1; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -id value -2 1 -5 1 -connection con2; -UPDATE t0 SET VALUE=10 WHERE id=1; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; -SET GLOBAL rocksdb_lock_scanned_rows=0; - ------------------------------------------------------------------------ -- Locking issues case 2: -- Rows that are scanned but do not match the WHERE are not locked -- using READ COMMITTED transaction isolation level unless -- rocksdb_lock_scanned_rows is on ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -SELECT @@global.rocksdb_lock_scanned_rows; -@@global.rocksdb_lock_scanned_rows -0 -SET GLOBAL rocksdb_lock_scanned_rows=ON; -CREATE TABLE t0(id INT PRIMARY KEY, value INT); -INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -connection con1; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -id value -2 1 -5 1 -connection con2; -UPDATE t0 SET VALUE=10 WHERE id=1; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; -SET GLOBAL rocksdb_lock_scanned_rows=0; - ------------------------------------------------------------------------ -- Locking issues case 3: -- After creating a snapshot, other clients updating rows -- using REPEATABLE READ transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); -Inserting 200,000 rows -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -UPDATE t0 SET VALUE=VALUE+1 WHERE id=190000; -connection con1; -ERROR: 1213 -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 3: -- After creating a snapshot, other clients updating rows -- using READ COMMITTED transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); -Inserting 200,000 rows -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -UPDATE t0 SET VALUE=VALUE+1 WHERE id=190000; -connection con1; -id value -190000 1 -ERROR: 0 -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 4: -- Phantom rows -- using REPEATABLE READ transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); -Inserting 200,000 rows -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -INSERT INTO t0 VALUES(200001,1), (-1,1); -connection con1; -id value -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 4: -- Phantom rows -- using READ COMMITTED transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); -Inserting 200,000 rows -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -INSERT INTO t0 VALUES(200001,1), (-1,1); -connection con1; -id value -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 5: -- Deleting primary key -- using REPEATABLE READ transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); -Inserting 200,000 rows -UPDATE t0 SET value=100 WHERE id=190000; -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -DELETE FROM t0 WHERE id=190000; -COMMIT; -connection con1; -ERROR: 1213 -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 5: -- Deleting primary key -- using READ COMMITTED transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); -Inserting 200,000 rows -UPDATE t0 SET value=100 WHERE id=190000; -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -DELETE FROM t0 WHERE id=190000; -COMMIT; -connection con1; -id value -ERROR: 0 -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 6: -- Changing primary key -- using REPEATABLE READ transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); -Inserting 200,000 rows -UPDATE t0 SET value=100 WHERE id=190000; -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -UPDATE t0 SET id=200001 WHERE id=190000; -COMMIT; -connection con1; -ERROR: 1213 -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 6: -- Changing primary key -- using READ COMMITTED transaction isolation level ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t0; -CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); -Inserting 200,000 rows -UPDATE t0 SET value=100 WHERE id=190000; -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -SELECT * FROM t0 WHERE value > 0 FOR UPDATE; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -UPDATE t0 SET id=200001 WHERE id=190000; -COMMIT; -connection con1; -id value -ERROR: 0 -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t0; - ------------------------------------------------------------------------ -- Locking issues case 7: -- Rows that are scanned as part of a query but not in the table being -- updated should not be locked unless rocksdb_lock_scanned_rows is on ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t1, t2; -SELECT @@global.rocksdb_lock_scanned_rows; -@@global.rocksdb_lock_scanned_rows -0 -CREATE TABLE t1(id INT PRIMARY KEY, value INT); -CREATE TABLE t2(id INT PRIMARY KEY, value INT); -INSERT INTO t1 VALUES (1,1), (2,2), (3,3); -INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -lock_scanned_rows is 0 -connection con1; -UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; -connection con2; -UPDATE t2 SET value=value+100; -SELECT * FROM t2; -id value -1 101 -2 102 -3 103 -4 104 -5 105 -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t1; -DROP TABLE t2; - ------------------------------------------------------------------------ -- Locking issues case 7: -- Rows that are scanned as part of a query but not in the table being -- updated should not be locked unless rocksdb_lock_scanned_rows is on ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t1, t2; -SELECT @@global.rocksdb_lock_scanned_rows; -@@global.rocksdb_lock_scanned_rows -0 -CREATE TABLE t1(id INT PRIMARY KEY, value INT); -CREATE TABLE t2(id INT PRIMARY KEY, value INT); -INSERT INTO t1 VALUES (1,1), (2,2), (3,3); -INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -lock_scanned_rows is 0 -connection con1; -UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; -connection con2; -UPDATE t2 SET value=value+100; -SELECT * FROM t2; -id value -1 101 -2 102 -3 103 -4 104 -5 105 -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t1; -DROP TABLE t2; - ------------------------------------------------------------------------ -- Locking issues case 7: -- Rows that are scanned as part of a query but not in the table being -- updated should not be locked unless rocksdb_lock_scanned_rows is on ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t1, t2; -SELECT @@global.rocksdb_lock_scanned_rows; -@@global.rocksdb_lock_scanned_rows -0 -SET GLOBAL rocksdb_lock_scanned_rows=ON; -CREATE TABLE t1(id INT PRIMARY KEY, value INT); -CREATE TABLE t2(id INT PRIMARY KEY, value INT); -INSERT INTO t1 VALUES (1,1), (2,2), (3,3); -INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -BEGIN; -lock_scanned_rows is 1 -connection con1; -UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; -connection con2; -UPDATE t2 SET value=value+100 WHERE id=3; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -UPDATE t2 SET value=value+100 WHERE id IN (1,2,4,5); -SELECT * FROM t2; -id value -1 101 -2 102 -3 3 -4 104 -5 105 -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t1; -DROP TABLE t2; -SET GLOBAL rocksdb_lock_scanned_rows=0; - ------------------------------------------------------------------------ -- Locking issues case 7: -- Rows that are scanned as part of a query but not in the table being -- updated should not be locked unless rocksdb_lock_scanned_rows is on ------------------------------------------------------------------------ -DROP TABLE IF EXISTS t1, t2; -SELECT @@global.rocksdb_lock_scanned_rows; -@@global.rocksdb_lock_scanned_rows -0 -SET GLOBAL rocksdb_lock_scanned_rows=ON; -CREATE TABLE t1(id INT PRIMARY KEY, value INT); -CREATE TABLE t2(id INT PRIMARY KEY, value INT); -INSERT INTO t1 VALUES (1,1), (2,2), (3,3); -INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connection con1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -connection con2; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -BEGIN; -lock_scanned_rows is 1 -connection con1; -UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; -connection con2; -UPDATE t2 SET value=value+100 WHERE id=3; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -UPDATE t2 SET value=value+100 WHERE id IN (1,2,4,5); -SELECT * FROM t2; -id value -1 101 -2 102 -3 3 -4 104 -5 105 -connection con1; -COMMIT; -connection default; -disconnect con1; -disconnect con2; -DROP TABLE t1; -DROP TABLE t2; -SET GLOBAL rocksdb_lock_scanned_rows=0; +tests moved to rocksdb.locking_issues_case* diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_1_rc.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_1_rc.result new file mode 100644 index 00000000000..a47aa3c7d90 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_1_rc.result @@ -0,0 +1,30 @@ + +----------------------------------------------------------------------- +- Locking issues case 1.1: +- Locking rows that do not exist when using all primary key columns in +- a WHERE clause +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); +INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; +id1 id2 value +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +INSERT INTO t0 VALUES (1,5,0); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_1_rr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_1_rr.result new file mode 100644 index 00000000000..c923c34c98e --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_1_rr.result @@ -0,0 +1,30 @@ + +----------------------------------------------------------------------- +- Locking issues case 1.1: +- Locking rows that do not exist when using all primary key columns in +- a WHERE clause +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); +INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; +id1 id2 value +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +INSERT INTO t0 VALUES (1,5,0); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_2_rc.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_2_rc.result new file mode 100644 index 00000000000..01c4e7e3b2f --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_2_rc.result @@ -0,0 +1,30 @@ + +----------------------------------------------------------------------- +- Locking issues case 1.2: +- Locking rows that do not exist without using all primary key +- columns in a WHERE clause +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); +INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE id1=1 FOR UPDATE; +id1 id2 value +1 1 0 +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE id1=1 AND id2=4 FOR UPDATE; +id1 id2 value +INSERT INTO t0 VALUES (1,5,0); +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_2_rr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_2_rr.result new file mode 100644 index 00000000000..798fd15b76e --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case1_2_rr.result @@ -0,0 +1,30 @@ + +----------------------------------------------------------------------- +- Locking issues case 1.2: +- Locking rows that do not exist without using all primary key +- columns in a WHERE clause +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); +INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE id1=1 FOR UPDATE; +id1 id2 value +1 1 0 +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE id1=1 AND id2=4 FOR UPDATE; +id1 id2 value +INSERT INTO t0 VALUES (1,5,0); +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rc.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rc.result new file mode 100644 index 00000000000..16480da8e80 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rc.result @@ -0,0 +1,50 @@ + +----------------------------------------------------------------------- +- Locking issues case 2: +- Rows that are scanned but do not match the WHERE are not locked +- using READ COMMITTED transaction isolation level unless +- rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +CREATE TABLE t0(id INT PRIMARY KEY, value INT); +INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +connection con1; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +id value +2 1 +5 1 +connection con2; +UPDATE t0 SET VALUE=10 WHERE id=1; +UPDATE t0 SET VALUE=10 WHERE id=5; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection con1; +UPDATE t0 SET value=100 WHERE id in (4,5) and value>0; +connection con2; +SELECT * FROM t0 WHERE id=4 FOR UPDATE; +id value +4 0 +COMMIT; +SELECT * FROM t0; +id value +1 10 +2 1 +3 0 +4 0 +5 1 +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rc_lsr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rc_lsr.result new file mode 100644 index 00000000000..330cd09d33e --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rc_lsr.result @@ -0,0 +1,37 @@ + +----------------------------------------------------------------------- +- Locking issues case 2: +- Rows that are scanned but do not match the WHERE are not locked +- using READ COMMITTED transaction isolation level unless +- rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +SET GLOBAL rocksdb_lock_scanned_rows=ON; +CREATE TABLE t0(id INT PRIMARY KEY, value INT); +INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +connection con1; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +id value +2 1 +5 1 +connection con2; +UPDATE t0 SET VALUE=10 WHERE id=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; +SET GLOBAL rocksdb_lock_scanned_rows=0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rr.result new file mode 100644 index 00000000000..3e6b63afaa8 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rr.result @@ -0,0 +1,50 @@ + +----------------------------------------------------------------------- +- Locking issues case 2: +- Rows that are scanned but do not match the WHERE are not locked +- using REPEATABLE READ transaction isolation level unless +- rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +CREATE TABLE t0(id INT PRIMARY KEY, value INT); +INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +connection con1; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +id value +2 1 +5 1 +connection con2; +UPDATE t0 SET VALUE=10 WHERE id=1; +UPDATE t0 SET VALUE=10 WHERE id=5; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection con1; +UPDATE t0 SET value=100 WHERE id in (4,5) and value>0; +connection con2; +SELECT * FROM t0 WHERE id=4 FOR UPDATE; +id value +4 0 +COMMIT; +SELECT * FROM t0; +id value +1 10 +2 1 +3 0 +4 0 +5 1 +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rr_lsr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rr_lsr.result new file mode 100644 index 00000000000..088e975ebc5 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case2_rr_lsr.result @@ -0,0 +1,37 @@ + +----------------------------------------------------------------------- +- Locking issues case 2: +- Rows that are scanned but do not match the WHERE are not locked +- using REPEATABLE READ transaction isolation level unless +- rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +SET GLOBAL rocksdb_lock_scanned_rows=ON; +CREATE TABLE t0(id INT PRIMARY KEY, value INT); +INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +connection con1; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +id value +2 1 +5 1 +connection con2; +UPDATE t0 SET VALUE=10 WHERE id=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; +SET GLOBAL rocksdb_lock_scanned_rows=0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case3_rc.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case3_rc.result new file mode 100644 index 00000000000..9a6f02cd41d --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case3_rc.result @@ -0,0 +1,25 @@ + +----------------------------------------------------------------------- +- Locking issues case 3: +- After creating a snapshot, other clients updating rows +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +UPDATE t0 SET VALUE=VALUE+1 WHERE id=190000; +connection con1; +id value +190000 1 +ERROR: 0 +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case3_rr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case3_rr.result new file mode 100644 index 00000000000..fdd4d8fcaca --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case3_rr.result @@ -0,0 +1,23 @@ + +----------------------------------------------------------------------- +- Locking issues case 3: +- After creating a snapshot, other clients updating rows +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +UPDATE t0 SET VALUE=VALUE+1 WHERE id=190000; +connection con1; +ERROR: 1213 +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case4_rc.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case4_rc.result new file mode 100644 index 00000000000..1a35d276192 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case4_rc.result @@ -0,0 +1,23 @@ + +----------------------------------------------------------------------- +- Locking issues case 4: +- Phantom rows +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +INSERT INTO t0 VALUES(200001,1), (-1,1); +connection con1; +id value +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case4_rr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case4_rr.result new file mode 100644 index 00000000000..7ecb1ac5f7a --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case4_rr.result @@ -0,0 +1,23 @@ + +----------------------------------------------------------------------- +- Locking issues case 4: +- Phantom rows +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +INSERT INTO t0 VALUES(200001,1), (-1,1); +connection con1; +id value +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case5_rc.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case5_rc.result new file mode 100644 index 00000000000..4d707e83eec --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case5_rc.result @@ -0,0 +1,29 @@ + +----------------------------------------------------------------------- +- Locking issues case 5: +- Deleting primary key +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +UPDATE t0 SET value=100 WHERE id=190000; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +DELETE FROM t0 WHERE id=190000; +COMMIT; +connection con1; +id value +ERROR: 0 +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case5_rr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case5_rr.result new file mode 100644 index 00000000000..0ebfe8e6079 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case5_rr.result @@ -0,0 +1,28 @@ + +----------------------------------------------------------------------- +- Locking issues case 5: +- Deleting primary key +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +UPDATE t0 SET value=100 WHERE id=190000; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +DELETE FROM t0 WHERE id=190000; +COMMIT; +connection con1; +ERROR: 1213 +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case6_rc.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case6_rc.result new file mode 100644 index 00000000000..f16ffbeaa63 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case6_rc.result @@ -0,0 +1,29 @@ + +----------------------------------------------------------------------- +- Locking issues case 6: +- Changing primary key +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +UPDATE t0 SET value=100 WHERE id=190000; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +UPDATE t0 SET id=200001 WHERE id=190000; +COMMIT; +connection con1; +id value +ERROR: 0 +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case6_rr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case6_rr.result new file mode 100644 index 00000000000..c2323937d15 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case6_rr.result @@ -0,0 +1,28 @@ + +----------------------------------------------------------------------- +- Locking issues case 6: +- Changing primary key +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +UPDATE t0 SET value=100 WHERE id=190000; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +UPDATE t0 SET id=200001 WHERE id=190000; +COMMIT; +connection con1; +ERROR: 1213 +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rc.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rc.result new file mode 100644 index 00000000000..f30dbab045d --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rc.result @@ -0,0 +1,41 @@ + +----------------------------------------------------------------------- +- Locking issues case 7: +- Rows that are scanned as part of a query but not in the table being +- updated should not be locked unless rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t1, t2; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +CREATE TABLE t1(id INT PRIMARY KEY, value INT); +CREATE TABLE t2(id INT PRIMARY KEY, value INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3); +INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +lock_scanned_rows is 0 +connection con1; +UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; +connection con2; +UPDATE t2 SET value=value+100; +SELECT * FROM t2; +id value +1 101 +2 102 +3 103 +4 104 +5 105 +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t1; +DROP TABLE t2; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rc_lsr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rc_lsr.result new file mode 100644 index 00000000000..d43e3efbfe0 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rc_lsr.result @@ -0,0 +1,45 @@ + +----------------------------------------------------------------------- +- Locking issues case 7: +- Rows that are scanned as part of a query but not in the table being +- updated should not be locked unless rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t1, t2; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +SET GLOBAL rocksdb_lock_scanned_rows=ON; +CREATE TABLE t1(id INT PRIMARY KEY, value INT); +CREATE TABLE t2(id INT PRIMARY KEY, value INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3); +INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +lock_scanned_rows is 1 +connection con1; +UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; +connection con2; +UPDATE t2 SET value=value+100 WHERE id=3; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +UPDATE t2 SET value=value+100 WHERE id IN (1,2,4,5); +SELECT * FROM t2; +id value +1 101 +2 102 +3 3 +4 104 +5 105 +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t1; +DROP TABLE t2; +SET GLOBAL rocksdb_lock_scanned_rows=0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rr.result new file mode 100644 index 00000000000..dbcb0a03aef --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rr.result @@ -0,0 +1,41 @@ + +----------------------------------------------------------------------- +- Locking issues case 7: +- Rows that are scanned as part of a query but not in the table being +- updated should not be locked unless rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t1, t2; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +CREATE TABLE t1(id INT PRIMARY KEY, value INT); +CREATE TABLE t2(id INT PRIMARY KEY, value INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3); +INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +lock_scanned_rows is 0 +connection con1; +UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; +connection con2; +UPDATE t2 SET value=value+100; +SELECT * FROM t2; +id value +1 101 +2 102 +3 103 +4 104 +5 105 +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t1; +DROP TABLE t2; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rr_lsr.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rr_lsr.result new file mode 100644 index 00000000000..d9f7e333d3c --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues_case7_rr_lsr.result @@ -0,0 +1,45 @@ + +----------------------------------------------------------------------- +- Locking issues case 7: +- Rows that are scanned as part of a query but not in the table being +- updated should not be locked unless rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t1, t2; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +SET GLOBAL rocksdb_lock_scanned_rows=ON; +CREATE TABLE t1(id INT PRIMARY KEY, value INT); +CREATE TABLE t2(id INT PRIMARY KEY, value INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3); +INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +lock_scanned_rows is 1 +connection con1; +UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3; +connection con2; +UPDATE t2 SET value=value+100 WHERE id=3; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +UPDATE t2 SET value=value+100 WHERE id IN (1,2,4,5); +SELECT * FROM t2; +id value +1 101 +2 102 +3 3 +4 104 +5 105 +connection con1; +COMMIT; +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t1; +DROP TABLE t2; +SET GLOBAL rocksdb_lock_scanned_rows=0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/mariadb_plugin.result b/storage/rocksdb/mysql-test/rocksdb/r/mariadb_plugin.result index 6ab7ab003fd..6d6cb1db54e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/mariadb_plugin.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/mariadb_plugin.result @@ -2,14 +2,18 @@ # MDEV-14843: Assertion `s_tx_list.size() == 0' failed in myrocks::Rdb_transaction::term_mutex # INSTALL SONAME 'ha_rocksdb'; +connect con1,localhost,root,,test; CREATE TABLE t1 (i INT) ENGINE=RocksDB; insert into t1 values (1); -connect con1,localhost,root,,; -connection con1; -insert into test.t1 values (1); -connection default; DROP TABLE t1; +connection default; UNINSTALL SONAME 'ha_rocksdb'; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown +SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='ROCKSDB'; +ENGINE SUPPORT +ROCKSDB NO +disconnect con1; # # MDEV-15686: Loading MyRocks plugin back after it has been unloaded causes a crash # diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_float.result b/storage/rocksdb/mysql-test/rocksdb/r/type_float.result index 778ed95d338..371b550d4ab 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_float.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_float.result @@ -138,15 +138,15 @@ CONCAT('', MAX(d1_0)), CONCAT('', MAX(d10_10)), CONCAT('', MAX(d53)), CONCAT('', MAX(d53_10)) FROM t1; -CONCAT('', MAX(f)) 9.999999680285692e37 +CONCAT('', MAX(f)) 1e38 CONCAT('', MAX(d)) 1e81 CONCAT('', MAX(d10_10)) 0.9999999999 CONCAT('', MAX(d1_0)) 9 CONCAT('', MAX(d53)) 100000000000000000000000000000000000000000000000000000 CONCAT('', MAX(d53_10)) 10000000000000000000000000000000000000000000.0000000000 -CONCAT('', MAX(f0)) 9.999999680285692e37 +CONCAT('', MAX(f0)) 1e38 CONCAT('', MAX(f20_3)) 99999998430674940.000 -CONCAT('', MAX(f23_0)) 9.999999680285692e37 +CONCAT('', MAX(f23_0)) 1e38 CONCAT('', MAX(r1_1)) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 9999999999999999999999999999999999999999999999999999999999999.9999, |