sugarcrm v5.0.0 scheduler gives FATAL cron error 1242

[gazza73]

running Linux 2.6.24.4_0 Appliance distro with Sugar v5.0.0.b and ireports v3.0.0.b

Problem: scheduling the genration of a report as a PDF via email.

Works manually OK (ie: can Run Report).

Many articles on all this, regarding dd/mm/yyyy 24hr date formats, crontab setup, and the problem in <sugarcrm location>/cache/modules/Relationships/relationships.cache.php regarding the missing or misspelt item called: "schedules_created_by" versus "schedules_created_by_rel"

I've narrowed all that down to two remaining issues:
a) If I run the client browser sugarCRM as user: admin and go to menu: ADMIN and SCHEDULER, I end up with the following error appearing in the <sugarcrm location>sugarcrm.log:
FATAL SugarCRM - error fetching relationship from cache schedulers_created_by
http://www.sugarcrm.com/forums/showthread.php?t=20051 covers this issue as does:
http://www.sugarcrm.com/wiki/index.p...hip_from_cache
but as soon as ADMIN => SCHEDULER menu is touched, the problem returns.

OK - so let's assume that remains a background issue.....

b) If we perform the steps suggested for (a), we still have this error:
<sugarcrm location>sugarcrm.log
shows: FATAL SugarCRM - MySQL error 1242: Subquery returns more than 1 row.

The cron procedure sends an email with the following clues/errors, which I am having trouble diagnosing:

email SUBJECT:

Cron <apache@abssugar> cd /usr/share/sugarcrm; php5 -f cron.php 2>&1

email CONTECT:

PHP Warning: mysql_query(): Unable to save result set in /usr/share/sugarcrm/include/database/MysqlManager.php on line 151
Query Failed:select user_id, (select id FROM tracker t2 WHERE t2.user_id = t1.user_id AND visible = 1 ORDER BY id DESC LIMIT 14,1) minid FROM tracker t1 GROUP by user_id::MySQL error 1242: Subquery returns more than 1 row


NOTE: in relation to issue (a) the resultant email that arrives from cron, has two additional lines at the start of the email:

PHP Notice: Undefined offset: 6 in /usr/share/sugarcrm/include/TimeDate.php on line 308
PHP Notice: Undefined offset: 6 in /usr/share/sugarcrm/include/TimeDate.php on line 314


My thinking so far:
I've been looking into (b) with the TimeDate.php matter, and been playing with date and time formats as admin.
I've also been looking into (a) using ireports to generate a report based on the 'tracker' table to try and see what possible data content could be messing up the Sql Query that is failing.

I'm considering doing the upgrade to v5.2.0c - but that may open up a new set of issues. I've been able to add modules, write reports and do most other matters I desire in this 5.0.0b version, and at this stage are reluctant to do the upgrade just becuase of the above 2 issues.

I find no other information about error 1242 on google, other forums or this forum - and would appreciate anybody else's ideas on this.

Gaz

[gazza73]

When I delete all scheduled reports and also delete all the reports from Zucker Reports in sugarcrm browser session as user: admin, then stop/start the cron daemon, I no longer see either of the two types of error.

instead, the 60second repeating crontan job (cron.php executed by php5) lists the error:

FATAL SugarCRM - If you see a whole string of these, there is a chance someone is attacking your system

I would have thought the frequency of these errors would not be one-for-one at the time of crontab running the cron.php list..... but that seems to be what is happening.

So now to try loading just one new report, and scheduling it, using the date/time convention of dd/mm/yyyy 23:00 (24-hr time without any am/pm suffixes).....

I also noticed that the admin account has a single character user_id: 1 whereas other created users
of the trial system have large user_id: 12140a97-b195-6a2b-4eb8-49c7182054c9

I also messed around using ireport to try and revisit the logic that is occuring when we got the error message that this thread is trying to address in (a)

and I found if I varied the LIMIT value in ".........WHERE t2.user_id = t1.user_id AND visible = 1 ORDER BY id DESC LIMIT 14,1)....." from the failing "14" to something exceeding the number of values that the SqlQuery was finding, then the error disappeared.

I just don't know where this piece of code is located, that is causing the end result:
"MySQL error 1242: Subquery returns more than 1 row"

So ..... back to more testing......

Gaz

[crmsiva]

(select id FROM tracker t2 WHERE t2.user_id = t1.user_id AND visible = 1 ORDER BY id DESC LIMIT 14,1)

This query is retrieving more than one records. This is the cause of the problem (b)

FATAL SugarCRM - If you see a whole string of these, there is a chance someone is attacking your system

This message will be logged, only if cron.php file is accessed frequently(more than once in a minute i think).

[gazza73]

thankls for input Siva. Do you know which records are "too many". I'm only playing with test data, so I can afford to kill off some test-uers or transactions.... The 'tracker' table seems to be only dealing with a list of user_id's, and in my test situation, I only have the following users:

- admin (default administrator)
- gazzatest (me)
- betty (test staff name)

I've put one single report back into the game, and scheduled it, and I'm back to problem (b).

Gaz

[crmsiva]

Did you change anything in tracker table?
You can simply truncate the tracker table and test the problem.

[gazza73]

Siva.

I've made no changes to tracker table 'directly'
I've run ireport and prepared a simple report showing the data contents of the entire tracker table, and it seems to contain a number of entries for the reports I made, the custom records I made for a new module called DEC01 (a bit like opportunities content)....

I then made another ireport layout to run the offending (b) error Query line, and it yeilded only 3 records - being the id and user_id of the three potential login uisers of the database: admin, gazzatest and betty.
(I could see that if I changed the LIMIT value to a number that exceeds the number of actual rows that are in the 'tracker' table.

I've only hacked into one table: relationships.cache.php to attempt to remedy error (a)....


*** I'd need a clue from you on how to 'truncate tracker'..... perhaps there is a mysql> command-line that lets me add entries to data tables better than using a vi editor on relationships.cache.php (obviously I'm not editing the /srv/mysql/sugarcrm binary files (assuming that's needed ????)

I see many a forum topic talking about using a 'Database Manager' to enter data into a table...... but I don't know what tool that is. Obviously to add records to opportunities, accounts, leads and so forth, I usse the GUI front-end client: SugarOS in firefox. But how do you enter data into the hidden tables that are underlying the whole operation of the sugarcrm database?

forum: http://www.sugarcrm.com/wiki/index.p...hip_from_cache spoke of using a "database Manager" to solve (a).




Gaz

[crmsiva]

truncate table tracker;

Use this command to truncate tracker table

[gazza73]

what data will I lose (I know I'm only testing.......) but what data is actually in 'tracker'... and if I truncate it, what's the downside of that move? Or is tracker only some logging/audit unnecessary data content?

[crmsiva]

yes tracker table don't have very important content. It is just filled with tracking data. The last viewed items are obtained from tracker table.

[gazza73]

Okay thanks for that tip. So I've performed the truncate as:

#mysql
> /u sugarcrm
> truncate table tracker;

Query OK, 0 rows affected (0.00 sec)


So I'm not sure if that deleted all the records or not.... But I'll retry my cron/scheduling testing now.......

Gaz