How to find the queue that has the problem (DB Error #1054)

nsl_94

Hello All

I've read on this forum that the DB Error #1054 usually is caused by a queue that has a problem.

here is my error log.

[SELECT COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND NOT A1.topic_id = 12 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q129, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 45 AND A1.ticket_id = Z1.ticket_id AND A3.object_type = 'T' THEN A1.ticket_id END) AS q156, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 13 AND A1.ticket_id = Z1.ticket_id AND A3.object_type = 'T' THEN A1.ticket_id END) AS q160, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.dept_id = 45 AND A1.ticket_id = Z1.ticket_id AND A3.object_type = 'T' THEN A1.ticket_id END) AS q161, COUNT(DISTINCT CASE WHEN A2.id IN (1, 20, 9, 19, 21) AND A1.dept_id = 45 AND A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q164, COUNT(DISTINCT CASE WHEN A2.id IN (1, 20, 9, 19, 21) AND A1.dept_id = 11 AND A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q165, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 51 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q170, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 8 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q171, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.dept_id = 8 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q173, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A1.dept_id = 45 AND NOT A2.id IN (2) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q174, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A1.dept_id = 13 AND NOT A2.id IN (2, 3) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q175, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 56 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q178, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A1.dept_id = 56 AND NOT A2.id IN (2) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q182, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND NOT A2.id IN (2) AND A2.state = 'open' AND A1.dept_id = 51 AND A1.staff_id IN (89) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q186, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND NOT (A1.team_id = 0 AND A1.staff_id = 0) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.team_id = 0 AND A1.staff_id = 0 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q4 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_thread A6 ON (A1.ticket_id = A6.object_id AND A6.object_type = 'C') LEFT JOIN ost_thread_referral A7 ON (A6.id = A7.thread_id) LEFT JOIN ost_staff A8 ON (A7.object_type = 'S' AND A7.object_id = A8.staff_id) LEFT JOIN ost_department B0 ON (A4.object_type = 'D' AND A4.object_id = B0.id) LEFT JOIN ost_department B1 ON (A7.object_type = 'D' AND A7.object_id = B1.id) WHERE ((A2.state = 'open' AND (A1.staff_id = 89 OR A5.staff_id = 89 OR A6.object_type = 'C' AND A8.staff_id = 89)) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (1, 6, 7, 8, 9, 11, 13, 14, 15, 16, 17, 18, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45, 46, 48, 50, 51, 56) OR B0.id IN (1, 6, 7, 8, 9, 11, 13, 14, 15, 16, 17, 18, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45, 46, 48, 50, 51, 56))) OR A6.object_type = 'C' AND B1.id IN (1, 6, 7, 8, 9, 11, 13, 14, 15, 16, 17, 18, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45, 46, 48, 50, 51, 56))]

Unknown column 'Z1.ticket_id' in 'field list' 
---- Backtrace ---- 
#0 (root)/include/mysqli.php(200): osTicket->logDBError('DB Error #1054', '[SELECT COUNT(D...') 
#1 (root)/include/class.orm.php(3459): db_query('SELECT COUNT(DI...', true, true) 
#2 (root)/include/class.orm.php(3506): MySqlExecutor->execute() 
#3 (root)/include/class.orm.php(2093): MySqlExecutor->getArray() 
#4 (root)/include/class.orm.php(2043): HashArrayIterator->{closure}() 
#5 (root)/include/class.orm.php(2022): CallbackSimpleIterator->next() 
#6 (root)/include/class.orm.php(2031): CallbackSimpleIterator->rewind() 
#7 (root)/include/class.orm.php(1703): CallbackSimpleIterator->valid() 
#8 (root)/include/class.orm.php(1713): CachedResultSet->fillTo(9223372036854775807) 
#9 (root)/include/class.orm.php(1336): CachedResultSet->asArray() 
#10 (root)/include/class.orm.php(1359): QuerySet->all() 
#11 (root)/include/class.search.php(973): QuerySet->one() 
#12 (root)/include/class.search.php(905): SavedQueue::counts(Object(StaffSession), true) 
#13 (root)/include/staff/templates/queue-tickets.tmpl.php(111): SavedQueue->getCount(Object(StaffSession)) 
#14 (root)/scp/tickets.php(539): require_once('(root)/i...') 
#15 {main}

How do I find wich queue I should delete and recreate? Is there a way to see in the log wich one it is?

Thank you for the help
Nuno

ntozier

The error you have is:
Unknown column 'Z1.ticket_id' in 'field list'

There are several

nsl_94 AND A1.ticket_id = Z1.ticket_id AND A3.object_type = 'T' THEN A1.ticket_id END) AS q156, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 13 AND A1.ticket_id = Z1.ticket_id AND A3.object_type = 'T' THEN A1.ticket_id END) AS q160, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.dept_id = 45 AND A1.ticket_id = Z1.ticket_id AND A3.object_type = 'T' THEN A1.ticket_id END) AS q161, COUNT(DISTINCT CASE WHEN A2.id IN (1, 20, 9, 19, 21) AND A1.dept_id = 45 AND A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q164, COUNT(DISTINCT CASE WHEN A2.id IN (1, 20, 9, 19, 21) AND A1.dept_id = 11 AND A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q165

So then Z1.ticket_id is at the end of that and reads AND A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q165
So then q165 is the problem queue.

nsl_94

Hello again

I've deleted queue 165 and the error is now:

You said that there are several queues with error. Which ones are causing the error now?

Thank you for your support
Nuno

KevinTheJedi

@nsl_94

The errors are with Queues 156, 160, 161, and 165. You will need to login to the database, go to the ost_queue table, and delete the records that match:

SELECT * FROM `ost_queue` WHERE `id` IN (156, 160, 161, 165);

Cheers.

nsl_94

Thank you again.
I've deleted the offending queues and now, the error is this one:

[SELECT COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND NOT A1.topic_id = 12 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q129, COUNT(DISTINCT CASE WHEN A2.id IN (1, 20, 9, 19, 21) AND A1.dept_id = 45 AND A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q164, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 51 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q170, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 8 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q171, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.dept_id = 8 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q173, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A1.dept_id = 45 AND NOT A2.id IN (2) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q174, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A1.dept_id = 13 AND NOT A2.id IN (2, 3) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q175, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 56 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q178, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND A1.dept_id = 56 AND NOT A2.id IN (2) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q182, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A2.state = 'open' AND A1.staff_id IN (89) AND NOT A2.id IN (2) AND A2.state = 'open' AND A1.dept_id = 51 AND A1.staff_id IN (89) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q186, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND NOT (A1.team_id = 0 AND A1.staff_id = 0) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.team_id = 0 AND A1.staff_id = 0 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.staff_id IN (89) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q4 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_thread A6 ON (A1.ticket_id = A6.object_id AND A6.object_type = 'C') LEFT JOIN ost_thread_referral A7 ON (A6.id = A7.thread_id) LEFT JOIN ost_staff A8 ON (A7.object_type = 'S' AND A7.object_id = A8.staff_id) LEFT JOIN ost_department B0 ON (A4.object_type = 'D' AND A4.object_id = B0.id) LEFT JOIN ost_department B1 ON (A7.object_type = 'D' AND A7.object_id = B1.id) WHERE ((A2.state = 'open' AND (A1.staff_id = 89 OR A5.staff_id = 89 OR A6.object_type = 'C' AND A8.staff_id = 89)) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (1, 6, 7, 8, 9, 11, 13, 14, 15, 16, 17, 18, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45, 46, 48, 50, 51, 56) OR B0.id IN (1, 6, 7, 8, 9, 11, 13, 14, 15, 16, 17, 18, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45, 46, 48, 50, 51, 56))) OR A6.object_type = 'C' AND B1.id IN (1, 6, 7, 8, 9, 11, 13, 14, 15, 16, 17, 18, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45, 46, 48, 50, 51, 56))]

Do all of the queues (q###) that apear in the error log are queues with error? If not, how do you confirm wich ones have errors?

Thank you
Nuno

ntozier

nsl_94 AND A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q164

It indicates that Queue 164 is problematic now.

You do have a LOT of queues... I think that I have only 25.

nsl_94

Thank you for your help. I have no more DB Error #1054 in my osTicket instalation.

We have around 40 agents, and each one has about 5/6 each one wich gives a LOT of queues 😀

Thank you once again
Nuno

ntozier

Very welcome. I will mark this thread as resolved and close the thread.
Please feel free to start a new thread if you have another issue, question, etc.