Flyspray

  • Status Assigned
  • Percent Complete
    50%
  • Task Type Bug Report
  • Category Installer and Upgrader
  • Assigned To
    peterdd
  • Operating System Linux
  • Severity High
  • Priority Very Low
  • Reported Version 1.0-rc10
  • Due in Version 1.0-rc11
  • Due Date Undecided
  • Votes
  • Private
Attached to Project: Flyspray
Opened by pizza - 29.04.2021
Last edited by peterdd - 05.05.2021

FS#2637 - Failure to upgrade 1.0-rc9 to 1.0-rc10 (postgresql 12.6, php 7.4.16)

Postgresql05.05.2021peterdd

I administer a small personal (<1K ticket) 1.0-rc9 instance running on a Fedora 32 host (php 7.4.16, postgresql 12.6) Following the upgrade instructions (ie transfer attachments, avatars, flyspray.conf.php) the setup/upgrade tool loads, and prompts me to upgrade.

Unfortunately, the upgrade fails spectacularly, with a reported SQL error that belies what’s actually wrong. Here’s a snippet from the postgresql logs where the upgrade is failing:

2021-04-28 10:33:07.190 EDT [2032049] ERROR: column “attachment_id” of relation “flyspray_attachments” already exists
2021-04-28 10:33:07.190 EDT [2032049] STATEMENT: ALTER TABLE flyspray_attachments ADD COLUMN attachment_id SERIAL
2021-04-28 10:33:07.194 EDT [2032049] ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-28 10:33:07.194 EDT [2032049] STATEMENT: ALTER TABLE flyspray_attachments ADD COLUMN task_id INTEGER
[…and everything else fails because the transaction aborted…]

It appears that the upgrade script is blindly trying to create columns that already exist in the -rc9 database, and postgresql is treating this as a failure. Because the entire upgrade happens within one transaction, this means the entire upgrade fails at the outset and won’t ever succeed.

The way past this specific problem is to make these ALTER TABLE operations conditional (eg “ALTER TABLE flyspray_attachments ADD COLUMN IF NOT EXISTS task_id INTEGER”).

Project Manager

Have you replaced the datadict file of ADOdb 5.21.0
vendor/adodb/adodb-php/adodb-datadict.inc.php

with the version that comes with Flyspray and seems better according feedback?

setup/upgrade/1.0/datadict-postgres.inc.php

Before Flyspray 1.0-rc10 with ADOdb 5.21.0 the Flyspray upgrader checked if there exists a specific function in postgresql adodb and if not points to use the Flysprays version setup/upgrade/1.0/datadict-postgres.inc.php

Sadly ADOdb added this by an own (buggy) for 5.21.0 ignoring several pull requests lurking several years. see https://github.com/ADOdb/ADOdb/issues/383

Please test (with a backup) if this fixes the problem.

pizza commented on 29.04.2021 22:03

Q$@!# site lost my post. Okay, let me try to recover everything. I hadn't tried to replace the datadict file; I used the -rc10 release tarball with everything bundled. I later tried to grab the raw source tarball and php compose to get the vendor stuff, but that broke.

The good news is that replaceing the datadict file with the one in the upgrade directory solves the problem I reported. The bad news is that there's another problem:

2021-04-29 17:26:11.997 EDT [3287408] ERROR: column "closure_comment" contains null values
2021-04-29 17:26:11.997 EDT [3287408] STATEMENT: ALTER TABLE flyspray_tasks ALTER COLUMN closure_comment SET NOT NULL

I can fix up the data by hand but I'll hold off in case you'd like me to do something else.

pizza commented on 29.04.2021 22:59

Oh, one other note – I don't recall seeing anything in the documentation about needing to replace the datadict driver, did I miss something or is it not actually documented?

Project Manager

I hoped fixes get included into official ADOdb 5.21, but not happened yet.

Previously the detection of this manual step during upgrade is not working anymore
Around line 362 of
https://github.com/Flyspray/flyspray/blob/master/setup/upgrade.php

if ($conf['database']['dbtype'] == 'pgsql') {
    $checks['postgresql_adodb'] = (bool) postgresql_adodb();
    $todo['postgresql_adodb'] = 'You have a version of ADOdb that does not contain overridden version of method ChangeTableSQL for PostgreSQL. '
	    . 'Please copy setup/upgrade/1.0/datadict-postgres.inc.php to '
	    . 'vendor/adodb/adodb-php/datadict/ before proceeding with the upgrade process.';
}
pizza commented on 04.05.2021 11:13

I dug into the data a little deeper, and it looks like I have 10 tickets with a null closure_comment. Non-coincidentally, these are the 10 oldest open tickets; the youngest of these dates from 2007-03-06, and the oldest open ticket with a non-null closure_comment was created 2007-03-24. My guess is that between those dates I'd updated to a newer version of flyspray that populated that column at task creation.

(The ticket #1 in this system was created 2005-10-22, so this is a pretty old instance…)

Again, I can manually fix up the database (ie UPDATE flyspray_tasks SET closure_comment = '' WHERE closure_comment IS NULL) but I'd prefer to hold off so I can test any changes made to the upgrade scripts.

pizza commented on 04.05.2021 11:21

Oh, one more thing. The replacement datadict-postgres.inc.php results in this warning:

Warning: Declaration of ADODB2_postgres::_getSize($ftype, $ty, $fsize, $fprec) should be compatible with ADODB_DataDict::_getSize($ftype, $ty, $fsize, $fprec, $options = false) in /var/www/flyspray/vendor/adodb/adodb-php/datadict/datadict-postgres.inc.php on line 493

And after pulling in the changes in https://github.com/Flyspray/flyspray/commit/579c756d1be40cfb22c47fd5dd7de81dc812e4eb my site upgrade completed successfully.

Project Manager

Good to hear someone has such long running Flyspray installations.

Wasn't aware versions with PostgreSQL are still in the wild as @jahto was the last guy contributing to Flyspray with PostgreSQL

Also glad to have some feedback by someone with such long history of Flyspray database entries as I had no access to such old real data for testing.

Q$@!# site lost my post.

Sorry, I had no influence of the webserver hosting bugs.flyspray.org except by the Flyspray source code. And this is a webserver issue deleting sessions with a way too short session timeout.

This really bothered me too for years, but recently I got more permissions for bugs.flyspray.org so I hope I can push the guys in the right direction. :-)

I made the required changes to the github master.

The datadict version that works for Flyspray replaces now the buggy version that is in ADOdb 5.21.0 as part of the composer.json

Loading...

Available keyboard shortcuts

Tasklist

Task Details

Task Editing