New ticket SystemLog Error

[Lubomirsb]

Everytime when ticket is created SystemLog I get this Error

Code: Select all

Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	Column 'queue_id' cannot be null, SQL: 'INSERT INTO ticket_history (name, history_type_id, ticket_id, article_id, queue_id, owner_id, priority_id, state_id, type_id, valid_id, create_time, create_by, change_time, change_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	No such TicketID (593)!
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	No such TicketID (593)!
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	No such TicketID (593)!
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	No such TicketID (593)!
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	No such TicketID (593)!
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	Column 'owner_id' cannot be null, SQL: 'INSERT INTO ticket_history (name, history_type_id, ticket_id, article_id, queue_id, owner_id, priority_id, state_id, type_id, valid_id, create_time, create_by, change_time, change_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	No such TicketID (593)!
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	No such TicketID (593)!
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	No such TicketID (593)!
Tue Jan 7 14:15:03 2014	error	OTRS-CGI-10	No such TicketID (593)!

Any toughts ?
Btw this error leads to another. Sometimes tickets that exists are "gone". By gone i mean they are still there but in the dashboard they are empty and they cannot be opened.

[crythias]

This wouldn't happen in stock otrs.

Without knowing anything more, it means you've attempted to create a ticket without a valid queue or owner.

[Lubomirsb]

This wouldn't happen in stock otrs.

Without knowing anything more, it means you've attempted to create a ticket without a valid queue or owner.

The queue is valid and i can see the ticket owner. The database is filled too. There is only NULL values in ticket_hiestory -> article_id .

[crythias]

The queue is valid and i can see the ticket owner. The database is filled too. There is only NULL values in ticket_hiestory -> article_id .

The errors you posted say they can't insert NULL values for queue and owner. This means what it says. If you disagree because you can see it in the history for the same time stamp, I'm not sure what to say. It shouldn't give you an error if it works.

[Lubomirsb]

The queue is valid and i can see the ticket owner. The database is filled too. There is only NULL values in ticket_hiestory -> article_id .

The errors you posted say they can't insert NULL values for queue and owner. This means what it says. If you disagree because you can see it in the history for the same time stamp, I'm not sure what to say. It shouldn't give you an error if it works.

Sorry , I dont disagree. I know what is says but I am very confused of what could possibly caused this.

[reneeb]

Do you have any custom eventhandler that run on ticket creation or article create?

[Lubomirsb]

Do you have any custom eventhandler that run on ticket creation or article create?

I dont think I have.

[Lubomirsb]

Any ideas where i can look for? Maybe in the SQL ?
Now I remeber this started happening after i runned otrs.DeleteCache.pl ?

[Lubomirsb]

Now i know what causes the tickets to be empty. If there is a ticket from customer with ID lets say customer1000 but there is no company with id customer1000 the thicket will go empty. Any ideas on that ? I think that this causes and the other errors.

The last thins i did were :
Add a custom field in statistics.
Add a Custom dropdown in NewPhoneTicket
Run otrs.DeleteCache.pl

[Lubomirsb]

Do you have any custom eventhandler that run on ticket creation or article create?

Hm i foind this in Ticket.pm

Code: Select all

# db insert
    return if !$Self->{DBObject}->Do(
        SQL => 'INSERT INTO ticket_history '
            . ' (name, history_type_id, ticket_id, article_id, queue_id, owner_id, '
            . ' priority_id, state_id, type_id, valid_id, '
            . ' create_time, create_by, change_time, change_by) '
            . 'VALUES '
            . '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, current_timestamp, ?, current_timestamp, ?)',
        Bind => [
            \$Param{Name},    \$Param{HistoryTypeID}, \$Param{TicketID},     \$Param{ArticleID},
            \$Param{QueueID}, \$Param{OwnerID},       \$Param{PriorityID},   \$Param{StateID},
            \$Param{TypeID},  \$Param{ValidID},       \$Param{CreateUserID}, \$Param{CreateUserID},
        ],
    );

    # trigger event
    $Self->EventHandler(
        Event => 'HistoryAdd',
        Data  => {
            TicketID => $Param{TicketID},
        },
        UserID => $Param{CreateUserID},
    );

    return 1;
}

There is this event .

[Lubomirsb]

Finally i know where the problem is .

Code: Select all

sub TicketGet {
    my ( $Self, %Param ) = @_;

    # check needed stuff
    if ( !$Param{TicketID} ) {
        $Self->{LogObject}->Log( Priority => 'error', Message => 'Need TicketID!' );
        return;
    }
    $Param{Extended} ||= '';

    # Caching TicketGet() is a bit more complex than usual.
    #   The full function result will be cached in an in-memory cache to
    #       speed up subsequent operations in one request, but not on disk,
    #       because there are dependencies to other objects such as queue which cannot
    #       easily be tracked.
    #   The SQL for fetching ticket data will be cached on disk as well because this cache
    #       can easily be invalidated on ticket changes.

    # check cache
    my $FetchDynamicFields = $Param{DynamicFields} ? 1 : 0;

    my $CacheKey = 'Cache::GetTicket' . $Param{TicketID};

    # check if result is cached
    if ( $Self->{$CacheKey}->{ $Param{Extended} }->{$FetchDynamicFields} ) {
        return %{ $Self->{$CacheKey}->{ $Param{Extended} }->{$FetchDynamicFields} };
    }

    my %Ticket;

    my $Cached = $Self->{CacheInternalObject}->Get( Key => $CacheKey );

    if ( ref $Cached eq 'HASH' ) {
        %Ticket = %{$Cached};
    }
    else {

        return if !$Self->{DBObject}->Prepare(
            SQL => '
                SELECT st.id, st.queue_id, st.ticket_state_id, st.ticket_lock_id, st.ticket_priority_id,
                    st.create_time_unix, st.create_time, st.tn, st.customer_id, st.customer_user_id,
                    st.user_id, st.responsible_user_id, st.until_time, st.change_time, st.title,
                    st.escalation_update_time, st.timeout, st.type_id, st.service_id, st.sla_id,
                    st.escalation_response_time, st.escalation_solution_time, st.escalation_time, st.archive_flag,
                    st.create_by, st.change_by, 
	             customer_user.erp_version, customer_user.plc_version ,customer_user.trz_version ,customer_user.kantar_version,
		      customer_company.oracle_version , customer_company.oracle_password , customer_company.server_parameters , 
		      customer_company.connections , customer_company.windows_version
                FROM ticket st
	         JOIN customer_user ON customer_user.login = st.customer_user_id
		  JOIN customer_company ON customer_company.customer_id = st.customer_id
                WHERE st.id = ?',
            Bind  => [ \$Param{TicketID} ],
            Limit => 1,
        );

        while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
            $Ticket{TicketID}   = $Row[0];
            $Ticket{QueueID}    = $Row[1];
            $Ticket{StateID}    = $Row[2];
            $Ticket{LockID}     = $Row[3];
            $Ticket{PriorityID} = $Row[4];

            $Ticket{CreateTimeUnix} = $Row[5];
            $Ticket{TicketNumber}   = $Row[7];
            $Ticket{CustomerID}     = $Row[8];
            $Ticket{CustomerUserID} = $Row[9];

            $Ticket{OwnerID}             = $Row[10];
            $Ticket{ResponsibleID}       = $Row[11] || 1;
            $Ticket{RealTillTimeNotUsed} = $Row[12];
            $Ticket{Changed}             = $Row[13];
            $Ticket{Title}               = $Row[14];

            $Ticket{EscalationUpdateTime} = $Row[15];
            $Ticket{UnlockTimeout}        = $Row[16];
            $Ticket{TypeID}               = $Row[17] || 1;
            $Ticket{ServiceID}            = $Row[18] || '';
            $Ticket{SLAID}                = $Row[19] || '';

            $Ticket{EscalationResponseTime} = $Row[20];
            $Ticket{EscalationSolutionTime} = $Row[21];
            $Ticket{EscalationTime}         = $Row[22];
            $Ticket{ArchiveFlag}            = $Row[23] ? 'y' : 'n';

            $Ticket{CreateBy} = $Row[24];
            $Ticket{ChangeBy} = $Row[25];
	     $Ticket{ErpVersion} = $Row[26];
	     $Ticket{PlcVersion} = $Row[27];
	     $Ticket{TrzVersion} = $Row[28];  
	     $Ticket{KantarVersion} = $Row[29];

	     $Ticket{CustomerOracleVersion} = $Row[30];
	     $Ticket{OraclePassword} = $Row[31];
	     $Ticket{CustomerServerParameters} = $Row[32]; 
	     $Ticket{Connetctions} = $Row[33];
 	     $Ticket{WindowsVersion} = $Row[34];
        }

        $Self->{CacheInternalObject}->Set(
            Key   => $CacheKey,
            Value => \%Ticket,
        );
    }

    # check ticket
    if ( !$Ticket{TicketID} ) {
        if ( !$Param{Silent} ) {
            $Self->{LogObject}->Log(
                Priority => 'error',
                Message  => "No such TicketID ($Param{TicketID})!",
            );
        }
        return;
    }

I think that the error is in the sql , or the rows i added.