database entries aren't displayed in contacts, leads, accounts list

[Kallisti]

Hi,
I am having a problem. When i click on any category that I know has data entries in it, such as Contacts, Leads, Accounts, nothing is displayed in the list. In fact, it displays (0 - 0 of 0) at the top of the list. I know that the data is there because I still have a few contact and lead links in my history bar that I can click on to view. When I search for an entry that I know is there, nothing shows up either. I have used mysql to check the databases to assure the data is there.

I had this problem before but I thought it occurred because I was playing around with custom fields, labels, etc. Since I didn't have any useful data yet, I simply cleared the database, deleted sugar, then re-installed sugar completely and i am now having the same problem with the fresh install. I am tracing the code right now to see if I can find the problem but if anyone has any advice, I would greatly appreciate it! I can't seem to find any posts that list similar problems.

My system:
Sugar OpenSource Version 4.5.1e (Build 1049)
PHP Version 4.4.6
MySql Version 4.1.22
Apache 1.3.37

Here is a screen shot:

[andydreisch]

Hi Kallisti, the best bet would be to trap the SQL statement issued by the app in response to the search and analyze it. You can enable logging of queries via Admin -> System Settings. In the Advanced are, check Log Slow Queries and for the 'Slow query time threshold' field, specify 0.

The log is written to your root installation directory.

A few questions:

** After pressing Clear on the search screen, are no records displayed?
** Does this occurr throughout? In Opps? Bugs? Cases?
** (Doubtful) Are the records deleted? (The record should not be on the tracker bar if so, but I thought I'd ask.)

HTH,

Andy

[Kallisti]

** After pressing Clear on the search screen, are no records displayed?

No records are displayed

** Does this occurr throughout? In Opps? Bugs? Cases?

Since it is a new install, I do not have much data in the databases. The only entries I have so far are Contact, Lead, and Account. Since I tried to see if a fresh install would fix this problem and it didn't, I am attempting to fix it before I continue populating the db

** (Doubtful) Are the records deleted? (The record should not be on the tracker bar if so, but I thought I'd ask.)

The records still exist. I just checked in MySql to make sure

I just added another user to make sure that this issue isn't a database-access issue. I was pretty sure it wasn't since I could log in but I wanted to see what happened. The new user was added without error and the user list displays correctly.

Here are some errors in the log (there is about 120 lines of errors generated from one or 2 clicks on the interface, all with the same FATAL error):

Thu Sep 20 12:37:22 2007,680 [6508] FATAL SugarCRM - Slow Query (time:0.000236
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
Thu Sep 20 12:37:22 2007,694 [6508] FATAL SugarCRM - Slow Query (time:0.000234
SELECT tracker.* from tracker WHERE user_id='1' ORDER BY id DESC LIMIT 0,10
Thu Sep 20 12:37:22 2007,803 [6508] FATAL SugarCRM - Slow Query (time:0.000419
SELECT category, name, value FROM config
Thu Sep 20 12:37:22 2007,874 [6508] FATAL SugarCRM - Slow Query (time:0.000298
SELECT id, first_name, last_name, user_name from users WHERE status='Active' AND is_group=0  ORDER BY user_name ASC
Thu Sep 20 12:37:22 2007,928 [6508] FATAL SugarCRM - Slow Query (time:0.000361
 SELECT  leads.id ,  as name, leads.first_name , leads.last_name , leads.salutation , leads.status , leads.account_name , leads.email1  , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, leads.assigned_user_id  FROM leads   LEFT JOIN  users jt0 ON
jt0.id= leads.assigned_user_id AND jt0.deleted=0
 AND jt0.deleted=0 where leads.deleted=0 LIMIT 0,21

I also selected the 'Display stack trace of errors:' option and received the following errors for each category of the list.

Notice: Undefined index: sortable occured in /<directory>/sugarcrm/cache/smarty/templates_c/%%78^786^7862BF8C%%ListViewGeneric.tpl.php on line 120 [2007-09-20 12:59:41]

Notice: Undefined index: orderBy occured in /<directory>/sugarcrm/cache/smarty/templates_c/%%78^786^7862BF8C%%ListViewGeneric.tpl.php on line 124 [2007-09-20 12:59:41]

Thank you for your tips so far, I will take what I have and see if I can find the problem.

[andydreisch]

Hi Kallisti, the last query looks a bit strange to me.

Can you use the Admin -> Rebuild options ... let's try Rebuild Relationships and Rebuild Database. One at a time.

Andy

[Kallisti]

I tried 'Rebuild Relationship' option but I don't have 'Rebuild Databases' I do have 'Repair Database' which I tried. No results so far.
I also tried to copy one of the queries that returns a FATAL error in the sugarcrm.log file straight to the MySql prompt and it seemed to work just fine, I got results to the query and no errors.

I ran the diagnostic tool and I am combing through the output right now to see if I can find anything useful.

Thanks for your help so far!

[Kallisti]

It looks like the following query is the problem:

SELECT leads.id , as name, leads.first_name , leads.last_name , leads.salutation , leads.status , leads.account_name , leads.email1 , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, leads.assigned_user_id FROM leads LEFT JOIN users jt0 ON
jt0.id= leads.assigned_user_id AND jt0.deleted=0
AND jt0.deleted=0 where leads.deleted=0 LIMIT 0,21

I've put the 'as name' part in bold because that is where mysql will throw an error. I'm trying to trace how and where this query is built so that i can track down the problem. Any clues would be helpful!

Thanks

[Kallisti]

Anyone have any helpful advice? I am trying to figure out where this query is built within the code:
SELECT leads.id , as name, leads.first_name , leads.last_name , leads.salutation , leads.status , leads.account_name , leads.email1 , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, leads.assigned_user_id FROM leads LEFT JOIN users jt0 ON
jt0.id= leads.assigned_user_id AND jt0.deleted=0
AND jt0.deleted=0 where leads.deleted=0 LIMIT 0,21

I'm trying to figure out why it puts 'as name' in the query as this is what throws the error and i believe is what causes the leads to not be listed....

Any help would be great

Thanks,
~Rebecca

[andydreisch]

Hi Kallisti, yes, the "as name" portion of the query threw me off. I should have been clearer that this was what I noticed. Also, the FATAL message precedes the SQL statement that may in fact be OK; it's just that the statement exceeded the alloted time (0 msec).

Overall, it sure sounds like something is lingering from when you ...

"... I simply cleared the database, deleted sugar, then re-installed sugar completely and i am now having the same problem with the fresh install."

The "as name" is where the first and last names are concatenated and this is driven by definitions in vardef. Do you recall modifying anything in Studio or vardefs re: the Lead first/last names?

Andy