Database Connectivity

[apsc]

Hi,

I have successfully installed SugarCRM onto my linux platoform. Running on MySQL and other stated requirements, I encountered some database error problems on specific tables which I am not sure. Care to shine some light which I may had overlooked during my initial installations ? below are some of the error messages i received:

Error retrieving Meeting list: Query Failed: SELECT contacts.id , jt0.id m_accept_status_fields, contacts.first_name , contacts.last_name , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name , jt1.name account_name, contacts.email1 , contacts.phone_work , contacts.assigned_user_id , 'contacts' panel_name FROM contacts AND jt0.deleted=0 AND jt1.deleted=0 where contacts.deleted=0 LIMIT 0,10::MySQL error 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 'AND jt0.deleted=0 AND jt1.deleted=0 where contacts.deleted=0 LIMIT 0,10' at line 1

It occured when I tried to set up a meeting record. I get these similar messages when I try to create accounts too.

PatriX

[blipper2000]

Same situation here after upgrading to 4.2.0a. For some reason either the 1=1 clause or another clause is not being generated.

REbuilding relationships, extensions, restarting httpd, etc has had no effect.

[kbrill]

Hi,

I have successfully installed SugarCRM onto my linux platoform. Running on MySQL and other stated requirements, I encountered some database error problems on specific tables which I am not sure. Care to shine some light which I may had overlooked during my initial installations ? below are some of the error messages i received:

Error retrieving Meeting list: Query Failed: SELECT contacts.id , jt0.id m_accept_status_fields, contacts.first_name , contacts.last_name , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name , jt1.name account_name, contacts.email1 , contacts.phone_work , contacts.assigned_user_id , 'contacts' panel_name FROM contacts AND jt0.deleted=0 AND jt1.deleted=0 where contacts.deleted=0 LIMIT 0,10::MySQL error 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 'AND jt0.deleted=0 AND jt1.deleted=0 where contacts.deleted=0 LIMIT 0,10' at line 1

It occured when I tried to set up a meeting record. I get these similar messages when I try to create accounts too.

PatriX

Where in the world is jt0 and jt1 comming from? I just searched my whole code base and dont see them anywhere. Did you put any new modules on your system?

[stevec]

Eh? Where's the 'WHERE' clause in that SQL statement? Are you sure you copied the whole message? It should be between "FROM contacts" and "AND jt0.deleted=0" as should some other condition.

[blipper2000]

My guess is that jt0 is a dynamically generated variable built from relationship mappings some how... There isn't an explicit jt0 variable in the code.

The problem is that the SQL is generating statement in pattern of:

SELECT <BLAH BLAH> FROM <TABLEA> <SHOULD BE LEFT JOIN HERE ON USERS OR SOME OTHER TABLE WITH ON CONDITION> AND <MORE CONDITIONS> <REST OF SQL STATEMENT INCLUDING LOTS OF UNIONS>

For some reason the left join is being ommitted. The variable probably just end sup holding null or empty string...

This sucks because
a) It is really hard to debug this
b) There is no sanity checking anywher ein the sugar code to figure out that the SQL statement is highly broken.

Sugar, please help! Or at least explain how to diagnose and figure out why the LEFT JOIN is resolving to null. Please note that the broken SQL reoccurs in the many of the selects that are glued together by the UNION ALL.

[blipper2000]

stevec,

The bug is that the WHERE clause is NOT being generated... Hence the MySQL syntax error generated.

[blipper2000]

It appears on my side that all the contacts relationships don't exist in the cache and the rebuild doesn't recreate them....

Tue May 2 14:19:30 2006,590 [16533] FATAL SugarCRM - Error fetching relationship from cache accounts_contacts
Tue May 2 14:19:30 2006,590 [16533] DEBUG SugarCRM - Link Constructor, _bean_table_name: contacts
Tue May 2 14:19:30 2006,590 [16533] DEBUG SugarCRM - Link Constructor, _bean_key_name: id
Tue May 2 14:19:30 2006,590 [16533] DEBUG SugarCRM - Link Constructor, No relationship record.

Tue May 2 14:19:30 2006,588 [16533] FATAL SugarCRM - Error fetching relationship from cache meetings_contacts
Tue May 2 14:19:30 2006,588 [16533] DEBUG SugarCRM - Link Constructor, _bean_table_name: contacts
Tue May 2 14:19:30 2006,588 [16533] DEBUG SugarCRM - Link Constructor, _bean_key_name: id
Tue May 2 14:19:30 2006,588 [16533] DEBUG SugarCRM - Link Constructor, No relationship record.

[smellygig]

Anyone from Sugar care to add something to this discussion?

The only thing that possibly sticks out is that we are running PHP 4.4.0 and the compatibility matrix says 4.4.1-4.4.2. Could this be the problem?


Alex

[smellygig]

Anyone?

Alex

[lancasteru]

Ya guys, its the same problem on my side but little bit unusual.

My crm screen got blank to nite and in server errro log found this;
PHP Fatal error: main(): Failed opening required './cache/custom_fields/custom_fields_def.php' (include_path='.:/usr/share/pear') in /var/www/vhosts/harlequindomains.com/httpdocs/crm/modules/CustomFields/CustomFields.php on line 35, referer: http://www.harlequindomains.com/crm/...eetings&return

Now as we can see, there is some file needed in customfileds.php on line 35. I tried to uncomment and bingo, I got my sugar back but with loads of sql error on list view of contacts just like you people. So just commented back that stuff. I checked whether the cache has that file and iindeed it has. I also checked the permission and everything sems laright. This cache folder is really killing me in 4.2.0. I think there is massive file write error in cache in version 4.2.0. What do u say guys, Sugar team, where r u. We need u guys, help us out, pleaseeee

Lancasteru...