OTRS 5.0.1: Error on OTRS-otrs.Console.pl-Maint::Ticket::UnlockTimeout-10 and Notifications

[davecosta]

Hi Folks,

I've upgraded my system from OTRS 4.0.12 to 5.0.1.
Upgrade went fine, no issues.

Actually I get this error in my System Log:
There was an error executing Execute() in Kernel::System::Console::Command::Maint::Ticket::UnlockTimeout: [Wed Oct 28 08:35:20 2015] otrs.Daemon.pl: 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 ')

Can someone point me to the right solution?


Besides there's another problem with Notifications.
My previous notifications has been transformed into the new notification engine.
But the TicketCreateNotification doesn't work anymore.

Any help would be appreciated.

Regards,

Dave

[davecosta]

Hi, just to let you know.

The NOTIFICATION issue is solved.
As you may know OTRS 5 has a new notification system: the upgrading Job will create new notification tasks and set an "Not Valid" the old ones.
For some strange causes the new ones were not working.
I've enabled the old ones and, at that time the new ones started working!
At that time I've then deactivated the old ones, and the new ones kept working.
Strange but this Is how it went!


The oter issue still present:

Actually I get this error in my System Log:
There was an error executing Execute() in Kernel::System::Console::Command::Maint::Ticket::UnlockTimeout: [Wed Oct 28 08:35:20 2015] otrs.Daemon.pl: 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 ')

Can someone point me to the right solution?

Thank's

Dave

[davecosta]

Hi all,

no ideas?

This appears in the System Log:

There was an error executing Execute() in Kernel::System::Console::Command::Maint::Ticket::UnlockTimeout: [Tue Nov 3 11:35:01 2015] otrs.Daemon.pl: 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 ')

Thank you

Dave

[davecosta]

Hi everyone,

one year has passed since my last post in this thread but still no solution.
I keep on experiencing the error:

There was an error executing Execute() in Kernel::System::Console::Command::Maint::Ticket::UnlockTimeout: [Thu Nov 3 19:35:00 2016] otrs.Daemon.pl: 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 ')

Any help would be appreciated.

Regards,

Dave

[davecosta]

Hi,

I've done a bit of deep investigating during the night and I feel link I'm not far from the solution.

The problem seems to be generated by this query:

Code: Select all

SELECT st.tn, st.id, st.timeout, sq.unlock_timeout
            FROM ticket st, queue sq
            WHERE st.queue_id = sq.id
                AND sq.unlock_timeout != 0
                AND st.ticket_state_id IN ( ${\(join ', ', @UnlockStateIDs)} )
                AND st.ticket_lock_id NOT IN ( ${\(join ', ', @ViewableLockIDs)} ) ",

The query is located in file

Code: Select all

/opt/otrs/Kernel/System/Console/Command/Maint/Ticket/UnlockTimeout.pm

From the same file

Code: Select all

my @UnlockStateIDs = $Kernel::OM->Get('Kernel::System::State')->StateGetStatesByType(
        Type   => 'Unlock',
        Result => 'ID',
    );
my @ViewableLockIDs = $Kernel::OM->Get('Kernel::System::Lock')->LockViewableLock( Type => 'ID' );

From the log file I get the complete error:

Code: Select all

[Fri Nov  4 10:35:18 2016][Error][Kernel::System::Console::Command::Maint::Ticket::UnlockTimeout::Run][46] 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 NOT IN ( 1, 3 )' at line 5, SQL: '
            SELECT st.tn, st.id, st.timeout, sq.unlock_timeout, st.sla_id, st.queue_id
            FROM ticket st, queue sq
            WHERE st.queue_id = sq.id
                AND sq.unlock_timeout != 0
                AND st.ticket_state_id IN (  )
                AND st.ticket_lock_id NOT IN ( 1, 3 ) '
[Fri Nov  4 10:35:18 2016][Error][Kernel::System::Daemon::DaemonModules::BaseTaskWorker::_HandleError][51] There was an error executing Execute() in Kernel::System::Console::Command::Maint::Ticket::UnlockTimeout: [Fri Nov  4 10:35:18 2016] otrs.Daemon.pl: 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 ')
[Fri Nov  4 10:35:18 2016] otrs.Daemon.pl:                 AND st.ticket_lock_id NOT IN ( 1, 3 )' at line 5 at /opt/otrs/Kernel/System/DB.pm line 666.
ERROR: OTRS-otrs.Console.pl-Maint::Ticket::UnlockTimeout-10 Perl: 5.14.2 OS: linux Time: Fri Nov  4 10:35:18 2016

 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.ticket_lock_id NOT IN ( 1, 3 )' at line 5, SQL: '
            SELECT st.tn, st.id, st.timeout, sq.unlock_timeout, st.sla_id, st.queue_id
            FROM ticket st, queue sq
            WHERE st.queue_id = sq.id
                AND sq.unlock_timeout != 0
                AND st.ticket_state_id IN (  )
                AND st.ticket_lock_id NOT IN ( 1, 3 ) '

 Traceback (10521): 
   Module: Kernel::System::Console::Command::Maint::Ticket::UnlockTimeout::Run Line: 46
   Module: (eval) Line: 444
   Module: Kernel::System::Console::BaseCommand::Execute Line: 438
   Module: (eval) Line: 147
   Module: Kernel::System::Daemon::DaemonModules::SchedulerTaskWorker::Cron::Run Line: 131
   Module: Kernel::System::Daemon::DaemonModules::SchedulerTaskWorker::Run Line: 259
   Module: (eval) Line: 316
   Module: main::Start Line: 316
   Module: /opt/otrs/bin/otrs.Daemon.pl Line: 138

and this is the query finally executed:

Code: Select all

'            SELECT st.tn, st.id, st.timeout, sq.unlock_timeout, st.sla_id, st.queue_id
FROM ticket st, queue sq
WHERE st.queue_id = sq.id
AND sq.unlock_timeout != 0 
AND st.ticket_state_id IN (  )
AND st.ticket_lock_id NOT IN ( 1, 3 ) '

I really can't understand what's going wrong.
The query syntax seems correct, a bit strange those spaces before the SELECT word.

I wanted to run the Unlock script by hand but finally can't figure out where I can do that.
It seems that in the past versions (4.x) there was a

Code: Select all

root@otrs:/opt/otrs# bin/otrs.UnlockTickets.pl --all

command which I cound not find in my 5.0.2 installation.

My /opt/otrs/bin contains these files:

Code: Select all

drwxrwsr-x 2 otrs www-data 4,0K set 13 21:03 cgi-bin
-rwxrwx--- 1 otrs www-data 3,4K gen 14  2016 Cron.sh
drwxrwsr-x 2 otrs www-data 4,0K set 13 21:03 fcgi-bin
-rwxrwx--- 1 otrs www-data  22K feb 10  2016 otrs.CheckModules.pl
-rwxrwx--- 1 otrs www-data 4,3K feb 10  2016 otrs.CheckSum.pl
-rwxrwx--- 1 otrs www-data 1,3K feb 10  2016 otrs.Console.pl
-rwxrwx--- 1 otrs www-data  18K feb 10  2016 otrs.Daemon.pl
-rwxrwx--- 1 otrs www-data 2,6K feb 10  2016 otrs.PostMaster.pl
-rwxrwx--- 1 otrs www-data 8,1K feb 10  2016 otrs.SetPermissions.pl

How can I execute the Unlock script by Hand?
This would really be an help.

Thank you, regards

Dave

[coolmf]

How can I execute the Unlock script by Hand?

That would be:

Code: Select all

perl bin/otrs.Console.pl Maint::Ticket::UnlockAll

Run:

Code: Select all

perl bin/otrs.Console.pl

to see all your options

[davecosta]

Hi all,

Thank you coolmf, with your suggestion I was able to manually run the script.

At last I've solved my issue modifying the executed script /opt/otrs/Kernel/System/Console/Command/Maint/Ticket/UnlockTimeout.pm

Here the detail:

Code: Select all

     $Kernel::OM->Get('Kernel::System::DB')->Prepare(
        SQL => "SELECT st.tn, st.id, st.timeout, sq.unlock_timeout, st.sla_id, st.queue_id
            FROM ticket st, queue sq
            WHERE st.queue_id = sq.id
                AND sq.unlock_timeout != 0
               # AND st.ticket_state_id IN ( ${\(join ', ', @UnlockStateIDs)} )
                AND st.ticket_lock_id NOT IN ( ${\(join ', ', @ViewableLockIDs)} )",

As you see I've commented the

Code: Select all

# AND st.ticket_state_id IN ( ${\(join ', ', @UnlockStateIDs)} )

line.

Not a neat solution but working.

Thank you very much to all contributors.

Feel free to ask if you are experiencing the same issue.

Regards,

Dave