1# 2# Test the limits of a file-per-table tablespace name. MySQL combines 3# the database name with the table name to make a unique table name. 4# 5SET default_storage_engine=InnoDB; 6# 7# MySQL limits each database and tablename identifier to 64 characters 8# of up to 3 bytes per character, corresponding to 192 bytes. 9# 10CREATE DATABASE `this_sixty_five_byte_name_is_too_long____________________________`; 11ERROR 42000: Identifier name 'this_sixty_five_byte_name_is_too_long____________________________' is too long 12CREATE DATABASE `this_sixty_four_byte_name_is_not_too_long_______________________`; 13USE `this_sixty_four_byte_name_is_not_too_long_______________________`; 14# 15# A 64 character tablename can be created in a 64 character database name 16# 17CREATE TABLE `this_sixty_four_byte_name_is_not_too_long_______________________`.`this_sixty_four_byte_name_is_not_too_long_______________________` (a SERIAL); 18# 19# A 65 character tablename is too long. 20# 21CREATE TABLE `test`.`this_sixty_five_byte_name_is_too_long____________________________` (a SERIAL); 22ERROR 42000: Identifier name 'this_sixty_five_byte_name_is_too_long____________________________' is too long 23CREATE TABLE `this_sixty_four_byte_name_is_not_too_long_______________________`.`this_sixty_five_byte_name_is_too_long____________________________` (a SERIAL); 24ERROR 42000: Identifier name 'this_sixty_five_byte_name_is_too_long____________________________' is too long 25# 26# Non-non-filename-safe characters like '#' are expanded to '@0023'. 27# On many file systems, such as Linux extfs, you can create a database name 28# that expands to up to 255 bytes long. 29# `##################################################_long` is expanded to 30# (50 * 5) + 5 = 255. 31# 32CREATE DATABASE `##################################################_long`;; 33USE `##################################################_long`; 34# 35# This 256-byte name is only one byte longer but fails with an error code 36# from the stat operation. 37# `##################################################_long_` is expanded to 38# (50 * 5) + 6 = 256. 39# 40CREATE DATABASE `##################################################_long_`; 41ERROR HY000: Can't get stat of './##################################################_long_' (Errcode: ## - File name too long) 42# 43# This 300-byte name which is the longest name that gets an error code 44# from the stat operation. 45# `###########################################################_long` is expanded to 46# (59 * 5) + 5 = 300. 47# 48CREATE DATABASE `###########################################################_long`; 49ERROR HY000: Can't get stat of './###########################################################_long' (Errcode: ## - File name too long) 50# 51# This 301-byte name which is only one byte longer but fails with ER_TOO_LONG_IDENT. 52# `###########################################################_long_` is expanded to 53# (59 * 5) + 6 = 301. 54# 55CREATE DATABASE `###########################################################_long_`; 56ERROR 42000: Identifier name '###########################################################_long_' is too long 57USE test; 58# 59# An expanded table name is limited to 251 bytes 60# 61CREATE TABLE `test`.`#################################################_long_` (a SERIAL); 62# 63# A 252-byte tablename is too long 64# 65CREATE TABLE `test`.`#################################################_long___` (a SERIAL); 66ERROR HY000: Can't create table '#################################################_long___' (errno: ##) 67CREATE DATABASE twenty_byte_db_name_; 68USE `twenty_byte_db_name_`; 69# 70# A 251 byte expanded table name will fit with a longer database name 71# 72CREATE TABLE `twenty_byte_db_name_`.`#################################################_long_` (a SERIAL); 73# 74# A 252 byte expanded table name is also too long in a longer database name 75# 76CREATE TABLE `twenty_byte_db_name_`.`#################################################_long___` (a SERIAL); 77ERROR HY000: Can't create table '#################################################_long___' (errno: ##) 78# 79# Another limitation is a 512 byte length to an expanded path that includes 80# the datadir which is './' in this test, the expanded database name, 81# the directory separator '/', the expanded table name, and the file extension. 82# './long_db_name.long_250_byte_table_name.frm' 83# 2+ 255 +1+ 250 +1+3 = 512 84# 85CREATE TABLE `##################################################_long`.`#################################################_long` (a SERIAL); 86CREATE TABLE `##################################################_long`.`#################################################_long_` (a SERIAL); 87ERROR HY000: Long database name and identifier for object resulted in path length exceeding 512 characters. Path: './@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023_long/@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@ 88SHOW WARNINGS; 89Level Code Message 90Error 1860 Long database name and identifier for object resulted in path length exceeding 512 characters. Path: './@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023_long/@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@ 91# 92# Show the successfully created databases and tables 93# 94---- list_files MYSQLD_DATADIR/test 95#################################################_long_.frm 96#################################################_long_.ibd 97---- list_files MYSQLD_DATADIR/this_sixty_four_byte_name_is_not_too_long_______________________ 98db.opt 99this_sixty_four_byte_name_is_not_too_long_______________________.frm 100this_sixty_four_byte_name_is_not_too_long_______________________.ibd 101---- list_files MYSQLD_DATADIR/##################################################_long 102#################################################_long.frm 103#################################################_long.ibd 104db.opt 105SELECT name FROM information_schema.innodb_sys_tables WHERE name LIKE '%long%'; 106name 107##################################################_long/#################################################_long 108test/#################################################_long_ 109this_sixty_four_byte_name_is_not_too_long_______________________/this_sixty_four_byte_name_is_not_too_long_______________________ 110twenty_byte_db_name_/#################################################_long_ 111SELECT name FROM information_schema.innodb_sys_tablespaces WHERE name LIKE '%long%'; 112name 113this_sixty_four_byte_name_is_not_too_long_______________________/this_sixty_four_byte_name_is_not_too_long_______________________ 114test/#################################################_long_ 115twenty_byte_db_name_/#################################################_long_ 116##################################################_long/#################################################_long 117SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%long%'; 118path 119./this_sixty_four_byte_name_is_not_too_long_______________________/this_sixty_four_byte_name_is_not_too_long_______________________.ibd 120./test/#################################################_long_.ibd 121./twenty_byte_db_name_/#################################################_long_.ibd 122./##################################################_long/#################################################_long.ibd 123SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%long%'; 124file_name ./this_sixty_four_byte_name_is_not_too_long_______________________/this_sixty_four_byte_name_is_not_too_long_______________________.ibd 125tablespace_name innodb_file_per_table_## 126file_name ./test/#################################################_long_.ibd 127tablespace_name innodb_file_per_table_## 128file_name ./twenty_byte_db_name_/#################################################_long_.ibd 129tablespace_name innodb_file_per_table_## 130file_name ./##################################################_long/#################################################_long.ibd 131tablespace_name innodb_file_per_table_## 132# 133# Cleanup 134# 135DROP TABLE `this_sixty_four_byte_name_is_not_too_long_______________________`.`this_sixty_four_byte_name_is_not_too_long_______________________`; 136DROP TABLE `test`.`#################################################_long_`; 137DROP TABLE `twenty_byte_db_name_`.`#################################################_long_`; 138DROP TABLE `##################################################_long`.`#################################################_long`; 139DROP DATABASE `this_sixty_four_byte_name_is_not_too_long_______________________`; 140DROP DATABASE `##################################################_long`; 141DROP DATABASE `twenty_byte_db_name_`; 142