Itop баг портала пользователя

Использование
1

Привет, есть баг в портале пользователя, который хотелось бы исправить, подскажите куда копать.

Screenshot_1
Screenshot_11581×507 153 KB
Screenshot_2
Screenshot_2649×557 41.4 KB

  1. Баг возникает только у пользователей с профилем power portal user, которые находятся в одной оранизации и видят тикеты друг друга.
  2. Баг возникает только в меню Закрытые запросы. В Меню текущих запросов проблемы нет.
  3. Баг возникает только если писать в фильтр кириллицей. С цифрами и латиницей проблем нет.
Код ошибки

Failed to issue SQL query: query = SELECT COUNT(*) AS COUNT FROM (SELECT 1 FROM ((SELECT DISTINCT GARE_ticket.id AS GAREid FROM ticket AS GARE_ticketLEFT JOIN contact AS Person_caller_id_contact ON GARE_ticket.caller_id = Person_caller_id_contact.idLEFT JOIN contact AS Team_team_id_contact ON GARE_ticket.team_id = Team_team_id_contact.idLEFT JOIN contact AS Person_agent_id_contact ON GARE_ticket.agent_id = Person_agent_id_contact.idINNER JOIN (ticket_request AS GARE_ticket_request LEFT JOIN ticket AS UserRequest_parent_request_id_ticket ON GARE_ticket_request.parent_request_id = UserRequest_parent_request_id_ticket.idLEFT JOIN ticket AS Incident_parent_incident_id_ticket ON GARE_ticket_request.parent_incident_id = Incident_parent_incident_id_ticket.idLEFT JOIN ticket AS Problem_parent_problem_id_ticket ON GARE_ticket_request.parent_problem_id = Problem_parent_problem_id_ticket.idLEFT JOIN ticket AS Change_parent_change_id_ticket ON GARE_ticket_request.parent_change_id = Change_parent_change_id_ticket.id) ON GARE_ticket.id = GARE_ticket_request.id WHERE (((((((((((((GARE_ticket.operational_status = ‘closed’) AND ((GARE_ticket.caller_id = 87) AND (GARE_ticket.finalclass IN (‘UserRequest’, ‘Incident’)))) AND (((((CAST(CONCAT(COALESCE(GARE_ticket.ref, ‘’)) AS CHAR) LIKE ‘%гк%’) OR (GARE_ticket.finalclass LIKE ‘%гк%’)) OR (GARE_ticket.title LIKE ‘%гк%’)) OR (GARE_ticket.start_date LIKE ‘%гк%’)) OR (GARE_ticket.caller_id LIKE ‘%гк%’))) AND (GARE_ticket.finalclass = ‘UserRequest’)) AND ((GARE_ticket.caller_id = 87) AND (GARE_ticket.finalclass IN (‘UserRequest’, ‘Incident’)))) AND COALESCE((Person_caller_id_contact.finalclass IN (‘Person’)), 1)) AND COALESCE((Team_team_id_contact.finalclass IN (‘Team’)), 1)) AND COALESCE((Person_agent_id_contact.finalclass IN (‘Person’)), 1)) AND COALESCE((GARE_ticket.finalclass IN (‘UserRequest’)), 1)) AND COALESCE((UserRequest_parent_request_id_ticket.finalclass IN (‘UserRequest’)), 1)) AND COALESCE((Incident_parent_incident_id_ticket.finalclass IN (‘Incident’)), 1)) AND COALESCE((Problem_parent_problem_id_ticket.finalclass IN (‘Problem’)), 1)) AND COALESCE((Change_parent_change_id_ticket.finalclass IN (‘RoutineChange’, ‘ApprovedChange’, ‘NormalChange’, ‘EmergencyChange’, ‘Change’)), 1)) ) UNION(SELECT DISTINCT GARE_ticket.id AS GAREid FROM ticket AS GARE_ticketLEFT JOIN contact AS Person_caller_id_contact ON GARE_ticket.caller_id = Person_caller_id_contact.idLEFT JOIN contact AS Team_team_id_contact ON GARE_ticket.team_id = Team_team_id_contact.idLEFT JOIN contact AS Person_agent_id_contact ON GARE_ticket.agent_id = Person_agent_id_contact.idINNER JOIN (ticket_request AS GARE_ticket_request LEFT JOIN ticket AS UserRequest_parent_request_id_ticket ON GARE_ticket_request.parent_request_id = UserRequest_parent_request_id_ticket.idLEFT JOIN ticket AS Incident_parent_incident_id_ticket ON GARE_ticket_request.parent_incident_id = Incident_parent_incident_id_ticket.idLEFT JOIN ticket AS Problem_parent_problem_id_ticket ON GARE_ticket_request.parent_problem_id = Problem_parent_problem_id_ticket.idLEFT JOIN ticket AS Change_parent_change_id_ticket ON GARE_ticket_request.parent_change_id = Change_parent_change_id_ticket.id) ON GARE_ticket.id = GARE_ticket_request.id WHERE (((((((((((((GARE_ticket.operational_status = ‘closed’) AND ((GARE_ticket.caller_id = 87) AND (GARE_ticket.finalclass IN (‘UserRequest’, ‘Incident’)))) AND (((((CAST(CONCAT(COALESCE(GARE_ticket.ref, ‘’)) AS CHAR) LIKE ‘%гк%’) OR (GARE_ticket.finalclass LIKE ‘%гк%’)) OR (GARE_ticket.title LIKE ‘%гк%’)) OR (GARE_ticket.start_date LIKE ‘%гк%’)) OR (GARE_ticket.caller_id LIKE ‘%гк%’))) AND (GARE_ticket.finalclass = ‘UserRequest’)) AND ((GARE_ticket.org_id = 12) AND (GARE_ticket.finalclass IN (‘UserRequest’, ‘Incident’)))) AND COALESCE((Person_caller_id_contact.finalclass IN (‘Person’)), 1)) AND COALESCE((Team_team_id_contact.finalclass IN (‘Team’)), 1)) AND COALESCE((Person_agent_id_contact.finalclass IN (‘Person’)), 1)) AND COALESCE((GARE_ticket.finalclass IN (‘UserRequest’)), 1)) AND COALESCE((UserRequest_parent_request_id_ticket.finalclass IN (‘UserRequest’)), 1)) AND COALESCE((Incident_parent_incident_id_ticket.finalclass IN (‘Incident’)), 1)) AND COALESCE((Problem_parent_problem_id_ticket.finalclass IN (‘Problem’)), 1)) AND COALESCE((Change_parent_change_id_ticket.finalclass IN (‘RoutineChange’, ‘ApprovedChange’, ‘NormalChange’, ‘EmergencyChange’, ‘Change’)), 1)) ) UNION(SELECT DISTINCT GARE_ticket.id AS GAREid FROM ticket AS GARE_ticketLEFT JOIN contact AS Person_caller_id_contact ON GARE_ticket.caller_id = Person_caller_id_contact.idLEFT JOIN contact AS Team_team_id_contact ON GARE_ticket.team_id = Team_team_id_contact.idLEFT JOIN contact AS Person_agent_id_contact ON GARE_ticket.agent_id = Person_agent_id_contact.idINNER JOIN (ticket_request AS GARE_ticket_request LEFT JOIN ticket AS UserRequest_parent_request_id_ticket ON GARE_ticket_request.parent_request_id = UserRequest_parent_request_id_ticket.idLEFT JOIN ticket AS Incident_parent_incident_id_ticket ON GARE_ticket_request.parent_incident_id = Incident_parent_incident_id_ticket.idLEFT JOIN ticket AS Problem_parent_problem_id_ticket ON GARE_ticket_request.parent_problem_id = Problem_parent_problem_id_ticket.idLEFT JOIN ticket AS Change_parent_change_id_ticket ON GARE_ticket_request.parent_change_id = Change_parent_change_id_ticket.id) ON GARE_ticket.id = GARE_ticket_request.id WHERE (((((((((((((GARE_ticket.operational_status = ‘closed’) AND ((GARE_ticket.org_id = 12) AND (GARE_ticket.finalclass IN (‘UserRequest’, ‘Incident’)))) AND (((((CAST(CONCAT(COALESCE(GARE_ticket.ref, ‘’)) AS CHAR) LIKE ‘%гк%’) OR (GARE_ticket.finalclass LIKE ‘%гк%’)) OR (GARE_ticket.title LIKE ‘%гк%’)) OR (GARE_ticket.start_date LIKE ‘%гк%’)) OR (GARE_ticket.caller_id LIKE ‘%гк%’))) AND (GARE_ticket.finalclass = ‘UserRequest’)) AND ((GARE_ticket.caller_id = 87) AND (GARE_ticket.finalclass IN (‘UserRequest’, ‘Incident’)))) AND COALESCE((Person_caller_id_contact.finalclass IN (‘Person’)), 1)) AND COALESCE((Team_team_id_contact.finalclass IN (‘Team’)), 1)) AND COALESCE((Person_agent_id_contact.finalclass IN (‘Person’)), 1)) AND COALESCE((GARE_ticket.finalclass IN (‘UserRequest’)), 1)) AND COALESCE((UserRequest_parent_request_id_ticket.finalclass IN (‘UserRequest’)), 1)) AND COALESCE((Incident_parent_incident_id_ticket.finalclass IN (‘Incident’)), 1)) AND COALESCE((Problem_parent_problem_id_ticket.finalclass IN (‘Problem’)), 1)) AND COALESCE((Change_parent_change_id_ticket.finalclass IN (‘RoutineChange’, ‘ApprovedChange’, ‘NormalChange’, ‘EmergencyChange’, ‘Change’)), 1)) ) UNION(SELECT DISTINCT GARE_ticket.id AS GAREid FROM ticket AS GARE_ticketLEFT JOIN contact AS Person_caller_id_contact ON GARE_ticket.caller_id = Person_caller_id_contact.idLEFT JOIN contact AS Team_team_id_contact ON GARE_ticket.team_id = Team_team_id_contact.idLEFT JOIN contact AS Person_agent_id_contact ON GARE_ticket.agent_id = Person_agent_id_contact.idINNER JOIN (ticket_request AS GARE_ticket_request LEFT JOIN ticket AS UserRequest_parent_request_id_ticket ON GARE_ticket_request.parent_request_id = UserRequest_parent_request_id_ticket.idLEFT JOIN ticket AS Incident_parent_incident_id_ticket ON GARE_ticket_request.parent_incident_id = Incident_parent_incident_id_ticket.idLEFT JOIN ticket AS Problem_parent_problem_id_ticket ON GARE_ticket_request.parent_problem_id = Problem_parent_problem_id_ticket.idLEFT JOIN ticket AS Change_parent_change_id_ticket ON GARE_ticket_request.parent_change_id = Change_parent_change_id_ticket.id) ON GARE_ticket.id = GARE_ticket_request.id WHERE (((((((((((((GARE_ticket.operational_status = ‘closed’) AND ((GARE_ticket.org_id = 12) AND (GARE_ticket.finalclass IN (‘UserRequest’, ‘Incident’)))) AND (((((CAST(CONCAT(COALESCE(GARE_ticket.ref, ‘’)) AS CHAR) LIKE ‘%гк%’) OR (GARE_ticket.finalclass LIKE ‘%гк%’)) OR (GARE_ticket.title LIKE ‘%гк%’)) OR (GARE_ticket.start_date LIKE ‘%гк%’)) OR (GARE_ticket.caller_id LIKE ‘%гк%’))) AND (GARE_ticket.finalclass = ‘UserRequest’)) AND ((GARE_ticket.org_id = 12) AND (GARE_ticket.finalclass IN (‘UserRequest’, ‘Incident’)))) AND COALESCE((Person_caller_id_contact.finalclass IN (‘Person’)), 1)) AND COALESCE((Team_team_id_contact.finalclass IN (‘Team’)), 1)) AND COALESCE((Person_agent_id_contact.finalclass IN (‘Person’)), 1)) AND COALESCE((GARE_ticket.finalclass IN (‘UserRequest’)), 1)) AND COALESCE((UserRequest_parent_request_id_ticket.finalclass IN (‘UserRequest’)), 1)) AND COALESCE((Incident_parent_incident_id_ticket.finalclass IN (‘Incident’)), 1)) AND COALESCE((Problem_parent_problem_id_ticket.finalclass IN (‘Problem’)), 1)) AND COALESCE((Change_parent_change_id_ticket.finalclass IN (‘RoutineChange’, ‘ApprovedChange’, ‘NormalChange’, ‘EmergencyChange’, ‘Change’)), 1)) )) as selects) AS union_tatooine, mysql_errno = 1271, mysql_error = Illegal mix of collations for operation ‘like’

2

Было что-то подобное…
Если не ошибаюсь, проблема устранилась после перемещения группы старых тикетов в Архив.
Возможно, проблема в обработке большого количества тикетов на этой странице.

3

Да я только запустил itop в продакшн, и старых тикетов у меня еще нет :slight_smile:
Думаю врядли дело в объеме, скорее всего что-то с БД…

4

Судя по скриншоту, у вашего пользователя уже накопилось 584 закрытых тикета.
У нас проблема возникала при количестве тикетов >300 или 400, не вспомню.
Что именно сбоит, я не знаю, но мы решили именно таким образом…

5

О, мы можем закрыть 300-400 тикетов в неделю, но ведь в открытых и решенных запросах такой проблемы нет, проблема именно с закрытыми и именно с поиском кириллицей :frowning:

6

Попробуй напрямую в базе выполнить:

SELECT * FROM ticket WHERE start_date LIKE '%гк%';
7

Screenshot_31

8

Похоже, айтоп тут не виноват.

9

А кто же виноват? :grinning:
Куда копать? в бд?

10

А, стоп. Вообще-то я ожидал увидеть ошибку mysql “Illegal mix of collations for operation ‘like’”.
Какая версия MySQL?

11

Версия 5.7.25

12

Похоже на этот баг: https://bugs.mysql.com/bug.php?id=61034. Если по комментариям посмотреть, его только в 8.0.2 исправили. iTop пока не поддерживает эту версию. У нас везде используется MariaDB, и такой проблемы никогда не возникало.

2 лайка