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