Hi

Some sql queries are refused in our production server, for example :

update institution SET Gal_Nom = REPLACE(Gal_Nom ,'É','É')

Server version :Server version: 5.0.24-Max-log

the colunm is a varchar(250), the table use INNODB

error message:

#1030 - Got error 139 from storage engine

Please somebody can ? The engine refuses 45 queries at this stage...

Best Regards

Stéphane Lambert

On Wed, 19 Dec 2007 11:27:32 +0100, slambert
<slambertNOSPAMPLEASE@vediovis.net> wrote:

> Hi
>
> Some sql queries are refused in our production server, for example :
>
> update institution SET Gal_Nom = REPLACE(Gal_Nom ,'É','&Eacute;')
>
> Server version :Server version: 5.0.24-Max-log
>
> the colunm is a varchar(250), the table use INNODB
>
> error message:
>
> #1030 - Got error 139 from storage engine
>
> Please somebody can ? The engine refuses 45 queries at this stage...

Google whispered to me ('mysql error 1030 39'):
http://bugs.mysql.com/bug.php?id=3442 (<= het is geen bug overigens..)

error 139 means "too big row"
InnoDB manual specifies that max. row length is slightly more than 8000
bytes.
Though blobs are excluded, still first 512 bytes of each blob are not.

Or another usefull link:
http://dev.mysql.com/doc/mysql/en/in...trictions.html

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

Although InnoDB supports row sizes larger than 65535 internally, you
cannot define a row containing VARBINARY or VARCHAR columns with a
combined size larger than 65535:
mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
-> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
-> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs

... so the rowlength seems to get to big.

Then again, I can't think of a logical reason why you'd want to change the
real data into a representational format.
--
Rik Wasmus

> error 139 means "too big row"
> .. so the rowlength seems to get to big.

It works fine in local and in the development server. We have crash only in
the production server, with exactly the same datas.

Weird....

> Then again, I can't think of a logical reason why you'd want to change the
> real data into a representational format.
Because everything is stored in representational datas in the database,
except an added part with old datas. Some buffering functionalities in the
software make comparisons, and sometime values are evaluated as different
when they are not just because of the html special chars.

I will try to find another solution, but this, one more time, really shows
how it's bad to do not have same environments for development and
production.

Thanks for replying,

Cheers

Stef

On Wed, 19 Dec 2007 13:31:49 -0000, slambert wrote:
>> error 139 means "too big row"
>> .. so the rowlength seems to get to big.
>
> It works fine in local and in the development server. We have crash only in
> the production server, with exactly the same datas.
>
> Weird....

So tell us what is different between the servers. "Nothing" is clearly
the wrong answer.

--
Remember, a 12'x12'x18" raised floor can hold over a thousand gallons of
blood before it starts to seep up through the cracks.
-- Roger Burton West in the Monastery

>> It works fine in local and in the development server. We have crash only
>> in
>> the production server, with exactly the same datas.
>>
>> Weird....
>
> So tell us what is different between the servers. "Nothing" is clearly
> the wrong answer.

development server :
MySQL - 5.0.21-log
phpMyAdmin - 2.8.0.4

production server:
Server version: 5.0.24-Max-log
phpMyAdmin - 2.11.2.2

The 2 of them are under a Linux, but I can't said wich one.

More, in local in my windows machine, the same queries works with the same
database and the same datas:
Server version: 5.0.41-community-nt
phpMyAdmin - 2.11.2.1

I konw that's bad to do not work with the same environment, but I asked for
a mysql5 and that's what I had in development and in production. I
discovered the production one a couple of weeks ago.

another example of failing query:
update institution SET Gal_Nom = REPLACE(Gal_Nom ,'é','&eacute;')

See++

Stef

On Wed, 19 Dec 2007 16:26:30 -0000, slambert wrote:
>>> It works fine in local and in the development server. We have crash only
>>> in
>>> the production server, with exactly the same datas.
>>>
>>> Weird....
>>
>> So tell us what is different between the servers. "Nothing" is clearly
>> the wrong answer.
>
> development server :
> MySQL - 5.0.21-log
> phpMyAdmin - 2.8.0.4
>
> production server:
> Server version: 5.0.24-Max-log
> phpMyAdmin - 2.11.2.2
>
> The 2 of them are under a Linux, but I can't said wich one.
>
> More, in local in my windows machine, the same queries works with the same
> database and the same datas:
> Server version: 5.0.41-community-nt
> phpMyAdmin - 2.11.2.1

So the dev server and the production server are approximately the same,
plus or minus max extensions.

> I konw that's bad to do not work with the same environment, but I asked for
> a mysql5 and that's what I had in development and in production. I
> discovered the production one a couple of weeks ago.
>
> another example of failing query:
> update institution SET Gal_Nom = REPLACE(Gal_Nom ,'é','&eacute;')
>
> See++

Okay, next I think we should look at is SHOW CREATE TABLE institution;
output. I am thinking that the row size problem that was talked about
earlier is fairly likely, but what the table looks like will prove or
disprove this.

--
A: Maybe because some people are too annoyed by top-posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

== Quote from Peter H. Coffin (hellsop@ninehells.com)'s article
> On Wed, 19 Dec 2007 16:26:30 -0000, slambert wrote:
> >>> It works fine in local and in the development server. We have crash only
> >>> in
> >>> the production server, with exactly the same datas.
> >>>
> >>> Weird....
> >>
> >> So tell us what is different between the servers. "Nothing" is clearly
> >> the wrong answer.
> >
> > development server :
> > MySQL - 5.0.21-log
> > phpMyAdmin - 2.8.0.4
> >
> > production server:
> > Server version: 5.0.24-Max-log
> > phpMyAdmin - 2.11.2.2
> >
> > The 2 of them are under a Linux, but I can't said wich one.
> >
> > More, in local in my windows machine, the same queries works with the same
> > database and the same datas:
> > Server version: 5.0.41-community-nt
> > phpMyAdmin - 2.11.2.1
> So the dev server and the production server are approximately the same,
> plus or minus max extensions.
> > I konw that's bad to do not work with the same environment, but I asked for
> > a mysql5 and that's what I had in development and in production. I
> > discovered the production one a couple of weeks ago.
> >
> > another example of failing query:
> > update institution SET Gal_Nom = REPLACE(Gal_Nom ,'é','&eacute;')
> >
> > See++
> Okay, next I think we should look at is SHOW CREATE TABLE institution;
> output. I am thinking that the row size problem that was talked about
> earlier is fairly likely, but what the table looks like will prove or
> disprove this.

sorry to bug in your conversation but error 139 indicates too big of a row. don't
know if this s.
--
POST BY: lark with PHP News Reader ;o)

On Wed, 19 Dec 2007 19:45:43 GMT, lark wrote:
>== Quote from Peter H. Coffin (hellsop@ninehells.com)'s article
>> Okay, next I think we should look at is SHOW CREATE TABLE institution;
>> output. I am thinking that the row size problem that was talked about
>> earlier is fairly likely, but what the table looks like will prove or
>> disprove this.
>
> sorry to bug in your conversation but error 139 indicates too big of a row. don't
> know if this s.

Yeah, that's kind of where we're going with this. We're going to tot up
the number of columns in this table and see what engine it is, and see
how likely that updating 2 bytes of UTF-8 to 7 would be to push a row
over the limit...

--
95. My dungeon will have its own qualified medical staff complete with
bodyguards. That way if a prisoner becomes sick and his cellmate tells the
guard it's an emergency, the guard will fetch a trauma team instead of
opening up the cell for a look. --Peter Anspach's Evil Overlord List

>> sorry to bug in your conversation but error 139 indicates too big of a
>> row. don't
>> know if this s.
>
> Yeah, that's kind of where we're going with this. We're going to tot up
> the number of columns in this table and see what engine it is, and see
> how likely that updating 2 bytes of UTF-8 to 7 would be to push a row
> over the limit...

Ok guys

Here we go

That's an innodb table, with 88 columns. There are more than 20 000 rows
inside.

Do you think this can ?......


CREATE TABLE `institution` (
`id` int(11) NOT NULL auto_increment,
`imgInstitution` varchar(250) default NULL,
`imgLegendInstitution` varchar(250) default NULL,
`id_commune` int(11) default NULL,
`id_pays` int(11) NOT NULL default '1',
`Gal_Nom` varchar(250) default NULL,
`Gal_NomCourt` varchar(250) default NULL,
`Gal_Adresse` varchar(250) default NULL,
`Gal_Adresse1` varchar(250) default NULL,
`Gal_Adresse2` varchar(250) default NULL,
`Gal_Adresse3` varchar(250) default NULL,
`Gal_Directeur` varchar(250) default NULL,
`Gal_Responsable` varchar(250) default NULL,
`Gal_Site` varchar(250) default NULL,
`Gal_Date_Crea` varchar(250) default NULL,
`Gal_Tel` varchar(250) default NULL,
`Gal_Fax` varchar(250) default NULL,
`Gal_Mel` varchar(250) default NULL,
`Projet_Artistique` text,
`Formation_Formation` varchar(250) default NULL,
`Formation_Diplomes` varchar(250) default NULL,
`Formation_coContinue` varchar(4) default NULL,
`Formation_Continue` varchar(250) default NULL,
`Formation_coDivers` varchar(4) default NULL,
`Formation_Divers` text,
`General_GoInstitutionnel` int(2) default NULL,
`General_coInstitutionnel` int(2) default NULL,
`CP` varchar(250) default NULL,
`Cedex` varchar(250) default NULL,
`Actions_culturelles` text,
`Activites_programmees` varchar(250) default NULL,
`Production` text,
`Partenaires` text,
`Informations_pratiques` text,
`Services` varchar(250) default NULL,
`id_theme` text,
`id_discipline` text,
`id_diplome` text,
`id_structure` text,
`Calendrier` enum('o','n') NOT NULL default 'n',
`ecole` enum('o','n') NOT NULL default 'n',
`NumTri` int(11) NOT NULL default '0',
`login` varchar(20) NOT NULL,
`pwd` varchar(20) NOT NULL,
`mail_admin` varchar(250) NOT NULL,
`notes_cnap` text,
`artistes_presentes` text NOT NULL,
`ie` tinyint(1) NOT NULL default '0',
`nom_court` varchar(50) NOT NULL,
`residence` enum('o','n') NOT NULL default 'n',
`inf_inst_cnap` tinyint(1) NOT NULL default '0',
`inf_inst_dap` tinyint(1) NOT NULL default '0',
`inf_inst_drac` tinyint(1) NOT NULL default '0',
`inf_inst_ecosupart` tinyint(1) NOT NULL default '0',
`inf_inst_frac` tinyint(1) NOT NULL default '0',
`inf_inst_cac` tinyint(1) NOT NULL default '0',
`inf_inst_manufacture` tinyint(1) NOT NULL default '0',
`exportable` int(1) NOT NULL default '0',
`visible` int(1) NOT NULL default '1',
`lieu_presentation` int(1) NOT NULL default '1',
`lieu_nom` text NOT NULL,
`lieu_adresse` text NOT NULL,
`lieu_statut` text NOT NULL,
`lieu_publications` text NOT NULL,
`lieu_prestations` text NOT NULL,
`lieu_reseaux_assoc` text NOT NULL,
`lieu_partenaires` text NOT NULL,
`ecole_nom` varchar(250) NOT NULL,
`ecole_datecreation` varchar(10) NOT NULL default '',
`ecole_statut` varchar(250) NOT NULL,
`ecole_autresdiplomes` text NOT NULL,
`ecole_nbetudiants` text NOT NULL,
`ecole_presentation` text NOT NULL,
`ecole_prestations` text NOT NULL,
`ecole_admission` text NOT NULL,
`ecole_formations` text NOT NULL,
`ecole_actionscult` text NOT NULL,
`ecole_reseaux_assoc` varchar(250) NOT NULL,
`ecole_parten_ecoleart` text NOT NULL,
`ecole_parten_etablcult` text NOT NULL,
`ecole_partenariats` text NOT NULL,
`ecole_mecenat` text NOT NULL,
`date_insertion` date NOT NULL default '0000-00-00',
`idAdminInsertion` int(11) default NULL,
`date_modif` datetime default NULL,
`idAdminModif` int(11) default NULL,
`indexation` text NOT NULL,
`status` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `inf_inst_cnap` (`inf_inst_cnap`),
KEY `inf_inst_dap` (`inf_inst_dap`),
KEY `inf_inst_drac` (`inf_inst_drac`),
KEY `inf_inst_ecosupart` (`inf_inst_ecosupart`),
KEY `inf_inst_frac` (`inf_inst_frac`),
KEY `inf_inst_cac` (`inf_inst_cac`),
KEY `inf_inst_manufacture` (`inf_inst_manufacture`),
KEY `visible` (`visible`),
KEY `id_commune` (`id_commune`),
KEY `id_pays` (`id_pays`),
KEY `idAdminInsertion` (`idAdminInsertion`),
KEY `idAdminModif` (`idAdminModif`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4127 ;

On Fri, 21 Dec 2007 13:20:08 -0000, slambert wrote:
>>> sorry to bug in your conversation but error 139 indicates too big of a
>>> row. don't
>>> know if this s.
>>
>> Yeah, that's kind of where we're going with this. We're going to tot up
>> the number of columns in this table and see what engine it is, and see
>> how likely that updating 2 bytes of UTF-8 to 7 would be to push a row
>> over the limit...
>
> Ok guys
>
> Here we go
>
> That's an innodb table, with 88 columns. There are more than 20 000 rows
> inside.
>
> Do you think this can ?......
>
>
> CREATE TABLE `institution` (
> `id` int(11) NOT NULL auto_increment,
> `imgInstitution` varchar(250) default NULL,
> `imgLegendInstitution` varchar(250) default NULL,
> `id_commune` int(11) default NULL,
> `id_pays` int(11) NOT NULL default '1',
> `Gal_Nom` varchar(250) default NULL,
> `Gal_NomCourt` varchar(250) default NULL,
> `Gal_Adresse` varchar(250) default NULL,
> `Gal_Adresse1` varchar(250) default NULL,
> `Gal_Adresse2` varchar(250) default NULL,
> `Gal_Adresse3` varchar(250) default NULL,
> `Gal_Directeur` varchar(250) default NULL,
> `Gal_Responsable` varchar(250) default NULL,
> `Gal_Site` varchar(250) default NULL,
> `Gal_Date_Crea` varchar(250) default NULL,
> `Gal_Tel` varchar(250) default NULL,
> `Gal_Fax` varchar(250) default NULL,
> `Gal_Mel` varchar(250) default NULL,

Okay, up to this point, we're already at a potential storage of nearly
3800 characters.

> `Projet_Artistique` text,
> `Formation_Formation` varchar(250) default NULL,
> `Formation_Diplomes` varchar(250) default NULL,
> `Formation_coContinue` varchar(4) default NULL,
> `Formation_Continue` varchar(250) default NULL,
> `Formation_coDivers` varchar(4) default NULL,
> `Formation_Divers` text,
> `General_GoInstitutionnel` int(2) default NULL,
> `General_coInstitutionnel` int(2) default NULL,
> `CP` varchar(250) default NULL,
> `Cedex` varchar(250) default NULL,
> `Actions_culturelles` text,
> `Activites_programmees` varchar(250) default NULL,
> `Production` text,
> `Partenaires` text,
> `Informations_pratiques` text,
> `Services` varchar(250) default NULL,
> `id_theme` text,
> `id_discipline` text,
> `id_diplome` text,
> `id_structure` text,
> `Calendrier` enum('o','n') NOT NULL default 'n',
> `ecole` enum('o','n') NOT NULL default 'n',
> `NumTri` int(11) NOT NULL default '0',
> `login` varchar(20) NOT NULL,
> `pwd` varchar(20) NOT NULL,
> `mail_admin` varchar(250) NOT NULL,
> `notes_cnap` text,
> `artistes_presentes` text NOT NULL,
> `ie` tinyint(1) NOT NULL default '0',
> `nom_court` varchar(50) NOT NULL,
> `residence` enum('o','n') NOT NULL default 'n',
> `inf_inst_cnap` tinyint(1) NOT NULL default '0',
> `inf_inst_dap` tinyint(1) NOT NULL default '0',
> `inf_inst_drac` tinyint(1) NOT NULL default '0',
> `inf_inst_ecosupart` tinyint(1) NOT NULL default '0',
> `inf_inst_frac` tinyint(1) NOT NULL default '0',
> `inf_inst_cac` tinyint(1) NOT NULL default '0',
> `inf_inst_manufacture` tinyint(1) NOT NULL default '0',
> `exportable` int(1) NOT NULL default '0',
> `visible` int(1) NOT NULL default '1',
> `lieu_presentation` int(1) NOT NULL default '1',
> `lieu_nom` text NOT NULL,
> `lieu_adresse` text NOT NULL,
> `lieu_statut` text NOT NULL,
> `lieu_publications` text NOT NULL,
> `lieu_prestations` text NOT NULL,
> `lieu_reseaux_assoc` text NOT NULL,
> `lieu_partenaires` text NOT NULL,
> `ecole_nom` varchar(250) NOT NULL,
> `ecole_datecreation` varchar(10) NOT NULL default '',
> `ecole_statut` varchar(250) NOT NULL,
> `ecole_autresdiplomes` text NOT NULL,
> `ecole_nbetudiants` text NOT NULL,
> `ecole_presentation` text NOT NULL,
> `ecole_prestations` text NOT NULL,
> `ecole_admission` text NOT NULL,
> `ecole_formations` text NOT NULL,
> `ecole_actionscult` text NOT NULL,
> `ecole_reseaux_assoc` varchar(250) NOT NULL,
> `ecole_parten_ecoleart` text NOT NULL,
> `ecole_parten_etablcult` text NOT NULL,
> `ecole_partenariats` text NOT NULL,
> `ecole_mecenat` text NOT NULL,
> `date_insertion` date NOT NULL default '0000-00-00',
> `idAdminInsertion` int(11) default NULL,
> `date_modif` datetime default NULL,
> `idAdminModif` int(11) default NULL,
> `indexation` text NOT NULL,
> `status` tinyint(4) NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `inf_inst_cnap` (`inf_inst_cnap`),
> KEY `inf_inst_dap` (`inf_inst_dap`),
> KEY `inf_inst_drac` (`inf_inst_drac`),
> KEY `inf_inst_ecosupart` (`inf_inst_ecosupart`),
> KEY `inf_inst_frac` (`inf_inst_frac`),
> KEY `inf_inst_cac` (`inf_inst_cac`),
> KEY `inf_inst_manufacture` (`inf_inst_manufacture`),
> KEY `visible` (`visible`),
> KEY `id_commune` (`id_commune`),
> KEY `id_pays` (`id_pays`),
> KEY `idAdminInsertion` (`idAdminInsertion`),
> KEY `idAdminModif` (`idAdminModif`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4127 ;

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
"&Eacute;" you've pushed a row with 7993 or more characters of storage
already to 8001 or more.

--
51. If one of my dungeon guards begins expressing concern over the conditions
in the beautiful princess' cell, I will immediately transfer him to a
less people-oriented position.
--Peter Anspach's list of things to do as an Evil Overlord

> 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
> "&Eacute;" 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
>> "&Eacute;" 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