Updating from 3.2.1.4 to 3.3.0.10, and DB error SQLSTATE[HY000]: General error: 1005

Software Support
, ,
1

Hi,

I am trying to update ojs from 3.2.1.4 to 3.3.0.10, but I get the following error:

ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 1005 Can’t create table ‘ojs33010.#sql-3de_bcfeba’ (errno: 150) (SQL: alter table submission_files add constraint submission_files_file_id_foreign foreign key (file_id) references files (file_id))

Before updating, I changed all tables into InnoDB, because some tables were MyISAM, as suggested in other threads related to this issue, but it doesn’t help. Upon the update breaks, some of the tables go back to MyISAM. Additionally, before this error comes up, there are a few following, not sure if related to the one above:

A file assigned to the file stage 1 could not be migrated.
A submission file was expected but not found at …

Thanks for suggestions.

2

Hi there,

You need to grant REFERENCES permission for your database user.

GRANT REFERENCES ON `database_name`.* TO 'database_user'@'localhost';
3

Hi @janiosarmento.
I checked and my user has this permission granted.

I missed one thing apparently:
'engine' => 'InnoDB',
according to this thread: UPDATING Problem from 3.2.1.3 to 3.3.0.2 - #17 by Vitaliy

Will check the update process again.

4

Applying the missing line helped, and the update succeeded.

5

how did you manage to solve it? I’m having this problem too

6

Upon duplication 3.2.1.4 database that would serve as a source for 3.3.0.10, I changed storage engine for all MyISAM tables into InnoDB. Next, I added

'engine' => 'InnoDB',

in /lib/pkp/classes/core/PKPApplication.inc.php, ahead of

'driver'    => $driver,

line.
Then I started regular ojs update.
If you are updating from the earlier versions of ojs, you might need to update through intermediate versions, and if so you might check my other issue: Issues upon upgrading ojs from 2.4.8.1 to 3.2.1.4

1 Like
7

Did you use any command to change all tables to InnoDB?

8

I altered each table individually with

ALTER TABLE table_name ENGINE = InnoDB;

command.

You can check storage engine of each table in phpMyAdmin in the Structure tab of a database you want to alter (“Type” column). Next, in the SQL tab you can run the above command.

Alternatively to sql command, you may change the storage engine also in phpMyAdmin in each table’s Operation tab > Table Options section > Storage Engine option.

9

I’d also recommend looking at https://stackoverflow.com/a/3050502

1 Like
10

Hello, thank you I changed the tables and updated to the latest version and now it worked!

1 Like