MySQL Bugs: #5856: ALTER TABLE fails for InnoDB tables with '#' in table name

Bug #5856	ALTER TABLE fails for InnoDB tables with '#' in table name
Submitted:	2 Oct 2004 3:50	Modified:	30 Dec 2004 23:03
Reporter:	John Ivski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1.5	OS:	Windows (Windows XP/Linux)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
According to the documentation, '#' character is allowed in table names.

InnoDB tables with '#' character in their names can be created and accessed without any problems. If, however, an attempt is made to issue ALTER on such table, MySQL error 1005 is returned, e.g.:

ERROR 1005 (HY000): Can't create table '.\testdb\#sql-a20_212f.frm' (errno: -1)

A record appears in the error log:
------
InnoDB: Error: could not find the table being ALTERED in:
ALTER TABLE `
-------

Apparently, the '#' character is being treated as comment, despite being quoted with backticks. MyISAM tables are not affected by this bug.

How to repeat:
CREATE TABLE `#test` (
  `column1` INT
  ) ENGINE=InnoDB;

ALTER TABLE `#test` CHANGE COLUMN `column1` `column2` INT;

Tested also on Linux with latest BK source.

Your MySQL connection id is 1 to server version: 4.1.6-gamma-debug-log

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

mysql> use test;
Database changed
mysql> CREATE TABLE `#test` (
    ->   `column1` INT
    ->   ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE `#test` CHANGE COLUMN `column1` `column2` INT;
ERROR 1005 (HY000): Can't create table './test/#sql-77c2_1.frm' (errno: -1)

mysql> ALTER TABLE `#test` engine=myisam;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `#test` CHANGE COLUMN `column1` `column2` INT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Hi!

Thank you for the bug report! You were right.

Best regards,

Heikki

/*************************************************************************
Removes MySQL comments from an SQL string. A comment is either
(a) '#' to the end of the line,
(b) '--<space>' to the end of the line, or
(c) '<slash><asterisk>' till the next '<asterisk><slash>' (like the familiar
C comment syntax). */
static
char*
dict_strip_comments(
/*================*/
                                        /* out, own: SQL string stripped from
                                        comments; the caller must free this
                                        with mem_free()! */
        const char*     sql_string)     /* in: SQL string */
{
        char*           str;
        const char*     sptr;
        char*           ptr;

        str = mem_alloc(strlen(sql_string) + 1);

        sptr = sql_string;
        ptr = str;

        for (;;) {
scan_more:
                if (*sptr == '\0') {
                        *ptr = '\0';

                        ut_a(ptr <= str + strlen(sql_string));

                        return(str);
                }

                if (*sptr == '#'
                    || (0 == memcmp("-- ", sptr, 3))) {
                        for (;;) {
                                /* In Unix a newline is 0x0A while in Windows
                                it is 0x0D followed by 0x0A */

                                if (*sptr == (char)0x0A
                                    || *sptr == (char)0x0D
                                    || *sptr == '\0') {

ChangeSet@1.2025, 2004-10-04 16:24:37+03:00, marko@hundin.mysql.fi
  InnoDB: make ALTER TABLE to work on table names containing '#' (Bug #5856)

The above patch fixes the problem in mysql 4.0, and it will be propagated to mysql 4.1.

created table using RogueWave SourcePro (v6).  Generated table create sql was:

create table tableName (colDefList) type = InnoDB

next, we used RWDBOSql to provide a generated sql statement string:

ALTER TABLE tableName ADD CONSTRAINT PRIMARY KEY (colListForKey)

Tried the same sql in MySQL COmmand prompt on Linux.
On execute we get:
ERROR 1005: Can't create table './directoryName/#temptablename.frm' (errno: 150)

As I said, same result regardless of having call from software generated our tables, or from MySQL command prompt.

Resolution I found, but don't like:

Step 1:  Create the table using RoqueWave's RWDBDatabase::createTable().
Step 2:  generate manual sql string for RWDBOSql object:
    Alter table tableName engine=MyIsam
Step 3: run the alter table primary key sql
Step 4: generate sql of step 2, changing MyIsam to InnoDB

Program runs correctly, data gets loaded, everything seems to be fine.
Using MySql version 4.1.7 on Linux.

Your report does not have anything to do with this bug, but with another bug (feature request) that foreign key errors should be better reported to the client. For now, you can use the SHOW INNODB STATUS command to view the latest foreign key error.