Umair Iqbal Umair Iqbal - 7 months ago 44
SQL Question

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

Table storage engine for doesn't have this option on order by query error return on mysql, column type varchar(2000).

The Query which which I run is:

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


Error return:


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


Please tell me the reason if any one knows.

Answer

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;
Comments