Hi,
as you know, we are working on this issue to be fixed in OXID eShop 4.5.0
Beta.
The proposed solution about multiple tables for language fields was selected
as a way to go - with addition of introducing DB views for a more convenient
handling of multiple tables.
Currently the highlights are:
Architecture changes:
* each multilanguage table will be splitted to multiple tables if language
limit is reached. The additional tables will contain only oxid field and
multilanguage fields for specific languages set e.g. (languages from 8th to
15th);
* the extra tables are named after the core table with language set suffix
e.g. oxarticles_set1;
* each language has its own DB view for each DB table, e.g.
oxv_oxarticles_de, oxv_oxarticles_en;
* For enterprise edition the language views are available with shop
include/exclude limiting functionality (combined) e.g. oxv_oxarticles_1_de,
oxv_oxarticles_2_de and without it e.g. oxv_oxarticles_de,
oxv_oxarticles_en;
* to select all multilanguage fields (e.g. when setEnableMultilang(false))
there will also be views, joining all language set tables e.g.
oxv_oxarticles.
These views are basically large joins on oxid, which are slower, but
contain whole object as one dataset - usually used for exporting data, and
not in eShop frontend.
Most important API changes:
* getLangTableName( $sTable, $iLangId ) - returns the table for given
language fields.
* getViewName( $sTable, $iLangId = null, $sShopId = null ) - changed to
support multilanguage tables.
* oxI18n::getViewName() - returns view name by object language.
* oxBase::getSqlFieldName() - replaced by oxI18n::getUpdateSqlFieldName()
as such functionality is only needed when updating multilingual object.
(select fields are handled by DB views).
Best regards,
Sarunas
----- Original Message -----
From: "Erik Kort" <[email protected]>
To: <[email protected]>
Sent: Thursday, June 17, 2010 4:56 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
