< TABLE > 的表存储引擎在按查询订购时没有这个选项(ERROR 1031)

< TABLE > 的表存储引擎没有这个选项。

这是 MySQL 在 order by查询中返回的错误。列类型为 varchar(2000)。

质疑:

select * from `dbo.table_1` order by textT;

返回错误:

ERROR 1031(HY000) : ‘ dbo.Table _ 1’的表存储引擎没有这个选项。

为什么会这样，我该怎么补救？

69910 次浏览
小开

I get the same error when I import a table definition that's InnoDB with ROW_FORMAT=DYNAMIC in it. The table was created with a MyISAM engine but I later switched it to InnoDB. When I removed the ROW_FORMAT=DYNAMIC from the create table statement and recreated the table it worked fine. My solution to your problem would be this.

show create table `dbo.table_1`;

then take the output from that command and remove the ROW_FORMAT=DYNAMIC then rename the table to dbo.table_1_old

rename table `dbo.table_1` to `dbo.table_1_old`;

Then execute the create table statement from the first step i.e.

-- don't use this create as there are missing columns use yours
create table `dbo.table_1` (textT VARCHAR(255));

Then repopulate your table with the old data.

insert into `dbo.table_1` select * from `dbo.table_1_old`;

Then you should be able to execute your original SQL

select * from `dbo.table_1` order by textT;
小开
最佳答案

This problem appears to occur when you're importing a table definition that had been created with MyISAM but later was switched to InnoDB; the resulting ROW_FORMAT options appear to be invalid.

If you're trying to import an exported database and encounter this problem, you can simply search and replace ROW_FORMAT=FIXED with nothing.

I used the following to do so really quickly:

sed -ie 's/ROW_FORMAT=FIXED//g' backup.sql

Problem solved! Thanks to jbrahy for pointing out that it was the ROW_FORMAT that was the problem.

EDIT: Updated to work for more platforms as per @seven's suggestion

EDIT2: Also note, as per @Steen-Schütt, this may be a safer fix

sed -ie 's/ROW_FORMAT=FIXED/ROW_FORMAT=COMPACT/g' backup.sql
小开

You can also try this: 

ALTER TABLE `dbo.table_1` ROW_FORMAT = DEFAULT ;
小开

This problem appears to occur when you're importing a table definition to MySQL 5.7 that had been created with MySQL 5.6 and earlier. The same error can produceb by option KEY_BUFFER_SIZE=8192 and similar sizes defined in bytes for INNODB ENGINE. I had this error when I'm importing base from sql-dump. Decision: sed -ie 's/KEY_BLOCK_SIZE=16384//g' my-file-sql_dump.sql

小开

I was facing this problem and my backup file was encrypted .zsql file. So I modified my.cnf by adding innodb_strict_mode = off. It worked fine


提交答案