Table storage engine for <TABLE> doesn't have this option on order by query (ERROR 1031)

Question

Table storage engine for <TABLE> doesn't have this option.

This is the error returned by MySQL on an order by query. The column type is varchar(2000).

Query:

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

Error returned:

ERROR 1031 (HY000): Table storage engine for 'dbo.table_1' doesn't have this option.

Why does this happen? And how can I fix it?

Answer 1

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