I am searching in all the tickers by attachment count greater than 1.
The result is empty.
Event if the search is Greater than 0 the result is still empty.
There is no Attachment count query I can make work.
There are about 1200MB of attachments in the DB. There are hundreds or even thousands of mails with attachments and that's why I am searching for them. Because I would like to archive or delete them.
Found this in the log. Is it related to the error
DB Error #1064
[SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`created` AS `created`, A7.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, A8.`name` AS `user__name`, A7.`priority` AS `cdata__priority`, B0.`firstname` AS `staff__firstname`, B0.`lastname` AS `staff__lastname`, B1.`name` AS `team__name`, A1.`team_id` AS `team_id`, A1.`isanswered` = '0' AS `2GkBZxz`, (SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND NOT R0.`flags` & 4 != 0) AS _thread_count, (SELECT COUNT(R1.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) LEFT JOIN `ost_attachment` R1 ON (R1.`type` = 'H' AND R0.`id` = R1.`object_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND R1.`inline` = 0) AS _att_count, COALESCE(B0.`firstname`, B0.`lastname`, B1.`name`, 'zzz') AS `assignee` 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`) LEFT JOIN `ost_ticket__cdata` A7 ON (A1.`ticket_id` = A7.`ticket_id`) JOIN `ost_user` A8 ON (A1.`user_id` = A8.`id`) LEFT JOIN `ost_staff` B0 ON (A1.`staff_id` = B0.`staff_id`) LEFT JOIN `ost_team` B1 ON (A1.`team_id` = B1.`team_id`) WHERE AND ((A2.`state` = 'open' AND (A1.`staff_id` = 2 OR A5.`staff_id` = 2)) OR A1.`dept_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 28) OR A6.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 28)) GROUP BY A1.`ticket_id` LIMIT 100] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND ((A2.`state` = 'open' AND (A1.`staff_id` = 2 OR A5.`staff_id` = 2)) OR A1.`d' at line 1
---- Backtrace ----
#0 (root)/include/mysqli.php(200): osTicket->logDBError('DB Error #1064', '[SELECT A1.`num...')
#1 (root)/include/class.orm.php(3457): db_query('SELECT A1.`numb...', true, true)
#2 (root)/include/class.orm.php(3504): MySqlExecutor->execute()
#3 (root)/include/class.orm.php(2087): MySqlExecutor->getArray()
#4 (root)/include/class.orm.php(2037): HashArrayIterator->{closure}()
#5 (root)/include/class.orm.php(2016): CallbackSimpleIterator->next()
#6 (root)/include/class.orm.php(2025): CallbackSimpleIterator->rewind()
#7 (root)/include/class.orm.php(1697): CallbackSimpleIterator->valid()
#8 (root)/include/class.orm.php(1707): CachedResultSet->fillTo(9223372036854775807)
#9 (root)/include/class.orm.php(1723): CachedResultSet->asArray()
#10 (root)/include/staff/templates/queue-tickets.tmpl.php(236): CachedResultSet->getIterator()
#11 (root)/scp/tickets.php(531): require_once('/home/username/...')
#12 {main}This is the system information
osTicket Version v1.12.5 (933bb1f) — Up to date
Web Server Software Apache
MySQL Version 10.1.44
PHP Version 5.6.40
I would recommend that you upgrade to 1.14.1 and PHP to 7.3 and see if this resolves your issue.
