| 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 |
# |
| 5 |
SET 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 |
# |
| 10 |
CREATE DATABASE `this_sixty_five_byte_name_is_too_long____________________________`; |
| 11 |
ERROR 42000: Identifier name 'this_sixty_five_byte_name_is_too_long____________________________' is too long |
| 12 |
CREATE DATABASE `this_sixty_four_byte_name_is_not_too_long_______________________`; |
| 13 |
USE `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 |
# |
| 17 |
CREATE 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 |
# |
| 21 |
CREATE TABLE `test`.`this_sixty_five_byte_name_is_too_long____________________________` (a SERIAL); |
| 22 |
ERROR 42000: Identifier name 'this_sixty_five_byte_name_is_too_long____________________________' is too long |
| 23 |
CREATE TABLE `this_sixty_four_byte_name_is_not_too_long_______________________`.`this_sixty_five_byte_name_is_too_long____________________________` (a SERIAL); |
| 24 |
ERROR 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 |
# |
| 32 |
CREATE DATABASE `##################################################_long`;; |
| 33 |
USE `##################################################_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 |
# |
| 40 |
CREATE DATABASE `##################################################_long_`; |
| 41 |
ERROR 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 |
# |
| 48 |
CREATE DATABASE `###########################################################_long`; |
| 49 |
ERROR 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 |
# |
| 55 |
CREATE DATABASE `###########################################################_long_`; |
| 56 |
ERROR 42000: Identifier name '###########################################################_long_' is too long |
| 57 |
USE test; |
| 58 |
# |
| 59 |
# An expanded table name is limited to 251 bytes |
| 60 |
# |
| 61 |
CREATE TABLE `test`.`#################################################_long_` (a SERIAL); |
| 62 |
# |
| 63 |
# A 252-byte tablename is too long |
| 64 |
# |
| 65 |
CREATE TABLE `test`.`#################################################_long___` (a SERIAL); |
| 66 |
ERROR HY000: Got error 168 - 'Unknown (generic) error from engine' from storage engine |
| 67 |
CREATE DATABASE twenty_byte_db_name_; |
| 68 |
USE `twenty_byte_db_name_`; |
| 69 |
# |
| 70 |
# A 251 byte expanded table name will fit with a longer database name |
| 71 |
# |
| 72 |
CREATE 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 |
# |
| 76 |
CREATE TABLE `twenty_byte_db_name_`.`#################################################_long___` (a SERIAL); |
| 77 |
ERROR HY000: Got error 168 - 'Unknown (generic) error from engine' from storage engine |
| 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 |
# |
| 85 |
CREATE TABLE `##################################################_long`.`#################################################_long` (a SERIAL); |
| 86 |
CREATE TABLE `##################################################_long`.`#################################################_long_` (a SERIAL); |
| 87 |
ERROR 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@ |
| 88 |
SHOW WARNINGS; |
| 89 |
Level Code Message |
| 90 |
Error 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_.ibd |
| 96 |
---- list_files MYSQLD_DATADIR/this_sixty_four_byte_name_is_not_too_long_______________________ |
| 97 |
this_sixty_four_byte_name_is_not_too_long_______________________.ibd |
| 98 |
---- list_files MYSQLD_DATADIR/##################################################_long |
| 99 |
#################################################_long.ibd |
| 100 |
SELECT name FROM information_schema.innodb_tables WHERE name LIKE '%long%'; |
| 101 |
name |
| 102 |
this_sixty_four_byte_name_is_not_too_long_______________________/this_sixty_four_byte_name_is_not_too_long_______________________ |
| 103 |
test/#################################################_long_ |
| 104 |
twenty_byte_db_name_/#################################################_long_ |
| 105 |
##################################################_long/#################################################_long |
| 106 |
SELECT name FROM information_schema.innodb_tablespaces WHERE name LIKE '%long%'; |
| 107 |
name |
| 108 |
this_sixty_four_byte_name_is_not_too_long_______________________/this_sixty_four_byte_name_is_not_too_long_______________________ |
| 109 |
test/#################################################_long_ |
| 110 |
twenty_byte_db_name_/#################################################_long_ |
| 111 |
##################################################_long/#################################################_long |
| 112 |
SELECT path FROM information_schema.innodb_datafiles WHERE path LIKE '%long%' order by path; |
| 113 |
PATH |
| 114 |
./##################################################_long/#################################################_long.ibd |
| 115 |
./test/#################################################_long_.ibd |
| 116 |
./this_sixty_four_byte_name_is_not_too_long_______________________/this_sixty_four_byte_name_is_not_too_long_______________________.ibd |
| 117 |
./twenty_byte_db_name_/#################################################_long_.ibd |
| 118 |
SELECT file_name, tablespace_name FROM information_schema.files |
| 119 |
WHERE file_name LIKE '%long%' ORDER BY file_name; |
| 120 |
FILE_NAME ./##################################################_long/#################################################_long.ibd |
| 121 |
TABLESPACE_NAME ##################################################_long/#################################################_long |
| 122 |
FILE_NAME ./test/#################################################_long_.ibd |
| 123 |
TABLESPACE_NAME test/#################################################_long_ |
| 124 |
FILE_NAME ./this_sixty_four_byte_name_is_not_too_long_______________________/this_sixty_four_byte_name_is_not_too_long_______________________.ibd |
| 125 |
TABLESPACE_NAME this_sixty_four_byte_name_is_not_too_long_______________________/this_sixty_four_byte_name_is_not_too_long_______________________ |
| 126 |
FILE_NAME ./twenty_byte_db_name_/#################################################_long_.ibd |
| 127 |
TABLESPACE_NAME twenty_byte_db_name_/#################################################_long_ |
| 128 |
# |
| 129 |
# Cleanup |
| 130 |
# |
| 131 |
DROP TABLE `this_sixty_four_byte_name_is_not_too_long_______________________`.`this_sixty_four_byte_name_is_not_too_long_______________________`; |
| 132 |
DROP TABLE `test`.`#################################################_long_`; |
| 133 |
DROP TABLE `twenty_byte_db_name_`.`#################################################_long_`; |
| 134 |
DROP TABLE `##################################################_long`.`#################################################_long`; |
| 135 |
DROP DATABASE `this_sixty_four_byte_name_is_not_too_long_______________________`; |
| 136 |
DROP DATABASE `##################################################_long`; |
| 137 |
DROP DATABASE `twenty_byte_db_name_`; |