Upgrade from OJS 3.2.1-4 to OJS 3.3.0-3 fails. PDOException ... Can't create table ... Foreign key constraint is incorrectly formed

Software Support
,
1

Application Version
OJS 3.3.0-3 (https://pkp.sfu.ca/ojs/download/ojs-3.3.0-3.tar.gz)

Server

  • PHP: 7.3.20
  • Database server: MariaDB 10.3.27

Description of issue
Successfully upgraded OJS from 3.2.1-3 to OJS 3.2.1-4
Upgrade fails when trying to upgrade from OJS 3.2.1-3 or from OJS 3.2.1-4 to OJS 3.3.0-3

We have two different OJS installations, and the same result occurs when trying to upgrade both on our staging server.

The database user has been granted all rights for the OJS database.

Any insights would be appreciated.

Best,
Eirik Hanssen
OsloMet University Library.

Error message:

ERROR: Upgrade failed: DB: PDOException: SQLSTATE[HY000]: General error: 1005 Can’t create table skriftserien_stage.submission_file_revisions (errno: 150 “Foreign key constraint is incorrectly formed”) in /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115
Stack trace:
#0 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(115): PDOStatement->execute(NULL)
#1 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(464): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘alter table su...', Array) #3 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('alter table su…’, Array, Object(Closure))
#4 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(465): Illuminate\Database\Connection->run(‘alter table su...', Array, Object(Closure)) #5 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(102): Illuminate\Database\Connection->statement('alter table su…’)
#6 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(290): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))
#7 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(151): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#8 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(549): Illuminate\Database\Schema\Builder->table(‘submission_file…’, Object(Closure))
#9 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(137): PKPv3_3_0UpgradeMigration->_migrateSubmissionFiles()
#10 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()
#11 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)
#12 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()
#13 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()
#14 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()
#15 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/tools/upgrade.php(22): UpgradeTool->execute()
#16 {main}

Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[HY000]: General error: 1005 Can’t create table skriftserien_stage.submission_file_revisions (errno: 150 “Foreign key constraint is incorrectly formed”) in /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
Stack trace:
#0 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(117): Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
#1 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(464): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘alter table su...', Array) #3 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('alter table su…’, Array, Object(Closure))
#4 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(465): Illuminate\Database\Connection->run(‘alter table su...', Array, Object(Closure)) #5 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(102): Illuminate\Database\Connection->statement('alter table su…’)
#6 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(290): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))
#7 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(151): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#8 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(549): Illuminate\Database\Schema\Builder->table(‘submission_file…’, Object(Closure))
#9 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(137): PKPv3_3_0UpgradeMigration->_migrateSubmissionFiles()
#10 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()
#11 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)
#12 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()
#13 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()
#14 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()
#15 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/tools/upgrade.php(22): UpgradeTool->execute()
#16 {main}

Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1005 Can’t create table skriftserien_stage.submission_file_revisions (errno: 150 “Foreign key constraint is incorrectly formed”) (SQL: alter table submission_file_revisions add constraint submission_file_revisions_submission_file_id_foreign foreign key (submission_file_id) references submission_files (submission_file_id)) in /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
Stack trace:
#0 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘alter table su...', Array, Object(Closure)) #1 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(465): Illuminate\Database\Connection->run('alter table su…’, Array, Object(Closure))
#2 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(102): Illuminate\Database\Connection->statement(‘alter table `su…’)
#3 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(290): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))
#4 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(151): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#5 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(549): Illuminate\Database\Schema\Builder->table(‘submission_file…’, Object(Closure))
#6 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(137): PKPv3_3_0UpgradeMigration->_migrateSubmissionFiles()
#7 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()
#8 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)
#9 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()
#10 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()
#11 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()
#12 /var/www/vhosts/skriftserien-stage.oslomet.no-ojs-3.3.0-3/tools/upgrade.php(22): UpgradeTool->execute()
#13 {main}

2

Hi @Eirik_Hanssen,

Have a look through this and please add your feedback: Upgrade to 3.3: default database engine doesn't support foreign key constraint · Issue #6732 · pkp/pkp-lib · GitHub

Regards,
Alec Smecher
Public Knowledge Project Team

3

I don’t know if this is the same case, but I got the same error Foreign key constraint is incorrectly formed, although it’s from the difference table.

ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 1005 Can't create table `u11516mqe_ojs2`.`review_round_files` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `review_round_files` add constraint `review_round_files_submission_file_id_foreign` foreign key (`submission_file_id`) references `submission_files` (`submission_file_id`))

I got this after upgrade our OJS from 2.4 to 3.2.1, and this happen when I upgrade from 3.2.1 to OJS 3.3.0.

In my case, change all the engine table of our database from MyISAM to InnoDB fix the issue.

4

This discussion for this topic cam be accessed too to gain your information

1 Like
5

Update with workaround/solution.
After making sure all tables use InnoDB, and that the default database engine is defined to be InnoDB in the mariadb config file, in my calse /etc/my.cnf.d/mariadb-server.cnf:
default_storage_engine=InnoDB,
I can actually upgrade one of our two OJS installs from OJS 3.2.1-4 to OJS 3.3.0-3 fine.

However the other database which is larger, fails during upgrade :
ERROR: Upgrade failed: DB: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (journals_stage.#sql-500_219b, CONSTRAINT review_files_submission_file_id_foreign FOREIGN KEY (submission_file_id) REFERENCES submission_files (submission_file_id)) in /var/www/vhosts/journals-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115

Maybe there is a problem/inconsitency with this database.

I have found a workaround to this problem:
Change the engine of all tables in the OJS database to MyISAM before upgrade (as MyISAM ignores foreign keys).

NOTE: Thanks to fix mentioned by @Vitality later down in this thread, this step should not be necessary!

When the mariadb default_storage_engine=InnoDB, and all tables in the database use MyISAM engine, upgrade fails again.

But when I change default_storage_engine=MyISAM in mariadb server config and (and reload the configuration file for the database server), and all the tables in the database use MyISAM engine, the OJS upgrade from 3.2.1-4 to 3.3.0-3 succeeds also on our larger database that had problem with Integrity constant violation/foreign key constraint failure when using InnoDB during upgrade.

Aftewords I can change engine back to InnoDB (for the tables in the database and the default_storage_engine used by mariadb server.

I hope this will help some users.

6

If you need to change engine of tables in a database and you log into the database using a shell with mysql client, here is one way of doing it. Replace “dbuser” and “dbname” with your own values here:

Connect with mysql in raw mode to avoid “|” and column names in the results so that you can paste back the statements without having to edit them (-s -r):

Before continuing you made sure to backup your database, right?

Connect in raw mode:
mysql -s -r -u dbuser -p

To create a list of ALTER TABLE statements to change the engine of all tables in your OJS database, run this sql query in mysql shell:

SELECT CONCAT(‘ALTER TABLE ‘,table_schema,’.’,table_name,’ ENGINE=MyISAM;’) FROM information_schema.tables WHERE engine =‘InnoDB’ and table_schema = 'dbname;

You will get a list of ALTER TABLE statements:
ALTER TABLE dbname.access_keys ENGINE=InnoDB;
ALTER TABLE dbname.announcement_settings ENGINE=InnoDB;
ALTER TABLE dbname.announcement_type_settings ENGINE=InnoDB;
(…)

Then copy the list of ALTER TABLE statements and paste it back into the mysql prompt to change the engine.

Make sure mariadb server is using the same default engine as the tables in the OJS database. Run the following sql query in mysql shell:

SHOW ENGINES;

This wil generate output like this (abbreviated):

Engine Support (…) (…) (…)
(…)
MyISAM DEFAULT (…) (…) (…)
InnoDB YES (…) (…) (…)
(…)

In this case MyISAM is the default engine. If the default engine wasn’t MyISAM, I would have to edit my mariadb config to change the default engine there.

Log out of the mysql shell (or use a different terminal to run commands on the server).

Do the OJS upgrade.

After the upgrade you can change the database engine back to InnoDB to InnoDB:

SELECT CONCAT(‘ALTER TABLE ‘,table_schema,’.’,table_name,’ ENGINE=InnoDB;’) FROM information_schema.tables WHERE engine =‘MyISAM’ and table_schema = 'dbname;

You should probably change the database server config at this point and make InnoDB the default engine again.

4 Likes
7

Hi Eirik;

I am trying to understand statement here but sometime I can’t understand sorry I am not into hard coding to the DB, but I tried myself to Understand suddenly I can’t.

Hope you can share some points
Darryl

8

I am just logging into the mariadb server using the mysql shell and running sql statements there.
I want to create the sql statements to alter all of the tables in the OJS database (changing the database engine), and then run them to actually change the engine.

If you don’t understand what those commands do, then the quickest option I suggest is getting help from some knowledgeable database administrators. Another option is to learn more. It is doable, but of course that comes with time investment.

I can edit my previous response a little to make it more clear.

9

Hi all,

Take a look at Review files pointing to non-existing submission file may break upgrade to 3.3 · Issue #6743 · pkp/pkp-lib · GitHub

10

Thanks @Vitaliy for bringing this to my attention. I have applied this fix and now and I can confirm that it works! I can now successfully upgrade the larger OJS database using InnoDB engine, where the previous upgrade attempt failed due to the error:

ERROR: Upgrade failed: DB: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (journals_stage.#sql-500_219b, CONSTRAINT review_files_submission_file_id_foreign FOREIGN KEY (submission_file_id) REFERENCES submission_files (submission_file_id)) in /var/www/vhosts/journals-stage.oslomet.no-ojs-3.3.0-3/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115

2 Likes
11

Thanks for all this info… I was unable to upgrade to 3.3.0-5 because of this error … and changing all the tables to InnoDB was enough to solve it.

1 Like