Problem with upgrading to OJS 3.3.0.3

Software Support
1

hello

I am trying to update to OJS 3.3.03 using Cpanel and i am receiving this messgae

A database error has occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘url’ in ‘field list’ (SQL: select url, navigation_menu_item_id from navigation_menu_items where context_id = 1)

How i can solve this Problem ?

Thank you

2

@gjphm I think you need to restore the database from backup and try again.
Best,
Ross

1 Like
3

Thanks for your reply

I already did that twice and same problem happen

4

Hello, the same problem
A database error has occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘url’ in ‘field list’ (SQL: select url, navigation_menu_item_id from navigation_menu_items where context_id = 1)

Fresh installation by Softaculous App Installer in Hostgator cPanel works well. When I disconnect this new database and connect the old one 3.2.1-4 to upgrade through the web interface then the upgrade stop and the above-mentioned database error occurred.

1 Like
5

@gjphm @potravinarstvo Maybe this link will be useful for research about details of database. As I know the new version (3.3.0) use Laravel/Illuminate library, the old version use DAO library.

6

after updating to 3.3.0.3 i get

HTTP ERROR 500

7

Hello, just wanted to share that I get the same database as @potravinarstvo in the final step of upgrading from OJS 3.2.1.2 to 3.3.0.3 via Softaculous:

A database error has occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘url’ in ‘field list’ (SQL: select url, navigation_menu_item_id from navigation_menu_items where context_id = 1)

I am adding to this thread to follow and help troubleshoot this issue. Thank you!

8

Hello, the same problem
A database error has occurred: SQLSTATE[42S22]…

How i can solve this Problem ?
Thank you

9

Yes, I have used the new config.inc.php from 3.3.0.3.
I just copy the base_url, database username, password, database name, files directory, SSL security to ON, repository_id, Recaptcha public, and private keys from old config.inc.php file.

After connecting the old database, the journal website is loaded without graphics. It is not possible to log in to the system (admin username and password are correct), the HTTP 500 Error occurs. When I switched installed to OFF in the config.inc.php, then the installation web interface is displayed and I start the upgrade process. Upgrade fails. When I try to upgrade again, the above-mentioned database error has occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘url’ in…

Will be this problem with the database upgrade solved in the further OJS version? Should we wait for the new OJS version, or should we solve this problem? What is the solution, what should we do? Thank you so much for your help. It is not only the problem of our database. As I read other comments, this problem occurs also in other journals.

1 Like
10

Hello,

I had the same problem: SQLSTATE[42S22]: Column not found. By the way, I remarked that this error starts to appear when I activate the default theme (it disappears if I deactivate the associated plugin).

I think I found the problem, actually the upgrade of the database (from 3.2.0 to 3.3.0.3), with command line, even if it looks like it has finished, with no error, when I look at the error_log (right after the upgrade, before lunching the website) I found this fatal error:
[26-Feb-2021 15:23:53 UTC] PHP Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 69632 bytes) in /home/directev/public_html/aem3303/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php on line 113

Then I created a new database (copying from the 3.2.0 clean backup), and used this command (allowing more memory, 800M!):

php -d memory_limit=800M tools/upgrade.php upgrade

and it worked!! I hope it will help some of you.

Said

1 Like
11

Hello, thank you. I have used: php -d memory_limit=800M tools/upgrade.php upgrade

ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table submission_file_revisions add constraint submission_file_revisions_submission_file_id_foreign foreign key (submission_file_id) references submission_files (submission_file_id))

OK after this message I reed the forum and found it should be an issue due to the MyISAM format of the tables, there should be InnoDB.

  1. I made a fresh copy of the production database by phpMyAdmin

  2. I checked the table status.

SHOW TABLE STATUS FROM pzjr_journal1upgrade3303;

I found all of my tables are in MyISAM form (…because we are using our database from 2007)
I decided to change the status of the problematic table:

ALTER TABLE submission_files ENGINE = InnoDB;

  1. I have opened the terminal, log-in to the server, moved to the upgrade folder and run the following command: php -d memory_limit=800M ./tools/upgrade.php upgrade

Upgraded successfully from 3.2.1.4 to 3.3.0.3.

After the upgrade, I have found one problem:

HTML galleys generated from XML JATS files are not loaded by eLens viewer

Error during loading. Please try again.

image

Please, how to solve this issue?

This is the Error log:
28-Feb-2021 09:43:29 America/Chicago] PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’ in /home1/pzjr/public_html/potravinarstvo/upgrade3303/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:78
Stack trace:
#0 /home1/pzjr/public_html/potravinarstvo/upgrade3303/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(78): PDO->prepare(‘SELECT\ti.*\n\t\t\tF…’, Array)
#1 /home1/pzjr/public_html/potravinarstvo/upgrade3303/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(361): Doctrine\DBAL\Driver\PDOConnection->prepare(Object(Illuminate\Database\Query\Expression))
#2 /home1/pzjr/public_html/potravinarstvo/upgrade3303/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(Object(Illuminate\Database\Query\Expression), Array)
#3 /home1/pzjr/public_html/potravinarstvo/upgrade3303/lib in /home1/pzjr/public_html/potravinarstvo/upgrade3303/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671

12

The same problem for me

13

Hi @potravinarstvo and @Kusmayadi_Kusmayadi

The error message says that database collation is mixed, see, e.g.: https://stackoverflow.com/search?tab=votes&q=1267%20Illegal%20mix%20of%20collations

14

The same problem for me when I use search function. Is there any specific solution?

15

PROBLEM SOLVED

The XML Galleys and some older article pages were not loaded after upgrade to 3.0.3.3. It was because
there was an Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’ in

Collations in my old database were in utf8_unicode_ci and they should be in utf8_general_ci

I log in to my Hostgator cpanel, and used phpMyAdmin, click on database.

Use SQL.
Firstly I changed the Table type from MyISAM to InnoDB.
I have used the following script:

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'your-db-name';

and following exactly this procedure: How to change your MyISAM DB to InnoDB using phpMyAdmin

Press the submit query button to see a list of tables to alter to InnoDB
Click the “+Options” link above the results, choose the “Full texts” option, and press the “Go” button in the shaeded area at the lower right
Above the results, tick the “Show all” checkbox
Copy all the queries using the “Copy to clipboard” link in the “Query results operations” box below the results
Paste the result into a text editor and copy all the lines starting with “ALTER TABLE” to your clipboard
Click the SQL tab above the results
Paste the ALTER TABLE statements into the textarea and press the “Go” button in the lower right shaded area

image

Then I changed the collation for all tables and columns:

image

XML galleys start loading, and all some Article subpages start loading.
THIS PROBLEM WAS SOLVED

NEW PROBLEMS

  • eLife Lens Article Viewer: figures in the XML GALLEYS are not displayed
  • XML galley publication date is -1 day, there is 27th of Feburary but it should be 28th of February
  • Saving any file takes too long.

image

And Figures are not displayed in the XML galleys

image

ERROR LOG:
[03-Mar-2021 10:10:17 America/Chicago] ojs2: 404 Not Found

image

Any idea how to solve this problem?

1 Like
16

Thank you Vitaliy, your advice help me to solve this problem, XML galleys start loading, but figures in XML galleys still not loading.

17

Hi @potravinarstvo,

See: Links to dependent files referenced in HTML/JATS XML galleys are broken · Issue #6801 · pkp/pkp-lib · GitHub

18

OK, at this moment I return back to 3.2.1.4 I will wait for the new PKP OJS release.

19

Hi @potravinarstvo, I have encountered the same problem, and I want to return to 3.2.1.4 as you do. How to do that? Can you give a brief description? Thank you for your help

20

Hello, my advice for you is: never did the changes on the production site. My standard working procedure is:

  • download the backup of the database (cPanel - backup)
  • make zip copy of the production OJS
  • copy the production database (PhpMyAdmin) name it as you want for example databasename_upgrade3303
  • assign the same user to the database (use MySQLDatabase)
  • install the new PKP OJS (I recommending using Softaculous app installer). Name the folder to the new installation as you want (for example upgrade3303)
  • Delete the PUBLIC folder from the new installation
  • Copy the old PUBLIC folder from the production site to the new installation
  • Copy the config.inc.php from the old installation to the new installation
  • Change the settings in the config.inc.php
    - base_url = “https://yoursitename.com/upgrade3303”
    - Database settings: change the database name = productiondatabasename_upgrade3303
    - change the installed to: off
  • Now you should upgrade your database to new version (you can do it through web interface or by terminal)
  • After upgrade change the installed to: on
  • Log in to the PKP OJS and change the website template as you have on the production site
  • Apply custom modification to your script
  • Upgrade plugins (only those you need)
  • Usually at this stage I am going to browser and delete the browsing data and cookies and all cashed sites.
  • Now you can check if everything is OK, check all sites, subsites… make a submission, upload one testing article. Check the galleys if they are OK, all URL should be loaded correctly, XML galleys should work, figures and tables should be loaded correctly…
  • If everything is OK, you can change this site to the production:
  • Rename the production site folder (at this moment your production site is down)
  • Copy the production database as productiondatabasename_backup_date
  • Delete the production database
  • Copy the new installed database (change the name… it should be the same as you had on production database)
  • Change the settings in the config.inc.php
    - base_url = “https://yoursitename.com/productionfoldername”
    - Database settings: change the database name = productiondatabasename
  • Now your site start working
  • At this moment you can decide to download old backups and retain them in the server for some period… if you found major problems in late… then you can return back

Returning back means:

  • unzip the previous backup of the production folder
  • if you have a copy on the server, use this copy, if you have downoladed database backup then you should upload it to the server (use cPanel, Backup, Restore MYSQL Database Backup), Use cPanel, MySQLDatabase and add the user to this database)
  • Now your site starts work

PS: usually 1x/month I am downloading the backup of the FILES folder (not all files… only the last100 article submissions). At the end of the year, I am downloading the whole FILES folder.

What concerns to the permanent article archivation. we have a contract with the University Library of The Slovak Republic in Bratislava. I am merging all articles published during the year to one PDF file and send this file to this library for permanent archivation. Also, we have another two archives: one in google drive and the second one in local HDD.

My workflow is probably very amateur, I’m not a programmer, my skills are limited, I only know the basic principles and basic database operations. I solve complicated problems with an expert. However, if everything goes smoothly, I can upgrade the OJS version myself without the help of an expert.

2 Likes