Hi @esciencepress,
That error message can only appear on a second (or later) upgrade attempt, not a first attempt. The upgrade process creates that table, migrates its content, then deletes it; if the upgrade is interrupted partway through, it’ll leave a review_assigments_tmp table behind. A second attempt to run the script will then crash when it tries to create the table because it already exists.
Regards,
Alec Smecher
Public Knowledge Project Team
Hi @esciencepress,
If you have the error message from the first time the upgrade failed, that’ll help to identify where in the upgrade process the crash happened. Do you have that message?
Regards,
Alec Smecher
Public Knowledge Project Team
Hi @esciencepress,
This is a tough one – I’m afraid you’ve run your database through a blender, several times, without a backup. I can’t walk you through correcting this in detail; we do clearly recommend that you make a backup before upgrading. If there’s any way you can recover using a known-good backup, I would strongly recommend going that route. Failing that, I would start with the earliest copy of the corrupted database you have, e.g. after the first failure happened, before you tried again. The more uncertain steps the database has gone through, the tougher it will be to clean up.
If that’s not possible, the steps that an upgrade goes through are listed in dbscripts/xml/upgrade.xml. You could try to ascertain how far your upgrade got by looking through those steps to see which were executed and which were not. You may need to run through these by hand, but be warned, they are extensive.
Regards,
Alec Smecher
Public Knowledge Project Team
@asmecher Thanks. If I found an older version of backup, will it be possible to upgrade keeping the recent data as I have published many issues in different journals after this.
Hi, @asmecher
I have tried all possible options but no recovery of the older version of the database. I’ve read this conversation from the start and would like to share there is something wrong. I’m afraid you’ve run your database through a blender, several times, without a backup. No, I have kept back of everytime I encountered a problem.
review_assignments_tmp already exists
First of all, I have seen the above error, and then I resorted the back up and removed review_assignments_tmp.
Then encountered the the next error, restored the backup of database from initial version and then removed the two error;
review_assignments_tmp already exists
notes_tmp already exists
and so on. So from now, I have fresh copy of the database and all the errors have been removed. Now from the initial backup, I am encountering the below mentioned error.
# DB Error: Unknown column 'a.setting_type' in 'where clause' Query: SELECT a.*, b.context_id FROM submission_settings a, submissions b WHERE a.setting_name = 'fileName' AND a.locale = ? AND a.setting_value LIKE ? AND a.setting_type = 'string' AND b.submission_id = a.submission_id
I hope you have not got an idea.
Hi @esciencepress,
The message:
review_assignments_tmp already exists
…can only appear if an upgrade attempt has already failed on that database. If you don’t have the error message from the upgrade attempt before that, it becomes very hard to determine what parts of the database are upgrades and what parts are not. I’m afraid you’ll need to walk through the upgrade script by hand as described in Issue in upgrading OJS 2.4 to OJS 3.x - #46 by asmecher.
Regards,
Alec Smecher
Public Knowledge Project Team
Hi @asmecher
I have been able to recover my database. My hosting provider had earlier backup. With this database I am facing the following error.
# DB Error: Unknown column 'a.setting_type' in 'where clause' Query: SELECT a.*, b.context_id FROM submission_settings a, submissions b WHERE a.setting_name = 'fileName' AND a.locale = ? AND a.setting_value LIKE ? AND a.setting_type = 'string' AND b.submission_id = a.submission_id
The same error I mentioned earlier.
Also how can I manually do upgrade using dbscripts/xml/upgrade.xml Any idea please.
Hi @esciencepress,
In the OJS 2.4.x database backup that your service provider recovered, is there a submission_settings table?
Regards,
Alec Smecher
Public Knowledge Project Team
Hi @esciencepress,
OJS 2.x does not use a submission_settings table, so I think this database has still suffered a failed upgrade. Check with your provider to see if they can get an older version, before the first upgrade to 3.x was attempted.
Regards,
Alec Smecher
Public Knowledge Project Team
Unfortunately, that was the only backup found. Isn’t there any solution. I am done with all possible options.
@asmecher I am done with all the possible options but there is no way I can find any older version of the database. What could be the possible solution? Any paid expert service? how much it will cost, any idea please? Is there any other option? moving to the fresh OJS installation and transferring articles as XML or any option to move full journal to another install and then upgrade. Can we maintain the article number same as the same reflect in the DOI numbers as well.
Hello @esciencepress . Can you explain step by step, how do you upgrade? OJS 2 that you want upgrade is, EScience Press ?
Thanks
@juanito thank you. I am using OJS 2.4.8.5 now and this was upgraded on July 04, 2019. The upgrade was successful. I don’t know why the following strings found in my database. These strings show I have a failed upgrade to OJS 3. This might be before July 04, 2019 as after this I never tried any upgrade. I mean Upgrade to 2.4.8.5 was successfull but @asmecher confirms I have a failed upgrade that might be before July 04, 2019. My OJS 2.X is working fine and now when I try to upgrade, and this is working fine. to 3.x and encountering a number of problems including;
review_assignments_tmp already exists
notes_tmp already exists
submission_galley_settings already exists
I deleted these and then encountered the following error;
DB Error: Unknown column 'assoc_type' in 'where clause' Query: UPDATE email_templates SET assoc_type = 530 WHERE assoc_type = '526'
I have then cleared data for these and re run the upgrade process but now encountering the following error; (STUCK HERE)
# DB Error: Unknown column 'a.setting_type' in 'where clause' Query: SELECT a.*, b.context_id FROM submission_settings a, submissions b WHERE a.setting_name = 'fileName' AND a.locale = ? AND a.setting_value LIKE ? AND a.setting_type = 'string' AND b.submission_id = a.submission_id
this is what i think you could do.
but this is for all the article that have been publish… for the backlog article you need to do it manually…
create a new fresh installation 2.4.8.5 in a development server.
create all the journal same as your current journal
export user from old journal to the new one
export all issue from all journal to the new one
manually submit backlog article that haven’t been publish (mind you all the editorial process need to start again)
you will loose all your metric but at least you can start fresh from a new database and save you from chasing the rabbit hole
make sure you stop accepting submission in you main journal before you do all this.
when you have done all that try to upgrade the development ojs 2.4.8.5 to ojs 3.x
if successful you can transfer to your main server
well this is what i think i would do…
Hi. today I try to upgrade my ojs 3.0.2.0 to 3.2.1.1, before starting any upgrade I get a backup of older version, i have same errors like below :
A database error has occurred: Table ‘submission_galley_settings’ already exists
A database error has occurred: Table ‘review_assignments_tmp’ already exists
(I think it cause because at first I did not change driver to mysqli so I had failed upgrade at first)
I should mention that I am not very profession on ojs supporting and I use softaculous to manage my website. I get backup by softaculous and try to restore backup by using softacculos several times but all times it failed with same error, I see that you guide people in this case by this instruction : drop database and restore it . Is this work for me? If yes how should I do that? by deleting (droping) all tabels on my database manually and restore my database from my last backup by softaculous scripts (only check backup restore checkbox) will solve my problem? if not how should I sove it? 
Hi @masoume_tm,
I’m afraid I’m not sure what the Softaculous backup/restore does – but if your database already contains a submission_galley_settings or review_assignments_tmp table before you run the upgrade script, then it’s not a “clean” copy of your 3.0.2-0 database. If you’re sure you have a backup, I’d recommend dropping the database fully, creating it fresh, then loading the backup. Depending on your host, you can usually use the mysqladmin tool to do this.
Regards,
Alec Smecher
Public Knowledge Project Team
Tnx for your quick response, to be honest I afraid of drop and creat data base again and Im not sure my back up work fine or not and im not very good at this, I dont know what to do. I also try to restore my full back up and Skip the idea of upgrading, when I try to restore full back up I face this problem: The following errors were found :