[SOLVED] No ticket data found

[brensly]

Hi!

This morning when i logged in to our OTRS, there were no tickets on the dashboard. OK, weird, i thought and looked at the queue view where I could see the number of tickets for each queue but it couldn't display any ticket data ("No ticket data found").

I did a complete restore from last Friday when it was working flawlessly, still no ticket data. I checked the database and it looks fine.

Apparently there are SQL syntax errors, how could they start showing up out of nowhere?

Here are some lines from the Apache error.log:

Code: Select all

[Mon Jul  9 10:03:06 2012] -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 'INNER JOIN users ru ON st.responsible_user_id = ru.id  WHERE 1=1 AND st.ticket_l' at line 1 at /opt/otrs//Kernel/System/DB.pm line 618.
ERROR: OTRS-CGI-44 Perl: 5.14.2 OS: linux Time: Mon Jul  9 10:03:06 2012

 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 'INNER JOIN users ru ON st.responsible_user_id = ru.id  WHERE 1=1 AND st.ticket_l' at line 1, SQL: 'SELECT COUNT(DISTINCT(st.id))INNER JOIN users ru ON st.responsible_user_id = ru.id  WHERE 1=1 AND st.ticket_lock_id IN (2,3) AND st.user_id IN (3) AND tf1.ticket_key = 'Seen' AND tf1.ticket_value = '1' AND tf1.create_by = 3 LIMIT 10000'

 Traceback (19136):
   Module: Kernel::System::TicketSearch::TicketSearch (v1.13.2.1) Line: 1643
   Module: Kernel::Output::HTML::ToolBarTicketLocked::Run (v1.9) Line: 53
   Module: Kernel::Output::HTML::Layout::Header (v1.381) Line: 1434
   Module: Kernel::Modules::AgentDashboard::Run (v1.29) Line: 429
   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 Jul  9 10:03:06 2012] -e: Use of uninitialized value $CountNew in subtraction (-) at /opt/otrs//Kernel/Output/HTML/ToolBarTicketLocked.pm line 64.
[Mon Jul  9 10:03:06 2012] -e: Use of uninitialized value $Count in subtraction (-) at /opt/otrs//Kernel/Output/HTML/ToolBarTicketLocked.pm line 64.
[Mon Jul  9 10:03:06 2012] -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 'INNER JOIN users ru ON st.responsible_user_id = ru.id  WHERE 1=1 AND st.ticket_s' at line 1 at /opt/otrs//Kernel/System/DB.pm line 618.
ERROR: OTRS-CGI-44 Perl: 5.14.2 OS: linux Time: Mon Jul  9 10:03:06 2012

 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 'INNER JOIN users ru ON st.responsible_user_id = ru.id  WHERE 1=1 AND st.ticket_s' at line 1, SQL: 'SELECT COUNT(DISTINCT(st.id))INNER JOIN users ru ON st.responsible_user_id = ru.id  WHERE 1=1 AND st.ticket_state_id IN ( 6 )  AND st.ticket_lock_id IN (2,3) AND st.user_id IN (3) AND st.ticket_state_id IN (6, 7, 8)  AND st.until_time <= 1341820926 LIMIT 10000'

 Traceback (19136):
   Module: Kernel::System::TicketSearch::TicketSearch (v1.13.2.1) Line: 1643
   Module: Kernel::Output::HTML::ToolBarTicketLocked::Run (v1.9) Line: 65
   Module: Kernel::Output::HTML::Layout::Header (v1.381) Line: 1434
   Module: Kernel::Modules::AgentDashboard::Run (v1.29) Line: 429
   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

Any clue on what's going on here?

We run OTRS 3.1.7 (also tried a restore from a couple of weeks ago when we ran 3.1.4, still the same problem) on Ubuntu 12.04 server.

Thanks in advance!


UPDATE: Just received a ticket notification, so apparently the database isn't corrupt. I clicked the link and could see the ticket and all. No ticket lists are showing up though.

[crythias]

There is no "FROM" in the sql query.
Why that is? I don't know, but check that TicketSearch.pm matches the current version and that you don't have, for instance, one in a Custom directory competing for it.
Kernel/System/TicketSearch.pm

Code: Select all

433	    my $SQLSelect;
434	    if ( $Result eq 'COUNT' ) {
435	        $SQLSelect = 'SELECT COUNT(DISTINCT(st.id))';
436	    }
437	    else {
438	        $SQLSelect = 'SELECT DISTINCT st.id, st.tn';
439	    }
440	
441	    my $SQLFrom = ' FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id ';

and then ...

Code: Select all

1636	    # return COUNT
1637	    if ( $Result eq 'COUNT' ) {
1638	        if ($CacheObject) {
1639	            $CacheObject->Set(
1640	                Type  => 'TicketSearch',
1641	                Key   => $SQLSelect . $SQLFrom . $SQLExt . $Result . $Limit,
1642	                Value => $Count,
1643	                TTL   => $Param{CacheTTL} || 60 * 4,
1644	            );
1645	        }
1646	        return $Count;
1647	    }

check otrs/Custom/Kernel/System/ for competing. Otherwise, I'm not sure where the problem lies. $SQLFrom definitely is unambiguously set and all further references have data appended.

[brensly]

Thank you, that explained it all. A couple of weeks ago I did create a custom TicketSearch.pm to add some columns, however I did not see any changes at all and decided to put it aside for a while.

What i didn't realize was that the Custom TicketSearch.pm won't be used until you restart the apache service. This weekend the apache service for some reason did a graceful restart, causing the custom TicketSearch.pm to be active.

Problem solved.

Thank you very much crythias, you saved me a lot of time.
I could have gone on for many, many hours before finding the cause myself.

Respect!