CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Profiles (Moderator: Dave Greenberg) »
  • Custom Data Group hits MySQL TEXT column limits
Pages: [1]

Author Topic: Custom Data Group hits MySQL TEXT column limits  (Read 1859 times)

tmcnulty1982

  • Guest
Custom Data Group hits MySQL TEXT column limits
February 17, 2009, 08:15:30 am
We have a client who created a large custom data group with about 20 TEXT fields.  This breaks with the following MySQL error if you try to insert a large amount of text in each column:

Code: [Select]
Database Error Code: Got error 139 from storage engine, 1030
I traced this error to the following InnoDB restriction:

"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 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." (from http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html)

So, you can't have more than roughly 10 TEXT columns with 768 or more bytes each in a single table in InnoDB.

Can CiviCRM catch this and prevent the user from exceeding these limits?  I can split the Custom Data Group into separate smaller groups, but it would have been a lot easier to build the system the right way from the beginning; I'd hate to see someone else hit with this issue.

In another vein, why does CiviCRM need to build a table for each custom data group in the first place?  You could simply create a single table for each column type with an id, a foreign key to the custom data group, and a column for the actual value.  All the ALTER TABLEs that the current setup requires sounds a lot more complicated.  Is there a reason CiviCRM does what it does?

Thanks.
Toby

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Custom Data Group hits MySQL TEXT column limits
February 17, 2009, 10:52:05 am

Yes, code to check and test of this limit would be a good addition. Wanna submit a patch?

CiviCRM 1.x had a skinny table implementation of custom groups. It just does not scale in our experience and hence the new structure.

this is similar to drupal's transition from flexinode to CCK.

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Profiles (Moderator: Dave Greenberg) »
  • Custom Data Group hits MySQL TEXT column limits

This forum was archived on 2017-11-26.