If a user clicks on "View Tickets by Queue", he sees in his browser:
OK
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.
The configuration of the gentoo server running the OTRS 3.1.10 hasn't changed in nearly a year. The only thing that has changed are tickets. For a while I blamed it on a recently created ticket with arabic words in it, but after deleting the arabic lines in the ticket that I blamed and deleting its attachments, the problem persists.
The Logfile reports a Syntax error when trying to translate the lock types 'unlock', 'tmp_lock', 'lock' into the corresponding lock-id's 0,1,2 because the word 'lock' is left out of the SQL statement and the trailing comma breaks the SQL syntax.
Does anyone have some pointers for me what might have broken the 'View tickets by queue' ?
Thank you in advance,
Marc.
Here's the log:
...
Module: Kernel::Modules::AgentTicketQueue::Run (v1.79) Line: 242
Module: Kernel::System::Web::InterfaceAgent::Run (v1.64) Line: 868
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 46
Module: (eval) (v1.90) Line: 204
Module: ModPerl::RegistryCooker::run (v1.90) Line: 204
Module: ModPerl::RegistryCooker::default_handler (v1.90) Line: 170
Module: ModPerl::Registry::handler (v1.99) Line: 31
[Mon Oct 21 13:54:23 2013] -e: DBD::mysql::st execute failed: 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 ')
[Mon Oct 21 13:54:23 2013] -e: AND st.queue_id = sq.id
[Mon Oct 21 13:54:23 2013] -e: AND st.archive_flag = ' at line 4 at /opt/otrs//Kernel/System/DB.pm line 618.
ERROR: OTRS-CGI-10 Perl: 5.12.4 OS: linux Time: Mon Oct 21 13:54:23 2013
Message: 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.queue_id = sq.id
AND st.archive_flag = ' at line 4, SQL: '
SELECT count(*)
FROM ticket st, queue sq, personal_queues suq
WHERE st.ticket_state_id IN ( 1, 4, 6, 7, 8 )
AND st.ticket_lock_id IN ( )
AND st.queue_id = sq.id
AND st.archive_flag = 0
AND suq.queue_id = st.queue_id
AND sq.group_id IN ( 1, 4, 8 )
AND suq.user_id = 22'
Traceback (28233):
Module: Kernel::System::Ticket::IndexAccelerator::RuntimeDB::TicketAcceleratorIndex (v1.76.2.1) Line: 120
Module: Kernel::Modules::AgentTicketQueue::BuildQueueView (v1.79) Line: 285
Module: Kernel::Modules::AgentTicketQueue::Run (v1.79) Line: 242
Module: Kernel::System::Web::InterfaceAgent::Run (v1.64) Line: 868
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 46
Module: (eval) (v1.90) Line: 204
Module: ModPerl::RegistryCooker::run (v1.90) Line: 204
Module: ModPerl::RegistryCooker::default_handler (v1.90) Line: 170
Module: ModPerl::Registry::handler (v1.99) Line: 31
[Mon Oct 21 14:59:56 2013] -e: DBD::mysql::st execute failed: 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 val
id_id IN ( 1 )' at line 1 at /opt/otrs//Kernel/System/DB.pm line 618.
ERROR: OTRS-CGI-10 Perl: 5.12.4 OS: linux Time: Mon Oct 21 14:59:56 2013
Message: 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 valid_id IN ( 1 )' at line 1, SQL: 'SELECT id, name FRO
M ticket_lock_type WHERE name IN ( 'unlock', 'tmp_lock', ) AND valid_id IN ( 1 )'
Traceback (29864):
Module: Kernel::System::Lock::LockViewableLock (v1.35.2.1) Line: 147
Module: Kernel::Modules::AgentTicketQueue::Run (v1.79) Line: 122
Module: Kernel::System::Web::InterfaceAgent::Run (v1.64) Line: 868
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 46
Module: (eval) (v1.90) Line: 204
Module: ModPerl::RegistryCooker::run (v1.90) Line: 204
Module: ModPerl::RegistryCooker::default_handler (v1.90) Line: 170
Module: ModPerl::Registry::handler (v1.99) Line: 31
ERROR: OTRS-CGI-10 Perl: 5.12.4 OS: linux Time: Mon Oct 21 14:59:56 2013
Message: The given param 'LockIDs' is invalid or an empty array reference!
Traceback (29864):
Module: Kernel::System::TicketSearch::TicketSearch (v1.13.2.1) Line: 325
Module: Kernel::Modules::AgentTicketQueue::Run (v1.79) Line: 194
Module: Kernel::System::Web::InterfaceAgent::Run (v1.64) Line: 868
View by Queue results in Internal Error message
Moderator: crythias
Re: View by Queue results in Internal Error message
Answer and Solution:
in the admin area of OTRS -> Sysconfig - > Search for "ViewableLocks" -> Search will have a hit in "tickets", which has 56 elements. It will not show you the one element that you are looking for but all 56.
But here you can find the element "Ticket::ViewableLocks", which in my case had 3 entries:
'unlock'
'tmp_lock'
plus an empty one !
In the empty field I entered 'lock' and then it was working again!
No idea how the empty field came to be but its fixed, that is what matters!
in the admin area of OTRS -> Sysconfig - > Search for "ViewableLocks" -> Search will have a hit in "tickets", which has 56 elements. It will not show you the one element that you are looking for but all 56.
But here you can find the element "Ticket::ViewableLocks", which in my case had 3 entries:
'unlock'
'tmp_lock'
plus an empty one !
In the empty field I entered 'lock' and then it was working again!
No idea how the empty field came to be but its fixed, that is what matters!