Bug ? #1030 - Got error 139 from storage engine

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 help? 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 help? 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 helps.
--
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 helps.

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 helps.
>
> 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 help ?......


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 helps.
>>
>> 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 help ?......
>
>
> 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