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
