MySQL Bugs: #49556: ERROR 1005 (HY000): Can't create table '#sql-b7c

Bug #49556	ERROR 1005 (HY000): Can't create table '#sql-b7c_3' (errno: -1)
Submitted:	9 Dec 2009 11:27	Modified:	20 Oct 2011 15:59
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.0.89, 5.1.43, 5.1.55, 5.5.99-m3, 5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
when altering some tables I found this in the error log of mysqld:

091209 13:17:25 [ERROR] Found wrong key definition in #sql-1c54_7; Please do "ALTER TABLE '#sql-1c54_7' FORCE " to fix it!

of course, this is incorrect syntax according to the manual, and:

mysql> ALTER TABLE '#sql-1c54_7' FORCE ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds

If anything, it should be backticks:

mysql>  ALTER TABLE `#sql-1c54_7` FORCE;
ERROR 1146 (42S02): Table 'test.#sql-1c54_7' doesn't exist

How to repeat:
do what the error says. notice syntax error. try find out what is force option. notice it's undocumented.

i'll attempt to give the sql that caused the output of the error, just for completeness.

Suggested fix:
1. put backticks surrounding the table name in the error message.
2. don't print the error for temporary tables where it is meaningless.
3. clearly document this "alter table .. force" syntax in 
   http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Item 3 of suggested fix is already reported and verified as Bug #24091.

Verified by code review. This is what grep shows (in recent 5.1.43 sources):

sql/table.cc:1261:                      "Please do \"ALTER TABLE '%s' FORCE\" to fix it!",
sql/table.cc:1267:                          "Please do \"ALTER TABLE '%s' FORCE\" to fix it!",
sql/table.cc:1471:                            "Please do \"ALTER TABLE '%s' FORCE \" to fix it!",
sql/table.cc:1477:                                "Please do \"ALTER TABLE '%s' FORCE\" to fix "
...

Here '%s' is wrong, as Shane explained.

MySQL 5.0.x is also affected:

sql/table.cc:618:      sql_print_error("Found incompatible DECIMAL field '%s' in %s; Please do \"ALTER TABLE '%s' FORCE\" to fix it!", share->fieldnames.type_names[i], name, share->table_name);
sql/table.cc:621:                          "Found incompatible DECIMAL field '%s' in %s; Please do \"ALTER TABLE '%s' FORCE\" to fix it!", share->fieldnames.type_names[i], name, share->table_name);
sql/table.cc:807:              sql_print_error("Found wrong key definition in %s; Please do \"ALTER TABLE '%s' FORCE \" to fix it!", name, share->table_name);
sql/table.cc:810:                                  "Found wrong key definition in %s; Please do \"ALTER TABLE '%s' FORCE\" to fix it!", name, share->table_name);

Reproducible Testcase:

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (`c1` decimal(19,14) unsigned zerofill NOT NULL) ENGINE=ARCHIVE DEFAULT CHARSET=latin1;
CREATE INDEX `i1` ON `t1`(`c1`);

CLI:
mysql> CREATE INDEX `idx67` ON `t50`(`col94`);
ERROR 1005 (HY000): Can't create table 'roelt.#sql-b7c_3' (errno: -1)

Error log:
110111 17:19:01 [ERROR] Found wrong key definition in #sql-b7c_3; Please do "ALTER TABLE '#sql-b7c_3' FORCE " to fix it!

Minimal testcase:

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (`c2` decimal(1,1) NOT NULL) ENGINE=ARCHIVE;
CREATE INDEX `i2` ON `t2`(`c2`);

triage: changing tag from CHECKED to SRMRTBD

Noted in 5.6.4 changelog.

InnoDB used incorrect identifier quoting style in one of its error
messages, which resulted in an error if a user followed the
suggestion in the message.