1--echo # 2--echo # Test the limits of a file-per-table tablespace name. MySQL combines 3--echo # the database name with the table name to make a unique table name. 4--echo # 5 6--source include/have_innodb.inc 7--source include/not_windows.inc 8# This will test the limit of a filename in MySQL at 512 bytes. 9# We control that by making it a relative path starting with "./". 10# The embedded server uses an absolute path as the datadir 11# which has a non-deterministic length. 12--source include/not_embedded.inc 13 14SET default_storage_engine=InnoDB; 15LET $MYSQLD_DATADIR = `select @@datadir`; 16 17--echo # 18--echo # MySQL limits each database and tablename identifier to 64 characters 19--echo # of up to 3 bytes per character, corresponding to 192 bytes. 20--echo # 21LET $too_long_name = this_sixty_five_byte_name_is_too_long____________________________; 22--error ER_TOO_LONG_IDENT 23--eval CREATE DATABASE `$too_long_name` 24 25LET $long_name = this_sixty_four_byte_name_is_not_too_long_______________________; 26--eval CREATE DATABASE `$long_name` 27--eval USE `$long_name` 28 29--echo # 30--echo # A 64 character tablename can be created in a 64 character database name 31--echo # 32--eval CREATE TABLE `$long_name`.`$long_name` (a SERIAL) 33 34--echo # 35--echo # A 65 character tablename is too long. 36--echo # 37--error ER_TOO_LONG_IDENT 38--eval CREATE TABLE `test`.`$too_long_name` (a SERIAL) 39--error ER_TOO_LONG_IDENT 40--eval CREATE TABLE `$long_name`.`$too_long_name` (a SERIAL) 41 42--echo # 43--echo # Non-non-filename-safe characters like '#' are expanded to '@0023'. 44--echo # On many file systems, such as Linux extfs, you can create a database name 45--echo # that expands to up to 255 bytes long. 46--echo # `##################################################_long` is expanded to 47--echo # (50 * 5) + 5 = 255. 48--echo # 49LET $long_db_name = ##################################################_long; 50--eval CREATE DATABASE `$long_db_name`; 51--eval USE `$long_db_name` 52 53--echo # 54--echo # This 256-byte name is only one byte longer but fails with an error code 55--echo # from the stat operation. 56--echo # `##################################################_long_` is expanded to 57--echo # (50 * 5) + 6 = 256. 58--echo # 59--replace_regex /Errcode: [0-9]+/Errcode: ##/ /@0023/#/ 60--error 13 61CREATE DATABASE `##################################################_long_`; 62 63--echo # 64--echo # This 300-byte name which is the longest name that gets an error code 65--echo # from the stat operation. 66--echo # `###########################################################_long` is expanded to 67--echo # (59 * 5) + 5 = 300. 68--echo # 69--replace_regex /Errcode: [0-9]+/Errcode: ##/ /@0023/#/ 70--error 13 71CREATE DATABASE `###########################################################_long`; 72 73--echo # 74--echo # This 301-byte name which is only one byte longer but fails with ER_TOO_LONG_IDENT. 75--echo # `###########################################################_long_` is expanded to 76--echo # (59 * 5) + 6 = 301. 77--echo # 78--replace_result @0023 # 79--error ER_TOO_LONG_IDENT 80CREATE DATABASE `###########################################################_long_`; 81 82USE test; 83 84LET $long_249_byte_table_name = #################################################long; 85LET $long_250_byte_table_name = #################################################_long; 86LET $long_251_byte_table_name = #################################################_long_; 87LET $long_252_byte_table_name = #################################################_long___; 88 89--echo # 90--echo # An expanded table name is limited to 251 bytes 91--echo # 92--eval CREATE TABLE `test`.`$long_251_byte_table_name` (a SERIAL) 93 94--echo # 95--echo # A 252-byte tablename is too long 96--echo # 97--replace_regex /errno: [0-9]+/errno: ##/ /@0023/#/ 98--error ER_CANT_CREATE_TABLE 99--eval CREATE TABLE `test`.`$long_252_byte_table_name` (a SERIAL) 100 101CREATE DATABASE twenty_byte_db_name_; 102USE `twenty_byte_db_name_`; 103 104--echo # 105--echo # A 251 byte expanded table name will fit with a longer database name 106--echo # 107--eval CREATE TABLE `twenty_byte_db_name_`.`$long_251_byte_table_name` (a SERIAL) 108 109--echo # 110--echo # A 252 byte expanded table name is also too long in a longer database name 111--echo # 112--replace_regex /errno: [0-9]+/errno: ##/ /@0023/#/ 113--error ER_CANT_CREATE_TABLE 114--eval CREATE TABLE `twenty_byte_db_name_`.`$long_252_byte_table_name` (a SERIAL) 115 116--echo # 117--echo # Another limitation is a 512 byte length to an expanded path that includes 118--echo # the datadir which is './' in this test, the expanded database name, 119--echo # the directory separator '/', the expanded table name, and the file extension. 120--echo # './long_db_name.long_250_byte_table_name.frm' 121--echo # 2+ 255 +1+ 250 +1+3 = 512 122--echo # 123--eval CREATE TABLE `$long_db_name`.`$long_250_byte_table_name` (a SERIAL) 124 125--error ER_IDENT_CAUSES_TOO_LONG_PATH 126--eval CREATE TABLE `$long_db_name`.`$long_251_byte_table_name` (a SERIAL) 127SHOW WARNINGS; 128 129--echo # 130--echo # Show the successfully created databases and tables 131--echo # 132--echo ---- list_files MYSQLD_DATADIR/test 133--replace_result @0023 # 134--list_files $MYSQLD_DATADIR/test 135--echo ---- list_files MYSQLD_DATADIR/$long_name 136--replace_result @0023 # 137--list_files $MYSQLD_DATADIR/$long_name 138--echo ---- list_files MYSQLD_DATADIR/$long_db_name 139--replace_result @0023 # 140--list_files $MYSQLD_DATADIR/@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 141 142--replace_result @0023 # 143SELECT name FROM information_schema.innodb_sys_tables WHERE name LIKE '%long%'; 144--replace_result @0023 # 145SELECT name FROM information_schema.innodb_sys_tablespaces WHERE name LIKE '%long%'; 146--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR @0023 # 147SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%long%'; 148--vertical_results 149--replace_regex /innodb_file_per_table_[0-9]*/innodb_file_per_table_##/ 150--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR @0023 # 151SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%long%'; 152--horizontal_results 153 154--echo # 155--echo # Cleanup 156--echo # 157 158--eval DROP TABLE `$long_name`.`$long_name` 159--eval DROP TABLE `test`.`$long_251_byte_table_name` 160--eval DROP TABLE `twenty_byte_db_name_`.`$long_251_byte_table_name` 161--eval DROP TABLE `$long_db_name`.`$long_250_byte_table_name` 162--eval DROP DATABASE `$long_name` 163--eval DROP DATABASE `$long_db_name` 164DROP DATABASE `twenty_byte_db_name_`; 165