[OJS 3.1.2.0 to 3.2] Upgrade Error - Duplicate column name ‘email_id’

asmecher · May 5, 2020, 11:24pm

Hi all,

Some folks have reported this issue in OJS 3.2.0-3 as well, and I’ve tracked down a data problem that can cause it. If you have custom-written email templates that have entries in the email_templates_data table, but don’t have corresponding entries in email_templates, then you can encounter the message during upgrade:

[data: dbscripts/xml/upgrade/3.2.0_update.xml]
ERROR: Upgrade failed: DB: Column 'email_id' cannot be null

To create the missing entries in your email_templates table, restore from backup, and before executing the upgrade script run the following SQL statement:

INSERT IGNORE INTO email_templates (email_key, assoc_type, assoc_id, enabled) SELECT etd.email_key, etd.assoc_type, etd.assoc_id, 1 FROM email_templates_data etd;

Regards,
Alec Smecher
Public Knowledge Project Team

Lazar_Stosic · May 6, 2020, 9:49am

Hi @asmecher,

I also have problem to upgrade to latest OJS with error>

A database error has occurred: Duplicate column name ‘email_id’

I read above post but nothing solve the problem.
In error.log received:

[06-May-2020 11:42:42 Europe/Belgrade] Duplicate column name ‘email_id’

I used Open Journal Systems 3.1.2.4, php 7.3

Any idea?

Regards,
Lazar

Lazar_Stosic · May 6, 2020, 10:23am

Hi @asmecher,

I can send you my database if you are interested.

asmecher · May 6, 2020, 3:37pm

Hi @Lazar_Stosic,

Did you try this solution? What happened?

Regards,
Alec Smecher
Public Knowledge Project Team

Lazar_Stosic · May 6, 2020, 4:09pm

Hi @asmecher,

I did that and cannot run this SQL statement. Received:

#1054 - Unknown column ‘assoc_type’ in ‘field list’

By the way, I do not have custom-written email templates.

asmecher · May 6, 2020, 4:15pm

Hi @Lazar_Stosic,

Are you running the query on your 3.2.0 database (after upgrade), or your 3.1.2 database (before upgrade)?

Regards,
Alec Smecher
Public Knowledge Project Team

Lazar_Stosic · May 6, 2020, 4:19pm

Hi @asmecher,

I run query on my old database. After fail backup I back up 3.1.2, old database.

asmecher · May 6, 2020, 4:28pm

Hi @Lazar_Stosic,

Do your email_templates_data and email_templates tables in the OJS 3.1.2 database have assoc_type and assoc_id tables?

Regards,
Alec Smecher
Public Knowledge Project Team

Lazar_Stosic · May 7, 2020, 9:21am

Hi @asmecher,

email_templates_data has both but email_templates does not have.

adm_sub · May 8, 2020, 9:40am

Dear community,
we also encounter the same problems when updating from OJS version 3.1.2.4 to 3.2.0.3 via the Full Package method.

PHP version: 7.3
MySQL version: 5.6.47

When updating, we receive the following error message first:

Table 'temp_authors' already exists

Then, after again clicking on the update-button:
Duplicate column name 'email_id'

We first tried the update with our modified email templates, i.e. the tables email-templates and email-templates-data contain values. Then we reset the email templates via “Workflow-Settings”-“Emails”—“Reset All Templates”. The error occurred in both update procedures.

The error Duplicate column name 'email_id' seems to refer to the column in the table email_templates_data. Because after renaming this column for testing purposes, the following error message occurred: Unknown column 'et.assoc_type' in 'on clause

We are very grateful for any tips and help!

Kind regards,
Michael

asmecher · May 8, 2020, 2:47pm

Hi @adm_sub,

The message…

Table 'temp_authors' already exists

…suggests that you’ve already tried an upgrade on that database, and it has failed. When an upgrade fails, it’s important to restore from backup; when an upgrade fails it leaves the database partway between the two versions and an upgrade should not be tried again on that same database.

Regards,
Alec Smecher
Public Knowledge Project Team

asmecher · May 8, 2020, 5:14pm

Hi @Lazar_Stosic,

Hmm, an OJS 3.1.2 database should have assoc_type and assoc_type columns on the email_templates table. Has this database undergone a failed upgrade that was not restored from backup or something similar?

Regards,
Alec Smecher
Public Knowledge Project Team

Lazar_Stosic · May 9, 2020, 6:59am

Hi @asmecher,
Yes. After database undergone a failed upgrade I back previous database, which I made before upgrade. This (old) database works perfect. Now we have problem. I will try again to backup old version of database, before made backup.
And yes. After I backup old database now have all tables.
Regards,
Lazar

Lazar_Stosic · May 10, 2020, 3:33pm

Hi @asmecher,

I tried with old database, new OJS from beginning and still received same errors. In error.log file I received:

[10-May-2020 17:24:24 Europe/Belgrade] PHP Fatal error: Uncaught Exception: DB Error: Unknown column ‘a.publication_id’ in ‘where clause’ Query: select *, p.locale as submission_locale from authors as a left join publications as p on a.publication_id = p.publication_id where a.publication_id in (?) order by a.seq asc in /home/ijcadsee/public_html/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/ijcadsee/public_html/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), ‘select *, p....') #1 /home/ijcadsee/public_html/lib/pkp/classes/db/DAO.inc.php(201): DAO->retrieve('select *, p.…’, Array, false)
#2 /home/ijcadsee/public_html/lib/pkp/classes/services/PKPAuthorService.inc.php(66): DAO->retrieveRange(‘select *, p....', Array) #3 /home/ijcadsee/public_html/lib/pkp/classes/publication/PKPPublicationDAO.inc.php(53): PKP\Services\PKPAuthorService->getMany(Array) #4 /home/ijcadsee/public_html/classes/publication/PublicationDAO.inc.php(40): PKPPublicationDAO->_fromRow(Array) [10-May-2020 17:24:27 Europe/Belgrade] PHP Fatal error: Uncaught Exception: DB Error: Unknown column 'a.publication_id' in 'where clause' Query: select *, p.localeassubmission_localefromauthorsasaleft joinpublicationsaspona.publication_id=p.publication_idwherea.publication_idin (?) order bya.seqasc in /home/ijcadsee/public_html/lib/pkp/classes/db/DAO.inc.php:703 Stack trace: #0 /home/ijcadsee/public_html/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), 'select *,p.…’)
#1 /home/ijcadsee/public_html/lib/pkp/classes/db/DAO.inc.php(201): DAO->retrieve(‘select *, p....', Array, false) #2 /home/ijcadsee/public_html/lib/pkp/classes/services/PKPAuthorService.inc.php(66): DAO->retrieveRange('select *, p.…’, Array)
#3 /home/ijcadsee/public_html/lib/pkp/classes/publication/PKPPublicationDAO.inc.php(53): PKP\Services\PKPAuthorService->getMany(Array)
#4 /home/ijcadsee/public_html/classes/publication/PublicationDAO.inc.php(40): PKPPublicationDAO->_fromRow(Array)
[10-May-2020 17:24:43 Europe/Belgrade] Table ‘temp_authors’ already exists
[10-May-2020 17:24:49 Europe/Belgrade] Duplicate column name ‘email_id’
[10-May-2020 17:24:58 Europe/Belgrade] Duplicate column name ‘email_id’
[10-May-2020 17:30:24 Europe/Belgrade] Table ‘temp_authors’ already exists
[10-May-2020 17:30:27 Europe/Belgrade] Duplicate column name ‘email_id’

Any idea?

Regards,
Lazar

adm_sub · May 12, 2020, 5:59am

Dear @asmecher,
I have been able to fix our database problem while updating. When using the database recovery function in our web server configuration tool, the old database does not seem to be deleted properly, hence the error. So I manually deleted the old database before the backup, then uploaded the backup and assigned the user and rights. Then the update process ran for a few minutes. Unfortunately it did not work in the end. The error message that appears in the menu is
Duplicate entry '0-en_DE-subject' for key 'email_settings_pkey'

In our error message we have a few fatal errors. I post the entire error_log here, hoping that someone can help us. Thank you very much in advance!

[12-May-2020 04:49:01 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/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), 'SELECT * FROM j...')
#1 /home/suburban/public_html/sys/lib/pkp/classes/context/ContextDAO.inc.php(64): DAO->retrieve('SELECT * FROM j...', 'suburban')
#2 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRouter.inc.php(252): ContextDAO->getByPath('suburban')
#3 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRequest.inc.php(760): PKPRouter->getContext(Object(Request), 1)
#4 /home/suburban/public_html/sys/classes/core/Request.inc.php(64): PKPRequest->_delegateToRouter('getContext', 1)
#5 /home/suburban/public_html/sys/lib/pkp/classes/template/PKPTemplateManager.inc.php(106): Request->getContext()
#6 /home/suburban/public_html/sys/classes/template/TemplateManager.inc.php(28) in /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php on line 703
[12-May-2020 04:49:23 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/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), 'SELECT * FROM j...')
#1 /home/suburban/public_html/sys/lib/pkp/classes/context/ContextDAO.inc.php(64): DAO->retrieve('SELECT * FROM j...', 'suburban')
#2 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRouter.inc.php(252): ContextDAO->getByPath('suburban')
#3 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRequest.inc.php(760): PKPRouter->getContext(Object(Request), 1)
#4 /home/suburban/public_html/sys/classes/core/Request.inc.php(64): PKPRequest->_delegateToRouter('getContext', 1)
#5 /home/suburban/public_html/sys/lib/pkp/classes/template/PKPTemplateManager.inc.php(106): Request->getContext()
#6 /home/suburban/public_html/sys/classes/template/TemplateManager.inc.php(28) in /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php on line 703
[12-May-2020 04:49:45 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/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), 'SELECT * FROM j...')
#1 /home/suburban/public_html/sys/lib/pkp/classes/context/ContextDAO.inc.php(64): DAO->retrieve('SELECT * FROM j...', 'suburban')
#2 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRouter.inc.php(252): ContextDAO->getByPath('suburban')
#3 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRequest.inc.php(760): PKPRouter->getContext(Object(Request), 1)
#4 /home/suburban/public_html/sys/classes/core/Request.inc.php(64): PKPRequest->_delegateToRouter('getContext', 1)
#5 /home/suburban/public_html/sys/lib/pkp/classes/template/PKPTemplateManager.inc.php(106): Request->getContext()
#6 /home/suburban/public_html/sys/classes/template/TemplateManager.inc.php(28) in /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php on line 703
[12-May-2020 04:51:00 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/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), 'SELECT * FROM j...')
#1 /home/suburban/public_html/sys/lib/pkp/classes/context/ContextDAO.inc.php(64): DAO->retrieve('SELECT * FROM j...', 'suburban')
#2 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRouter.inc.php(252): ContextDAO->getByPath('suburban')
#3 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRequest.inc.php(760): PKPRouter->getContext(Object(Request), 1)
#4 /home/suburban/public_html/sys/classes/core/Request.inc.php(64): PKPRequest->_delegateToRouter('getContext', 1)
#5 /home/suburban/public_html/sys/lib/pkp/classes/template/PKPTemplateManager.inc.php(106): Request->getContext()
#6 /home/suburban/public_html/sys/classes/template/TemplateManager.inc.php(28) in /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php on line 703
[12-May-2020 04:51:13 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/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), 'SELECT * FROM j...')
#1 /home/suburban/public_html/sys/lib/pkp/classes/context/ContextDAO.inc.php(64): DAO->retrieve('SELECT * FROM j...', 'suburban')
#2 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRouter.inc.php(252): ContextDAO->getByPath('suburban')
#3 /home/suburban/public_html/sys/lib/pkp/classes/core/PKPRequest.inc.php(760): PKPRouter->getContext(Object(Request), 1)
#4 /home/suburban/public_html/sys/classes/core/Request.inc.php(64): PKPRequest->_delegateToRouter('getContext', 1)
#5 /home/suburban/public_html/sys/lib/pkp/classes/template/PKPTemplateManager.inc.php(106): Request->getContext()
#6 /home/suburban/public_html/sys/classes/template/TemplateManager.inc.php(28) in /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php on line 703
[12-May-2020 06:52:05 Europe/Berlin] PHP Fatal error:  Uncaught Exception: DB Error: Table 'suburban_5.publications' doesn't exist Query: SELECT
				o.submission_id,
				MAX(s.context_id) AS journal_id,
				MAX(i.date_published) AS i_pub,
				MAX(p.date_published) AS s_pub,
				COUNT(*) AS count
			FROM
				submissions s
				JOIN publications p ON (p.publication_id = s.current_publication_id)
				JOIN publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name='issueId')
				JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)
				JOIN submission_search_objects o ON (s.submission_id = o.submission_id)
				NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0
			WHERE
				s.status = 3 AND
				i.published = 1 AND k0.keyword_text = ? AND (o.type & ?) != 0 AND i.journal_id = ?
			GROUP BY o.submission_id
			ORDER BY count DESC
			LIMIT 500 in /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php(135):  in /home/suburban/public_html/sys/lib/pkp/classes/db/DAO.inc.php on line 703
[12-May-2020 06:52:15 Europe/Berlin] PHP Fatal error:  Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'suburban_5.publications' doesn't exist in /home/suburban/public_html/sys/lib/pkp/lib/vendor/illuminate/database/Connection.php:326
Stack trace:
#0 /home/suburban/public_html/sys/lib/pkp/lib/vendor/illuminate/database/Connection.php(326): PDO->prepare('select * from `...')
#1 /home/suburban/public_html/sys/lib/pkp/lib/vendor/illuminate/database/Connection.php(657): Illuminate\Database\Connection->Illuminate\Database\{closure}('select * from `...', Array)
#2 /home/suburban/public_html/sys/lib/pkp/lib/vendor/illuminate/database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('select * from `...', Array, Object(Closure))
#3 /home/suburban/public_html/sys/lib/pkp/lib/vendor/illuminate/database/Connection.php(333): Illuminate\Database\Connection->run('select * from `...', Array, Object(Closure))
#4 /home/suburban/public_html/sys/lib/pkp/lib/vendor/illuminate/database/Query/Builder.php(2130): Illuminate\Database\C in /home/suburban/public_html/sys/lib/pkp/lib/vendor/illuminate/database/Connection.php on line 664
[12-May-2020 06:55:10 Europe/Berlin] WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[12-May-2020 06:55:10 Europe/Berlin] WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[12-May-2020 06:55:10 Europe/Berlin] WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[12-May-2020 06:55:10 Europe/Berlin] WARNING: The StaticPage "Verein" uses a path (verein) that conflicts with an existing Custom Navigation Menu Item path. Skipping this StaticPage.
[12-May-2020 06:55:10 Europe/Berlin] Duplicate entry '0-de_DE-subject' for key 'email_settings_pkey'

asmecher · May 12, 2020, 4:36pm

Hi @adm_sub,

I would suggest first resolving the Access Denied messages; these are from MySQL/MariaDB, complaining that the credentials you’ve given to OJS don’t permit access to the database’s contents. Double-check your GRANT statements, and make sure that OJS has the access it needs to work with the database’s contents.

Regards,
Alec Smecher
Public Knowledge Project Team

adm_sub · May 15, 2020, 6:39am

Hi @asmecher,
thanks for the hint to first check the database access rights. I tried a couple of upgrades but unfortunately, nothing has worked.

As far as I understand it, the user has all necessary rights to access the database.
Anmerkung%202020-05-15%20083218

The password and names of user and database are also correct.

For testing purposes, I tried to create a new database, import the old data and assign a new user, but that didn’t work either.

Since the last upgrade in December to our current OJS version 3.1.2.4 nothing has changed in the database settings and access rights. So I am surprised that there are problems with updating here. These error messages (DB Error: Access denied for user '************'@'localhost' to database '********') do not appear in the error_log of our current installation (3.1.2.4).

I would be very grateful for ideas and suggestions.

Thank you,
Michael

Diplodok · May 17, 2020, 1:49pm

Hi, @asmecher
I also cannot upgrade from 3.1.1.2 to 3.2.0.3
I have Error 500 after upgrade
Full package

I installed it into new directory, make all changes in config…
Then I renamed old directory to ojs.old, new directory to ojs
Selected PHP 7.2 on hosting
Get error 500
Support on hosting send me log

PHP Fatal error: Uncaught Error: Call to undefined function mysql_connect() in /home/triacon/domains/triacon.org/public_html/ojs/lib/pkp/lib/vendor/adodb/adodb-php/drivers/adodb-mysql.inc.php:461

I changed names of the directories back, old OJS (3.1.1.2) did not work.
I changed PHP to 5.2, 3.1.1.2 works now

Guys from support tell me that this is an error in OJS code
Could you please, help?

asmecher · May 19, 2020, 7:24pm

Hi @adm_sub,

OJS hasn’t changed between 3.1.2-4 and 3.2 with regard to its expectations for database credentials; I think this can probably be solved with the right SQL GRANT statement to ensure those credentials have access to everything in the database.

Hi @Diplodok,

Try searching the forum for the message Call to undefined function mysql_connect(); you’ll find lots of posts with suggestions.

Regards,
Alec Smecher
Public Knowledge Project Team

Lazar_Stosic · June 22, 2020, 4:44pm

Dear @asmecher

I tried to many options, contacted to many peoples, but nobody know what I can do. I constantly, on upgrade, receive an error:

[22-June-2020 17:24:43 Europe/Belgrade] Table ‘temp_authors’ already exists
[22-June-2020 17:24:49 Europe/Belgrade] Duplicate column name ‘email_id’

I dropped the Table temp_authors and in error.log received only Duplicate column name ‘email_id’. Interesting that I came only with this error and cannot do nothing?
Is there any way to solve that or how I can bypass this to upgrade to the latest OJS? If I use some of older databases I do not have articles which are submitted after the date when backup made.
Any option to solve this problem?