Bug ? #1030 - Got error 139 from storage engine

> 80 columns, of which over 50 are essentially freeform text, and over
> half of those are fields of type text, which implies that you expect
> them to contain large amounts of data and that do not trim trailing
> blanks. That's a lot of columns and a lot of text columns, and I can
> very easily imagine this filling the 8000 character row limit that will
> trigger the error you see. So, with the substutution of the "É" to
> "É" you've pushed a row with 7993 or more characters of storage
> already to 8001 or more.

Yes, but I never had this error before, and I don't have it elsewhere.

By the way, this is a serious engine limitations we never had in the past...

Anyway, now we understood why, any idea to resolve it ? I mean, without
breaking all the 7 years old existing project, of course.... :)

Thanks for replying

See++

Stef

On Fri, 21 Dec 2007 18:45:43 -0000, slambert wrote:
>> 80 columns, of which over 50 are essentially freeform text, and over
>> half of those are fields of type text, which implies that you expect
>> them to contain large amounts of data and that do not trim trailing
>> blanks. That's a lot of columns and a lot of text columns, and I can
>> very easily imagine this filling the 8000 character row limit that will
>> trigger the error you see. So, with the substutution of the "É" to
>> "É" you've pushed a row with 7993 or more characters of storage
>> already to 8001 or more.
>
> Yes, but I never had this error before, and I don't have it elsewhere.
>
> By the way, this is a serious engine limitations we never had in the past...

You've laways HAD this limitation, you've just never HIT it.

> Anyway, now we understood why, any idea to resolve it ? I mean, without
> breaking all the 7 years old existing project, of course.... :)

Ideally, you look at your giant table and start figuring out what
doesn't need to be in there, and put thost columns in other tables. If
it's not possible to do that, seriously consider capping the total
amount of data storable into those columns at amounts that don't exceed
8000 characters. I don't recall that table having any constraints
listed, so you MAY be able to just change the engine from InnoDB to
another engine that doesn't have the row length limit. Most applications
don't care what the DB engine is.

--
We're the technical experts. We were hired so that management could
ignore our recommendations and tell us how to do our jobs.
-- Mike Andrews

> Ideally, you look at your giant table and start figuring out what
> doesn't need to be in there, and put thost columns in other tables.

I can't, this should mean too much updates in the application.

Furthermore, the conception will be affected : these are really properties
from the entity, so they must be columns of the tables...

> listed, so you MAY be able to just change the engine from InnoDB to
> another engine that doesn't have the row length limit. Most applications
> don't care what the DB engine is.


I just need the relational functionalities and the foreign keys. Which
engine do you suggest ?

Stef

On Mon, 24 Dec 2007 03:53:14 -0000, "slambert"
<slambertNOSPAMPLEASE@vediovis.net> wrote:

>> Ideally, you look at your giant table and start figuring out what
>> doesn't need to be in there, and put thost columns in other tables.
>
>I can't, this should mean too much updates in the application.

You're stuck then. The database design is far from
optimal, I see a lot of columns that indicate they contain
a list, like Formation_Diplomes, Actions_culturelles,
Activites_programmees, Partenaires,
Informations_pratiques, Services and several more.
It really should be normalized.

>Furthermore, the conception will be affected : these are really properties
>from the entity, so they must be columns of the tables...
>
>> listed, so you MAY be able to just change the engine from InnoDB to
>> another engine that doesn't have the row length limit. Most applications
>> don't care what the DB engine is.
>
>I just need the relational functionalities and the foreign keys. Which
>engine do you suggest ?

I don't see any foreign keys in your table, and relational
clauses like JOIN work in any engine.

There are quite a few VARCHAR(250) columns that probably
could be made smaller. Perhaps some of the TEXT columns
can be replaced by shorter types.
But the only real solution is normalization.

>Stef

Good luck
--
( Kees
)
c[_] Work like you don't need the money,
Love like you've never been hurt,
Dance like nobody's watching. (#425)

>>I can't, this should mean too much updates in the application.
>
> You're stuck then. The database design is far from
> optimal, I see a lot of columns that indicate they contain
> a list, like Formation_Diplomes, Actions_culturelles,
> Activites_programmees, Partenaires,
> Informations_pratiques, Services and several more.
> It really should be normalized.

Damned. I'm trapped, they saw it :)

When you take over a 6 years old project, sometime, you have architectures
troubles coming from the past, and you have to manage with them. My job was
to make this version evoluate, of course for yesterday, and I never really
had possibility to go further with the worst thing for a DB ever :
concatened foreign keys.... By the way, we managed with it, and the
softwares makes what it is supposed to do. We will see later when emergency
will be finished (so I mean maybe in another life :)). But I'm sure that's
not the most important reason to make fail a REPLACE query.....



>>I just need the relational functionalities and the foreign keys. Which
>>engine do you suggest ?
> I don't see any foreign keys in your table, and relational
> clauses like JOIN work in any engine.

there are plenty : id_commune , id_pays, idAdminInsertion, idAdminModif,
........

And I have maybe 6 or 7 tables with foreign keys pointing to this table. As
some paret are very dirty, I want to force the integrity constraint from the
DB. Si I need the InnoDb engine.

By the way, if we have to make more things with this software, I will ask
for an Oracle DB : that's not possible to be stuck on production juste
because a REPLACE query failed on a server and not in another. That's just
not serious.

> But the only real solution is normalization.

yeah, in a perfect world, but I don't have.

Merry chrtismas all, I have to escape from there now.

see ya

Stef