Trying to upgrade a sandbox install of OJS 3.2.1-4 to the latest 3.4.0-3 version (CentOS 7 with Apache and MariaDB).
The upgrade works until [migration: APP\migration\upgrade\v3_4_0\I6093_AddForeignKeys] and then throws an error 1553 Cannot drop index 'review_form_responses_unique'
This is the ending of the upgrade log:
2023-09-01 12:20:36 [migration: APP\migration\upgrade\v3_4_0\I6093_AddForeignKeys]
2023-09-01 12:20:54 [revert migration: APP\migration\upgrade\v3_4_0\I3573_AddPrimaryKeys]
2023-09-01 12:21:14 [error while downgrading "APP\migration\upgrade\v3_4_0\I3573_AddPrimaryKeys": SQLSTATE[HY000]: General error: 1553 Cannot drop index 'review_form_responses_unique': needed in a foreign key constraint (SQL: alter table `review_form_responses` drop index `review_form_responses_unique`)]
ERROR: Upgrade failed: DB: SQLSTATE[42000]: Syntax error or access violation: 1280 Incorrect index name 'sections_review_form_id' (SQL: alter table `sections` add index `sections_review_form_id`(`review_form_id`))
Is there a way to fix the database before the upgrade? In the mean time I’ll try to do an intermediate upgrade to 3.3.0-15 to see if I still get that error. I think the leap from the 3.2. version might be to large, as I’m getting quite a lot of [Removing orphaned entries...] warnings during the upgrade.
I stumbled here in an effort to finally upgrade our 2.4.8-5 installation to a recent OJS version. Struggled a bit, but I sucessfully upgraded to 3.2.1-4 version with the help of brave people doing it before and documenting the process.
No luck, reverted the upgrade and did an upgrade to 3.3.0-15 first, which was perfect no warnings or errors.
Tried the 3.4.0-3 upgrade again, ended up with the same error as above.
I think the problem might actually be the sections_review_form_id index, not the review_form_responses_unique one. Does your 3.2.1-4 database have an index called sections_review_form_id already on the sections table? It shouldn’t, I don’t think.
Regards,
Alec Smecher
Public Knowledge Project Team
However, I have two indexes with the same name in the review_form_responses table, but no index named review_form_responses_unique that should be dropped in the upgrade process.
SELECT table_schema, table_name, index_name, column_name
FROM information_schema.statistics
WHERE non_unique = 1 AND table_schema = "your_database_name";
EDIT: I have the same indexes already in the 2.4.8.5 database version
Dear Alec,
I’m going to upgrade to 3.3.0-15 LTS version for now, which worked in my sandbox install, but for later I think I need to fix our database - I’m sure there are some issues here. I had to change the storage engine to InnoDB and convert old latin1 tables to utf8 encoding. For now it seems to work in the OJS 3.3.
Are there any tips how to check the database, if it contains some errors from before? We run this install from 2009 and upgraded the production server up to the 2.4.8.5 version, but there might be some errors introduced in the database in that long time and upgrading through many versions.
The upgrade from 3.3.0-x to 3.4.0-x does a ton of checking and correcting, so performing that upgrade will implicitly clean up a lot of old data problems. (And it’ll make things a lot smoother in the future.) That’s mostly around foreign keys and leftover (orphan) data, though, not so much about encodings. I’m not aware of a tool that’ll check encodings beyond something like ftfy.
Stepping up to 3.3.0 LTS will already be a big improvement over 3.2.0-x, though, so you should be fine there for a while until you’re ready to make the leap to 3.4.0-x!
Regards,
Alec Smecher
Public Knowledge Project Team
Thank you Alec,
I’m going to install the 3.3.0 LTS version for now, and later try to debug the database in my local install. I’ll let you know how it goes. This topic can be closed for now.
Regards,
Aleš
