CiviCRM Community Forums (archive)

Hi!

My client are trying to create several groups where the names are long and the first 31 character is the same for all groups, like:
ÖFÖC Östersjöns Författar- och Översättarcentrum repr. skap
ÖFÖC Östersjöns Författar- och Översättarcentrum styrelse

The first group are created but the second can't be created probably because the database names are the same for both groups. The second generates following error on both my setup with Drupal 7.0/Civicrm 4.0.5 and on d7.demo.civicrm.org:
Sorry. A non-recoverable error has occurred.
DB Error: already exists

The backtrace (on my site) is as follows:
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 159
, handle,
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 931
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 968
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 564
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/DB/DataObject.php, query, 2424
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1054
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Core/DAO.php, insert, 283
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Contact/BAO/Group.php, save, 366
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Group/Form/Edit.php, create, 417
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Core/Form.php, postProcess, 250
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php, mainProcess, 153
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php, realPerform, 130
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
/var/www/drupal-7.0/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Core/Controller.php, handle, 284
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Group/Controller.php, run, 76
/var/www/drupal-7.0/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 224
/var/www/drupal-7.0/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 443
, civicrm_invoke,
/var/www/drupal-7.0/includes/menu.inc, call_user_func_array, 501
/var/www/drupal-7.0/index.php, menu_execute_active_handler, 22

I guess a good solution is to add a random suffix to all generated database names. By generated I mean database names where the title/label had to be changed due to foreign characters and/or to long name.

Or to check if both label and database name match if we suspect a duplet. And if the labels arent't the same add a random suffix.

Does anyone know wich part of the code where I can find the code that converts and/or truncates titles/labels into database names?

1. name and title are 64 characters. we might want to extend title to 255 characters

2. there is a unique index on title which seems wrong. we should eliminate it, IMO

Comment on 1: The name can hold 64 characters in the database but we never store longer names than 31 characters due to the default maxlen in titleToVar function that is being used to create the name: static function titleToVar( $title, $maxLength = 31 )

Comment on 2: At first glance it seems wrong because it's sufficient that the name is unique but do a civicrm user ever want to groups with identical titles? I think it's a good thing that a user can't create to groups with identical display name.

I added following line too the create function before the first group->save():
$group->name .= "_tmp";

And then after we got an id I change the group name to the final name with these line
$group->name = substr($group->name,0,-4) . "_{$group->id}";

And it worked out fine. When I created a group it got the id as a suffix. So it solved my problem. But when I rename a group the database name is being changed to _<id>. The name part disappears. I will look into this more if I find the time. Not today though. Any ideas?

I was just so suprised by the fact that the create function is being called when editing an existing group. That is being done in the function postProcess() in the file Group/Form/Edit.php. I don't know why.

But I solved the problem by making sure that we don't already have an ID (which should mean that the group already exists?) and only change name if that's the case. Like this:

if ( !CRM_Utils_Array::value( 'id', $params ) ) {
            $group->name .= "_{$group->id}";
}

Now it works for me but not completely because I already have groups with database names without the id suffix. This code above is run after the creation of the group, that means after the check if the name already exists. So when I try to add ÖFÖC Östersjöns Författar- och Översättarcentrum styrelse civicrm tells me that the group already exist. But everything works fine if I first go into the database and manually add the id suffix to all existing group names.

Do you write code to handle this kind of issues? The issue could never arise if you start adding groups from scratch with my code. It could only become an issue when upgrading. There's an simple solution to this. And that is to use  the _tmp suffix I showed in my previous post. Comments?

I found another error in the formRule function. When we search for match we use a like search but don't take care of the underscore. Underscore in a mysql like search mathes any character and we don't want that.

So for my problem with Fackreferensgrupp Ö I solved it by escaping any underscore, like this:
-$grpCnt = CRM_Core_DAO::singleValueQuery( $query, array( 1 => array( $name, 'String' ),
                                                                                               2 => array( $title, 'String' ),
                                                                                               3 => array( (int)$self->_id, 'Integer' ) ) );
+$grpCnt = CRM_Core_DAO::singleValueQuery( $query, array( 1 => array( str_replace('_','\_',$name), 'String' ),
                                                                     2 => array( str_replace('_','\_',$title), 'String' ),
                                                                     3 => array( (int)$self->_id, 'Integer' ) ) );

Maybe this should be a generic solution instead but the singleValueQuery don't know if we use a like search or not. So we could alter the singleValueQuery so that we can trigger the replace for all like searches but that is a much bigger change.

I have only submitted one patch before so I don't know how you guys want the solution to be:

1. simple and easy to test but it does only affect groups
2. extensive

And should we take in consideration that many people are going to upgrade to this version and write code that also handle "old" records in the database that lacks the id suffix? (I doing this now because my client already have many groups. It's just a creation of a _tmp suffix first and then change it to the _<id> after we have recieved the new id)

Yes I tried changing the LIKE to = and that also solved another character encoding problem I have (in LIKE searches MySQL often thinks the scandinavian characters Å and Ä are the same).

The only downside of using = instead of LIKE (in this case) is that = is case sensitive

So do you want me to submit a patch with following:

1. First create new group with name suffix _tmp (to avoid problem with already existing "old" group names)
2. When group is successfully created: remove _tmp suffix and add id suffix to group database name (<name>_<id>)
3.  When checking if group already exists:
     a) change LIKE to =
     b) don't care if title is the same
     c) use 31 character for database name comparison (as we do when we create new groups)

So hello again Lobo!

I have now created the issue http://issues.civicrm.org/jira/browse/CRM-9002 and uploaded to patch files including this:

 First create new group with name suffix _tmp (to avoid problem with already existing "old" group names)
2. When group is successfully created: remove _tmp suffix and add id suffix to group database name (<name>_<id>)
3.  When checking if group already exists:
     a) change LIKE to =
     b) use same length for name as when group is created, which is the default length of 31

Maybe the length of name when checking should be 63 as you say but then it would never match groups that were created "the normal" way via the web interface. So in my case I have to check for 31. Maybe the name should be 64 characters when creating groups via the web interface?

Yeah, you're completely right! I thought about already existing names without the suffix. But, as you say, that's not ever going to be an issue thanks to the id suffix.

So now our work here is done, I guess 

// Jonas