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.138.119.75
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-index.result
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; create table t1(a varchar(2) primary key) engine=innodb; insert into t1 values(''); create index t1a1 on t1(a(1)); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 drop table t1; create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb; insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak'); commit; alter table t1 add index b (b), add index b (b); ERROR 42000: Duplicate key name 'b' alter table t1 add index (b,b); ERROR 42S21: Duplicate column name 'b' alter table t1 add index d2 (d); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) NOT NULL, `d` varchar(20) DEFAULT NULL, KEY `d2` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 analyze table t1; explain select * from t1 force index(d2) order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL d2 23 NULL 4 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d2`) order by `test`.`t1`.`d` select * from t1 force index (d2) order by d; a b c d 3 4 ad ad 2 3 ak ak 5 5 oo oo 4 4 tr tr alter table t1 add unique index (b); ERROR 23000: Duplicate entry '4' for key 'b' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) NOT NULL, `d` varchar(20) DEFAULT NULL, KEY `d2` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t1 add index (b); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) NOT NULL, `d` varchar(20) DEFAULT NULL, KEY `d2` (`d`), KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t1 add unique index (c), add index (d); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 1 Warnings: Warning 1831 Duplicate index 'd' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) NOT NULL, `d` varchar(20) DEFAULT NULL, UNIQUE KEY `c` (`c`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 10 NULL 4 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` alter table t1 add primary key (a), drop index c; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) NOT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 affected rows: 1 alter table t1 add primary key (c); ERROR 42000: Multiple primary key defined alter table t1 drop primary key, add primary key (b); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' create unique index c on t1 (c); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) NOT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `c` (`c`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 10 NULL 4 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` select * from t1 force index(c) order by c; a b c d 3 4 ad ad 2 3 ak ak 5 5 oo oo 4 4 tr tr alter table t1 drop index b, add index (b); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) NOT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `c` (`c`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 insert into t1 values(6,1,'ggg','ggg'); select * from t1; a b c d 2 3 ak ak 3 4 ad ad 4 4 tr tr 5 5 oo oo 6 1 ggg ggg select * from t1 force index(b) order by b; a b c d 6 1 ggg ggg 2 3 ak ak 3 4 ad ad 4 4 tr tr 5 5 oo oo select * from t1 force index(c) order by c; a b c d 3 4 ad ad 2 3 ak ak 6 1 ggg ggg 5 5 oo oo 4 4 tr tr select * from t1 force index(d) order by d; a b c d 3 4 ad ad 2 3 ak ak 6 1 ggg ggg 5 5 oo oo 4 4 tr tr analyze table t1; explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 5 NULL 5 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 10 NULL 5 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 force index(d) order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL d 23 NULL 5 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) NOT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `c` (`c`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb; insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe'); commit; alter table t1 add index (c(2)); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY `c` (`c`(2)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 affected rows: 1 alter table t1 add unique index (d(10)); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `d` (`d`(10)), KEY `c` (`c`(2)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 affected rows: 1 insert into t1 values(5,1,'ggg','ggg'); analyze table t1; select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 5 1 ggg ggg select * from t1 force index(c) order by c; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 5 1 ggg ggg select * from t1 force index(d) order by d; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 5 1 ggg ggg explain select * from t1 order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 force index(d) order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `d` (`d`(10)), KEY `c` (`c`(2)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t1 drop index d; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values(8,9,'fff','fff'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 5 1 ggg ggg 8 9 fff fff select * from t1 force index(c) order by c; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 8 9 fff fff 5 1 ggg ggg analyze table t1; explain select * from t1 order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` order by `test`.`t1`.`d` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY `c` (`c`(2)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb; insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); commit; alter table t1 add unique index (b,c); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values(8,9,'fff','fff'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff analyze table t1; explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 16 NULL 5 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t1 add index (b,c); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values(11,11,'kkk','kkk'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk analyze table t1; explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 16 NULL 6 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`,`c`), KEY `b_2` (`b`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t1 add unique index (c,d); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values(13,13,'yyy','aaa'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk 13 13 yyy aaa select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk 13 13 yyy aaa select * from t1 force index(c) order by c; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk 13 13 yyy aaa analyze table t1; explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 16 NULL 7 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 34 NULL 7 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`,`c`), UNIQUE KEY `c` (`c`,`d`), KEY `b_2` (`b`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb; create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb; create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb; create table t2(a int not null, b int, c int, d int, e int, foreign key (b) references t1(b) on delete set null, foreign key (c) references t3(c), foreign key (d) references t4(d) on update set null) engine = innodb; alter table t1 drop index b; ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint alter table t3 drop index c; ERROR HY000: Cannot drop index 'c': needed in a foreign key constraint alter table t4 drop index d; ERROR HY000: Cannot drop index 'd': needed in a foreign key constraint alter table t2 drop index b; ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint alter table t2 drop index b, drop index c, drop index d; ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint alter table t2 MODIFY b INT NOT NULL, ALGORITHM=COPY; ERROR HY000: Cannot change column 'b': used in a foreign key constraint 't2_ibfk_1' alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE; ERROR HY000: Column 'b' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_1' SET NULL SET FOREIGN_KEY_CHECKS=0; alter table t2 DROP COLUMN b, ALGORITHM=COPY; ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1' alter table t2 DROP COLUMN b; ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint alter table t1 DROP COLUMN b, ALGORITHM=COPY; ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 't2_ibfk_1' of table 'test.t2' alter table t1 DROP COLUMN b; ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; create unique index dc on t2 (d,c); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 create index dc on t1 (b,c); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 alter table t2 add primary key (alpha), change a alpha int, change b beta int not null, change c charlie int not null; ERROR HY000: Column 'b' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_1' SET NULL alter table t2 add primary key (alpha), change a alpha int, change c charlie int not null, change d delta int not null; ERROR HY000: Column 'd' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_3' SET NULL alter table t2 add primary key (alpha), change a alpha int, change b beta int, modify c int not null; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values (1,1,1); insert into t3 values (1,1,1); insert into t4 values (1,1,1); insert into t2 values (1,1,1,1,1); commit; alter table t4 add constraint dc foreign key (a) references t1(a); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL, `d` int(11) NOT NULL, `e` int(11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `d` (`d`), CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t3 add constraint dc foreign key (a) references t1(a); ERROR 23000: Can't write; duplicate key in table '#sql-temporary' SET FOREIGN_KEY_CHECKS=0; alter table t3 add constraint dc foreign key (a) references t1(a); ERROR HY000: Failed to add the foreign key constraint 'test/dc' to system tables SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL, `c` int(11) NOT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `c` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t2 drop index b, add index (beta); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `alpha` int(11) NOT NULL, `beta` int(11) DEFAULT NULL, `c` int(11) NOT NULL, `d` int(11) DEFAULT NULL, `e` int(11) DEFAULT NULL, PRIMARY KEY (`alpha`), UNIQUE KEY `dc` (`d`,`c`), KEY `c` (`c`), KEY `beta` (`beta`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`beta`) REFERENCES `t1` (`b`) ON DELETE SET NULL, CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t3` (`c`), CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`d`) REFERENCES `t4` (`d`) ON UPDATE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 delete from t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) drop index dc on t4; ERROR 42000: Can't DROP 'dc'; check that column/key exists alter table t3 drop foreign key dc; ERROR 42000: Can't DROP 'dc'; check that column/key exists alter table t4 drop foreign key dc; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 select * from t2; alpha beta c d e 1 1 1 1 1 delete from t1; select * from t2; alpha beta c d e 1 NULL 1 1 1 drop table t2,t4,t3,t1; create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=utf8; insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); commit; alter table t1 add unique index (b); ERROR 23000: Duplicate entry '2' for key 'b' insert into t1 values(8,9,'fff','fff'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 alter table t1 add index (b); insert into t1 values(10,10,'kkk','iii'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 5 NULL 6 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 alter table t1 add unique index (c), add index (d); insert into t1 values(11,11,'aaa','mmm'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii 11 11 aaa mmm select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii 11 11 aaa mmm select * from t1 force index(c) order by c; a b c d 11 11 aaa mmm 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii select * from t1 force index(d) order by d; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii 11 11 aaa mmm explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 5 NULL 7 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 31 NULL 7 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 force index(d) order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL d 63 NULL 7 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `c` (`c`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; create table t1(a int not null, b int) engine = innodb; insert into t1 values (1,1),(1,1),(1,1),(1,1); alter table t1 add unique index (a); ERROR 23000: Duplicate entry '1' for key 'a' alter table t1 add unique index (b); ERROR 23000: Duplicate entry '1' for key 'b' alter table t1 add unique index (a), add unique index(b); ERROR 23000: Duplicate entry '1' for key 'a' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb; alter table t1 drop index c, drop index b; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `c` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1(a int not null, b int, primary key(a)) engine = innodb; alter table t1 add index (b); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb; insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe'); alter table t1 add unique index (b), add unique index (c), add unique index (d); ERROR 23000: Duplicate entry '4' for key 'b' alter table t1 add unique index (c), add unique index (b), add index (d); ERROR 23000: Duplicate entry 'ac' for key 'c' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb; insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1); alter table t1 add unique index (b); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`), KEY `c` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 check table t1; Table Op Msg_type Msg_text test.t1 check status OK analyze table t1; explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 5 NULL 9 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 order by a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 9 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a` explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 4 NULL 9 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` select * from t1 order by a; a b c 1 5 1 2 4 2 3 3 3 4 2 4 5 1 5 10 20 20 11 19 19 12 18 18 13 17 17 select * from t1 force index(b) order by b; a b c 5 1 5 4 2 4 3 3 3 2 4 2 1 5 1 13 17 17 12 18 18 11 19 19 10 20 20 select * from t1 force index(c) order by c; a b c 1 5 1 2 4 2 3 3 3 4 2 4 5 1 5 13 17 17 12 18 18 11 19 19 10 20 20 drop table t1; create table t1(a int not null, b int not null) engine=innodb; insert into t1 values (1,1); alter table t1 add primary key(b); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values (2,2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 check table t1; Table Op Msg_type Msg_text test.t1 check status OK select * from t1; a b 1 1 2 2 analyze table t1; Table Op Msg_type Msg_text test.t1 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 2 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` explain select * from t1 order by a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a` explain select * from t1 order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 2 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b` checksum table t1; Table Checksum test.t1 582702641 drop table t1; create table t1(a int not null) engine=innodb; insert into t1 values (1); alter table t1 add primary key(a); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values (2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 check table t1; Table Op Msg_type Msg_text test.t1 check status OK commit; select * from t1; a 1 2 analyze table t1; Table Op Msg_type Msg_text test.t1 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 index NULL PRIMARY 4 NULL 2 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` explain select * from t1 order by a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 2 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` drop table t1; SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; CREATE TABLE t1( c1 BIGINT(12) NOT NULL, PRIMARY KEY (c1) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE t2( c1 BIGINT(16) NOT NULL, c2 BIGINT(12) NOT NULL, c3 BIGINT(12) NOT NULL, PRIMARY KEY (c1) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3) REFERENCES t1(c1); SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` bigint(16) NOT NULL, `c2` bigint(12) NOT NULL, `c3` bigint(12) NOT NULL, PRIMARY KEY (`c1`), KEY `fk_t2_ca` (`c3`), CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2); SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` bigint(16) NOT NULL, `c2` bigint(12) NOT NULL, `c3` bigint(12) NOT NULL, PRIMARY KEY (`c1`), KEY `i_t2_c3_c2` (`c3`,`c2`), CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; INSERT INTO t2 VALUES(0,0,0); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)) INSERT INTO t1 VALUES(0); INSERT INTO t2 VALUES(0,0,0); DROP TABLE t2; CREATE TABLE t2( c1 BIGINT(16) NOT NULL, c2 BIGINT(12) NOT NULL, c3 BIGINT(12) NOT NULL, PRIMARY KEY (c1,c2,c3) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET FOREIGN_KEY_CHECKS=0; ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3) REFERENCES t1(c1); SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` bigint(16) NOT NULL, `c2` bigint(12) NOT NULL, `c3` bigint(12) NOT NULL, PRIMARY KEY (`c1`,`c2`,`c3`), KEY `fk_t2_ca` (`c3`), CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2); SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` bigint(16) NOT NULL, `c2` bigint(12) NOT NULL, `c3` bigint(12) NOT NULL, PRIMARY KEY (`c1`,`c2`,`c3`), KEY `i_t2_c3_c2` (`c3`,`c2`), CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO t2 VALUES(0,0,1); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)) INSERT INTO t2 VALUES(0,0,0); DELETE FROM t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)) DELETE FROM t2; DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1( c1 BIGINT(12) NOT NULL, c2 INT(4) NOT NULL, PRIMARY KEY (c2,c1) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE t2( c1 BIGINT(16) NOT NULL, c2 BIGINT(12) NOT NULL, c3 BIGINT(12) NOT NULL, PRIMARY KEY (c1) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET FOREIGN_KEY_CHECKS=0; ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY; ERROR HY000: Cannot add foreign key constraint ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1); ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1' ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY; ERROR HY000: Cannot add foreign key constraint ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2); ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1' ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=INPLACE; ERROR HY000: Failed to add the foreign key constraint on table 't2'. Incorrect options in FOREIGN KEY constraint 'test/fk_t2_ca' ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY; ERROR HY000: Cannot add foreign key constraint ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY; ERROR HY000: Cannot add foreign key constraint ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2); ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1' ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; affected rows: 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` bigint(12) NOT NULL, `c2` bigint(12) NOT NULL, PRIMARY KEY (`c2`,`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 affected rows: 1 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` bigint(16) NOT NULL, `c2` bigint(12) NOT NULL, `c3` bigint(12) NOT NULL, PRIMARY KEY (`c1`), KEY `fk_t2_ca` (`c3`,`c2`), CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 affected rows: 1 CREATE INDEX i_t2_c2_c1 ON t2(c2, c1); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` bigint(16) NOT NULL, `c2` bigint(12) NOT NULL, `c3` bigint(12) NOT NULL, PRIMARY KEY (`c1`), KEY `fk_t2_ca` (`c3`,`c2`), KEY `i_t2_c2_c1` (`c2`,`c1`), CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 affected rows: 1 CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` bigint(16) NOT NULL, `c2` bigint(12) NOT NULL, `c3` bigint(12) NOT NULL, PRIMARY KEY (`c1`), KEY `fk_t2_ca` (`c3`,`c2`), KEY `i_t2_c2_c1` (`c2`,`c1`), KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`), CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 affected rows: 1 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` bigint(16) NOT NULL, `c2` bigint(12) NOT NULL, `c3` bigint(12) NOT NULL, PRIMARY KEY (`c1`), KEY `i_t2_c2_c1` (`c2`,`c1`), KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`), KEY `i_t2_c3_c2` (`c3`,`c2`), CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 affected rows: 1 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (3,'a'),(3,'b'),(1,'c'),(0,'d'),(1,'e'); CREATE TABLE t2 (a INT, b CHAR(1)) ENGINE=InnoDB; CREATE TABLE t2i (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB; CREATE TABLE t2c (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t2 SELECT * FROM t1; INSERT INTO t2i SELECT * FROM t1; INSERT INTO t2c SELECT * FROM t1; BEGIN; SELECT * FROM t1; a b 3 a 3 b 1 c 0 d 1 e SET lock_wait_timeout=1; CREATE INDEX t1a ON t1(a); ERROR HY000: Lock wait timeout exceeded; try restarting transaction CREATE INDEX t2a ON t2(a); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY; affected rows: 5 info: Records: 5 Duplicates: 0 Warnings: 0 SELECT * FROM t2i; ERROR HY000: Table definition has changed, please retry transaction SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a; ERROR HY000: Table definition has changed, please retry transaction SELECT * FROM t2c; ERROR HY000: Table definition has changed, please retry transaction SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a; ERROR HY000: Table definition has changed, please retry transaction SELECT * FROM t2; a b 3 a 3 b 1 c 0 d 1 e SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a; ERROR HY000: Table definition has changed, please retry transaction SELECT * FROM t2; a b 3 a 3 b 1 c 0 d 1 e COMMIT; SELECT * FROM t2; a b 3 a 3 b 1 c 0 d 1 e SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a; a b 0 d 1 c 1 e 3 a 3 b SELECT * FROM t2i; a b 0 d 1 c 1 e 3 a 3 b SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a; a b 0 d 1 c 1 e 3 a 3 b SELECT * FROM t2c; a b 0 d 1 c 1 e 3 a 3 b SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a; a b 0 d 1 c 1 e 3 a 3 b alter table t2 add index t2a(b); ERROR 42000: Duplicate key name 't2a' alter table t2 drop index t2a, add index t2a(b); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, `b` char(1) DEFAULT NULL, KEY `t2a` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 show create table t2i; Table Create Table t2i CREATE TABLE `t2i` ( `a` int(11) NOT NULL, `b` char(1) NOT NULL, PRIMARY KEY (`a`,`b`), KEY `t2a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 show create table t2c; Table Create Table t2c CREATE TABLE `t2c` ( `a` int(11) NOT NULL, `b` char(1) NOT NULL, PRIMARY KEY (`a`,`b`), KEY `t2a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t1,t2,t2c,t2i; # # Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE # SET GLOBAL innodb_monitor_enable = module_ddl; create table t1(f1 int not null, f2 blob)engine=innodb; insert into t1 values(1, repeat('a',20000)); # Skip sort # Reusing the same pk alter table t1 force; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; drop table t1; create table t1(f1 int not null, f2 int not null, primary key(f1))engine=innodb; insert into t1 values(1,2), (3,4); # Add Secondary index. # Skip temp file usage due to small table size alter table t1 add key(f2); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; drop table t1; create table t480(a serial)engine=innodb; insert into t480 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(), (),(),(),(),(),(),(),(); insert into t480 select 0 from t480; insert into t480 select 0 from t480; insert into t480 select 0 from t480; insert into t480 select 0 from t480; create table t1(f1 int auto_increment not null, f2 char(200) not null, f3 char(200) not null, f4 char(200) not null,primary key(f1))engine=innodb; insert into t1 select NULL,'aaa','bbb','ccc' from t480; insert into t1 select NULL,'aaaa','bbbb','cccc' from t480; insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480; insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480; insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480; insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480; select count(*) from t1; count(*) 2880 # Skip sort # Change PK from (f1) to (f1,f2,f3,f4) alter table t1 drop primary key, add primary key(f1,f2,f3,f4); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns) alter table t1 drop primary key,add column f5 int not null, add column f6 int not null,add primary key(f1,f2,f5,f6); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Change PK from (f1,f2,f5,f6) to (f1,f2,f5) alter table t1 drop column f6; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Reusing the same PK alter table t1 add column f6 int; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Reusing the same pk alter table t1 drop column f6; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Must sort # Change PK from (f1,f2,f5) to (f1,f5) alter table t1 drop column f2; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset ddl_sort_file_alter_table 2 SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Reusing the same pk alter table t1 add column f2n int after f1, drop primary key, add primary key (f1,f5,f2n); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Reusing the same pk alter table t1 change f5 f2n int not null,change f2n f5 int not null, add column f8 int not null; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n) alter table t1 add column f7 int, drop primary key, add primary key (f1,f5,f7,f2n); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset ddl_sort_file_alter_table 2 SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Reusing the same pk alter table t1 force; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Reusing the same pk alter table t1 row_format=compact; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Reusing the same pk alter table t1 engine=innodb; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Optimize table optimize table t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK affected rows: 2 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Sort files used for adding secondary index alter table t1 drop primary key, add primary key(f1,f5,f7), add index i(f3); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset ddl_sort_file_alter_table 2 SET GLOBAL innodb_monitor_reset = module_ddl; # No sort files used for dropping secondary index alter table t1 drop primary key, add primary key(f1,f5),drop index i; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Change PK(f1,f5) to (f1,added_columns) and drop f5 alter table t1 drop primary key, add primary key(f1,f12), drop column f5, add column f12 int not null; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Must sort # Change PK(f1,f12) to (f1,existing_columns) alter table t1 drop primary key, add primary key(f1,f3); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset ddl_sort_file_alter_table 2 SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort # Change PK(f1,f3) to (f1,added_column,f3,added_column) alter table t1 drop primary key, add column f3n int, add column f4n int, add primary key(f1,f3n,f3,f4n); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset SET GLOBAL innodb_monitor_reset = module_ddl; # Adding Secondary index alone. alter table t1 add key(f1); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset ddl_sort_file_alter_table 2 SET GLOBAL innodb_monitor_reset = module_ddl; # Must sort # Change PK(f1,f3) to (existing_column,f1) alter table t1 drop primary key, add primary key(f4,f1); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset ddl_sort_file_alter_table 3 SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort for PK. # Change PK(f4,f1) to (added_columns,f4,f1) # Secondary index rebuild happens alter table t1 drop primary key, add column f5n int, add column f6n int, add primary key(f5n,f6n,f4,f1); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset ddl_sort_file_alter_table 2 SET GLOBAL innodb_monitor_reset = module_ddl; # Skip sort for PK. # Change PK(f5n,f6n,f4,f1) to # (added_columns,f5n,added_column,f6n,f4,f1) # Secondary index rebuild happens alter table t1 drop primary key, add column f7n int, add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl' and count_reset > 0; name count_reset ddl_sort_file_alter_table 2 SET GLOBAL innodb_monitor_reset = module_ddl; SET GLOBAL innodb_monitor_disable = module_ddl; select count(*) from t1; count(*) 2880 drop table t1; SET GLOBAL innodb_monitor_reset = default; SET GLOBAL innodb_monitor_enable = default; SET GLOBAL innodb_monitor_disable = default; # Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST # WHEN SKIPPING SORT FOR CLUSTER INDEX SELECT @@innodb_sort_buffer_size; @@innodb_sort_buffer_size 65536 create table t1(f1 int auto_increment not null, f2 char(200) not null, f3 char(200) not null, f4 char(200) not null,primary key(f1,f2,f3,f4)); insert into t1 select NULL,'aaa','bbb','ccc' from t480; insert into t1 values(106, 'aaa','bbb','cccc'); select count(*) from t1; count(*) 481 # Skip sort # Change PK from (f1,f2,f3,f4) to (f1,f2,f3) alter table t1 drop primary key, add primary key(f1,f2,f3); ERROR 23000: Duplicate entry '106-aaa-bbb' for key 'PRIMARY' select count(*) from t1; count(*) 481 drop table t1; create table t1(f1 int auto_increment not null, f2 char(200) not null, f3 char(200) not null, f4 char(200) not null,primary key(f1,f2,f3,f4)); insert into t1 select NULL,'aaa','bbb','ccc' from t480; insert into t1 values(108,'aaa','bbb','cccc'); select count(*) from t1; count(*) 481 alter table t1 drop primary key, add primary key(f1,f2,f3); ERROR 23000: Duplicate entry '108-aaa-bbb' for key 'PRIMARY' select count(*) from t1; count(*) 481 drop table t1, t480; # # Bug #19896922 SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK # FIELD IS CHANGED # create table t1(a int not null, b varchar(30) not null, primary key (b(10), a)) engine = innodb; insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L'); select * from t1; a b 0 khdHps6UxW8Lwaoxa604oK6zkb 1 khdHps6UxW8L alter table t1 drop primary key, add primary key (b(18),a); select * from t1; a b 1 khdHps6UxW8L 0 khdHps6UxW8Lwaoxa604oK6zkb drop table t1; create table t1(a int not null, b varchar(30) not null, primary key (b(10), a)) engine = innodb; insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L'); select * from t1; a b 1 khdHps6UtW8L 0 khdHps6UxW8Lwaoxa604oK6zkb alter table t1 drop primary key, add primary key (b(8),a); select * from t1; a b 0 khdHps6UxW8Lwaoxa604oK6zkb 1 khdHps6UtW8L drop table t1; # # Bug #21103101 SORTING SKIPPED WHEN DROPPING THE SINGLE # COLUMN PRIMARY KEY # create table t1(f1 int not null, f2 int not null, primary key (f1), unique key(f1, f2))engine=innodb; insert into t1 values(1,3), (2,2); alter table t1 drop column f1; drop table t1; create table t1(f1 int not null, f2 int not null, primary key (f1), unique key(f1, f2))engine=innodb; insert into t1 values(1,3), (2,2); alter table t1 drop primary key, lock=none; ERROR 0A000: LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED. drop table t1; # # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP # LAST COLUMN OF OLD PK # create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop o1, drop o2, add primary key(o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o1,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,1,2),(1,2,1); alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,1,2),(2,2,1); alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop primary key, add primary key(o2, o1), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop primary key, add primary key(o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop primary key, add primary key(o2,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb; insert into t1 values(1,1,2),(2,1,1); alter table t1 drop primary key, add primary key(o2,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o1,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop o1, lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop o2, lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop o1, drop o2, lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb; insert into t1 values('abd', 1, 1), ('abc', 2, 2); alter table t1 drop primary key, add primary key(o1(3), o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb; insert into t1 values('abd', 1, 1), ('abc', 2, 2); alter table t1 drop primary key, add primary key(o1, o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb; insert into t1 values('abd', 1, 1), ('abc', 2, 2); alter table t1 drop primary key, add primary key(o1(3), o3), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb; insert into t1 values('abd', 1, 1), ('abc', 2, 2); alter table t1 drop primary key, add primary key(o1, o3), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb; insert into t1 values('abc', 2, 1), ('abd', 1, 2); alter table t1 drop primary key, add primary key(o1(2), o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb; insert into t1 values('abc', 2, 1), ('abd', 1, 2); alter table t1 drop primary key, add primary key(o1(2), o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb; insert into t1 values('abc', 2, 2), ('abd', 1, 1); alter table t1 drop primary key, add primary key(o1(2), o3), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb; insert into t1 values('abc', 2, 2), ('abd', 1, 1); alter table t1 drop primary key, add primary key(o1(2), o3), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb; insert into t1 values(1, 'abd', 1), (1, 'abc', 2); alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb; insert into t1 values(1, 'abd', 1), (1, 'abc', 2); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb; insert into t1 values('abc', 'acd'), ('abd', 'abd'); alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb; insert into t1 values('abc', 'acd'), ('abd', 'abd'); alter table t1 drop primary key, add primary key(o1(2),o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o2,o1), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; insert into t1 values('abd'), ('acd'); alter table t1 drop primary key, add primary key(o1(3)), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; insert into t1 values('abd'), ('acd'); alter table t1 drop primary key, add primary key(o1), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; insert into t1 values('abd'), ('acd'); alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; insert into t1 values('abd'), ('acd'); alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb; insert into t1 values('abd'), ('acd'); alter table t1 drop primary key, add primary key(o1(2)), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1)) engine = innodb; insert into t1 values('abd'), ('acd'); alter table t1 drop primary key, add primary key(o1(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb; insert into t1 values('abd', 1), ('acd', 2); alter table t1 drop primary key, add primary key(o1(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb; insert into t1 values('abd', 1), ('acd', 2); alter table t1 drop primary key, add primary key(o1(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb; insert into t1 values(1,'abd'), (2,'acd'); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb; insert into t1 values(1,'abd'), (2,'acd'); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb; insert into t1 values(1, 'abd'), (2, 'acd'); alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb; insert into t1 values(1, 'abd'), (2, 'acd'); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb; insert into t1 values(1, 'abd', 1), (2, 'acd', 2); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1, 'abd', 1), (2, 'acd', 2); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb; insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb; insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb; insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb; insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1),(2,2); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1,2),(2,2,1); alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none; drop table t1; create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb; insert into t1 values(1,1,2),(2,2,1); alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1,1,2),(2,2,1); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1,1,2,2),(2,2,1,1); alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1,1,2),(2,2,1); alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1,1,2),(2,2,1); alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; insert into t1 values(1,1,2),(2,2,1); alter table t1 drop primary key, add primary key(o1), lock=none; drop table t1;
Close