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

Asked Apr 07 '15 at 07:56

Active Apr 07 '21 at 22:34

Viewed 7.3k times

90

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?

mysql sql

edited Aug 05 '16 at 09:10
TRiG

10,148

7

57

107

asked Apr 07 '15 at 07:56
Umair Iqbal

1,405

2

14

24

use separate backticks for both database name `dbo` and table name `table_1` – Ravinder Reddy Apr 07 '15 at 07:58

statement should look like `select * from \`dbo\`.\`table_1\` order by textT` – Ravinder Reddy Apr 07 '15 at 08:00

May be an issue of `max_length_for_sort_data` – Abhik Chakraborty Apr 07 '15 at 08:00

Check this: http://dev.mysql.com/doc/refman/5.0/en/example-storage-engine.html – Stanislovas Kalašnikovas Apr 07 '15 at 08:01

@Ravinder I used backticks but error return consistantly. – Umair Iqbal Apr 07 '15 at 08:03

Please edit and add result of `show create table dbo.table_1` to your posting – Ravinder Reddy Apr 07 '15 at 08:05

@UmairIqbal: And also look into the storage engine page referred by `Stanislovas Kalašnikovas` – Ravinder Reddy Apr 07 '15 at 08:07

2

You have left out three important bits of info, and none of the commenters have asked for them. -- What Version of MySQL? Which Storage ENGINE? Provide `SHOW CREATE TABLE table_1`. – Rick James Apr 08 '15 at 23:40

I got this error when trying to load a 5.5 dump using a 5.5 server, in case it might help someone. – deivid Apr 21 '16 at 18:22

5 Answers5

185

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`

edited Apr 07 '21 at 22:34

jhaagsma

answered May 03 '16 at 20:09
jhaagsma

2,414

2

24

26

This worked for me, I was blocked from importing from a dump file, and now it imports. – blackwood May 10 '16 at 15:08

nice quickie! saved me – Tim Kretschmer Sep 03 '16 at 17:53

2

@haagsma suggesting edit for "-ie" instead of just sed -e. -ie works both on GNU and BSD versions of sed. I am os osx and -i works differently) ```sed -ie 's/ROW_FORMAT=FIXED//g' backup.sql``` – seven Sep 07 '16 at 21:08

3

If @jbrahy pointed out the problem, shouldn't he be awarded the answer? Seems weird that he has 4 points and you have 32 when his was the correct answer. – Johnny 3653925 Nov 30 '16 at 23:07

I think this error is mostly thrown when importing a backup; and as this is the quickest way to fix the issue in that case... I can edit my answer to include some more details of the problem though. – jhaagsma Nov 30 '16 at 23:51

@SivaKaruppiah details? – jhaagsma Nov 26 '19 at 16:24

I'm getting "Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead" error – Siva Karuppiah Nov 27 '19 at 00:29

That looks like it's related to this: https://stackoverflow.com/questions/13283381/row-size-too-large-error-in-mysql-create-table-query You are have an additional problem related to conversion. Try replacing longer varchars with text in that case. – jhaagsma Nov 28 '19 at 16:23

1

According to [this bug report](https://bugs.mysql.com/bug.php?id=77625), `FIXED` is silently changed to `COMPACT` in MySQL 5.6 and before. That seems to suggest that `sed -ie 's/ROW_FORMAT=FIXED/ROW_FORMAT=COMPACT/g' backup.sql` would be a "safer" solution in terms of maintaining the same behaviour. – Steen Schütt Dec 11 '20 at 12:48

13

You can also try this:

ALTER TABLE `dbo.table_1` ROW_FORMAT = DEFAULT ;

answered Sep 04 '17 at 08:01
Michele Manzato

156

1

2

Thank you, this worked perfectly for me and saved me having to rebuild innodb multiple tables full of foreign keys. – techjp Sep 23 '17 at 06:36

11

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;

answered Aug 18 '15 at 21:49
jbrahy

4,228

1

42

54

2

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

answered Sep 19 '17 at 00:46
ANF-67

21

1

2

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

answered Feb 06 '18 at 09:57
prgmrDev

910

1

10

20

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?

use separate backticks for both database name `dbo` and table name `table_1` — Ravinder Reddy, Apr 07 '15 at 07:58
statement should look like `select * from \`dbo\`.\`table_1\` order by textT` — Ravinder Reddy, Apr 07 '15 at 08:00
Check this: http://dev.mysql.com/doc/refman/5.0/en/example-storage-engine.html — Stanislovas Kalašnikovas, Apr 07 '15 at 08:01
Please edit and add result of `show create table dbo.table_1` to your posting — Ravinder Reddy, Apr 07 '15 at 08:05
@UmairIqbal: And also look into the storage engine page referred by `Stanislovas Kalašnikovas` — Ravinder Reddy, Apr 07 '15 at 08:07
You have left out three important bits of info, and none of the commenters have asked for them. -- What Version of MySQL? Which Storage ENGINE? Provide `SHOW CREATE TABLE table_1`. — Rick James, Apr 08 '15 at 23:40
I got this error when trying to load a 5.5 dump using a 5.5 server, in case it might help someone. — deivid, Apr 21 '16 at 18:22

jhaagsma · Accepted Answer · 2021-04-07T22:34:45.130

185

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

edited Apr 07 '21 at 22:34

answered May 03 '16 at 20:09

jhaagsma

2,414
2
24
26

This worked for me, I was blocked from importing from a dump file, and now it imports. – blackwood May 10 '16 at 15:08
nice quickie! saved me – Tim Kretschmer Sep 03 '16 at 17:53
2

@haagsma suggesting edit for "-ie" instead of just sed -e. -ie works both on GNU and BSD versions of sed. I am os osx and -i works differently) ```sed -ie 's/ROW_FORMAT=FIXED//g' backup.sql``` – seven Sep 07 '16 at 21:08
3

If @jbrahy pointed out the problem, shouldn't he be awarded the answer? Seems weird that he has 4 points and you have 32 when his was the correct answer. – Johnny 3653925 Nov 30 '16 at 23:07
I think this error is mostly thrown when importing a backup; and as this is the quickest way to fix the issue in that case... I can edit my answer to include some more details of the problem though. – jhaagsma Nov 30 '16 at 23:51
@SivaKaruppiah details? – jhaagsma Nov 26 '19 at 16:24
I'm getting "Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead" error – Siva Karuppiah Nov 27 '19 at 00:29
That looks like it's related to this: https://stackoverflow.com/questions/13283381/row-size-too-large-error-in-mysql-create-table-query You are have an additional problem related to conversion. Try replacing longer varchars with text in that case. – jhaagsma Nov 28 '19 at 16:23
1

According to [this bug report](https://bugs.mysql.com/bug.php?id=77625), `FIXED` is silently changed to `COMPACT` in MySQL 5.6 and before. That seems to suggest that `sed -ie 's/ROW_FORMAT=FIXED/ROW_FORMAT=COMPACT/g' backup.sql` would be a "safer" solution in terms of maintaining the same behaviour. – Steen Schütt Dec 11 '20 at 12:48

score 13 · Answer 2 · answered Sep 04 '17 at 08:01

13

You can also try this:

ALTER TABLE `dbo.table_1` ROW_FORMAT = DEFAULT ;

answered Sep 04 '17 at 08:01

Michele Manzato

156
1
2

Thank you, this worked perfectly for me and saved me having to rebuild innodb multiple tables full of foreign keys. – techjp Sep 23 '17 at 06:36

score 11 · Answer 3 · answered Aug 18 '15 at 21:49

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;

score 2 · Answer 4 · answered Sep 19 '17 at 00:46

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

score 2 · Answer 5 · answered Feb 06 '18 at 09:57

2

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

answered Feb 06 '18 at 09:57

prgmrDev

910
1
10
20