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 | : 18.217.142.228
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.result
drop table if exists t1,t2,t3,t4; drop database if exists mysqltest; CREATE TABLE bug58912 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB; INSERT INTO bug58912 VALUES(REPEAT('a',8000),REPEAT('b',8000)); UPDATE bug58912 SET a=REPEAT('a',7999); create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); select id, code, name from t1 order by id; id code name 1 1 Tim 2 1 Monty 3 2 David 4 2 Erik 5 3 Sasha 6 3 Jeremy 7 4 Matt update ignore t1 set id = 8, name = 'Sinisa' where id < 3; Warnings: Warning 1062 Duplicate entry '8' for key 'PRIMARY' select id, code, name from t1 order by id; id code name 2 1 Monty 3 2 David 4 2 Erik 5 3 Sasha 6 3 Jeremy 7 4 Matt 8 1 Sinisa update ignore t1 set id = id + 10, name = 'Ralph' where id < 4; Warnings: Warning 1062 Duplicate entry 'Ralph' for key 'name' select id, code, name from t1 order by id; id code name 3 2 David 4 2 Erik 5 3 Sasha 6 3 Jeremy 7 4 Matt 8 1 Sinisa 12 1 Ralph drop table t1; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, parent_id int(11) DEFAULT '0' NOT NULL, level tinyint(4) DEFAULT '0' NOT NULL, PRIMARY KEY (id), KEY parent_id (parent_id), KEY level (level) ) engine=innodb; INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2); update t1 set parent_id=parent_id+100; select * from t1 where parent_id=102; id parent_id level 8 102 2 9 102 2 15 102 2 update t1 set id=id+1000; update t1 set id=1024 where id=1009; Got one of the listed errors select * from t1; id parent_id level 1001 100 0 1002 101 1 1003 101 1 1004 101 1 1005 101 1 1006 101 1 1007 101 1 1008 102 2 1009 102 2 1015 102 2 1016 103 2 1017 103 2 1018 103 2 1019 103 2 1020 103 2 1021 104 2 1022 104 2 1024 104 2 1025 105 2 1026 105 2 1027 105 2 1028 105 2 1029 105 2 1030 105 2 1031 106 2 1032 106 2 1033 106 2 1034 106 2 1035 106 2 1036 107 2 1037 107 2 1038 107 2 1040 107 2 1157 100 0 1179 105 2 1183 104 2 1193 105 2 1202 107 2 1203 107 2 update ignore t1 set id=id+1; Warnings: Warning 1062 Duplicate entry '1002' for key 'PRIMARY' Warning 1062 Duplicate entry '1003' for key 'PRIMARY' Warning 1062 Duplicate entry '1004' for key 'PRIMARY' Warning 1062 Duplicate entry '1005' for key 'PRIMARY' Warning 1062 Duplicate entry '1006' for key 'PRIMARY' Warning 1062 Duplicate entry '1007' for key 'PRIMARY' Warning 1062 Duplicate entry '1008' for key 'PRIMARY' Warning 1062 Duplicate entry '1009' for key 'PRIMARY' Warning 1062 Duplicate entry '1016' for key 'PRIMARY' Warning 1062 Duplicate entry '1017' for key 'PRIMARY' Warning 1062 Duplicate entry '1018' for key 'PRIMARY' Warning 1062 Duplicate entry '1019' for key 'PRIMARY' Warning 1062 Duplicate entry '1020' for key 'PRIMARY' Warning 1062 Duplicate entry '1021' for key 'PRIMARY' Warning 1062 Duplicate entry '1022' for key 'PRIMARY' Warning 1062 Duplicate entry '1025' for key 'PRIMARY' Warning 1062 Duplicate entry '1026' for key 'PRIMARY' Warning 1062 Duplicate entry '1027' for key 'PRIMARY' Warning 1062 Duplicate entry '1028' for key 'PRIMARY' Warning 1062 Duplicate entry '1029' for key 'PRIMARY' Warning 1062 Duplicate entry '1030' for key 'PRIMARY' Warning 1062 Duplicate entry '1031' for key 'PRIMARY' Warning 1062 Duplicate entry '1032' for key 'PRIMARY' Warning 1062 Duplicate entry '1033' for key 'PRIMARY' Warning 1062 Duplicate entry '1034' for key 'PRIMARY' Warning 1062 Duplicate entry '1035' for key 'PRIMARY' Warning 1062 Duplicate entry '1036' for key 'PRIMARY' Warning 1062 Duplicate entry '1037' for key 'PRIMARY' Warning 1062 Duplicate entry '1038' for key 'PRIMARY' Warning 1062 Duplicate entry '1203' for key 'PRIMARY' select * from t1; id parent_id level 1001 100 0 1002 101 1 1003 101 1 1004 101 1 1005 101 1 1006 101 1 1007 101 1 1008 102 2 1010 102 2 1015 102 2 1016 103 2 1017 103 2 1018 103 2 1019 103 2 1020 103 2 1021 104 2 1023 104 2 1024 104 2 1025 105 2 1026 105 2 1027 105 2 1028 105 2 1029 105 2 1030 105 2 1031 106 2 1032 106 2 1033 106 2 1034 106 2 1035 106 2 1036 107 2 1037 107 2 1039 107 2 1041 107 2 1158 100 0 1180 105 2 1184 104 2 1194 105 2 1202 107 2 1204 107 2 update ignore t1 set id=1023 where id=1010; Warnings: Warning 1062 Duplicate entry '1023' for key 'PRIMARY' select * from t1 where parent_id=102; id parent_id level 1008 102 2 1010 102 2 1015 102 2 explain select level from t1 where level=1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref level level 1 const # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level` from `test`.`t1` where (`test`.`t1`.`level` = 1) explain select level,id from t1 where level=1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref level level 1 const # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`level` = 1) explain select level,id,parent_id from t1 where level=1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref level level 1 const # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`parent_id` AS `parent_id` from `test`.`t1` where (`test`.`t1`.`level` = 1) select level,id from t1 where level=1; level id 1 1002 1 1003 1 1004 1 1005 1 1006 1 1007 select level,id,parent_id from t1 where level=1; level id parent_id 1 1002 101 1 1003 101 1 1004 101 1 1005 101 1 1006 101 1 1007 101 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 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 id A # NULL NULL BTREE t1 1 parent_id 1 parent_id A # NULL NULL BTREE t1 1 level 1 level A # NULL NULL BTREE drop table t1; CREATE TABLE t1 ( gesuchnr int(11) DEFAULT '0' NOT NULL, benutzer_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (gesuchnr,benutzer_id) ) engine=innodb; replace into t1 (gesuchnr,benutzer_id) values (2,1); replace into t1 (gesuchnr,benutzer_id) values (1,1); replace into t1 (gesuchnr,benutzer_id) values (1,1); select * from t1; gesuchnr benutzer_id 1 1 2 1 drop table t1; create table t1 (a int) engine=innodb; insert into t1 values (1), (2); 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 delete from t1 where a = 1; select * from t1; a 2 check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; create table t1 (a int,b varchar(20)) engine=innodb; insert into t1 values (1,""), (2,"testing"); delete from t1 where a = 1; select * from t1; a b 2 testing create index skr on t1 (a); insert into t1 values (3,""), (4,"testing"); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 1 skr 1 a A # NULL NULL YES BTREE drop table t1; create table t1 (a int,b varchar(20),key(a)) engine=innodb; insert into t1 values (1,""), (2,"testing"); select * from t1 where a = 1; a b 1 drop table t1; create table t1 (n int not null primary key) engine=innodb; set autocommit=0; insert into t1 values (4); rollback; select n, "after rollback" from t1; n after rollback insert into t1 values (4); commit; select n, "after commit" from t1; n after commit 4 after commit commit; insert into t1 values (5); insert into t1 values (4); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' commit; select n, "after commit" from t1; n after commit 4 after commit 5 after commit set autocommit=1; insert into t1 values (6); insert into t1 values (4); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' select n from t1; n 4 5 6 set autocommit=0; begin; savepoint `my_savepoint`; insert into t1 values (7); savepoint `savept2`; insert into t1 values (3); select n from t1; n 3 4 5 6 7 savepoint savept3; rollback to savepoint savept2; rollback to savepoint savept3; ERROR 42000: SAVEPOINT savept3 does not exist rollback to savepoint savept2; release savepoint `my_savepoint`; select n from t1; n 4 5 6 7 rollback to savepoint `my_savepoint`; ERROR 42000: SAVEPOINT my_savepoint does not exist rollback to savepoint savept2; ERROR 42000: SAVEPOINT savept2 does not exist insert into t1 values (8); savepoint sv; commit; savepoint sv; set autocommit=1; rollback; drop table t1; create table t1 (n int not null primary key) engine=innodb; start transaction; insert into t1 values (4); flush tables with read lock; commit; unlock tables; commit; select * from t1; n 4 drop table t1; create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb; begin; insert into t1 values(1,'hamdouni'); select id as afterbegin_id,nom as afterbegin_nom from t1; afterbegin_id afterbegin_nom 1 hamdouni rollback; select id as afterrollback_id,nom as afterrollback_nom from t1; afterrollback_id afterrollback_nom set autocommit=0; insert into t1 values(2,'mysql'); select id as afterautocommit0_id,nom as afterautocommit0_nom from t1; afterautocommit0_id afterautocommit0_nom 2 mysql rollback; select id as afterrollback_id,nom as afterrollback_nom from t1; afterrollback_id afterrollback_nom set autocommit=1; drop table t1; CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb; insert into t1 values ('pippo', 12); insert into t1 values ('pippo', 12); ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY' delete from t1; delete from t1 where id = 'pippo'; select * from t1; id val insert into t1 values ('pippo', 12); set autocommit=0; delete from t1; rollback; select * from t1; id val pippo 12 delete from t1; commit; select * from t1; id val drop table t1; create table t1 (a integer) engine=innodb; start transaction; rename table t1 to t2; create table t1 (b integer) engine=innodb; insert into t1 values (1); rollback; drop table t1; rename table t2 to t1; drop table t1; set autocommit=1; CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb; INSERT INTO t1 VALUES (1, 'Jochen'); select * from t1; ID NAME 1 Jochen drop table t1; CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb; set autocommit=0; INSERT INTO t1 SET _userid='marc@anyware.co.uk'; COMMIT; SELECT * FROM t1; _userid marc@anyware.co.uk SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk'; _userid marc@anyware.co.uk drop table t1; set autocommit=1; CREATE TABLE t1 ( user_id int(10) DEFAULT '0' NOT NULL, name varchar(100), phone varchar(100), ref_email varchar(100) DEFAULT '' NOT NULL, detail varchar(200), PRIMARY KEY (user_id,ref_email) )engine=innodb; INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar'); select * from t1 where user_id=10292; user_id name phone ref_email detail 10292 sanjeev 29153373 sansh777@hotmail.com xxx 10292 shirish 2333604 shirish@yahoo.com ddsds 10292 sonali 323232 sonali@bolly.com filmstar INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds'); select * from t1 where user_id=10292; user_id name phone ref_email detail 10292 sanjeev 29153373 sansh777@hotmail.com xxx 10292 shirish 2333604 shirish@yahoo.com ddsds 10292 sonali 323232 sonali@bolly.com filmstar select * from t1 where user_id>=10292; user_id name phone ref_email detail 10292 sanjeev 29153373 sansh777@hotmail.com xxx 10292 shirish 2333604 shirish@yahoo.com ddsds 10292 sonali 323232 sonali@bolly.com filmstar 10293 shirish 2333604 shirish@yahoo.com ddsds select * from t1 where user_id>10292; user_id name phone ref_email detail 10293 shirish 2333604 shirish@yahoo.com ddsds select * from t1 where user_id<10292; user_id name phone ref_email detail 10291 sanjeev 29153373 sansh777@hotmail.com xxx drop table t1; CREATE TABLE t1 (a int not null, b int not null,c int not null, key(a),primary key(a,b), unique(c),key(a),unique(b)); Warnings: Warning 1831 Duplicate index 'a_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 a A # NULL NULL BTREE t1 0 PRIMARY 2 b A # NULL NULL BTREE t1 0 c 1 c A # NULL NULL BTREE t1 0 b 1 b A # NULL NULL BTREE t1 1 a 1 a A # NULL NULL BTREE t1 1 a_2 1 a A # NULL NULL BTREE drop table t1; create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)); alter table t1 engine=innodb; insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); select * from t1; col1 col2 1 1 2 3 3 4 4 4 5 2 update t1 set col2='7' where col1='4'; select * from t1; col1 col2 1 1 2 3 3 4 4 7 5 2 alter table t1 add co3 int not null; select * from t1; col1 col2 co3 1 1 0 2 3 0 3 4 0 4 7 0 5 2 0 update t1 set col2='9' where col1='2'; select * from t1; col1 col2 co3 1 1 0 2 9 0 3 4 0 4 7 0 5 2 0 drop table t1; create table t1 (a int not null , b int, primary key (a)) engine = innodb; create table t2 (a int not null , b int, primary key (a)) engine = myisam; insert into t1 VALUES (1,3) , (2,3), (3,3); select * from t1; a b 1 3 2 3 3 3 insert into t2 select * from t1; select * from t2; a b 1 3 2 3 3 3 delete from t1 where b = 3; select * from t1; a b insert into t1 select * from t2; select * from t1; a b 1 3 2 3 3 3 select * from t2; a b 1 3 2 3 3 3 drop table t1,t2; CREATE TABLE t1 ( user_name varchar(12), password text, subscribed char(1), user_id int(11) DEFAULT '0' NOT NULL, quota bigint(20), weight double, access_date date, access_time time, approved datetime, dummy_primary_key int(11) NOT NULL auto_increment, PRIMARY KEY (dummy_primary_key) ) ENGINE=innodb; INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3); INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4); INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5); select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name; user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1 user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2 user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3 user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4 user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5 drop table t1; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, parent_id int(11) DEFAULT '0' NOT NULL, level tinyint(4) DEFAULT '0' NOT NULL, KEY (id), KEY parent_id (parent_id), KEY level (level) ) engine=innodb; INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1); INSERT INTO t1 values (179,5,2); update t1 set parent_id=parent_id+100; select * from t1 where parent_id=102; id parent_id level 8 102 2 9 102 2 15 102 2 update t1 set id=id+1000; update t1 set id=1024 where id=1009; select * from t1; id parent_id level 1001 100 0 1003 101 1 1004 101 1 1008 102 2 1024 102 2 1017 103 2 1022 104 2 1024 104 2 1028 105 2 1029 105 2 1030 105 2 1031 106 2 1032 106 2 1033 106 2 1203 107 2 1202 107 2 1020 103 2 1157 100 0 1193 105 2 1040 107 2 1002 101 1 1015 102 2 1006 101 1 1034 106 2 1035 106 2 1016 103 2 1007 101 1 1036 107 2 1018 103 2 1026 105 2 1027 105 2 1183 104 2 1038 107 2 1025 105 2 1037 107 2 1021 104 2 1019 103 2 1005 101 1 1179 105 2 update ignore t1 set id=id+1; select * from t1; id parent_id level 1002 100 0 1004 101 1 1005 101 1 1009 102 2 1025 102 2 1018 103 2 1023 104 2 1025 104 2 1029 105 2 1030 105 2 1031 105 2 1032 106 2 1033 106 2 1034 106 2 1204 107 2 1203 107 2 1021 103 2 1158 100 0 1194 105 2 1041 107 2 1003 101 1 1016 102 2 1007 101 1 1035 106 2 1036 106 2 1017 103 2 1008 101 1 1037 107 2 1019 103 2 1027 105 2 1028 105 2 1184 104 2 1039 107 2 1026 105 2 1038 107 2 1022 104 2 1020 103 2 1006 101 1 1180 105 2 update ignore t1 set id=1023 where id=1010; select * from t1 where parent_id=102; id parent_id level 1009 102 2 1025 102 2 1016 102 2 explain select level from t1 where level=1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref level level 1 const # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`level` AS `level` from `test`.`t1` where (`test`.`t1`.`level` = 1) select level,id from t1 where level=1; level id 1 1004 1 1005 1 1003 1 1007 1 1008 1 1006 select level,id,parent_id from t1 where level=1; level id parent_id 1 1004 101 1 1005 101 1 1003 101 1 1007 101 1 1008 101 1 1006 101 select level,id from t1 where level=1 order by id; level id 1 1003 1 1004 1 1005 1 1006 1 1007 1 1008 delete from t1 where level=1; select * from t1; id parent_id level 1002 100 0 1009 102 2 1025 102 2 1018 103 2 1023 104 2 1025 104 2 1029 105 2 1030 105 2 1031 105 2 1032 106 2 1033 106 2 1034 106 2 1204 107 2 1203 107 2 1021 103 2 1158 100 0 1194 105 2 1041 107 2 1016 102 2 1035 106 2 1036 106 2 1017 103 2 1037 107 2 1019 103 2 1027 105 2 1028 105 2 1184 104 2 1039 107 2 1026 105 2 1038 107 2 1022 104 2 1020 103 2 1180 105 2 drop table t1; CREATE TABLE t1 ( sca_code char(6) NOT NULL, cat_code char(6) NOT NULL, sca_desc varchar(50), lan_code char(2) NOT NULL, sca_pic varchar(100), sca_sdesc varchar(50), sca_sch_desc varchar(16), PRIMARY KEY (sca_code, cat_code, lan_code), INDEX sca_pic (sca_pic) ) engine = innodb ; INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING'); select count(*) from t1 where sca_code = 'PD'; count(*) 1 select count(*) from t1 where sca_code <= 'PD'; count(*) 1 select count(*) from t1 where sca_pic is null; count(*) 2 alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); select count(*) from t1 where sca_code='PD' and sca_pic is null; count(*) 1 select count(*) from t1 where cat_code='E'; count(*) 0 alter table t1 drop index sca_pic, add index (sca_pic, cat_code); select count(*) from t1 where sca_code='PD' and sca_pic is null; count(*) 1 select count(*) from t1 where sca_pic >= 'n'; count(*) 1 select sca_pic from t1 where sca_pic is null; sca_pic NULL NULL update t1 set sca_pic="test" where sca_pic is null; delete from t1 where sca_code='pd'; drop table t1; set @a:=now(); CREATE TABLE t1 (a int not null, b timestamp not null default current_timestamp on update current_timestamp, primary key (a)) engine=innodb; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; a 1 2 3 select a from t1 natural join t1 as t2 where b >= @a order by a; a 1 2 3 update t1 set a=5 where a=1; select a from t1; a 2 3 5 drop table t1; create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb; insert into t1 values("hello",1),("world",2); select * from t1 order by b desc; a b world 2 hello 1 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 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 a A # NULL NULL BTREE drop table t1; create table t1 (i int, j int ) ENGINE=innodb; insert into t1 values (1,2); select * from t1 where i=1 and j=2; i j 1 2 create index ax1 on t1 (i,j); select * from t1 where i=1 and j=2; i j 1 2 drop table t1; CREATE TABLE t1 ( a int3 unsigned NOT NULL, b int1 unsigned NOT NULL, UNIQUE (a, b) ) ENGINE = innodb; INSERT INTO t1 VALUES (1, 1); SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; MIN(B) MAX(b) 1 1 drop table t1; create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb; insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); explain select * from t1 where a > 0 and a < 50; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where 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`,`test`.`t1`.`e` AS `e`,`test`.`t1`.`f` AS `f`,`test`.`t1`.`g` AS `g`,`test`.`t1`.`h` AS `h`,`test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j`,`test`.`t1`.`k` AS `k`,`test`.`t1`.`l` AS `l`,`test`.`t1`.`m` AS `m`,`test`.`t1`.`n` AS `n`,`test`.`t1`.`o` AS `o`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`q` AS `q`,`test`.`t1`.`r` AS `r`,`test`.`t1`.`s` AS `s`,`test`.`t1`.`t` AS `t`,`test`.`t1`.`u` AS `u`,`test`.`t1`.`v` AS `v`,`test`.`t1`.`w` AS `w`,`test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t1`.`z` AS `z`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`a3` AS `a3`,`test`.`t1`.`a4` AS `a4`,`test`.`t1`.`a5` AS `a5`,`test`.`t1`.`a6` AS `a6`,`test`.`t1`.`a7` AS `a7`,`test`.`t1`.`a8` AS `a8`,`test`.`t1`.`a9` AS `a9`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`b2` AS `b2`,`test`.`t1`.`b3` AS `b3`,`test`.`t1`.`b4` AS `b4`,`test`.`t1`.`b5` AS `b5`,`test`.`t1`.`b6` AS `b6` from `test`.`t1` where ((`test`.`t1`.`a` > 0) and (`test`.`t1`.`a` < 50)) drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; insert into t1 values (99,1,2,'D'),(1,1,2,'D'); ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' select id from t1; id 0 1 2 select id from t1; id 0 1 2 UNLOCK TABLES; DROP TABLE t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; begin; insert into t1 values (99,1,2,'D'),(1,1,2,'D'); ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' select id from t1; id 0 1 2 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); Warnings: Warning 1062 Duplicate entry '1-1' for key 'PRIMARY' commit; select id,id3 from t1; id id3 0 0 1 1 2 2 100 2 UNLOCK TABLES; DROP TABLE t1; create table t1 (a char(20), unique (a(5))) engine=innodb; drop table t1; create table t1 (a char(20), index (a(5))) engine=innodb; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(20) DEFAULT NULL, KEY `a` (`a`(5)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb; insert into t1 values (NULL),(NULL),(NULL); delete from t1 where a=3; insert into t1 values (NULL); select * from t1; a 1 2 4 alter table t1 add b int; select * from t1; a b 1 NULL 2 NULL 4 NULL drop table t1; create table t1 ( id int auto_increment primary key, name varchar(32) not null, value text not null, uid int not null, unique key(name,uid) ) engine=innodb; insert into t1 values (1,'one','one value',101), (2,'two','two value',102),(3,'three','three value',103); set insert_id=5; replace into t1 (value,name,uid) values ('other value','two',102); delete from t1 where uid=102; set insert_id=5; replace into t1 (value,name,uid) values ('other value','two',102); set insert_id=6; replace into t1 (value,name,uid) values ('other value','two',102); select * from t1; id name value uid 1 one one value 101 3 three three value 103 6 two other value 102 drop table t1; create database mysqltest; create table mysqltest.t1 (a int not null) engine= innodb; insert into mysqltest.t1 values(1); create table mysqltest.t2 (a int not null) engine= myisam; insert into mysqltest.t2 values(1); create table mysqltest.t3 (a int not null) engine= heap; insert into mysqltest.t3 values(1); commit; drop database mysqltest; show tables from mysqltest; ERROR 42000: Unknown database 'mysqltest' set autocommit=0; create table t1 (a int not null) engine= innodb; insert into t1 values(1),(2); truncate table t1; commit; truncate table t1; truncate table t1; select * from t1; a insert into t1 values(1),(2); delete from t1; select * from t1; a commit; drop table t1; set autocommit=1; create table t1 (a int not null) engine= innodb; insert into t1 values(1),(2); truncate table t1; insert into t1 values(1),(2); select * from t1; a 1 2 truncate table t1; insert into t1 values(1),(2); delete from t1; select * from t1; a drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); 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 # 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 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 # 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` from `test`.`t1` order by `test`.`t1`.`b` explain select * from t1 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 # 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` from `test`.`t1` order by `test`.`t1`.`c` explain select a 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 # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` explain select b 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 b 4 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b` explain select a,b 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 b 4 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b` explain select a,b from t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` explain select a,b,c 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`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` drop table t1; create table t1 (t int not null default 1, key (t)) engine=innodb; desc t1; Field Type Null Key Default Extra t int(11) NO MUL 1 drop table t1; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. CREATE TABLE t1 ( number bigint(20) NOT NULL default '0', cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', last_mod_date timestamp NOT NULL, last_mod_id smallint(6) NOT NULL default '0', last_app_date timestamp NOT NULL, last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', status tinyint(4) default '0' ) ENGINE=InnoDB; INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1); INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0); INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1); INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0); INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0); INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0); CREATE TABLE t2 ( number bigint(20) NOT NULL default '0', cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', last_mod_date timestamp NOT NULL, last_mod_id smallint(6) NOT NULL default '0', last_app_date timestamp NOT NULL, last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', status tinyint(4) default '0' ) ENGINE=InnoDB; INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1); INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0); INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1); INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0); select * from t1; number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 4077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1 9197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0 650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1 302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0 6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 select * from t2; number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 4077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1 9197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0 650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1 333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null); select * from t1; number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0 333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0 select * from t2; number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 select * from t2; number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0 drop table t1,t2; SET sql_mode = default; create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb; BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT @@tx_isolation,@@global.tx_isolation; @@tx_isolation @@global.tx_isolation SERIALIZABLE REPEATABLE-READ Warnings: Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); select id, code, name from t1 order by id; id code name 1 1 Tim 2 1 Monty 3 2 David COMMIT; BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); select id, code, name from t1 order by id; id code name 1 1 Tim 2 1 Monty 3 2 David 4 2 Erik 5 3 Sasha COMMIT; SET binlog_format='MIXED'; BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); select id, code, name from t1 order by id; id code name 1 1 Tim 2 1 Monty 3 2 David 4 2 Erik 5 3 Sasha 6 3 Jeremy 7 4 Matt COMMIT; DROP TABLE t1; create table t1 (n int(10), d int(10)) engine=innodb; create table t2 (n int(10), d int(10)) engine=innodb; insert into t1 values(1,1),(1,2); insert into t2 values(1,10),(2,20); UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; select * from t1; n d 1 10 1 10 select * from t2; n d 1 30 2 20 drop table t1,t2; drop table if exists t1, t2; CREATE TABLE t1 (a int, PRIMARY KEY (a)); CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; create trigger trg_del_t2 after delete on t2 for each row insert into t1 values (1); insert into t1 values (1); insert into t2 values (1),(2); delete t2 from t2; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' select count(*) from t2 /* must be 2 as restored after rollback caused by the error */; count(*) 2 drop table t1, t2; drop table if exists t1, t2; CREATE TABLE t1 (a int, PRIMARY KEY (a)); CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; create trigger trg_del_t2 after delete on t2 for each row insert into t1 values (1); insert into t1 values (1); insert into t2 values (1),(2); delete t2 from t2; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' select count(*) from t2 /* must be 2 as restored after rollback caused by the error */; count(*) 2 drop table t1, t2; create table t1 (a int, b int) engine=innodb; insert into t1 values(20,null); select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on t2.b=t3.a; b ifnull(t2.b,"this is null") NULL this is null select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on t2.b=t3.a order by 1; b ifnull(t2.b,"this is null") NULL this is null insert into t1 values(10,null); select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on t2.b=t3.a order by 1; b ifnull(t2.b,"this is null") NULL this is null NULL this is null drop table t1; create table t1 (a varchar(10) not null) engine=myisam; create table t2 (b varchar(10) not null unique) engine=innodb; select t1.a from t1,t2 where t1.a=t2.b; a drop table t1,t2; create table t1 (a int not null, b int, primary key (a)) engine = innodb; create table t2 (a int not null, b int, primary key (a)) engine = innodb; insert into t1 values (10, 20); insert into t2 values (10, 20); update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10; drop table t1,t2; CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=INNODB; insert into t1 set id=1; insert into t2 set id=1, t1_id=1; delete t1,t2 from t1,t2 where t1.id=t2.t1_id; select * from t1; id select * from t2; id t1_id drop table t2,t1; CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB; INSERT INTO t1 VALUES(1); INSERT INTO t2 VALUES(1, 1); SELECT * from t1; id 1 UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1; SELECT * from t1; id 2 UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id; SELECT * from t1; id 3 DROP TABLE t1,t2; set autocommit=0; CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB; INSERT INTO t3 VALUES("my-test-1", "my-test-2"); COMMIT; INSERT INTO t1 VALUES("this-key", "will disappear"); INSERT INTO t2 VALUES("this-key", "will also disappear"); DELETE FROM t3 WHERE id1="my-test-1"; SELECT * FROM t1; id value this-key will disappear SELECT * FROM t2; id value this-key will also disappear SELECT * FROM t3; id1 id2 ROLLBACK; SELECT * FROM t1; id value SELECT * FROM t2; id value SELECT * FROM t3; id1 id2 my-test-1 my-test-2 SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE; id1 id2 my-test-1 my-test-2 COMMIT; set autocommit=1; DROP TABLE t1,t2,t3; CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb; INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000; SELECT * from t1; a b 1 1 102 2 103 3 4 4 5 5 6 6 7 7 8 8 9 9 drop table t1; CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM; CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB; SET AUTOCOMMIT=0; INSERT INTO t1 ( B_ID ) VALUES ( 1 ); INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back SELECT * FROM t1; B_ID drop table t1,t2; create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb; insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); select distinct parent,child from t1 order by parent; parent child 0 4 1 2 1 3 2 1 drop table t1; create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb; create table t2 (a int not null auto_increment primary key, b int); insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); insert into t2 (a) select b from t1; insert into t1 (b) select b from t2; insert into t2 (a) select b from t1; insert into t1 (a) select b from t2; insert into t2 (a) select b from t1; insert into t1 (a) select b from t2; insert into t2 (a) select b from t1; insert into t1 (a) select b from t2; insert into t2 (a) select b from t1; insert into t1 (a) select b from t2; select count(*) from t1; count(*) 623 explain select * from t1 where c between 1 and 2500; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range c c 5 NULL # 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` between 1 and 2500) update t1 set c=a; explain select * from t1 where c between 1 and 2500; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL c NULL NULL NULL # # Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` between 1 and 2500) drop table t1,t2; create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb; insert into t1 (id) values (null),(null),(null),(null),(null); update t1 set fk=69 where fk is null order by id limit 1; SELECT * from t1; id fk 2 NULL 3 NULL 4 NULL 5 NULL 1 69 drop table t1; create table t1 (a int not null, b int not null, key (a)); insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3); SET @tmp=0; update t1 set b=(@tmp:=@tmp+1) order by a; update t1 set b=99 where a=1 order by b asc limit 1; update t1 set b=100 where a=1 order by b desc limit 2; update t1 set a=a+10+b where a=1 order by b; select * from t1 order by a,b; a b 2 4 2 5 2 6 3 7 3 8 3 9 3 10 3 11 3 12 13 2 111 100 111 100 drop table t1; create table t1 ( c char(8) not null ) engine=innodb; insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); alter table t1 add b char(8) not null; alter table t1 add a char(8) not null; alter table t1 add primary key (a,b,c); update t1 set a=c, b=c; create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb; insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; drop table t1,t2; SET AUTOCOMMIT=1; create table t1 (a integer auto_increment primary key) engine=innodb; insert into t1 (a) values (NULL),(NULL); truncate table t1; insert into t1 (a) values (NULL),(NULL); SELECT * from t1; a 1 2 drop table t1; CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB; CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=INNODB; drop table t2,t1; create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb; insert into `t1`values ( 1 ) ; create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = innodb; insert into `t2`values ( 1 ) ; create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb; insert into `t3`values ( 1 ) ; delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; ERROR 42S22: Unknown column 't1.id' in 'where clause' drop table t3,t2,t1; CREATE TABLE t1 ( c1 VARCHAR(8), c2 VARCHAR(8), PRIMARY KEY (c1, c2) ) ENGINE=InnoDB; CREATE TABLE t2 ( c0 INT PRIMARY KEY, c1 VARCHAR(8) UNIQUE, FOREIGN KEY (c1) REFERENCES t1 (c1) ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO t1 VALUES ('old', 'somevalu'), ('other', 'anyvalue'); INSERT INTO t2 VALUES (10, 'old'), (20, 'other'); UPDATE t1 SET c1 = 'other' WHERE c1 = 'old'; ERROR 23000: Foreign key constraint for table 't1', record 'other-somevalu' would lead to a duplicate entry in table 't2', key 'c1' DROP TABLE t2,t1; call mtr.add_suppression("Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop excessive foreign constraints and try again"); create table t1( id int primary key, pid int, index(pid), foreign key(pid) references t1(id) on delete cascade) engine=innodb; insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6), (8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14); delete from t1 where id=0; ERROR HY000: Foreign key cascade delete/update exceeds max depth of 15. delete from t1 where id=15; delete from t1 where id=0; drop table t1; CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB; CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx (stamp))ENGINE=InnoDB; insert into t1 values (1),(2),(3); insert ignore into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000); Warnings: Warning 1265 Data truncated for column 'stamp' at row 3 SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < '20020204120000' GROUP BY col1; col1 1 2 3 4 drop table t1,t2; CREATE TABLE t1 ( `id` int(10) unsigned NOT NULL auto_increment, `id_object` int(10) unsigned default '0', `id_version` int(10) unsigned NOT NULL default '1', `label` varchar(100) NOT NULL default '', `description` text, PRIMARY KEY (`id`), KEY `id_object` (`id_object`), KEY `id_version` (`id_version`) ) ENGINE=InnoDB; INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL); CREATE TABLE t2 ( `id` int(10) unsigned NOT NULL auto_increment, `id_version` int(10) unsigned NOT NULL default '1', PRIMARY KEY (`id`), KEY `id_version` (`id_version`) ) ENGINE=InnoDB; INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9"); SELECT t2.id, t1.`label` FROM t2 INNER JOIN (SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object) ORDER BY 1; id label 102 Le Pekin (Test) 1794 Test de resto 1822 Test 3 3382 Test 3524 Societe Test 3525 Fournisseur Test drop table t1,t2; create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam; create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb; create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb; insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); insert t2 select * from t1; insert t3 select * from t1; checksum table t1, t2, t3, t4 quick; Table Checksum test.t1 2948697075 test.t2 NULL test.t3 NULL test.t4 NULL Warnings: Error 1146 Table 'test.t4' doesn't exist checksum table t1, t2, t3, t4; Table Checksum test.t1 2948697075 test.t2 2948697075 test.t3 2948697075 test.t4 NULL Warnings: Error 1146 Table 'test.t4' doesn't exist checksum table t1, t2, t3, t4 extended; Table Checksum test.t1 2948697075 test.t2 2948697075 test.t3 2948697075 test.t4 NULL Warnings: Error 1146 Table 'test.t4' doesn't exist drop table t1,t2,t3; create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb; insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt'); select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1; trim(name2) fff sss ttt first second third 1 2 3 drop table t1; create table t1 (a int) engine=innodb; create table t2 like t1; drop table t1,t2; create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb; create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, KEY `t1_id_fk` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 create index id on t2 (id); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, KEY `id` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 create index id2 on t2 (id); Warnings: Warning 1831 Duplicate index 'id2' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release. show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, KEY `id` (`id`), KEY `id2` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop index id2 on t2; drop index id on t2; ERROR HY000: Cannot drop index 'id': needed in a foreign key constraint show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, KEY `id` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = innodb; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, `id2` int(11) NOT NULL, KEY `t1_id_fk` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 create unique index id on t2 (id,id2); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`), KEY `t1_id_fk` (`id2`,`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = innodb; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`), KEY `t1_id_fk` (`id2`,`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = innodb; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id2` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `id` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= innodb; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id2` int(11) NOT NULL, KEY `t1_id_fk` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t2 add index id_test (id), add index id_test2 (id,id2); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id2` int(11) NOT NULL, KEY `id_test` (`id`), KEY `id_test2` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb; ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b_2` (`b`), KEY `b` (`b`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2; create table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=innodb; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`), CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2, t1; create table t1 (c char(10), index (c,c)) engine=innodb; ERROR 42S21: Duplicate column name 'c' create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb; ERROR 42S21: Duplicate column name 'c1' create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb; ERROR 42S21: Duplicate column name 'c1' create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb; ERROR 42S21: Duplicate column name 'c1' create table t1 (c1 char(10), c2 char(10)) engine=innodb; alter table t1 add key (c1,c1); ERROR 42S21: Duplicate column name 'c1' alter table t1 add key (c2,c1,c1); ERROR 42S21: Duplicate column name 'c1' alter table t1 add key (c1,c2,c1); ERROR 42S21: Duplicate column name 'c1' alter table t1 add key (c1,c1,c2); ERROR 42S21: Duplicate column name 'c1' drop table t1; CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES (1),(2),(3); CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a), CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; a_id b_list 1 1,2,3 2 4,5 3 NULL DROP TABLE t2; DROP TABLE t1; create temporary table t1 (a int) engine=innodb; insert into t1 values (4711); truncate t1; insert into t1 values (42); select * from t1; a 42 drop table t1; create table t1 (a int) engine=innodb; insert into t1 values (4711); truncate t1; insert into t1 values (42); select * from t1; a 42 drop table t1; create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=innodb; insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3); analyze table t1; select * from t1 order by a,b,c,d; a b c d e 1 1 a 1 1 2 2 b 2 2 3 3 ab 3 3 explain select * from t1 order by a,b,c,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 3 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`,`test`.`t1`.`e` AS `e` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t1`.`c`,`test`.`t1`.`d` drop table t1; create table t1 (a char(1), b char(1), key(a, b)) engine=innodb; insert into t1 values ('8', '6'), ('4', '7'); select min(a) from t1; min(a) 4 select min(b) from t1 where a='8'; min(b) 6 drop table t1; create table t1 (x bigint unsigned not null primary key) engine=innodb; insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); select * from t1; x 18446744073709551600 18446744073709551601 select count(*) from t1 where x>0; count(*) 2 select count(*) from t1 where x=0; count(*) 0 select count(*) from t1 where x<0; count(*) 0 select count(*) from t1 where x < -16; count(*) 0 select count(*) from t1 where x = -16; count(*) 0 explain select count(*) from t1 where x > -16; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 2 50.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`x` > <cache>(-(16))) select count(*) from t1 where x > -16; count(*) 2 select * from t1 where x > -16; x 18446744073709551600 18446744073709551601 select count(*) from t1 where x = 18446744073709551601; count(*) 1 drop table t1; SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total'; variable_value {checked_valid} SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; variable_value {checked_valid} SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'; variable_value - @innodb_rows_deleted_orig 71 SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; variable_value - @innodb_rows_inserted_orig 966 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; variable_value - @innodb_rows_updated_orig 866 SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits'; variable_value - @innodb_row_lock_waits_orig 0 SELECT variable_value - @innodb_row_lock_current_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits'; variable_value - @innodb_row_lock_current_waits_orig 0 SELECT variable_value - @innodb_row_lock_time_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time'; variable_value - @innodb_row_lock_time_orig 0 SELECT variable_value - @innodb_row_lock_time_max_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max'; variable_value - @innodb_row_lock_time_max_orig 0 SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg'; variable_value - @innodb_row_lock_time_avg_orig 0 SET @innodb_sync_spin_loops_orig = @@innodb_sync_spin_loops; show variables like "innodb_sync_spin_loops"; Variable_name Value innodb_sync_spin_loops 30 set global innodb_sync_spin_loops=1000; show variables like "innodb_sync_spin_loops"; Variable_name Value innodb_sync_spin_loops 1000 set global innodb_sync_spin_loops=0; show variables like "innodb_sync_spin_loops"; Variable_name Value innodb_sync_spin_loops 0 set global innodb_sync_spin_loops=20; show variables like "innodb_sync_spin_loops"; Variable_name Value innodb_sync_spin_loops 20 set global innodb_sync_spin_loops=@innodb_sync_spin_loops_orig; show variables like "innodb_thread_concurrency"; Variable_name Value innodb_thread_concurrency 0 set global innodb_thread_concurrency=1001; Warnings: Warning 1292 Truncated incorrect innodb_thread_concurrency value: '1001' show variables like "innodb_thread_concurrency"; Variable_name Value innodb_thread_concurrency 1000 set global innodb_thread_concurrency=0; show variables like "innodb_thread_concurrency"; Variable_name Value innodb_thread_concurrency 0 set global innodb_thread_concurrency=16; show variables like "innodb_thread_concurrency"; Variable_name Value innodb_thread_concurrency 16 show variables like "innodb_concurrency_tickets"; Variable_name Value innodb_concurrency_tickets 5000 set global innodb_concurrency_tickets=1000; show variables like "innodb_concurrency_tickets"; Variable_name Value innodb_concurrency_tickets 1000 set global innodb_concurrency_tickets=0; Warnings: Warning 1292 Truncated incorrect innodb_concurrency_tickets value: '0' show variables like "innodb_concurrency_tickets"; Variable_name Value innodb_concurrency_tickets 1 set global innodb_concurrency_tickets=5000; show variables like "innodb_concurrency_tickets"; Variable_name Value innodb_concurrency_tickets 5000 show variables like "innodb_thread_sleep_delay"; Variable_name Value innodb_thread_sleep_delay 10000 set global innodb_thread_sleep_delay=100000; show variables like "innodb_thread_sleep_delay"; Variable_name Value innodb_thread_sleep_delay 100000 set global innodb_thread_sleep_delay=0; show variables like "innodb_thread_sleep_delay"; Variable_name Value innodb_thread_sleep_delay 0 set global innodb_thread_sleep_delay=10000; show variables like "innodb_thread_sleep_delay"; Variable_name Value innodb_thread_sleep_delay 10000 set default_storage_engine=INNODB; set session old_alter_table=1; drop table if exists t1,t2,t3; --- Testing varchar --- --- Testing varchar --- create table t1 (v varchar(10), c char(10), t text); insert into t1 values('+ ', '+ ', '+ '); set @a=repeat(' ',20); insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); Warnings: Note 1265 Data truncated for column 'v' at row 1 select concat('*',v,'*',c,'*',t,'*') from t1; concat('*',v,'*',c,'*',t,'*') *+ *+*+ * *+ *+*+ * show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 create table t2 like t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 create table t3 select * from t1; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t1 modify c varchar(10); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, `t` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t1 modify v char(10); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` char(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, `t` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t1 modify t varchar(10); Warnings: Note 1265 Data truncated for column 't' at row 2 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` char(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, `t` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 select concat('*',v,'*',c,'*',t,'*') from t1; concat('*',v,'*',c,'*',t,'*') *+*+*+ * *+*+*+ * drop table t1,t2,t3; create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))) stats_persistent=0; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `v` (`v`), KEY `c` (`c`), KEY `t` (`t`(10)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 select count(*) from t1; count(*) 270 insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); select count(*) from t1 where v='a'; count(*) 10 select count(*) from t1 where c='a'; count(*) 10 select count(*) from t1 where t='a'; count(*) 10 select count(*) from t1 where v='a '; count(*) 10 select count(*) from t1 where c='a '; count(*) 10 select count(*) from t1 where t='a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; count(*) 10 select count(*) from t1 where v like 'a%'; count(*) 11 select count(*) from t1 where c like 'a%'; count(*) 11 select count(*) from t1 where t like 'a%'; count(*) 11 select count(*) from t1 where v like 'a %'; count(*) 9 explain select count(*) from t1 where v='a '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 13 const # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` = 'a ') explain select count(*) from t1 where c='a '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref c c 11 const # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`c` = 'a ') explain select count(*) from t1 where t='a '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref t t 13 const # 100.00 Using where Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`t` = 'a ') explain select count(*) from t1 where v like 'a%'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range v v 13 NULL # 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` like 'a%') explain select count(*) from t1 where v between 'a' and 'a '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 13 const # 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` between 'a' and 'a ') explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 13 const # 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`v` between 'a' and 'a ') and (`test`.`t1`.`v` between 'a ' and 'b\n')) alter table t1 add unique(v); ERROR 23000: Duplicate entry '{ ' for key 'v_2' alter table t1 add key(v); Warnings: Warning 1831 Duplicate index 'v_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; qq *a*a*a* *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * *a *a*a * explain select * from t1 where v='a'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v,v_2 # 13 const # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`t` AS `t` from `test`.`t1` where (`test`.`t1`.`v` = 'a') select v,count(*) from t1 group by v limit 10; v count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(c) from t1 group by v limit 10; v count(c) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(c) from t1 group by v limit 10; v count(c) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select c,count(*) from t1 group by c limit 10; c count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select c,count(t) from t1 group by c limit 10; c count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result c,count(t) from t1 group by c limit 10; c count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select t,count(*) from t1 group by t limit 10; t count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select t,count(t) from t1 group by t limit 10; t count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result t,count(t) from t1 group by t limit 10; t count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(300) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `c` (`c`), KEY `t` (`t`(10)), KEY `v` (`v`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 select count(*) from t1 where v='a'; count(*) 10 select count(*) from t1 where v='a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; count(*) 10 select count(*) from t1 where v like 'a%'; count(*) 11 select count(*) from t1 where v like 'a %'; count(*) 9 explain select count(*) from t1 where v='a '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 303 const # 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` = 'a ') explain select count(*) from t1 where v like 'a%'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range v v 303 NULL # 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` like 'a%') explain select count(*) from t1 where v between 'a' and 'a '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 303 const # 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` between 'a' and 'a ') explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 303 const # 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`v` between 'a' and 'a ') and (`test`.`t1`.`v` between 'a ' and 'b\n')) explain select * from t1 where v='a'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 303 const # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`t` AS `t` from `test`.`t1` where (`test`.`t1`.`v` = 'a') select v,count(*) from t1 group by v limit 10; v count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 alter table t1 drop key v, add key v (v(30)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(300) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `c` (`c`), KEY `t` (`t`(10)), KEY `v` (`v`(30)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 select count(*) from t1 where v='a'; count(*) 10 select count(*) from t1 where v='a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a '; count(*) 10 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; count(*) 10 select count(*) from t1 where v like 'a%'; count(*) 11 select count(*) from t1 where v like 'a %'; count(*) 9 explain select count(*) from t1 where v='a '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` = 'a ') explain select count(*) from t1 where v like 'a%'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range v v 33 NULL # 100.00 Using where Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` like 'a%') explain select count(*) from t1 where v between 'a' and 'a '; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`v` between 'a' and 'a ') explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where Warnings: Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`v` between 'a' and 'a ') and (`test`.`t1`.`v` between 'a ' and 'b\n')) explain select * from t1 where v='a'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref v v 33 const # 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`t` AS `t` from `test`.`t1` where (`test`.`t1`.`v` = 'a') select v,count(*) from t1 group by v limit 10; v count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 alter table t1 modify v varchar(600), drop key v, add key v (v); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(600) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `c` (`c`), KEY `t` (`t`(10)), KEY `v` (`v`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 select v,count(*) from t1 group by v limit 10; v count(*) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 a 10 b 10 c 10 d 10 e 10 f 10 g 10 h 10 i 10 drop table t1; create table t1 (a char(10), unique (a)); insert into t1 values ('a '); insert into t1 values ('a '); ERROR 23000: Duplicate entry 'a' for key 'a' alter table t1 modify a varchar(10); insert into t1 values ('a '),('a '),('a '),('a '); ERROR 23000: Duplicate entry 'a ' for key 'a' insert into t1 values ('a '); ERROR 23000: Duplicate entry 'a ' for key 'a' insert into t1 values ('a '); ERROR 23000: Duplicate entry 'a ' for key 'a' insert into t1 values ('a '); ERROR 23000: Duplicate entry 'a ' for key 'a' update t1 set a='a ' where a like 'a%'; select concat(a,'.') from t1; concat(a,'.') a . update t1 set a='abc ' where a like 'a '; select concat(a,'.') from t1; concat(a,'.') a . update t1 set a='a ' where a like 'a %'; select concat(a,'.') from t1; concat(a,'.') a . update t1 set a='a ' where a like 'a '; select concat(a,'.') from t1; concat(a,'.') a . drop table t1; create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL, `t` text, KEY `v` (`v`(5)), KEY `c` (`c`(5)), KEY `t` (`t`(5)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1 (v char(10) character set utf8); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` char(10) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1 (v varchar(10), c char(10)) row_format=fixed; Warnings: Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC. show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` varchar(10) DEFAULT NULL, `c` char(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED insert into t1 values('a','a'),('a ','a '); select concat('*',v,'*',c,'*') from t1; concat('*',v,'*',c,'*') *a*a* *a *a* drop table t1; create table t1 (v varchar(65530), key(v(10))); insert into t1 values(repeat('a',65530)); select length(v) from t1 where v=repeat('a',65530); length(v) 65530 drop table t1; create table t1(a int, b varchar(12), key ba(b, a)); insert into t1 values (1, 'A'), (20, NULL); explain select * from t1 where a=20 and b is null; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ref ba ba 20 const,const 1 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 20) and isnull(`test`.`t1`.`b`)) select * from t1 where a=20 and b is null; a b 20 NULL drop table t1; set session old_alter_table=0; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. SET GLOBAL innodb_large_prefix=OFF; Warnings: Warning 131 Using innodb_large_prefix is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html create table t1 (v varchar(65530), key(v)); Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes SET GLOBAL innodb_large_prefix=default; Warnings: Warning 131 Using innodb_large_prefix is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html drop table t1; create table t1 (v varchar(65536)); Warnings: Note 1246 Converting column 'v' from VARCHAR to TEXT show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` mediumtext ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1 (v varchar(65530) character set utf8); Warnings: Note 1246 Converting column 'v' from VARCHAR to TEXT show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `v` mediumtext CHARACTER SET utf8 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; SET sql_mode = default; set default_storage_engine=MyISAM; create table t1 (v varchar(16384)) engine=innodb; drop table t1; create table t1 (a char(1), b char(1), key(a, b)) engine=innodb; insert into t1 values ('8', '6'), ('4', '7'); select min(a) from t1; min(a) 4 select min(b) from t1 where a='8'; min(b) 6 drop table t1; CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb; insert into t1 (b) values (1); replace into t1 (b) values (2), (1), (3); select * from t1; a b 3 1 2 2 4 3 truncate table t1; insert into t1 (b) values (1); replace into t1 (b) values (2); replace into t1 (b) values (1); replace into t1 (b) values (3); select * from t1; a b 3 1 2 2 4 3 drop table t1; create table t1 (rowid int not null auto_increment, val int not null,primary key (rowid), unique(val)) engine=innodb; replace into t1 (val) values ('1'),('2'); replace into t1 (val) values ('1'),('2'); insert into t1 (val) values ('1'),('2'); ERROR 23000: Duplicate entry '1' for key 'val' select * from t1; rowid val 3 1 4 2 drop table t1; create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB; insert into t1 (val) values (1); update t1 set a=2 where a=1; insert into t1 (val) values (1); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' select * from t1; a val 2 1 drop table t1; CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB; INSERT INTO t1 (GRADE) VALUES (151),(252),(343); SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300; GRADE 252 SELECT GRADE FROM t1 WHERE GRADE= 151; GRADE 151 DROP TABLE t1; create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb; create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb; insert into t2 values ('aa','cc'); insert into t1 values ('aa','bb'),('aa','cc'); delete t1 from t1,t2 where f1=f3 and f4='cc'; select * from t1; f1 f2 drop table t1,t2; CREATE TABLE t1 ( id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB; INSERT INTO t1 (id) VALUES (NULL); SELECT * FROM t1; id 1 TRUNCATE t1; INSERT INTO t1 (id) VALUES (NULL); SELECT * FROM t1; id 1 DELETE FROM t1; TRUNCATE t1; INSERT INTO t1 (id) VALUES (NULL); SELECT * FROM t1; id 1 DROP TABLE t1; CREATE TABLE t1 ( id INT PRIMARY KEY ) ENGINE=InnoDB; CREATE TEMPORARY TABLE t2 ( id INT NOT NULL PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES test.t1(id) ) ENGINE=InnoDB; ERROR HY000: Cannot add foreign key constraint DROP TABLE t1; create table t1 (col1 varchar(2000), index (col1(767))) character set = latin1 engine = innodb; create table t2 (col1 char(255), index (col1)) character set = latin1 engine = innodb; create table t3 (col1 binary(255), index (col1)) character set = latin1 engine = innodb; create table t4 (col1 varchar(767), index (col1)) character set = latin1 engine = innodb; create table t5 (col1 varchar(767) primary key) character set = latin1 engine = innodb; create table t6 (col1 varbinary(767) primary key) character set = latin1 engine = innodb; create table t7 (col1 text, index(col1(767))) character set = latin1 engine = innodb; create table t8 (col1 blob, index(col1(767))) character set = latin1 engine = innodb; drop table t1, t2, t3, t4, t5, t6, t7, t8; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. SET GLOBAL innodb_large_prefix=OFF; Warnings: Warning 131 Using innodb_large_prefix is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html create table t1 (col1 varchar(768), index(col1)) character set = latin1 engine = innodb; Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes create table t2 (col1 varbinary(768), index(col1)) character set = latin1 engine = innodb; Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes create table t3 (col1 text, index(col1(768))) character set = latin1 engine = innodb; Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes create table t4 (col1 blob, index(col1(768))) character set = latin1 engine = innodb; Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes SET GLOBAL innodb_large_prefix=default; Warnings: Warning 131 Using innodb_large_prefix is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `col1` varchar(768) DEFAULT NULL, KEY `col1` (`col1`(767)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1, t2, t3, t4; set global innodb_large_prefix=OFF; Warnings: Warning 131 Using innodb_large_prefix is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html create table t1 (col1 varchar(768) primary key) character set = latin1 engine = innodb; ERROR 42000: Specified key was too long; max key length is 767 bytes create table t2 (col1 varbinary(768) primary key) character set = latin1 engine = innodb; ERROR 42000: Specified key was too long; max key length is 767 bytes create table t3 (col1 text, primary key(col1(768))) character set = latin1 engine = innodb; ERROR 42000: Specified key was too long; max key length is 767 bytes create table t4 (col1 blob, primary key(col1(768))) character set = latin1 engine = innodb; ERROR 42000: Specified key was too long; max key length is 767 bytes SET sql_mode = default; set global innodb_large_prefix=default; Warnings: Warning 131 Using innodb_large_prefix is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html CREATE TABLE t1 ( id INT PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE t2 ( v INT, CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id) ) ENGINE=InnoDB; INSERT INTO t2 VALUES(2); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) INSERT INTO t1 VALUES(1); INSERT INTO t2 VALUES(1); DELETE FROM t1 WHERE id = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) DROP TABLE t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails SET FOREIGN_KEY_CHECKS=0; DROP TABLE t1; SET FOREIGN_KEY_CHECKS=1; INSERT INTO t2 VALUES(3); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) DROP TABLE t2; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; insert into t1 values (1),(2); set autocommit=0; checksum table t1; Table Checksum test.t1 1531596814 insert into t1 values(3); checksum table t1; Table Checksum test.t1 1531596814 commit; checksum table t1; Table Checksum test.t1 2050879373 commit; drop table t1; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; insert into t1 values (1),(2); set autocommit=1; checksum table t1; Table Checksum test.t1 1531596814 set autocommit=1; insert into t1 values(3); checksum table t1; Table Checksum test.t1 2050879373 drop table t1; set foreign_key_checks=0; create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb; create table t1(a char(10) primary key, b varchar(20)) engine = innodb; ERROR HY000: Cannot add foreign key constraint set foreign_key_checks=1; drop table t2; set foreign_key_checks=0; create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8; ERROR HY000: Cannot add foreign key constraint set foreign_key_checks=1; drop table t1; set foreign_key_checks=0; create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb; create table t1(a varchar(10) primary key) engine = innodb; alter table t1 modify column a int; Got one of the listed errors set foreign_key_checks=1; drop table t2,t1; set foreign_key_checks=0; create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; alter table t1 convert to character set utf8; set foreign_key_checks=1; drop table t2,t1; set foreign_key_checks=0; create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8; rename table t3 to t1; ERROR HY000: Error on rename of './test/t3' to './test/t1' (errno: 150 - Foreign key constraint is incorrectly formed) set foreign_key_checks=1; drop table t2,t3; create table t1(a int primary key) row_format=redundant engine=innodb; create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb; create table t3(a int primary key) row_format=compact engine=innodb; create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb; insert into t1 values(1); insert into t3 values(1); insert into t2 values(2); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) insert into t4 values(2); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) insert into t2 values(1); insert into t4 values(1); update t1 set a=2; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) update t2 set a=2; ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) update t3 set a=2; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) update t4 set a=2; ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) truncate t1; ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`)) truncate t3; ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t3` (`a`)) truncate t2; truncate t4; truncate t1; ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`)) truncate t3; ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t3` (`a`)) drop table t4,t3,t2,t1; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb; create table t2 (s1 binary(2),primary key (s1)) engine=innodb; create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb; create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb; insert into t1 values (0x41),(0x4120),(0x4100); insert into t2 values (0x41),(0x4120),(0x4100); ERROR 23000: Duplicate entry 'A' for key 'PRIMARY' insert into t2 values (0x41),(0x4120); insert into t3 values (0x41),(0x4120),(0x4100); ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY' insert into t3 values (0x41),(0x4100); insert into t4 values (0x41),(0x4120),(0x4100); ERROR 23000: Duplicate entry 'A' for key 'PRIMARY' insert into t4 values (0x41),(0x4100); select hex(s1) from t1; hex(s1) 41 4100 4120 select hex(s1) from t2; hex(s1) 4100 4120 select hex(s1) from t3; hex(s1) 4100 41 select hex(s1) from t4; hex(s1) 4100 41 drop table t1,t2,t3,t4; create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb; create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb; insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42); insert into t2 values(0x42); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) insert into t2 values(0x41); select hex(s1) from t2; hex(s1) 4100 update t1 set s1=0x123456 where a=2; select hex(s1) from t2; hex(s1) 4100 update t1 set s1=0x12 where a=1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) update t1 set s1=0x12345678 where a=1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) update t1 set s1=0x123457 where a=1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) update t1 set s1=0x1220 where a=1; select hex(s1) from t2; hex(s1) 1220 update t1 set s1=0x1200 where a=1; select hex(s1) from t2; hex(s1) 1200 update t1 set s1=0x4200 where a=1; select hex(s1) from t2; hex(s1) 4200 delete from t1 where a=1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) delete from t1 where a=2; update t2 set s1=0x4120; delete from t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) delete from t1 where a!=3; select a,hex(s1) from t1; a hex(s1) 3 4120 select hex(s1) from t2; hex(s1) 4120 drop table t2,t1; create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb; create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb; insert into t1 values(1,0x4100),(2,0x41); insert into t2 values(0x41); select hex(s1) from t2; hex(s1) 41 update t1 set s1=0x1234 where a=1; select hex(s1) from t2; hex(s1) 41 update t1 set s1=0x12 where a=2; select hex(s1) from t2; hex(s1) 12 delete from t1 where a=1; delete from t1 where a=2; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) select a,hex(s1) from t1; a hex(s1) 2 12 select hex(s1) from t2; hex(s1) 12 drop table t2,t1; CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB; CREATE TABLE t2(a INT) ENGINE=InnoDB; ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a); ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1; ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a); ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, KEY `t2_ibfk_0` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t2,t1; SET sql_mode = default; CREATE TABLE t1 ( field1 varchar(8) NOT NULL DEFAULT '', field2 varchar(8) NOT NULL DEFAULT '', PRIMARY KEY (field1, field2) ) ENGINE=InnoDB; CREATE TABLE t2 ( field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY, FOREIGN KEY (field1) REFERENCES t1 (field1) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO t1 VALUES ('old', 'somevalu'); INSERT INTO t1 VALUES ('other', 'anyvalue'); INSERT INTO t2 VALUES ('old'); INSERT INTO t2 VALUES ('other'); UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu'; ERROR 23000: Foreign key constraint for table 't1', record 'other-somevalu' would lead to a duplicate entry in table 't2', key 'PRIMARY' DROP TABLE t2; DROP TABLE t1; create table t1 ( c1 bigint not null, c2 bigint not null, primary key (c1), unique key (c2) ) engine=innodb; create table t2 ( c1 bigint not null, primary key (c1) ) engine=innodb; alter table t1 add constraint c2_fk foreign key (c2) references t2(c1) on delete cascade; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` bigint(20) NOT NULL, `c2` bigint(20) NOT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `c2` (`c2`), CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t1 drop foreign key c2_fk; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` bigint(20) NOT NULL, `c2` bigint(20) NOT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `c2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1, t2; create table t1(a date) engine=innodb; create table t2(a date, key(a)) engine=innodb; insert into t1 values('2005-10-01'); insert into t2 values('2005-10-01'); select * from t1, t2 where t2.a between t1.a - interval 2 day and t1.a + interval 2 day; a a 2005-10-01 2005-10-01 drop table t1, t2; create table t1 (id int not null, f_id int not null, f int not null, primary key(f_id, id)) engine=innodb; create table t2 (id int not null,s_id int not null,s varchar(200), primary key(id)) engine=innodb; INSERT INTO t1 VALUES (8, 1, 3); INSERT INTO t1 VALUES (1, 2, 1); INSERT INTO t2 VALUES (1, 0, ''); INSERT INTO t2 VALUES (8, 1, ''); commit; DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id) WHERE mm.id IS NULL; select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) where mm.id is null lock in share mode; id f_id f drop table t1,t2; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); commit; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t1 set b = 5 where b = 1; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; select * from t1 where a = 7 and b = 3 for update; a b 7 3 commit; commit; drop table t1; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2); commit; set autocommit = 0; select * from t1 lock in share mode; a b 1 1 2 2 3 1 4 2 5 1 6 2 update t1 set b = 5 where b = 1; set autocommit = 0; select * from t1 where a = 2 and b = 2 for update; ERROR HY000: Lock wait timeout exceeded; try restarting transaction commit; commit; drop table t1; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values (1,2),(5,3),(4,2); create table t2(d int not null, e int, primary key(d)) engine=innodb; insert into t2 values (8,6),(12,1),(3,1); commit; set autocommit = 0; select * from t2 for update; d e 3 1 8 6 12 1 SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into t1 select * from t2; update t1 set b = (select e from t2 where a = d); create table t3(d int not null, e int, primary key(d)) engine=innodb select * from t2; commit; commit; drop table t1, t2, t3; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values (1,2),(5,3),(4,2); create table t2(a int not null, b int, primary key(a)) engine=innodb; insert into t2 values (8,6),(12,1),(3,1); create table t3(d int not null, b int, primary key(d)) engine=innodb; insert into t3 values (8,6),(12,1),(3,1); create table t5(a int not null, b int, primary key(a)) engine=innodb; insert into t5 values (1,2),(5,3),(4,2); create table t6(d int not null, e int, primary key(d)) engine=innodb; insert into t6 values (8,6),(12,1),(3,1); create table t8(a int not null, b int, primary key(a)) engine=innodb; insert into t8 values (1,2),(5,3),(4,2); create table t9(d int not null, e int, primary key(d)) engine=innodb; insert into t9 values (8,6),(12,1),(3,1); commit; set autocommit = 0; select * from t2 for update; a b 3 1 8 6 12 1 SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into t1 select * from t2; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; update t3 set b = (select b from t2 where a = d); SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into t5 (select * from t2 lock in share mode); SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t6 set e = (select b from t2 where a = d lock in share mode); SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into t8 (select * from t2 for update); SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t9 set e = (select b from t2 where a = d for update); SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update; ERROR HY000: Lock wait timeout exceeded; try restarting transaction ERROR HY000: Lock wait timeout exceeded; try restarting transaction ERROR HY000: Lock wait timeout exceeded; try restarting transaction ERROR HY000: Lock wait timeout exceeded; try restarting transaction ERROR HY000: Lock wait timeout exceeded; try restarting transaction ERROR HY000: Lock wait timeout exceeded; try restarting transaction ERROR HY000: Lock wait timeout exceeded; try restarting transaction ERROR HY000: Lock wait timeout exceeded; try restarting transaction ERROR HY000: Can't update table 't2' while 't10' is being created. commit; drop table t1, t2, t3, t5, t6, t8, t9; CREATE TABLE t1 (DB_ROW_ID int) engine=innodb; ERROR 42000: Incorrect column name 'DB_ROW_ID' CREATE TABLE t1 ( a BIGINT(20) NOT NULL, PRIMARY KEY (a) ) ENGINE=INNODB DEFAULT CHARSET=UTF8; CREATE TABLE t2 ( a BIGINT(20) NOT NULL, b VARCHAR(128) NOT NULL, c TEXT NOT NULL, PRIMARY KEY (a,b), KEY idx_t2_b_c (b,c(100)), CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=UTF8; INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1, 'bar', 'vbar'); INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR'); INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi'); INSERT INTO t2 VALUES (1, 'customer_over', '1'); SELECT * FROM t2 WHERE b = 'customer_over'; a b c 1 customer_over 1 SELECT * FROM t2 WHERE BINARY b = 'customer_over'; a b c 1 customer_over 1 SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over'; a 1 /* Bang: Empty result set, above was expected: */ SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; a 1 SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; a 1 drop table t2, t1; CREATE TABLE t1 ( a int ) ENGINE=innodb; BEGIN; INSERT INTO t1 VALUES (1); 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 DROP TABLE t1; CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB; CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL, CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB; ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON DELETE CASCADE ON UPDATE CASCADE; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, `f` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `f` (`f`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t2, t1; CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB; CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL; ALTER TABLE t2 MODIFY a INT NOT NULL; ERROR HY000: Column 'a' cannot be NOT NULL: needed in a foreign key constraint 'test/t2_ibfk_1' SET NULL DELETE FROM t1; DROP TABLE t2,t1; CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4); DELETE FROM t1; INSERT INTO t1 VALUES ('DDD'); SELECT * FROM t1; a DDD DROP TABLE t1; CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=42; INSERT INTO t1 VALUES (0),(347),(0); SELECT * FROM t1; id 42 347 348 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1 CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t2 VALUES(42),(347),(348); ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1 DROP TABLE t1,t2; SET innodb_strict_mode=ON; CREATE TABLE t1 ( c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255), c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255), c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255), c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255), c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255), c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255), c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255), c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255) ) ENGINE = InnoDB; ERROR 42000: Row size too large (> {checked_valid}). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. SET innodb_strict_mode=OFF; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 'test.t1' CREATE TABLE t1( id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO t1 VALUES(-10); SELECT * FROM t1; id -10 INSERT INTO t1 VALUES(NULL); SELECT * FROM t1; id -10 1 DROP TABLE t1; SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; Warnings: Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead SET AUTOCOMMIT=0; DROP TABLE IF EXISTS t1, t2; Warnings: Note 1051 Unknown table 'test.t1' Note 1051 Unknown table 'test.t2' CREATE TABLE t1 ( a int ) ENGINE=InnoDB; CREATE TABLE t2 LIKE t1; SELECT * FROM t2; a SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; Warnings: Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (1); COMMIT; SELECT * FROM t1 WHERE a=1; a 1 SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; Warnings: Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead SET AUTOCOMMIT=0; SELECT * FROM t2; a SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; Warnings: Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (2); COMMIT; SELECT * FROM t1 WHERE a=2; a 2 SELECT * FROM t1 WHERE a=2; a 2 DROP TABLE t1; DROP TABLE t2; create table t1 (i int, j int) engine=innodb; insert into t1 (i, j) values (1, 1), (2, 2); update t1 set j = 2; affected rows: 1 info: Rows matched: 2 Changed: 1 Warnings: 0 drop table t1; create table t1 (id int) comment='this is a comment' engine=innodb; select table_comment, data_free > 0 as data_free_is_set from information_schema.tables where table_schema='test' and table_name = 't1'; table_comment data_free_is_set this is a comment 1 drop table t1; CREATE TABLE t1 ( c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, c2 VARCHAR(128) NOT NULL, PRIMARY KEY(c1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100; CREATE TABLE t2 ( c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, c2 INT(10) UNSIGNED DEFAULT NULL, PRIMARY KEY(c1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200; SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2'; AUTO_INCREMENT 200 ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1); SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2'; AUTO_INCREMENT 200 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 (c1 int default NULL, c2 int default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; TRUNCATE TABLE t1; affected rows: 0 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); affected rows: 5 info: Records: 5 Duplicates: 0 Warnings: 0 TRUNCATE TABLE t1; affected rows: 0 DROP TABLE t1; Variable_name Value Handler_update 0 Variable_name Value Handler_delete 0 Variable_name Value Handler_update 1 Variable_name Value Handler_delete 1 DROP TABLE bug58912; create table t1 (f1 integer primary key) engine=innodb; flush status; show status like "handler_read_key"; Variable_name Value Handler_read_key 0 select f1 from t1; f1 show status like "handler_read_key"; Variable_name Value Handler_read_key 1 drop table t1; CREATE TABLE t1 (c1 INT) ENGINE=InnoDB; CREATE TEMPORARY TABLE t2 (c1 INT) ENGINE=InnoDB; START TRANSACTION READ ONLY; INSERT INTO t2 VALUES(0); INSERT INTO t1 VALUES(0); ERROR 25006: Cannot execute statement in a READ ONLY transaction. ROLLBACK; SELECT * FROM t1; c1 SELECT * FROM t2; c1 START TRANSACTION READ ONLY; INSERT INTO t1 VALUES(0); ERROR 25006: Cannot execute statement in a READ ONLY transaction. INSERT INTO t2 VALUES(1); COMMIT; SET TRANSACTION READ ONLY; START TRANSACTION; INSERT INTO t2 VALUES(3); INSERT INTO t1 VALUES(0); ERROR 25006: Cannot execute statement in a READ ONLY transaction. COMMIT; SELECT * FROM t1; c1 SELECT * FROM t2; c1 1 3 DROP TABLE t2; CREATE TEMPORARY TABLE t2 ( c1 INT AUTO_INCREMENT PRIMARY KEY, c2 INT, INDEX idx(c2)) ENGINE=InnoDB; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TEMPORARY TABLE `t2` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `idx` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 START TRANSACTION READ ONLY; INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); INSERT INTO t1 VALUES(0); ERROR 25006: Cannot execute statement in a READ ONLY transaction. ROLLBACK; SELECT * FROM t1; c1 SELECT * FROM t2; c1 c2 START TRANSACTION READ ONLY; INSERT INTO t1 VALUES(0); ERROR 25006: Cannot execute statement in a READ ONLY transaction. INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); COMMIT; SET TRANSACTION READ ONLY; START TRANSACTION; INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); INSERT INTO t1 VALUES(0); ERROR 25006: Cannot execute statement in a READ ONLY transaction. COMMIT; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TEMPORARY TABLE `t2` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `idx` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 SELECT * FROM t2; c1 c2 4 1 7 1 5 2 8 2 6 3 9 3 DROP TABLE t1; DROP TABLE t2; count_alloc WHERE event_name like 'memory/innodb/other' count_alloc 0
Close
