If so, check for entries in submission_file_revisions that reference missing files in submission_files:
SELECT COUNT(*)
FROM submission_file_revisions sfr
LEFT JOIN submission_files sf ON (sfr.submission_file_id = sf.submission_file_id)
WHERE sf.submission_file_id IS NULL;
If this gives you an empty result, then it’s probably still an issue with storage engines. If there is a result, then it’s probably due to a data error.
Regards,
Alec Smecher
Public Knowledge Project Team
you are right if I execute the ‘alter’ statement I get the same result.
Then I checked the engine with the statement
SELECT TABLE_NAME, ENGINE FROM information_schema. TABLES where TABLE_SCHEMA = 'ojsutf';
and all the tables have InnoDB (including submission_file_revisions and submission_files).
But I am confused withe execution of the count statement. This is C&P from of my actual code (that is copy of yours)
SELECT COUNT(*)
FROM submission_file_revisions sfr
LEFT JOIN submission_files ON (sfr.submission_file_id = sf.submission_file_id)
WHERE sf.submission_file_id IS NOT NULL;
And the result is:
#1054 - Unknown column 'sf.submission_file_id' in 'where clause'
I don’t see where is the catch. The statement look OK to me.
The table itself looks OK and has the submission_file_id column.
Uf, good point, I simply didn’t notice it. As I expected (I checked with similar statement) the result is 2183.
If I understand correctly that means something is wrong with the data. Can you please explain to me what does it mean, what is wrong? What can I check and fix? Is it connected with the ‘Removing orphaned review_files’ entries in the upgrade log?
I tried once more to do the upgrade, just in case, and investigating it a bit more.
I checked what is with one of the files from the ‘missing list’. That is I looked for a file that has entry in the submission_file_revisions but not in the submission_files. I looked at the files table and then on the filesystem and the file is there.
Does that help in any way? What is actually going on during the upgrade and how these two tables are being built? Maybe something is wrong there? Should I just delete all the files from the missing list and start upgrade again?
thank you for your help, looks like I solved the issues.
As I understand during the upgrade the above mentioned tables are somehow generated. Looks like the table submission_file_revisions contains records of the files that are found on the filesystem. The table submission_files contains a kind of files that are really required (my assumption). My conclusion is that there are files on the filesystem that are not really required and can be deleted.
At the moment I have disabled the constraint that caused the issue (commented out lines around 668 in the file classes/migration/upgrade/PKPv3_3_0UpgradeMigration.php) and I got running system.
Later on I will investigate the files and most probably delete the files and corresponding entires in the submission_file_revisions table. Afterward will manually add constraint that I commented out from the upgrade script.
Hopefully that will work correctly.