Issues

Select view

  • Select search mode

 
7 of 7

Importing grades spreadsheet into Gradebook 2 results in "constraint violation" error

Duplicate

Description

Support has received two reports of instructors getting an error when attempting to upload grades into Gradebook 2; "ERROR: Database constraint violation occurred. : 500" (see attached screenshot).

In both cases, I was able to replicate the error and, after some effort, eventually complete a successful upload. It is unclear what is triggering the error. In the first reported case, simply reducing the number of students in the file to <500 allowed the upload, however the file in the second case uploaded with over 600 students once I removed duplicate uniqnames and added missing uniqnames to the file. The files in question are attached to the respective FootPrints tickets.

Incident details:

Ticket 211859 = BIO171 F12 (leidieti) d0557774-48c7-424c-a6bf-7789a284bdaf

Ticket 213265 = BIOLOGY 172 001 F12 (dspill) e211df5f-7d7c-4338-9e8a-b3cefdaf56ff

Filing as a UMICH Jira as there is no QA server (of which I am aware) that is running GB2 so unable to test outside of our local instances.

Attachments

1
  • 02 Nov 2012, 06:41 AM

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

Created November 2, 2012 at 6:34 AM
Updated November 2, 2012 at 9:19 AM
Resolved November 2, 2012 at 9:19 AM

Activity

Thomas AmslerNovember 2, 2012 at 9:18 AM

The duplicate student entry in the upload spreadsheet error is fixed in Gradebook2 v1.8.0. The relevant JIRA is:

https://jira.sakaiproject.org/browse/GRBK-1315

Zhen QianNovember 2, 2012 at 8:57 AM

It turns out the CTOOLS_USER.SYS_C0039128 is an unique constraint of 'GRADABLE_OBJECT_ID' and 'STUDENT_ID' columns on GB_GRADE_RECORD_T table.

select *
from all_cons_columns
where constraint_name='SYS_C0039128'

CTOOLS_USER SYS_C0039128 GB_GRADE_RECORD_T GRADABLE_OBJECT_ID 1
CTOOLS_USER SYS_C0039128 GB_GRADE_RECORD_T STUDENT_ID 2

So it is violated whenever there are duplicate entries inside the spreadsheet being uploaded. Removing the duplicates should solved the problem.

Further fix on GB2 side might be:

1) make instruction text change in the upload screen, to warn user not to include duplicate grade items;

2) filter out the possible duplicates inside spreadsheet before inserting into db. Give user errors;

Zhen QianNovember 2, 2012 at 8:51 AM

Here is the relevant stack trace from log file:

2012-11-01 14:01:48,630 [TP-Processor321] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1, SQLState: 23000
2012-11-01 14:01:48,630 [TP-Processor321] ERROR org.hibernate.util.JDBCExceptionReporter - ORA-00001: unique constraint (CTOOLS_USER.SYS_C0039128) violated

2012-11-01 14:01:48,630 [TP-Processor321] ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
....
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (CTOOLS_USER.SYS_C0039128) violated

at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10296)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:216)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:247)
... 109 more

Jeff A ZieglerNovember 2, 2012 at 8:26 AM

The timestamp on the original screenshot was "2012-11-01 at 2.01.51 PM".