一个一亿多条数据的表,在创建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 (有时间好好研究一下他这个场景)