Keep getting DB ERROR #1054

xy001

Hi, Please see my OS Ticket info here:
I haven't set up LDAP yet, just created some local test user. When I try to create a ticket as an agent, and I cannot select any user in here(even through I create some user in local) In this example, I try to select an user call test123:
And here is my user directory:

This is the error I got:

Can anyone help me resolve this issue? Thank you!

KevinTheJedi

xy001

Delete the user__cdata table in the database, search for a user, and the cdata table should be regenerated with the proper columns and it will auto-populate the data in batches on every cron run. If you don’t have a cron job setup I highly recommend you set one up.

Cheers.

xy001

KevinTheJedi
Thank you so much! I will try to delete user__cdata table in the database first and see if it's been resolved. Then I will try to setup cron job.

xy001

KevinTheJedi
Hi, Kevin. I followed your instruction deleted the user_cdata table in database, and tried to search for a user. But looks like the table is still missing. And this is the result I got.

DB Error #1146
[SELECT A1.id, A1.name, A2.address FROM ost_user A1 LEFT JOIN ost_user_email A2 ON (A1.default_email_id = A2.id) WHERE A1.name LIKE '%test%' UNION (SELECT A1.id, A1.name, A3.address FROM ost_user A1 LEFT JOIN ost_organization A2 ON (A1.org_id = A2.id) LEFT JOIN ost_user_email A3 ON (A1.default_email_id = A3.id) WHERE A2.name LIKE '%test%' LIMIT 25) UNION (SELECT A1.id, A1.name, A3.address FROM ost_user A1 LEFT JOIN ost_user_email A2 ON (A1.id = A2.user_id) LEFT JOIN ost_user_email A3 ON (A1.default_email_id = A3.id) WHERE A2.address LIKE '%test%' LIMIT 25) UNION (SELECT A1.id, A1.name, A3.address FROM ost_user A1 LEFT JOIN ost_user_account A2 ON (A1.id = A2.user_id) LEFT JOIN ost_user_email A3 ON (A1.default_email_id = A3.id) WHERE A2.username LIKE '%test%' LIMIT 25) UNION (SELECT A1.id, A1.name, A3.address FROM ost_user A1 LEFT JOIN ost_user__cdata A2 ON (A1.id = A2.user_id) LEFT JOIN ost_user_email A3 ON (A1.default_email_id = A3.id) WHERE A2.phone LIKE '%test%' LIMIT 25) LIMIT 25] Table 'osticket_db.ost_user__cdata' doesn't exist ---- Backtrace ---- #0 (root)/include/mysqli.php(201): osTicket->logDBError() #1 (root)/include/class.orm.php(3468): db_query() #2 (root)/include/class.orm.php(3524): MySqlExecutor->execute() #3 (root)/include/class.orm.php(2072): MySqlExecutor->getRow() #4 (root)/include/class.orm.php(2045): FlatArrayIterator->{closure}() #5 (root)/include/class.orm.php(2024): CallbackSimpleIterator->next() #6 (root)/include/class.orm.php(2033): CallbackSimpleIterator->rewind() #7 (root)/include/class.orm.php(1704): CallbackSimpleIterator->valid() #8 (root)/include/class.orm.php(1714): CachedResultSet->fillTo() #9 (root)/include/class.orm.php(1730): CachedResultSet->asArray() #10 (root)/include/ajax.users.php(100): CachedResultSet->getIterator() #11 (root)/include/class.dispatcher.php(145): UsersAjaxAPI->search() #12 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch() #13 (root)/include/class.dispatcher.php(120): Dispatcher->resolve() #14 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch() #15 (root)/scp/ajax.php(316): Dispatcher->resolve() #16 {main}

KevinTheJedi

xy001

Run cron manually a bit.

Cheers.

xy001

KevinTheJedi Thank you. Because we don't usually use create ticket through email, we only enabled POP/IMAP email fetch. No cron job been setup. Is there another way to resolve this issue?

KevinTheJedi

xy001

You can logout and back in a bunch of times to see if you can trigger auto-cron. Cron does far more things than just fetching emails. Recreating and repopulating the CDATA tables is one of the many things Cron does.

Cheers.

xy001

KevinTheJedi Thanks. I tried multiple time login as agent and logout. But it still showing "Missing CDATA table" in Users tab . Is there a way that I can run a command in the server to trigger the auto-cron?

KevinTheJedi

xy001

Then you will need to run cron.

Cheers.

xy001

KevinTheJedi Thank you, Kevin. I set up a cron job in my web server according to the instruction you sent before, and here is the syslog for that command in our server. But the OS ticket didn't create any ticket when email coming in. Those feathers can only work when I set Auto-Cron on. And the CDATA table are still missing. Could you let me know if I set the cron job incorrectly? Thank you!

KevinTheJedi

xy001

I don’t think so as I don’t think /etc/php is the actual executable. You can google “how to create cron job on xxx” where “xxx” is your server distribution.

Cheers.

xy001

KevinTheJedi Thank you so much! I changed the "nobody /etc/php" to "/usr/bin/php8.0" now it's working, and the table is back.