Statement violates GTID consistency

restlessmindless

Server Information
osTicket Version v1.14.1 (f1e9e88) — Up to date
Web Server Software Apache
MySQL Version 5.7.28
PHP Version 7.3.12

All installation went flawless. Everything seems to work fine. However, I started to get several error messages on system logs: DB Error #1146 and DB Error #1146.

Error seems to realte to ost_ticket__cdata' doesn't exist and Statement violates GTID consistency: CREATE TABLE ... SELECT.

Any ideas? I already tried to search for users and created a ticket, but issues are still here...

ntozier

Might want to post the full errors that you are seeing instead of pieces and parts.

restlessmindless

Alright. here you have them:

DB Error #1786
[CREATE TABLE IF NOT EXISTS ost_ticket__cdata (PRIMARY KEY (ticket_id)) DEFAULT CHARSET=utf8 AS SELECT entry.object_id as ticket_id, MAX(IF(field.id='20', coalesce(ans.value_id, ans.value), NULL)) as subject, MAX(CASE WHEN field.id='22' THEN REPLACE(REPLACE(REPLACE(REPLACE(coalesce(ans.value_id, ans.value), '{', ''), '}', ''), '"', ''), ':', ', ') ELSE NULL END) as priority FROM ost_form_entry entry JOIN ost_form_entry_values ans ON ans.entry_id = entry.id JOIN ost_form_field field ON field.id=ans.field_id WHERE entry.object_type='T' GROUP BY entry.object_id] Statement violates GTID consistency: CREATE TABLE ... SELECT.

---- Backtrace ----
#0 (root)/include/mysqli.php(200): osTicket->logDBError('DB Error #1786', '[CREATE TABLE I...')
#1 (root)/include/class.dynamic_forms.php(299): db_query('CREATE TABLE IF...')
#2 (root)/include/class.dynamic_forms.php(292): DynamicForm::buildDynamicDataView(Array)
#3 (root)/include/staff/templates/queue-tickets.tmpl.php(21): DynamicForm::ensureDynamicDataView()
#4 (root)/scp/tickets.php(539): require_once('/homepages/5/d2...')
#5 (root)/scp/index.php(17): require('/homepages/5/d2...')
#6 {main}

Log Date: Monday, December 9, 2019 at 5:55 PM IP Address: 2804:14c:100:89cf::1001

DB Error #1146
[SELECT A1.number AS number, A1.ticket_id AS ticket_id, A1.source AS source, A1.lastupdate AS lastupdate, B2.subject AS cdata__subject, A1.isoverdue AS isoverdue, B4.name AS user__name, B2.priority AS cdata__priority, B5.firstname AS staff__firstname, B5.lastname AS staff__lastname, B6.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, B7.expire > NOW() AND NOT B7.staff_id = 1 AS _locked, (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_collaborator R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id) AS _collabs, COALESCE(B5.firstname, B5.lastname, B6.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_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) LEFT JOIN ost_ticket__cdata B2 ON (A1.ticket_id = B2.ticket_id) LEFT JOIN ost_ticket_priority B3 ON (B2.priority = B3.priority_id) JOIN ost_user B4 ON (A1.user_id = B4.id) LEFT JOIN ost_staff B5 ON (A1.staff_id = B5.staff_id) LEFT JOIN ost_team B6 ON (A1.team_id = B6.team_id) LEFT JOIN ost_lock B7 ON (A1.lock_id = B7.lock_id) WHERE A2.state = 'open' AND ((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.dept_id IN (1, 3) OR B0.id IN (1, 3) OR A6.object_type = 'C' AND B1.id IN (1, 3)) AND A3.object_type = 'T' GROUP BY A1.ticket_id ORDER BY B3.priority_urgency ASC, A1.lastupdate DESC LIMIT 25] Table 'dbs238139.ost_ticket__cdata' doesn't exist

---- Backtrace ----
#0 (root)/include/mysqli.php(200): osTicket->logDBError('DB Error #1146', '[SELECT A1.num...') #1 (root)/include/class.orm.php(3459): db_query('SELECT A1.numb...', 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(1729): CachedResultSet->asArray()
#10 (root)/include/staff/templates/queue-tickets.tmpl.php(245): CachedResultSet->getIterator()
#11 (root)/scp/tickets.php(539): require_once('/homepages/5/d2...')
#12 (root)/scp/index.php(17): require('/homepages/5/d2...')
#13 {main}

Log Date: Monday, December 9, 2019 at 5:55 PM IP Address: 2804:14c:100:89cf::1001

KevinTheJedi

@restlessmindless

This is GTID related...you'll most likely need this:

https://github.com/osTicket/osTicket/pull/3832

We are looking into this issue.

Cheers.

restlessmindless

Excellent KevinTheJedi.
I changed the static function buildDynamicDataView($cdata) as per your suggestion and it seems to be working fine. I will do more tests but it seems the issue with GTID consistency cannot be solved with another approach. The complicated structure using temporary tables looks dirty, but effective. Thank you!

If you foresee a better way to solve it, let me know.

restlessmindless

I guess I was too optimistic about it. I am still getting "Statement violates GTID consistency".
Any other ways to solve it?

DB Error #1786
[CREATE TABLE IF NOT EXISTS ost_task__cdata (PRIMARY KEY (task_id)) DEFAULT CHARSET=utf8 AS SELECT entry.object_id as task_id, MAX(IF(field.id='32', coalesce(ans.value_id, ans.value), NULL)) as title FROM ost_form_entry entry JOIN ost_form_entry_values ans ON ans.entry_id = entry.id JOIN ost_form_field field ON field.id=ans.field_id WHERE entry.object_type='A' GROUP BY entry.object_id] Statement violates GTID consistency: CREATE TABLE ... SELECT.

---- Backtrace ----
#0 (root)/include/mysqli.php(200): osTicket->logDBError('DB Error #1786', '[CREATE TABLE I...')
#1 (root)/include/class.dynamic_forms.php(305): db_query('CREATE TABLE IF...')
#2 (root)/include/class.dynamic_forms.php(292): DynamicForm::buildDynamicDataView(Array)
#3 (root)/include/staff/tasks.inc.php(6): DynamicForm::ensureDynamicDataView()
#4 (root)/scp/tasks.php(257): require_once('/homepages/5/d2...')
#5 {main}

KevinTheJedi

@restlessmindless

Yes this is an ongoing issue that we are looking into addressing very soon. We have a few things to take care of before then but this is on our list of things to address asap.

Cheers.

dashi

any temporary work around available? i tried a few options mentioned online but still generate db errors

ZoshMan

dashi The workaround you are seeking is literally mentioned in the preceding replies and in the linked GitHub pull.

dashi

ZoshMan the workaround works somewhat. i still get the following error.

DB Error #1471
[INSERT INTO ost_ticket__cdata SET subject='test from email', ticket_id= 5 ON DUPLICATE KEY UPDATE subject='test from email'] The target table ost_ticket__cdata of the INSERT is not insertable-into

---- Backtrace ----
#0 (root)/include/mysqli.php(200): osTicket->logDBError('DB Error #1471', '[INSERT INTO o...') #1 (root)/include/class.dynamic_forms.php(351): db_query('INSERT INTOos...')
#2 (root)/include/class.dynamic_forms.php(363): DynamicForm::updateDynamicDataView(Object(DynamicFormEntryAnswer), NULL)
#3 (root)/include/class.signal.php(98): DynamicForm::updateDynamicFormEntryAnswer(Object(DynamicFormEntryAnswer), NULL)
#4 (root)/include/class.orm.php(680): Signal::send('model.created', Object(DynamicFormEntryAnswer))
#5 (root)/include/class.dynamic_forms.php(1475): VerySimpleModel->save(false)
#6 (root)/include/class.dynamic_forms.php(1317): DynamicFormEntryAnswer->save(false)
#7 (root)/include/class.dynamic_forms.php(1267): DynamicFormEntry->saveAnswers(NULL, false)
#8 (root)/include/class.ticket.php(4278): DynamicFormEntry->save()
#9 (root)/include/class.mailfetch.php(890): Ticket::create(Array, Array, 'Email')
#10 (root)/include/class.mailfetch.php(939): MailFetcher->createTicket(2)
#11 (root)/include/class.mailfetch.php(1019): MailFetcher->fetchEmails()
#12 (root)/include/class.cron.php(25): MailFetcher::run()
#13 (root)/scp/autocron.php(61): Cron::MailFetcher()
#14 {main}