1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

  2. The forum you are viewing relates to Kayako Classic. If you signed up or upgraded to the new Kayako (after the 4th July 2016), the information in this thread may not apply to you. You can visit the forums for the new Kayako here.

Upgrade problems since 4.73

Discussion in 'Installation and setup (Kayako Classic Download)' started by sby, Mar 11, 2016.

  1. sby

    sby Established Member

    Hi,

    I have yesterday upgrade and now when i want to see history mails from users then i see a header and blank page.

    I get the follow error.

    Undefined index: _executeSegment (./__swift/cache/22106ae9f1b368a4085534ab33d7469d.php:14)

    Staff/Tickets/Ticket/View/136806
    Undefined index: _staffEmail (./__swift/cache/22106ae9f1b368a4085534ab33d7469d.php:15)

    How can i fix this issue.
     
  2. Gary McGrath

    Gary McGrath Staff Member

    Hi Sby,

    We have released a fix for the issue you have reported, please download Kayako 4.73.2

    Gary
     
  3. sby

    sby Established Member

    Thanks Gary i have download and the fix works.
     
  4. deyura

    deyura New Member

    Hello.
    After update 4.64 - 4.73.2 have trouble with fast search (above tikets). It's don't work.
    Try to install 4.73.2 on clear DB, same.

    Advanced search work perfect on both cases.
    MySQL 5.7, php 5.4.45

    And you have error in DB structure:
    ticketposts.contents LongText
    but
    searchindex.ft is Text
    so impossible to Reduild index in Admin Panel
    and will fail recive big email
     
  5. Gary McGrath

    Gary McGrath Staff Member

    Hi there,

    Can you submit a ticket or start a live chat at support.kayako.com as we might need to take a look at your DB, it sounds like maybe your swsearchindex might need to be rebuilt

    Gary
     
  6. deyura

    deyura New Member

    Gary McGrath, Hello.
    can't start live chat because in corporate network.

    1 case:
    update 4.64 - 4.73.2, table searchindex was chanced to InnoDB automatically, then try rebuild index using AdminCP, have error, change searchindex.ft to LognText, rebuild succesfull, fast search not work. advanced search well.

    then I think "maybe trouble in my DB and try 2 case"

    2 case "clear install":
    clear install on empty DB, create 2 tickets, try search - fast search not work. - so on clear installation rebuild not need
    in clear install "ticketposts.contents LongText but searchindex.ft is Text" - so I report on forum.
     
  7. Gary McGrath

    Gary McGrath Staff Member

    Hi there,

    If you cannot start a live chat, I would recommend you use the submit a request option to open a case with us. I think we will still need to login to see what is happening in your case, the error you got while trying to rebuild the index would need to be investigated, simply altering the field type will likely not work.

    Gary
     
  8. deyura

    deyura New Member

    altering the field type help for rebuilding index.
    but not fix fast search.

    try to install 4.73.2 on your server, and you can see mismatch column type.
    ther try to create tickets and search. on you server it work fine with version 4.73.2 cleat install?
     
  9. Gary McGrath

    Gary McGrath Staff Member

    Hi there,

    Yes it is working for me, this is why we need to investigate your install.

    Gary
     
  10. deyura

    deyura New Member

    Spent some time and big amount of knowledge.

    Enable MySQL query log to debug and progile Kayako queries
    Code:
    SET global general_log =1;
    than can able to view where failing was
    Code:
    UPDATE registry SET data = '{\"1\":{\"ticketmanagegrid\":{\"offset\":0,\"searchquery\":\"SEARCHWORD\",\"searchstoreid\":3}}}', datasize = '81', dateline = '1458302915' WHERE vkey = 'gridcache'
    ok

    Code:
    SELECT COUNT(1) AS totalitems FROM tickets AS tickets
                LEFT JOIN ticketposts AS ticketposts ON (tickets.ticketid = ticketposts.ticketid)
                WHERE ((MATCH(tickets.subject ) AGAINST('SEARCHWORD' IN BOOLEAN MODE)) OR (MATCH(ticketposts.contents ) AGAINST('SEARCHWORD' IN BOOLEAN MODE)))
                AND tickets.departmentid IN ('1','0') AND tickets.ticketstatusid != '-1' AND tickets.ownerstaffid != '-1' LIMIT 0,1
    MySQL error - #1191 - Can't find FULLTEXT index matching the column list

    to fix it use
    Code:
    ALTER TABLE `tickets` ADD FULLTEXT(`subject`);
    ALTER TABLE `ticketposts` ADD FULLTEXT(`contents`);
    then second search subquery
    Code:
    SELECT tickets.* FROM tickets AS tickets
                LEFT JOIN ticketposts AS ticketposts ON (tickets.ticketid = ticketposts.ticketid)
                LEFT JOIN ticketlocks AS ticketlocks ON (tickets.ticketid = ticketlocks.ticketid)
                LEFT JOIN ticketnotes AS ticketnotes ON (tickets.ticketid = ticketnotes.linktypeid)
                LEFT JOIN ticketwatchers AS ticketwatchers ON (tickets.ticketid = ticketwatchers.ticketid AND ticketwatchers.staffid = '1')
                LEFT JOIN users AS users ON (tickets.userid = users.userid)
                LEFT JOIN usergroups AS usergroups ON (users.usergroupid = usergroups.usergroupid)
                LEFT JOIN userorganizations AS userorganizations ON (users.userorganizationid = userorganizations.userorganizationid)
                LEFT JOIN ticketpriorities AS ticketpriorities ON (tickets.priorityid = ticketpriorities.priorityid)
                LEFT JOIN ticketstatus AS ticketstatus ON (tickets.ticketstatusid = ticketstatus.ticketstatusid)
                WHERE ((MATCH(tickets.subject ) AGAINST('SEARCHWORD' IN BOOLEAN MODE)) OR (MATCH(ticketposts.contents ) AGAINST('SEARCHWORD' IN BOOLEAN MODE))
                OR (MATCH(ticketnotes.note ) AGAINST('SEARCHWORD' IN BOOLEAN MODE)))
                AND tickets.departmentid IN ('1','0') AND tickets.ticketstatusid != '-1' AND tickets.ownerstaffid != '-1' ORDER BY tickets.lastactivity desc LIMIT 0,20
    MySQL error - #1191 - Can't find FULLTEXT index matching the column list

    to fix it use
    Code:
    ALTER TABLE `ticketnotes` ADD FULLTEXT(`note`);
    so, after this fixes below fast search start work
    Code:
    ALTER TABLE `tickets` ADD FULLTEXT(`subject`);
    ALTER TABLE `ticketposts` ADD FULLTEXT(`contents`);
    ALTER TABLE `ticketnotes` ADD FULLTEXT(`note`);

    when try to rebuild tickets search DB in AdminCP have error ...
    to fix
    Code:
    ALTER TABLE `searchindex` CHANGE `ft` `ft` LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
    and I think should add, but not sure
    Code:
    ALTER TABLE `searchindex` ADD FULLTEXT(`ft`);
    ... MySQL 5.7 on CentOS with default configuration


    Code:
    SET global general_log =0;
     
  11. deyura

    deyura New Member

    now I'm thinking how to update our production DB, because we have 100000post in long text
    and when try
    Code:
    ALTER TABLE `ticketposts` ADD FULLTEXT(`contents`);
    have error - #2006 - MySQL server has gone away
     
  12. Gary McGrath

    Gary McGrath Staff Member

    are your table engines all set to InnoDB?

    You might need to use max_statement_time in your query to ensure that it does not time out and completes adding the index

    Gary
     
  13. deyura

    deyura New Member

    Thanks! To mush googling today for me...

    Yes, InnoDB.
    All that I have described is done on a clean installation DB, where the table is InnoDB.
    In the coming days I will try to do an update of the existing database.
     
  14. deyura

    deyura New Member

    to fix it
    Code:
    CREATE TABLE IF NOT EXISTS ticketposts_transfer (
      ticketpostid int(11) NOT NULL,
      contents longtext COLLATE utf8_unicode_ci
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    
    ALTER TABLE ticketposts_transfer ADD PRIMARY KEY (ticketpostid);
    
    
    INSERT INTO ticketposts_transfer (ticketpostid, contents)
    SELECT ticketpostid, contents
    FROM ticketposts /*WHERE ticketpostid = 5*/;
    
    
    UPDATE ticketposts
    SET contents = 'a';
    
    ALTER TABLE ticketposts ADD FULLTEXT INDEX idxft_ticketposts (contents);
    
    UPDATE ticketposts c
    INNER JOIN ticketposts_transfer tt ON c.ticketpostid = tt.ticketpostid
    SET c.contents = tt.contents;
    
    /*DROP TABLE ticketposts_transfer;*/
     

Share This Page