无法更改表，表"xxx/#sql-ib265"已存在 [英] unable to alter table, Table 'xxx/#sql-ib265' already exists

问题描述

我在数据库xxx中有一个mysql表y，我尝试在使用前更改压缩类型

I have a mysql table y in database xxx which I attempted to change compression type before using

    alter table y row_format=compressed key_block_size=8

该过程停止了一半.我在mysql lib目录中删除了临时文件#sql-ib265.frm和#sql-ib265"，然后重新启动了服务器.然而 现在，当我再次尝试更改表y(使用与上面相同的命令)时，我得到了错误.

the process stopped half way. I removed temp file '#sql-ib265.frm and #sql-ib265' in mysql lib directory and restarted the server. However Now when I attempt the alter table y (with the same command above) again I get error.

    ERROR 1050 (42S01) at line 1: Table 'xxx/#sql-ib265' already exists

我无法删除表'xxx/#sql-ib265'，因为找不到该表. 我该怎么办?

I can't drop table 'xxx/#sql-ib265' because it can't be found. what should I do?

编辑 解决方案:

I ended up dropping the old database and recreate the database.

推荐答案

尝试使用--skip-auto-rehash选项重新启动mysql客户端，然后再次尝试DROP TABLE.

Try to restart mysql client with the --skip-auto-rehash option and try DROP TABLE again.

如果上述方法不起作用，请从MySQL手册中尝试以下方法:

If above does not work, try this from MySQL Manual:

您的innodb数据字典已损坏.

You have a corrupt innodb data dictionary..

https://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html

临时表问题

如果MySQL在ALTER TABLE操作的中间崩溃，您可能会在InnoDB表空间内得到一个孤立的临时表.使用表监视器，您可以看到列出的表以#sql-开头.如果将名称包含在反引号中，则可以在名称包含字符#"的表上执行SQL语句.因此，您可以使用前面介绍的方法像其他任何孤立表一样删除这样的孤立表.要在Unix Shell中复制或重命名文件，如果文件名包含#"，则需要在文件名中用双引号引起来.

If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. Using the Table Monitor, you can see listed a table with a name that begins with #sql-. You can perform SQL statements on tables whose name contains the character "#" if you enclose the name within backticks. Thus, you can drop such an orphaned table like any other orphaned table using the method described earlier. To copy or rename a file in the Unix shell, you need to put the file name in double quotation marks if the file name contains "#".

这篇关于无法更改表，表"xxx/#sql-ib265"已存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！