Need to upgrade from 1.12 to 1.17

CoastalData · Jan 26, 2023

No matter what I've tried, this upgrade will not proceed.

Any help would be greatly appreciated.

KevinTheJedi · Jan 26, 2023

CoastalData

What are you seeing now?

Cheers.

CoastalData · Jan 26, 2023

It says "Upgrade Aborted" and the latest error is:

DB Error #1054 [SELECT A1.* FROM `soft_email_account` A1 WHERE A1.`type` = 'smtp' AND A1.`type` = 'smtp' AND A1.`email_id` = 5] Unknown column 'A1.type' in 'where clause' ---- Backtrace ---- #0 (root)/include/mysqli.php(207): osTicket->logDBError('DB Error #1054', '[SELECT A1.* FR...') #1 (root)/include/class.orm.php(3482): db_query('SELECT A1.* FRO...', true, true) #2 (root)/include/class.orm.php(3529): MySqlExecutor->execute() #3 (root)/include/class.orm.php(2010): MySqlExecutor->getArray() #4 (root)/include/class.orm.php(2054): ModelInstanceManager->{closure}() #5 (root)/include/class.orm.php(2033): CallbackSimpleIterator->next() #6 (root)/include/class.orm.php(2042): CallbackSimpleIterator->rewind() #7 (root)/include/class.orm.php(1713): CallbackSimpleIterator->valid() #8 (root)/include/class.orm.php(1723): CachedResultSet->fillTo(9223372036854775807) #9 (root)/include/class.orm.php(1337): CachedResultSet->asArray() #10 (root)/include/class.orm.php(1360): QuerySet->all() #11 (root)/include/class.orm.php(606): QuerySet->one() #12 (root)/include/class.orm.php(381): VerySimpleModel::lookup(Array) #13 (root)/include/class.orm.php(417): VerySimpleModel->get('smtp', NULL) #14 (root)/include/class.email.php(210): VerySimpleModel->__get('smtp') #15 (root)/include/class.mailer.php(35): Email->getSmtpAccount(false) #16 (root)/include/class.email.php(231): osTicket\Mail\Mailer->__construct(Object(Email)) #17 (root)/include/class.email.php(245): Email->send('jscott@kellytou...', '[core]: Upgrade...', '[/**\n* @signatu...', NULL, Array) #18 (root)/include/class.upgrader.php(204): Email->sendAlert('jscott@kellytou...', '[core]: Upgrade...', '[/**\n* @signatu...') #19 (root)/include/class.setup.php(115): StreamUpgrader->onError('[/**\n* @signatu...') #20 (root)/include/class.setup.php(69): SetupWizard->abort('[/**\n* @signatu...', false) #21 (root)/include/class.setup.php(44): SetupWizard->load_sql('/**\n* @signatur...', 'soft_', true, false) #22 (root)/include/class.upgrader.php(379): SetupWizard->load_sql_file('/home/customer/...', 'soft_') #23 (root)/include/class.upgrader.php(112): StreamUpgrader->upgrade() #24 (root)/include/ajax.upgrader.php(42): Upgrader->upgrade() #25 (root)/include/class.dispatcher.php(153): UpgraderAjaxAPI->upgrade() #26 (root)/include/class.dispatcher.php(40): UrlMatcher->dispatch('/upgrader', NULL) #27 (root)/scp/ajax.php(326): Dispatcher->resolve('/upgrader') #28 {main}

KevinTheJedi · Jan 26, 2023

CoastalData

It seems like the type column is missing from the soft_email_account table which it shouldn't be. Something is definitely going wrong somewhere I just don't know where as I cannot replicate this. Are you sure you are using v1.17.2? Did you copy/replace the code files or completely clear the directory and upload the new files? It's always better to fully replace the files in case copy/replace doesn't work correctly or if there are some old lingering files.

Cheers.

KevinTheJedi · Jan 26, 2023

CoastalData

Ah, I see now that you updated the error. So it looks like it's failing with an error and tries to send you an email but that fails. There should be an error logged before this one.

You can see it abort here (stack traces are in reverse, confusing, I know lol):

#20 (root)/include/class.setup.php(69): SetupWizard->abort('[/**\n* @signatu...', false)<br />
#21 (root)/include/class.setup.php(44): SetupWizard->load_sql('/**\n* @signatur...', 'soft_', true, false)<br />

Cheers.

CoastalData · Jan 26, 2023

You're right, here's the error that came before that:

[core]: Upgrader Error [/** * @signature e69781546e08be96d787199a911d0ffe * @version v1.14.0 * @title Thread Type * * This patch adds a new field to the Thread Event table called thread_type * it allows us to be able to delete threads and thread entries when a ticket * is deleted while still maintaining dashboard statistics * */ CREATE TABLE `soft_thread_event_new` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `thread_id` int(11) unsigned NOT NULL default '0', `thread_type` char(1) DEFAULT '', `event_id` int(11) unsigned DEFAULT NULL, `staff_id` int(11) unsigned NOT NULL, `team_id` int(11) unsigned NOT NULL, `dept_id` int(11) unsigned NOT NULL, `topic_id` int(11) unsigned NOT NULL, `data` varchar(1024) DEFAULT NULL COMMENT 'Encoded differences', `username` varchar(128) NOT NULL default 'SYSTEM', `uid` int(11) unsigned DEFAULT NULL, `uid_type` char(1) NOT NULL DEFAULT 'S', `annulled` tinyint(1) unsigned NOT NULL default '0', `timestamp` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ticket_state` (`thread_id`, `event_id`, `timestamp`), KEY `ticket_stats` (`timestamp`, `event_id`) ) DEFAULT CHARSET=utf8] Table 'soft_thread_event_new' already exists

CoastalData · Jan 26, 2023

KevinTheJedi Are you sure you are using v1.17.2? Did you copy/replace the code files or completely clear the directory and upload the new files?

I copied the new files to overwrite the old site so any pre-existing files remain. Should I delete all the old files and then extract the new ones again?

And yes, definitely 1.17.2.

KevinTheJedi · Jan 26, 2023

CoastalData

It tried to create a new table but it already exists. So restore from backup again, but before you run the upgrader, make sure the soft_thread_event_new table doesn't already exist.

Cheers.

CoastalData · Jan 26, 2023

Already did that, but it finds another table to fail on. I almost need a list of every new table added from 1.14 onwards.

Or, the installer needs that extra database logic, "IF EXISTS".

KevinTheJedi · Jan 26, 2023

CoastalData

I would at least try. Make sure you copy/backup your /path/to/osTicket/include/ost-config.php file as this houses important information.

Cheers.

CoastalData · Jan 26, 2023

OK sure, this will be my 6th run through. First I'll delete every table from the database, and then I'll restore it, and then I'll run through the installer and post every error, again.

KevinTheJedi · Jan 26, 2023

CoastalData

Roger that. Normally this shouldn’t be an issue but it seems like something is wonky in your install/setup.

Cheers.

CoastalData · Jan 26, 2023

OK, started it all over on a freshly restored database, ran it until it quit, and it shows 3 new log events:

Upgrader - 8b923d61 applied Patch 00c949a6-8b923d61 applied successfully (v1.14.0)

Then

Upgrader - 8b923d61 (task pending). The ScheduleCreator task reports there is work to do

Then finally:

Upgrader - 9b5550da applied Patch 8b923d61-9b5550da applied successfully (v1.14.0)

at this point, all I can do is re-run the upgrader, and the next patch is "Thread Type".

CoastalData · Jan 26, 2023

As soon as I run that, with no hesitation, I get a 502 error, and then these two new log entries:

This one is repeated:
Upgrader - 9b5550da applied Patch 8b923d61-9b5550da applied successfully (v1.14.0)

Next is this one:

DB Error #1062 [INSERT INTO `soft_schedule` SET `id` = 1, `flags` = 1, `name` = 'Monday - Friday 8am - 5pm with U.S. Holidays', `created` = NOW(), `updated` = NOW()] Duplicate entry '1' for key 'PRIMARY' ---- Backtrace ---- #0 (root)/include/mysqli.php(207): osTicket->logDBError('DB Error #1062', '[INSERT INTO `s...') #1 (root)/include/class.orm.php(3482): db_query('INSERT INTO `so...', true, true) #2 (root)/include/class.orm.php(658): MySqlExecutor->execute() #3 (root)/include/class.schedule.php(319): VerySimpleModel->save(false) #4 (root)/include/class.schedule.php(364): Schedule->save() #5 (root)/include/class.schedule.php(369): Schedule::create(Array) #6 (root)/include/upgrader/streams/core/00c949a6-8b923d61.task.php(9): Schedule::__create(Array) #7 (root)/include/class.upgrader.php(348): ScheduleCreator->run(30) #8 (root)/include/class.upgrader.php(121): StreamUpgrader->doTask() #9 (root)/scp/upgrade.php(39): Upgrader->__call('doTask', Array) #10 {main}

As you can see, that's complaining about records in the soft_schedule table.

I found that those records DO EXIST in that table, and in previous attempts, I went in and cleared those records in that table, which does let us get to the next step, but it's still a path to failure.

CoastalData · Jan 26, 2023

Should I continue? I feel like that spot right there where it failed on soft_schedule is critical.

CoastalData · Jan 26, 2023

INSERT INTO soft_schedule SET id = 1, flags = 1, name = 'Monday - Friday 8am - 5pm with U.S. Holidays', created = NOW(), updated = NOW()] Duplicate entry '1' for key 'PRIMARY'

This is another IF EXISTS scenario; it tries to insert a record with id=1 but that record already exists, and rather than handling that, it fails.

Why does the table and record already exist, I do not know, the database was restored clean, so either the old site already had that table on v1.12, or the upgrader script tried twice to create it, thus failing on the second attempt.

KevinTheJedi · Jan 26, 2023

CoastalData

Duplicate records can be ignored they shouldn't cause a full exit.

Cheers.

CoastalData · Jan 26, 2023

Well continuing to try to run just results in repetetive errors, as seen here:

Emptying those records from that table:

Ran the upgrader again, got a "task pending" message, run it again, I get "Upgrade Aborted".

soft_thread_event_new exists:
[core]: Upgrader Error [/** * @signature e69781546e08be96d787199a911d0ffe * @version v1.14.0 * @title Thread Type * * This patch adds a new field to the Thread Event table called thread_type * it allows us to be able to delete threads and thread entries when a ticket * is deleted while still maintaining dashboard statistics * */ CREATE TABLE `soft_thread_event_new` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `thread_id` int(11) unsigned NOT NULL default '0', `thread_type` char(1) DEFAULT '', `event_id` int(11) unsigned DEFAULT NULL, `staff_id` int(11) unsigned NOT NULL, `team_id` int(11) unsigned NOT NULL, `dept_id` int(11) unsigned NOT NULL, `topic_id` int(11) unsigned NOT NULL, `data` varchar(1024) DEFAULT NULL COMMENT 'Encoded differences', `username` varchar(128) NOT NULL default 'SYSTEM', `uid` int(11) unsigned DEFAULT NULL, `uid_type` char(1) NOT NULL DEFAULT 'S', `annulled` tinyint(1) unsigned NOT NULL default '0', `timestamp` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ticket_state` (`thread_id`, `event_id`, `timestamp`), KEY `ticket_stats` (`timestamp`, `event_id`) ) DEFAULT CHARSET=utf8] Table 'soft_thread_event_new' already exists

And then:

DB Error #1054 [SELECT A1.* FROM `soft_email_account` A1 WHERE A1.`type` = 'smtp' AND A1.`type` = 'smtp' AND A1.`email_id` = 5] Unknown column 'A1.type' in 'where clause' ---- Backtrace ---- #0 (root)/include/mysqli.php(207): osTicket->logDBError('DB Error #1054', '[SELECT A1.* FR...') #1 (root)/include/class.orm.php(3482): db_query('SELECT A1.* FRO...', true, true) #2 (root)/include/class.orm.php(3529): MySqlExecutor->execute() #3 (root)/include/class.orm.php(2010): MySqlExecutor->getArray() #4 (root)/include/class.orm.php(2054): ModelInstanceManager->{closure}() #5 (root)/include/class.orm.php(2033): CallbackSimpleIterator->next() #6 (root)/include/class.orm.php(2042): CallbackSimpleIterator->rewind() #7 (root)/include/class.orm.php(1713): CallbackSimpleIterator->valid() #8 (root)/include/class.orm.php(1723): CachedResultSet->fillTo(9223372036854775807) #9 (root)/include/class.orm.php(1337): CachedResultSet->asArray() #10 (root)/include/class.orm.php(1360): QuerySet->all() #11 (root)/include/class.orm.php(606): QuerySet->one() #12 (root)/include/class.orm.php(381): VerySimpleModel::lookup(Array) #13 (root)/include/class.orm.php(417): VerySimpleModel->get('smtp', NULL) #14 (root)/include/class.email.php(210): VerySimpleModel->__get('smtp') #15 (root)/include/class.mailer.php(35): Email->getSmtpAccount(false) #16 (root)/include/class.email.php(231): osTicket\Mail\Mailer->__construct(Object(Email)) #17 (root)/include/class.email.php(245): Email->send('support@kellyto...', '[core]: Upgrade...', '[/**\n* @signatu...', NULL, Array) #18 (root)/include/class.upgrader.php(204): Email->sendAlert('support@kellyto...', '[core]: Upgrade...', '[/**\n* @signatu...') #19 (root)/include/class.setup.php(115): StreamUpgrader->onError('[/**\n* @signatu...') #20 (root)/include/class.setup.php(69): SetupWizard->abort('[/**\n* @signatu...', false) #21 (root)/include/class.setup.php(44): SetupWizard->load_sql('/**\n* @signatur...', 'soft_', true, false) #22 (root)/include/class.upgrader.php(379): SetupWizard->load_sql_file('/home/customer/...', 'soft_') #23 (root)/include/class.upgrader.php(112): StreamUpgrader->upgrade() #24 (root)/include/ajax.upgrader.php(42): Upgrader->upgrade() #25 (root)/include/class.dispatcher.php(153): UpgraderAjaxAPI->upgrade() #26 (root)/include/class.dispatcher.php(40): UrlMatcher->dispatch('/upgrader', NULL) #27 (root)/scp/ajax.php(326): Dispatcher->resolve('/upgrader') #28 {main}

KevinTheJedi · Jan 26, 2023

CoastalData

Then I’m stumped. One last thing to try is upping the PHP memory limit and max_execution_time and retest. I think the core problem is it’s timing out when doing batches of data.

Cheers.

CoastalData · Jan 26, 2023

Neither memory limit nor max_execution_time are editable on my host (SiteGround).

Need to upgrade from 1.12 to 1.17

CCoastalData

KevinTheJedi

CCoastalData

KevinTheJedi

KevinTheJedi

CCoastalData

CCoastalData

KevinTheJedi

CCoastalData

KevinTheJedi

CCoastalData

KevinTheJedi

CCoastalData

CCoastalData

CCoastalData

CCoastalData

KevinTheJedi

CCoastalData

KevinTheJedi

CCoastalData