I am getting spammed by hundreds of emails of the same error and I have zero clue what this is telling me:

[SELECT COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A2.`id` IN (6) THEN A1.`ticket_id` END) AS `q15`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A2.`id` IN (8) THEN A1.`ticket_id` END) AS `q16`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 THEN A1.`ticket_id` END) AS `q22`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (20) THEN A1.`ticket_id` END) AS `q24`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (16) AND A1.`ticket_id` = Z1.`ticket_id` THEN A1.`ticket_id` END) AS `q26`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (15) AND A1.`ticket_id` = Z1.`ticket_id` THEN A1.`ticket_id` END) AS `q27`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (18) THEN A1.`ticket_id` END) AS `q28`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (17) THEN A1.`ticket_id` END) AS `q29`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (20) THEN A1.`ticket_id` END) AS `q30`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (21) THEN A1.`ticket_id` END) AS `q31`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (19) THEN A1.`ticket_id` END) AS `q32`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' THEN A1.`ticket_id` END) AS `q1`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`isanswered` = '0' THEN A1.`ticket_id` END) AS `q2`, COUNT(DISTINCT CASE WHEN (A1.`team_id` IN (10, 11) OR A1.`staff_id` IN (4)) AND A2.`state` = 'open' AND (A1.`team_id` IN (10, 11) OR A1.`staff_id` IN (4)) AND A2.`state` = 'open' AND A1.`staff_id` IN (4) THEN A1.`ticket_id` END) AS `q6`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2019-03-08 00:00:00' AND '2019-03-08 23:59:59' THEN A1.`ticket_id` END) AS `q9`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`isanswered` = '1' THEN A1.`ticket_id` END) AS `q3`, COUNT(DISTINCT CASE WHEN (A1.`team_id` IN (10, 11) OR A1.`staff_id` IN (4)) AND A2.`state` = 'open' AND (A1.`team_id` IN (10, 11) OR A1.`staff_id` IN (4)) AND A2.`state` = 'open' AND NOT A1.`staff_id` IN (4) THEN A1.`ticket_id` END) AS `q7`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2019-03-07 00:00:00' AND '2019-03-07 23:59:59' THEN A1.`ticket_id` END) AS `q10`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`isoverdue` = '1' THEN A1.`ticket_id` END) AS `q4`, COUNT(DISTINCT CASE WHEN (A1.`team_id` IN (10, 11) OR A1.`staff_id` IN (4)) AND A2.`state` = 'open' THEN A1.`ticket_id` END) AS `q5`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2019-03-04 00:00:00' AND '2019-03-10 23:59:59' THEN A1.`ticket_id` END) AS `q11`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' THEN A1.`ticket_id` END) AS `q8`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2019-03-01 00:00:00' AND '2019-03-31 23:59:59' THEN A1.`ticket_id` END) AS `q12`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2019-01-01 00:00:00' AND '2019-03-31 23:59:59' THEN A1.`ticket_id` END) AS `q13`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59' THEN A1.`ticket_id` END) AS `q14` FROM `ost_ticket` A1 JOIN `ost_ticket_status` A2 ON (A1.`status_id` = A2.`id`) LEFT JOIN `ost_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`) LEFT JOIN `ost_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) LEFT JOIN `ost_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`) LEFT JOIN `ost_team` A6 ON (A4.`object_type` = 'E' AND A4.`object_id` = A6.`team_id`) LEFT JOIN `ost_department` A7 ON (A4.`object_type` = 'D' AND A4.`object_id` = A7.`id`) LEFT JOIN (SELECT COALESCE(Z3.`object_id`, Z5.`ticket_id`, Z8.`ticket_id`) as `ticket_id`, Z1.relevance FROM (SELECT Z1.`object_id`, Z1.`object_type`, MATCH (Z1.title, Z1.content) AGAINST ('enroll@nhlearninggroup.com' IN NATURAL LANGUAGE MODE) AS `relevance` FROM `ost__search` Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('enroll@nhlearninggroup.com' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN `ost_thread_entry` Z2 ON (Z1.`object_type` = 'H' AND Z1.`object_id` = Z2.`id`) LEFT JOIN `ost_thread` Z3 ON (Z2.`thread_id` = Z3.`id` AND Z3.`object_type` = 'T') LEFT JOIN `ost_ticket` Z5 ON (Z1.`object_type` = 'T' AND Z1.`object_id` = Z5.`ticket_id`) LEFT JOIN `ost_user` Z6 ON (Z6.`id` = Z1.`object_id` and Z1.`object_type` = 'U') LEFT JOIN `ost_organization` Z7 ON (Z7.`id` = Z1.`object_id` AND Z7.`id` = Z6.`org_id` AND Z1.`object_type` = 'O') LEFT JOIN `ost_ticket` Z8 ON (Z8.`user_id` = Z6.`id`)) Z1  ON ((A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (16) AND A1.`ticket_id` = Z1.`ticket_id`)) LEFT JOIN (SELECT COALESCE(Z3.`object_id`, Z5.`ticket_id`, Z8.`ticket_id`) as `ticket_id`, Z1.relevance FROM (SELECT Z1.`object_id`, Z1.`object_type`, MATCH (Z1.title, Z1.content) AGAINST ('orders@nhlearninggroup.com' IN NATURAL LANGUAGE MODE) AS `relevance` FROM `ost__search` Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('orders@nhlearninggroup.com' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN `ost_thread_entry` Z2 ON (Z1.`object_type` = 'H' AND Z1.`object_id` = Z2.`id`) LEFT JOIN `ost_thread` Z3 ON (Z2.`thread_id` = Z3.`id` AND Z3.`object_type` = 'T') LEFT JOIN `ost_ticket` Z5 ON (Z1.`object_type` = 'T' AND Z1.`object_id` = Z5.`ticket_id`) LEFT JOIN `ost_user` Z6 ON (Z6.`id` = Z1.`object_id` and Z1.`object_type` = 'U') LEFT JOIN `ost_organization` Z7 ON (Z7.`id` = Z1.`object_id` AND Z7.`id` = Z6.`org_id` AND Z1.`object_type` = 'O') LEFT JOIN `ost_ticket` Z8 ON (Z8.`user_id` = Z6.`id`)) Z1  ON ((A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 6 AND A1.`team_id` IN (15) AND A1.`ticket_id` = Z1.`ticket_id`)) WHERE (A2.`state` = 'open' AND (A1.`staff_id` = 4 OR A5.`staff_id` = 4 OR A1.`team_id` IN (10, 11) OR A6.`team_id` IN (10, 11))) OR A1.`dept_id` IN (1, 6, 14, 16, 17) OR A7.`id` IN (1, 6, 14, 16, 17)]

Not unique table/alias: 'Z1'<br />
<br />
---- Backtrace ----<br />
#0 (root)/include/mysqli.php(199): osTicket->logDBError('DB Error #1066', '[SELECT COUNT(D...')<br />
#1 (root)/include/class.orm.php(3454): db_query('SELECT COUNT(DI...', true, true)<br />
#2 (root)/include/class.orm.php(3501): MySqlExecutor->execute()<br />
#3 (root)/include/class.orm.php(2085): MySqlExecutor->getArray()<br />
#4 (root)/include/class.orm.php(2035): HashArrayIterator->{closure}()<br />
#5 (root)/include/class.orm.php(2014): CallbackSimpleIterator->next()<br />
#6 (root)/include/class.orm.php(2023): CallbackSimpleIterator->rewind()<br />
#7 (root)/include/class.orm.php(1695): CallbackSimpleIterator->valid()<br />
#8 (root)/include/class.orm.php(1705): CachedResultSet->fillTo(9223372036854775807)<br />
#9 (root)/include/class.orm.php(1328): CachedResultSet->asArray()<br />
#10 (root)/include/class.orm.php(1351): QuerySet->all()<br />
#11 (root)/include/class.search.php(922): QuerySet->one()<br />
#12 (root)/scp/autocron.php(51): SavedQueue::counts(Object(StaffSession), false)<br />
#13 {main}

Does anyone know what this gobbeldy-gook is saying?

System Specs:

Server Information
osTicket Version v1.11 (7b1eee8) — Up to date
Web Server Software Apache/2.4.18 (Ubuntu)
MySQL Version 5.7.25
PHP Version 5.6.39-1+ubuntu16.04.1+deb.sury.org+1

Forgot to mention its registering as DB Error #1066

It constant, about 80 logs of this per minute. Not sure what is causing it.

I think I narrowed it down to some custom open-queues I setup.

I dont know why this was being caused by some very simple queues.

@Pietro_Aretino

Delete all agent related queues in the ost_queue table (ie. all the old saved searches). You can find these by looking for records with a value in the staff_id column. Delete these first and retest. If you still have issues I'd suggest deleting all non-standard queues and then you'll be fine from here on out.

NOTE: Keep track of the queue_ids of the ones you delete and go through all the ost_queue_* tables to ensure you delete the associated data like ost_queue_columns, etc. However not necessary it's still good practice to do so.

The problem is that the queues have changed dramatically and not all config formats from the old saved searches reformat properly causing query errors, etc. If you create any new Personal Queues/Saved Searches these will work fine, it's just the old ones that have been reformatted that have issues.

TLDR;

Either delete all agent related queues (ie. records with staff_id values in ost_queue) or just delete all non-standard queues and you should be good.

Standard Queues:

  • Open
  • Answered
  • My Tickets
  • Closed
  • Assigned
  • Overdue
  • Team Tickets
  • Assigned To Me
  • Open (yes another one)

Cheers.

    7 days later
    4 months later

    Had the same issue with version 1.12, its a fresh docker installed from campbellsoftwaresolutions/osticket
    Have followed your suggestion but we don't have staff specific queue.
    Any suggestion ?

    DB Error #1066
    [SELECT COUNT(*) FROM (SELECT A1.ticket_id FROM ost_ticket A1 JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) as ticket_id, Z1.relevance FROM (SELECT Z1.object_id, Z1.object_type, MATCH (Z1.title, Z1.content) AGAINST ('en attente' IN NATURAL LANGUAGE MODE) AS relevance FROM ost__search Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('en attente' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ost_thread_entry Z2 ON (Z1.object_type = 'H' AND Z1.object_id = Z2.id) LEFT JOIN ost_thread Z3 ON (Z2.thread_id = Z3.id AND Z3.object_type = 'T') LEFT JOIN ost_ticket Z5 ON (Z1.object_type = 'T' AND Z1.object_id = Z5.ticket_id) LEFT JOIN ost_user Z6 ON (Z6.id = Z1.object_id and Z1.object_type = 'U') LEFT JOIN ost_organization Z7 ON (Z7.id = Z1.object_id AND Z7.id = Z6.org_id AND Z1.object_type = 'O') LEFT JOIN ost_ticket Z8 ON (Z8.user_id = Z6.id)) Z1 JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) as ticket_id, Z1.relevance FROM (SELECT Z1.object_id, Z1.object_type, MATCH (Z1.title, Z1.content) AGAINST ('en attente' IN NATURAL LANGUAGE MODE) AS relevance FROM ost__search Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('en attente' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ost_thread_entry Z2 ON (Z1.object_type = 'H' AND Z1.object_id = Z2.id) LEFT JOIN ost_thread Z3 ON (Z2.thread_id = Z3.id AND Z3.object_type = 'T') LEFT JOIN ost_ticket Z5 ON (Z1.object_type = 'T' AND Z1.object_id = Z5.ticket_id) LEFT JOIN ost_user Z6 ON (Z6.id = Z1.object_id and Z1.object_type = 'U') LEFT JOIN ost_organization Z7 ON (Z7.id = Z1.object_id AND Z7.id = Z6.org_id AND Z1.object_type = 'O') LEFT JOIN ost_ticket Z8 ON (Z8.user_id = Z6.id)) Z1 JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) as ticket_id, Z1.relevance FROM (SELECT Z1.object_id, Z1.object_type, MATCH (Z1.title, Z1.content) AGAINST ('Waiting for customer' IN NATURAL LANGUAGE MODE) AS relevance FROM ost__search Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('Waiting for customer' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ost_thread_entry Z2 ON (Z1.object_type = 'H' AND Z1.object_id = Z2.id) LEFT JOIN ost_thread Z3 ON (Z2.thread_id = Z3.id AND Z3.object_type = 'T') LEFT JOIN ost_ticket Z5 ON (Z1.object_type = 'T' AND Z1.object_id = Z5.ticket_id) LEFT JOIN ost_user Z6 ON (Z6.id = Z1.object_id and Z1.object_type = 'U') LEFT JOIN ost_organization Z7 ON (Z7.id = Z1.object_id AND Z7.id = Z6.org_id AND Z1.object_type = 'O') LEFT JOIN ost_ticket Z8 ON (Z8.user_id = Z6.id)) Z1 WHERE A1.ticket_id = Z1.ticket_id AND A1.ticket_id = Z1.ticket_id AND A1.ticket_id = Z1.ticket_id ORDER BY Z1.relevance , Z1.relevance , Z1.relevance ) __] Not unique table/alias: 'Z1'

    ---- Trace ----
    #0 (racine)/include/mysqli.php(199): osTicket->logDBError('DB Error #1066', '[SELECT COUNT(...')
    #1 (racine)/include/class.orm.php(3455): db_query('SELECT COUNT(    )...', true, true)
    #2 (racine)/include/class.orm.php(3511): MySqlExecutor->execute()
    #3 (racine)/include/class.orm.php(2941): MySqlExecutor->getRow()
    #4 (racine)/include/class.orm.php(1372): MySqlCompiler->compileCount(Object(QuerySet))
    #5 (racine)/include/class.search.php(857): QuerySet->count()
    #6 (racine)/include/class.search.php(928): SavedQueue->getTotal()
    #7 (racine)/include/class.search.php(865): SavedQueue::counts(Object(StaffSession), true)
    #8 (racine)/include/staff/templates/queue-tickets.tmpl.php(96): SavedQueue->getCount(Object(StaffSession))
    #9 (racine)/scp/tickets.php(526): require_once('(racine)/in...')
    #10 (racine)/scp/index.php(17): require('(racine)/sc...')
    #11 {main}

    Write a Reply...