MYSQL Too much for a InnoDB Indexes?

Thread view:

P.Hill - 25 May 2005 23:15 GMT

I get the following error.

ERROR 1030 (HY000) at line 31143: Got error 139 from storage engine

When trying to insert into a InnoDB table with lots of keys, but
it dies doing an insert.

Any ideas?

Longer Version:

I was trying to move from MyISAM tables to InnoDB tables because I
wanted to specify all kinds of foreign keys since I would like to see
what hibernate tools like hib. synchronizer can do with the information
and I might want to leverage some contraints, see:

http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html

My data was extracted from an old MyIsam database
then I edited the table definitions to say InnoDB
instead of MyISAM, but ran into some kind of max key
length problem.

I read
http://bugs.mysql.com/bug.php?id=10035
and particularly:
http://bugs.mysql.com/bug.php?id=3442

So I upgraded to 5.0.1 and it runs.
But I get exactly the same error. :-(

I tried to specify a key length
KEY `name` (`name`(20)),

But that does nothing to help. I dies on the first
line of the INSERT INTO for a table with the following
keys.
PRIMARY KEY (`id`),
KEY `countryId` (`countryId`),
KEY `name` (`name`(20)),
KEY `dateStart` (`dateStart`(20)),
KEY `dateFinish` (`dateFinish`(20)),
KEY `ded` (`ded`(20)),
KEY `regionId` (`regionId`),
KEY `order` (`order`(20))
}

The create works fine, it just dies when it goes it
think about the insert.

Any ideas?

-Paul

Reply to this Message

P.Hill - 26 May 2005 00:32 GMT

> The create works fine, it just dies when it goes it
> think about the insert.

I broke the inserts into no 50 rows per insert, but 10 or 20 and
things worked just fine. So it was the size of the buffers for
the current set of inserts that was using up whatever resources.

Can anyone suggest if there is something I should be worried about,
given I most often only insert a couple at a time, but read 100s.

-Paul

Reply to this Message

P.Hill - 26 May 2005 07:01 GMT

>> The create works fine, it just dies when it goes it
>> think about the insert.
>
> I broke the inserts into no 50 rows per insert, but 10 or 20 and
> things worked just fine.

I take that back. I have a few (1 at least) large INSERT that still
won't work.

I tried to break up one particularly large insert into a series of
UPDATES and found that

UPDATE `community` SET
`publishedPrimarySources` =
'<S7603>, i. 40, 240, 278, 325, 328, 329, 436, ii. 18, 66, iii. 112;
<S7290>, 4, 8, 34, 36, 52, 118, 354; B&A 1898 [PN=4I027], 108, 117, 120,
121, 168; <S7607>, ii. 78, index;<S7291>, 7, 40, 72, 83; <S7606>, 7, 10,
28, 38, 88; <S7294>, 11, 23; <S7293>, 87, 217, 488, index; <S8419>, 18,
34; <S7804>; <S7770> [?], 458; <S8199>, 42 a; <S10463>, 15 a, 24 a..
For twelfth-century episodes concerning Kildare, see Gerald of Wales,
Conquest of Ireland in <S6193>.'
WHERE id=1130;

Which isn't even 500 characters in a TEXT field. The above results in:
ERROR 1030 (HY000) at line 1: Got error 139 from storage engine

This column is NOT part of any index.

Any suggestions?
-Paul

Reply to this Message

David Harper - 26 May 2005 07:28 GMT

>>> The create works fine, it just dies when it goes it
>>> think about the insert.
[quoted text clipped - 26 lines]
> Any suggestions?
> -Paul

It would help if you could post the full spec of your table (use MySQL's
"show create table" command to get this) as well as the Java code that
you're using to insert the data. The more information you can provide,
the more likely you will be to get a helpful response.

David Harper
Cambridge, England

Reply to this Message

P.Hill - 26 May 2005 07:44 GMT

>> UPDATE `community` SET
>> `publishedPrimarySources` =
[quoted text clipped - 17 lines]
> It would help if you could post the full spec of your table (use MySQL's
> "show create table" command to get this)

CREATE TABLE `community` (
`id` int(11) NOT NULL auto_increment,
`countryId` int(11) default NULL,
`regionId` int(11) default NULL,
`communityNumber` varchar(20) default NULL,
`name` text,
`alternateNames` text,
`town` text,
`diocese` text,
`region` text,
`medievalLocation` text,
`modernLocation` text,
`corporateStatus` text,
`dedication` text,
`dateFounded` text,
`dateTerminated` text,
`foundationInformation` text,
`firstMembers` text,
`notableHeads` text,
`notableMembersResidentsGuests` text,
`populationCounts` text,
`religiousOrder` text,
`rule` text,
`congregation` text,
`papalExemptions` text,
`incorporatedCommunities` text,
`incorporatedBy` text,
`dependentCommunities` text,
`dependencyOf` text,
`otherEcclesiasticalRelations` text,
`visitations` text,
`benefactors` text,
`patronsBenefactors` text,
`secularPoliticalAffiliations` text,
`socialCharacteristics` text,
`relativeWealth` text,
`assetsProperty` text,
`income` text,
`charitableWork` text,
`otherEconomicActivities` text,
`litigations` text,
`literaryWorks` text,
`earlyDocuments` text,
`artArtifacts` text,
`architectureArchaeology` text,
`presentStateOfMedievalStructure` text,
`relics` text,
`manuscriptSources` text,
`publishedPrimarySources` text,
`secondarySources` text,
`miscellaneousInformation` text,
`contributors` text,
`contributorsNotes` text,
`lastModified` varchar(80) default NULL,
`manuscriptsProduced` text,
`clientsTenantsOther` text,
`conversiServants` text,
`lastModifiedBy` text,
`adminNotes` text,
`nameIntial` varchar(10) default NULL,
`dedicationInitial` varchar(10) default NULL,
`dateStart` varchar(80) default NULL,
`dateFinish` varchar(80) default NULL,
`length` int(11) default NULL,
`hide` varchar(10) default NULL,
`created` varchar(40) default NULL,
PRIMARY KEY (`id`),
KEY `countryId` (`countryId`),
KEY `name` (`name`(10)),
KEY `dateStart` (`dateStart`(10)),
KEY `dateFinish` (`dateFinish`(10)),
KEY `dedication` (`dedication`(3)),
KEY `regionId` (`regionId`),
KEY `religiousOrder` (`religiousOrder`(10))
) TYPE=InnoDB;

> as well as the Java code that
> you're using to insert the data.

That has become irrelevant since I can make MySQL fail
using a script with the above UPDATE statement that touches
just one column.

I hope that helps.
-Paul

Reply to this Message

Mark Matthews - 26 May 2005 14:03 GMT

>>>UPDATE `community` SET
>>> `publishedPrimarySources` =
[quoted text clipped - 93 lines]
> KEY `religiousOrder` (`religiousOrder`(10))
> ) TYPE=InnoDB;

[snip]

Paul,

What you're running into is "MySQL error code 139: Too big row". On
inspection it appears you have an _awful_lot_ of TEXT columns, do they
really need to be TEXT (i.e. up to 65K characters) instead of VARCHAR
(up to 255 characters)? There are restrictions on how large a single row
can be, and if you include that many TEXT columns, you can easily
overflow it.

It also (on inspection again, since we don't know what problem you're
trying to solve, and can't see the rest of your datamodel) that perhaps
you need some normalization?

-Mark

Signature

Mark Matthews
MySQL AB, Software Development Manager - Connectivity
www.mysql.com

Reply to this Message

P.Hill - 26 May 2005 17:57 GMT

> [snip]

Table with a gazillion TEXT columns deleted.

> What you're running into is "MySQL error code 139: Too big row". On
> inspection it appears you have an _awful_lot_ of TEXT columns,

Yes, I do. I agree, the right solution is some type of structured
document, maybe XML in ONE (or a few) large TEXT fields. That would be
optimal, but that is not my current situation.

> do they
> really need to be TEXT (i.e. up to 65K characters) instead of VARCHAR
> (up to 255 characters)?

The users are academics; they like to write. 255 is just a bit small
for any one field. If I had something in between 255 and 65K I'd
use that, but the design grew from a few text fields.

> It also (on inspection again, since we don't know what problem you're
> trying to solve, and can't see the rest of your datamodel) that perhaps
> you need some normalization?

See comment above.

Apparently this many didn't give the MyISAM table a problem.
What I will do is go through all the fields
and see if I can as many as possible back to VARCHAR(). I'll even
verify the current data (all rows, not just this one) will fit in
whatever limiting I do. I see a few right off that I'm sure are NOT big
long winded narrative fields. Because this table does have so many TEXT
fields, I think a many were promoted to TEXT really without
cause.

Thanks for the clue about what was causing the max'ing out.

-Paul

Reply to this Message

David Harper - 26 May 2005 20:54 GMT

[snip]

>> It also (on inspection again, since we don't know what problem you're
>> trying to solve, and can't see the rest of your datamodel) that perhaps
>> you need some normalization?
>
> See comment above.

What Mark is suggesting is that instead of one table with dozen of text
columns, you make a separate table for each text field, indexed by the
primary key of the main table, hence your main table might look
something like this, with most of the text fields removed:

CREATE TABLE `community` (
`id` int(11) NOT NULL auto_increment,
`countryId` int(11) default NULL,
`regionId` int(11) default NULL,
`communityNumber` varchar(20) default NULL,
`name` varchar(20),
`dateStart` date default NULL,
`dateFinish` date default NULL,
`length` int(11) default NULL,
`hide` varchar(10) default NULL,
`created` date default NULL,
PRIMARY KEY (`id`),
KEY `countryId` (`countryId`),
KEY `name` (`name`)
) type=InnoDB;

whilst each text field would have its own table, e.g.:

create table `foundation_info` (
`id` int(11) NOT NULL,
`foundationInformation` text,
PRIMARY KEY (`id`)
) type=InnoDB;

or you could group related text fields into a single table, but not too
many in one table, otherwise you hit the dreaded error 139 again :-)

David Harper
Cambridge, England

Reply to this Message

P.Hill - 26 May 2005 22:49 GMT

> CREATE TABLE `community` (

[...]

> create table `foundation_info` (
> `id` int(11) NOT NULL,
> `foundationInformation` text,
> PRIMARY KEY (`id`)
> ) type=InnoDB;

Actually, if I had a choice I'd probably put all of the big
text fields in one child table, because there is very little
that deals with all the big text fields as individual fields.

CREATE TABLE `community_texts` (
`id` int(11) NOT NULL,
communityId int(11) NOT NULLREFERENCES ,
text_type int(11) NOT NULL,
`body` text,
PRIMARY KEY (`id`),
FOREIGN KEY commId (communityId) REFERENCES community.id
ON DELETE CASCADE
) type=InnoDB;

-Paul

Reply to this Message

David Harper - 27 May 2005 06:19 GMT

[snip]

> Actually, if I had a choice I'd probably put all of the big
> text fields in one child table, because there is very little
> that deals with all the big text fields as individual fields.

Yes, that would be an elegant solution.

David Harper
Cambridge, England

Reply to this Message

MYSQL Too much for a InnoDB Indexes?

Free Magazines