Description:
Creating a table with a trailing space in the name using backtick quotes causes the server to create the files on-disk with inconsistent filenames. This occurs when a user accidentally or intentionally creates a table with one or more spaces in the end. The .frm file is named with the spaces intact, but the .MYD and .MYI files are named with the spaces stripped. This seems to affect ISAM, MyISAM, and InnoDB tables and may affect others.
When creating a new table, this causes common operations (ie, INSERT, SELECT, DELETE) to fail on the new table with "Table 'database.table ' doesn't exist" errors.
If the user picks the name of an existing table and creates a table with that name plus trailing spaces, the new (empty) .MYD (and probably .MYI) file clobbers the old one.
MySQL 4.0 rejects table names with trailing spaces, but the bug is present all 3.23.xx servers I tried, binary and compiled from source, including in the 3.23.58 release compiled by hand.
How to repeat:
Creating a new table:
mysql> CREATE TABLE `test `
(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(60),
PRIMARY KEY (id)
);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into `test ` values (0,'test');
ERROR 1146: Table 'test.test ' doesn't exist
mysql> select * from `test `;
ERROR 1146: Table 'test.test ' doesn't exist
mysql> delete from `test `;
ERROR 1146: Table 'test.test ' doesn't exist
shell> ls -l /var/lib/mysql/test
-rw-rw---- 1 mysql mysql 8574 Jun 17 11:19 test .frm
-rw-rw---- 1 mysql mysql 0 Jun 17 11:19 test.MYD
-rw-rw---- 1 mysql mysql 1024 Jun 17 11:19 test.MYI
Clobbering an existing table:
mysql> CREATE TABLE `test`
(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(60),
PRIMARY KEY (id)
);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values (0, 'test 1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (0, 'test 2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (0, 'test 3');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | test 1 |
| 2 | test 2 |
| 3 | test 3 |
+----+--------+
3 rows in set (0.01 sec)
shell> ls -l /var/lib/mysql/test
-rw-rw---- 1 mysql mysql 60 Jun 17 15:16 test.MYD
-rw-rw---- 1 mysql mysql 2048 Jun 17 15:16 test.MYI
-rw-rw---- 1 mysql mysql 8574 Jun 17 15:16 test.frm
mysql> CREATE TABLE `test `
(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(60),
PRIMARY KEY (id)
);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
shell> ls -l /var/lib/mysql/test
-rw-rw---- 1 mysql mysql 8574 Jun 17 15:17 test .frm
-rw-rw---- 1 mysql mysql 0 Jun 17 15:17 test.MYD
-rw-rw---- 1 mysql mysql 1024 Jun 17 15:17 test.MYI
-rw-rw---- 1 mysql mysql 8574 Jun 17 15:16 test.frm
Suggested fix:
Make trailing-space stripping consistent when calculating the various filenames, or make table names with trailing spaces illegal, as in MySQL 4.0.
Description: Creating a table with a trailing space in the name using backtick quotes causes the server to create the files on-disk with inconsistent filenames. This occurs when a user accidentally or intentionally creates a table with one or more spaces in the end. The .frm file is named with the spaces intact, but the .MYD and .MYI files are named with the spaces stripped. This seems to affect ISAM, MyISAM, and InnoDB tables and may affect others. When creating a new table, this causes common operations (ie, INSERT, SELECT, DELETE) to fail on the new table with "Table 'database.table ' doesn't exist" errors. If the user picks the name of an existing table and creates a table with that name plus trailing spaces, the new (empty) .MYD (and probably .MYI) file clobbers the old one. MySQL 4.0 rejects table names with trailing spaces, but the bug is present all 3.23.xx servers I tried, binary and compiled from source, including in the 3.23.58 release compiled by hand. How to repeat: Creating a new table: mysql> CREATE TABLE `test ` ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(60), PRIMARY KEY (id) ); Query OK, 0 rows affected (0.00 sec) mysql> insert into `test ` values (0,'test'); ERROR 1146: Table 'test.test ' doesn't exist mysql> select * from `test `; ERROR 1146: Table 'test.test ' doesn't exist mysql> delete from `test `; ERROR 1146: Table 'test.test ' doesn't exist shell> ls -l /var/lib/mysql/test -rw-rw---- 1 mysql mysql 8574 Jun 17 11:19 test .frm -rw-rw---- 1 mysql mysql 0 Jun 17 11:19 test.MYD -rw-rw---- 1 mysql mysql 1024 Jun 17 11:19 test.MYI Clobbering an existing table: mysql> CREATE TABLE `test` ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(60), PRIMARY KEY (id) ); Query OK, 0 rows affected (0.01 sec) mysql> insert into test values (0, 'test 1'); Query OK, 1 row affected (0.00 sec) mysql> insert into test values (0, 'test 2'); Query OK, 1 row affected (0.00 sec) mysql> insert into test values (0, 'test 3'); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +----+--------+ | id | name | +----+--------+ | 1 | test 1 | | 2 | test 2 | | 3 | test 3 | +----+--------+ 3 rows in set (0.01 sec) shell> ls -l /var/lib/mysql/test -rw-rw---- 1 mysql mysql 60 Jun 17 15:16 test.MYD -rw-rw---- 1 mysql mysql 2048 Jun 17 15:16 test.MYI -rw-rw---- 1 mysql mysql 8574 Jun 17 15:16 test.frm mysql> CREATE TABLE `test ` ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(60), PRIMARY KEY (id) ); Query OK, 0 rows affected (0.00 sec) mysql> select * from test; Empty set (0.00 sec) shell> ls -l /var/lib/mysql/test -rw-rw---- 1 mysql mysql 8574 Jun 17 15:17 test .frm -rw-rw---- 1 mysql mysql 0 Jun 17 15:17 test.MYD -rw-rw---- 1 mysql mysql 1024 Jun 17 15:17 test.MYI -rw-rw---- 1 mysql mysql 8574 Jun 17 15:16 test.frm Suggested fix: Make trailing-space stripping consistent when calculating the various filenames, or make table names with trailing spaces illegal, as in MySQL 4.0.