LIMIT 10000 You have an error in your SQL syntax

[rmeredith]

Hi,

We are using OTRS 3.0.6 which was running on a windows box but due has been moved to Linux (CentOS). Everything works fine but I am getting a lot of errors in the system log.

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 st.ticket_lock_id IN (2) AND st.user_id IN (2) AND st.until_time <= 13007' at line 1, SQL: 'SELECT DISTINCT count(*) FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( ) AND st.ticket_lock_id IN (2) AND st.user_id IN (2) AND st.until_time <= 1300726300 LIMIT 10000'

Can anyone please help with these errors.

Thanks

Rich

[crythias]

http://forums.otrs.org/viewtopic.php?f=62&t=8189

[rmeredith]

Hi,

Thanks for your reply. I have read through the document you suggested but am still no closer to resolving the issue.

Thanks

Rich

[crythias]

The errors are coming from (I believe) a Generic Agent ...

[rmeredith]

Hi,

I have been looking into this for the last few days and I have disabled the cron job for the generic agents but still these errors are appearing. I have noticed that over night when the system is not used that these errors do not appear (even if the cron is running).

Rich

[crythias]

It also seems to be related to States that might have been disabled.

[rmeredith]

Thanks. I have made all the states valid but still I am getting the errors.

[ferrosti]

Please post your crontab for otrs user and your ticket_state table.
Do it like this:

Code: Select all

SELECT id, name, type_id, valid_id, change_by FROM `otrs`.`ticket_state`;

[rmeredith]

Thanks for your reply. Please find below details as requested:

id name type_id valid_id change_by
1 new 1 1 1
2 closed successful 3 1 1
3 closed unsuccessful 3 2 2
4 open 2 1 1
5 removed 6 2 2
6 pending reminder 4 2 2
7 pending auto close+ 5 2 2
8 pending auto close- 5 2 2
9 merged 7 1 1
10 Awaiting Site Visit 2 1 2
11 Awaiting Customer 2 1 2
12 Awaiting Parts 2 1 2
13 Awaiting Dial In 2 1 4
14 Awaiting 3rd Party Company 2 1 2
15 Awaiting Internal Response 2 1 2
16 Awaiting Call Back 2 1 2



[root@support ~]# crontab -u otrs -l
# --
# cron/aaa_base - base crontab package
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: aaa_base.dist,v 1.7 2009/02/16 20:35:25 tr Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# Who gets the cron emails?
MAILTO="root@localhost"
# --
# cron/cache - delete expired cache
# Copyright (C) 2001-2010 xxx, http://otrs.org/
# --
# $Id: cache.dist,v 1.2 2010/11/22 12:15:35 mg Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# delete expired cache weekly (Sunday mornings)
20 0 * * 0 $HOME/bin/otrs.DeleteCache.pl --expired >> /dev/null
30 0 * * 0 $HOME/bin/otrs.LoaderCache.pl -o delete >> /dev/null
# --
# cron/fetchmail - fetchmail cron of the OTRS
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: fetchmail.dist,v 1.10 2009/02/16 20:53:10 tr Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# fetch every 5 minutes emails via fetchmail
#*/5 * * * * [ -x /usr/bin/fetchmail ] && /usr/bin/fetchmail -a >> /dev/null
#*/5 * * * * /usr/bin/fetchmail -a --ssl >> /dev/null
# --
# cron/generic_agent - otrs.GenericAgent.pl cron of the OTRS
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: generic_agent.dist,v 1.13 2009/11/04 12:27:57 mn Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# start generic agent every 20 minutes
0,20,40 * * * * $HOME/bin/otrs.GenericAgent.pl >> /dev/null

# example to execute otrs.GenericAgent.pl on 23:00 with
# Kernel::Config::GenericAgentMove job file
#0 23 * * * $HOME/bin/otrs.GenericAgent.pl -c "Kernel::Config::GenericAgentMove" >> /dev/null
# --
# cron/generic_agent - otrs.GenericAgent.pl cron of the OTRS
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: generic_agent-database.dist,v 1.9 2009/11/04 12:27:57 mn Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# start generic agent every 10 minutes
0,10,20,30,40,50 * * * * $HOME/bin/otrs.GenericAgent.pl -c db >> /dev/null
# --
# cron/pending_jobs - pending_jobs cron of the OTRS
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: pending_jobs.dist,v 1.12 2009/11/04 12:27:57 mn Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# check every 120 min the pending jobs
45 */2 * * * $HOME/bin/otrs.PendingJobs.pl >> /dev/null
# --
# cron/postmaster - postmaster cron of the OTRS
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: postmaster.dist,v 1.9 2009/02/16 20:55:11 tr Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# check daily the spool directory of OTRS
#10 0 * * * * [ -e /etc/init.d/otrs ] && /etc/init.d/otrs cleanup >> /dev/null; [ -e /etc/rc.d/init.d/otrs ] && /etc/rc.d/init.d/otrs cleanup >> /dev/null
10 0 * * * $HOME/bin/otrs.cleanup >> /dev/null
# --
# cron/postmaster_mailbox - postmaster_mailbox cron of the OTRS
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: postmaster_mailbox.dist,v 1.3 2009/11/09 15:24:13 mn Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# fetch emails every 10 minutes
0,5,10,15,20,25,30,40,45,50,55 * * * * $HOME/bin/otrs.PostMasterMailbox.pl >> /dev/null
# --
# cron/rebuild_ticket_index - rebuild ticket index for OTRS
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: rebuild_ticket_index.dist,v 1.10 2009/11/09 15:24:13 mn Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# just every day
01 01 * * * $HOME/bin/otrs.RebuildTicketIndex.pl >> /dev/null
# --
# cron/session - delete old session ids of the OTRS
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: session.dist,v 1.12 2009/11/04 12:27:57 mn Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# delete every 120 minutes old/idle session ids
55 */2 * * * $HOME/bin/otrs.DeleteSessionIDs.pl --expired >> /dev/null
# --
# cron/unlock - unlock old locked ticket of the OTRS
# Copyright (C) 2001-2009 xxx, http://otrs.org/
# --
# $Id: unlock.dist,v 1.11 2009/11/04 12:27:57 mn Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

# unlock every hour old locked tickets
0,10,15,20,25,30,35,40,44,50,55 * * * * $HOME/bin/otrs.UnlockTickets.pl --timeout >> /dev/null
# @hourly $HOME/bin/otrs.RebuildFulltextIndex.pl

[ferrosti]

Your type_id of your 'awaiting' states should be the same like 'pending reminder'. But this is not the problem.

Also it is better to write '5/*' instead of '5,10,15,20, ...' for a job that should run every 5 minutes, since you forgot 35 for your PostMasterMailbox

Can you please have a look at your log file, whether this error occurs at 0,20,40 minutes of any hours? This is more important. I assume the error to come from the generic_agent, which then should be switched to DB

[rmeredith]

Thanks for your reply.

I have had a look in the log and this is happening more often than the times you mentioned. I have noticed that over night when the system is not being used there appears to be no errors!

I have attached a screen shot from the log.

Rich

[jojo]

as the error indicates in the logfile (OTRS_CGI) it is a webfrontend error. Do you use some "old" templates?

[rmeredith]

Thanks for your reply. Im not sure what you are reffering to re templates.

[jojo]

did you upgrade OTRS from a previous version? Do you made modifications on templates or module code?

[rmeredith]

The install used to be on a windows system but moved it to a linux for stability. I exported the sql and config and reimported into the new install. software versions where of the same release level.

[rmeredith]

Does anyone have any ideas to fix this please.

[crythias]

it's still a problem with states as it's trying to populate the dashboard.

It gets them from Viewable StateIDs

Which gets them from State types ...

Which means that if you've changed the name of the State Types relative to what Sysconfig is looking for for the dashboard, you'll have this error.

Or, in English Terms: If the dashboard is looking for state type of open and new and all your states belong to state types of foo and bar, and nothing to new and open, you'll get this error.

[rmeredith]

Thanks. I have not had much time to look at this before now as OTRS seemed to be working OK.

I have had a look at the states like you mentioned and they appear to be assigned to open or closed.

Please can you advise where else I should be looking.

Rich