Linux ip-172-26-2-223 5.4.0-1018-aws #18-Ubuntu SMP Wed Jun 24 01:15:00 UTC 2020 x86_64
Apache
: 172.26.2.223 | : 3.15.221.165
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 : create_tablespace.result
# # CREATE TABLESPACE related tests. # SET DEFAULT_STORAGE_ENGINE=InnoDB; SET NAMES utf8; # Strict-mode has no effect on CREATE TABLESPACE. # It rejects all invalid input, as if strict mode is always ON. SHOW VARIABLES LIKE 'innodb_strict_mode'; Variable_name Value innodb_strict_mode ON # Neither file_format=antelope nor file_per_table=OFF will prevent # CREATE TABLESPACE from working because the tablespace is an empty # shell that can contain multiple row formats. SET GLOBAL innodb_file_format = 'antelope'; Warnings: Warning 131 Using innodb_file_format 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 VARIABLES LIKE 'innodb_file_format'; Variable_name Value innodb_file_format Antelope SHOW VARIABLES LIKE 'innodb_file_per_table'; Variable_name Value innodb_file_per_table ON # # Try to create a tablespace without specifying the name # CREATE TABLESPACE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 # # Try to create a tablespace without specifying the datafile # CREATE TABLESPACE s_bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 # # Try to create a tablespace with bad characters in the tablespace name identifier. # CREATE TABLESPACE 's_bad' ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''s_bad' ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE "s_bad" ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"s_bad" ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE `` ADD DATAFILE 's_bad.ibd'; ERROR 42000: Incorrect tablespace name `` CREATE TABLESPACE s#bad ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 CREATE TABLESPACE s@bad ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@bad ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE s-bad ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-bad ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE test/s_bad ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/s_bad ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. CREATE TABLESPACE `s_too_long_file_name` ADD DATAFILE '../xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.ibd'; ERROR HY000: Failed to create TABLESPACE s_too_long_file_name CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=1k FILE_BLOCK_SIZE=2k; ERROR HY000: It is not allowed to specify FILE_BLOCK_SIZE more than once CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=3k; ERROR HY000: InnoDB does not support FILE_BLOCK_SIZE=3072 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=65k; ERROR HY000: InnoDB does not support FILE_BLOCK_SIZE=66560 CREATE TABLESPACE `s_bad` ADD DATAFILE 'sub/dir/////s_bad.ibd'; ERROR HY000: Incorrect File Name 'sub/dir/////s_bad.ibd'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name 'sub/dir/////s_bad.ibd'. Error 3121 The directory does not exist. Error 1528 Failed to create TABLESPACE s_bad Error 1031 Table storage engine for 's_bad' doesn't have this option DROP TABLESPACE 's_bad'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''s_bad'' at line 1 DROP TABLESPACE "s_bad"; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"s_bad"' at line 1 DROP TABLESPACE ``; ERROR 42000: Incorrect tablespace name `` SHOW WARNINGS; Level Code Message Error 3119 Incorrect tablespace name `` DROP TABLESPACE s#bad; ERROR HY000: Failed to drop TABLESPACE s DROP TABLESPACE s@bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@bad' at line 1 DROP TABLESPACE s-bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-bad' at line 1 DROP TABLESPACE `test/s_bad`; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. DROP TABLESPACE s_does_not_exist; ERROR HY000: Failed to drop TABLESPACE s_does_not_exist SHOW WARNINGS; Level Code Message Error 1529 Failed to drop TABLESPACE s_does_not_exist Error 1812 Tablespace is missing for table s_does_not_exist. # # InnoDB does not allow General tablespace names with '/' # CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 1528 Failed to create TABLESPACE test/s_bad Error 1031 Table storage engine for 'test/s_bad' doesn't have this option CREATE TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` ADD DATAFILE 's_utf8.ibd'; CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` (a int, b text) TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `; INSERT INTO `t !@#$%^&*()_+-={}[]|\?<>,.` VALUES(1,'one'); SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; Table Create Table t !@#$%^&*()_+-={}[]|\?<>,. CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=latin1; SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; Table Create Table t !@#$%^&*()_+-={}[]|\?<>,. CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2`; CREATE TABLESPACE `#sql_1` ADD DATAFILE '#sql_1.ibd'; CREATE TABLE `#sql_1` (a int, b text) TABLESPACE `#sql_1`; INSERT INTO `#sql_1` VALUES(1,'one'); SHOW CREATE TABLE `#sql_1`; Table Create Table #sql_1 CREATE TABLE `#sql_1` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `#sql_1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLESPACE `s_Cöŀumň` ADD DATAFILE 's_utf8_a.ibd'; CREATE TABLE `t_utf8_1` (a int, b text) TABLESPACE `s_Cöŀumň`; SHOW CREATE TABLE `t_utf8_1`; Table Create Table t_utf8_1 CREATE TABLE `t_utf8_1` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_Cöŀumň` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLESPACE `s_cöĿǖmň` ADD DATAFILE 's_utf8_b.ibd'; CREATE TABLE `t_utf8_2` (a int, b text) TABLESPACE `s_cöĿǖmň`; SHOW CREATE TABLE `t_utf8_2`; Table Create Table t_utf8_2 CREATE TABLE `t_utf8_2` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_cöĿǖmň` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLESPACE `s_😲` ADD DATAFILE 's_utf8_c.ibd'; ERROR HY000: Invalid utf8 character string: 's_\xF0\x9F\x98\xB2' CREATE TABLESPACE `s_𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜` ADD DATAFILE 's_utf8_d.ibd'; ERROR HY000: Invalid utf8 character string: 's_\xF0\x90\x8C\x80\xF0\x90\x8C\x81\xF0\x90\x8C\x82\xF0\x90\x8C\x' CREATE TABLESPACE `s_வணக்கம்` ADD DATAFILE 'ஆவணம்.ibd'; CREATE TABLE `t_utf8_3` (a int, b text) TABLESPACE `s_வணக்கம்`; SHOW CREATE TABLE `t_utf8_3`; Table Create Table t_utf8_3 CREATE TABLE `t_utf8_3` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_வணக்கம்` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_ !@#$%^&*()_+-={}[]|\?<>,. General DEFAULT 0 Any MYSQLD_DATADIR/s_utf8.ibd #sql_1 General DEFAULT 0 Any MYSQLD_DATADIR/#sql_1.ibd s_Cöŀumň General DEFAULT 0 Any MYSQLD_DATADIR/s_utf8_a.ibd s_cöĿǖmň General DEFAULT 0 Any MYSQLD_DATADIR/s_utf8_b.ibd s_வணக்கம் General DEFAULT 0 Any MYSQLD_DATADIR/ஆவணம்.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_ !@#$%^&*()_+-={}[]|\?<>,. TABLESPACE InnoDB NORMAL s_ !@#$%^&*()_+-={}[]|\?<>,. MYSQLD_DATADIR/s_utf8.ibd #sql_1 TABLESPACE InnoDB NORMAL #sql_1 MYSQLD_DATADIR/#sql_1.ibd s_Cöŀumň TABLESPACE InnoDB NORMAL s_Cöŀumň MYSQLD_DATADIR/s_utf8_a.ibd s_cöĿǖmň TABLESPACE InnoDB NORMAL s_cöĿǖmň MYSQLD_DATADIR/s_utf8_b.ibd s_வணக்கம் TABLESPACE InnoDB NORMAL s_வணக்கம் MYSQLD_DATADIR/ஆவணம்.ibd === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/@0023sql_1 #sql_1 161 5 Dynamic 0 General test/t@0020@0021@0040@0023@0024@0025@005e@0026@002a@0028@0029_@002b@002d@003d@007b@007d@005b@005d@007c@005c@003f@003c@003e@002c@002e s_ !@#$%^&*()_+-={}[]|\?<>,. 161 5 Dynamic 0 General test/t_utf8_1 s_Cöŀumň 161 5 Dynamic 0 General test/t_utf8_2 s_cöĿǖmň 161 5 Dynamic 0 General test/t_utf8_3 s_வணக்கம் 161 5 Dynamic 0 General # Directory listing of MYSQLD_DATADIR/ #sql_1.ibd s_utf8.ibd s_utf8_a.ibd s_utf8_b.ibd ஆவணம்.ibd CHECK TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; Table Op Msg_type Msg_text test.t !@#$%^&*()_+-={}[]|\?<>,. check status OK DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; DROP TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `; CHECK TABLE `#sql_1`; Table Op Msg_type Msg_text test.#sql_1 check status OK DROP TABLE `#sql_1`; DROP TABLESPACE `#sql_1`; DROP TABLE `t_utf8_1`; DROP TABLESPACE `s_Cöŀumň`; DROP TABLE `t_utf8_2` ; DROP TABLESPACE `s_cöĿǖmň` ; DROP TABLE `t_utf8_3`; DROP TABLESPACE `s_வணக்கம்`; # # Try to create a tablespace with the reserved case-sensitive prefix 'innodb_' # CREATE TABLESPACE `innodb_system` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: `innodb_system` is a reserved tablespace name. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: `innodb_system` is a reserved tablespace name. Error 1813 InnoDB: A tablespace named `innodb_system` already exists. Error 1528 Failed to create TABLESPACE innodb_system Error 1813 Tablespace 'innodb_system' exists. DROP TABLESPACE `innodb_system`; ERROR 42000: InnoDB: `innodb_system` is a reserved tablespace name. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: `innodb_system` is a reserved tablespace name. Error 1529 Failed to drop TABLESPACE innodb_system Error 1031 Table storage engine for 'innodb_system' doesn't have this option CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 's_InnoDB_System.ibd'; DROP TABLESPACE `InnoDB_System`; CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1'; ERROR HY000: Incorrect File Name 'ibdata1'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name 'ibdata1'. Error 3121 An IBD filepath must end with `.ibd`. Error 1528 Failed to create TABLESPACE InnoDB_System Error 1031 Table storage engine for 'InnoDB_System' doesn't have this option CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1.ibd'; DROP TABLESPACE `InnoDB_System`; CREATE TABLESPACE `innodb_temporary` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: `innodb_temporary` is a reserved tablespace name. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: `innodb_temporary` is a reserved tablespace name. Error 1813 InnoDB: A tablespace named `innodb_temporary` already exists. Error 1528 Failed to create TABLESPACE innodb_temporary Error 1813 Tablespace 'innodb_temporary' exists. DROP TABLESPACE `innodb_temporary`; ERROR 42000: InnoDB: `innodb_temporary` is a reserved tablespace name. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: `innodb_temporary` is a reserved tablespace name. Error 1529 Failed to drop TABLESPACE innodb_temporary Error 1031 Table storage engine for 'innodb_temporary' doesn't have this option CREATE TABLESPACE `InnoDB_Temporary` ADD DATAFILE 's_InnoDB_Temporary.ibd'; DROP TABLESPACE `InnoDB_Temporary`; CREATE TABLESPACE `innodb_custom` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot start with `innodb_`. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot start with `innodb_`. Error 1528 Failed to create TABLESPACE innodb_custom Error 1031 Table storage engine for 'innodb_custom' doesn't have this option DROP TABLESPACE `innodb_custom`; ERROR 42000: InnoDB: A general tablespace name cannot start with `innodb_`. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot start with `innodb_`. Error 1529 Failed to drop TABLESPACE innodb_custom Error 1031 Table storage engine for 'innodb_custom' doesn't have this option CREATE TABLESPACE `InnoDB_Custom` ADD DATAFILE 's_InnoDB_Custom.ibd'; DROP TABLESPACE `InnoDB_Custom`; CREATE TABLESPACE `INNODB_CUSTOM` ADD DATAFILE 's_INNODB_CUSTOM.ibd'; DROP TABLESPACE `INNODB_CUSTOM`; DROP TABLESPACE `ib_logfile0`; ERROR HY000: Failed to drop TABLESPACE ib_logfile0 SHOW WARNINGS; Level Code Message Error 1529 Failed to drop TABLESPACE ib_logfile0 Error 1812 Tablespace is missing for table ib_logfile0. CREATE TABLESPACE `ib_logfile0` ADD DATAFILE 'ib_logfile0.ibd'; DROP TABLESPACE `ib_logfile0`; DROP TABLESPACE `ib_logfile1`; ERROR HY000: Failed to drop TABLESPACE ib_logfile1 SHOW WARNINGS; Level Code Message Error 1529 Failed to drop TABLESPACE ib_logfile1 Error 1812 Tablespace is missing for table ib_logfile1. CREATE TABLESPACE `ib_logfile1` ADD DATAFILE 'ib_logfile1.ibd'; DROP TABLESPACE `ib_logfile1`; DROP TABLESPACE `ibdata1`; ERROR HY000: Failed to drop TABLESPACE ibdata1 SHOW WARNINGS; Level Code Message Error 1529 Failed to drop TABLESPACE ibdata1 Error 1812 Tablespace is missing for table ibdata1. CREATE TABLESPACE `ibdata1` ADD DATAFILE 'ibdata1.ibd'; DROP TABLESPACE `ibdata1`; DROP TABLESPACE `undo001`; ERROR HY000: Failed to drop TABLESPACE undo001 SHOW WARNINGS; Level Code Message Error 1529 Failed to drop TABLESPACE undo001 Error 1812 Tablespace is missing for table undo001. CREATE TABLESPACE `undo001` ADD DATAFILE 'undo001.ibd'; DROP TABLESPACE `undo001`; DROP TABLESPACE `undo002`; ERROR HY000: Failed to drop TABLESPACE undo002 SHOW WARNINGS; Level Code Message Error 1529 Failed to drop TABLESPACE undo002 Error 1812 Tablespace is missing for table undo002. CREATE TABLESPACE `undo002` ADD DATAFILE 'undo002.ibd'; DROP TABLESPACE `undo002`; # # Test various forms of ADD DATAFILE # CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd' ENGINE=InnoDB; CREATE TABLESPACE `s1_#_hash` ADD DATAFILE 's1_#_hash.ibd'; CREATE TABLESPACE s1_remote ADD DATAFILE 'MYSQL_TMP_DIR/s1.ibd.ibd'; CREATE TABLESPACE s2_remote ADD DATAFILE 'MYSQL_TMP_DIR/s2_#_dir/../s2_#_dir/s2.ibd'; CREATE TABLESPACE s_bad ADD DATAFILE '.ibd'; ERROR HY000: Incorrect File Name '.ibd'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name '.ibd'. Error 1528 Failed to create TABLESPACE s_bad Error 1031 Table storage engine for 's_bad' doesn't have this option CREATE TABLESPACE s_bad ADD DATAFILE 's_dir/s_subdir/.ibd'; ERROR HY000: Incorrect File Name 's_dir/s_subdir/.ibd'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name 's_dir/s_subdir/.ibd'. Error 1528 Failed to create TABLESPACE s_bad Error 1031 Table storage engine for 's_bad' doesn't have this option CREATE TABLESPACE s_bad ADD DATAFILE 's_dir/s_bad.ibs'; ERROR HY000: Incorrect File Name 's_dir/s_bad.ibs'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name 's_dir/s_bad.ibs'. Error 3121 An IBD filepath must end with `.ibd`. Error 1528 Failed to create TABLESPACE s_bad Error 1031 Table storage engine for 's_bad' doesn't have this option CREATE TABLESPACE s_bad ADD DATAFILE 'c:s_bad.ibd'; ERROR HY000: Incorrect File Name 'c:s_bad.ibd'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name 'c:s_bad.ibd'. Error 3121 Invalid use of ':'. Error 1528 Failed to create TABLESPACE s_bad Error 1031 Table storage engine for 's_bad' doesn't have this option CREATE TABLESPACE s_bad ADD DATAFILE 'cc:/s_bad.ibd'; ERROR HY000: Incorrect File Name 'cc:/s_bad.ibd'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name 'cc:/s_bad.ibd'. Error 3121 Invalid use of ':'. Error 1528 Failed to create TABLESPACE s_bad Error 1031 Table storage engine for 's_bad' doesn't have this option CREATE TABLESPACE s_bad ADD DATAFILE './drive:/s_bad.ibd'; ERROR HY000: Incorrect File Name './drive:/s_bad.ibd'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name './drive:/s_bad.ibd'. Error 3121 Invalid use of ':'. Error 1528 Failed to create TABLESPACE s_bad Error 1031 Table storage engine for 's_bad' doesn't have this option # # Try to create a tablespace where a same-named directory and file exist. # CREATE TABLESPACE s4_def ADD DATAFILE 'MYSQL_TMP_DIR/tablespace.ibd/s4.ibd'; CREATE TABLESPACE s5_def ADD DATAFILE 'MYSQL_TMP_DIR/tablespace.ibd/s4.ibd'; ERROR HY000: Failed to create TABLESPACE s5_def SHOW WARNINGS; Level Code Message Error 1528 Failed to create TABLESPACE s5_def Error 1813 Tablespace 's5_def' exists. CREATE TABLESPACE s6_def ADD DATAFILE 'MYSQL_TMP_DIR/tablespace.ibd'; ERROR HY000: Failed to create TABLESPACE s6_def === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd s1_#_hash General DEFAULT 0 Any MYSQLD_DATADIR/s1_#_hash.ibd s1_remote General DEFAULT 0 Any MYSQL_TMP_DIR/s1.ibd.ibd s2_remote General DEFAULT 0 Any MYSQL_TMP_DIR/s2_#_dir/.MYSQLD_DATADIR/s2_#_dir/s2.ibd s4_def General DEFAULT 0 Any MYSQL_TMP_DIR/tablespace.ibd/s4.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s1_#_hash TABLESPACE InnoDB NORMAL s1_#_hash MYSQLD_DATADIR/s1_#_hash.ibd s1_remote TABLESPACE InnoDB NORMAL s1_remote MYSQL_TMP_DIR/s1.ibd.ibd s2_remote TABLESPACE InnoDB NORMAL s2_remote MYSQL_TMP_DIR/s2_#_dir/.MYSQLD_DATADIR/s2_#_dir/s2.ibd s4_def TABLESPACE InnoDB NORMAL s4_def MYSQL_TMP_DIR/tablespace.ibd/s4.ibd # Directory listing of MYSQLD_DATADIR/ s1_#_hash.ibd s_def.ibd # Directory listing of MYSQL_TMP_DIR/ s1.ibd.ibd tablespace.ibd # Directory listing of MYSQL_TMP_DIR/tablespace.ibd/ s4.ibd # Directory listing of MYSQL_TMP_DIR/s2_#_dir/ s2.ibd DROP TABLESPACE s4_def; DROP TABLESPACE s1_remote; DROP TABLESPACE s2_remote; DROP TABLESPACE `s1_#_hash`; # # Try to create a tablespace that already exists. # Make sure that the tablespace name is compared in a CASE SENSITIVE way. # CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd'; ERROR HY000: InnoDB: A tablespace named `s_def` already exists. SHOW WARNINGS; Level Code Message Error 1813 InnoDB: A tablespace named `s_def` already exists. Error 1528 Failed to create TABLESPACE s_def Error 1813 Tablespace 's_def' exists. CREATE TABLESPACE s_def ADD DATAFILE 's_def_2.ibd'; ERROR HY000: InnoDB: A tablespace named `s_def` already exists. SHOW WARNINGS; Level Code Message Error 1813 InnoDB: A tablespace named `s_def` already exists. Error 1528 Failed to create TABLESPACE s_def Error 1813 Tablespace 's_def' exists. CREATE TABLESPACE S_new ADD DATAFILE 's_def.ibd'; ERROR HY000: Failed to create TABLESPACE S_new SHOW WARNINGS; Level Code Message Error 1528 Failed to create TABLESPACE S_new Error 1813 Tablespace 'S_new' exists. CREATE TABLESPACE `s_DEF` ADD DATAFILE 's_def_2.ibd'; DROP TABLESPACE `s_DEF`; # # Make a file-per-table tablespace name and try to use it as a General tablespace # CREATE TABLE t_single (a int, b text); === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_single test/t_single 33 5 Dynamic 0 Single === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd test/t_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_single.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd test/t_single TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_single.ibd CREATE TABLE t_general (a int, b text) TABLESPACE test/t_single engine=InnoDB; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/t_single engine=InnoDB' at line 1 CREATE TABLE t_general (a int, b text) TABLESPACE `test/t_single` engine=InnoDB; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 1031 Table storage engine for 't_general' doesn't have this option CREATE TABLE t_general (a int, b text) TABLESPACE `S_Def` engine=InnoDB; ERROR HY000: InnoDB: A general tablespace named `S_Def` cannot be found. SHOW WARNINGS; Level Code Message Error 1812 InnoDB: A general tablespace named `S_Def` cannot be found. Error 1031 Table storage engine for 't_general' doesn't have this option CREATE TABLE t_general (a int, b text) TABLESPACE `S_DEF` engine=InnoDB; ERROR HY000: InnoDB: A general tablespace named `S_DEF` cannot be found. SHOW WARNINGS; Level Code Message Error 1812 InnoDB: A general tablespace named `S_DEF` cannot be found. Error 1031 Table storage engine for 't_general' doesn't have this option CREATE TABLESPACE `test/t_single` ADD DATAFILE 's_single.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 1813 InnoDB: A tablespace named `test/t_single` already exists. Error 1528 Failed to create TABLESPACE test/t_single Error 1813 Tablespace 'test/t_single' exists. CREATE TABLESPACE `Test/t_Single` ADD DATAFILE 's_single.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 1528 Failed to create TABLESPACE Test/t_Single Error 1031 Table storage engine for 'Test/t_Single' doesn't have this option CREATE TABLESPACE `TEST/T_SINGLE` ADD DATAFILE 's_single.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 1528 Failed to create TABLESPACE TEST/T_SINGLE Error 1031 Table storage engine for 'TEST/T_SINGLE' doesn't have this option DROP TABLE t_single; === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd # # Row format is not allowed on CREATE TABLESPACE # CREATE TABLESPACE s_red ADD DATAFILE 's_red.ibd' ROW_FORMAT=redundant; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROW_FORMAT=redundant' at line 1 # # Add tables to the tablespaces. # CREATE TABLE t_def_in_def (a int, b text) TABLESPACE s_def; CREATE TABLE t_red_in_def (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_def; SET GLOBAL innodb_file_per_table = OFF; CREATE TABLE t_dyn_in_def (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_def; # Add data to the existing Tables INSERT INTO t_def_in_def VALUES (1,'a'),(2,'b'),(3,'c'); INSERT INTO t_red_in_def VALUES (1,'a'),(2,'b'),(3,'c'); INSERT INTO t_dyn_in_def VALUES (1,'a'),(2,'b'),(3,'c'); # # Try to drop a tablespace which is not empty # DROP TABLESPACE s_def; ERROR HY000: Failed to drop TABLESPACE s_def SHOW WARNINGS; Level Code Message Error 1529 Failed to drop TABLESPACE s_def Error 3120 Tablespace `s_def` is not empty. === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_def_in_def s_def 161 5 Dynamic 0 General test/t_dyn_in_def s_def 161 5 Dynamic 0 General test/t_red_in_def s_def 128 5 Redundant 0 General # Directory of MYSQLD_DATADIR/ s_def.ibd # Directory of MYSQLD_DATADIR/test/ # # Drop the tables we no longer need. # CHECK TABLE t_dyn_in_def; Table Op Msg_type Msg_text test.t_dyn_in_def check status OK CHECK TABLE t_red_in_def; Table Op Msg_type Msg_text test.t_red_in_def check status OK DROP TABLE t_dyn_in_def; DROP TABLE t_red_in_def; # # Try to make a table using the database name with an existing table name # CREATE TABLE test/t_def_in_def (a int, b text) TABLESPACE s_def; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/t_def_in_def (a int, b text) TABLESPACE s_def' at line 1 CREATE TABLE `test/t_def_in_def` (a int, b text) TABLESPACE s_def; === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/test@002ft_def_in_def s_def 161 5 Dynamic 0 General test/t_def_in_def s_def 161 5 Dynamic 0 General === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd # Directory listing of MYSQLD_DATADIR/ s_def.ibd # Directory listing of MYSQLD_DATADIR/test/ # # Try to create a temporary tablespace # CREATE TEMPORARY TABLESPACE s_temp ADD DATAFILE 's_temp.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLESPACE s_temp ADD DATAFILE 's_temp.ibd'' at line 1 SHOW WARNINGS; Level Code Message Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLESPACE s_temp ADD DATAFILE 's_temp.ibd'' at line 1 # # Try to put a temporary table into a non-temporary tablespace # CREATE TEMPORARY TABLE t_temp_red (a int, b text) ROW_FORMAT=redundant TABLESPACE s_def; ERROR HY000: InnoDB: Tablespace `s_def` cannot contain TEMPORARY tables. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: Tablespace `s_def` cannot contain TEMPORARY tables. Error 1031 Table storage engine for 't_temp_red' doesn't have this option # # Try to put a compressed temporary table into the system temporary tablespace # CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=compressed TABLESPACE=`innodb_temporary`; ERROR HY000: InnoDB: Temporary tablespace `innodb_temporary` cannot contain COMPRESSED tables. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: Temporary tablespace `innodb_temporary` cannot contain COMPRESSED tables. Error 1031 Table storage engine for 't_temp_zip' doesn't have this option # # Try to add a second table to a single-table tablespace # SET GLOBAL innodb_file_format = 'barracuda'; Warnings: Warning 131 Using innodb_file_format 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 SET GLOBAL innodb_file_per_table = ON; CREATE TABLE s_single (a int, b text) ROW_FORMAT=dynamic; CREATE TABLE t_second (a int, b text) TABLESPACE s_single; ERROR HY000: InnoDB: A general tablespace named `s_single` cannot be found. SHOW WARNINGS; Level Code Message Error 1812 InnoDB: A general tablespace named `s_single` cannot be found. Error 1031 Table storage engine for 't_second' doesn't have this option CREATE TABLE t_second (a int, b text) TABLESPACE=`test/s_single`; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 1031 Table storage engine for 't_second' doesn't have this option === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd test/s_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/s_single.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd test/s_single TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/s_single.ibd DROP TABLE s_single; # # Try to use both TABLESPACE and DATA DIRECTORY in the same CREATE TABLE. # # Strict mode, file_format and file_per_table should make no difference # when using general tablespaces. If TABLESPACE=innodb_file-per-table, # innodb_strict_mode apply. # SET GLOBAL innodb_file_per_table = OFF; SET innodb_strict_mode = OFF; CREATE TABLE t3 (a int, b text) DATA DIRECTORY='MYSQL_TMP_DIR' TABLESPACE s_def; ERROR HY000: InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment. Error 1031 Table storage engine for 't3' doesn't have this option # Cannot use both a general tablespace and a DATA DIRECTORY CREATE TABLE t4 (a int, b text) TABLESPACE=s_def DATA DIRECTORY='MYSQL_TMP_DIR'; ERROR HY000: InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment. Error 1031 Table storage engine for 't4' doesn't have this option # TABLESPACE=innodb_file_per_table can be used with DATA DIRECTORY CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR'; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/' # An altered table should retain both TABLESPACE=innodb_file_per_table and the DATA DIRECTORY ALTER TABLE t4 ROW_FORMAT=dynamic, algorithm=copy; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/' DROP TABLE t4; # An empty string in DATA DIRECTORY='' indicates the current directory, # which is not allowed. CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY=''; ERROR 42000: Incorrect table name '' SHOW WARNINGS; Level Code Message Error 1103 Incorrect table name '' SET GLOBAL innodb_file_per_table = ON; SET innodb_strict_mode = ON; CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY=''; ERROR 42000: Incorrect table name '' SHOW WARNINGS; Level Code Message Error 1103 Incorrect table name '' CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='.'; ERROR 42000: Incorrect table name '.' SHOW WARNINGS; Level Code Message Error 1103 Incorrect table name '.' CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='test'; ERROR 42000: Incorrect table name 'test' SHOW WARNINGS; Level Code Message Error 1103 Incorrect table name 'test' === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/test@002ft_def_in_def s_def 161 5 Dynamic 0 General test/t_def_in_def s_def 161 5 Dynamic 0 General DROP TABLE t_def_in_def; DROP TABLE `test/t_def_in_def`; # # Create tables explicitly in the system tablespace. # CREATE TABLE t_red_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=redundant; CREATE TABLE t_com_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compact; CREATE TABLE t_dyn_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=dynamic; CREATE TABLE t_zip_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compressed; ERROR HY000: InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table SHOW WARNINGS; Level Code Message Error 1478 InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table Error 1031 Table storage engine for 't_zip_in_system' doesn't have this option === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_com_in_system innodb_system 129 5 Compact 0 System test/t_dyn_in_system innodb_system 161 5 Dynamic 0 System test/t_red_in_system innodb_system 128 5 Redundant 0 System CHECK TABLE t_red_in_system; Table Op Msg_type Msg_text test.t_red_in_system check status OK CHECK TABLE t_com_in_system; Table Op Msg_type Msg_text test.t_com_in_system check status OK CHECK TABLE t_dyn_in_system; Table Op Msg_type Msg_text test.t_dyn_in_system check status OK DROP TABLE t_red_in_system; DROP TABLE t_com_in_system; DROP TABLE t_dyn_in_system; # # Create tables explicitly as file_per_table tablespaces. # CREATE TABLE t_red_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=redundant; CREATE TABLE t_com_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compact; CREATE TABLE t_dyn_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=dynamic; CREATE TABLE t_def_as_remote (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR'; === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd test/t_red_as_file_per_table Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t_red_as_file_per_table.ibd test/t_com_as_file_per_table Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t_com_as_file_per_table.ibd test/t_dyn_as_file_per_table Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_dyn_as_file_per_table.ibd test/t_def_as_remote Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t_def_as_remote.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd test/t_red_as_file_per_table TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_red_as_file_per_table.ibd test/t_com_as_file_per_table TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_com_as_file_per_table.ibd test/t_dyn_as_file_per_table TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_dyn_as_file_per_table.ibd test/t_def_as_remote TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/test/t_def_as_remote.ibd === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_com_as_file_per_table test/t_com_as_file_per_table 1 5 Compact 0 Single test/t_def_as_remote test/t_def_as_remote 97 5 Dynamic 0 Single test/t_dyn_as_file_per_table test/t_dyn_as_file_per_table 33 5 Dynamic 0 Single test/t_red_as_file_per_table test/t_red_as_file_per_table 0 5 Redundant 0 Single # Directory listing of MYSQLD_DATADIR/ s_def.ibd # Directory listing of MYSQLD_DATADIR/test/ t_com_as_file_per_table.ibd t_dyn_as_file_per_table.ibd t_red_as_file_per_table.ibd SHOW CREATE TABLE t_red_as_file_per_table; Table Create Table t_red_as_file_per_table CREATE TABLE `t_red_as_file_per_table` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT SHOW CREATE TABLE t_com_as_file_per_table; Table Create Table t_com_as_file_per_table CREATE TABLE `t_com_as_file_per_table` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT SHOW CREATE TABLE t_dyn_as_file_per_table; Table Create Table t_dyn_as_file_per_table CREATE TABLE `t_dyn_as_file_per_table` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC SHOW CREATE TABLE t_def_as_remote; Table Create Table t_def_as_remote CREATE TABLE `t_def_as_remote` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/' # # These explicit file_per_table tables can be moved to a general tablespace. # CREATE TABLESPACE s_multiple ADD DATAFILE 'multiple.ibd'; ALTER TABLE `t_red_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_red_was_file_per_table`; ALTER TABLE `t_com_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_com_was_file_per_table`; ALTER TABLE `t_dyn_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_dyn_was_file_per_table`; ALTER TABLE `t_def_as_remote` TABLESPACE=`s_multiple`, RENAME TO `t_def_was_remote`; === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd s_multiple General DEFAULT 0 Any MYSQLD_DATADIR/multiple.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_multiple TABLESPACE InnoDB NORMAL s_multiple MYSQLD_DATADIR/multiple.ibd === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_com_was_file_per_table s_multiple 129 5 Compact 0 General test/t_def_was_remote s_multiple 161 5 Dynamic 0 General test/t_dyn_was_file_per_table s_multiple 161 5 Dynamic 0 General test/t_red_was_file_per_table s_multiple 128 5 Redundant 0 General # Directory listing of MYSQLD_DATADIR/ multiple.ibd s_def.ibd # Directory listing of MYSQLD_DATADIR/test/ # # Tables in a general tablespace can be moved to file_per_table locations. # ALTER TABLE `t_red_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_red_to_file_per_table`; ALTER TABLE `t_com_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_com_to_file_per_table`; ALTER TABLE `t_dyn_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_dyn_to_file_per_table`; # Note that MySQL ignores DATA DIRECTORY on all ALTER TABLE statements. ALTER TABLE `t_def_was_remote` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='MYSQL_TMP_DIR', RENAME TO `t_def_to_file_per_table`; Warnings: Warning 1618 <DATA DIRECTORY> option ignored === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd s_multiple General DEFAULT 0 Any MYSQLD_DATADIR/multiple.ibd test/t_red_to_file_per_table Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t_red_to_file_per_table.ibd test/t_com_to_file_per_table Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t_com_to_file_per_table.ibd test/t_dyn_to_file_per_table Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_dyn_to_file_per_table.ibd test/t_def_to_file_per_table Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_def_to_file_per_table.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_multiple TABLESPACE InnoDB NORMAL s_multiple MYSQLD_DATADIR/multiple.ibd test/t_red_to_file_per_table TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_red_to_file_per_table.ibd test/t_com_to_file_per_table TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_com_to_file_per_table.ibd test/t_dyn_to_file_per_table TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_dyn_to_file_per_table.ibd test/t_def_to_file_per_table TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_def_to_file_per_table.ibd === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_com_to_file_per_table test/t_com_to_file_per_table 1 5 Compact 0 Single test/t_def_to_file_per_table test/t_def_to_file_per_table 33 5 Dynamic 0 Single test/t_dyn_to_file_per_table test/t_dyn_to_file_per_table 33 5 Dynamic 0 Single test/t_red_to_file_per_table test/t_red_to_file_per_table 0 5 Redundant 0 Single # Directory listing of MYSQLD_DATADIR/ multiple.ibd s_def.ibd # Directory listing of MYSQLD_DATADIR/test/ t_com_to_file_per_table.ibd t_def_to_file_per_table.ibd t_dyn_to_file_per_table.ibd t_red_to_file_per_table.ibd DROP TABLE t_red_to_file_per_table; DROP TABLE t_com_to_file_per_table; DROP TABLE t_dyn_to_file_per_table; DROP TABLE t_def_to_file_per_table; DROP TABLESPACE s_multiple; # # Try the syntax that InnoDB does not support. # CREATE TABLESPACE s_bad USE LOGFILE GROUP logfile_group; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USE LOGFILE GROUP logfile_group' at line 1 CREATE TABLESPACE s_bad EXTENT_SIZE = 1G; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXTENT_SIZE = 1G' at line 1 CREATE TABLESPACE s_bad INITIAL_SIZE = 100M; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INITIAL_SIZE = 100M' at line 1 CREATE LOGFILE GROUP s_bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 CREATE LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat'; ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'LOGFILE GROUP' DROP LOGFILE GROUP s_bad; ERROR 42000: The storage engine for the table doesn't support DROP LOGFILE GROUP ALTER LOGFILE GROUP s_bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 ALTER TABLESPACE s_bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd'; ERROR 42000: The storage engine for the table doesn't support ALTER TABLESPACE ALTER TABLESPACE s_bad DROP DATAFILE 'bad2.ibd'; ERROR 42000: The storage engine for the table doesn't support ALTER TABLESPACE ALTER TABLESPACE s_bad READ_ONLY; ERROR 42000: The storage engine for the table doesn't support ALTER TABLESPACE ACCESS MODE ALTER TABLESPACE s_bad READ_WRITE; ERROR 42000: The storage engine for the table doesn't support ALTER TABLESPACE ACCESS MODE ALTER TABLESPACE s_bad NOT ACCESSIBLE; ERROR 42000: The storage engine for the table doesn't support ALTER TABLESPACE ACCESS MODE ALTER TABLESPACE s_def INITIAL_SIZE = 1G; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INITIAL_SIZE = 1G' at line 1 ALTER TABLESPACE s_def MAX_SIZE = 1G; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX_SIZE = 1G' at line 1 ALTER TABLESPACE s_def AUTOEXTEND_SIZE = 10M; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTOEXTEND_SIZE = 10M' at line 1 ALTER TABLESPACE s_def NODEGROUP = 5; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NODEGROUP = 5' at line 1 ALTER TABLESPACE s_def WAIT; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WAIT' at line 1 ALTER TABLESPACE s_def COMMENT = 'This comment is ignored'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COMMENT = 'This comment is ignored'' at line 1 # # Try the syntax that MySQL docs say is ignored. # CREATE TABLESPACE s_ignore1 ADD DATAFILE 's_ignore1.ibd' AUTOEXTEND_SIZE = 10M; CREATE TABLESPACE s_ignore2 ADD DATAFILE 's_ignore2.ibd' MAX_SIZE = 1G; CREATE TABLESPACE s_ignore3 ADD DATAFILE 's_ignore3.ibd' NODEGROUP = 5; CREATE TABLESPACE s_ignore4 ADD DATAFILE 's_ignore4.ibd' WAIT; CREATE TABLESPACE s_ignore5 ADD DATAFILE 's_ignore5.ibd' COMMENT = 'This comment is ignored'; === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd s_ignore1 General DEFAULT 0 Any MYSQLD_DATADIR/s_ignore1.ibd s_ignore2 General DEFAULT 0 Any MYSQLD_DATADIR/s_ignore2.ibd s_ignore3 General DEFAULT 0 Any MYSQLD_DATADIR/s_ignore3.ibd s_ignore4 General DEFAULT 0 Any MYSQLD_DATADIR/s_ignore4.ibd s_ignore5 General DEFAULT 0 Any MYSQLD_DATADIR/s_ignore5.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_ignore1 TABLESPACE InnoDB NORMAL s_ignore1 MYSQLD_DATADIR/s_ignore1.ibd s_ignore2 TABLESPACE InnoDB NORMAL s_ignore2 MYSQLD_DATADIR/s_ignore2.ibd s_ignore3 TABLESPACE InnoDB NORMAL s_ignore3 MYSQLD_DATADIR/s_ignore3.ibd s_ignore4 TABLESPACE InnoDB NORMAL s_ignore4 MYSQLD_DATADIR/s_ignore4.ibd s_ignore5 TABLESPACE InnoDB NORMAL s_ignore5 MYSQLD_DATADIR/s_ignore5.ibd DROP TABLESPACE s_ignore1; DROP TABLESPACE s_ignore2; DROP TABLESPACE s_ignore3; DROP TABLESPACE s_ignore4; DROP TABLESPACE s_ignore5; # # Try various ALTER TABLE statements. # CREATE TABLE t_in_def (a serial key, b text) TABLESPACE s_def; INSERT INTO t_in_def(b) VALUES(repeat("a short string - ",5)); INSERT INTO t_in_def(b) VALUES(repeat("a long string - ",50)); INSERT INTO t_in_def(b) SELECT b FROM t_in_def; SELECT a, left(b,50) FROM t_in_def; a left(b,50) 1 a short string - a short string - a short string - 2 a long string - a long string - a long string - a 3 a short string - a short string - a short string - 4 a long string - a long string - a long string - a SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_in_def s_def 161 5 Dynamic 0 General ALTER TABLE t_in_def ROW_FORMAT=redundant; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT ALTER TABLE t_in_def ROW_FORMAT=dynamic; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD COLUMN (c int); SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD INDEX c (c); SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`), KEY `c` (`c`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def DROP INDEX c; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def DROP COLUMN c; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def AUTO_INCREMENT=100; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC INSERT INTO t_in_def(b) VALUES("after ALTER TABLE AUTO_INCREMENT"); SELECT a, left(b,50) FROM t_in_def; a left(b,50) 1 a short string - a short string - a short string - 2 a long string - a long string - a long string - a 3 a short string - a short string - a short string - 4 a long string - a long string - a long string - a 100 after ALTER TABLE AUTO_INCREMENT SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def CHANGE a aa int; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `b` text, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def CHANGE b bb varchar(1000); SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def DROP PRIMARY KEY; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, UNIQUE KEY `a` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD PRIMARY KEY aa(aa), ALGORITHM=INPLACE; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD INDEX ab(aa,bb(25)), ALGORITHM=COPY; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD INDEX bb(bb(50)); SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)), KEY `bb` (`bb`(50)) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC RENAME TABLE t_in_def TO t; SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)), KEY `bb` (`bb`(50)) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t s_def 161 5 Dynamic 0 General INSERT INTO t VALUES(200, "after RENAME TABLE t_in_def TO t"); SELECT aa, left(bb,50) FROM t; aa left(bb,50) 1 a short string - a short string - a short string - 2 a long string - a long string - a long string - a 3 a short string - a short string - a short string - 4 a long string - a long string - a long string - a 100 after ALTER TABLE AUTO_INCREMENT 200 after RENAME TABLE t_in_def TO t # # Move tables between tablespaces. # CREATE TABLESPACE s_alt1 ADD DATAFILE 's_alt1.ibd'; CREATE TABLESPACE s_alt2 ADD DATAFILE 's_alt2.ibd'; === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd s_alt1 General DEFAULT 0 Any MYSQLD_DATADIR/s_alt1.ibd s_alt2 General DEFAULT 0 Any MYSQLD_DATADIR/s_alt2.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_alt1 TABLESPACE InnoDB NORMAL s_alt1 MYSQLD_DATADIR/s_alt1.ibd s_alt2 TABLESPACE InnoDB NORMAL s_alt2 MYSQLD_DATADIR/s_alt2.ibd ALTER TABLE t RENAME TO t_nomad, TABLESPACE s_alt1; SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)), KEY `bb` (`bb`(50)) ) /*!50100 TABLESPACE `s_alt1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_nomad s_alt1 161 5 Dynamic 0 General ALTER TABLE t_nomad TABLESPACE s_alt2; SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)), KEY `bb` (`bb`(50)) ) /*!50100 TABLESPACE `s_alt2` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_nomad s_alt2 161 5 Dynamic 0 General ALTER TABLE t_nomad TABLESPACE s_def, DROP COLUMN bb; SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `aa` int(11) NOT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_nomad s_def 161 4 Dynamic 0 General === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd s_alt1 General DEFAULT 0 Any MYSQLD_DATADIR/s_alt1.ibd s_alt2 General DEFAULT 0 Any MYSQLD_DATADIR/s_alt2.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_alt1 TABLESPACE InnoDB NORMAL s_alt1 MYSQLD_DATADIR/s_alt1.ibd s_alt2 TABLESPACE InnoDB NORMAL s_alt2 MYSQLD_DATADIR/s_alt2.ibd DROP TABLE t_nomad; DROP TABLESPACE s_alt1; DROP TABLESPACE s_alt2; # # Move a table from the System Tablespace into a General Tablespace # and then back out to the system tablespace again. # SET GLOBAL innodb_file_per_table=OFF; CREATE TABLE t_system (a serial key, b text); INSERT INTO t_system(b) VALUES(repeat("a short string - ",5)); INSERT INTO t_system(b) VALUES(repeat("a long string - ",50)); SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_system innodb_system 33 5 Dynamic 0 System ALTER TABLE t_system TABLESPACE s_def; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_system s_def 161 5 Dynamic 0 General ALTER TABLE t_system TABLESPACE=`innodb_system`; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_system innodb_system 161 5 Dynamic 0 System # # Do a few ALTER TABLES for this table that was moved out and back into the system tablespace. # ALTER TABLE t_system ROW_FORMAT=redundant; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT ALTER TABLE t_system ROW_FORMAT=dynamic; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_system ADD COLUMN (c int); SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_system ADD INDEX c (c); SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`), KEY `c` (`c`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_system DROP INDEX c; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ALTER TABLE t_system DROP COLUMN c; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC # # Now try to put that table into its own Single-Table tablespace. # It is not allowed since the table is now marked with the system tablespace # SET GLOBAL innodb_file_per_table=ON; ALTER TABLE t_system RENAME TO t_still_system; SHOW CREATE TABLE t_still_system; Table Create Table t_still_system CREATE TABLE `t_still_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_still_system innodb_system 161 5 Dynamic 0 System DROP TABLE t_still_system; # # Try to create or move a table into the temporary tablespace. # CREATE TABLE t_not_temp (a int, b text) TABLESPACE=`innodb_temporary`; ERROR HY000: InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables. Error 1031 Table storage engine for 't_not_temp' doesn't have this option CREATE TABLE t_not_temp (a int, b text) TABLESPACE s_def; ALTER TABLE t_not_temp TABLESPACE=`innodb_temporary`; ERROR HY000: InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables. Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE' DROP TABLE t_not_temp; # # Try to create or move a temporary table in innodb_file_per_table # or innodb_temporary tablespaces with STRICT_MODE ON and OFF # CREATE TEMPORARY TABLE t_my_temp (a int, b text) TABLESPACE=`innodb_temporary`; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is deprecated and will be removed in a future release. SHOW CREATE TABLE t_my_temp; Table Create Table t_my_temp CREATE TEMPORARY TABLE `t_my_temp` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_temporary` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t_my_temp; SET innodb_strict_mode = OFF; CREATE TEMPORARY TABLE t_my_temp (a int, b text); SHOW CREATE TABLE t_my_temp; Table Create Table t_my_temp CREATE TEMPORARY TABLE `t_my_temp` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ALTER TABLE t_my_temp TABLESPACE innodb_temporary; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is deprecated and will be removed in a future release. SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is deprecated and will be removed in a future release. SHOW CREATE TABLE t_my_temp; Table Create Table t_my_temp CREATE TEMPORARY TABLE `t_my_temp` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_temporary` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 ALTER TABLE t_my_temp TABLESPACE innodb_file_per_table; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_file_per_table option is ignored. This option is deprecated and will be removed in a future release. SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: TABLESPACE=innodb_file_per_table option is ignored. This option is deprecated and will be removed in a future release. SHOW CREATE TABLE t_my_temp; Table Create Table t_my_temp CREATE TEMPORARY TABLE `t_my_temp` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t_my_temp; SET innodb_strict_mode = ON; CREATE TEMPORARY TABLE t_my_temp (a int, b text); SHOW CREATE TABLE t_my_temp; Table Create Table t_my_temp CREATE TEMPORARY TABLE `t_my_temp` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ALTER TABLE t_my_temp TABLESPACE=innodb_file_per_table; ERROR HY000: InnoDB: TABLESPACE=innodb_file_per_table option is disallowed for temporary tables with INNODB_STRICT_NODE=ON. This option is deprecated and will be removed in a future release ALTER TABLE t_my_temp TABLESPACE=innodb_temporary; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is deprecated and will be removed in a future release. SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is deprecated and will be removed in a future release. SHOW CREATE TABLE t_my_temp; Table Create Table t_my_temp CREATE TEMPORARY TABLE `t_my_temp` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_temporary` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t_my_temp; # # Try to create or move a table into the redo tablespace # or any tablespace using the reserved `innodb_` prefix. # CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_redo_log`; ERROR 42000: InnoDB: A general tablespace name cannot start with `innodb_`. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot start with `innodb_`. Error 1031 Table storage engine for 't_data' doesn't have this option CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_anything`; ERROR 42000: InnoDB: A general tablespace name cannot start with `innodb_`. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot start with `innodb_`. Error 1031 Table storage engine for 't_data' doesn't have this option CREATE TABLE t_data (a int, b text) TABLESPACE s_def; ALTER TABLE t_data TABLESPACE=`innodb_redo_log`; ERROR 42000: InnoDB: A general tablespace name cannot start with `innodb_`. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot start with `innodb_`. Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE' ALTER TABLE t_data TABLESPACE=`innodb_anything`; ERROR 42000: InnoDB: A general tablespace name cannot start with `innodb_`. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot start with `innodb_`. Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE' DROP TABLE t_data; # # Move a table from a Single-Table tablespace into a General Tablespace. # CREATE TABLE t_single (a serial key, b text); INSERT INTO t_single(b) VALUES(repeat("a short string - ",5)); INSERT INTO t_single(b) VALUES(repeat("a long string - ",50)); SHOW CREATE TABLE t_single; Table Create Table t_single CREATE TABLE `t_single` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_single test/t_single 33 5 Dynamic 0 Single ALTER TABLE t_single TABLESPACE s_def; SHOW CREATE TABLE t_single; Table Create Table t_single CREATE TABLE `t_single` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_single s_def 161 5 Dynamic 0 General DROP TABLE t_single; # # Move a MyISAM table into an InnoDB General Tablespace and back. # CREATE TABLE t_myisam (a serial key, b int) ENGINE=MyISAM; INSERT INTO t_myisam(b) VALUES(11); SHOW CREATE TABLE t_myisam; Table Create Table t_myisam CREATE TABLE `t_myisam` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 ALTER TABLE t_myisam RENAME TO t_innodb, TABLESPACE s_def, ENGINE=InnoDB; INSERT INTO t_innodb(b) VALUES(22); SHOW CREATE TABLE t_innodb; Table Create Table t_innodb CREATE TABLE `t_innodb` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_innodb s_def 161 5 Dynamic 0 General ALTER TABLE t_innodb RENAME TO t_myisam, ENGINE=MyISAM; INSERT INTO t_myisam(b) VALUES(33); SELECT * FROM t_myisam; a b 1 11 2 22 3 33 SHOW CREATE TABLE t_myisam; Table Create Table t_myisam CREATE TABLE `t_myisam` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ALTER TABLE t_myisam RENAME TO t_innodb, ENGINE=InnoDB; INSERT INTO t_innodb(b) VALUES(44); SHOW CREATE TABLE t_innodb; Table Create Table t_innodb CREATE TABLE `t_innodb` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_innodb s_def 161 5 Dynamic 0 General DROP TABLE t_innodb; # # Move a Dynamic table from an InnoDB General tablespace to MyISAM # and Memory and back without having to re-specify the tablespace # and row_format. # CREATE TABLE t_dyn_in_s_def (a serial key, b int) ROW_FORMAT=Dynamic TABLESPACE=s_def ENGINE=InnoDB; INSERT INTO t_dyn_in_s_def(b) VALUES(11); SHOW CREATE TABLE t_dyn_in_s_def; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_dyn_in_s_def s_def 161 5 Dynamic 0 General ALTER TABLE t_dyn_in_s_def ENGINE=MyISAM; INSERT INTO t_dyn_in_s_def(b) VALUES(22); SHOW CREATE TABLE t_dyn_in_s_def; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB; INSERT INTO t_dyn_in_s_def(b) VALUES(33); SHOW CREATE TABLE t_dyn_in_s_def; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_dyn_in_s_def s_def 161 5 Dynamic 0 General ALTER TABLE t_dyn_in_s_def ENGINE=Memory; INSERT INTO t_dyn_in_s_def(b) VALUES(44); SHOW CREATE TABLE t_dyn_in_s_def; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB; INSERT INTO t_dyn_in_s_def(b) VALUES(55); SELECT * FROM t_dyn_in_s_def; a b 1 11 2 22 3 33 4 44 5 55 SHOW CREATE TABLE t_dyn_in_s_def; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_dyn_in_s_def s_def 161 5 Dynamic 0 General CHECK TABLE t_dyn_in_s_def; Table Op Msg_type Msg_text test.t_dyn_in_s_def check status OK DROP TABLE t_dyn_in_s_def; # # If a tablespace name is associated with a table in another SE, and that tablespace # does not exist in InnoDB, the ALTER TABLE will fail # CREATE TABLESPACE s_short_life ADD DATAFILE 'short_life.ibd'; CREATE TABLE t_nomad (a serial key, b int) TABLESPACE=s_short_life ENGINE=InnoDB; INSERT INTO t_nomad(b) VALUES(11); SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_short_life` */ ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_nomad s_short_life 161 5 Dynamic 0 General ALTER TABLE t_nomad ENGINE=MyISAM; INSERT INTO t_nomad(b) VALUES(22); SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_short_life` */ ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type DROP TABLESPACE s_short_life; ALTER TABLE t_nomad ENGINE=InnoDB; ERROR HY000: InnoDB: A general tablespace named `s_short_life` cannot be found. CREATE TABLESPACE s_shorter_life ADD DATAFILE 'shorter_life.ibd'; ALTER TABLE t_nomad ENGINE=InnoDB TABLESPACE=s_shorter_life; SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_shorter_life` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_nomad s_shorter_life 161 5 Dynamic 0 General DROP TABLE t_nomad; DROP TABLESPACE s_shorter_life; # # Show that a table with the same SQL name can be created in two different # databases, whether they are in the system tablespace, a general tablespace # or their own file-per-table tablespace. # Also show that DROP DATABASE will remove tables from inside a General Tablespace # without removing the tablespace or other tables in it for another databases. # SET GLOBAL innodb_file_per_table=OFF; CREATE TABLE t_system (a serial key, b text); CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def; CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`; SHOW TABLES; Tables_in_test t_general t_single t_system CREATE DATABASE test1; USE test1; CREATE TABLESPACE s_empty1 ADD DATAFILE 's_empty1.ibd' ENGINE InnoDB; CREATE TABLE t_system (a serial key, b text); CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def; CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`; SHOW TABLES; Tables_in_test1 t_general t_single t_system USE test; === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_general s_def 161 5 Dynamic 0 General test/t_single test/t_single 33 5 Dynamic 0 Single test/t_system innodb_system 33 5 Dynamic 0 System test1/t_general s_def 161 5 Dynamic 0 General test1/t_single test1/t_single 33 5 Dynamic 0 Single test1/t_system innodb_system 33 5 Dynamic 0 System === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd test/t_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_single.ibd s_empty1 General DEFAULT 0 Any MYSQLD_DATADIR/s_empty1.ibd test1/t_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test1/t_single.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd test/t_single TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_single.ibd s_empty1 TABLESPACE InnoDB NORMAL s_empty1 MYSQLD_DATADIR/s_empty1.ibd test1/t_single TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test1/t_single.ibd DROP DATABASE test1; === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t_general s_def 161 5 Dynamic 0 General test/t_single test/t_single 33 5 Dynamic 0 Single test/t_system innodb_system 33 5 Dynamic 0 System === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path s_def General DEFAULT 0 Any MYSQLD_DATADIR/s_def.ibd test/t_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_single.ibd s_empty1 General DEFAULT 0 Any MYSQLD_DATADIR/s_empty1.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd test/t_single TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t_single.ibd s_empty1 TABLESPACE InnoDB NORMAL s_empty1 MYSQLD_DATADIR/s_empty1.ibd DROP TABLE t_system; DROP TABLE t_general; DROP TABLE t_single; DROP TABLESPACE s_empty1; # # Assign different tablespace for PK-FK tables # CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB; CREATE TABLE t1 (a int primary key) ENGINE=innodb TABLESPACE=s1; CREATE TABLE t2 (b int , foreign key (b) references t1(a)) ENGINE=innodb ; INSERT INTO t1 VALUES (100); INSERT INTO t1 VALUES (200); INSERT INTO t2 VALUES (100); === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t1 s1 161 4 Dynamic 0 General test/t2 innodb_system 33 4 Dynamic 0 System SELECT * FROM t1; a 100 200 SELECT * FROM t2; b 100 SELECT * FROM t1,t2 WHERE t1.a=t2.b; a b 100 100 DROP TABLE t2,t1; DROP TABLESPACE s1; # # Assigning general tablespace from procedure # USE test; CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB; CREATE TABLE t1 (a geometry NOT NULL, SPATIAL (a)) ENGINE=InnoDB; INSERT INTO t1 VALUES (st_GeomFromText("LINESTRING(100 100, 200 200, 300 300)")) ; CREATE PROCEDURE p1() BEGIN ALTER TABLE t1 TABLESPACE=s1; END| === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t1 innodb_system 33 4 Dynamic 0 System CALL p1(); === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t1 s1 161 4 Dynamic 0 General DROP PROCEDURE p1; # # Show that DISCARD/IMPORT tablespace does not work on a general tablespace. # ALTER TABLE t1 DISCARD TABLESPACE; ERROR 42000: InnoDB: Cannot discard table `test/t1` because it is in a general tablespace. It must be file-per-table. SHOW WARNINGS; Level Code Message Error 1148 InnoDB: Cannot discard table `test/t1` because it is in a general tablespace. It must be file-per-table. Error 1148 The used command is not allowed with this MySQL version ALTER TABLE t1 IMPORT TABLESPACE; ERROR 42000: InnoDB: Cannot import table `test/t1` because it is in a general tablespace. It must be file-per-table. SHOW WARNINGS; Level Code Message Error 1148 InnoDB: Cannot import table `test/t1` because it is in a general tablespace. It must be file-per-table. Error 1148 The used command is not allowed with this MySQL version DROP TABLE t1; DROP TABLESPACE s1; # # Clean-up. # DROP TABLESPACE s_def; SET GLOBAL innodb_file_per_table=default; SET GLOBAL innodb_strict_mode=default;
Close
