ERROR 1050 (42S01): Table 'crmdb/#sql-ib269' already exists

一个一亿多条数据的表，在创建primary key的时候时间过长，vpn timeout导致创建中断，重新连接之后重建主键报错：

mysql> alter table OFFER_MEMBER  add constraint PK_OFFER_MEMBER primary key (OFFER_MEMBER_ID);
ERROR 1050 (42S01): Table 'crmdb/#sql-ib269' already exists

在数据目录下：

# pwd
/mysql/data/crmdb

# ll \#sql-2241_1*
-rw-rw---- 1 mysql mysql 9204 Aug 20 23:37 #sql-2241_10.frm
-rw-rw---- 1 mysql mysql 9176 Aug 20 23:23 #sql-2241_12.frm

-rw-rw---- 1 mysql mysql 9176 Aug 20 23:23 #sql-ib269.ibd

执行删除主键命令：

mysql> alter table offer_member drop primary key;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists

rm掉临时文件之后重启数据库，报错：

2014-08-21 10:35:15 31001 [ERROR] InnoDB: Could not find a valid tablespace file for 'crmdb/#sql-ib269'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

临时解决方案是create as 方法copy之后drop：

mysql> create table OFFER_MEMBER_bk as select * from OFFER_MEMBER;

mysql> drop table OFFER_MEMBER;

mysql> rename table OFFER_MEMBER_bk to OFFER_MEMBER;

mysql> show index from  OFFER_MEMBER;
Empty set (0.00 sec)

mysql> alter table OFFER_MEMBER  add constraint PK_OFFER_MEMBER primary key (OFFER_MEMBER_ID);

不再报错

重建index和主键.

问了度娘，下面url中有类似解决方案，但是感觉不适合我这种情况：

http://www.blogjava.net/xiaomage234/archive/2014/07/25/416200.html  （有时间好好研究一下他这个场景）