Failure at the migration I8073_RemoveNotesWithoutQueriesAndRelatedObjects

[jonasraoni]

Describe the bug The migration I8073_RemoveNotesWithoutQueriesAndRelatedObjects is attempting to drop some foreign keys, but looks like they might not exist.

In fact, the user had the constraint in place (perhaps before the upgrade) and perhaps one of the operations ended up dropping it before it reached the migration mentioned above, difficult to simulate without the original dataset.

See trace below:

Syntax error or access violation: 1091 Can't DROP `submission_files_file_id_foreign`;
`[revert migration: PKP\migration\upgrade\v3_4_0\I6895_CreateNewInstitutionsTables]
[downgrade for "PKP\migration\upgrade\v3_4_0\I6895_CreateNewInstitutionsTables" unsupported: Downgrade not supported]

ERROR: Upgrade failed: DB: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'submission_files_file_id_foreign'; check that column/key exists in F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:545
Stack trace:
#0 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php(545): PDOStatement->execute()
#1 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php(753): Illuminate\Database\Connection->Illuminate\Database\{closure}('alter table `su...', Array)
#2 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php(720): Illuminate\Database\Connection->runQueryCallback('alter table `su...', Array, Object(Closure))
#3 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php(546): Illuminate\Database\Connection->run('alter table `su...', Array, Object(Closure))
#4 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Schema\Blueprint.php(109): Illuminate\Database\Connection->statement('alter table `su...')
#5 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php(439): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))
#6 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php(269): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#7 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Support\Facades\Facade.php(338): Illuminate\Database\Schema\Builder->table('submission_file...', Object(Closure))
#8 F:\wamp64\www\ojs34rc3\lib\pkp\classes\migration\upgrade\v3_4_0\I8073_RemoveNotesWithoutQueriesAndRelatedObjects.php(65): Illuminate\Support\Facades\Facade::__callStatic('table', Array)
#9 F:\wamp64\www\ojs34rc3\lib\pkp\classes\install\Installer.php(461): PKP\migration\upgrade\v3_4_0\I8073_RemoveNotesWithoutQueriesAndRelatedObjects->up()
#10 F:\wamp64\www\ojs34rc3\lib\pkp\classes\install\Installer.php(290): PKP\install\Installer->executeAction(Array)
#11 F:\wamp64\www\ojs34rc3\lib\pkp\classes\install\Installer.php(205): PKP\install\Installer->executeInstaller()
#12 F:\wamp64\www\ojs34rc3\lib\pkp\classes\cliTool\UpgradeTool.php(95): PKP\install\Installer->execute()
#13 F:\wamp64\www\ojs34rc3\lib\pkp\classes\cliTool\UpgradeTool.php(68): PKP\cliTool\UpgradeTool->upgrade()
#14 F:\wamp64\www\ojs34rc3\tools\upgrade.php(21): PKP\cliTool\UpgradeTool->execute()
#15 {main}

Next Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'submission_files_file_id_foreign'; check that column/key exists (SQL: alter table `submission_files` drop foreign key `submission_files_file_id_foreign`) in F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:760
Stack trace:
#0 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php(720): Illuminate\Database\Connection->runQueryCallback('alter table `su...', Array, Object(Closure))
#1 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php(546): Illuminate\Database\Connection->run('alter table `su...', Array, Object(Closure))
#2 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Schema\Blueprint.php(109): Illuminate\Database\Connection->statement('alter table `su...')
#3 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php(439): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))
#4 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php(269): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#5 F:\wamp64\www\ojs34rc3\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Support\Facades\Facade.php(338): Illuminate\Database\Schema\Builder->table('submission_file...', Object(Closure))
#6 F:\wamp64\www\ojs34rc3\lib\pkp\classes\migration\upgrade\v3_4_0\I8073_RemoveNotesWithoutQueriesAndRelatedObjects.php(65): Illuminate\Support\Facades\Facade::__callStatic('table', Array)
#7 F:\wamp64\www\ojs34rc3\lib\pkp\classes\install\Installer.php(461): PKP\migration\upgrade\v3_4_0\I8073_RemoveNotesWithoutQueriesAndRelatedObjects->up()
#8 F:\wamp64\www\ojs34rc3\lib\pkp\classes\install\Installer.php(290): PKP\install\Installer->executeAction(Array)
#9 F:\wamp64\www\ojs34rc3\lib\pkp\classes\install\Installer.php(205): PKP\install\Installer->executeInstaller()
#10 F:\wamp64\www\ojs34rc3\lib\pkp\classes\cliTool\UpgradeTool.php(95): PKP\install\Installer->execute()
#11 F:\wamp64\www\ojs34rc3\lib\pkp\classes\cliTool\UpgradeTool.php(68): PKP\cliTool\UpgradeTool->upgrade()
#12 F:\wamp64\www\ojs34rc3\tools\upgrade.php(21): PKP\cliTool\UpgradeTool->execute()
#13 {main}`

What application are you using? OJS 3.4.0rc3

Additional information Source: https://github.com/pkp/pkp-lib/issues/8929

[asmecher]

During the upgrade to 3.3.0 there should be a FOREIGN KEY constraint added on submission_files.file_id constrained to files.file_id. However, it was added without an ON DELETE CASCADE clause, so the I8073_RemoveNotesWithoutQueriesAndRelatedObjects migration drops the constraint and adds it with the missing clause.

If the submission_files_file_id_foreign constraint is missing on a 3.3.0-x database, suspect it means the upgrade to 3.3.0 wasn't run successfully. I don't mind accommodating out-of-spec databases if it's quick and easy to do, but it's not a priority.

[jonasraoni]

It's a simple fix. As the problem already happened with a user under a small test coverage, I think it's better to fix anything suspect to avoid new reports/forum threads.

And about this specific case, the user HAD the constraint on his database. But instead of trying to find why it doesn't exist at this point, I decided to just add a cheap patch (which I still think it's ok) and move forward to the next issue.

[asmecher]

Merged all, thanks!