Unable to list contacts

[nathanfunk]

I've been having a somewhat rough experience setting up SugarCRM... I installed according to the docs and imported accounts successfully. I now also imported contacts, but I am having problems viewing them and adding new ones through sugar.

This is what I get in the log file when I hit the "Clear" button in the Contacts section. I expect to see a list of contacts under Contact List, but instead, none are listed.

Thanks,

Nathan


Wed Jul 18 02:21:01 2007,809 [30643] INFO SugarCRM - Query:SELECT * from relationships where deleted=0
Wed Jul 18 02:21:01 2007,809 [30643] INFO SugarCRM - Query Execution Time:0.00031599999999998
Wed Jul 18 02:21:01 2007,861 [30643] FATAL SugarCRM - Error fetching relationship from cache accounts_contacts
Wed Jul 18 02:21:01 2007,862 [30643] INFO SugarCRM - Query:SELECT * from relationships where deleted=0
Wed Jul 18 02:21:01 2007,863 [30643] INFO SugarCRM - Query Execution Time:0.000305
Wed Jul 18 02:21:01 2007,913 [30643] FATAL SugarCRM - Error fetching relationship from cache contacts_assigned_user
Wed Jul 18 02:21:01 2007,914 [30643] INFO SugarCRM - Query: SELECT contacts.id , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, contacts.title , contacts.email1 , contacts.phone_work , jt1.user_name assigned_user_name, contacts.assigned_user_id FROM contacts AND accounts.deleted=0 AND jt1.deleted=0 where contacts.deleted=0 LIMIT 0,21
Wed Jul 18 02:21:01 2007,915 [30643] INFO SugarCRM - Query Execution Time:0.00014900000000007
Wed Jul 18 02:21:01 2007,915 [30643] ERROR SugarCRM - 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 accounts.deleted=0 AND jt1.deleted=0 where contacts.deleted=0 LIMIT 0,21' at line 1
Wed Jul 18 02:21:02 2007,130 [30643] INFO SugarCRM - Query:SELECT id, name FROM saved_search
WHERE
deleted = '0' AND
assigned_user_id = '1' AND
search_module = 'Contacts'
ORDER BY name
Wed Jul 18 02:21:02 2007,131 [30643] INFO SugarCRM - Query Execution Time:0.00011

[nathanfunk]

I should add that I am using version 4.5.1e. I expect the issue to possibly be related to file permissions and ownership, since that's what has been causing previous problems (e.g. log file wasn't created because main directory wasn't writable)

Nathan

[sanjaykatiyar1]

Which version of MySQL you are using.

Also check in the sugar database the contacts table, if the records are really there.

[nathanfunk]

I am using MySQL version 4.1.21-standard, and I have checked whether there are entries in the contacts table.

I got a little bit further on this by manually creating a cache/modules/Relationships directory with 777 permissions. Now I no longer see the

Error fetching relationship from cache...

errors in the log. It appears that the directories created by SugarCRM do not have the appropriate permissions, so it makes a directory but can not create files in it. This is just a hunch.

The problem is however still not solved. For both contacts and accounts I do not see any entries through SugarCRM pages even though they exist in the database.

I've attached the logs for pressing the "Clear" button in Contacts again. Now there are no errors, but as mentioned, I can still not see any contacts listed.

Thanks,

Nathan


Wed Jul 18 12:07:54 2007,482 [30366] INFO SugarCRM - Connect:Resource id #86
Wed Jul 18 12:07:54 2007,482 [30366] INFO SugarCRM - Query:SELECT id, name, symbol, conversion_rate FROM currencies WHERE status = 'Active' and deleted = 0
Wed Jul 18 12:07:54 2007,483 [30366] INFO SugarCRM - Query Execution Time:7.5999999999965E-05
Wed Jul 18 12:07:54 2007,488 [30366] INFO SugarCRM - Query:SELECT category, name, value FROM config
Wed Jul 18 12:07:54 2007,489 [30366] INFO SugarCRM - Query Execution Time:8.9999999999979E-05
Wed Jul 18 12:07:54 2007,496 [30366] INFO SugarCRM - Query:SELECT users.* FROM users WHERE users.id = '1' AND users.deleted=0 LIMIT 0,1
Wed Jul 18 12:07:54 2007,497 [30366] INFO SugarCRM - Query Execution Time:0.00015099999999996
Wed Jul 18 12:07:54 2007,505 [30366] INFO SugarCRM - Query:SELECT u1.first_name, u1.last_name from users u1, users u2 where u1.id = u2.reports_to_id AND u2.id = '1' and u1.deleted=0
Wed Jul 18 12:07:54 2007,505 [30366] INFO SugarCRM - Query Execution Time:7.5999999999965E-05
Wed Jul 18 12:07:54 2007,506 [30366] INFO SugarCRM - In module: Contacts -- About to take action index
Wed Jul 18 12:07:54 2007,507 [30366] INFO SugarCRM - current page is modules/Contacts/index.php
Wed Jul 18 12:07:54 2007,507 [30366] INFO SugarCRM - current module is Contacts
Wed Jul 18 12:07:54 2007,507 [30366] INFO SugarCRM - Query:SELECT count(*) as the_count FROM config WHERE category='info' AND name='sugar_version' AND value = '4.5.1'
Wed Jul 18 12:07:54 2007,508 [30366] INFO SugarCRM - Query Execution Time:7.1000000000043E-05
Wed Jul 18 12:07:54 2007,539 [30366] INFO SugarCRM - Query:
SELECT meetings.id, name,reminder_time, description,location, date_start, time_start, assigned_user_id,
CONCAT( date_start, CONCAT(' ', time_start ) ) AS dateTime
FROM meetings LEFT JOIN meetings_users ON meetings.id = meetings_users.meeting_id
WHERE meetings_users.user_id ='1'
AND meetings.reminder_time != -1
AND meetings_users.deleted != 1
HAVING dateTime >= '2007-07-18 17:07:54' AND dateTime <= '2007-07-18 18:07:54'
Wed Jul 18 12:07:54 2007,540 [30366] INFO SugarCRM - Query Execution Time:0.00034900000000004
Wed Jul 18 12:07:54 2007,541 [30366] INFO SugarCRM - Query:
SELECT calls.id, name, reminder_time, description, date_start, time_start,
CONCAT( date_start, CONCAT(' ', time_start) ) AS dateTime
FROM calls LEFT JOIN calls_users ON calls.id = calls_users.call_id
WHERE calls_users.user_id ='1'
AND calls.reminder_time != -1
AND calls_users.deleted != 1
HAVING dateTime >= '2007-07-18 17:07:54' AND dateTime <= '2007-07-18 18:07:54'
Wed Jul 18 12:07:54 2007,541 [30366] INFO SugarCRM - Query Execution Time:0.00022500000000003
Wed Jul 18 12:07:54 2007,617 [30366] INFO SugarCRM - Query:SELECT placement,name,id,url from iframes WHERE deleted=0 AND status=1 AND (placement='tab' OR placement='all') AND (type='global' OR (type='personal' AND created_by='1')) ORDER BY iframes.name
Wed Jul 18 12:07:54 2007,617 [30366] INFO SugarCRM - Query Execution Time:0.000112
Wed Jul 18 12:07:54 2007,624 [30366] INFO SugarCRM - Query:SELECT tracker.* from tracker WHERE user_id='1' ORDER BY id DESC LIMIT 0,10
Wed Jul 18 12:07:54 2007,625 [30366] INFO SugarCRM - Query Execution Time:8.7000000000059E-05
Wed Jul 18 12:07:54 2007,712 [30366] INFO SugarCRM - Query:SELECT category, name, value FROM config
Wed Jul 18 12:07:54 2007,713 [30366] INFO SugarCRM - Query Execution Time:0.00012699999999999
Wed Jul 18 12:07:54 2007,754 [30366] INFO SugarCRM - Here is the where clause for the list view:
Wed Jul 18 12:07:54 2007,756 [30366] INFO SugarCRM - Query:SELECT id, first_name, last_name, user_name from users WHERE status='Active' AND is_group=0 ORDER BY user_name ASC
Wed Jul 18 12:07:54 2007,757 [30366] INFO SugarCRM - Query Execution Time:0.00011099999999997
Wed Jul 18 12:07:54 2007,790 [30366] INFO SugarCRM - Query: SELECT contacts.id , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.email1 , contacts.phone_work , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, contacts.assigned_user_id FROM contacts LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
AND accounts.deleted=0 LEFT JOIN users jt1 ON
jt1.id= contacts.assigned_user_id AND jt1.deleted=0
AND jt1.deleted=0 where contacts.deleted=0 LIMIT 0,21
Wed Jul 18 12:07:54 2007,790 [30366] INFO SugarCRM - Query Execution Time:0.00013799999999997
Wed Jul 18 12:07:54 2007,797 [30366] INFO SugarCRM - Query:SELECT id, first_name, last_name, user_name from users WHERE 1=1 AND is_group=0 ORDER BY user_name ASC
Wed Jul 18 12:07:54 2007,797 [30366] INFO SugarCRM - Query Execution Time:9.0999999999952E-05
Wed Jul 18 12:07:55 2007,010 [30366] INFO SugarCRM - Query:SELECT id, name FROM saved_search
WHERE
deleted = '0' AND
assigned_user_id = '1' AND
search_module = 'Contacts'
ORDER BY name
Wed Jul 18 12:07:55 2007,011 [30366] INFO SugarCRM - Query Execution Time:0.000118

[nathanfunk]

I still haven't been able to figure out the problem. If anybody has any idea what to try next, I would really appreciate it.

I have tried running the Diagnostic Tool but I get the following error:

Getting config.php...
Done

Getting custom dir...
Error: PCLZIP_ERR_READ_OPEN_FAIL (-2) : Unable to open archive 'cache/diagnostic/22bd76a5-a37e-c80d-1041-469eeade6179/diagnostic20070718-233610/custom_directory.zip' in wb mode

My hunch is still that this has something to do with the way PHP is creating files. I think they aren't created with the right permissions and then can not be written to.

Nathan

[andydreisch]

Hi Nathan, use the Admin->Repair area and try Rebuild Relationships. Just a hunch here and if it doesn't fix the problem outright it may yield more clues.

Andy

[nathanfunk]

Thanks for the reply Andy,

The Admin Repair Rebuild Relationships ran with no errors in the web page output, but the original problem is not solved.

I found two errors in the logs for the rebuild relationships action. Both look like this:

Thu Jul 19 14:19:53 2007,142 [3440] FATAL SugarCRM - createRelationshipMeta: Metadata for table tracker does not exist

Of course I have no clue what this means or how to fix it...

Thanks for any help,

Nathan

[nathanfunk]

Does anybody have recommendations on what to try next? I'm still stuck with this...

Thanks,

Nathan