It looks like I resolved the issue. In case it is helpful to anyone in the future here is my solution to the problem.
By logging in on the MariaDB as root and switching on general logging (and setting it to 'OFF' after I found what I needed):
SELECT GLOBAL general_log = 'ON';
The file where MariaDB stores the log can be found with this query:
SHOW GLOBAL VARIABLES LIKE 'general%';
I could find the offending query as the last entry in /var/lib/mysql/client.log (on CentOS 7).
SELECT A1.ticket_id, A1.ticket_pid, A1.number, A1.user_id, A1.user_email_id, A1.status_id, A1.dept_id, A1.sla_id, A1.topic_id, A1.staff_id, A1.team_id, A1.email_id, A1.lock_id, A1.flags, A1.sort, A1.ip_address, A1.source, A1.source_extra, A1.isoverdue, A1.isanswered, A1.duedate, A1.est_duedate, A1.reopened, A1.closed, A1.lastupdate, A1.created, A1.updated, A2.topic_id, A2.topic_pid, A2.ispublic, A2.noautoresp, A2.flags, A2.status_id, A2.priority_id, A2.dept_id, A2.staff_id, A2.team_id, A2.sla_id, A2.page_id, A2.sequence_id, A2.sort, A2.topic, A2.number_format, A2.notes, A2.created, A2.updated, A3.staff_id, A3.dept_id, A3.role_id, A3.username, A3.firstname, A3.lastname, A3.passwd, A3.backend, A3.email, A3.phone, A3.phone_ext, A3.mobile, A3.signature, A3.lang, A3.timezone, A3.locale, A3.notes, A3.isactive, A3.isadmin, A3.isvisible, A3.onvacation, A3.assigned_only, A3.show_assigned_tickets, A3.change_passwd, A3.max_page_size, A3.auto_refresh_rate, A3.default_signature_type, A3.default_paper_size, A3.extra, A3.permissions, A3.created, A3.lastlogin, A3.passwdreset, A3.updated, A4.id, A4.org_id, A4.default_email_id, A4.status, A4.name, A4.created, A4.updated, A5.team_id, A5.lead_id, A5.flags, A5.name, A5.notes, A5.created, A5.updated, A6.id, A6.pid, A6.tpl_id, A6.sla_id, A6.schedule_id, A6.email_id, A6.autoresp_email_id, A6.manager_id, A6.flags, A6.name, A6.signature, A6.ispublic, A6.group_membership, A6.ticket_auto_response, A6.message_auto_response, A6.path, A6.updated, A6.created, A7.id, A7.schedule_id, A7.flags, A7.grace_period, A7.name, A7.notes, A7.created, A7.updated, A8.id, A8.object_id, A8.object_type, A8.extra, A8.lastresponse, A8.lastmessage, A8.created, B0.id, B0.object_id, B0.object_type, B0.extra, B0.lastresponse, B0.lastmessage, B0.created, B1.id, B1.user_id, B1.flags, B1.address, B2.id, B2.name, B2.state, B2.mode, B2.flags, B2.sort, B2.properties, B2.created, B2.updatedFROMost_ticketA1 LEFT JOINost_help_topicA2 ON (A1.topic_id= A2.topic_id) LEFT JOINost_staffA3 ON (A1.staff_id= A3.staff_id) LEFT JOINost_userA4 ON (A1.user_id= A4.id) LEFT JOINost_teamA5 ON (A1.team_id= A5.team_id) LEFT JOINost_departmentA6 ON (A1.dept_id= A6.id) LEFT JOINost_slaA7 ON (A1.sla_id= A7.id) LEFT JOINost_threadA8 ON (A8.object_type= 'T' AND A1.ticket_id= A8.object_id) LEFT JOINost_threadB0 ON (A1.ticket_id= B0.object_idAND B0.object_type= 'C') LEFT JOINost_user_emailB1 ON (A4.default_email_id= B1.id) JOINost_ticket_statusB2 ON (A1.status_id= B2.id) WHERE A1.ticket_id= '39';
In the command line console I only selected A1.ticket_id but gradually added more tables (LEFT JOIN) to the query. After a while I found that it went wrong with the two joins of ost_thread.
This query indeed gave two results:
SELECT A1.ticket_idFROMost_ticketA1 LEFT JOINost_threadA8 ON (A8.object_type= 'T' AND A1.ticket_id= A8.object_id) LEFT JOINost_threadB0 ON (A1.ticket_id= B0.object_idAND B0.object_type= 'C') WHERE A1.ticket_id= '39';
This is the definition of that table (from the database dump):
CREATE TABLE `ost_thread` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`object_id` int(11) unsigned NOT NULL,
`object_type` char(1) NOT NULL,
`extra` text,
`lastresponse` datetime DEFAULT NULL,
`lastmessage` datetime DEFAULT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `object_id` (`object_id`),
KEY `object_type` (`object_type`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;
These were the last two entries:
(53,39,'C','{\"ticket_id\":14,\"number\":\"000052\"}',NULL,'2022-05-13 14:57:01','2022-05-13 14:57:01'),
(54,39,'T',NULL,NULL,'2022-05-17 10:15:02','2022-05-17 10:15:02')
The entry '53' must have been left after something went wrong with merging tickets where I normally choose to delete a ticket that is merged with the correct ticket.
After deleting entry '53' from ost_thread I could view the ticket 39 again.
