Database error upgrading from 3.2.1.3 to 3.3.0.4

Software Support
1

I am getting this error during the upgrade:
Base table or view already exists: 1050 Table ‘files’ already exists
See Fatal Error during upgrade from OJS 3.2.1.2 to 3.3.0.3 (Cannot add foreign key constraint (SQL: alter table submission_filesadd constraint... · Issue #6729 · pkp/pkp-lib · GitHub

I have already set the client_charset to utf-8, and changed all tables to InnoDB and utf8_unicode_ci encoding. These various steps, recommended in various threads, avoided various other database errors but this one still occurs.

Does anybody have a solution? (Does the table need a create if not exists clause?)

2

Hi @spiraledge

Did you attempt an upgrade previously and forget to restore from a backup before trying again? The “files” table only exists in 3.3 - it gets created during the upgrade.

Best,
Jason

3

Hi @jnugent,

Thanks for your reply. I did restore the DB from a backup because the site was broken by the upgrade - I had to revert the files and DB. Strangely, the tables files exists anyway - could this be the restore process not deleting the old table perhaps? From what you say, I should be able to drop files and it should work?

Thanks.

4

Hi @spiraledge

A restore won’t delete tables that are present in a database but not in the dump that you’re restoring from. At PKP|PS we drop all of the tables in a database before restoring from a backup. OJS will occasionally create *_migration tables to hold content while it does other things, for instance.

Cheers,
Jason

5

Hi @jnugent,

That makes sense, the affected tables are files and submission_file_revisions i.e. these were left over from the install.

There is now a new error, regarding foreign key constraints between these two tables. Looking at the database at this point, these two tables are created as MyISAM; at this point I have already avoided foreign key errors by converting all tables to INNODB, but these now reoccur with the new tables.

Thanks for any further advice.

6

if new tables are being created as MyISAM, you’ll need to configure MySQL and change the default engine used when creating tables. That’s probably here:

https://dev.mysql.com/doc/refman/8.0/en/storage-engine-setting.html

Cheers,