MySQL Bugs: #10035: #1030 - Got error 139 from storage engine

Bug #10035	#1030 - Got error 139 from storage engine
Submitted:	20 Apr 2005 17:07	Modified:	16 Jun 2005 16:47
Reporter:	Andrew Blee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.11	OS:	MacOS (MacOS X 10.3.9)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
Since upgrading MySQL from version 4.0.xx to 4.1.11, when trying to place data into a record it can fail with the error "#1030 - Got error 139 from storage engine". The problem appears to be if the data reaches a certain size. I first found the problem when trying to import a dump created by phpMyAdmin for one of my tables.

I am using the "Standard" install package from MySQL Web site. Running Apple Mac/MacOS X 10.3.9. PHP is version 4.3.10 (client API is 3.23.49).

I use phpMyAdmin to administer the DB, but I have run tests using PHP and the problem still occurs.

I have also performed tests on my ISP's server (UNIX based) and the problem also occurs, they are using MySQL client API of 4.1.11.

I still have access to a 4.0 server and can import the dump with no problems at all, even using a dump from MySQL 4.1.11 using the "backward compatibility" option in phpMyAdmin.

How to repeat:
Create an InnoDB table with 1 INT field and 11 TEXT fields. 

Create an index on the INT field of type "PRIMARY". 

In the first 10 text fields, enter as many "a" characters as phpMyAdmin will allow. This is 32000 characters in each field. Leave the last text field empty. Click "Go" to save the changes. This should save without any problems. 

Then copy/paste field 10 to field 11 and click "Go". This results in a "#1030 - Got error 139 from storage engine" error.

If I remove the characters little by little at some point it will work, suggesting a size problem. 

Please let me know if you need any additional information.

Hi!

In 4.1, to support at least 256-character UTF-8 column prefix indexes, InnoDB stores at least 768 bytes of each column 'internally' to the record. With 11 TEXT fields you will run over the 8000 byte record len limit.

./include/dict0mem.h:159:#define DICT_MAX_COL_PREFIX_LEN        768

I probably need to update the manual.

Thank you,

Heikki

Hi Heikki

Many thanks for the reply.

I read what you wrote several times, but a lot of it still went over my head, so apologies if what I write below is irrelevant :-)

Is this 8000 byte limit you mention the same one mentioned in section 15.17 of the manual, i.e. because of the 16k database page size?

After reading the manual, section 15.17, I know:

1. The Database Page Size is set as standard as 16k - server needs recompiling to use a new limit. From what I can see this setting affects the maximum row length for NON TEXT and BLOB fields. If I set this limit to 64k I would expect to get a maximum of 41 TEXT columns before this error occured?

2. Maximum Row Length for TEXT and BLOB columns is not mentioned, but 4GB limit is mentioned for LONGTEXT and LONGBLOB columns.

3. Total Row Length cannot exceed 4GB.

4. "The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes." - Not sure what this means :-)

You mentioned "column prefix indexes", not sure if the index I created is the same thing so I removed this as well as the INT field and the problem still occurs. I.e. I had a record with 11 TEXT fields only.

As a standard build, I find it hard to believe MySQL/InnoDB cannot support 11 TEXT fields in one table. An 8000 byte limit for "internal" information about column prefix indexes seems very small.

Are there any links I can read to help my confusion about what limits apply and when. More importantly what changes can I make to help me solve this problem.

Many thanks for your time.

I have a similar problem. I use an InnoDB table with 1 mediumint unsigned column, 1 varchar(200) column and 24 text columns (for localization of all text of my web site). When I try to import a dump file (created on MySQL 4.0.24) into MySQL 4.1.11, many rows of this table are simply ignored and the error 139 is thrown during the import process.
Is there any chance to have this problem fixed in the future? Yes, I can make a workaround of splitting the table into more tables with less number of columns but it seems to be a very naughtly solution to me.
Thanks for any help.

Hi Filip

I never got a response to my second post so had no choice but to split my tables into smaller tables. Seems absolutely crazy to me that upgrading to a newer version breaks existing tables!

I could never work out whether the problem was the amount of fields, or the amount of data within the fields, to me it seems to be a mixture of both.

I also tried posting in the InnoDB forum but didn't get success there either.

I just wish I could read something somewhere that gave a nice clear answer to the problem and provide possible solutions.

Thanks for your reaction Andrew. At least I know the problem is not on my side.

Heikki: Is there a chance to support tables like the described one in the InnoDB engine? If not, is the only solution for me to split the table to several smaller ones or would you recommend something else?
Thank you.

Hi!

Splitting the table into smaller ones is the way to solve this. It will also increase the portability to other database brands, since many brands have the maximum row length even smaller than 8000 bytes.

Regards,

Heikki

Heikki

Is there any documentation anywhere that explains why the maximum amount of fields allowed in a table varies depending on the data stored within the fields?

Many thanks.

Andy.

I updated:

 <section id="innodb-restrictions">

...

   <listitem><para>
    The maximum row length, except for <literal>VARCHAR</literal>,
    <literal>BLOB</literal> and
    <literal>TEXT</literal> columns, is slightly less than half of a
    database page. That is, the maximum row length is about 8000 bytes.
    <literal>LONGBLOB</literal> and <literal>LONGTEXT</literal> columns
    must be less than 4GB, and the total row length, including also
    <literal>BLOB</literal> and <literal>TEXT</literal> columns, must be
    less than 4GB. <literal>InnoDB</literal> stores the first 768 bytes
    of a <literal>VARCHAR</literal>,
    <literal>BLOB</literal>, or <literal>TEXT</literal> column in
    the row, and the rest into separate pages.
   </para></listitem>

Regards,

Heikki

Para aqueles que sabem português, e está com este problema, gostaria de deixar claro que o erro: Got error 139 from storage engine até este presente momento neste forum teve somente uma solução:

Dividir os campos text em outras tabelas, no meu caso vou criar uma tabela mensagem e cada registro nessa será como um campo.

ex.

Old Table
--------------
tbl_messages
txt_msg1
txt_msg2
txt_msg3
txt_msg4
txt_msg5
txt_msg6
txt_msg7
txt_msg8
txt_msg9
txt_msg10
txt_msg11

New Table
------------

table_messages
id_msg  |  txt_msg  | dtm_msg

i using 11 text filds in table type InnoDB.
estou usando 11 campos texto em uma tabela do tipo innodb.

Boa sorte a todos!!

i would like to suggest a better way to do localization than have a table with 24 columns of type TEXT. please see my post on sitepoint.com here: http://www.sitepoint.com/forums/showpost.php?p=2117922&postcount=6

Para evitar ese error lo mejor es pasar las tablas a MYISAM o trabajar con maximo 10 TEXT por tabla