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

Hi @asmecher,

  1. I was upgrading to 3.2.0.1
  2. Current version ojs 3.1.2.1 according to system information in ojd dashboard
  3. One month ago, I tried to upgrade 3.1.2.2, but upgrade process ended with white page,didn’t completed.
  4. Now I want to upgrade to 3.2.0.1
  5. Meanwhile, when I tried repair and backup mysql db (which is 1.2 Gib size), get the following error:
    image

Hi @birkok,

After your attempted upgrade to 3.2.0-1 failed, did you restore your database from backup?

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

No. I didn’t restore old database backup
Since it seems worked, I left as is.
But some plugins, such as crossref export, cannot be upgraded, but still works.

Hi @birkok,

Uh oh, I’m afraid that’s a bit of a problem. When your upgrade failed, it left your database halfway between OJS 3.1.2 and 3.2. Only the error message can tell you how far it got. The error you’re encountering now is happening because the upgrade script is trying to execute an upgrade step that’s already been executed. See Issue in upgrading OJS 2.4 to OJS 3.x - #46 by asmecher for more information on this situation. You should always restore from backup after a failed upgrade.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

I have examined your guidances but unfortunately I have no skills to accomplish.
Do you recommend any other way?

Is it possible export issues and users via OJS’s export plugin and then import to new empty OJS installation via import plugin?

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

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

Hi @asmecher,

I can send you my database if you are interested.

Hi @Lazar_Stosic,

Did you try this solution? What happened?

Regards,
Alec Smecher
Public Knowledge Project Team

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.

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

Hi @asmecher,

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

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

Hi @asmecher,

email_templates_data has both but email_templates does not have.
Picture

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

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

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

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

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

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'