My sugarCRM displays SQL errorr

[emu]

I check on Cases, and the information of all case is shown on the upper part of the screen, but on the lower part, the SQL error appears:

Error retrieving Case list: Query Failed SELECT meetings.id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.date_start , meetings.parent_id , meetings.parent_type , meetings.time_start , meetings.assigned_user_id , 'meetings' panel_name FROM meetings where ( meetings.parent_id= '5c1a5b4a-381f-5984-24c6-43c720bd32eb' AND meetings.parent_type='Cases' AND meetings.deleted=0 AND (meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , tasks.status , CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')) contact_name , tasks.contact_id , jt0.assigned_user_id contact_name_owner , 'Contacts' contact_name_mod, tasks.date_due as date_start , tasks.parent_id , tasks.parent_type , tasks.time_due as time_start , tasks.assigned_user_id , 'tasks' panel_name FROM tasks LEFT JOIN contacts jt0 ON jt0.id= tasks.contact_id AND jt0.deleted=0 AND jt0.deleted=0 where ( tasks.parent_id= '5c1a5b4a-381f-5984-24c6-43c720bd32eb' AND tasks.parent_type='Cases' AND tasks.deleted=0 AND (tasks.status='Not Started' OR tasks.status='In Progress' OR tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.date_start , calls.parent_id , calls.parent_type , calls.time_start , calls.assigned_user_id , 'calls' panel_name FROM calls where ( calls.parent_id= '5c1a5b4a-381f-5984-24c6-43c720bd32eb' AND calls.parent_type='Cases' AND calls.deleted=0 AND (calls.status='Planned')) AND calls.deleted=0 )::MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (tis620_thai_ci,IMPLICIT) for operation 'UNION'

Same as previous Thread
http://www.sugarcrm.com/forums/showt...ighlight=cases

In that thread solve problem by edit config.php form 'db_type' => 'NULL', to'db_type' => 'mysql',
So I check but my config.php is'db_type' => 'mysql' already !!!

Please suggest me to solve this problem.

Emu

[Superman]

What version of Sugar are you running?

[emu]

I use SugarCRM 4.0 Latest Stable Full

[Superman]

I will give a solution that WingedFox has suggested.

Open {sugar}/include/database/PearDatabase.php.
Now find function connect() method (line 535).
Write the following:

 $this->database =@mysql_pconnect($this->dbHostName,$this->userName,$this->userPassword);
mysql_query("SET @@ collation_connection = @@ collation_database"); 
  

This should help.

[Muhammad]

Nop. It didnt help me.
Let me paste the exact code so we can where am I doing what wrong?

This was the initial code:

function connect($dieOnError = false){
global $sugar_config;
if($this->dbType == "mysql" && $sugar_config['dbconfigoption']['persistent'] == true){
$this->database =@mysql_pconnect($this->dbHostName,$this->userName,$this->userPassword);
@mysql_select_db($this->dbName) or sugar_die( "Unable to select database: " . mysql_error());
if(!$this->database){
$this->connection = mysql_connect($this->dbHostName,$this->userName,$this->userPassword) or sugar_die("Could not connect to server ".$this->dbHostName." as ".$this->userName.".".mysql_error());
......

i replaced it with:

function connect($dieOnError = false){
global $sugar_config;
if($this->dbType == "mysql" && $sugar_config['dbconfigoption']['persistent'] == true){
$this->database =@mysql_pconnect($this->dbHostName,$this->userName,$this->userPassword);
/////// i entered this line of code in /////////////////////////
mysql_query("SET @@ collation_connection = @@ collation_database");
//////// end of code ////////////////////////////////////////////////////
@mysql_select_db($this->dbName) or sugar_die( "Unable to select database: " . mysql_error());
if(!$this->database){
$this->connection = mysql_connect($this->dbHostName,$this->userName,$this->userPassword) or sugar_die("Could not connect to server ".$this->dbHostName." as ".$this->userName.".".mysql_error());
......

is that OK?

cheers.