article_files table has duplicate primary key entries

[barbarah]

I just noticed that through the course of the submission/review/copyedit process, OJS sometimes creates multiple article_files database records with the same file_id. Since file_id is the primary key for the table, this means there is no enforced unique identifier for these records. For example, we have 5 records with file_id = 49 in our ojs database:

mysql> select file_id, revision, type, article_id, file_name, file_type, original_file_name from article_files where file_id = 49;
+---------+----------+---------------------+------------+----------------+--------------------+---------------------------------------+
| file_id | revision | type | article_id | file_name | file_type | original_file_name |
+---------+----------+---------------------+------------+----------------+--------------------+---------------------------------------+
| 49 | 1 | submission/review | 13 | 13-49-1-RV.doc | application/msword | 13-48-1-SM.doc |
| 49 | 2 | submission/copyedit | 13 | 13-49-2-CE.doc | application/msword | Announcing the latest XTF release.doc |
| 49 | 3 | submission/copyedit | 13 | 13-49-3-CE.doc | application/msword | mellonMATC2009Final.doc |
| 49 | 4 | submission/copyedit | 13 | 13-49-4-CE.doc | application/msword | mellonMATC2009Final.doc |
| 49 | 5 | submission/copyedit | 13 | 13-49-5-CE.doc | application/msword | mellonMATC2009Final.doc |
+---------+----------+---------------------+------------+----------------+--------------------+---------------------------------------+
5 rows in set (0.00 sec)

I searched the forum to see if this issue has been raised before, but I can't find any info on it (it only seems to have come up when people try to migrate their database and postgresql/mysql throws an error since the primary key rule is violated by the data). Have other people run into this problem? It seems like this could be a big issue, for example when the code tries to find a record based on its file_id. How does OJS know which record to use? We've noticed some problems with uploading files before, and are wondering if this could be part of the problem?

I also see that in the articles table, the revised_file_id, review_file_id and editor_file_id fields are links to the article_files table on file_id. But this link is broken/made ambiguous since there can be more than 1 article_files records with a given file_id.

Any thoughts/feedback appreciated. Thanks!

[mcrider]

Hi Barbara,

Each row in the article_files table is identified by a (file_id, revision) tuple. In the case of other tables referring to files in the article_files table, the code will always get the latest revision of that file.

Cheers,
Matt

[barbarah]

Hi Matt,

Thanks for your reply. OK, that's what I figured out after I posted; good to have confirmation though. Not an ideal situation, but workable :-)

-Barbara