CRM-19915 Multi-lingual Upgrade from 4.6 fails on missing DB table or field

          Details

          • Type: Bug
          • Status: Won't Do
          • Priority: Important
          • Resolution: Won't Do
          • Affects Version/s: 4.7.15
          • Fix Version/s: None
          • Component/s: Internationalisation
          • Labels:
          • Versioning Impact:
            None (no code merged)
          • Documentation Required?:
            None
          • Funding Source:
            Needs Funding
          • Verified?:
            No

            Description

            In trying to upgrade from 4.6.24 to 4.7.15, I've been running into the following error:

            (
    [callback] => Array
        (
            [0] => CRM_Core_Error   
            [1] => handle
        )

    [code] => -18
    [message] => DB Error: no such table
    [mode] => 16
    [debug_info] =>
SELECT  v.label as label ,v.name as value, v.grouping as grouping
FROM   civicrm_option_value_ v,
       civicrm_option_group_ g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'languages'
  AND  g.is_active       = 1  AND  v.is_active = 1  ORDER BY v.weight [nativecode=1146 ** Table 'civisociet206b_0.civicrm_option_value_' doesn't exist]
    [type] => DB_Error
    [user_info] =>
SELECT  v.label as label ,v.name as value, v.grouping as grouping
FROM   civicrm_option_value_ v,
       civicrm_option_group_ g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'languages'
  AND  g.is_active       = 1  AND  v.is_active = 1  ORDER BY v.weight [nativecode=1146 ** Table 'civisociet206b_0.civicrm_option_value_' doesn't exist]
    [to_string] => [db_error: message="DB Error: no such table" code=-18 mode=callback callback=CRM_Core_Error::handle prefix="" info="
SELECT  v.label as label ,v.name as value, v.grouping as grouping
FROM   civicrm_option_value_ v,
       civicrm_option_group_ g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'languages'
  AND  g.is_active       = 1  AND  v.is_active = 1  ORDER BY v.weight [nativecode=1146 ** Table 'civisociet206b_0.civicrm_option_value_' doesn't exist]"]
)

            As you can see, it's looking for tables named "civicrm_option_value_" and "civicrm_option_group_", neither of which exit. However, looking for similarly named tables resulted in:

            mysql> show tables like '%civicrm_option_value%';
+-----------------------------------------------------+
| Tables_in_civisociet206bde (%civicrm_option_value%) |
+-----------------------------------------------------+
| civicrm_option_value                                |
| civicrm_option_value_en_US                          |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show tables like '%civicrm_option_group%';
+-----------------------------------------------------+
| Tables_in_civisociet206bde (%civicrm_option_group%) |
+-----------------------------------------------------+
| civicrm_option_group                                |
| civicrm_option_group_en_US                          |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

            So, it looks to me like an i10n settings isn't being set or picked up properly. Unfortunately, I haven't been able to find where this query is being built yet, to dig any further into it.

              Attachments

                Activity

                [CRM-19915] Multi-lingual Upgrade from 4.6 fails on missing DB table or field
                Jitendra Purohit added a comment -

                I've tried to upgrade from 4.6.24 to 4.7.15 and the process executed clean for me without any errors. Can you add some details to this eg. on which file the upgrade process halts itself, include backtrace etc.?

                Thanks.

                Christopher Gervais added a comment -

                Below is a backtrace of the error. At first glance, the issue appears to be caused by a custom Drush extension. Assuming I can confirm that, then I'll close this issue.

                #0 /path/to/civicrm/CRM/Core/Error.php(229): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /path/to/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
#3 /path/to/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: no such table", -18, 16, (Array:2), "\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...")
#4 /path/to/civicrm/packages/PEAR.php(575): DB_Error->__construct(-18, 16, (Array:2), "\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...")
#5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -18, NULL, NULL, "\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...", "DB_Error", TRUE)
#6 /path/to/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))
#7 /path/to/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#8 /path/to/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -18, NULL, NULL, "\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...", "DB_Error", TRUE)
#9 /path/to/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-18, NULL, NULL, NULL, "1146 ** Table 'civisociet206d_2.civicrm_option_value_' doesn't exist")
#10 /path/to/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#11 /path/to/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...")
#12 /path/to/civicrm/packages/DB/DataObject.php(2438): DB_common->query("\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...")
#13 /path/to/civicrm/packages/DB/DataObject.php(1627): DB_DataObject->_query("\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...")
#14 /path/to/civicrm/CRM/Core/DAO.php(354): DB_DataObject->query("\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...")
#15 /path/to/civicrm/CRM/Core/DAO.php(1297): CRM_Core_DAO->query("\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...", TRUE)
#16 /path/to/civicrm/CRM/Core/OptionGroup.php(159): CRM_Core_DAO::executeQuery("\nSELECT v.label as label ,v.name as value, v.grouping as grouping\nFROM c...", (Array:1))
#17 /path/to/civicrm/CRM/Core/PseudoConstant.php(296): CRM_Core_OptionGroup::values("languages", FALSE, FALSE, FALSE, NULL, "label", TRUE, FALSE, "name", "weight")
#18 /path/to/civicrm/CRM/Contact/BAO/Contact.php(3371): CRM_Core_PseudoConstant::get("CRM_Contact_BAO_Contact", "preferred_language", (Array:0), NULL)
#19 /path/to/civicrm/CRM/Core/I18n.php(156): CRM_Contact_BAO_Contact::buildOptions("preferred_language")
#20 /path/to/civicrm/CRM/Core/Smarty.php(154): CRM_Core_I18n::languages(TRUE)
#21 /path/to/civicrm/CRM/Core/Smarty.php(172): CRM_Core_Smarty->initialize()
#22 /path/to/civicrm/extensions/ndicivimp/ndicivimp.civix.php(13): CRM_Core_Smarty::singleton()
#23 /path/to/civicrm/extensions/ndicivimp/ndicivimp.php(381): _ndicivimp_civix_civicrm_config(Object(CRM_Core_Config))
#24 /path/to/civicrm/CRM/Utils/Hook.php(224): ndicivimp_civicrm_config(Object(CRM_Core_Config))
#25 /path/to/civicrm/CRM/Utils/Hook/DrupalBase.php(85): CRM_Utils_Hook->runHooks((Array:32), "civicrm_config", 1, Object(CRM_Core_Config), NULL, NULL, NULL, NULL, NULL)
#26 /path/to/civicrm/CRM/Utils/Hook.php(1104): CRM_Utils_Hook_DrupalBase->invoke(1, Object(CRM_Core_Config), NULL, NULL, NULL, NULL, NULL, "civicrm_config")
#27 /path/to/civicrm/CRM/Core/Config.php(118): CRM_Utils_Hook::config(Object(CRM_Core_Config))
#28 /path/to/civicrm/drupal/civicrm.module(218): CRM_Core_Config::singleton()
#29 /path/to/civicrm/drupal/civicrm_user.inc(136): civicrm_initialize()
#30 [internal function](): civicrm_user_categories()
#31 /var/aegir/platforms/demtools-civicrm-20170116/includes/module.inc(957): call_user_func_array("civicrm_user_categories", (Array:0))
#32 /var/aegir/platforms/demtools-civicrm-20170116/modules/user/user.module(3436): module_invoke_all("user_categories")
#33 /var/aegir/platforms/demtools-civicrm-20170116/modules/user/user.module(1845): _user_categories()
#34 [internal function](): user_menu()
#35 /var/aegir/platforms/demtools-civicrm-20170116/includes/menu.inc(2812): call_user_func("user_menu")
#36 /var/aegir/platforms/demtools-civicrm-20170116/includes/menu.inc(2775): menu_router_build()
#37 /var/aegir/platforms/demtools-civicrm-20170116/includes/common.inc(7660): menu_rebuild()
#38 /usr/share/drush/commands/provision/platform/drupal/clear_7.inc(7): drupal_flush_all_caches()
#39 phar:///usr/local/bin/drush/includes/drush.inc(78): include_once("/usr/share/drush/commands/provision/platform/drupal/clear_7.inc")
#40 phar:///usr/local/bin/drush/includes/engines.inc(520): drush_include("/usr/share/drush/commands/provision/platform/drupal", "clear")
#41 /usr/share/drush/commands/provision/platform/provision_drupal.drush.inc(372): drush_include_engine("drupal", "clear")
#42 /usr/share/drush/commands/provision/platform/deploy.provision.inc(194): _provision_drupal_rebuild_caches()
#43 [internal function](): drush_provision_drupal_post_provision_deploy("/var/aegir/backups/civisociet-206d.demcloud.org-20170124.190621.tar.gz")
#44 phar:///usr/local/bin/drush/includes/command.inc(373): call_user_func_array("drush_provision_drupal_post_provision_deploy", (Array:1))
#45 phar:///usr/local/bin/drush/includes/command.inc(224): _drush_invoke_hooks((Array:37), (Array:1))
#46 [internal function](): drush_command("/var/aegir/backups/civisociet-206d.demcloud.org-20170124.190621.tar.gz")
#47 phar:///usr/local/bin/drush/includes/command.inc(192): call_user_func_array("drush_command", (Array:1))
#48 phar:///usr/local/bin/drush/lib/Drush/Boot/BaseBoot.php(67): drush_dispatch((Array:37))
#49 phar:///usr/local/bin/drush/includes/preflight.inc(66): Drush\Boot\BaseBoot->bootstrap_and_dispatch()
#50 phar:///usr/local/bin/drush/includes/startup.inc(325): drush_main()
#51 phar:///usr/local/bin/drush/drush(114): drush_startup((Array:10))
#52 /usr/local/bin/drush(10): require("phar:///usr/local/bin/drush/drush")
#53 {main}
                Christopher Gervais added a comment -

                Commenting-out the offending line in the custom extension resolved the issue, and allowed the upgrade to proceed to completion. Sorry for the noise.

                Christopher Gervais added a comment -

                While I was able to proceed with the upgrade in the case above, there appear to be multiple things that will trigger this issue. It looks like they all call `CRM_Core_Config::clearDBCache()`, and the error happens shortly thereafter.

                Christopher Gervais added a comment -

                It looks like the global `$dbLocale` isn't set properly in `CRM_Core_DAO::query()` (CRM/Core/DAO.php). Found this, potentilly related issue: https://issues.civicrm.org/jira/browse/CRM-10606.

                Christopher Gervais added a comment -

                As far as I can tell, the problem appears to be in this snippet from CRM/Core/BAO/ConfigSetting.php:

                   global $dbLocale;

    // try to inherit the language from the hosting CMS
    if ($settings->get('inheritLocale')) {
      // FIXME: On multilanguage installs, CRM_Utils_System::getUFLocale() in many cases returns nothing if $dbLocale is not set
      $dbLocale = $multiLang ? ("_" . $settings->get('lcMessages')) : '';
      $chosenLocale = CRM_Utils_System::getUFLocale();
      if ($activatedLocales and !in_array($chosenLocale, explode(CRM_Core_DAO::VALUE_SEPARATOR, $activatedLocales))) {
        $chosenLocale = NULL;
      }
    }

    if (empty($chosenLocale)) {
      //CRM-11993 - if a single-lang site, use default
      $chosenLocale = $settings->get('lcMessages');
    }

    // set suffix for table names - use views if more than one language
    $dbLocale = $multiLang ? "_{$chosenLocale}" : '';

                The logic here (and earlier in CRM_Core_BAO_ConfigSetting::applyLocale()) seems somewhat convoluted. Either way, it's in this code that $dbLocale is getting set to "_", which is, in turn, is the causing this issue.

                Christopher Gervais added a comment - - edited

                Here is a patch that ensures that $dbLocale doesn't get set to "_".

                Mathieu Test added a comment -

                Hi Chris,

                Could you describe a few steps to reproduce a test-case?

                Could you submit a pull-request? Fork civicrm-core & commit on the master branch and submit a pull-request, this will run the tests automatically.

                The patch makes sense, but I need to better understand the context to avoid side effects.

                Mathieu Lutfy added a comment -

                (last comment was from me, mistakenly posted from my test account)

                Christopher Gervais added a comment -

                Sure thing.

                Note that I believe there's a deeper issue at work here that this patch doesn't fix. That is, presumably $chosenLocale ought to be populated by the time it gets appended to $dbLocale.

                Christopher Gervais added a comment -

                PR submitted and tests passed: https://github.com/civicrm/civicrm-core/pull/9739

                Christopher Gervais added a comment -

                Since $dbLocale is no longer being set, we get past the initial error, but then it creeps up again, as we're now looking for fields that ought to have a locale suffix. Relevant error and backtrace:

                CiviCRM: Clearing config cache. [17.67 sec, 31.03 MB]                                                                                                                                [notice]
CiviCRM: Removed /path/to/site/files/civicrm/ConfigAndLog/Config.IDS.ini [17.67 sec, 31.04 MB]                [success]
WD civicrm: $Fatal Error Details = Array                                                                                                                                              [debug]
(   
    [callback] => Array
        (   
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] =>
SELECT  v.label as label ,v.name as value, v.grouping as grouping
FROM   civicrm_option_value v,
       civicrm_option_group g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'languages'
  AND  g.is_active       = 1  AND  v.is_active = 1  ORDER BY v.weight [nativecode=1054 ** Unknown column 'v.label' in 'field list']
    [type] => DB_Error
    [user_info] =>
SELECT  v.label as label ,v.name as value, v.grouping as grouping
FROM   civicrm_option_value v,
       civicrm_option_group g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'languages'
  AND  g.is_active       = 1  AND  v.is_active = 1  ORDER BY v.weight [nativecode=1054 ** Unknown column 'v.label' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="
SELECT  v.label as label ,v.name as value, v.grouping as grouping
FROM   civicrm_option_value v,
       civicrm_option_group g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'languages'
  AND  g.is_active       = 1  AND  v.is_active = 1  ORDER BY v.weight [nativecode=1054 ** Unknown column 'v.label' in 'field list']"]
)
 [17.94 sec, 41.96 MB]
WD civicrm: $backTrace = #0 /path/to/civicrm/CRM/Core/Error.php(229): CRM_Core_Error::backtrace("backTrace", TRUE)    [debug]
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /path/to/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
#3 /path/to/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2),
"\nSELECT  v.label as label ,v.name as value, v.grouping as grouping\nFROM   c...")
#4 /path/to/civicrm/packages/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), "\nSELECT  v.label as label
,v.name as value, v.grouping as grouping\nFROM   c...")
#5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -19, NULL, NULL, "\nSELECT  v.label as label ,v.name as value, v.grouping as grouping\nFROM   c...",
"DB_Error", TRUE)
#6 /path/to/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))
#7 /path/to/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#8 /path/to/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "\nSELECT  v.label as
label ,v.name as value, v.grouping as grouping\nFROM   c...", "DB_Error", TRUE)
#9 /path/to/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown
column 'v.label' in 'field list'")
#10 /path/to/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#11 /path/to/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("\nSELECT  v.label as label ,v.name as
value, v.grouping as grouping\nFROM   c...")
#12 /path/to/civicrm/packages/DB/DataObject.php(2438): DB_common->query("\nSELECT  v.label as label ,v.name as
value, v.grouping as grouping\nFROM   c...")
#13 /path/to/civicrm/packages/DB/DataObject.php(1627): DB_DataObject->_query("\nSELECT  v.label as label ,v.name
as value, v.grouping as grouping\nFROM   c...")
#14 /path/to/civicrm/CRM/Core/DAO.php(354): DB_DataObject->query("\nSELECT  v.label as label ,v.name as value,
v.grouping as grouping\nFROM   c...")
#15 /path/to/civicrm/CRM/Core/DAO.php(1297): CRM_Core_DAO->query("\nSELECT  v.label as label ,v.name as value,
v.grouping as grouping\nFROM   c...", TRUE)
#16 /path/to/civicrm/CRM/Core/OptionGroup.php(159): CRM_Core_DAO::executeQuery("\nSELECT  v.label as label ,v.name
as value, v.grouping as grouping\nFROM   c...", (Array:1))
#17 /path/to/civicrm/CRM/Core/PseudoConstant.php(296): CRM_Core_OptionGroup::values("languages", FALSE, FALSE,
FALSE, NULL, "label", TRUE, FALSE, "name", "weight")
#18 /path/to/civicrm/CRM/Contact/BAO/Contact.php(3371): CRM_Core_PseudoConstant::get("CRM_Contact_BAO_Contact",
"preferred_language", (Array:0), NULL)
#19 /path/to/civicrm/CRM/Core/I18n.php(156): CRM_Contact_BAO_Contact::buildOptions("preferred_language")
#20 /path/to/civicrm/CRM/Core/Smarty.php(154): CRM_Core_I18n::languages(TRUE)
#21 /path/to/civicrm/CRM/Core/Smarty.php(172): CRM_Core_Smarty->initialize()
#22 /path/to/civicrm/extensions/ndipermissions/ndipermissions.civix.php(15): CRM_Core_Smarty::singleton()
#23 /path/to/civicrm/extensions/ndipermissions/ndipermissions.php(108):
_ndipermissions_civix_civicrm_config(Object(CRM_Core_Config))
#24 /path/to/civicrm/CRM/Utils/Hook.php(224): ndipermissions_civicrm_config(Object(CRM_Core_Config))
#25 /path/to/civicrm/CRM/Utils/Hook/DrupalBase.php(85): CRM_Utils_Hook->runHooks((Array:68), "civicrm_config", 1,
Object(CRM_Core_Config), NULL, NULL, NULL, NULL, NULL)
#26 /path/to/civicrm/CRM/Utils/Hook.php(1104): CRM_Utils_Hook_DrupalBase->invoke(1, Object(CRM_Core_Config), NULL,
NULL, NULL, NULL, NULL, "civicrm_config")
#27 /path/to/civicrm/CRM/Core/Config.php(118): CRM_Utils_Hook::config(Object(CRM_Core_Config))
#28 /path/to/civicrm/drupal/drush/civicrm.drush.inc(1513): CRM_Core_Config::singleton()
#29 /path/to/aegir/modules/hosting_civicrm/drush/provision_civicrm.inc(691): _civicrm_init()
#30 /path/to/aegir/modules/hosting_civicrm/drush/provision_civicrm.inc(667): _provision_civicrm_codebase_version()
#31 /path/to/aegir/modules/hosting_civicrm/drush/verify.provision.inc(47): _provision_civicrm_check_upgrade_mode()
#32 [internal function](): drush_provision_civicrm_provision_verify()
#33 phar:///usr/local/bin/drush/includes/command.inc(373): call_user_func_array("drush_provision_civicrm_provision_verify", (Array:0))
#34 phar:///usr/local/bin/drush/includes/command.inc(224): _drush_invoke_hooks((Array:37), (Array:0))
#35 [internal function](): drush_command()
#36 phar:///usr/local/bin/drush/includes/command.inc(192): call_user_func_array("drush_command", (Array:0))
#37 phar:///usr/local/bin/drush/lib/Drush/Boot/BaseBoot.php(67): drush_dispatch((Array:37))
#38 phar:///usr/local/bin/drush/includes/preflight.inc(66): Drush\Boot\BaseBoot->bootstrap_and_dispatch()
#39 phar:///usr/local/bin/drush/includes/startup.inc(325): drush_main()
#40 phar:///usr/local/bin/drush/drush(114): drush_startup((Array:7))
#41 /usr/local/bin/drush(10): require("phar:///usr/local/bin/drush/drush")
#42 {main}
                Christopher Gervais added a comment -

                So, while the patch/PR is an improvement (in the defensive programming sense), I think we still need to address the underlying issue. That is, $chosenLocale not getting set in CRM_Core_BAO_ConfigSetting::applyLocale(), as far as I can tell.

                Juan David Hurtado G added a comment -

                Seems to be an old unresolved issue, I can report that I have the same issue but under the next scenario:

                • Drupal version: 8.4.3
                • CiviCRM version: 4.7.27

                I installed CiviCRM following this steps  and using the drupal module from https://github.com/mydropwizard/civicrm-drupal.git

                In a clean install I just went to Settigns - Localization - Enable Multiple Languages and after enabling it, the site shows a "DB Error: no such table" which is not pretty informative, so I checked the log file and I can see this  (looks like same error from a previous comment )

                Please also check this comment when I compare the DB table before and after enabling the feature: https://gist.github.com/dsnopek/56311dbea347874e75180883efabb620#gistcomment-2310587

                Also I have to say that regarding the language of the Drupal installation, the column is always `label_en_US`

                Juan David Hurtado G added a comment -

                I can confirm same behavior using Drupal 7.56 with CiviCRM 4.7.27 or even 4.7.29. Steps to reproduce are the same:

                1. Install Drupal site (Drupal 7.56)
                2. Install CiviCRM 4.7.29
                3. Go to Settigns - Localization
                4. Check Enable Multiple Languages
                5. Click Save

                Error.

                Mathieu Lutfy added a comment -

                Hi Juan,

                Could you open a separate issue? This is unrelated. Also, in the new issue, can you post the full error and backtrace? It should be visible if you enable debugging. This documentation should have other tips: https://docs.civicrm.org/dev/en/latest/tools/debugging/

                Thank you,

                Mathieu

                Juan David Hurtado G added a comment -

                Hi Mathieu, for sure. I was going to ask if a separate issue makes more sense since the title of this one is a not exactly related.

                Done, new issue is: https://issues.civicrm.org/jira/browse/CRM-21627

                Thanks so much !

                Mathieu Lutfy added a comment -

                Closing this issue for lack of recent activity.

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    Christopher Gervais

                    Dates

                    • Created:
                      Updated:
                      Resolved: