• BugCompleted

  • Multiple SQL issues after update causing error 500 pages

Hello,

I've just updated via command line (after also updating via web), to the latest IT Flow. I get the following errors in my error log.

[Sun Mar 30 11:16:11.581511 2025] [php:error] [pid 494103] [client 192.168.4.5:52877] PHP Fatal error: Uncaught mysqli_sql_exception: Unknown column 'role_id' in 'on clause' in /var/www/portal.********.com/includes/check_login.php:40\nStack trace:\n#0 /var/www/portal.********.com/includes/check_login.php(40): mysqli_query()\n#1 /var/www/portal.********.com/includes/inc_all_admin.php(5): require_once('...')\n#2 /var/www/portal.********.com/admin_user.php(7): require_once('...')\n#3 {main}\n thrown in /var/www/portal.********.com/includes/check_login.php on line 40, referer: https://portal.********.com/ticket.php?ticket_id=67

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

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

[Sun Mar 30 11:20:24.770219 2025] [php:warn] [pid 505473] [client 192.168.4.5:53156] PHP Warning: Undefined variable $config_recurring_invoice_auto_send_invoice in /var/www/portal.********.com/recurring_invoice.php on line 128, referer: https://portal.********.com/recurring_invoices.php

[Sun Mar 30 11:20:24.772851 2025] [php:warn] [pid 505473] [client 192.168.4.5:53156] PHP Warning: Undefined variable $recurring_note in /var/www/portal.********.com/modals/recurring_invoice_note_modal.php on line 14, referer: https://portal.********.com/recurring_invoices.php

[Sun Mar 30 11:20:45.025083 2025] [php:warn] [pid 505471] [client 192.168.4.5:53170] PHP Warning: Undefined variable $config_recurring_invoice_auto_send_invoice in /var/www/portal.********.com/recurring_invoice.php on line 128, referer: https://portal.********.com/recurring_invoice.php?recurring_invoice_id=4

[Sun Mar 30 11:20:45.027966 2025] [php:warn] [pid 505471] [client 192.168.4.5:53170] PHP Warning: Undefined variable $recurring_note in /var/www/portal.********.com/modals/recurring_invoice_note_modal.php on line 14, referer: https://portal.********.com/recurring_invoice.php?recurring_invoice_id=4

[Sun Mar 30 11:21:00.067215 2025] [php:warn] [pid 494101] [client 192.168.4.5:53174] PHP Warning: Undefined variable $config_recurring_invoice_auto_send_invoice in /var/www/portal.********.com/recurring_invoice.php on line 128, referer: https://portal.********.com/recurring_invoice.php?recurring_invoice_id=4

[Sun Mar 30 11:21:00.068177 2025] [php:warn] [pid 494101] [client 192.168.4.5:53174] PHP Warning: Undefined variable $recurring_note in /var/www/portal.********.com/modals/recurring_invoice_note_modal.php on line 14, referer: https://portal.********.com/recurring_invoice.php?recurring_invoice_id=4

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

The last thing I tried to do was assign myself a ticket.

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

php update_cli.php --update_db

Hi johnny,

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

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

    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#

      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

      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

      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

      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

      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

        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

          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

          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

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

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

            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