MySQL Bugs: #12325: Unable to create table with the name nu

Bug #12325	Unable to create table with the name nu
Submitted:	2 Aug 2005 19:44	Modified:	1 Sep 2005 17:53
Reporter:	Matt Garrish	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.13/5.0.XX	OS:	Windows (WinXP)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
If you try to create a table with the name "nu" you get the following error:

mysql> create table nu (mytest text);
ERROR 1017 (HY000): Can't find file: '.\nutemp\nu.frm' (errno: 13)

As nu is the postal abbreviation for Nunavut in Canada, this makes for a significant problem for me. I cannot upgrade until this is fixed, and I'd really like to make use of subselects.

How to repeat:
1. Default install of MySQL 4.1.13
2. Create any database with any name
3. Switch to the database in step two
4. Try to create a table called "nu" with any number or type of columns and the error will be returned

Suggested fix:
Not known.

This is specify Windows issue. Looks as reserved words on Windows
like COM1, PRN and so on:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.13-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table nu (id int);
ERROR 1017 (HY000): Can't find file: '.\test\nu.frm' (errno: 13)
mysql>

050802 16:44:17 [Note] mysqld-nt: ready for connections.
Version: '5.0.10-beta-nt'  socket: ''  port: 3306  Official MySQL binary
050802 16:48:20 [ERROR] mysqld-nt: Can't find file: '.\test\nu.frm' (errno: 13)

The problem does not occur in 4.0.18. I also started off on a 3.23 version (now lost on me which release), and never had the problem. The error has occurred only since upgrading to 4.1.13.

Im having this too, but with a table name a. (I'm running 5.0.10 on WinXP Prof)

mysql> create table a (c int);
ERROR 1017 (HY000): Can't find file: '.\test\a.frm' (errno: 13)

There's also a problem with int1 as tablename:

mysql> create table int1(c int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int1(
c int)' at line 1

But the error message is different, so maybe this is unrelated and there's another reason why you can do this.

Seems table name c is a problem too (WinXP, MySQL 5.0.10)

mysql> create table c(code int, week int, score int);
ERROR 1017 (HY000): Can't find file: '.\test\c.frm' (errno: 13)

I'm afraid there is an improper string comparison at my_access.c: check_if_legal_filename(). This function checks if the given table name is one of reserved names. Reserved names are defined as below(Ver 5.0.11). 

static const char *reserved_names[]={ "CON", "PRN", "AUX", "NUL", "COM1", "COM2", "COM3", "COM4", "COM5", "COM6",
  "COM7", "COM8", "COM9", "LPT1", "LPT2", "LPT3", "LPT4", "LPT5", "LPT6",
  "LPT7", "LPT8", "LPT9", "CLOCK$",  NullS};

For now, if one of the reserved names is "ABCDEFG", table "A", "AB", "ABC", ..., "ABCDEF" can't be created as well as "ABCDEFG".
Is this as designed???

nu, a, c,

intl

I'm sorry. The last two lines (nu, a, c, and intl) are just rubbish.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28710

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28792

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29145

Fixed in 4.1.15 and 5.0.13.

Noted in 4.1.15, 5.0.13 changelogs.

This bugs affects views too!
Has this been fixed ?

DROP VIEW IF EXISTS `test`.`co`;
CREATE VIEW `test`.`co` AS ( SELECT * FROM mytable );

returns

Error Code : 1050
Table 'co' already exists
(0 ms taken)

I just got a 5.0.13 win binary from FTP mirrors.

It still IS NOT possible to create neither a table nor a view named 'co'.
Err. msg. goes "table allready exists".

But any other (legal) name seems to work!

I found this problem after upgrade from 4.1.12a to 4.1.14.

4.1.12a works OK. You can use 4.1 features and create table 'nu' (tested).