Troubles moving a multilanguage CiviCRM 4.1.5 to a new server

[cray146]

Hi,

I run into trouble when i try to move my D7/CiviCRM 4.1.5 install to a new server. Drupal seems ok but when I enable CiviCRM i get the following error:

<snip>
Initialization Error

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => simpleHandler
        )

   

Code: [Select]

=> -13
    [message] => DB Error: division by zero
    [mode] => 16
    [debug_info] =>
SELECT
   option_value.id          as id,
   option_value.label       as label,
   option_value.value       as value,
   option_value.name        as name,
   option_value.description as description,
   option_value.weight      as weight,
   option_value.is_active   as is_active,
   option_value.is_default  as is_default
FROM
   civicrm_option_value_en_US  as option_value,
   civicrm_option_group_en_US  as option_group  WHERE option_group.id = option_value.option_group_id  AND option_group.name = 'languages' ORDER BY weight [nativecode=1356 ** View 'mysite_civicrm.civicrm_option_value_en_US' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them]
    [type] => DB_Error
    [user_info] =>
SELECT
   option_value.id          as id,
   option_value.label       as label,
   option_value.value       as value,
   option_value.name        as name,
   option_value.description as description,
   option_value.weight      as weight,
   option_value.is_active   as is_active,
   option_value.is_default  as is_default
FROM
   civicrm_option_value_en_US  as option_value,
   civicrm_option_group_en_US  as option_group  WHERE option_group.id = option_value.option_group_id  AND option_group.name = 'languages' ORDER BY weight [nativecode=1356 ** View 'mysite_civicrm.civicrm_option_value_en_US' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them]
    [to_string] => [db_error: message="DB Error: division by zero" code=-13 mode=callback callback=CRM_Core_Error::simpleHandler prefix="" info="
SELECT
   option_value.id          as id,
   option_value.label       as label,
   option_value.value       as value,
   option_value.name        as name,
   option_value.description as description,
   option_value.weight      as weight,
   option_value.is_active   as is_active,
   option_value.is_default  as is_default
FROM
   civicrm_option_value_en_US  as option_value,
   civicrm_option_group_en_US  as option_group  WHERE option_group.id = option_value.option_group_id  AND option_group.name = 'languages' ORDER BY weight [nativecode=1356 ** View 'mysite_civicrm.civicrm_option_value_en_US' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them]"]
)

backTrace

/home/mysite/apps/default/civicrm-4.1.5/CRM/Core/Error.php, backtrace, 225
, simpleHandler,
/home/mysite/apps/default/civicrm-4.1.5/packages/PEAR.php, call_user_func, 931
/home/mysite/apps/default/civicrm-4.1.5/packages/DB.php, PEAR_Error, 968
/home/mysite/apps/default/civicrm-4.1.5/packages/PEAR.php, DB_Error, 564
/home/mysite/apps/default/civicrm-4.1.5/packages/DB/common.php, raiseError, 1903
/home/mysite/apps/default/civicrm-4.1.5/packages/DB/mysql.php, raiseError, 898
/home/mysite/apps/default/civicrm-4.1.5/packages/DB/mysql.php, mysqlRaiseError, 327
/home/mysite/apps/default/civicrm-4.1.5/packages/DB/common.php, simpleQuery, 1216
/home/mysite/apps/default/civicrm-4.1.5/packages/DB/DataObject.php, query, 2428
/home/mysite/apps/default/civicrm-4.1.5/packages/DB/DataObject.php, _query, 1614
/home/mysite/apps/default/civicrm-4.1.5/CRM/Core/DAO.php, query, 153
/home/mysite/apps/default/civicrm-4.1.5/CRM/Core/DAO.php, query, 886
/home/mysite/apps/default/civicrm-4.1.5/CRM/Core/OptionValue.php, executeQuery, 488
/home/mysite/apps/default/civicrm-4.1.5/CRM/Core/I18n/PseudoConstant.php, getValues, 64
/home/mysite/apps/default/civicrm-4.1.5/CRM/Core/I18n/PseudoConstant.php, longForShortMapping, 55
/home/mysite/apps/default/civicrm-4.1.5/CRM/Utils/System/Drupal.php, longForShort, 569
/home/mysite/apps/default/civicrm-4.1.5/CRM/Utils/System.php, getUFLocale, 1086
/home/mysite/apps/default/civicrm-4.1.5/CRM/Core/BAO/ConfigSetting.php, getUFLocale, 315
/home/mysite/apps/default/civicrm-4.1.5/CRM/Core/Config.php, retrieve, 409
/home/mysite/apps/default/civicrm-4.1.5/CRM/Core/Config.php, _initVariables, 205
/home/mysite/apps/default/civicrm-4.1.5/drupal/civicrm.module, singleton, 336
/home/mysite/apps/default/civicrm-4.1.5/drupal/civicrm.module, civicrm_initialize, 1010
/home/mysite/apps/default/modules/filter/filter.module, _civicrm_filter_process, 761
/home/mysite/apps/default/modules/field/modules/text/text.module, check_markup, 319
/home/mysite/apps/default/modules/field/modules/text/text.module, _text_sanitize, 278
/home/mysite/apps/default/modules/field/field.default.inc, text_field_formatter_view, 210
/home/mysite/apps/default/modules/field/field.attach.inc, field_default_view, 209
/home/mysite/apps/default/modules/field/field.attach.inc, _field_invoke, 386
/home/mysite/apps/default/modules/field/field.attach.inc, _field_invoke_default, 1181
/home/mysite/apps/default/modules/node/node.module, field_attach_view, 1368
/home/mysite/apps/default/modules/node/node.module, node_build_content, 1284
/home/mysite/apps/default/sites/all/modules/ds/views/views_plugin_ds_entity_view.inc, node_view, 394
/home/mysite/apps/default/sites/all/modules/ds/views/views_plugin_ds_entity_view.inc, ds_views_row_render_node, 385
/home/mysite/apps/default/sites/all/modules/ds/views/views_plugin_ds_entity_view.inc, ds_views_row_render_entity, 326
/home/mysite/apps/default/sites/all/modules/views/plugins/views_plugin_style.inc, render, 364
/home/mysite/apps/default/sites/all/modules/views/plugins/views_plugin_style.inc, render_grouping_sets, 327
/home/mysite/apps/default/sites/all/modules/views/theme/theme.inc, render, 49
/home/mysite/apps/default/includes/theme.inc, template_preprocess_views_view, 1106
/home/mysite/apps/default/sites/all/modules/views/plugins/views_plugin_display.inc, theme, 2601
/home/mysite/apps/default/sites/all/modules/views/includes/view.inc, render, 1256
/home/mysite/apps/default/sites/all/modules/views/plugins/views_plugin_display_page.inc, render, 229
/home/mysite/apps/default/sites/all/modules/views/includes/view.inc, execute, 1337
/home/mysite/apps/default/sites/all/modules/views/views.module, execute_display, 526
, views_page,
/home/mysite/apps/default/includes/menu.inc, call_user_func_array, 517
/home/mysite/apps/default/index.php, menu_execute_active_handler, 21
</snip>

The db user civicrm_super has the necessary permissions as per http://wiki.civicrm.org/confluence/display/CRMDOC41/Localization

MariaDB [(none)]> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for civicrm_super@localhost                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SUPER, CREATE VIEW, SHOW VIEW ON *.* TO 'civicrm_super'@'localhost' IDENTIFIED BY PASSWORD '*903E44D2709E05313EC4D12603BD1B70C0621107' |
| GRANT ALL PRIVILEGES ON `mysite_civicrm`.* TO 'civicrm_super'@'localhost'                                                                  |

Does anyone have any clue?

Thanks,

c.

[mathieu]

Hi,

The main bug is this:

View 'mysite_civicrm.civicrm_option_value_en_US' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

If you type in MySQL: "show create view civicrm_option_value_en_US", it should output something with:

Code: [Select]

CREATE ALGORITHM=UNDEFINED DEFINER=`foobar`@`localhost` SQL SECURITY DEFINER VIEW `civicrm_option_value_en_US` AS
select `civicrm_option_value`.`id` AS `id`,
  `civicrm_option_value`.`option_group_id` AS `option_group_id`,
  `civicrm_option_value`.`value` AS `value`, [...]

In the above, the trigger is defined as "foobar", which is my MySQL user.

If you export/import your database from another server, your user was probably named differently. If you imported using mysql's root user, it probably accepted to create the trigger as another user, but then your user cannot run it.

Edit your ".sql" mysql dump file (with a text editor, or anything that can search & replace), to remove the definer statements, before doing the import.

Search for:

• /*!50003 CREATE*/ /*!50017 DEFINER=`foouser`@`localhost`*/
• * there may be other places where the definer is present, worst case, search for just the string of the user itself.

Also, make sure you are importing as your mysql user that will run civicrm, not the mysql root/admin user. It will help to detect any permission issue.

Mathieu

[Chris Burgess]

Ran into this "Divided by zero" issue again today - thought I'd drop a note here for future people loading a DB.

One way work around this I've seen in the community is to use the same MySQL user/password on dev and live.

I don't think that approach is ideal (live and dev sites should have different DB credentials IMO) so I strip the DEFINER out of the .sql dump.

We're removing this line:

Code: [Select]

/*!50013 DEFINER=`productionuser`@`localhost` SQL SECURITY DEFINER */

Using this one-liner:

Code: [Select]

perl -pi -e 's#DEFINER.* DEFINER##g' productiondbdump.sql

It'd be great if mysqldump could exclude the definer data but I haven't found that switch yet Or CiviCRM could not use triggers - I've posed this question before, Tim Otten gives some of CiviCRM's reasons for putting CRM logic into triggers  here.

[haro]

There seems to be a lot of different solutions for this "The user specified as a definer does not exist" error, see http://stackoverflow.com/questions/10169960/mysql-error-1449-the-user-specified-as-a-definer-does-not-exist and http://dba.stackexchange.com/questions/9249/how-do-i-change-the-definer-of-a-view-in-mysql.

I think the easiest one is to dump the civicrm database with the the --single-transaction switch so

Code: [Select]

mysqldump --single-transaction -uusername -p civicrmdb > c.sqlwich avoids this problem.

[Chris Burgess]

I am amazed. This actually works ... even though I don't think it should! You beauty.

[haro]

I'm not 100% sure about the --single-transaction dump. The problem is gone, so it nicely restores, but I don't see the views anymore so I think they need to be remade when you restore that way.

[Chris Burgess]

That does make sense. CiviCRM will rebuild the views periodically - whenever it detects a cache clear for one - so if you can get the site up and running, then clear caches CiviCRM may restore all its views and triggers and whatnot without permissions issues.

I'll be impressed if this works! Nice way to sidestep the username lock-in.