[SOLVED] Upgrade to 4.4.beta4 fails on 'Migrate custom word-replacements'

[ken]

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 ...

Code: [Select]

--- /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  ;
     ";

[ken]

Digging deeper, the SQL that fails is ...

Code: [Select]

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 'group' for key 'UI_find']
The problem is that the constraint UI_find is defined as ...

Code: [Select]

UNIQUE INDEX `UI_find`(find_word),
... when it should be ...

Code: [Select]

UNIQUE INDEX `UI_find`(domain_id, find_word),

[ken]

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

Code: [Select]

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'

[ken]

The civicrm_word_replacement needs a case sensitive collation in order to translate words that can appear in both uppercase and lowercase forms. Unless the wildcard match can get around that (?).

The following patch fixes the domain issue and the collation issue.

Code: [Select]

--- /data/Download/CiviCRM/civicrm-4.4.beta4-drupal/CRM/Upgrade/Incremental/php/FourFour.php 2013-10-02 23:25:16.000000000 +1000
+++ /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Upgrade/Incremental/php/FourFour.php 2013-10-14 20:11:56.452885782 +1100
@@ -221,9 +221,9 @@
      `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),
+    UNIQUE INDEX `UI_find`(domain_id, 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  ;
+)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_bin  ;
     ";
     $dao = CRM_Core_DAO::executeQuery($query);
 


[ken]

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.}

[Interpolat]

Pretty nifty Ken. Thanks.  Will try this patch and let you know if it resolves the issue on my end as well.

[Interpolat]

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.

[andros]

I run into this issue with the new stable release of civicrm 4.4
I have tried the patch but no luck.

[totten]

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]

Dear @totten,

in our context, we translate "group" and "Group" to "list" and "List" respectively (since our business model is that a 'group' is a bunch of people who get together occasionally, not the CiviCRM definition).

So the problem I'm observing requires either that the table be defined with a case-sensitive collation, or without an index. Neither option is ideal.

An alternative to using an index is to implement the check in code. Not strictly kosher, but as Word Replacements aren't edited often, not a big deal.

Dear @andros,

Are you doing word replacements for "word" and "Word". If not you may have another problem.

Dear @jmdezigns,

I can't really do patch consulting online, but as the changes are one-liners, you could try them by hand. Alternately, wait for 4.4.1 because using my patches to change table definitions without official sanction might 'bite you in the bum' (Australian slang for 'cause you a problem') in a later release.

[totten]

@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.

Code: [Select]

| 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 |


[ken]

@totten, cool!

[Interpolat]

@totten:  Looking forward to 4.4.1.  Thanks!

[Generic]

Same error for me.

Does this mean its best to wait for 4.4.1 before installing 4.4?

Code: [Select]

[Error: Migrate custom word-replacements]
CiviCRM_API3_Exception: "DB Error: already exists"

#0 /var/www/civicrm/public_html/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Incremental/php/FourFour.php(333): civicrm_api3("word_replacement", "replace", (Array:2))
#1 /var/www/civicrm/public_html/administrator/components/com_civicrm/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/civicrm/public_html/administrator/components/com_civicrm/civicrm/CRM/Queue/Task.php(79): call_user_func_array((Array:2), (Array:1))
#4 /var/www/civicrm/public_html/administrator/components/com_civicrm/civicrm/CRM/Queue/Runner.php(186): CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))
#5 /var/www/civicrm/public_html/administrator/components/com_civicrm/civicrm/CRM/Queue/Page/AJAX.php(44): CRM_Queue_Runner->runNext(TRUE)
#6 /var/www/civicrm/public_html/administrator/components/com_civicrm/civicrm/CRM/Queue/ErrorPolicy.php(80): {closure}()
#7 /var/www/civicrm/public_html/administrator/components/com_civicrm/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/civicrm/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(289): call_user_func((Array:2), (Array:5))
#10 /var/www/civicrm/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(72): CRM_Core_Invoke::runItem((Array:4))
#11 /var/www/civicrm/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:5))
#12 /var/www/civicrm/public_html/administrator/components/com_civicrm/admin.civicrm.php(116): CRM_Core_Invoke::invoke((Array:5))
#13 /var/www/civicrm/public_html/administrator/components/com_civicrm/admin.civicrm.php(40): civicrm_invoke()
#14 /var/www/civicrm/public_html/libraries/joomla/application/component/helper.php(383): require_once("/var/www/civicrm/public_html/administrator/components/com_civicrm/admin...")
#15 /var/www/civicrm/public_html/libraries/joomla/application/component/helper.php(351): JComponentHelper::executeComponent("/var/www/civicrm/public_html/administrator/components/com_civicrm/admin...")
#16 /var/www/civicrm/public_html/administrator/includes/application.php(153): JComponentHelper::renderComponent("com_civicrm")
#17 /var/www/civicrm/public_html/administrator/index.php(46): JAdministrator->dispatch()
#18 {main}

[nickholden]

@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?

Code: [Select]

$ 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