support adresss for creating tickets is suddenly not working anymore.

OrionExodus · May 21, 2020

First the log for the mail account that was supposed to create the tickets out of the mails:
osTicket is having trouble fetching emails from the following mail account: Benutzer: support@gerber-web.ch Server: imap.mail.hostpoint.ch Fehler: Can not authenticate to IMAP server: [AUTHENTICATIONFAILED] Authentication failed. 5 aufeinander folgende Fehler. Maximal sind 5 erlaubt Das können Verbindungsprobleme zum E-Mail-Server sein. Der nächste verzögerte Anmeldeversuch wird in ungefähr 10 Minuten erfolgen https://support.gerber-web.ch

and besides of that error, i also got immediately error logs für a Database query:

DB Error #1054

[SELECT COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 3 AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q15`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A2.`state` = 'open' AND A1.`dept_id` = 1 AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q16`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A1.`dept_id` = 1 AND A2.`state` = 'open' AND A1.`dept_id` = 1 AND B5.`value` LIKE '%bugreportongerber-web%' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q17`, COUNT(DISTINCT CASE WHEN (A1.`team_id` IN (1) OR A1.`staff_id` IN (1)) AND A2.`state` = 'open' AND (A1.`team_id` IN (1) OR A1.`staff_id` IN (1)) AND A2.`state` = 'open' AND (A1.`team_id` IN (1) OR A1.`staff_id` IN (1)) AND A1.`ticket_id` = Z1.`ticket_id` AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q18`, COUNT(DISTINCT CASE WHEN A2.`state` = 'open' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q1`, COUNT(DISTINCT CASE WHEN (A1.`team_id` IN (1) OR A1.`staff_id` IN (1)) AND A2.`state` = 'open' AND (A1.`team_id` IN (1) OR A1.`staff_id` IN (1)) AND A2.`state` = 'open' AND A1.`staff_id` IN (1) AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q6`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2020-05-18 00:00:00' AND '2020-05-18 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q9`, 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 `q3`, COUNT(DISTINCT CASE WHEN (A1.`team_id` IN (1) OR A1.`staff_id` IN (1)) AND A2.`state` = 'open' AND (A1.`team_id` IN (1) OR A1.`staff_id` IN (1)) AND A2.`state` = 'open' AND NOT A1.`staff_id` IN (1) AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q7`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2020-05-17 00:00:00' AND '2020-05-17 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q10`, 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 `q4`, COUNT(DISTINCT CASE WHEN (A1.`team_id` IN (1) OR A1.`staff_id` IN (1)) AND A2.`state` = 'open' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q5`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2020-05-18 00:00:00' AND '2020-05-24 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q11`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q8`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2020-05-01 00:00:00' AND '2020-05-31 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q12`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2020-04-01 00:00:00' AND '2020-06-30 23:59:59' AND A3.`object_type` = 'T' THEN A1.`ticket_id` END) AS `q13`, COUNT(DISTINCT CASE WHEN A2.`state` = 'closed' AND A2.`state` = 'closed' AND A1.`closed` BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' AND A3.`object_type` = 'T' 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_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_team` B0 ON (A4.`object_type` = 'E' AND A4.`object_id` = B0.`team_id`) LEFT JOIN `ost_team` B1 ON (A7.`object_type` = 'E' AND A7.`object_id` = B1.`team_id`) LEFT JOIN `ost_department` B2 ON (A4.`object_type` = 'D' AND A4.`object_id` = B2.`id`) LEFT JOIN `ost_department` B3 ON (A7.`object_type` = 'D' AND A7.`object_id` = B3.`id`) JOIN `ost_form_entry` B4 ON (B4.`object_type` = 'T' AND A1.`ticket_id` = B4.`object_id`) LEFT JOIN `ost_form_entry_values` B5 ON (B4.`id` = B5.`entry_id`) WHERE ((A2.`state` = 'open' AND (A1.`staff_id` = 1 OR A5.`staff_id` = 1 OR A6.`object_type` = 'C' AND A8.`staff_id` = 1 OR A1.`team_id` IN (1) OR B0.`team_id` IN (1) OR A6.`object_type` = 'C' AND B1.`team_id` IN (1))) OR (A2.`state` IN ('open', 'closed') AND (A1.`dept_id` IN (1, 3, 4, 5) OR B2.`id` IN (1, 3, 4, 5))) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 3, 4, 5))] Unknown column 'Z1.ticket_id' in 'field list'
 
 ---- Ablaufverfolgung ----
 #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('/home/ch284888/...')
 #15 {main}
Log-Datum: Montag, 18. Mai 2020 um 21:09 IP-Adresse: 62.167.136.43

I can normally login to this support account over webmail. But I have to mention, my whole page was broken after some faulty settings for .htaccess over plugins, i managed to restore access and I have setup HTTP Headers ifs thats also important for this case? I don't see any console errors there.

ntozier · May 21, 2020

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? (see Admin panel -> Dashboard -> Information)

OrionExodus Server: imap.mail.hostpoint.ch Fehler: Can not authenticate to IMAP server: [AUTHENTICATIONFAILED]

Have you changed anything on the server recently?

I would start by:

re-entering the password in the control panel for that email.
go to the mailserver (imap.mail.hostpoint.ch) and look at the mail server logs and see if you can find why the connection failed.

OrionExodus · May 21, 2020

I managed to get mail working again. but the solution was strange. I only changed the imap settings to "unsecure" connection. But i still got a lot of DB errors. So heres my info page:

PHP Error log: no related error is thrown
Mail Sending: SMTP
Mail Fetching/Receiving: IMAP
PHP version: 7.4.6
Operating System: Ubuntu
Client or Staff side of the UI
the location - support.gerber-web.ch/scp/index.php
A screen of the error:

Currently there are 3 assigned tickets to me, 2 closed and 1 open. everything else is deleted. I try to delete all search filter and reacreate it.

I will add a feedback if it has worked.

OrionExodus · May 21, 2020

Okay, where exactly are those search filters stored?

ntozier · May 22, 2020

osTicket does not support PHP 7.4 at this time. Please downgrade to 7.3 and re-test.

OrionExodus · May 22, 2020

If i set 7.3 the backend will not work and an internal server errror 500 appears..
[Fri May 22 18:03:14.396917 2020] [fcgid:warn] [pid 5143] [client 89.217.251.100:38596] mod_fcgid: stderr: PHP Fatal error: Uncaught ObjectNotUnique: One object was expected; however multiple objects in the database matched the query. In fact, there are 2 matching objects. in /home/ch284888/web/support.gerber-web.ch/public_html/include/class.orm.php:1363, referer: https://support.gerber-web.ch/scp/settings.php [Fri May 22 18:03:14.396966 2020] [fcgid:warn] [pid 5143] [client 89.217.251.100:38596] mod_fcgid: stderr: Stack trace:, referer: https://support.gerber-web.ch/scp/settings.php [Fri May 22 18:03:14.396976 2020] [fcgid:warn] [pid 5143] [client 89.217.251.100:38596] mod_fcgid: stderr: #0 /home/ch284888/web/support.gerber-web.ch/public_html/include/class.orm.php(606): QuerySet->one(), referer: https://support.gerber-web.ch/scp/settings.php [Fri May 22 18:03:14.397008 2020] [fcgid:warn] [pid 5143] [client 89.217.251.100:38596] mod_fcgid: stderr: #1 /home/ch284888/web/support.gerber-web.ch/public_html/include/class.thread.php(1446): VerySimpleModel::lookup(Array), referer: https://support.gerber-web.ch/scp/settings.php [Fri May 22 18:03:14.397030 2020] [fcgid:warn] [pid 5143] [client 89.217.251.100:38596] mod_fcgid: stderr: #2 /home/ch284888/web/support.gerber-web.ch/public_html/include/class.mailfetch.php(865): ThreadEntry::lookupByEmailHeaders(Array, false), referer: https://support.gerber-web.ch/scp/settings.php [Fri May 22 18:03:14.397038 2020] [fcgid:warn] [pid 5143] [client 89.217.251.100:38596] mod_fcgid: stderr: #3 /home/ch284888/web/support.gerber-web.ch/public_html/include/class.mailfetch.php(939): MailFetcher->createTicket(2), referer: https://support.gerber-web.ch/scp/settings.php [Fri May 22 18:03:14.397045 2020] [fcgid:warn] [pid 5143] [client 89.217.251.100:38596] mod_fcgid: stderr: #4 /home/ch284888/web/support.gerber-web.ch/public_html/include/class.mailfetch.php(1019): MailFetcher->fetchEmails(), referer: https://support.gerber-web.ch/scp/settings.php [Fri May 22 18:03:14.397052 2020] [fcgid:warn] [pid 5143] [client 89.217.251.100:38596] mod_fcgid: stderr: #5 /home/ch284888/web/support.gerber-web.ch/public_html/include/class.cron.php(25): MailFetcher::run(), referer: https://support.gerber-web.ch/scp/settings.php [Fri May 22 18:03:14.397058 2020] [fcgid:warn] [pid 5143] [client 89.217.251.100:38596] mod_fcgid: stderr: #6 /home/ch284888/web/support.gerber-web.ch/public_html/scp/autocron.php(61): Cro in /home/ch284888/web/support.gerber-web.ch/public_html/include/class.orm.php on line 1363, referer: https://support.gerber-web.ch/scp/settings.php [Fri May 22 18:10:52.470943 2020] [fcgid:warn] [pid 17867] (104)Connection reset by peer: [client 89.217.251.100:60432] mod_fcgid: error reading data from FastCGI server, referer: https://support.gerber-web.ch/scp/tickets.php?queue=16 [Fri May 22 18:10:52.470999 2020] [core:error] [pid 17867] [client 89.217.251.100:60432] End of script output before headers: ajax.php, referer: https://support.gerber-web.ch/scp/tickets.php?queue=16 [Fri May 22 18:10:59.950072 2020] [fcgid:warn] [pid 23385] (104)Connection reset by peer: [client 89.217.251.100:60650] mod_fcgid: error reading data from FastCGI server, referer: https://support.gerber-web.ch/scp/tickets.php?queue=16 [Fri May 22 18:10:59.950122 2020] [core:error] [pid 23385] [client 89.217.251.100:60650] End of script output before headers: ajax.php, referer: https://support.gerber-web.ch/scp/tickets.php?queue=16 [Fri May 22 18:11:01.760203 2020] [fcgid:warn] [pid 17867] (104)Connection reset by peer: [client 89.217.251.100:60692] mod_fcgid: error reading data from FastCGI server, referer: https://support.gerber-web.ch/scp/tickets.php?queue=16 [Fri May 22 18:11:01.760245 2020] [core:error] [pid 17867] [client 89.217.251.100:60692] End of script output before headers: admin.php, referer: https://support.gerber-web.ch/scp/tickets.php?queue=16

ntozier · May 22, 2020

Did you restart the webserver service after making the change?

OrionExodus · May 23, 2020

Yes sure... I have a script to all related services for 15 second of restaring.

OrionExodus · May 24, 2020

So has noone a clue? I get for about 50-100 mails per day about that error if im logged in since the morning. The strange thing is i dont have to open one of the filters, and i dont know which filter is causing this, would be amazing if the ID would have been saved or something like that. Maybe im just too dumb too read that error because its so much, i cant hold on for long, i dont even knoe what to search for in the log.. I really hope we can get this fixed, it was out of nowhere and i have customers writing tickets :/

OrionExodus · May 24, 2020

Oh now i suddenly get thousands of mails with the content:
powered-by-osticket.png: Dateityp ist nicht erlaubt / Filetype is not allowed

OrionExodus · May 24, 2020

well great... My Mailbox is full now, osticket is at its limits, everything is not usable anymore . Guess ill just start from scratch again... I dont see any clue in this DB query.

KevinTheJedi · May 26, 2020

@OrionExodus

Unknown column 'Z1.ticket_id' in 'field list'

This is due to a saved search that was not converted correctly upon upgrading (known issue). You will need to look at the full query and see where Z1.ticket_id is. Once you find that part of the query look for the ) AS qXXX for the part of the query, then go to the database, go to the ost_queue table, and delete the record with id = XXX (where XXX is the value from ) AS qXXX).

ObjectNotUnique: One object was expected; however multiple objects in the database matched the query. In fact, there are 2 matching objects.

This is caused by duplicate records in the database. You will need to look for any duplicates in the ost_thread_entry table. You can either delete one of the dupes or update the IDs in all related tables (don't forget to update the auto_index for all the tables you updated the id of).

Oh now i suddenly get thousands of mails with the content:
powered-by-osticket.png: Dateityp ist nicht erlaubt / Filetype is not allowed

This is caused by your configuration settings. You appear you do not have PNG as an allowed filetype. You can configure attachment settings under Admin Panel > Settings > System.

Cheers.