Database Limit: Limited to a maximum length of 65,555 char

[zacharylai]

Hi. Anyone here tried adding all the extension and reach a database limit? Well here the story. I am testing typolight and its extension and i notice if you keep adding new extension ( i think i reach less than halfway ) you will hit a limit with mysql table. This has to do with varchar maximum length of 65,555 characters. Somehow a lot of the extension keep adding a new column to the table and eventually it will hit the maximum.

is there a solution around this problem ? I know not everyone need to add all the extension when building a website but; typolight seems like a robust cms, with what i think is a fantastic easy to use extension management. So its very silly to suddenly see this problem coming up, making typolight short of extensive.

Hope anyone can correct me at least.

[acenes]

What was the actual error message? Maybe it could be cured by a column redefinition.

[zacharylai]

Ah hey,

here the error

ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci' thrown in \\typolight\system\libraries\Database.php on line 599

[27-Mar-2010 10:10:32] PHP Fatal error: Uncaught exception 'Exception' with message 'Query error: 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 (ALTER TABLE `tl_module` ADD `nesiumplayer` varchar(255) NOT NULL default ''' thrown in \typolight\system\libraries\Database.php on line 599

well i know i can manually change each type to BLOB, which would be an arduous work. And i am not sure if changing it to BLOBs will cause any problem. Is there another solution here ?

[acenes]

Yes, the easiest solution seems changing the larger varchar columns to text. Another solution would be to use a different DB software such as Oracle or PostgreSQL, but that involves even more work because automatic DB installation does not work and many extensions SQL statements would need to be modified. However still you would hit a limit some day when more and more extensions get available and you keep installing them all.

But to be honest, I cant imagine a real life site with more than say 10-20 extensions.
What is your intention to install 350+ extensions at same time?

[zacharylai]

lol. Well yeah i know. Not many site will use that many extension and some extension are similar. But i was testing typolight to test each extension when i hit this wall, which is a bit irritating, seeing that the extension catalog & extension manager is so user friendly and powerful, and the system is very well built, but it does have this very small problem.

Ok another related question. I have hit this limit. Now i want to remove some extension. And we can only do that in the extension manager. I go to extension manager... but it give me a prompt to update my database. But i can't update the database because i have reach the limit. And without updating the database, they won't let me go to the list in extension manager where i can remove the extension. How can i go around this ?

[ramjet]

arrghhh catcha 22!
you can ftp in to system/modules and manually remove some extensions (remove the folders), then run typolight/install.php.
That should work :D

[acenes]

Manually deleting some of the no longer needed columns in phpMySql might help, also maybe a "repair" action on the table from within phpMyAdmin

[ramjet]

Well I've just hit this problem myself.
I recovered from it by refreshing the install tool, and dropping fields first, then altering, then adding.

But it begs the question re module development.

I have the core installed of course, plus dlh_googlemaps,isotope (and its associated ajax,conitionalselectmenu and tablelookupwizard),menus,gerichte,Availability Calendars, gd_googledirections,xgoogletranslator, youtubereader, zedseries_lib, zedseries_moduleaccess and zibepla plus 5 of my own I'm working on. So 19 in total to reach the error .

Fatal error: Uncaught exception Exception with message Query error: 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 (ALTER TABLE `tl_module` ADD `pc_streamer` varchar(255) NOT NULL default '' thrown in /x/system/libraries/Database.php on line 642

So the tl_module table becomes overloaded quite quickly really.

I myself use varchar most often, and I assume most others do.
I'll check and do a bit of a count after dinner to see what its holding.

Yes, the easiest solution seems changing the larger varchar columns to text.

Is there a disadvantage at all to using text instead of varchar when developing?

I'm wondering if mysql would throw an error when there were too many text fields.

[ramjet]

Well heres more
http://dev.mysql.com/doc/refman/5.0/...unt-limit.html
In essence

The Maximum Number of Columns Per Table
There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors, listed in the following discussion.
Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. BLOB and TEXT columns are counted at one to four plus eight bytes per column toward the row size limit because the contents of these columns are stored separately from the rest of the row. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.
The maximum row size constrains the number of columns because the total width of all columns cannot exceed this size. For example, utf8 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.
Storage for variable-length columns includes length bytes, which are assessed against the row size. For example, a VARCHAR(255) CHARACTER SET utf8 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.
BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately

3 bytes for UTF8 char(1), 767 bytes for UTF8 varchar(255), as opposed to 12 bytes maximum for blob or text.
My 5 modules alone place 113 new columns into tl_module - 59 are varchar(255) and 49 are char(1) - the varchars account for over 45,000 bytes of the 65,000 limit table limit.

No wonder then!

Is there a negative side to making them all text fields (on the positive that 45000 bytes will become 700 bytes).