Author Topic: Troubles moving a multilanguage CiviCRM 4.1.5 to a new server (Read 3379 times)

cray146 · November 30, 2012, 03:47:11 am

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

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 · November 30, 2012, 07:22:19 am

Hi,

The main bug is this:

Quote

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 · May 26, 2014, 07:47:55 pm

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 · August 15, 2014, 02:38:38 am

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 · August 18, 2014, 01:17:22 pm

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

haro · August 30, 2014, 01:52:54 am

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 · September 05, 2014, 07:36:14 pm

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.

CiviCRM Community Forums (archive)

News:

Author Topic: Troubles moving a multilanguage CiviCRM 4.1.5 to a new server (Read 3379 times)

cray146

Troubles moving a multilanguage CiviCRM 4.1.5 to a new server

mathieu

Re: Troubles moving a multilanguage CiviCRM 4.1.5 to a new server

Chris Burgess

Re: Troubles moving a multilanguage CiviCRM 4.1.5 to a new server

haro

Re: Troubles moving a multilanguage CiviCRM 4.1.5 to a new server

Chris Burgess

Re: Troubles moving a multilanguage CiviCRM 4.1.5 to a new server

haro

Re: Troubles moving a multilanguage CiviCRM 4.1.5 to a new server

Chris Burgess

Re: Troubles moving a multilanguage CiviCRM 4.1.5 to a new server