Author Topic: Error running report with custom fields and multiple language support enabled (Read 2472 times)

josue · January 31, 2011, 10:58:06 am

hey folks,

trying to run a report with custom fields. it's the civiengage walk list. i get

Quote

backTrace

/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 143
, handle,
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 931
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 968
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 564
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/packages/DB/DataObject.php, query, 2411
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1597
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 145
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 868
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm_engage/Engage/Report/Form/List.php, executeQuery, 185
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm_engage/Engage/Report/Form/WalkList.php, __construct, 46
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/CRM/Core/Controller.php, __construct, 388
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/CRM/Core/Controller/Simple.php, addPages, 81
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/CRM/Utils/Wrapper.php, __construct, 90
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/CRM/Report/Page/Report.php, run, 70
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 219
/home/members/progressivetech/sites/cj.ourpowerbase.net/web/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 351
, civicrm_invoke,
/usr/local/share/drupal-6.20/includes/menu.inc, call_user_func_array, 348
/usr/local/share/drupal-6.20/index.php, menu_execute_active_handler, 18

and

Code: [Select]

Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1, 1064
Additional Details:

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

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT label, value FROM civicrm_option_value_en_US WHERE option_group_id= [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]
    [type] => DB_Error
    [user_info] => SELECT label, value FROM civicrm_option_value_en_US WHERE option_group_id= [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT label, value FROM civicrm_option_value_en_US WHERE option_group_id= [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]"]
)

when i look at my tables, i have:

civicrm_option_values with 1359 records
civicrm_option_values_en_US that shows me 30 rows but tells me i have 0 rows
civicrm_option_values_es_MX that shows me 30 rows but tells me i have 0 rows

how is this supposed to work? did some of my tables get corrupted? please help!

--josue

josue · January 31, 2011, 03:57:41 pm

after some help on IRC i now know a little more about multiple language.

apparently civicrm_option_value_en_US is a DB VIEW (a temporary table of sorts). and it is supposed to be empty.

i was able to add a label in spanish to an activity title. i was then able to change the language to spanish (although my language switcher is not working, but i will save that problem for another post) and add an activity in spanish.

i also then ran other reports and they work. so it does seem to be a problem with the specific reports themselves.

CiviEngage has two reports, PhoneList and WalkList. both don't work on this site.

josue · January 31, 2011, 04:01:16 pm

sorry, forgot to add specific details.

am running civicrm 3.2.5 with civiengage and civicase enabled.

mysql version 5.0.

drupal 6.20

Piotr Szotkowski · February 01, 2011, 03:11:08 am

In multilingual installs the *_xx_YY views expose their counterpart ‘base’ tables in a way that translates the localised base tables’ *_xx_YY column names under their original names. To give you an example, the civicrm_option_value table has label_en_US and label_es_MX columns (but no label column), while the civicrm_option_value_en_US and civicrm_option_value_es_MX views each have a single label column, exposing civicrm_option_value.label_en_US and civicrm_option_value.label_es_MX columns (respectively).

To make this work more-or-less ‘transparently’ for code that does not care whether it’s being run on a multilingual install or not, we rewrite queries accessing multilingual tables on-the-fly and make them access views instead (we do that by rewriting the table name to the view name relevant to the current locale).

My guess about the problem with these two reports is that Engage_Report_Form_List::__construct() does quite a few db calls, and we might be rewriting them a bit too over-zealously, as it seems (from the error above) that the SELECT label, value FROM civicrm_option_value_en_US WHERE option_group_id= query is missing the value for option_group_id.

Can you check the queries in Engage_Report_Form_List::__construct() and see which one fails?

Note that CRM_Core_DAO::executeQuery()’s sixth parameter decides whether the query gets rewritten or not, so you can turn off rewriting by calling CRM_Core_DAO::executeQuery($query, array(), true, null, false, false) instead of CRM_Core_DAO::executeQuery($query).

jamie · June 27, 2011, 02:11:53 pm

I think I've worked this one out:

http://issues.civicrm.org/jira/browse/CRM-8379

CiviCRM Community Forums (archive)

News:

Author Topic: Error running report with custom fields and multiple language support enabled (Read 2472 times)

josue

Error running report with custom fields and multiple language support enabled

josue

Re: Error running report with custom fields and multiple language support enabled

josue

Re: Error running report with custom fields and multiple language support enabled

Piotr Szotkowski

Re: Error running report with custom fields and multiple language support enabled

jamie

Re: Error running report with custom fields and multiple language support enabled