Bug - Unable to update database

JosephWithCOR

Thought I would be foolish and update just before leaving for the day.

Administration / Maintenance / Update - Program updated. Trying to step the database updates and I am left at a blank https://Server.Domain/post.php?update_db

Pressing the back button takes me back to the database update page

Current DB Version: 1.5.9

Latest DB Version: 1.6.2

Checking my server logs

access.log

[29/Oct/2024:17:13:01 -0700] "GET /post.php?update_db HTTP/1.1" 500 640 "https://Server.Domain/admin_update.php" "User Agent String"

error.log

[Tue Oct 29 17:13:01.728536 2024] [php:error] [pid 397002:tid 397002] [client w.x.y.z:55320] PHP Fatal error: Uncaught mysqli_sql_exception: Duplicate column name 'login_folder_id' in /var/www/html/database_updates.php:2296

Stack trace:

#0 /var/www/html/database_updates.php(2296): mysqli_query()

#1 /var/www/html/post/admin_update.php(292): require_once('...')

#2 /var/www/html/post.php(63): require_once('...')

#3 {main} thrown in /var/www/html/database_updates.php on line 2296, referer: https://Server.Domain/admin_update.php

Idea's?

johnny

Hey @JosephWithCOR that it is strange the logs are stating you already have a loginfolderid

field in the logins tables

Try Deleting that field then re-update the database.

klockemy

Can confirm that I'm getting the same when trying to DB update after the recent updates.

[Wed Oct 30 08:39:41.737797 2024] [php:error] [pid 1086] [client xxx.xxx.xxx.xxx:40241] PHP Fatal error: Uncaught mysqli_sql_exception: Duplicate column name 'login_folder_id' in /var/www/itflow/database_updates.php:2296\nStack trace:\n#0 /var/www/itflow/database_updates.php(2296): mysqli_query()\n#1 /var/www/itflow/post/admin_update.php(292): require_once('...')\n#2 /var/www/itflow/post.php(63): require_once('...')\n#3 {main}\n thrown in /var/www/itflow/database_updates.php on line 2296, referer: https://itflow/admin_update.php

E-werd

I'm in the same situation here. Unable to update, on sitting on database version 1.5.9 as well.

I use nginx + php-fpm, but it's the same errors as above.

EDIT: Updated the logs to be more complete, there's a sequence of errors not just a single error.

2024/10/30 10:22:01 [error] 587965#587965: *76723 FastCGI sent in stderr: "PHP message: PHP Warning: Undefined variable $comments in /opt/itflow/post/admin_update.php on line 206" while reading response header from upstream, client: ::150:e527:a37d:a68f:620f, server: domain.com, request: "GET /post.php?update HTTP/3.0", upstream: "fast>2024/10/30 10:22:03 [error] 587965#587965: *76727 FastCGI sent in stderr: "PHP message: PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect integer value: 'Closed' for column 'ticket_status' at row 42 in /opt/itflow/database_updates.php:2304

Stack trace:

#0 /opt/itflow/database_updates.php(2304): mysqli_query()

#1 /opt/itflow/post/admin_update.php(292): require_once('...')

#2 /opt/itflow/post.php(63): require_once('...')

#3 {main}

thrown in /opt/itflow/database_updates.php on line 2304" while reading response header from upstream, client: ::150:e527:a37d:a68f:620f, server: domain.com, request: "GET /post.php?update_db HTTP/3.0", upstream: "fastcgi://unix:/var/run/php/php-fpm.sock:", referrer: "https://domain.com/admin_update.php"

2024/10/30 10:22:06 [error] 587965#587965: *76730 FastCGI sent in stderr: "PHP message: PHP Warning: Undefined array key "HTTP_REFERER" in /opt/itflow/post.php on line 17; PHP message: PHP Fatal error: Uncaught mysqli_sql_exception: Duplicate column name 'login_folder_id' in /opt/itflow/database_updates.php:2296

Stack trace:

#0 /opt/itflow/database_updates.php(2296): mysqli_query()

#1 /opt/itflow/post/admin_update.php(292): require_once('...')

#2 /opt/itflow/post.php(63): require_once('...')

#3 {main}

thrown in /opt/itflow/database_updates.php on line 2296" while reading response header from upstream, client: ::150:e527:a37d:a68f:620f, server: domain.com, request: "GET /post.php?update_db HTTP/3.0", upstream: "fastcgi://unix:/var/run/php/php-fpm.sock:"

2024/10/30 10:22:22 [error] 587965#587965: *76737 FastCGI sent in stderr: "PHP message: PHP Fatal error: Uncaught mysqli_sql_exception: Duplicate column name 'login_folder_id' in /opt/itflow/database_updates.php:2296

Stack trace:

#0 /opt/itflow/database_updates.php(2296): mysqli_query()

#1 /opt/itflow/post/admin_update.php(292): require_once('...')

#2 /opt/itflow/post.php(63): require_once('...')

#3 {main}

2024/10/30 10:22:31 [error] 587965#587965: *76741 FastCGI sent in stderr: "PHP message: PHP Fatal error: Uncaught mysqli_sql_exception: Duplicate column name 'login_folder_id' in /opt/itflow/database_updates.php:2296

Stack trace:

#0 /opt/itflow/database_updates.php(2296): mysqli_query()

#1 /opt/itflow/post/admin_update.php(292): require_once('...')

#2 /opt/itflow/post.php(63): require_once('...')

#3 {main}

johnny

I've reviewed the update logic and its not duplicated anywhere and I have not seen this on our 3 instances we updated.

@klockemy @E-werd please the delete / drop the column loginfolderidin the logins table.

Then re-update the DB, if it break post those logs.

Theres def something going on I just don't know how or what yet

JosephWithCOR

searching /var/www/html/database_updates.php for login_folder_id returns

mysqli_query($mysqli, "ALTER TABLE `logins` ADD `login_folder_id` INT(11) NOT NULL DEFAULT 0 AFTER `login_password_changed_at`"); <-- I hate this line

Logging into the server and MariaDB, show me all active databases. Bunch of commands then

show databases;

Switch to itflow database

use itflow;

List all tables

show tables;

NOTE: It is a very long list that will scroll off screen

List the colums in the logins database

show columns from logins;

There is login_folder_id int(11) not null default 0

Is it empty?

select login_folder_id from logins;

Yes. Value 0 across 349 rows.

Drop back to a command prompt and BACKUP THE DATABASE

mariadb-dump --user=root --password --lock-tables --databases itflow > /home/joseph/itflow.sql

Back into MariaDB and itflow database and drop column

alter table logins drop if exists login_folder_id;

Verify it worked

show columns from logins;

Attempt to update database from website - No Bueno

Left at https://itflow.Server.Domain.local/post.php?update_db

access.log

[30/Oct/2024:08:16:41 -0700] "GET /post.php?update_db HTTP/1.1" 500 640 "https://Server.Domain/admin_update.php" "Agent String"

error.log

[Wed Oct 30 08:16:41.464142 2024] [php:error] [pid 416211:tid 416211] [client w.x.y.z:37758] PHP Fatal error: Uncaught mysqli_sql_exception: Truncated incorrect INTEGER value: 'Closed' in /var/www/html/database_updates.php:2304

Stack trace:

#0 /var/www/html/database_updates.php(2304): mysqli_query()

#1 /var/www/html/post/admin_update.php(292): require_once('...')

#2 /var/www/html/post.php(63): require_once('...')

#3 {main}\n thrown in /var/www/html/database_updates.php on line 2304, referer: https://itflow.Server.Domain.local/admin_update.php

Help?

NOTE to all readers of this forum. Please UNDERSTAND the ramifications of entering and running a command from any forum post PRIOR to running it. Your data is at stake. This is the steps and results of my attempting to troubleshoot this issue.

@johnny @wrongecho Will there be an issue using itflow with an out of date database while we are working on resolving this issue?

wrongecho

JosephWithCOR

That's a bit odd, we moved away from using text ticket statuses a while ago, that field should only have numbers now - itflow-org/itflowblob/11f68a59f31dd961c19988b3dc4fbbeeda5e3bcb/database_updates.php#L1749

Can you grab a list of any ticket_status values that aren't ints please?

You're fine on 1.5.9 whilst we work it out - there's nothing game changing in the latest updates, just increasing field counts, asset history and tags for logins.

JosephWithCOR

Looking at the error I posted, we now have a different error with this line

mysqli_query($mysqli, "ALTER TABLE `tickets` MODIFY `ticket_status` INT(11) NOT NULL");

The line is attempting to change the existing ticket_status from varchar(200) to int(11)

JosephWithCOR

Nope. There are three 'Closed' out of 2818 rows.

wrongecho

JosephWithCOR

Hmmm, can you swap the closed status for '5' please and if there aren't any more, drop the login_folder_id column again and see if the update goes through?

johnny

That is interesting, @wrongecho your right it should all be numbers in the ticket status field, I know the demo and my 3 instances were.

So it sounds like The database update was performed but failed so they may have refreshed the screen. and it continued to fail, this time on the first entry which we was creating the login folder id field in the logins table.

So really the update initially failed on changing ticket status from varchar to an int.

JosephWithCOR

johnny So it sounds like The database update was performed but failed so they may have refreshed the screen. and it continued to fail, this time on the first entry which we was creating the login folder id field in the logins table.

That is exactly what I did.

johnny

@JosephWithCOR

Set those 3 Closed tickets to the 5 status as this represents closed from the legend below

Delete the login folder id field Then try to update

Ticket Status Legend

1 New
2 Open
3 On Hold
4 Resolved
5 Closed

johnny

@JosephWithCOR Hey also curious what is the closed_at timestamps on those 3 tickets?

JosephWithCOR

select ticket_status, ticket_closed_at from tickets where ticket_status = "Closed";
+---------------+---------------------+
| ticket_status | ticket_closed_at    |
+---------------+---------------------+
| Closed        | 2024-04-03 12:00:23 |
| Closed        | 2024-04-08 16:34:03 |
| Closed        | 2024-04-08 16:35:04 |
+---------------+---------------------+

johnny

O wow those were awhile, somehow must of slipped in as we were doing the updates at that time

johnny

Dont do anything just yet im gonna try to squeeze in an update that fixes this

wrongecho

@johnny Is it worth editing 1.5.9 to include:

Updating any leftover closed tickets:

UPDATE tickets SET ticket_status = 5 WHERE ticket_status = 'Closed' AND ticket_closed_at IS NULL

Dropping the new column first for anyone else that's also failed

ALTER TABLE logins DROP login_folder_id

JosephWithCOR

update tickets set ticket_status=5 where ticket_status='Closed';

Query OK, 3 rows affected (0.009 sec)

Rows matched: 3 Changed: 3 Warnings: 0

Verify

MariaDB [itflow]> select ticket_status, ticket_closed_at from tickets where ticket_status = "Closed";

Empty set (0.002 sec)

Drop column from logins

alter table logins drop if exists login_folder_id;

Attempt to update through website - Success!!!

Thank you for your help in troubleshooting this

johnny

as @wrongecho pointed out we pushed out a fix

that makes sure all statuses are a number and to only add login folder if it exists

itflow-org/itflow58738de

@E-werd and @klockemy can you please confirm

E-werd

johnny I just did a manual pull and I was able to go through with the database updates through the site.

It appears to be good now.

klockemy

johnny

I logged in this morning and saw the updates but was forced to upgrade the DB and not see the "Update App" buttons. Did a manual git pull then ran the DB updates. All is well.

JosephWithCOR

Will they be able to update the program with a database update pending?

wrongecho

JosephWithCOR

Probably not. We moved database updates to be in front of code updates because it's generally better that you get the latest DB before getting any more code..