Multiple SQL issues after update causing error 500 pages

johnny

@litechforce there are multiple database updates. You will have to keep running them until your on the latest

php update_cli.php --update_db

litechforce

Hi johnny,

I did. The updatecli --update_db says I am up to date. The problem is still there though

johnny

show me your output of php update_cli.php --update_db

litechforce

johnny

root@portal:/var/www/portal.********.com/scripts# sudo -u www-data php update_cli.php --update_db

Database is already at the latest version (2.1.2). No updates were applied.

root@portal:/var/www/portal.********.com/scripts#

johnny

Can you revert from backup and just update via command line only, I think something may have went wrong on the WebUI this is why were not recommending using the cli updater to update for this release

litechforce

Yes, I can do that

johnny

Awesome lemme know how it goes.

Start with php update_cli.php

then

php update_cli.php --update_db

keep running that note any errors if there are any

litechforce

The restore from backup finished.

I ran the sudo -u www-data php updatecli.php and the php updatecli.php --update_db.

Getting the same errors as above. If I try to assign myself a ticket, I get an error 500

Wikiphil

I've replicated the issue from the web page, Error 500.

So, I rolled back and then apply from the shell. This time, it works.

In order, to make it works:

From the scripts folder,

# sudo -u www-data php update_cli.php
Updating ff80a3db3..0390b1bc2
Fast-forward
CHANGELOG.md | 62 +
admin_role.php | 30 +-
admin_settings_company.php | 21 +-
admin_settings_default.php | 22 -Regards
…
create mode 100644 plugins/intl-tel-input/js/utils.js
create mode 100644 report_time_by_tech.php
delete mode 100644 temp_bulk_close_bugfix.php
Update successful
# sudo -u www-data php update_cli.php --update_db
Database updated from version 1.8.7 to 1.8.8.
The latest database version is 2.1.2.
# sudo -u www-data php update_cli.php --update_db
Database updated from version 1.8.8 to 1.8.9.
The latest database version is 2.1.2.
# sudo -u www-data php update_cli.php --update_db
Database updated from version 1.8.9 to 1.9.0.
The latest database version is 2.1.2.
…
# Run this command until it displays "Database is already at the latest version (2.1.2). No updates were applied.

Best regards! :-)

litechforce

How did you "roll back"?

johnny

@litechforce

Typically through a Virtual Machine Snapshot of backup.

We use Proxmox as our hypervisor Before we update we take a snapshot which takes roughly a few seconds. This can be done with other hypervisors as well XCP-NG, Hyper V and VMWare.

Excellent write up @Wikiphil

litechforce

johnny

I use proxmox as well. I just restored the VM again, and re-ran the update_cli

and the updatecli --updatedb again until it said there were no more updates.

Still having the same problem

litechforce

This is the php error I am currently getting while trying to assign a ticket:

[Sun Mar 30 17:38:01.919900 2025] [php:error] [pid 1057] [client 192.168.4.5:59075] PHP Fatal error: Uncaught mysqli_sql_exception: Unknown column 'user_settings.user_role' in 'where clause' in /var/www/portal.litechforce.com/post/user/ticket.php:579\nStack trace:\n#0 /var/www/portal.litechforce.com/post/user/ticket.php(579): mysqli_query()\n#1 /var/www/portal.litechforce.com/post.php(49): require_once('...')\n#2 {main}\n thrown in /var/www/portal.litechforce.com/post/user/ticket.php on line 579, referer: https://portal.litechforce.com/ticket.php?ticket_id=69

johnny

ah I see Its when you assign a tech, i was able to replicate thanks for that info I just pushed out a fix to the develop branch will push out a hotfix tommarow

litechforce

johnny

Thank you! I was worried I really fouled something up!

johnny

hahaha nope it woulda helped if I read further because you did mention assigning your self a ticket.

qduchenne

Nearly on time for an April fool ! xD
The command update did the trick for me 🙂

litechforce

Just tried to do the php update_cli.php command again. Here is the output:

root@portal:/var/www/portal.litechforce.com/scripts# sudo -u www-data php update_cli.php
From https://github.com/itflow-org/itflow
 * [new branch]        fix-users          -> origin/fix-users
 * [new branch]        recurring-invoices -> origin/recurring-invoices
Already up to date.
root@portal:/var/www/portal.litechforce.com/scripts# sudo -u www-data php update_cli.php --update_db
Database is already at the latest version (2.1.2). No updates were applied.
root@portal:/var/www/portal.litechforce.com/scripts#

Tried assigning a ticket to myself again - same problem

johnny

@litechforce we fixed the bug to assign tickets in develop will be out soon

litechforce

johnny

Thank you so much!

litechforce

johnny

Just curious, when will the hotfix go out?