Linux ip-172-26-2-223 5.4.0-1018-aws #18-Ubuntu SMP Wed Jun 24 01:15:00 UTC 2020 x86_64
Apache
: 172.26.2.223 | : 3.148.250.110
Cant Read [ /etc/named.conf ]
8.1.13
www
www.github.com/MadExploits
Terminal
AUTO ROOT
Adminer
Backdoor Destroyer
Linux Exploit
Lock Shell
Lock File
Create User
CREATE RDP
PHP Mailer
BACKCONNECT
UNLOCK SHELL
HASH IDENTIFIER
CPANEL RESET
CREATE WP USER
BLACK DEFEND!
README
+ Create Folder
+ Create File
/
www /
server /
mysql /
mysql-test /
suite /
innodb /
r /
[ HOME SHELL ]
Name
Size
Permission
Action
add_foreign_key.result
1.28
KB
-rw-r--r--
alter_crash.result
4.43
KB
-rw-r--r--
alter_foreign_crash.result
751
B
-rw-r--r--
alter_kill.result
2.42
KB
-rw-r--r--
alter_missing_tablespace.resul...
1.46
KB
-rw-r--r--
alter_page_size.result
1.14
KB
-rw-r--r--
alter_rename_existing.result
5.32
KB
-rw-r--r--
alter_rename_files.result
557
B
-rw-r--r--
alter_row_format.result
1.59
KB
-rw-r--r--
alter_table_redundant.result
278
B
-rw-r--r--
alter_table_stage_progress.res...
942
B
-rw-r--r--
analyze_index.result
705
B
-rw-r--r--
analyze_table.result
538
B
-rw-r--r--
attachable_trx.result
2.48
KB
-rw-r--r--
autoinc_debug.result
2.53
KB
-rw-r--r--
blob-crash-16k.result
2.1
KB
-rw-r--r--
blob-crash-4k.result
2.1
KB
-rw-r--r--
blob-crash.result
4.59
KB
-rw-r--r--
blob-update-debug.result
489
B
-rw-r--r--
blob_page_reserve.result
3.81
KB
-rw-r--r--
blob_redo.result
1.05
KB
-rw-r--r--
buf_pool_resize_oom.result
493
B
-rw-r--r--
bulk_create_index_online.resul...
1.21
KB
-rw-r--r--
cascade_lock_wait.result
1.52
KB
-rw-r--r--
check_ibd_filesize_16k.result
1.58
KB
-rw-r--r--
check_sector_size.result
276
B
-rw-r--r--
checksum.result
331
B
-rw-r--r--
cmp_per_index.result
2.96
KB
-rw-r--r--
crc32_endianness.result
540
B
-rw-r--r--
create-index.result
283
B
-rw-r--r--
create_isl_with_direct.result
328
B
-rw-r--r--
create_table.result
1.97
KB
-rw-r--r--
create_tablespace.result
81.15
KB
-rw-r--r--
create_tablespace_16k.result
69.23
KB
-rw-r--r--
create_tablespace_32k.result
7.33
KB
-rw-r--r--
create_tablespace_4k.result
36.13
KB
-rw-r--r--
create_tablespace_64k.result
7.64
KB
-rw-r--r--
create_tablespace_8k.result
50.53
KB
-rw-r--r--
create_tablespace_debug.result
5.02
KB
-rw-r--r--
create_tablespace_notembedded....
692
B
-rw-r--r--
create_tablespace_partition.re...
33.17
KB
-rw-r--r--
create_tablespace_replication....
2.02
KB
-rw-r--r--
deadlock_detect.result
562
B
-rw-r--r--
default_row_format.result
17.56
KB
-rw-r--r--
default_row_format_16k.result
17.91
KB
-rw-r--r--
default_row_format_compatibili...
4.14
KB
-rw-r--r--
default_row_format_tablespace....
8.64
KB
-rw-r--r--
deprecate_part_in_shared_ts.re...
5.87
KB
-rw-r--r--
doublewrite.result
8.1
KB
-rw-r--r--
dropdb.result
222
B
-rw-r--r--
end_range_check.result
3.06
KB
-rw-r--r--
end_range_check_2.result
811
B
-rw-r--r--
events-merge-tmp-path.result
1.33
KB
-rw-r--r--
file_format_defaults.result
2.84
KB
-rw-r--r--
file_format_upgrade_16k.result
420
B
-rw-r--r--
flush-hang.result
1.27
KB
-rw-r--r--
foreign_key.result
9.21
KB
-rw-r--r--
fts_exec_interrupt.result
723
B
-rw-r--r--
help_verbose.result
80
B
-rw-r--r--
high_prio_trx_1.result
505
B
-rw-r--r--
high_prio_trx_2.result
581
B
-rw-r--r--
high_prio_trx_3.result
665
B
-rw-r--r--
high_prio_trx_4.result
711
B
-rw-r--r--
high_prio_trx_5.result
858
B
-rw-r--r--
high_prio_trx_6.result
775
B
-rw-r--r--
high_prio_trx_7.result
5.67
KB
-rw-r--r--
high_prio_trx_commit_crash.res...
1
KB
-rw-r--r--
high_prio_trx_debug.result
1.09
KB
-rw-r--r--
high_prio_trx_fk.result
918
B
-rw-r--r--
high_prio_trx_predicate.result
824
B
-rw-r--r--
high_prio_trx_rpl.result
1.65
KB
-rw-r--r--
i_s_files_debug.result
471
B
-rw-r--r--
ibuf_not_empty.result
969
B
-rw-r--r--
import.result
1.72
KB
-rw-r--r--
import_compress_encrypt.result
5.28
KB
-rw-r--r--
import_compress_encrypt_upgrad...
2.53
KB
-rw-r--r--
import_export_4k.result
724
B
-rw-r--r--
import_tablespace_page_corrupt...
555
B
-rw-r--r--
import_tablespace_schema_missm...
2.11
KB
-rw-r--r--
import_update_stats.result
2.86
KB
-rw-r--r--
index-online-norebuild.result
897
B
-rw-r--r--
index_length.result
1.28
KB
-rw-r--r--
index_merge_threshold.result
54.7
KB
-rw-r--r--
index_tree_operation.result
1.65
KB
-rw-r--r--
innodb-2byte-collation.result
3.28
KB
-rw-r--r--
innodb-ac-non-locking-select.r...
861
B
-rw-r--r--
innodb-alter-autoinc.result
4.27
KB
-rw-r--r--
innodb-alter-debug-wl6554.resu...
12.79
KB
-rw-r--r--
innodb-alter-debug.result
2.64
KB
-rw-r--r--
innodb-alter-nullable.result
1.52
KB
-rw-r--r--
innodb-alter-wl6554.result
3.13
KB
-rw-r--r--
innodb-alter.result
30.33
KB
-rw-r--r--
innodb-analyze.result
58
B
-rw-r--r--
innodb-autoinc-18274.result
805
B
-rw-r--r--
innodb-autoinc-44030.result
732
B
-rw-r--r--
innodb-autoinc-56228.result
847
B
-rw-r--r--
innodb-autoinc-optimize.result
321
B
-rw-r--r--
innodb-autoinc.result
41.48
KB
-rw-r--r--
innodb-blob.result
4.91
KB
-rw-r--r--
innodb-bug-14068765.result
2.81
KB
-rw-r--r--
innodb-bug-14084530.result
701
B
-rw-r--r--
innodb-bug12552164.result
1.73
KB
-rw-r--r--
innodb-bug14219515.result
244
B
-rw-r--r--
innodb-change-buffer-recovery....
1.08
KB
-rw-r--r--
innodb-consistent.result
670
B
-rw-r--r--
innodb-import-partition-rpl.re...
2.46
KB
-rw-r--r--
innodb-import-partition.result
40.01
KB
-rw-r--r--
innodb-index-debug.result
13.64
KB
-rw-r--r--
innodb-index-online-delete.res...
642
B
-rw-r--r--
innodb-index-online-fk.result
26.4
KB
-rw-r--r--
innodb-index-online-purge.resu...
1.2
KB
-rw-r--r--
innodb-index-online.result
13.35
KB
-rw-r--r--
innodb-index.result
67.62
KB
-rw-r--r--
innodb-index_ucs2.result
3.62
KB
-rw-r--r--
innodb-isolation.result
43.25
KB
-rw-r--r--
innodb-large-prefix.result
1.27
KB
-rw-r--r--
innodb-lock-inherit-read_commi...
4.18
KB
-rw-r--r--
innodb-lock.result
3.22
KB
-rw-r--r--
innodb-multiple-tablespaces.re...
14.79
KB
-rw-r--r--
innodb-read-view.result
2.73
KB
-rw-r--r--
innodb-replace-debug.result
309
B
-rw-r--r--
innodb-semi-consistent.result
1.35
KB
-rw-r--r--
innodb-status-output.result
1.3
KB
-rw-r--r--
innodb-system-table-view.resul...
9.07
KB
-rw-r--r--
innodb-table-online.result
13.11
KB
-rw-r--r--
innodb-timeout.result
953
B
-rw-r--r--
innodb-truncate-debug.result
4.46
KB
-rw-r--r--
innodb-truncate.result
2.28
KB
-rw-r--r--
innodb-ucs2.result
15.38
KB
-rw-r--r--
innodb-update-insert.result
1.1
KB
-rw-r--r--
innodb-wl5522-1.result
29.45
KB
-rw-r--r--
innodb-wl5522-debug.result
30.05
KB
-rw-r--r--
innodb-wl5522.result
20.99
KB
-rw-r--r--
innodb-wl5980-alter.result
38.86
KB
-rw-r--r--
innodb-wl5980-debug.result
1.33
KB
-rw-r--r--
innodb-wl5980-discard.result
29.2
KB
-rw-r--r--
innodb-wl5980-linux.result
9.7
KB
-rw-r--r--
innodb-wl5980-windows.result
10.07
KB
-rw-r--r--
innodb-wl6045-1.result
3.72
KB
-rw-r--r--
innodb-wl6045-linux.result
11.11
KB
-rw-r--r--
innodb-wl6445-1.result
69
KB
-rw-r--r--
innodb-wl6445-2.result
3.57
KB
-rw-r--r--
innodb-wl6445.result
1.78
KB
-rw-r--r--
innodb.result
108.76
KB
-rw-r--r--
innodb_32k.result
3.25
KB
-rw-r--r--
innodb_64k.result
3.47
KB
-rw-r--r--
innodb_autoinc_lock_mode_zero....
1.22
KB
-rw-r--r--
innodb_autoinc_reset.result
423
B
-rw-r--r--
innodb_buffer_pool_dump_pct.re...
1.49
KB
-rw-r--r--
innodb_buffer_pool_load.result
2.02
KB
-rw-r--r--
innodb_buffer_pool_load_now.re...
1.6
KB
-rw-r--r--
innodb_buffer_pool_resize.resu...
1.29
KB
-rw-r--r--
innodb_buffer_pool_resize_debu...
3.24
KB
-rw-r--r--
innodb_buffer_pool_resize_with...
1.08
KB
-rw-r--r--
innodb_bug-13628249.result
988
B
-rw-r--r--
innodb_bug11754376.result
180
B
-rw-r--r--
innodb_bug11766634.result
396
B
-rw-r--r--
innodb_bug11789106.result
404
B
-rw-r--r--
innodb_bug11933790.result
135
B
-rw-r--r--
innodb_bug12400341.result
966
B
-rw-r--r--
innodb_bug12429573.result
708
B
-rw-r--r--
innodb_bug12661768.result
524
B
-rw-r--r--
innodb_bug14006907.result
934
B
-rw-r--r--
innodb_bug14007109.result
223
B
-rw-r--r--
innodb_bug14007649.result
1.17
KB
-rw-r--r--
innodb_bug14147491.result
730
B
-rw-r--r--
innodb_bug14169459.result
1.48
KB
-rw-r--r--
innodb_bug14676111.result
1.99
KB
-rw-r--r--
innodb_bug14704286.result
1.12
KB
-rw-r--r--
innodb_bug19164038.result
1023
B
-rw-r--r--
innodb_bug21704.result
2.65
KB
-rw-r--r--
innodb_bug30113362.result
2.95
KB
-rw-r--r--
innodb_bug30423.result
3.79
KB
-rw-r--r--
innodb_bug30594501.result
317
B
-rw-r--r--
innodb_bug30919.result
17.44
KB
-rw-r--r--
innodb_bug31205266.result
798
B
-rw-r--r--
innodb_bug34053.result
35
B
-rw-r--r--
innodb_bug34300.result
439
B
-rw-r--r--
innodb_bug35220.result
35
B
-rw-r--r--
innodb_bug38231.result
35
B
-rw-r--r--
innodb_bug39438.result
35
B
-rw-r--r--
innodb_bug40360.result
274
B
-rw-r--r--
innodb_bug40565.result
311
B
-rw-r--r--
innodb_bug41904.result
195
B
-rw-r--r--
innodb_bug42101-nonzero.result
810
B
-rw-r--r--
innodb_bug42101.result
704
B
-rw-r--r--
innodb_bug42419.result
858
B
-rw-r--r--
innodb_bug44032.result
313
B
-rw-r--r--
innodb_bug44369.result
300
B
-rw-r--r--
innodb_bug44571.result
334
B
-rw-r--r--
innodb_bug45357.result
290
B
-rw-r--r--
innodb_bug46000.result
906
B
-rw-r--r--
innodb_bug46676.result
362
B
-rw-r--r--
innodb_bug47167.result
1.51
KB
-rw-r--r--
innodb_bug47621.result
777
B
-rw-r--r--
innodb_bug47622.result
748
B
-rw-r--r--
innodb_bug47777.result
612
B
-rw-r--r--
innodb_bug48024.result
592
B
-rw-r--r--
innodb_bug49164.result
913
B
-rw-r--r--
innodb_bug51378.result
2.19
KB
-rw-r--r--
innodb_bug51920.result
316
B
-rw-r--r--
innodb_bug52199.result
175
B
-rw-r--r--
innodb_bug52663.result
857
B
-rw-r--r--
innodb_bug53046.result
1016
B
-rw-r--r--
innodb_bug53290.result
771
B
-rw-r--r--
innodb_bug53592.result
1.9
KB
-rw-r--r--
innodb_bug53674.result
234
B
-rw-r--r--
innodb_bug53756.result
3.62
KB
-rw-r--r--
innodb_bug54044.result
678
B
-rw-r--r--
innodb_bug56143.result
73.71
KB
-rw-r--r--
innodb_bug56716.result
168
B
-rw-r--r--
innodb_bug56947.result
406
B
-rw-r--r--
innodb_bug57252.result
89
B
-rw-r--r--
innodb_bug57255.result
501
B
-rw-r--r--
innodb_bug57904.result
1.3
KB
-rw-r--r--
innodb_bug59307.result
483
B
-rw-r--r--
innodb_bug59410.result
529
B
-rw-r--r--
innodb_bug59641.result
927
B
-rw-r--r--
innodb_bug59733.result
822
B
-rw-r--r--
innodb_bug60049.result
258
B
-rw-r--r--
innodb_bug60196.result
2.86
KB
-rw-r--r--
innodb_bug60229.result
955
B
-rw-r--r--
innodb_bug70867.result
126
B
-rw-r--r--
innodb_bulk_create_index.resul...
29.62
KB
-rw-r--r--
innodb_bulk_create_index_debug...
11.38
KB
-rw-r--r--
innodb_bulk_create_index_flush...
1.46
KB
-rw-r--r--
innodb_bulk_create_index_repli...
5.82
KB
-rw-r--r--
innodb_bulk_create_index_small...
4.26
KB
-rw-r--r--
innodb_corrupt_bit.result
2.61
KB
-rw-r--r--
innodb_corrupt_readonly.result
1011
B
-rw-r--r--
innodb_ctype_ldml.result
27.55
KB
-rw-r--r--
innodb_deadlock_with_autoinc.r...
565
B
-rw-r--r--
innodb_file_format.result
3.11
KB
-rw-r--r--
innodb_file_limit_check.result
457
B
-rw-r--r--
innodb_force_recovery.result
3.7
KB
-rw-r--r--
innodb_fts_limit_check.result
1.04
KB
-rw-r--r--
innodb_high_prio_commit_crash....
1.13
KB
-rw-r--r--
innodb_high_prio_rpl.result
1.07
KB
-rw-r--r--
innodb_i_s_innodb_locks.result
4.89
KB
-rw-r--r--
innodb_i_s_innodb_trx.result
2.85
KB
-rw-r--r--
innodb_information_schema_buff...
2.29
KB
-rw-r--r--
innodb_io_pf.result
407
B
-rw-r--r--
innodb_lock_wait_timeout_1.res...
9.04
KB
-rw-r--r--
innodb_misc1.result
29.29
KB
-rw-r--r--
innodb_multi_update.result
1.62
KB
-rw-r--r--
innodb_mysql.result
95.87
KB
-rw-r--r--
innodb_mysql_rbk.result
629
B
-rw-r--r--
innodb_notembedded.result
637
B
-rw-r--r--
innodb_page_size_func.result
1.49
KB
-rw-r--r--
innodb_pagesize_max_recordsize...
14.37
KB
-rw-r--r--
innodb_prefix_index_check.resu...
1.14
KB
-rw-r--r--
innodb_prefix_index_restart_se...
2.69
KB
-rw-r--r--
innodb_rename_index.result
31.12
KB
-rw-r--r--
innodb_rename_index_err.result
1.81
KB
-rw-r--r--
innodb_replace.result
2.7
KB
-rw-r--r--
innodb_row_log_read.result
867
B
-rw-r--r--
innodb_stats.result
11.62
KB
-rw-r--r--
innodb_stats_auto_recalc.resul...
1.33
KB
-rw-r--r--
innodb_stats_auto_recalc_ddl.r...
1.13
KB
-rw-r--r--
innodb_stats_auto_recalc_lots....
2.07
KB
-rw-r--r--
innodb_stats_auto_recalc_on_no...
2.19
KB
-rw-r--r--
innodb_stats_create_on_corrupt...
705
B
-rw-r--r--
innodb_stats_create_table.resu...
1.1
KB
-rw-r--r--
innodb_stats_del_mark.result
2.81
KB
-rw-r--r--
innodb_stats_drop_locked.resul...
1.55
KB
-rw-r--r--
innodb_stats_external_pages.re...
880
B
-rw-r--r--
innodb_stats_fetch.result
3.35
KB
-rw-r--r--
innodb_stats_fetch_corrupted.r...
1.01
KB
-rw-r--r--
innodb_stats_fetch_nonexistent...
980
B
-rw-r--r--
innodb_stats_flag_global_off.r...
7.36
KB
-rw-r--r--
innodb_stats_flag_global_on.re...
7.36
KB
-rw-r--r--
innodb_stats_rename_table.resu...
1.48
KB
-rw-r--r--
innodb_stats_rename_table_if_e...
1.89
KB
-rw-r--r--
innodb_stats_sample_pages.resu...
1023
B
-rw-r--r--
innodb_stats_table_flag_auto_r...
2.97
KB
-rw-r--r--
innodb_stats_table_flag_sample...
4.23
KB
-rw-r--r--
innodb_sys_var_valgrind.result
2.35
KB
-rw-r--r--
innodb_tablespace.result
22.76
KB
-rw-r--r--
innodb_tablespace_zip.result
1.13
KB
-rw-r--r--
innodb_thread_concurrency_debu...
824
B
-rw-r--r--
innodb_timeout_rollback.result
570
B
-rw-r--r--
innodb_trx_weight.result
35
B
-rw-r--r--
innodb_upd_stats_if_needed_not...
466
B
-rw-r--r--
innodb_ut_format_name.result
140
B
-rw-r--r--
innodb_wl6326.result
10.95
KB
-rw-r--r--
innodb_wl6326_big.result
17.67
KB
-rw-r--r--
innodb_wl6469_1.result
1.48
KB
-rw-r--r--
innodb_wl6469_debug.result
1.63
KB
-rw-r--r--
innodb_wl6470.result
3.11
KB
-rw-r--r--
innodb_wl6470_debug.result
1.42
KB
-rw-r--r--
innodb_wl6501_crash_1.result
15.37
KB
-rw-r--r--
innodb_wl6501_crash_2.result
15.37
KB
-rw-r--r--
innodb_wl6501_crash_6.result
13.07
KB
-rw-r--r--
innodb_wl6501_crash_7.result
13.07
KB
-rw-r--r--
innodb_wl6501_crash_8.result
468
B
-rw-r--r--
innodb_wl6560_1.result
7.27
KB
-rw-r--r--
innodb_wl6560_debug.result
3.36
KB
-rw-r--r--
innodb_wl6915.result
3.99
KB
-rw-r--r--
innodb_wl6915_debug.result
2.88
KB
-rw-r--r--
innodb_wl8114.result
1.78
KB
-rw-r--r--
insert_debug.result
956
B
-rw-r--r--
iodku.result
6.34
KB
-rw-r--r--
iodku_debug.result
2.71
KB
-rw-r--r--
log_alter_table.result
521
B
-rw-r--r--
log_corruption.result
843
B
-rw-r--r--
log_file.result
4.84
KB
-rw-r--r--
log_file_name.result
1.55
KB
-rw-r--r--
log_file_name_debug.result
345
B
-rw-r--r--
log_file_size.result
1.09
KB
-rw-r--r--
log_file_size_1.result
4.85
KB
-rw-r--r--
log_file_size_checkpoint.resul...
106
B
-rw-r--r--
missing_tablespaces.result
664
B
-rw-r--r--
monitor.result
23.64
KB
-rw-r--r--
monitor_restart.result
324
B
-rw-r--r--
mysqldump_max_recordsize.resul...
8.78
KB
-rw-r--r--
optimizer_temporary_table.resu...
114.72
KB
-rw-r--r--
page_reorganize.result
638
B
-rw-r--r--
partition-blob.result
1.53
KB
-rw-r--r--
partition-upgrade.result
33.24
KB
-rw-r--r--
partition.result
39.54
KB
-rw-r--r--
readahead.result
398
B
-rw-r--r--
readonly.result
713
B
-rw-r--r--
records_in_range.result
35.31
KB
-rw-r--r--
redo_log_during_checkpoint.res...
827
B
-rw-r--r--
rename_table.result
890
B
-rw-r--r--
row_format_redundant.result
1.71
KB
-rw-r--r--
sp_temp_table.result
5.72
KB
-rw-r--r--
stored_fk.result
3.52
KB
-rw-r--r--
strict_checksum.result
1.02
KB
-rw-r--r--
strict_mode.result
3.54
KB
-rw-r--r--
sys_tablestats.result
571
B
-rw-r--r--
table_compress.result
25.4
KB
-rw-r--r--
table_encrypt_1.result
6.82
KB
-rw-r--r--
table_encrypt_2.result
3.44
KB
-rw-r--r--
table_encrypt_3.result
66.48
KB
-rw-r--r--
table_encrypt_4.result
3.64
KB
-rw-r--r--
table_encrypt_5.result
56.6
KB
-rw-r--r--
table_encrypt_debug.result
3.22
KB
-rw-r--r--
table_encrypt_fts.result
1.7
KB
-rw-r--r--
table_encrypt_kill.result
7.59
KB
-rw-r--r--
table_encryption.result
3.42
KB
-rw-r--r--
tablespace_crash.result
8.16
KB
-rw-r--r--
tablespace_embedded.result
6.89
KB
-rw-r--r--
tablespace_embedded_windows.re...
6.93
KB
-rw-r--r--
tablespace_per_table.result
21.6
KB
-rw-r--r--
tablespace_per_table_not_windo...
9.11
KB
-rw-r--r--
tablespace_per_table_windows.r...
3.12
KB
-rw-r--r--
tablespace_portability.result
24.68
KB
-rw-r--r--
tablespace_portability_windows...
24.41
KB
-rw-r--r--
timestamp.result
790
B
-rw-r--r--
tinytext-groupby.result
1.33
KB
-rw-r--r--
tmpdir.result
1.8
KB
-rw-r--r--
trx_id_future.result
329
B
-rw-r--r--
undo.result
850
B
-rw-r--r--
update-cascade.result
8.04
KB
-rw-r--r--
update_time.result
1.62
KB
-rw-r--r--
update_time_wl6658.result
8.08
KB
-rw-r--r--
virtual_basic.result
53.55
KB
-rw-r--r--
virtual_blob.result
359
B
-rw-r--r--
virtual_debug.result
53.92
KB
-rw-r--r--
virtual_debug_purge.result
3.98
KB
-rw-r--r--
virtual_fk.result
28.34
KB
-rw-r--r--
virtual_fk_restart.result
1.48
KB
-rw-r--r--
virtual_index.result
7.71
KB
-rw-r--r--
virtual_purge.result
3.95
KB
-rw-r--r--
virtual_stats.result
4.84
KB
-rw-r--r--
xa_prepare_lock_release.result
1.21
KB
-rw-r--r--
xa_recovery.result
381
B
-rw-r--r--
xa_recovery_debug.result
528
B
-rw-r--r--
Delete
Unzip
Zip
${this.title}
Close
Code Editor : innodb-isolation.result
# # WL#6742 - Test the interaction of multiple transactions using # different isolation levels to make sure that the value returned # by count(*) always reflects the correct view of the table according # to the transaction's selected isolation level. # # # Traverse various indexes to get the right counts. # This especially tests count(*) which is pushed down to InnoDB in WL#6742. # CREATE TABLE t1 ( c1 INT AUTO_INCREMENT PRIMARY KEY, c2 INT, c3 INT, c4 INT, INDEX k2(c2) ) Engine=InnoDB; INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); CREATE TABLE t2 LIKE t1; INSERT INTO t2 (SELECT * FROM t1); affected rows: 10 info: Records: 10 Duplicates: 0 Warnings: 0 CREATE TABLE t3 ( c1 INT AUTO_INCREMENT PRIMARY KEY, c2 INT, c3 INT, c4 INT, INDEX k2(c2) ) Engine=InnoDB PARTITION BY HASH(c1) PARTITIONS 4; INSERT INTO t3 (SELECT * FROM t1); affected rows: 10 info: Records: 10 Duplicates: 0 Warnings: 0 ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 1 1 1 3 1 1 1 4 1 1 1 5 1 1 1 6 1 1 1 7 1 1 1 8 1 1 1 9 1 1 1 10 1 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 55 SELECT SUM(c2) FROM t1; SUM(c2) 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.5000 10 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.5000 10 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 7 1 8 1 9 1 10 1 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 7 1 8 1 9 1 10 1 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 5 ############################################### # # Do some DML in the default connection and leave the transaction pending. # SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; UPDATE t1 SET c2 = c2 * 3 WHERE c1 = 1; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 UPDATE t3 SET c2 = c2 * 3 WHERE c1 = 1; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 DELETE FROM t1 WHERE c1 = 6; affected rows: 1 DELETE FROM t3 WHERE c1 = 6; affected rows: 1 ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 1 2 1 1 1 3 1 1 1 4 1 1 1 5 1 1 1 7 1 1 1 8 1 1 1 9 1 1 1 10 1 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 9 SELECT COUNT(*) FROM t3; COUNT(*) 9 SELECT COUNT(c1) FROM t1; COUNT(c1) 9 SELECT COUNT(c2) FROM t1; COUNT(c2) 9 SELECT COUNT(c3) FROM t1; COUNT(c3) 9 SELECT SUM(c1) FROM t1; SUM(c1) 49 SELECT SUM(c2) FROM t1; SUM(c2) 11 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.4444 10 1 1.0000 9 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.4444 10 1 1.0000 9 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 5 1 7 1 8 1 9 1 10 1 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 5 1 7 1 8 1 9 1 10 1 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### # # Start transactions of Repeatable Read, Read Committed, and Read Uncommitted # # Connection 1 REPEATABLE READ # SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 1 1 1 3 1 1 1 4 1 1 1 5 1 1 1 6 1 1 1 7 1 1 1 8 1 1 1 9 1 1 1 10 1 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 55 SELECT SUM(c2) FROM t1; SUM(c2) 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.5000 10 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.5000 10 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 7 1 8 1 9 1 10 1 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 7 1 8 1 9 1 10 1 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 5 ############################################### UPDATE t1 SET c2 = c2 * 5 WHERE c1 = 2; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 UPDATE t3 SET c2 = c2 * 5 WHERE c1 = 2; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 DELETE FROM t1 WHERE c1 = 7; affected rows: 1 DELETE FROM t3 WHERE c1 = 7; affected rows: 1 INSERT INTO t1(c2,c3,c4) VALUES (100, 1, 1); affected rows: 1 INSERT INTO t3(c2,c3,c4) VALUES (100, 1, 1); affected rows: 1 ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 5 1 1 3 1 1 1 4 1 1 1 5 1 1 1 6 1 1 1 8 1 1 1 9 1 1 1 10 1 1 1 11 100 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 59 SELECT SUM(c2) FROM t1; SUM(c2) 113 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 11 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 11 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 8 1 9 1 10 1 11 100 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 8 1 9 1 10 1 11 100 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 5 ############################################### # # Test a lock wait timeout during COUNT(*) # SET innodb_lock_wait_timeout = 1; SELECT COUNT(*) FROM t1 FOR UPDATE; ERROR HY000: Lock wait timeout exceeded; try restarting transaction # # Connection 2 READ COMMITTED # SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 1 1 1 3 1 1 1 4 1 1 1 5 1 1 1 6 1 1 1 7 1 1 1 8 1 1 1 9 1 1 1 10 1 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 55 SELECT SUM(c2) FROM t1; SUM(c2) 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.5000 10 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.5000 10 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 7 1 8 1 9 1 10 1 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 7 1 8 1 9 1 10 1 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 5 ############################################### UPDATE t1 SET c2 = c2 * 7 WHERE c1 = 3; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 DELETE FROM t1 WHERE c1 = 8; affected rows: 1 DELETE FROM t3 WHERE c1 = 8; affected rows: 1 INSERT INTO t1(c2,c3,c4) VALUES (1000, 1, 1); affected rows: 1 INSERT INTO t3(c2,c3,c4) VALUES (1000, 1, 1); affected rows: 1 ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 1 1 1 3 7 1 1 4 1 1 1 5 1 1 1 6 1 1 1 7 1 1 1 9 1 1 1 10 1 1 1 12 1000 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 59 SELECT SUM(c2) FROM t1; SUM(c2) 1015 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 12 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 12 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 7 1 9 1 10 1 12 1000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 7 1 9 1 10 1 12 1000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 5 ############################################### # # Connection 3 READ UNCOMMITTED # SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; BEGIN; ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 1 2 5 1 1 3 7 1 1 4 1 1 1 5 1 1 1 9 1 1 1 10 1 1 1 11 100 1 1 12 1000 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 9 SELECT COUNT(*) FROM t3; COUNT(*) 9 SELECT COUNT(c1) FROM t1; COUNT(c1) 9 SELECT COUNT(c2) FROM t1; COUNT(c2) 9 SELECT COUNT(c3) FROM t1; COUNT(c3) 9 SELECT SUM(c1) FROM t1; SUM(c1) 57 SELECT SUM(c2) FROM t1; SUM(c2) 1119 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.3333 12 1 1.0000 9 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.3333 12 1 1.0000 9 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 5 1 9 1 10 1 11 100 12 1000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 5 1 9 1 10 1 11 100 12 1000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### UPDATE t1 SET c2 = c2 * 11 WHERE c1 = 4; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 UPDATE t3 SET c2 = c2 * 11 WHERE c1 = 4; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 INSERT INTO t1(c2,c3,c4) VALUES (10000, 1, 1); affected rows: 1 INSERT INTO t3(c2,c3,c4) VALUES (10000, 1, 1); affected rows: 1 DELETE FROM t1 WHERE c1 in(9); affected rows: 1 DELETE FROM t3 WHERE c1 in(9); affected rows: 1 ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 1 2 5 1 1 3 7 1 1 4 11 1 1 5 1 1 1 10 1 1 1 11 100 1 1 12 1000 1 1 13 10000 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 9 SELECT COUNT(*) FROM t3; COUNT(*) 9 SELECT COUNT(c1) FROM t1; COUNT(c1) 9 SELECT COUNT(c2) FROM t1; COUNT(c2) 9 SELECT COUNT(c3) FROM t1; COUNT(c3) 9 SELECT SUM(c1) FROM t1; SUM(c1) 61 SELECT SUM(c2) FROM t1; SUM(c2) 11128 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 9 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 9 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### # # Connection default REPEATABLE READ # ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 1 2 1 1 1 3 1 1 1 4 1 1 1 5 1 1 1 7 1 1 1 8 1 1 1 9 1 1 1 10 1 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 9 SELECT COUNT(*) FROM t3; COUNT(*) 9 SELECT COUNT(c1) FROM t1; COUNT(c1) 9 SELECT COUNT(c2) FROM t1; COUNT(c2) 9 SELECT COUNT(c3) FROM t1; COUNT(c3) 9 SELECT SUM(c1) FROM t1; SUM(c1) 49 SELECT SUM(c2) FROM t1; SUM(c2) 11 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.4444 10 1 1.0000 9 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.4444 10 1 1.0000 9 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 5 1 7 1 8 1 9 1 10 1 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 5 1 7 1 8 1 9 1 10 1 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### # # Commit the 3 extra connections # # Connection 1 REPEATABLE READ # ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 5 1 1 3 1 1 1 4 1 1 1 5 1 1 1 6 1 1 1 8 1 1 1 9 1 1 1 10 1 1 1 11 100 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 59 SELECT SUM(c2) FROM t1; SUM(c2) 113 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 11 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 11 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 8 1 9 1 10 1 11 100 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 8 1 9 1 10 1 11 100 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 5 ############################################### # # Connection 2 READ COMMITTED # ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 1 1 1 3 7 1 1 4 1 1 1 5 1 1 1 6 1 1 1 7 1 1 1 9 1 1 1 10 1 1 1 12 1000 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 59 SELECT SUM(c2) FROM t1; SUM(c2) 1015 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 12 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 12 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 7 1 9 1 10 1 12 1000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 7 1 9 1 10 1 12 1000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 5 ############################################### # # Connection 3 READ UNCOMMITTED # ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 1 2 5 1 1 3 7 1 1 4 11 1 1 5 1 1 1 10 1 1 1 11 100 1 1 12 1000 1 1 13 10000 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 9 SELECT COUNT(*) FROM t3; COUNT(*) 9 SELECT COUNT(c1) FROM t1; COUNT(c1) 9 SELECT COUNT(c2) FROM t1; COUNT(c2) 9 SELECT COUNT(c3) FROM t1; COUNT(c3) 9 SELECT SUM(c1) FROM t1; SUM(c1) 61 SELECT SUM(c2) FROM t1; SUM(c2) 11128 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 9 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 9 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### COMMIT; ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 1 2 5 1 1 3 7 1 1 4 11 1 1 5 1 1 1 10 1 1 1 11 100 1 1 12 1000 1 1 13 10000 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 9 SELECT COUNT(*) FROM t3; COUNT(*) 9 SELECT COUNT(c1) FROM t1; COUNT(c1) 9 SELECT COUNT(c2) FROM t1; COUNT(c2) 9 SELECT COUNT(c3) FROM t1; COUNT(c3) 9 SELECT SUM(c1) FROM t1; SUM(c1) 61 SELECT SUM(c2) FROM t1; SUM(c2) 11128 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 9 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 9 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### # # Connection 2 READ COMMITTED # ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 1 1 1 3 7 1 1 4 11 1 1 5 1 1 1 6 1 1 1 7 1 1 1 10 1 1 1 12 1000 1 1 13 10000 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 63 SELECT SUM(c2) FROM t1; SUM(c2) 11024 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.3000 13 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.3000 13 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 7 1 10 1 12 1000 13 10000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 7 1 10 1 12 1000 13 10000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### COMMIT; ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 1 1 1 3 7 1 1 4 11 1 1 5 1 1 1 6 1 1 1 7 1 1 1 10 1 1 1 12 1000 1 1 13 10000 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 63 SELECT SUM(c2) FROM t1; SUM(c2) 11024 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.3000 13 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.3000 13 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 7 1 10 1 12 1000 13 10000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 7 1 10 1 12 1000 13 10000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### # # Connection 1 REPEATABLE READ # ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 5 1 1 3 1 1 1 4 1 1 1 5 1 1 1 6 1 1 1 8 1 1 1 9 1 1 1 10 1 1 1 11 100 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 59 SELECT SUM(c2) FROM t1; SUM(c2) 113 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 11 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.9000 11 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 8 1 9 1 10 1 11 100 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 8 1 9 1 10 1 11 100 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 5 ############################################### # # Select the first 5 records FOR UPDATE using count(*) in a subquery. # The second record is still pending so we get a lock timeout. # SET innodb_lock_wait_timeout = 1; SELECT c1, c2 FROM t1 WHERE c1 < ((SELECT COUNT(*) FROM t1) / 2) FOR UPDATE; ERROR HY000: Lock wait timeout exceeded; try restarting transaction SELECT COUNT(*) FROM t1 FOR UPDATE; ERROR HY000: Lock wait timeout exceeded; try restarting transaction COMMIT; ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 1 1 1 2 5 1 1 3 7 1 1 4 11 1 1 5 1 1 1 6 1 1 1 10 1 1 1 11 100 1 1 12 1000 1 1 13 10000 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 10 SELECT COUNT(*) FROM t3; COUNT(*) 10 SELECT COUNT(c1) FROM t1; COUNT(c1) 10 SELECT COUNT(c2) FROM t1; COUNT(c2) 10 SELECT COUNT(c3) FROM t1; COUNT(c3) 10 SELECT SUM(c1) FROM t1; SUM(c1) 67 SELECT SUM(c2) FROM t1; SUM(c2) 11127 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7000 13 1 1.0000 10 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7000 13 1 1.0000 10 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 6 1 10 1 11 100 12 1000 13 10000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 6 1 10 1 11 100 12 1000 13 10000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### # # Show The EXPLAIN output for these queries; # ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK ANALYZE TABLE t2; Table Op Msg_type Msg_text test.t2 analyze status OK ANALYZE TABLE t3; Table Op Msg_type Msg_text test.t3 analyze status OK EXPLAIN SELECT * FROM t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1` EXPLAIN SELECT COUNT(*) FROM t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` EXPLAIN SELECT COUNT(*) FROM t3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t3` EXPLAIN SELECT COUNT(c1) FROM t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(`test`.`t1`.`c1`) AS `COUNT(c1)` from `test`.`t1` EXPLAIN SELECT COUNT(c2) FROM t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(`test`.`t1`.`c2`) AS `COUNT(c2)` from `test`.`t1` EXPLAIN SELECT COUNT(c3) FROM t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select count(`test`.`t1`.`c3`) AS `COUNT(c3)` from `test`.`t1` EXPLAIN SELECT SUM(c1) FROM t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select sum(`test`.`t1`.`c1`) AS `SUM(c1)` from `test`.`t1` EXPLAIN SELECT SUM(c2) FROM t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select sum(`test`.`t1`.`c2`) AS `SUM(c2)` from `test`.`t1` EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select avg(`test`.`t1`.`c1`) AS `AVG(c1)`,max(`test`.`t1`.`c1`) AS `MAX(c1)`,min(`test`.`t1`.`c2`) AS `MIN(c2)`,avg(`test`.`t1`.`c3`) AS `AVG(c3)`,sum(`test`.`t1`.`c4`) AS `SUM(c4)` from `test`.`t1` EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 p0,p1,p2,p3 ALL NULL NULL NULL NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select avg(`test`.`t3`.`c1`) AS `AVG(c1)`,max(`test`.`t3`.`c1`) AS `MAX(c1)`,min(`test`.`t3`.`c2`) AS `MIN(c2)`,avg(`test`.`t3`.`c3`) AS `AVG(c3)`,sum(`test`.`t3`.`c4`) AS `SUM(c4)` from `test`.`t3` EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where 2 SUBQUERY t1 NULL index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` > <cache>(((/* select#2 */ select count(0) from `test`.`t1`) / 2))) EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where 2 SUBQUERY t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` > <cache>(((/* select#2 */ select count(0) from `test`.`t3`) / 2))) EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where 2 SUBQUERY t1 NULL index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(`test`.`t1`.`c2`) AS `COUNT(c2)` from `test`.`t1` where (`test`.`t1`.`c1` > <cache>(((/* select#2 */ select count(0) from `test`.`t1`) / 2))) EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where 2 SUBQUERY t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(`test`.`t1`.`c2`) AS `COUNT(c2)` from `test`.`t1` where (`test`.`t1`.`c1` > <cache>(((/* select#2 */ select count(0) from `test`.`t3`) / 2))) EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where; Using index 2 SUBQUERY t1 NULL index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`c1` > (/* select#2 */ select avg(`test`.`t1`.`c1`) from `test`.`t1`)) EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where; Using index 2 SUBQUERY t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`c1` > (/* select#2 */ select avg(`test`.`t3`.`c1`) from `test`.`t3`)) # # Make all indexes in t2 obsolete to the active repeatable read transaction # in the default connection. # ALTER TABLE t2 row_format=redundant; # # Connection default REPEATABLE READ # Do more DML in the default REPEATABLE READ transaction in order to use recently committed records. # ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 1 2 1 1 1 3 1 1 1 4 1 1 1 5 1 1 1 7 1 1 1 8 1 1 1 9 1 1 1 10 1 1 1 SELECT COUNT(*) FROM t1; COUNT(*) 9 SELECT COUNT(*) FROM t3; COUNT(*) 9 SELECT COUNT(c1) FROM t1; COUNT(c1) 9 SELECT COUNT(c2) FROM t1; COUNT(c2) 9 SELECT COUNT(c3) FROM t1; COUNT(c3) 9 SELECT SUM(c1) FROM t1; SUM(c1) 49 SELECT SUM(c2) FROM t1; SUM(c2) 11 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.4444 10 1 1.0000 9 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5.4444 10 1 1.0000 9 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 5 1 7 1 8 1 9 1 10 1 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 5 1 7 1 8 1 9 1 10 1 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### UPDATE t1 SET c4 = c2 * 10; affected rows: 9 info: Rows matched: 9 Changed: 9 Warnings: 0 UPDATE t3 SET c4 = c2 * 10; affected rows: 9 info: Rows matched: 9 Changed: 9 Warnings: 0 ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 30 2 5 1 50 3 7 1 70 4 11 1 110 5 1 1 10 7 1 1 1 8 1 1 1 9 1 1 1 10 1 1 10 11 100 1 1000 12 1000 1 10000 13 10000 1 100000 SELECT COUNT(*) FROM t1; COUNT(*) 12 SELECT COUNT(*) FROM t3; COUNT(*) 12 SELECT COUNT(c1) FROM t1; COUNT(c1) 12 SELECT COUNT(c2) FROM t1; COUNT(c2) 12 SELECT COUNT(c3) FROM t1; COUNT(c3) 12 SELECT SUM(c1) FROM t1; SUM(c1) 85 SELECT SUM(c2) FROM t1; SUM(c2) 11131 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 7.0833 13 1 1.0000 111283 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 7.0833 13 1 1.0000 111223 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 7 1 8 1 9 1 10 1 11 100 12 1000 13 10000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 7 1 8 1 9 1 10 1 11 100 12 1000 13 10000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 7 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 7 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 6 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 6 ############################################### # # Table t2 has been altered to a new row format. # The index should not be useable. # SELECT COUNT(*) FROM t2; ERROR HY000: Table definition has changed, please retry transaction SELECT * FROM t2; ERROR HY000: Table definition has changed, please retry transaction COMMIT; SELECT COUNT(*) FROM t2; COUNT(*) 10 ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 30 2 5 1 50 3 7 1 70 4 11 1 110 5 1 1 10 10 1 1 10 11 100 1 1000 12 1000 1 10000 13 10000 1 100000 SELECT COUNT(*) FROM t1; COUNT(*) 9 SELECT COUNT(*) FROM t3; COUNT(*) 9 SELECT COUNT(c1) FROM t1; COUNT(c1) 9 SELECT COUNT(c2) FROM t1; COUNT(c2) 9 SELECT COUNT(c3) FROM t1; COUNT(c3) 9 SELECT SUM(c1) FROM t1; SUM(c1) 61 SELECT SUM(c2) FROM t1; SUM(c2) 11128 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 111280 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 111220 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### # # Connection 2 # ########## innodb_isolation_selects.inc ########## SELECT * FROM t1; c1 c2 c3 c4 1 3 1 30 2 5 1 50 3 7 1 70 4 11 1 110 5 1 1 10 10 1 1 10 11 100 1 1000 12 1000 1 10000 13 10000 1 100000 SELECT COUNT(*) FROM t1; COUNT(*) 9 SELECT COUNT(*) FROM t3; COUNT(*) 9 SELECT COUNT(c1) FROM t1; COUNT(c1) 9 SELECT COUNT(c2) FROM t1; COUNT(c2) 9 SELECT COUNT(c3) FROM t1; COUNT(c3) 9 SELECT SUM(c1) FROM t1; SUM(c1) 61 SELECT SUM(c2) FROM t1; SUM(c2) 11128 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 111280 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6.7778 13 1 1.0000 111220 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); c1 c2 5 1 10 1 11 100 12 1000 13 10000 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); COUNT(c2) 5 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); COUNT(c2) 5 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); COUNT(*) 4 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); COUNT(*) 4 ############################################### # # Try COUNT(*) on a DISCARDED table. # CREATE TABLE t4 LIKE t1; INSERT INTO t4 (SELECT * FROM t1); SELECT COUNT(*) FROM t4; COUNT(*) 9 ALTER TABLE t4 DISCARD TABLESPACE; SELECT COUNT(*) FROM t4; ERROR HY000: Tablespace has been discarded for table 't4' # # Test the interaction of a repeatable read transaction # to changes that happen outside its view. # CREATE TABLE t5 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; INSERT INTO t5(b) VALUES ("inserted by client 1"); INSERT INTO t5(b) VALUES ("inserted by client 1"); INSERT INTO t5(b) VALUES ("inserted by client 1"); INSERT INTO t5(b) VALUES ("inserted by client 1"); UPDATE t5 SET aa=a, bb=b; CREATE TABLE t6 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; INSERT INTO t6(b) VALUES ("inserted by client 1"); INSERT INTO t6(b) VALUES ("inserted by client 1"); INSERT INTO t6(b) VALUES ("inserted by client 1"); INSERT INTO t6(b) VALUES ("inserted by client 1"); UPDATE t6 SET aa=a, bb=b; CREATE TABLE t7 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; INSERT INTO t7(b) VALUES ("inserted by client 1"); INSERT INTO t7(b) VALUES ("inserted by client 1"); INSERT INTO t7(b) VALUES ("inserted by client 1"); INSERT INTO t7(b) VALUES ("inserted by client 1"); UPDATE t7 SET aa=a, bb=b; BEGIN; SELECT * FROM t5; a b aa bb 1 inserted by client 1 1 inserted by client 1 2 inserted by client 1 2 inserted by client 1 3 inserted by client 1 3 inserted by client 1 4 inserted by client 1 4 inserted by client 1 SELECT COUNT(*) FROM t5; COUNT(*) 4 SELECT * FROM t6; a b aa bb 1 inserted by client 1 1 inserted by client 1 2 inserted by client 1 2 inserted by client 1 3 inserted by client 1 3 inserted by client 1 4 inserted by client 1 4 inserted by client 1 SELECT COUNT(*) FROM t6; COUNT(*) 4 SELECT * FROM t7; a b aa bb 1 inserted by client 1 1 inserted by client 1 2 inserted by client 1 2 inserted by client 1 3 inserted by client 1 3 inserted by client 1 4 inserted by client 1 4 inserted by client 1 SELECT COUNT(*) FROM t7; COUNT(*) 4 # # Connection 1 # INSERT INTO t5(b) VALUES ("inserted by client 2"); UPDATE t5 SET a = 10 where a = 1; UPDATE t5 SET b = "updated by client 2" where a = 2; DELETE FROM t5 WHERE a = 3; SELECT * FROM t5; a b aa bb 2 updated by client 2 2 inserted by client 1 4 inserted by client 1 4 inserted by client 1 5 inserted by client 2 NULL NULL 10 inserted by client 1 1 inserted by client 1 SELECT COUNT(*) FROM t5; COUNT(*) 4 INSERT INTO t6(b) VALUES ("inserted by client 2"); UPDATE t6 SET a = 10 where a = 1; UPDATE t6 SET b = "updated by client 2" where a = 2; DELETE FROM t6 WHERE a = 3; SELECT * FROM t6; a b aa bb 2 updated by client 2 2 inserted by client 1 4 inserted by client 1 4 inserted by client 1 5 inserted by client 2 NULL NULL 10 inserted by client 1 1 inserted by client 1 SELECT COUNT(*) FROM t6; COUNT(*) 4 INSERT INTO t7(b) VALUES ("inserted by client 2"); UPDATE t7 SET a = 10 where a = 1; UPDATE t7 SET b = "updated by client 2" where a = 2; DELETE FROM t7 WHERE a = 3; SELECT * FROM t7; a b aa bb 2 updated by client 2 2 inserted by client 1 4 inserted by client 1 4 inserted by client 1 5 inserted by client 2 NULL NULL 10 inserted by client 1 1 inserted by client 1 SELECT COUNT(*) FROM t7; COUNT(*) 4 # # Connection default # SELECT * FROM t5; a b aa bb 1 inserted by client 1 1 inserted by client 1 2 inserted by client 1 2 inserted by client 1 3 inserted by client 1 3 inserted by client 1 4 inserted by client 1 4 inserted by client 1 INSERT INTO t5(b) VALUES ("inserted by client 1"); SELECT * FROM t5; a b aa bb 1 inserted by client 1 1 inserted by client 1 2 inserted by client 1 2 inserted by client 1 3 inserted by client 1 3 inserted by client 1 4 inserted by client 1 4 inserted by client 1 6 inserted by client 1 NULL NULL UPDATE t5 SET a = a + 100; SELECT * FROM t5; a b aa bb 1 inserted by client 1 1 inserted by client 1 3 inserted by client 1 3 inserted by client 1 102 updated by client 2 2 inserted by client 1 104 inserted by client 1 4 inserted by client 1 105 inserted by client 2 NULL NULL 106 inserted by client 1 NULL NULL 110 inserted by client 1 1 inserted by client 1 SELECT COUNT(*) FROM t5; COUNT(*) 7 UPDATE t6 SET b = "updated by client 2"; SELECT * FROM t6; a b aa bb 1 inserted by client 1 1 inserted by client 1 2 updated by client 2 2 inserted by client 1 3 inserted by client 1 3 inserted by client 1 4 updated by client 2 4 inserted by client 1 5 updated by client 2 NULL NULL 10 updated by client 2 1 inserted by client 1 SELECT COUNT(*) FROM t6; COUNT(*) 6 DELETE FROM t7; SELECT * FROM t7; a b aa bb 1 inserted by client 1 1 inserted by client 1 3 inserted by client 1 3 inserted by client 1 SELECT COUNT(*) FROM t7; COUNT(*) 2 COMMIT; SELECT * FROM t5; a b aa bb 102 updated by client 2 2 inserted by client 1 104 inserted by client 1 4 inserted by client 1 105 inserted by client 2 NULL NULL 106 inserted by client 1 NULL NULL 110 inserted by client 1 1 inserted by client 1 SELECT COUNT(*) FROM t5; COUNT(*) 5 SELECT * FROM t6; a b aa bb 2 updated by client 2 2 inserted by client 1 4 updated by client 2 4 inserted by client 1 5 updated by client 2 NULL NULL 10 updated by client 2 1 inserted by client 1 SELECT COUNT(*) FROM t6; COUNT(*) 4 SELECT * FROM t7; a b aa bb SELECT COUNT(*) FROM t7; COUNT(*) 0 # # Cleanup # DROP TABLE t1,t2,t3,t4,t5,t6,t7; # # Bug #23596760: FORCE INDEX IS SKIPPED WHILE EXECUTING SELECT COUNT(*) # CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY, c2 INT NOT NULL DEFAULT 1, c3 char(20) DEFAULT '', KEY c2_idx (c2)); INSERT INTO t1(c1) VALUES (1), (2), (3); INSERT INTO t1(c1) SELECT c1 + 10 FROM t1; INSERT INTO t1(c1) SELECT c1 + 100 FROM t1; CREATE TABLE t2 SELECT * FROM t1; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK ANALYZE TABLE t2; Table Op Msg_type Msg_text test.t2 analyze status OK EXPLAIN SELECT COUNT(*) FROM t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` FORCE INDEX (`c2_idx`) EXPLAIN SELECT COUNT(*) FROM t1, t2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # 100.00 Using join buffer (Block Nested Loop) Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` join `test`.`t2` EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx), t2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # 100.00 Using join buffer (Block Nested Loop) Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` FORCE INDEX (`c2_idx`) join `test`.`t2` DROP TABLE t1, t2;
Close