Hi Erik,
Four additional comments to the feedback from Christopher Simon:
* So long as this doesn't break the oxarticles__<fieldname>
functionality, the "Proposed Solution" sounds acceptable and should protect
most of the 3rd party modules & tools, with the exception of those that perform
direct database access (e.g. xtc2oxid)
* Have you counted how many fields (columns) will still be resident in
oxarticles ? Perhaps some other fields could be moved elsewhere or generalised ?
* As per the comment from Christopher Simon, do you really need a table
per language ?
* Since there will be fewer oxarticles columnsthan today and you will
be moving data between tables, this makes for a more complicated and risky
MySQL upgrade process than normal
________________________________
From: Erik Kort <[email protected]>
To: [email protected]
Sent: Thu, June 17, 2010 4:56:07 PM
Subject: Re: [oxid-dev-general] Problem with Translation DB fields
Hi,
Yes, you are right, this is an internal limitation of innodb.
We have investigated it and we can confirm this is the case. InnoDB internally
limits the combined row size INCLUDING all VARxxx data types to 65535 bytes..
It's this limit we are hitting, especially if the shop is an utf8 shop, because
utf8 needs max 3 bytes per character. BTW it's actually not limited by the max
rowsize mysql internally has (8000 bytes) as for this the variable length data
types are not part of this 8000 bytes. Variable length data types are stored
somewhere else.
Following bugs have been opened for this:
https://bugs.oxid-esales.com/view.php?id=1915
https://bugs.oxid-esales.com/view.php?id=1705.
Workarounds:
1. Use/convert language columns to TEXT fields instead of VARCHAR.
Disadvantages: Selects with ORDER BY or GROUP BY will force disk based
temporary tables. Thus reducing your shop performance by making the database
earlier disk bound then before.
2. Reduce size of your existing language columns to maximum of your used
data. Disadvantages: Newer data may not fit.
Proposed Solution (we prefer):
Taking out the language columns from oxarticle table into separate multiple
tables. This is basically sharding the tables. In effect making unlimited
number of languages possible. Shop will manage the multiple tables and select
correct table based on language identifier. First possible version we can
implement this is version 4.5.
Any comments or other input about this ?
Regards, Erik
More info on mysql / innodb limitation:
-----
See http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
The maximum row length, except for variable-length columns (VARBINARY, VARCHAR,
BLOB and TEXT), 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 BLOB and TEXT columns, must
be less than 4GB.
If a row is less than half a page long, all of it is stored locally within the
page. If it exceeds half a page, variable-length columns are chosen for
external off-page storage until the row fits within half a page, as described
in Section 13.2.11.2, "File Space Management".
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:
Regards, Erik
-----Ursprüngliche Nachricht-----
Von: [email protected]
[mailto:[email protected]] Im Auftrag von Christopher
Simon
Gesendet: Montag, 14. Juni 2010 15:56
An: [email protected]
Betreff: [oxid-dev-general] Problem with Translation DB fields
Hi there,
i'm kinda stuck on a problem with an EE here. It's a shop which has
about 14 languages, therefore it has all translateable fields 14 times.
in oxarticles, thats a total of 206 (sic!) columns.
This blows up the row limit of 8000 bytes of innodb, if you want to add
another row to this table and you get "got error 139 from storage
engine". Some other developer seems to have already converted the
variant fields to text fields, instead of varchars. if you convert this
fields back to varchar, you get this:
#1118 - 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
Because of this, you aren't able to add one more column to this table,
which makes it incredibly hard to maintain this shop and do some updates
or install modules. i surely will find a solution for this, but it's an
unecessary "stone" in my way to update this shop.
I think the denormalized database layout should be seriously revamped at
least for multilanguage fields, and the step has to be to a more
normalized table layout for this purpose.
Any ideas for a new language management? Maybe we can figure out some
solution for oxid in general, together.
_______________________________________________
dev-general mailing list
[email protected]
http://dir.gmane.org/gmane.comp.php.oxid.general
_______________________________________________
dev-general mailing list
[email protected]
http://dir.gmane.org/gmane.comp.php.oxid.general
_______________________________________________
dev-general mailing list
[email protected]
http://dir.gmane.org/gmane.comp.php.oxid.general
