I know my information may be not connected directly with the original question in this thread, but I had some database related problems while upgrading to the latest release and I solved them successfully, so I think I can share my experience here, so it may help some people (or maybe give some useful information for code-makers).
The first problem was connected with NULL-values in ‘review_assignments’ table. I had such error message:
SQLSTATE[22004]: Null value not allowed: 1138 Invalid use of NULL value (SQL: ALTER TABLE review_assignments CHANGE review_round_id review_round_id BIGINT NOT NULL)
The following SQL request (before starting the upgrade procedure) solved the problem:
update review_assignments SET review_round_id = 0 WHERE review_round_id IS NULL;
Need to mention that in a week before upgrading we moved to a new hosting provider, so MAYBE some problems I describe appeared while exporting and importing the base. Really, maybe some export/import options were not set properly but everything worked fine (on the new server already) before this upgrading attempt.
The second problem was more serious and I spent hours to find the reason (and a good solution) of it. The error message was the following:
MySQL Error Code 1215: “Cannot add foreign key constraint” (SQL: alter table submission_files add constraint submission_files_file_id_foreign foreign key (file_id) references files (file_id))
This error was already mentioned earlier here, but let me tell what was the reason in my case.
As other people explained earlier the problem is connected with different Database Engine type for those tables (“MyISAM” vs “InnoDB”), and yes I could see it in my MySQL database - they were different. But … I couldn’t just change the engine for one of the tables (‘files’) manually, because it was created (or recreated) by upgrade script every time I tried to start it! So the problem was connected with hosting server settings (MySQL settings) - the default database engine for newly created tables on this server was set to “MyISAM” so Upgrade script couldn’t pass that line with creating “foreign key constraint”.
You may say - change your sever settings and forget about this problem, but … with affordable shared hosting service we use, such settings are not available for us, so I had to find another solution.
I won’t describe it all in details now (my post is too long even without it already) but in two words - I had to modify file /lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Grammars/MySqlGrammar.php - it has “datbase-engine name” related function (“compileCreateEngine”) but it seems it was not used (not involved in forming final sql-request string) - the engine name wasn’t appended. I changed the code there a bit to force this function be used (with ‘InnoDB’ as database engine name) in every table creation request and my problems was solved.
One important suggestion to those who have problems with database during upgrade procedure: export your database BEFORE trying to upgrade for the first time and restore it EVERY time before trying to upgrade again (after fixing something in your settings or anything else). I had to try many times (because of my “default database engine” problem) and every time I restored the database and applied that “fix” about NULL-values before trying to start upgrade script again.