Author Topic: Impact of increasing field size in database? (Read 885 times)

vavroom · March 26, 2014, 07:17:17 pm

I'm working on a bilingual, wordpress based, site with CiviCRM. One of the things I *must* have is the ability to generate greetings automatically depending on the gender of the person and their language. e.g. If the person speaks English, they get "Dear". If the person speaks French and is a woman, they get "Chère". If the person is French and is a man, they get "Cher".

I found and manually implemented a patch that solves this: https://github.com/sfe-ev/civicrm-core/commit/73d64eb695adbf17d25e1714226e519b4a6e155b.patch

The only problem is the one discussed on that page. That is, the email/postal greeting field size is limited to 255 characters. To use the conditional greeting, I am in need of more than that.

I am thinking about simply going into the database and increasing the character size limit of both the postal and email greetings. Before doing so, are there any potential issues that I need to be aware of if I do that? Am I likely to make my database explode, or CiviCRM throw a hissy fit?

Thank you

Nic

Chris Burgess · March 26, 2014, 08:10:56 pm

(hi Nick!)

The time you're most likely to run into issues is at upgrade time. Mostly you can get by modifying the underlying DB structure, although it's not a supported use. However CiviCRM's upgrader uses raw SQL to manage the upgrade logic, and expects a stock DB structure. If your upgrader adds indexes on what it thinks is a short text field, or otherwise doesn't anticipate that the field isn't in the expected format, it may fail. The SQL components of the upgrader don't implement a lot of "check the DB element exists before you drop the element" logic and when they hit something unexpected you end up with a failed upgrade and a DB in an inconsistent state.

(NB: That's why you should ALWAYS test CiviCRM upgrades on a copy!)

Given that the patch expands the greeting field to fit a code-like block of template, I'd probably have thought about doing it with a custom token instead - that might be a path which permits you to avoid patching your CiviCRM code and DB structure. That's an alternative approach, and may or may not be feasible, I don't know the details here.

I don't know if sfe-ev's patch made it into main CiviCRM - guessing not if you need to apply it several months later? Was there ever an issue in Jira for it? If you do find you need that patch, consider helping it become part of CiviCRM core - putting the effort into getting that code into CiviCRM core will mean the software expands to include your use case, which benefits others AND means you are no longer "out on a limb".

vavroom · March 26, 2014, 08:30:07 pm

(Hey Chris! Fancy meeting you here

)

That patch has made it to 4.5, except I need this *now*. I haven't found how they are handling the greetings field.

Thank you for feedback regarding upgrade time. Will keep that in mind. I'll investigate the custom token solution. It might do what I need it to.

Thank you.

Chris Burgess · March 26, 2014, 08:32:14 pm

OK, that's fine. You might need to rejigger the upgrader to re-order (or simply skip) the parts which come along with that patch. It might be worse, but probably not a disaster.

We track lots of stuff that's not in 4.4 yet with our deployed sites. It's OK. Maybe not for the faint of heart is all

Eileen · March 26, 2014, 09:00:34 pm

Generally ALTER field commands don't fail on upgrade if they have already happened. ie. you can issue

ALTER fieldname FULL_SPEC_OF_FIELD ;

& it will not change parts of that spec that already meet that spec.

Adding or removing fields or altering indexes will often cause failure.

vavroom · March 27, 2014, 05:22:34 am

Thanks again Chris and Eileen.

Boldly going forward, but looking backward thanks to backups!)

Nicolas

vavroom · April 11, 2014, 09:52:43 am

//Edit: Never mind me. Need to update the length of the label fields as well as the name field. Now, it works...

Update on this: I did increase the field from varchar(255) to varchar(450). Added the code:

Code: [Select]

{capture assign=lang}{contact.preferred_language}{/capture} 
{capture assign=prefix}{contact.individual_prefix}{/capture} 
{if $lang=="fr_CA"} 
{if $prefix=="Mme"}
Chère {contact.individual_prefix} {contact.lastname}
{elseif $prefix=="M."}
Cher {contact.individual_prefix} {contact.lastname}
{else} 
Chère/Cher ami(e)
{elseif $lang=="en_CA"}
Dear {contact.individual_prefix} {contact.lastname}
{/if}

Assigned that greeting to a contact. So far so good.
Came to save and it killed things. The error message shows me that the greeting was truncated after 252 characters anyway. Went to look in the database itself, the greeting is there as a whole. Which indicates to me that somehow, Civi itself is discarding any character above the limit. *sigh*.

Looks like I'm going to have to learn custom tokens after all.

CiviCRM Community Forums (archive)

News:

Author Topic: Impact of increasing field size in database? (Read 885 times)

vavroom

Impact of increasing field size in database?

Chris Burgess

Re: Impact of increasing field size in database?

vavroom

Re: Impact of increasing field size in database?

Chris Burgess

Re: Impact of increasing field size in database?

Eileen

Re: Impact of increasing field size in database?

vavroom

Re: Impact of increasing field size in database?

vavroom

Re: Impact of increasing field size in database?