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

smtp · March 10, 2020, 7:23pm

Hi every one.

I am trying to update from 3.1.2.0 to 3.2.0 and i get this error

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

A tried to delete content, delete table, restore etc and i can’t skip this step. Any idea?

Thanks in advance

asmecher · March 10, 2020, 8:06pm

Hi @smtp,

Is this a first attempt to upgrade the database, or did you try to run the upgrade again after an earlier failure?

If this is the command-line upgrade tool, could you post the full output of the upgrade command? If you’re using the web-based upgrade tool, and have access to the command-line one, could you try the command-line upgrade?

Regards,
Alec Smecher
Public Knowledge Project Team

smtp · March 11, 2020, 3:38pm

Thanks @asmecher It was the first attemp. I made a backup of the DB into other one, where I tried.

I made it via web based tool. Unfortunately I have not acces to the command line.

Should I upload to other version, such us 3.1.2.4 and after to 3.2.0?

Clean instalation work perfectly, but i need preserve data

smtp · March 11, 2020, 6:56pm

I forgot write my server specifications

PHP: 7.3.13
Mysqli: 5.6.44-86.0-log

Well I tried to update from 3.1.2.0 to 3.1.2.4 and all was OK, was updated properly.

In this step y tried to update from 3.1.2.4 to 3.2.0 and the same error. I can access to php log and this is what happens when i try to upload:

AH00687: Negotiation: discovered file(s) matching request: /web/htdocs/web/home/index (None could be negotiated)., referer https://web/ojs320/index.php/index/install/installUpgrade
AH00687: Negotiation: discovered file(s) matching request: /web/htdocs/web/home/index (None could be negotiated)., referer https://web/ojs320/index.php/index/install/upgrade
AH01215: Duplicate column name ‘email_id’: /web/htdocs/web/home/ojs320/index.php, referer https://web/ojs320/index.php/index/install/upgrade
AH00687: Negotiation: discovered file(s) matching request: /web/htdocs/web/home/index (None could be negotiated)., referer https://web/ojs320/index.php/index/install/upgrade

smtp · March 11, 2020, 7:54pm

I solved it partially. I deleted content in 3.2.0_preupdate_email_templates (i set it blank) and deleted from 3.2.0_update this code lines:

<sql>
	<query>
		INSERT INTO email_templates_settings (email_id, locale, setting_value, setting_name)
		SELECT email_id, locale, subject, 'subject'
		FROM email_templates_data
	</query>
	<query>
		INSERT INTO email_templates_settings (email_id, locale, setting_value, setting_name)
		SELECT email_id, locale, body, 'body'
		FROM email_templates_data
	</query>
	<drop table="email_templates_data" />
</sql>

And now, i will copy email templates databases from clean installation

It seems to work

asmecher · March 12, 2020, 11:15pm

Hi all,

Would someone experiencing this problem be willing to send me a copy of their database, taken prior to the upgrade attempt? If so, please send me a private message.

Thanks,
Alec Smecher
Public Knowledge Project Team

asmecher · March 12, 2020, 11:25pm

Alternately, this may already be fixed by the commit mentioned at Check potential upgrade bug: Duplicate column name ‘email_id’ · Issue #5624 · pkp/pkp-lib · GitHub. Confirmation of whether that resolves the issue would be much appreciated!

Regards,
Alec Smecher
Public Knowledge Project Team

Mohammad_Jaafar_Ali · March 13, 2020, 10:22pm

I tried the fix but still getting the “Duplicate column name 'email_id” error.

Mohammad_Jaafar_Ali · March 13, 2020, 10:51pm

This is the full output of the upgrade command from the command line:

PHP Warning: Use of undefined constant SCHEMA_PUBLICATION - assumed ‘SCHEMA_PUB LICATION’ (this will throw an Error in a future version of PHP) in /home/journal /public_html/lib/pkp/includes/functions.inc.php on line 223
PHP Warning: Use of undefined constant SCHEMA_PUBLICATION - assumed ‘SCHEMA_PUB LICATION’ (this will throw an Error in a future version of PHP) in /home/journal /public_html/lib/pkp/includes/functions.inc.php on line 223
PHP Warning: Creating default object from empty value in /home/journal/public_h tml/classes/plugins/PubObjectsExportPlugin.inc.php on line 62
[pre-install]
[load: upgrade.xml]
[version: 3.2.0.0]

[code: Installer Installer::checkPhpVersion]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_user_author_names.xml (skipped)]

[code: Installer Installer::migrateSubmissionCoverImages]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_email_templates.xml]
ERROR: Upgrade failed: DB: Duplicate column name ‘email_id’

asmecher · March 13, 2020, 11:25pm

Hi @Mohammad_Jaafar_Ali,

Can you clarify specifically what fix you tried?

Regards,
Alec Smecher
Public Knowledge Project Team

Mohammad_Jaafar_Ali · March 14, 2020, 3:22am

@asmecher
This commit pkp/pkp-lib#5589 Fix upgrade for PostgreSQL · pkp/ojs@dbf319e · GitHub

asmecher · March 16, 2020, 11:27pm

Hi all,

Hmm, back to the other option – would someone be willing to privately send a copy of their database?

Regards,
Alec Smecher
Public Knowledge Project Team

KarwanJacksi · March 17, 2020, 11:44am

Hello smtp

I already upgraded the ojs to 3.2, all the things needed from you is to use PHP 7.3 and that’s it. It is working fine with me.

Kind regards,
Karwan Jacksi

birkok · April 7, 2020, 12:06pm

hello asmecher,
i have run the above sql querry. It didn’t work for me.
is there some unskillful way for me?

This is the error message from phpmy admin:

Hata

Sabit çözümleme:

Çözümleme sırasında 1 hata bulundu.

Tanınmayan ifade türü. (near “sql” at position 1)

SQL sorgusu:

INSERT INTO email_templates_settings (email_id, locale, setting_value, setting_name) SELECT email_id, locale, subject, ‘subject’ FROM email_templates_data INSERT INTO email_templates_settings (email_id, locale, setting_value, setting_name) SELECT email_id, locale, body, ‘body’ FROM email_templates_data

MySQL çıktısı:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<sql> <query> INSERT INTO email_templates_settings (email_id, locale, setti' at line 1

asmecher · April 7, 2020, 5:27pm

Hi @birkok,

Can you clarify what the issue is that you’re trying to resolve, and what release of OJS you’re working with?

I think the immediate problem is that you’re trying to execute the XML as SQL, but before working around that – I don’t think the above SQL was intended to resolve the problem.

Regards,
Alec Smecher
Public Knowledge Project Team

birkok · April 8, 2020, 8:06am

Hi @asmecher,

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

asmecher · April 8, 2020, 6:03pm

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

birkok · April 10, 2020, 6:11am

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.

asmecher · April 11, 2020, 12:42am

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

birkok · April 13, 2020, 11:21am

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?