Error upgrading OJS 3.1.2-1 to 3.2.1-1: ERROR: Upgrade failed: DB: Duplicate column name 'assoc_type'

[juanito]

Hello.

Upgrading OJS 3.1.2 to 3.2.1-1

debug= On

PKP-Database-Logger 1594746751,5807: -----
(mysqli): ALTER TABLE subeditor_submission_group ADD assoc_type BIGINT 
   
-----

PKP-Database-Logger 1594746751,5807: Query: ALTER TABLE subeditor_submission_group ADD assoc_type BIGINT failed. Duplicate column name 'assoc_type'
PKP-Database-Logger 1594746751,5807: 1060: Duplicate column name 'assoc_type'
...
ERROR: Upgrade failed: DB: Duplicate column name 'assoc_type'

What can i do? Thanks.

[juanito]

We received this installation, broken, they try to upgrade, and fail, and no backup.

i did

DROP TABLE subeditor_submission_group;

But now:

PKP-Database-Logger 1594750242,4605: Query: 
			UPDATE submissions as s
			SET current_publication_id = (
				SELECT p.publication_id
				FROM publications as p
				WHERE s.submission_id = p.submission_id
			)
		 failed. Subquery returns more than 1 row
PKP-Database-Logger 1594750242,4605: 1242: Subquery returns more than 1 row
							ADOConnection._Execute( 			UPDATE submissions as s 	SET current_publication_id = ( 				SELECT p.publication_id 				FROM publications as p 				WHERE s..., false)

[Lazar_Stosic]

There are to many posts on this topic. On of them is [OJS 3.1.2.0 to 3.2] Upgrade Error - Duplicate column name ‘email_id’

[marc]

Hi @juanito

Yesterday a 3.1.1-1 installation falls in my hands with same error.
They ensure me they never try to upgrade… but you never know.

Anyway, there is no backup so I’m wondering… did you finally find a solution for this?

Cheers,
m.

[juanito]

Hola Marc, cómo está.

Parece que si se te presenta ese error, sí han intentado actualizar, pero lo habrán hecho por el método web y se les ha roto a medias.

No pude arreglarlo, al final conseguimos una copia de seguridad de 15 días posterior, que sí estaba íntegra.

Por lo visto, la tabla subeditor_submission_group, no existe en 3.1, pero sí en 3.2. Ese fue un elemento para detectar si una base de datos estaba bien o no.

¿Tendrán alguna copia de seguridad de hace unos días? ¿Dónde la tienen alojada, servidor dedicado propio o algún hosting? hay algunos hostings que sí guardan copias de seguridad aunque no lo digan.

[juanito]

Marc, qué te devuelve

SELECT major, minor, revision, build FROM versions WHERE product IN ('ojs2', 'omp') AND current=1;

[marc]

Gracias por tu rápida respuesta.
Pues la verdad es que no se muy bien de que hay cópia y de que no.

Pregunto y a ver que se puede hacer.

Gracias de nuevo,
m.

[juanito]

A ver si hay suerte. Alguna solución tiene que existir, no me vi capacitado para determinar en qué punto se rompió el intento de actualización y deshacer, así que les presioné un poco para que me contasen la verdad y buscasen alguna copia de seguridad

[marc]

La query me devuelve la versión correcta:

major	minor	revision	build
3	1	1	0

Es curioso pq tampoco veo ninguna de las tablas temporales que se crean durante el upgrade.

[juanito]

¿No existe la tabla
subeditor_submission_group
?

Entonces es buena señal y puede que te hayan contado la verdad, en el caso que abría el post, había rastro de esta tabla “antes” de actualizar y el select de versión, devolvía una 3.2 rota en actualización

[marc]

Intentaré asegurar los backups por si las moscas… que los upgrades de OJS3 son un poco quisquillosos y tampoco es plan de ir a calzón sacao.

Gracias por tu tiempo,
m.

[juanito]

Otra opción para detectar bases de datos tocadas. En este caso, el OJS 3.1 tenía más tablas que una instalación limpia de OJS 3.1

[marc]

La tabla “subeditor_submission_group” si que existe. La verdad es que ni me había planteado que hubiesen hecho el upgrade y no me hubiesen dicho nada… pero como sea así, termina la actualización Rita.

[juanito]

Estaba ya dudando, he hecho una prueba en una 3.1.1-4

Y no existe la tabla subeditor_submission_group. Así que puede ser signo de intento de actualización a medias, rota.

[marc]

Probably we don’t need to anyone Alec for this.
He usually have his hands full and this was an easy to solve.

First I though in look into the schema… but I couldn’t find the subeditor_submission_group table you talk about in the last release, so I’m missing something here: ojs/ojs_schema.xml at stable-3_2_1 · pkp/ojs · GitHub

So I started an ojs 3.1.1-0 over docker (it took me 5 minutes) and you are right… there is no “subeditor_submission_group” there. As you pointed, a clean 3.1.1-0 installation has 112 tables… and in the ojs that I like to upgrade we have 156.

The point is they don’t have backup… so I will need to dig further in the forum to discover how to undone the first steps of an unsuccessful migration.

Thanks for your help @juanito

[marc]

Well, I also forget to report back in this issue. Sorry.

As expected, they did an upgrade (via softaculous) ant it breaks the system.

I deleted all the tables that were not in a clean OJS 3.1.1-0.

In my case the query to clean the DB I need to

DROP TABLE `books_for_review`, `books_for_review_authors`, `books_for_review_settings`, `captchas`, `categories`, `category_settings`, `comments`, `dataverse_files`, `dataverse_studies`, `edit_assignments`, `email_templates_settings`, `external_feeds`, `external_feed_settings`, `groups`, `group_memberships`, `group_settings`, `mutex`, `objects_for_review`, `object_for_review_assignments`, `object_for_review_persons`, `object_for_review_settings`, `paypal_transactions`, `pln_deposits`, `pln_deposit_objects`, `processes`, `publications`, `publication_categories`, `publication_galleys`, `publication_galley_settings`, `publication_settings`, `referrals`, `referral_settings`, `review_object_metadata`, `review_object_metadata_settings`, `review_object_types`, `review_object_type_settings`, `roles`, `subeditor_submission_group`, `submission_xml_galleys`, `theses`;

Then I go with the progressive upgrade that Antti-Jussi suggested… first baby-steps, version by version (with docker it’s easy ) and after some try-error I discover my minimal migration path was:

3_1_1-0 > 3_1_2-4 > 3_2_0-3 > 3_2_1-1

After this process I was able to upgrade to the last version but I found some extra errors related with the journal’s about field… and thanks to Nate, I was able to debug and find the offending field:

Unable to reach masterhead or contact tabs in journal settings

That suggests to me that one of the form fields that is being shown is not configured correctly. This could be because a plugin you are running has added or modified a form incorrectly. Or it could be because the data in your forms is wrong. The most common culprit is in journal locale settings. The forms rely on these to display multilingual forms, and I’ve seen lots of cases where the settings are off in the database. So first I’d recommend checking the active locales and trying to reset them…

Finally I got some trouble to login… but was something wired with my browser cache because after cleaning server’s cache, and ensure I was on php 7.3 I changed from Friefox to chromium and all worked like a charm.

So, checking the post as solved.

Thanks you all for your help.