Web app (Japanese questionnaire) throwing database error

[shanecoughlan]

Hello Mr Shane

I've met an error message on our OpenChain Self Certification Questionnaire v2.1.1. And it said that SQL exception error happen and need to notice this error message to OpenChain Technical team. Please tell me how to notice this to them. Or send this message to them.

summary: In OpenChain Self Certification Questionnaire v2.1.1 the following error occurs when clicking "Save Answers" in Japanese Language after selecting all the items

Parameter: the specification v2.1, Language Japanese, version.

Error message: エラー 予期しないSQL例外次のエラーの発生をOpenChainテクニカルチームに通知してください: Batch entry 0 insert into answer (response_id, question_id, answer, evidence) values((select id from survey_response where user_id=171 and spec_version=19), (select id from question where number='1.f.iii' and section_id in (select id from section where spec_version=19)), 'No', NULL) was aborted: ERROR: more than one row returned by a subquery used as an expression Call getNextException to see the cause.

Steps:

1. Access the site https://certification.openchainproject.org/ with logged in via sign up
2. Check the service version : 2.1.1
3. Check the web service Laguage: ja, Spec version: 2.1 and Questionnaire Language: ja
4. Answer all question with "false" in Japanese Language
5. Pudh "Save Answers" in Japanese Language

Browser: Chrome 87.0.4280.88 (Official Build) 64bit on Windows 10

Note: It happened to me but it may not to the other.

[goneall]

@shanecoughlan Looking into it now

[goneall]

In reviewing the error message, logs, and the source code - it looks like there are multiple responses to the same specification version in the database. This is clearly a bug in the web application, but I could not identify the source of the problem from the information available.

Multiple responses for the same version should not occur since a response is only created when no responses exists or when the questionnaire response is reset. On reset, the old questionnaire response is deleted before the new one is created.

@shanecoughlan It would be helpful to understand if the user reset or changed versions prior to this error occurring.

I will add some additional checks and logging code in the web app to fail sooner and provide more information. I will also see if I can clean up the database to remove the error for this user. I'll update this issue once these tasks are complete.

[shanecoughlan]

Adding Dote San to address the questions directly.

Dote San, Gary asked: @shanecoughlan It would be helpful to understand if the user reset or changed versions prior to this error occurring.

On Dec 16, 2020, at 12:59, goneall notifications@github.com wrote:

In reviewing the error message, logs, and the source code - it looks like there are multiple responses to the same specification version in the database. This is clearly a bug in the web application, but I could not identify the source of the problem from the information available.

Multiple responses for the same version should not occur since a response is only created when no responses exists or when the questionnaire response is reset. On reset, the old questionnaire response is deleted before the new one is created.

@shanecoughlan It would be helpful to understand if the user reset or changed versions prior to this error occurring.

I will add some additional checks and logging code in the web app to fail sooner and provide more information. I will also see if I can clean up the database to remove the error for this user. I'll update this issue once these tasks are complete.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[shanecoughlan]

Using better address for Dote San.

==

Adding Dote San to address the questions directly.

Dote San, Gary asked: @shanecoughlan It would be helpful to understand if the user reset or changed versions prior to this error occurring.

On Dec 16, 2020, at 12:59, goneall notifications@github.com wrote:

In reviewing the error message, logs, and the source code - it looks like there are multiple responses to the same specification version in the database. This is clearly a bug in the web application, but I could not identify the source of the problem from the information available.

Multiple responses for the same version should not occur since a response is only created when no responses exists or when the questionnaire response is reset. On reset, the old questionnaire response is deleted before the new one is created.

@shanecoughlan It would be helpful to understand if the user reset or changed versions prior to this error occurring.

I will add some additional checks and logging code in the web app to fail sooner and provide more information. I will also see if I can clean up the database to remove the error for this user. I'll update this issue once these tasks are complete.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[fu7mu4]

Hi I'm Dote.

The answer is Yes. I had reset and changed version prior to this error occurring.

[goneall]

The application has been updated to fail sooner when a duplicate response has been added. This will not resolve the issue, but will provide more information if the error happens again.

I also removed all responses except the very last one added. The error should not occur unless another duplicate responses is added.

Dote San, please try the app again and let us know if there are any issues.

[fu7mu4]

now it success !

[fu7mu4]

OK I'll try it with data reset again.

[fu7mu4]

I had reset data and try this again. It success without no error

[shanecoughlan]

(^ ^)/ Thank you @fu7mu4 ! Thank you @goneall !

[goneall]

@fu7mu4 Your welcome and thank you for the very detailed description of the error.

[fu7mu4]

Thank you for your quick response. @goneall @shanecoughlan !