PHP Fatal error: Uncaught Exception: DB Error: Unknown column when upgrading OJS 2.4 to 3.2

Software Support
1

[Hi,
Please i am trying to upgrade from ojs 2.4 to 3.2 and I have been experiencing different challenges which I have been able to solve with past related post in the forum. But below is the present error I am getting and I’m yet to be able to find a way round it.
Someone should kindly help out please. Meanwhile I have backup of both the database and files.
Thank you

21-Jul-2020 21:11:33 UTC] PHP Fatal error: Uncaught Exception: DB Error: Unknown column ‘a.setting_type’ in ‘where clause’ Query: SELECT a., b.context_id FROM submission_settings a, submissions b WHERE a.setting_name = ‘fileName’ AND a.locale = ? AND a.setting_value LIKE ? AND a.setting_type = ‘string’ AND b.submission_id = a.submission_id in /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), 'SELECT a., b.c…’)
#1 /home/wharcajrh/public_html/classes/install/Upgrade.inc.php(2751): DAO->retrieve(‘SELECT a.*, b.c…’, Array)
#2 /home/wharcajrh/public_html/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->migrateSRLocale(Object(Upgrade), Array)
#3 /home/wharcajrh/public_html/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction(Array)
#4 /home/wharcajrh/public_html/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#5 /home/wharcajrh/public_html/lib/pkp/classes/install/ in /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php on line 703
[21-Jul-2020 21:20:49 UTC] PHP Fatal error: Uncaught Exception: DB Error: Unknown column ‘assoc_type’ in ‘where clause’ Query: UPDATE email_templates SET assoc_type = 530 WHERE assoc_type = ‘526’ in /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php(231): DAO->handleError(Object(ADODB_mysqli), ‘UPDATE email_te…’)
#1 /home/wharcajrh/public_html/classes/install/Upgrade.inc.php(742): DAO->update(‘UPDATE email_te…’)
#2 /home/wharcajrh/public_html/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->syncSeriesAssocType(Object(Upgrade), Array)
#3 /home/wharcajrh/public_html/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction(Array)
#4 /home/wharcajrh/public_html/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#5 /home/wharcajrh/public_html/lib/pkp/classes/install/form/UpgradeForm.inc.php(42): Installer->execute()
#6 /home/wharcajrh/public_html/lib/pkp/pages/install/InstallHandler.inc.php(106): UpgradeForm->execute( in /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php on line 703

2

Hi @leyeaa,

Are you trying to upgrade to OJS 3.2.0? I’d recommend working with the latest release (currently 3.2.1-1) instead – some upgrade issues have been resolved there, among other things.

Regards,
Alec Smecher
Public Knowledge Project Team

3

Hello and thanks for your response.

The version I am using for the upgrade is actually 3.2.1-1.

4

Hello @asmecher
Please i have drop the database and files and replace with the backup but still getting the same error message.

Kindly assist please.
Thank you.

5

Hi @leyeaa,

Are you still getting the message…

PHP Fatal error: Uncaught Exception: DB Error: Unknown column ‘assoc_type’ in ‘where clause’ Query: UPDATE email_templates SET assoc_type = 530 WHERE assoc_type = ‘526’ in /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php:703

…or something else?

Regards,
Alec Smecher
Public Knowledge Project Team

6

@asmecher, Thank you for your response. Below are the error i’m getting from the log now.

#0 /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), ‘SELECT * FROM j…’)
#1 /home/wharcajrh/public_html/lib/pkp/classes/context/ContextDAO.inc.php(64): DAO->retrieve(‘SELECT * FROM j…’, ‘ajrh’)
#2 /home/wharcajrh/public_html/lib/pkp/classes/core/PKPRouter.inc.php(252): ContextDAO->getByPath(‘ajrh’)
#3 /home/wharcajrh/public_html/lib/pkp/classes/core/PKPRequest.inc.php(760): PKPRouter->getContext(Object(Request), 1)
#4 /home/wharcajrh/public_html/classes/core/Request.inc.php(64): PKPRequest->_delegateToRouter(‘getContext’, 1)
#5 /home/wharcajrh/public_html/lib/pkp/classes/plugins/LazyLoadPlugin.inc.php(102): Request->getContext()
#6 /home/wharcajrh/public_html/lib/pkp/classes/plugins/LazyLoadPlugin.inc.php(63): LazyLoadPlugin->getCurrentCon in /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php on line 703
[23-Jul-2020 15:32:00 UTC] WARNING: Unable to find a match for “73-174-1-RV.pdf” in “/home/wharcajrh/files/journals/1//articles/73/”. Skipping this file.
[23-Jul-2020 15:32:00 UTC] WARNING: Unable to find a match for “76-183-1-RV.pdf” in “/home/wharcajrh/files/journals/1//articles/76/”. Skipping this file.
[23-Jul-2020 15:32:00 UTC] WARNING: Unable to find a match for “82-194-1-RV.pdf” in “/home/wharcajrh/files/journals/1//articles/82/”. Skipping this file.

#0 /home/wharcajrh/public_html/lib/pkp/lib/vendor/adodb/adodb-php/adodb.inc.php(683): ADODB_mysql->_connect(‘localhost’, ‘ojs2’, ‘’, ‘’)
#1 /home/wharcajrh/public_html/lib/pkp/classes/db/DBConnection.inc.php(162): ADOConnection->Connect(‘localhost’, ‘ojs2’, ‘’, NULL, false)
#2 /home/wharcajrh/public_html/lib/pkp/classes/db/DBConnection.inc.php(137): DBConnection->connect()
#3 /home/wharcajrh/public_html/lib/pkp/classes/db/DBConnection.inc.php(124): DBConnection->initConn()
#4 /home/wharcajrh/public_html/lib/pkp/classes/db/DBConnection.inc.php(56): DBConnection->initCustomDBConnection(‘mysql’, ‘localhost’, ‘ojs2’, ‘’, NULL, false, ‘utf8’)
#5 /home/wharcajrh/public_html/lib/pkp/classes/install/PKPInstall.inc.php(77): DBConnection->__construct(‘mysql’, ‘localhost’, ‘ojs2’, ‘’, NULL, false, ‘utf8’)
#6 /home/wharcajrh/public_html/lib/pkp/classes/i in /home/wharcajrh/public_html/lib/pkp/lib/vendor/adodb/adodb-php/drivers/adodb-mysql.inc.php on line 461
Thank you in advance for your support

7

Hi @leyeaa,

It looks like you’ve posted the stack traces, but not the error messages themselves. Those should appear just above the line starting with #0.

Regards,
Alec Smecher
Public Knowledge Project Team

8

Thank you @asmecher
Below are the error messages
[23-Jul-2020 15:33:41 UTC] PHP Fatal error: Uncaught Exception: DB Error: Unknown column ‘a.setting_type’ in ‘where clause’ Query: SELECT a.*, b.context_id FROM submission_settings a, submissions b WHERE a.setting_name = ‘fileName’ AND a.locale = ? AND a.setting_value LIKE ? AND a.setting_type = ‘string’ AND b.submission_id = a.submission_id in /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php:703

AND

[23-Jul-2020 15:23:37 UTC] PHP Fatal error: Uncaught Exception: DB Error: Access denied for user ‘ojs’@‘localhost’ (using password: YES) Query: SELECT * FROM journals WHERE path = ? in /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php:703

Meanwhile, the username set for the database is not the default ojs and I set the driver to mysqli not mysql.

Kindly assist as I’m getting frustrated already

9

Hello @asmecher Below is the only error i’m getting now, kindly help out. Thanks
[27-Jul-2020 10:22:10 UTC] PHP Fatal error: Uncaught Exception: DB Error: Unknown column ‘a.setting_type’ in ‘where clause’ Query: SELECT a.*, b.context_id FROM submission_settings a, submissions b WHERE a.setting_name = ‘fileName’ AND a.locale = ? AND a.setting_value LIKE ? AND a.setting_type = ‘string’ AND b.submission_id = a.submission_id in /home/wharcajrh/public_html/lib/pkp/classes/db/DAO.inc.php:703

10

Hi @leyeaa,

It looks like you’re trying to run an upgrade on a database that has already had a failed upgrade run on it. Do you have a backup taken from before you first attempted to run the upgrade?

Regards,
Alec Smecher
Public Knowledge Project Team

11

Hi @asmecher and thank you for your response.
I have a backup of the database and files before attempting the upgrade at first.

I have re-setup from the backup database and files after the initial failed upgrade and above is the message i’m getting now.

I’ve reverted back to the old version 2.4 with the backup files and database now just to keep the journal running and it’s running fine.

Kindly assist, I really need to do the upgrade.

Thank you.

12

Hi @leyeaa,

Could you try applying this change before running the upgrade script?

diff --git a/classes/install/Upgrade.inc.php b/classes/install/Upgrade.inc.php
index eb32bb69f1..fcac1a4197 100644
--- a/classes/install/Upgrade.inc.php
+++ b/classes/install/Upgrade.inc.php
@@ -2748,7 +2748,7 @@ class Upgrade extends Installer {
 
 		// Consider article cover images
 		// Note that the locale column values are already changed above
-		$settingValueResult = $journalSettingsDao->retrieve('SELECT a.*, b.context_id FROM submission_settings a, submissions b WHERE a.setting_name = \'fileName\' AND a.locale = ? AND a.setting_value LIKE ? AND a.setting_type = \'string\' AND b.submission_id = a.submission_id', array($newLocale, '%' .$oldLocale .'%'));
+		$settingValueResult = $journalSettingsDao->retrieve('SELECT a.*, b.context_id FROM submission_settings a, submissions b WHERE a.setting_name = \'fileName\' AND a.locale = ? AND a.setting_value LIKE ? AND b.submission_id = a.submission_id', array($newLocale, '%' .$oldLocale .'%'));
 		while (!$settingValueResult->EOF) {
 			$row = $settingValueResult->getRowAssoc(false);
 			$oldCoverImage = $row['setting_value'];
@@ -2767,7 +2767,7 @@ class Upgrade extends Installer {
 		// blockContent (from a custom block plugin), additionalInformation (from objects for review plugin)
 		$pluginSettingsDao = DAORegistry::getDAO('PluginSettingsDAO'); /* @var $pluginSettingsDao PluginSettingsDAO */
 		$settingNames = "('blockContent', 'additionalInformation')";
-		$settingValueResult = $pluginSettingsDao->retrieve('SELECT * FROM plugin_settings WHERE setting_name IN ' .$settingNames .' AND setting_value LIKE ? AND setting_type = \'object\'', array('%' .$oldLocaleStringLength .':"' .$oldLocale .'%'));
+		$settingValueResult = $pluginSettingsDao->retrieve('SELECT * FROM plugin_settings WHERE setting_name IN ' .$settingNames .' AND setting_value LIKE ?', array('%' .$oldLocaleStringLength .':"' .$oldLocale .'%'));
 		while (!$settingValueResult->EOF) {
 			$row = $settingValueResult->getRowAssoc(false);
 			$arraySettingValue = $pluginSettingsDao->getSetting($row['context_id'], $row['plugin_name'], $row['setting_name']);

If you’re able to confirm that this works, I can get the change committed for the next release of OJS.

Thanks,
Alec Smecher
Public Knowledge Project Team

13

Hi @asmecher
Thank you for your help. Please how do I apply the above change before running the upgrade script.

The server is not hosted by me and I only have access through Cpanel.
I could see the Class “class Upgrade extends Installer” in classes/install/Upgrade.inc.php but comparing the content of the file and modifying it correctly line by line with what you posted above is what I can seems to get that easily.

Can you explain further a bit please?

I really appreciate your support. Thank you
Thank you

14

Hi @leyeaa,

That’s a patch file; you can apply it by using the GNU patch tool, which is available on most servers, or you can make the changes yourself. Lines starting with - are removals, and lines starting with + are additions. The rest are for context. So you see the change proposes two changes to classes/install/Upgrade.inc.php, each removing a line and replacing it with a modified version.

Regards,
Alec Smecher
Public Knowledge Project Team

15

Hi @asmecher,

I was just doing an upgrade from OJS 2.4.8-1 to OJS 3.2.1-1 to migrate a journal to our instance and ran into the same issue as @leyeaa. I can confirm that the patch you posted worked for me.

Best,
Alex