UPGRADE 3.0 to 3.2 - Unknown column 'setting_type' in 'where clause' Query: DELETE FROM submission_settings

Hi everyone,

I am working on a OJS upgrading process of a 3.0.0.0 installation. The last upgrade on this database was on 13/10/2021, so I believe there is no backup from 2481 version to 3000.

An error that seems to be related with this topic PHP Fatal error: Uncaught Exception: DB Error: Unknown column when upgrading OJS 2.4 to 3.2 has occured, although, solution seems not to be the same.

php error log on 3000 > 3214 upgrading:

[31-Oct-2023 15:02:10 America/Sao_Paulo] PHP Fatal error:  Uncaught Exception: DB Error: Unknown column 'setting_type' in 'where clause' Query: DELETE FROM submission_settings WHERE setting_name = 'coverImage' AND setting_type = 'object' in ./3214/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 ./3214/lib/pkp/classes/db/DAO.inc.php(231): DAO->handleError(Object(ADODB_mysqli), 'DELETE FROM sub...')
#1 ./3214/classes/install/Upgrade.inc.php(1881): DAO->update('DELETE FROM sub...')
#2 ./3214/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->localizeArticleCoverImages(Object(Upgrade), Array)
#3 ./3214/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction(Array)
#4 ./3214/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#5 ./3214/lib/pkp/classes/cliTool/UpgradeTool.inc.php(8 in ./3214/lib/pkp/classes/db/DAO.inc.php on line 703

The table submission_settings has only 1 entry on setting_name = coverImage and 1 entry on setting_name = coverImageAltText, both related to the same submission_id. I tried run the upgrade removing these entries, but result was the same.

I tried to upgrade to another versions too but received differents errors.

Error on 3000 > 33015 upgrading:

[31-Oct-2023 09:39:24 America/Sao_Paulo] PHP Fatal error:  Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'setting_type' in 'where clause' in ./33015/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:80
Stack trace:
#0 ./33015/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(80): PDO->prepare('DELETE FROM sub...', Array)
#1 ./33015/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(485): Doctrine\DBAL\Driver\PDOConnection->prepare('DELETE FROM sub...')
#2 ./33015/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}('DELETE FROM sub...', Array)
#3 ./33015/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('DELETE FROM sub in ./33015/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671

Error on 3000 > 3124 upgrading:

[31-Oct-2023 18:11:18 America/Los_Angeles] PHP Fatal error:  Uncaught Error: Call to a member function getUsername() on null in ./3124/lib/pkp/classes/submission/SubmissionFile.inc.php:618
Stack trace:
#0 ./3124/classes/install/Upgrade.inc.php(1022): SubmissionFile->_generateName()
#1 ./3124/lib/pkp/classes/install/Installer.inc.php(421): Upgrade->setFileName(Object(Upgrade), Array)
#2 ./3124/lib/pkp/classes/install/Installer.inc.php(265): Installer->executeAction(Array)
#3 ./3124/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#4 ./3124/lib/pkp/classes/cliTool/UpgradeTool.inc.php(88): Installer->execute()
#5 ./3124/lib/pkp/classes/cliTool/UpgradeTool.inc.php(64): UpgradeTool->upgrade()
#6 ./3124/tools/upgrade.php(34): UpgradeTool->execute()
#7 {main}
  thrown in ./3124/lib/pkp/classes/submission/SubmissionFile.inc.php on line 618

These erros indicates that there is an issue has occured in a previous upgrading? Is this possible to be solved?

php version: 7.4
tables was converted from MyISAM to InnoDB and collation from latin1_swedish_ci to utb8_general_ci

Hopeful regards

Hi @asmecher @Vitaliy ,

Any help, pls? Ty

Hi @geniusdesign,

Debugging your error message on attempting to upgrade to 3.3.0-15: does your submission_settings table in OJS 3.0.0-0 have a setting_type column before you attempt to run the upgrade?

Debugging your attempt to upgrade to 3.1.2-4: It looks like you have some incomplete data. Look in your submission_files table for entries with a user_id that doesn’t exist in the users table. The following query will identify these:

SELECT sf.* FROM submission_files sf LEFT JOIN users u ON (sf.uploader_user_id = u.user_id) WHERE u.user_id IS NULL;

Hopefully you will just have a few of these that can be updated to existing user IDs.

Regards,
Alec Smecher
Public Knowledge Project Team

Thank you very much for answering, @asmecher

Yes, it does. coverImage setting_value is a string setting_type.

The query you wrote returned 35k results. submission_files table has about 41k entries. :dizzy_face:

user_id should be a column? This column doesn’t exist on submission_files table. There is a uploader_user_id column… I believe it is the same. All entries seems to have NULL value at this column.

How bad is this?

Thank you once again

Hi @geniusdesign,

Hmm, let me review the database schema and get back to you before you start modifying things – I think I’ve gotten a detail wrong here.

Regards,
Alec Smecher
Public Knowledge Project Team

Ty very much for your kindly attention, @asmecher.

I made an attempt setting the admin user id (=“1”) on uploader_user_id column overwriting NULL values but I got the same error.

Hi @geniusdesign,

With apologies for the delay in looking into this –

The upgrade from OJS 2.x to 3.x should set the uploader_file_id column as part of the upgrade (the setFileUploader function in classes/install/Upgrade.inc.php). So the fact that you appear to be missing a lot of data in that column before the 2.x to 3.x upgrade suggests that something happened with your upgrade to 3.0.

Are you dealing with an installation that has a lot of journals in it, or just one or two? If just a few, you can fill in the missing data as follows for each journal…

  • Identify the user ID of someone who is a journal manager in the journal
  • Run a SQL query to update submission files:
    UPDATE submission_files sf, submissions s
    SET sf.uploader_user_id = USER_ID_GOES_HERE
    WHERE sf.uploader_user_id IS NULL AND
      sf.submission_id = s.submission_id AND
      s.context_id = JOURNAL_ID_GOES_HERE
    
    …replacing USER_ID_GOES_HERE and JOURNAL_ID_GOES_HERE with the appropriate numbers.

When you are done, the query on the above comment should return no results.

(And I have to say it – please take a good backup before working with the database directly!)

Regards,
Alec Smecher
Public Knowledge Project Team

Hi Alec! Ty for the answer, I’ll try your tips.

There are 11 journals, almost 40 Gb on files_dir and 1 Gb on database.


I have some news about this issue, but still could not finish successfuly the upgrade.

I used the query below to find submission_files entries without an user on users table:

SELECT * FROM `submission_files` WHERE uploader_user_id IS NULL OR uploader_user_id NOT IN (SELECT user_id FROM User);

Found 2 entries, both from the same inexisting user_id. I have filled user_id as 1 and ran the upgrade.

After this, process has foward beyond issue step, even with NULLentries on uploader_user_id, but I have found a new issue:

...
[code: Installer Installer::moveCSSFiles]
[code: Installer Installer::moveReviewerFiles]
<h1>There are no readable files in this directory tree. Are safe mode or open_basedir active?</h1>

files_dir exists and has write permission. I can’t find a reason to this error yet :confused:

Hi @asmecher ,

It was very hard to follow as you suggested :confused: there are too many empty submissions and too many journals.

I could overcome this step but I am now stucked on something related to the files_dir directory.

On prompt, I have the message above and on php error the following:

[20-Nov-2023 16:30:10 UTC] ERROR: Unable to move "./ojs-files/journals/3//articles/4206/submission/review/4206-1-14399-1-4-20140904.docx" to "./ojs-files/journals/3//articles/4206/submission/review/attachment/4206-1-14399-1-5-20140904.docx".
[20-Nov-2023 16:30:10 UTC] PHP Warning:  rename(./ojs-files/journals/3//articles/4206/submission/review/4206-1-14364-1-4-20140828.doc,./ojs-files/journals/3//articles/4206/submission/review/attachment/4206-1-14364-1-5-20140828.doc): Arquivo ou diretório não encontrado in ./ojs-3124/classes/install/Upgrade.inc.php on line 1998
[20-Nov-2023 16:30:10 UTC] ERROR: Unable to move "./ojs-files/journals/3//articles/4206/submission/review/4206-1-14364-1-4-20140828.doc" to "./ojs-files/journals/3//articles/4206/submission/review/attachment/4206-1-14364-1-5-20140828.doc".
[20-Nov-2023 16:30:10 UTC] PHP Warning:  rename(./ojs-files/journals/13//articles/4117/submission/review/4117-1-14433-1-4-20140912.pdf,./ojs-files/journals/13//articles/4117/submission/review/attachment/4117-1-14433-1-5-20140912.pdf): Arquivo ou diretório não encontrado in ./ojs-3124/classes/install/Upgrade.inc.php on line 1998
[20-Nov-2023 16:30:10 UTC] ERROR: Unable to move "./ojs-files/journals/13//articles/4117/submission/review/4117-1-14433-1-4-20140912.pdf" to "./ojs-files/journals/13//articles/4117/submission/review/attachment/4117-1-14433-1-5-20140912.pdf".
[20-Nov-2023 16:30:10 UTC] PHP Warning:  rename(./ojs-files/journals/3//articles/4253/submission/review/4253-1-14427-1-4-20140911.pdf,./ojs-files/journals/3//articles/4253/submission/review/attachment/4253-1-14427-1-5-20140911.pdf): Arquivo ou diretório não encontrado in ./ojs-3124/classes/install/Upgrade.inc.php on line 1998
[20-Nov-2023 16:30:10 UTC] ERROR: Unable to move "./ojs-files/journals/3//articles/4253/submission/review/4253-1-14427-1-4-20140911.pdf" to "./ojs-files/journals/3//articles/4253/submission/review/attachment/4253-1-14427-1-5-20140911.pdf".
[20-Nov-2023 16:30:10 UTC] PHP Warning:  rename(./ojs-files/journals/3//articles/3934/submission/review/3934-1-14421-1-4-20140910.docx,./ojs-files/journals/3//articles/3934/submission/review/attachment/3934-1-14421-1-5-20140910.docx): Arquivo ou diretório não encontrado in ./ojs-3124/classes/install/Upgrade.inc.php on line 1998
[20-Nov-2023 16:30:10 UTC] ERROR: Unable to move "./ojs-files/journals/3//articles/3934/submission/review/3934-1-14421-1-4-20140910.docx" to "./ojs-files/journals/3//articles/3934/submission/review/attachment/3934-1-14421-1-5-20140910.docx".
[20-Nov-2023 16:30:10 UTC] ERROR: Reviewer files with ID 16630 from review assignment 2601 could not be found in the database table submission_files
[20-Nov-2023 16:30:10 UTC] ERROR: Reviewer files with ID 18091 from review assignment 2961 could not be found in the database table submission_files
[20-Nov-2023 16:30:10 UTC] ERROR: Reviewer files with ID 18414 from review assignment 3078 could not be found in the database table submission_files
[20-Nov-2023 16:30:10 UTC] PHP Warning:  rename(./ojs-files/journals/3//articles/5114/submission/review/5114-1-19062-1-4-20161014.do,./ojs-files/journals/3//articles/5114/submission/review/attachment/5114-1-19062-1-5-20161014.do): Arquivo ou diretório não encontrado in ./ojs-3124/classes/install/Upgrade.inc.php on line 1998
[20-Nov-2023 16:30:10 UTC] ERROR: Unable to move "./ojs-files/journals/3//articles/5114/submission/review/5114-1-19062-1-4-20161014.do" to "./ojs-files/journals/3//articles/5114/submission/review/attachment/5114-1-19062-1-5-20161014.do".
[20-Nov-2023 16:30:10 UTC] PHP Warning:  rename(./ojs-files/journals/8//articles/5635/submission/review/5635-37-19870-1-4-20170225.txt,./ojs-files/journals/8//articles/5635/submission/review/attachment/5635-37-19870-1-5-20170225.txt): Arquivo ou diretório não encontrado in ./ojs-3124/classes/install/Upgrade.inc.php on line 1998
[20-Nov-2023 16:30:10 UTC] ERROR: Unable to move "./ojs-files/journals/8//articles/5635/submission/review/5635-37-19870-1-4-20170225.txt" to "./ojs-files/journals/8//articles/5635/submission/review/attachment/5635-37-19870-1-5-20170225.txt".
[20-Nov-2023 16:30:10 UTC] ojs2: There are no readable files in this directory tree. Are safe mode or open_basedir active?

There is about 1500 lines with this kind of error and warning. I could not check all of those lines, but I have checked some submissions by ID and really does not exist anymore.

Hi @geniusdesign,

Is your files_dir really set to ojs-files? If so (unless you’ve protected your files directory from direct access) that’s a dangerous configuration as the contents of that directory can be accessed directly via the web server (rather than via OJS). You should set files_dir to something outside the web root, and move the directory there on the filesystem.

The OJS 3.2.x to 3.3.x upgrade renames/rearranges the files directory’s contents. If you’re trying to run an upgrade without restoring the files directory from backup, that’s probably the cause.

Regards,
Alec Smecher
Public Knowledge Project Team

files_dir is outside OJS root directory.

I am running a 3.1.2.4 upgrade. The original topic title cited 3.0 to 3.2 upgrade, but I also shared some logs from 3.1 and 3.3 try. After your explanation on 3.1 upgrading log, I am working on this version.

What could be the causes for the message There are no readable files in this directory tree. Are safe mode or open_basedir active??

Hi @geniusdesign,

It comes from this line of code, which is attempting to recursively make a directory; it tends to crop up when there’s a file permission problem.

Regards,
Alec Smecher
Public Knowledge Project Team