CiviCRM Community Forums (archive)

The analysis and solution is included below

@jmdezigns reported this at http://forum.civicrm.org/index.php/topic,29773.msg128302.html#msg128302 but on the back of another issue, so I'm going to give it it's own thread and take @totten's advice from that thread

Running the upgrade script gives the following exception ...

[Error: Migrate custom word-replacements]

CiviCRM_API3_Exception: "DB Error: already exists"

#0 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Upgrade/Incremental/php/FourFour.php(333): civicrm_api3("word_replacement", "replace", (Array:2))
#1 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Upgrade/Incremental/php/FourFour.php(230): CRM_Upgrade_Incremental_php_FourFour::rebuildWordReplacementTable()
#2 [internal function](): CRM_Upgrade_Incremental_php_FourFour::wordReplacements(Object(CRM_Queue_TaskContext))
#3 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Queue/Task.php(79): call_user_func_array((Array:2), (Array:1))
#4 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Queue/Runner.php(186): CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))
#5 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php(44): CRM_Queue_Runner->runNext(TRUE)
#6 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Queue/ErrorPolicy.php(80): CRM_Queue_Page_AJAX::{closure}()
#7 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php(47): CRM_Queue_ErrorPolicy->call(Object(Closure))
#8 [internal function](): CRM_Queue_Page_AJAX::runNext((Array:5))
#9 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Invoke.php(289): call_user_func((Array:2), (Array:5))
#10 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Invoke.php(72): CRM_Core_Invoke::runItem((Array:4))
#11 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:5))
#12 /var/www/citybibleforum/sites/all/modules/civicrm/drupal/civicrm.module(436): CRM_Core_Invoke::invoke((Array:5))
#13 [internal function](): civicrm_invoke("upgrade", "queue", "ajax", "runNext")
#14 /var/www/citybibleforum/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:4))
#15 /var/www/citybibleforum/index.php(21): menu_execute_active_handler()
#16 {main}

@totten asks "Can you use an SQL client to run the query 'select locale_custom_strings from civicrm_domain;' on the original database and post the result?" The result is the same in both the 4.2.7 and 4.4.beta4 databases ...

locale_custom_strings   
---------------------
a:1:{s:5:"en_US";a:2:{s:7:"enabled";a:2:{s:13:"wildcardMatch";a:4:{s:5:"Group";s:4:"List";s:5:"group";s:4:"list";s:12:"Organization";s:12:"Organisation";s:12:"organization";s:12:"organisation";}s:10:"exactMatch";a:0:{}}s:8:"disabled";a:2:{s:13:"wildcardMatch";a:0:{}s:10:"exactMatch";a:0:{}}}}
a:1:{s:5:"en_US";a:2:{s:7:"enabled";a:2:{s:13:"wildcardMatch";a:4:{s:5:"Group";s:4:"List";s:5:"group";s:4:"list";s:12:"Organization";s:12:"Organisation";s:12:"organization";s:12:"organisation";}s:10:"exactMatch";a:0:{}}s:8:"disabled";a:2:{s:13:"wildcardMatch";a:0:{}s:10:"exactMatch";a:0:{}}}}
a:1:{s:5:"en_US";a:2:{s:7:"enabled";a:2:{s:13:"wildcardMatch";a:4:{s:5:"Group";s:4:"List";s:5:"group";s:4:"list";s:12:"Organization";s:12:"Organisation";s:12:"organization";s:12:"organisation";}s:10:"exactMatch";a:0:{}}s:8:"disabled";a:2:{s:13:"wildcardMatch";a:0:{}s:10:"exactMatch";a:0:{}}}}
a:1:{s:5:"en_US";a:2:{s:7:"enabled";a:2:{s:13:"wildcardMatch";a:4:{s:5:"group";s:4:"list";s:5:"Group";s:4:"List";s:12:"organization";s:12:"organisation";s:12:"Organization";s:12:"Organisation";}s:10:"exactMatch";a:0:{}}s:8:"disabled";a:2:{s:13:"wildcardMatch";a:0:{}s:10:"exactMatch";a:0:{}}}}

Analysis

There are two issues with the definition of table civicrm_word_replacement.

• The unique index 'UI_find' doesn't include the domain_id, so the upgrade will fail if multiple domains have the same 'from' string.
• The table uses a case-insensitive collation, so the upgrade will fail if you want to do word replacements for both 'Word' and 'word'.

I suspect both these issues will be common and affect a number of sites.

Solution

The following patch fixes these issues, by removing the UNIQUE INDEX ...

--- /data/Download/CiviCRM/civicrm-4.4.beta4-drupal/./CRM/Upgrade/Incremental/php/FourFour.php 2013-10-02 23:25:16.000000000 +1000
+++ /data/Work/IT/CiviCRM/Local/4.4.beta4/civicrm/./CRM/Upgrade/Incremental/php/FourFour.php 2013-10-15 09:09:22.240937010 +1100
@@ -221,7 +221,6 @@
      `match_type` enum('wildcardMatch', 'exactMatch')   DEFAULT 'wildcardMatch',
      `domain_id` int unsigned    COMMENT 'FK to Domain ID. This is for Domain specific word replacement',
     PRIMARY KEY ( `id` ),
-    UNIQUE INDEX `UI_find`(find_word),
     CONSTRAINT FK_civicrm_word_replacement_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
     ";

Digging (yet) deeper, having patched UI_find as described above, I get another error ...

INSERT INTO civicrm_word_replacement (find_word , replace_word , is_active , match_type , domain_id ) VALUES ('group' , 'list' ,  1 , 'wildcardMatch' ,  1 )  [nativecode=1062 ** Duplicate entry '1-group' for key 'UI_find']
This occurs because I am attempting to convert 'Group' to 'List' and 'group' to 'list' and the unique index borks on the second '[Gg]roup'

An issue with my solution is that using a non-standard collation setting for this one table is bound to cause an issue with future maintenance.

Would it be useful to simply drop the constraint? Is it really an issue if a system admin sets up 'Contact' to translate to both (say) 'Brother' and 'Sister'? Either replacement is either correct, or if not, the problem would be soon recognised and fixed.

{I've updated the original post with this analysis and solution.}

Ken,

I am having problems applying the patches.  I am trying to upgrade to the newly released 4.4 on Wordpress.  I see references to Drupal within your patch.  Is this correct.  Can you please walk me through how I apply the patch.

Thanks, Ken!

It makes sense tome that we would include domain_id as part of the unique key. I'll add that to the task list for 4.4.1 and see if I can reproduce any errors with the data from jmdezigns example.

As far as removing the unique constraint entirely... Doing so may not cause any hard errors, but I don't understand what behavior is intended by such a configuration. For example, if the UI normally displays the string "Delete contact" and there are two replacements for "contact" ("brother", "sister"), we wouldn't simultaneously print out "Delete brother" and "Delete sister"... And if the software only translates to one of them, it would basically be an arbitrary choice. (Aside: There's an ulterior motive for including this constraint -- it makes the migration logic and the form simpler.)

@ken, right - so we need to also set the collation to provide for case-sensitivity.

I've opened an issue, submitted changes for 4.4.1, and tested locally with two word-replacements that differ in case ("The" vs "the"):
 * Issue: http://issues.civicrm.org/jira/browse/CRM-13655
 * Changes: https://github.com/civicrm/civicrm-core/pull/1859
 * Changes (as a diff file) https://github.com/civicrm/civicrm-core/pull/1859.diff

Note that this patch makes a few changes from ken's:
 * The new collation applies to the specific columns, find_word and replace_word, rather than the whole table.
 * The constraint is renamed from UI_find to UI_domain_find (rather than keeping the name "UI_find").
 * The final schema will be the same in all three of these situations (rather than producing slightly different variants in each case):
   - Fresh installation
   - Upgrade 4.3.x => 4.4.0 => 4.4.1
   - Upgrade 4.3.x => 4.4.1

You should be able to apply this patch on top of 4.4.0. However, if you previously used ken's patch, it may not upgrade cleanly to 4.4.1. The easiest/safest thing will be rerun upgrade from the original (pre-4.4.0) DB.

| civicrm_word_replacement | CREATE TABLE `civicrm_word_replacement` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Word replacement ID',
  `find_word` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'Word which need to be replaced',
  `replace_word` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'Word which will replace the word in find',
  `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this entry active?',
  `match_type` enum('wildcardMatch','exactMatch') COLLATE utf8_unicode_ci DEFAULT 'wildcardMatch',
  `domain_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Domain ID. This is for Domain specific word replacement',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UI_domain_find` (`domain_id`,`find_word`),
  CONSTRAINT `FK_civicrm_word_replacement_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |


@totten:  Looking forward to 4.4.1.  Thanks!

@totten

I've tried to patch the 4.4.0 files but hit a problem. I don't seem to have the /xml/GenCode.php file - where should it be?

$ sudo patch -p1 < 1859.diff
patching file CRM/Upgrade/Incremental/php/FourFour.php
Hunk #1 FAILED at 141.
Hunk #2 succeeded at 215 (offset -40 lines).
Hunk #3 succeeded at 232 (offset -40 lines).
1 out of 3 hunks FAILED -- saving rejects to file CRM/Upgrade/Incremental/php/FourFour.php.rej
can't find file to patch at input line 63
Perhaps you used the wrong -p or --strip option?
The text leading up to this was:
--------------------------
|diff --git a/xml/GenCode.php b/xml/GenCode.php
|index 043850e..75ec817 100644
|--- a/xml/GenCode.php
|+++ b/xml/GenCode.php
--------------------------

Can you describe where / how to apply this patch?

Thanks