DB Error #1813

peter_kropf

I'm running osTicket 1.16.1 on a Ubuntu system. I added a second email address for submitting tickets and it's working as expected. After adding a third, I'm receiving these errors. Any advise?

[CREATE TABLE IF NOT EXISTS ost_organization__cdata (PRIMARY KEY
(org_id)) DEFAULT CHARSET=utf8 AS SELECT entry.object_id as org_id, MAX(IF(field.id='27',coalesce(ans.value_id, ans.value),NULL)) as name,MAX(IF(field.id='28',coalesce(ans.value_id, ans.value),NULL)) as address,MAX(IF(field.id='29',coalesce(ans.value_id, ans.value),NULL)) as phone,MAX(IF(field.id='30',coalesce(ans.value_id, ans.value),NULL)) as website,MAX(IF(field.id='31',coalesce(ans.value_id, ans.value),NULL)) as notes 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='O' GROUP BY entry.object_id]

Tablespace 'ost.ost_organization__cdata' exists. 
 
---- Backtrace ---- 
#0 (root)/include/mysqli.php(201): osTicket->logDBError() 
#1 (root)/include/class.dynamic_forms.php(345): db_query() 
#2 (root)/include/class.dynamic_forms.php(338): DynamicForm::buildDynamicDataView() 
#3 (root)/include/class.dynamic_forms.php(312): DynamicForm::ensureDynamicDataView() 
#4 (root)/include/class.dynamic_forms.php(320): DynamicForm::ensureDynamicDataViews() 
#5 (root)/include/class.signal.php(98): DynamicForm::ensureCdataTables() 
#6 (root)/include/class.cron.php(123): Signal::send() 
#7 (root)/include/api.cron.php(19): Cron::run() 
#8 (root)/include/api.cron.php(40): CronApiController->run() 
#9 (root)/api/cron.php(23): LocalCronApiController::call() 
#10 {main}

peter_kropf

My issue was related to an mysql database internal issue. In the /var/lib/mysql/ost folder, there was an ost_organization__cdata.ibd file but the database didn't have an associated organization table. I shut down the apache and mysql services, created a backup of the physical files and deleted the .ibd file. After restarting mysql and apache my helpdesk looks to be running correctly again.

KevinTheJedi

peter_kropf

First, you really need to upgrade to v1.16.3 as v1.16.1 had issues still.

Secondly, the organization__cdata table can be regenerated in the UI. You can add a dummy field to the Organization Information Form, save changes, delete the dummy field, and Save Changes. This will trigger the CDATA table to be dropped, recreated, and repopulated. CDATA data is repopulated in batches and if you don't have a cron job setup then it will only trigger on auto-cron.

Cheers.