[SELECT A1.* FROM ost_thread_event A1 WHERE A1.thread_id = 27 AND A1.state IN ('created', 'closed', 'reopened', 'edited', 'collab') ORDER BY A1.id ASC]

Unknown column 'A1.state' in 'where clause'<br />
<br />
---- Recular ----<br />
#0 (raíz)/include/mysqli.php(199): osTicket->logDBError('DB Error #1054', '[SELECT A1. FR...')<br />
#1 (raíz)/include/class.orm.php(3455): db_query('SELECT A1. FRO...', true, true)<br />
#2 (raíz)/include/class.orm.php(3502): MySqlExecutor->execute()<br />
#3 (raíz)/include/class.orm.php(1991): MySqlExecutor->getArray()<br />
#4 (raíz)/include/class.orm.php(2035): ModelInstanceManager->{closure}()<br />
#5 (raíz)/include/class.orm.php(2014): CallbackSimpleIterator->next()<br />
#6 (raíz)/include/class.orm.php(2023): CallbackSimpleIterator->rewind()<br />
#7 (raíz)/include/class.orm.php(1695): CallbackSimpleIterator->valid()<br />
#8 (raíz)/include/class.orm.php(1705): CachedResultSet->fillTo(9223372036854775807)<br />
#9 (raíz)/include/class.orm.php(1721): CachedResultSet->asArray()<br />
#10 [internal function]: CachedResultSet->getIterator()<br />
#11 (raíz)/include/client/templates/thread-entries.tmpl.php(5): IteratorIterator->__construct(Object(ModelResultSet))<br />
#12 (raíz)/include/class.thread.php(397): include('/var/www/html/o...')<br />
#13 (raíz)/include/client/view.inc.php(137): Thread->render(Array, Array)<br />
#14 (raíz)/tickets.php(147): include('/var/www/html/o...')<br />
#15 {main}

Please help us to help you by reading and following the posting guidelines located in this thread: Please read before requesting assistance. The more information you give us the better we will be able to assist you. Thank you.

Environment details?
Version of osTicket?

9 days later

I have the same problem when I try to watch created task.

Hello,

You use 5.6 version Php, 7.2 is currently the recommended version of PHP for 1.12.
You could try if you can to upgrade your php version and see if there are some changes.

Please upgrade to 1.12.2 and see if you still have a problem.

I have the same problems as OP.
So far I notice that this is related to Personal Quick Search.

I think OP have used non standard Search Pattern .

For example I get such reports:

That error looks like your custom queue #44 has a problem and needs to be deleted.

Can I some how find which exactly this is ? I mean how to check which queue has #44 ?

If it is an admin created queue you can go to: Admin panel -> Settings -> Tickets.
Click on Queues tab.
Mousing over each queue name shows you the queue id in the bottom left hand corner of the screen. Do that until you find 44. If you do not find 44, then an Agent has a custom queue.

osTicket only has something like 14 default queues on install so anything over that is a custom one added by the admin or an agent. If you view your ost_queue table, you can just delete the row with id 44. You can also get the name (title) and the staff_id of who made it.

Thanks for your handy answer.

So I identify this query in the following way:

And now I'm able to make a further investigation.

Yeah that works too. ? I should have thought of that its faster and easier.

5 days later

I found which functionality raise this error
This is related to this following field:

If I have a Quee with specified string to search then each one when the query is fired I get DB Error #1054 email raport.

@mLipok

We fixed this already, are you using v1.12.2? If not upgrade and retest.

Cheers.

I just go to check... and I'm wondering why here:

and here:

There is no info about the complete installed version number?
I mean that I see only v1.12 but there should be v1.12.X

That would be because you are not running 1.12.2?

Previous version was 1.12.1 so the number also should be presented.

My WebAdmin just upgrade my osTicket installation to the recent version.
Check in progress.

But the problem still occurs.

a month later

By the way, I have the exact same issue. OsTicket 1.12.2 (recently upgraded from 1.12.1) /PHP5.6 and getting DB errors if keyword is filled in a custom queue.
Removed keyword, the DB error is gone.
Do you remember which commit fixed that issue? (I want to double check that the code is there... For minor upgrades I'm using diffs by comparing tags in github)

ps. I'm planning to upgrade PHP to 7.x to keep future compatibility with OS Ticket (and OS security updates) but I need a fresh OS first (now it's Ubuntu 14.04.x ...I know...)

6 days later

@bujam1

So I was mistaken, we temporarily patched the count issues with keyword searches (that's why you see the number 500 results when doing a search). Yes, keywords will still throw errors when saved in a queue, this is something we look to address in the upcoming release.

Cheers.

Thanks for the info

9 days later

Hi guys,

I don't mean to high-jack this thread - simply add to it. It seems I have the identical problem and I understand that it will be sorted in next release so this is just extra info that may assist. The emails however are driving me mad, is any way to turn them off? I'm getting about 500 each day - because the system is being used very often. Also, I don't believe there are any custom queues that I'm aware of.

[SELECT COUNT(DISTINCT CASE WHEN B0.`address` = 'Oasis' AND A1.`ticket_id` = Z1.`ticket_id` THEN A1.`ticket_id` END) AS `q15`, 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.`staff_id` IN (16) AND A2.`state` = 'open' AND A1.`staff_id` IN (16) AND A2.`state` = 'open' AND A1.`staff_id` IN (16) THEN A1.`ticket_id` END) AS `q6`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2019-09-25 22:00:00' AND '2019-09-26 21: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.`staff_id` IN (16) AND A2.`state` = 'open' AND A1.`staff_id` IN (16) AND A2.`state` = 'open' AND NOT A1.`staff_id` IN (16) THEN A1.`ticket_id` END) AS `q7`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2019-09-24 22:00:00' AND '2019-09-25 21: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.`staff_id` IN (16) 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-09-22 22:00:00' AND '2019-09-29 21: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-08-31 22:00:00' AND '2019-09-30 21: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-06-30 22:00:00' AND '2019-09-30 21:59:59' THEN A1.`ticket_id` END) AS `q13`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2018-12-31 22:00:00' AND '2019-12-31 21: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_department` A6 ON (A4.`object_type` = 'D' AND A4.`object_id` = A6.`id`) JOIN `ost_user` A7 ON (A1.`user_id` = A7.`id`) LEFT JOIN `ost_organization` A8 ON (A7.`org_id` = A8.`id`) LEFT JOIN `ost_organization__cdata` B0 ON (A8.`id` = B0.`org_id`) WHERE (A2.`state` = 'open' AND (A1.`staff_id` = 16 OR A5.`staff_id` = 16)) OR (A2.`state` IN ('open', 'closed') AND (A1.`dept_id` IN (1, 2, 3, 4, 5, 6) OR A6.`id` IN (1, 2, 3, 4, 5, 6)))]

Unknown column 'Z1.ticket_id' in 'field list'<br />
<br />
---- Backtrace ----<br />
#0 (root)/include/mysqli.php(199): osTicket->logDBError('DB Error #1054', '[SELECT COUNT(D...')<br />
#1 (root)/include/class.orm.php(3455): db_query('SELECT COUNT(DI...', true, true)<br />
#2 (root)/include/class.orm.php(3502): 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(925): QuerySet->one()<br />
#12 (root)/include/class.search.php(865): SavedQueue::counts(Object(StaffSession), true)<br />
#13 (root)/include/staff/templates/queue-tickets.tmpl.php(100): SavedQueue->getCount(Object(StaffSession))<br />
#14 (root)/scp/tickets.php(526): require_once('/home/jackyind/...')<br />
#15 {main}
9 days later
ntozier changed the title to [resolved] DB Error #1054 help!!!!.
Write a Reply...