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
Triage: Triaged: D2 (Serious)

[9 Dec 2009 11:27] Shane Bester
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
[9 Dec 2009 14:56] Valeriy Kravchuk
Item 3 of suggested fix is already reported and verified as Bug #24091.
[9 Dec 2009 15:08] Valeriy Kravchuk
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.
[9 Dec 2009 15:11] Valeriy Kravchuk
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);
[11 Jan 2011 6:31] Roel Van de Paar
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!
[11 Jan 2011 7:46] Roel Van de Paar
Minimal testcase:

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (`c2` decimal(1,1) NOT NULL) ENGINE=ARCHIVE;
CREATE INDEX `i2` ON `t2`(`c2`);
[12 Jan 2011 16:10] Omer Barnir
triage: changing tag from CHECKED to SRMRTBD
[20 Oct 2011 15:59] Paul Dubois
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.