Hello, the same problem
A database error has occurred: SQLSTATE[42S22]…
How i can solve this Problem ?
Thank you
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
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
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.
-
I made a fresh copy of the production database by phpMyAdmin
-
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;
- 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.
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
The same problem for me
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
The same problem for me when I use search function. Is there any specific solution?
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
Then I changed the collation for all tables and columns:
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.
And Figures are not displayed in the XML galleys
ERROR LOG:
[03-Mar-2021 10:10:17 America/Chicago] ojs2: 404 Not Found
Any idea how to solve this problem?
1 Like
Thank you Vitaliy, your advice help me to solve this problem, XML galleys start loading, but figures in XML galleys still not loading.
OK, at this moment I return back to 3.2.1.4 I will wait for the new PKP OJS release.
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
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
(it is not my production site)
I´ve made:
cd /usr/share/ojs/tools
php -d memory_limit=800M upgrade.php upgrade
And, It works!
Thanks
Hi, @cerceau where to edit/add what you’ve made?
Please help with location, such /public_html/… etc
Thanks, best regards
1 Like
Till now, when i tried to upgrade, this message shows:
Fatal error : Uncaught Error: Call to a member function getConnection() on null in /home/u9461685/public_html/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php:74 Stack trace: #0 /home/u9461685/public_html/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php(200): Illuminate\Database\Capsule\Manager::connection() #1 /home/u9461685/public_html/lib/pkp/classes/db/DAO.inc.php(65): Illuminate\Database\Capsule\Manager::__callStatic(‘raw’, Array) #2 /home/u9461685/public_html/lib/pkp/classes/context/ContextDAO.inc.php(63): DAO->retrieve(‘SELECT * FROM j…’, Array) #3 /home/u9461685/public_html/lib/pkp/classes/core/PKPRouter.inc.php(252): ContextDAO->getByPath(‘ijiis’) #4 /home/u9461685/public_html/lib/pkp/classes/core/PKPRequest.inc.php(760): PKPRouter->getContext(Object(Request), 1) #5 /home/u9461685/public_html/classes/core/Request.inc.php(64): PKPRequest->_delegateToRouter(‘getContext’, 1) #6 /home/u9461685/public_html/lib/pkp/classes/template/PKPTemplateManager.inc. in /home/u9461685/public_html/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php on line 74
How to solve it? Thanks for the help
Hi, I am trying to upgrade from ojs 3.2 to ojs 3.3, however I am getting error below. Appreciate some help
Fatal error : Uncaught Error: Call to a member function getVersionString() on bool in /var/www/html/ojs/cache/t_compile/13376d7934b5f251160733e5e3c8b2ff6a1e1f24^8e26d4d5b957a6848a96c58ac27d2fddb2c40f9a_0.app.installinstall.tpl.php:125 Stack trace: #0 /var/www/html/ojs/lib/pkp/lib/vendor/smarty/smarty/libs/sysplugins/smarty_internal_runtime_inheritance.php(248): Block_16628595776040663bf016a7_29694503->callBlock() #1 /var/www/html/ojs/lib/pkp/lib/vendor/smarty/smarty/libs/sysplugins/smarty_internal_runtime_inheritance.php(184): Smarty_Internal_Runtime_Inheritance->callBlock() #2 /var/www/html/ojs/lib/pkp/lib/vendor/smarty/smarty/libs/sysplugins/smarty_internal_runtime_inheritance.php(186): Smarty_Internal_Runtime_Inheritance->process() #3 /var/www/html/ojs/lib/pkp/lib/vendor/smarty/smarty/libs/sysplugins/smarty_internal_runtime_inheritance.php(156): Smarty_Internal_Runtime_Inheritance->process() #4 /var/www/html/ojs/cache/t_compile/13376d7934b5f251160733e5e3c8b2ff6a1e1f24^164bd5a4f3891532cf93e9d19911fe9cb3308efd in /var/www/html/ojs/cache/t_compile/13376d7934b5f251160733e5e3c8b2ff6a1e1f24^8e26d4d5b957a6848a96c58ac27d2fddb2c40f9a_0.app.installinstall.tpl.php on line 125
I have the same problem also.
I upgrade the OJS and it appears: 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’m using softaculous appp.
So can anyone teach me step-by-step to solve this problems?
Thanks in advance.
PROBLEM SOLVED
Fresh installation in cPanel by Softacullous 3.3.0.4
What concerns to the database, I have followed migrated 3.2.1.4 to 3.3.0.4
- In old database I have changed the tables and content collation from utf8_unicode_ci to utf8_general_ci
- ALTER TABLE submission_files ENGINE = InnoDB;
- using terminal: php -d memory_limit=800M ./tools/upgrade.php upgrade
Thank you for help and support.
1 Like