Unknown column 'rf.submission_file_id' in 'field list'

Hello, I am trying to upgrade our current OJS version 3.1.2.4 to 3.3.0.8, but the upgrade failed due to the following SQL error:

PHP Notice:  Undefined property: stdClass::$properties in /var/www/ojs/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php on line 89
PHP Warning:  Invalid argument supplied for foreach() in /var/www/ojs/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php on line 89
PHP Warning:  Creating default object from empty value in /var/www/ojs/classes/plugins/PubObjectsExportPlugin.inc.php on line 458
ERROR: Upgrade failed: DB: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rf.submission_file_id' in 'field list' (SQL: select `rf`.`submission_file_id`, `rf`.`review_id` from `publication_galleys` as `pg` left join `submission_files` as `sf` on `pg`.`submission_file_id` = `sf`.`submission_file_id` where `sf`.`submission_file_id` is null and `pg`.`submission_file_id` is not null)

Is it me, or does the SQL query seem to be a bit off because the alias “rf” is not defined in the query? Has anyone encountered a similar issue?

The database is running on 10.3.31-MariaDB. All the tables within it use InnoDB as a storage engine. I have tried to upgrade only the pre-upgrade database where the upgrade has not failed.

Hello @asmecher

I have found a great deal of help from your suggestions regarding topics that are related to upgrading the OJS software. Maybe you can suggest why and where such SQL query appears? Even more interesting is that there is no such field as “review_id” in neither of those tables in the failed SQL query. So I assume that the alias “rf” is meant for the “review_files” table. There is also a field called “review_id” in that . Based on that, I modified the SQL query to look like this:
select "rf"."submission_file_id", "rf"."review_id" from "review_files" as "rf" left join "submission_files" as "sf" on "rf"."submission_file_id" = "sf.submission_file_id" where "sf"."submission_file_id" is null and "rf"."submission_file_id" is not null

The query now worked but returned an empty result set.

I would be happy for any suggestion or idea.

Thank you

Hi @kopikas,

That query is written in classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php in the _migrateSubmissionFiles function.

Is it possible that your upgrade failed, and then you attempted to re-run it without restoring from backup? If an upgrade fails it’s important to restore your database from a backup taken before the first upgrade attempt. Otherwise your database will be left in an inconsistent state somewhere between the two versions.

Regards,
Alec Smecher
Public Knowledge Project Team

Hello @asmecher

Thank you for your reply! Indeed there was the issue why the upgrade failed, there was an error within the $orphanedIds query, so I replaced the whole OJSv3_3_0UpgradeMigration.inc.php from the original version 3.3.0.8. After that, I was able to upgrade successfully from version 3.1.2.4 to 3.3.0.8.

All the best

1 Like