Native XML import error

Describe the issue or problem
Please tell us what happens and what you expected to happen.

Steps I took leading up to the issue
For example:

  1. Go to ‘…’
    Native xml export

  2. Click on ‘…’
    Import to other journal

  3. See error

Warnings encountered:

Issue

  • Existing issue with id 2 matches the given issue identification “<issue_identification> 6 12 2023 International Research Journal of Ayurveda & Yoga (Dec) (2023) </issue_identification>”. This issue will not be modified, but articles will be added.

Errors occured:

Generic Items

  • SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (ijvsbsgt_irjay.submission_files, CONSTRAINT submission_files_source_submission_file_id_foreign FOREIGN KEY (source_submission_file_id) REFERENCES submission_files (submission_file_i) (SQL: insert into submission_files (created_at, file_id, file_stage, genre_id, source_submission_file_id, submission_id, updated_at, uploader_user_id, viewable`) values (2024-01-26 06:42:41, 5, 2, 1, 18326, 5, 2024-01-26 06:42:41, 1, 1))

What application are you using?
For example, OJS 3.4.0-4

Additional information

Anyone pls look into this specially i request to staff…

Hi @Nive! Thanks for that.

It seems that in your case the export tool tries to export a submission file that has a source_submission_file_id value but no associated entity, meaning assoc_id and assoc_type values assigned to it.

The absence of assoc_id and assoc_type makes the export tool export this submission file with no other questions “asked”, but the related source submissionFile (the submissionFileId=18326 in your case) is not handled at the export stage, and, consequently, is not found/handled in the import stage, resulting the DB error you encounter.

In order to investigate that further we need to have the set of data that correspond to that submission, namely, if possible, the submission files corresponding to that submission for starters.

Also we need to know the version of the installation that you export the submission (source) and the version of the one that you import it (target). Has the source installation undergone any previous upgrades, and if yes from which version/versions.

Thank you in advance.

Thanks for your time.
Our platform undergoes regular upgrades, transitioning from version 3.3.0.13 and back, with the most recent upgrade being to version 3.4.0.4. Currently, we are in the process of individually migrating our journals, originally hosted collectively on the domain abc.com (approximately 10 journals), to separate domains, such as xyz.com. To facilitate this migration, we have installed version 3.4.0.4. However, upon exporting data from this upgraded version and attempting to import it into the individual installations, we encounter the mentioned error.

Thanks @Nive, so the target and the source installations are 3.4.0.4 version of OJS, correct?

I will still need the data for this individual submission if that is possible, so I could replicate the issue and see if something needs fixing on the native import/export code side, or whether there is a workaround involving some data manipulation/clearance at the DB side.

I think that having the submission DB row and all the submissionFiles is sufficient.

Could you execute something like those in the source DB

select * from submissions a
left outer join submission_files b on a.submission_id = b.submission_id
left outer join files c on b.file_id = c.file_id
where a.submission_id = [id]

select * from submissions a
left outer join submission_files b on a.submission_id = b.submission_id
left outer join submission_files c on b.submission_id = c.source_submission_file_id
where a.submission_id = [id]

where [id] is the id of the submission that fails?

Hope it help you
select * from submissions a
left outer join submission_files b on a.submission_id = b.submission_id
left outer join files c on b.file_id = c.file_id
where a.submission_id = 12970;
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0007 seconds.)

select * from submissions a
left outer join submission_files b on a.submission_id = b.submission_id
left outer join submission_files c on b.submission_id = c.source_submission_file_id
where a.submission_id = 12970;
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0006 seconds.)

@Nive

  1. Are both your source installation and your target installation OJS 3.4.0.4?
  2. Did you execute that queries on your source installation?
  3. Is the 12970 the id of the submission (not submissionFile) that fails - that is the id on the source installation, not the target installation?

1 Yes, both versions are 3.4.0.4
2. In imported installation, errors may arise during the import process.
3. 12970 come in front screenshot attached

@Nive thanks, regarding this “Did you execute that queries on your source installation?” maybe I was not very clear - the query

select * from submissions a
left outer join submission_files b on a.submission_id = b.submission_id
left outer join files c on b.file_id = c.file_id
where a.submission_id = 12970;

must be executed on your source installation DB - meaning the DB that contains the initial/exported submissions.

@Nive thanks for sharing the data requested for the submission_files of the submission that is failing.

Down below I am adding the specific data, so I can better explain what, I think, the problem is:

submission_file 1

submission_file_id submission_id file_id source_submission_file_id genre_id file_stage direct_sales_price sales_type viewable created_at updated_at uploader_user_id assoc_type assoc_id
18789 12970 14435 NULL 567 10 NULL NULL NULL 2024-01-25 06:25:36 2024-01-25 06:25:40 239 521 11828

submission_file 2

submission_file_id submission_id file_id source_submission_file_id genre_id file_stage direct_sales_price sales_type viewable created_at updated_at uploader_user_id assoc_type assoc_id
18790 12970 14435 18789 567 2 NULL NULL 1 2024-01-25 06:26:14 2024-01-25 06:26:14 239 521 11828

submission_file 1 seems to be referenced by submission_file 2 through the source_submission_file_id column.

submission_file 1 has file_stage = SubmissionFile::SUBMISSION_FILE_PROOF (10) and assoc_type = PKPApplication::ASSOC_TYPE_REPRESENTATION (521)

submission_file 2 has file_stage = SubmissionFile::SUBMISSION_FILE_SUBMISSION (2) and assoc_type = PKPApplication::ASSOC_TYPE_REPRESENTATION (521)

Checking the submissionFile/Repository.php here I see that there could be a conflict between the assoc_type = PKPApplication::ASSOC_TYPE_REPRESENTATION and a file_stage that is not SubmissionFile::SUBMISSION_FILE_PROOF.

The above pair mismatch seems to be the case in submission_file 2 that is actually causing the problem during the import process. This file, as mentioned before, has an assoc_type = PKPApplication::ASSOC_TYPE_REPRESENTATION and a file_stage = SubmissionFile::SUBMISSION_FILE_SUBMISSION which does not match the necessary SubmissionFile::SUBMISSION_FILE_PROOF

Can you find the submission_file 2 in your source installation OJS web application and possibly replicate the creation of that specific submission_file?

Thank you for bringing this issue to our attention. Open Journal Systems (OJS) ensures that each ID and submission is unique to avoid any conflicts. However, it seems there’s an issue with the association between submission files, causing discrepancies in file stages and association types.

To address this, we kindly request the technical team of OJS to investigate this matter further. It’s crucial to understand why this issue occurred and ensure it’s resolved in future releases of the platform.

Dear Dimitris_Efstathiou,

Thank you for your assistance in providing this explanation. Your insight has been incredibly helpful in shedding light on the issue at hand. We deeply appreciate your contribution to clarifying the situation.

1 Like