MariaDB Update

mpmsadmin

osTicket: 1.12
mariadb: 10.1.38
php: 7.0.33
apache: 2.4.25
os: debian 9 stable

Over the weekend upgraded from mariadb-server and client 10.1.38 to 10.1.40, which after that I got many emails of DB errors, including #1932, #1146, #2006. I rolled back the server and client versions to before the update, but database still throwing errors like

[DELETE `ost_lock`.* FROM `ost_lock` WHERE `ost_lock`.`expire` < NOW()]

Table 'osticket.ost_lock' doesn't exist in engine

---- Backtrace ----
#0 (root)/include/mysqli.php(199): osTicket->logDBError('DB Error #1932', '[DELETE ost_lo...')
#1 (root)/include/class.orm.php(3455): db_query('DELETE ost_loc...', true, true)
#2 (root)/include/class.orm.php(1526): MySqlExecutor->execute()
#3 (root)/include/class.lock.php(147): QuerySet->delete()
#4 (root)/include/class.cron.php(33): Lock::cleanup()
#5 (root)/scp/autocron.php(62): Cron::TicketMonitor()
#6 {main}

From error 1932. When accessing the tickets, I get this logged to /var/log/apache2/error.log

[:error] [pid 1188] [client 10.10.1.61:27229] PHP Fatal error:  Uncaught InconsistentModelException: Unable to prepare query: Table 'osticket.ost_syslog' doesn't exist in engine SELECT A1.`number` AS `nu
mber`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`lastupdate` AS `lastupdate`, A8.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, B1.`name` AS `user__name`, A8.`priority` AS `cdata__priority`, B2.`firstname
` AS `staff__firstname`, B2.`lastname` AS `staff__lastname`, B3.`name` AS `team__name`, A1.`team_id` AS `team_id`, A1.`dept_id` AS `dept_id`, B4.`name` AS `dept__name`, 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_t in /var/www/html/include/class.orm.php on line 3456, referer:
https://osticket.example/scp/login.php

The web interface is all functioning fine, ticket creation through emails and web interface okay, but there is nothing in the ticket menu and I am getting constant #1932 errors, which I couldn't find much about. Tried removing table but didn't do much, don't know much about SQL. Any ideas? Thanks

mpmsadmin

After running show table status;, looks like both ost_lock and ost_syslog have NULL values all the way across, but they show in the list. Would these normally contain default values to restore to?

KevinTheJedi

@mpmsadmin

Looks like you don't have the ost_syslog and ost_lock tables. You say they show in the list but that doesn't mean much. Can you run the following commands in MariaDB?

mariadb> SHOW CREATE TABLE `ost_syslog`;
mariadb> SHOW CREATE TABLE `ost_lock`;

The results should look like:

CREATE TABLE `ost_syslog` (
  `log_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `log_type` enum('Debug','Warning','Error') NOT NULL,
  `title` varchar(255) NOT NULL,
  `log` text NOT NULL,
  `logger` varchar(64) NOT NULL,
  `ip_address` varchar(64) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `log_type` (`log_type`)
) ENGINE=InnoDB AUTO_INCREMENT=10138 DEFAULT CHARSET=utf8
----------------------------------------------------------
CREATE TABLE `ost_lock` (
  `lock_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `staff_id` int(10) unsigned NOT NULL DEFAULT '0',
  `expire` datetime DEFAULT NULL,
  `code` varchar(20) DEFAULT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`lock_id`),
  KEY `staff_id` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Cheers.

mpmsadmin

KevinTheJedi for both I get ERROR 1932 (42S02): Table 'osticket.ost_lock' doesn't exist in engine

ntozier Thanks for the heads up.

Again, the email I am getting contains this error message every couple of minutes: [DELETE ost_lock.* FROM ost_lock WHERE ost_lock.expire < NOW()]

Table 'osticket.ost_lock' doesn't exist in engine<br />

So that's where I checked. From the Agent panel, this is what I see.

ntozier

My ost_lock is all nulls. My ost_syslog is huge and has a ton of data in it. If you have logging disabled though you would likely see nothing (This is set at: Admin panel -> Settings -> System -> Default Log Level.

KevinTheJedi

@mpmsadmin

Then that means the tables do not exist. They are mandatory tables so you must recreate them using the queries I posted above.

A better thing to do is to restore the two tables from a backup from before the upgrade and use those instead as all your original data for the tables will be present. If you do not care about the data for those two tables then you can just recreate them.

Cheers.

mpmsadmin

KevinTheJedi Thanks, I was able to do so using your data. Thought I was able to do this, but was not sure of syntax or correct terms. Cheers.