org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO stg_external_person (batch_id,school_id,username,first_name,middle_name,last_name,birth_date,primary_email_address,address_line_1,address_line_2,city,state,zip_code,residency_county,non_local_address,home_phone,work_phone,cell_phone,office_location,office_hours,department_name,actual_start_term,actual_start_year,marital_status,ethnicity,gender,f1_status,balance_owed,coach_school_id,photo_url,student_type_code,race_code) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);]; Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails.; nested exception is java.sql.BatchUpdateException: Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails.

Apereo Issues | Dan McCallum | 3 years ago
  1. 0

    From the field today... if {{external_person.csv}} has empty values for the {{non_local_address}} column, this problem doesn't seem to be discovered until the writes to the staging table: {noformat} 16:40:01.381 [taskExecutor-1] ERROR o.j.s.u.i.j.l.StagingAndUpsertSkipListener - ERROR on Stage/Upsert Write org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO stg_external_person (batch_id,school_id,username,first_name,middle_name,last_name,birth_date,primary_email_address,address_line_1,address_line_2,city,state,zip_code,residency_county,non_local_address,home_phone,work_phone,cell_phone,office_location,office_hours,department_name,actual_start_term,actual_start_year,marital_status,ethnicity,gender,f1_status,balance_owed,coach_school_id,photo_url,student_type_code,race_code) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);]; Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails.; nested exception is java.sql.BatchUpdateException: Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.jasig.ssp.util.importer.job.staging.SqlServerStagingTableWriter.write(SqlServerStagingTableWriter.java:119) ~[ssp-data-importer-impl-1.0.0.jar:1.0.0] at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source) ~[na:na] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.6.0_31] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.6.0_31] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:132) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:120) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at $Proxy0.write(Unknown Source) ~[na:na] at org.springframework.batch.item.support.CompositeItemWriter.write(CompositeItemWriter.java:51) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source) ~[na:na] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.6.0_31] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.6.0_31] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:132) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:120) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at $Proxy8.write(Unknown Source) ~[na:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.scan(FaultTolerantChunkProcessor.java:580) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.access$900(FaultTolerantChunkProcessor.java:50) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$3.doWithRetry(FaultTolerantChunkProcessor.java:348) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:255) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:188) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.BatchRetryTemplate.execute(BatchRetryTemplate.java:217) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.write(FaultTolerantChunkProcessor.java:423) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) [spring-tx-3.2.0.RELEASE.jar:3.2.0.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368) [spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) [spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) [spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:253) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:139) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:136) [spring-batch-core-2.2.2.RELEASE.jar:na] at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) [na:1.6.0_31] at java.util.concurrent.FutureTask.run(Unknown Source) [na:1.6.0_31] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) [na:1.6.0_31] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.6.0_31] at java.lang.Thread.run(Unknown Source) [na:1.6.0_31] Caused by: java.sql.BatchUpdateException: Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails. at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:947) ~[jtds-1.2.4.jar:1.2.4] at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) ~[commons-dbcp-1.2.2.jar:1.2.2] at org.jasig.ssp.util.importer.job.staging.SqlServerStagingTableWriter$1.doInPreparedStatement(SqlServerStagingTableWriter.java:144) ~[ssp-data-importer-impl-1.0.0.jar:1.0.0] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] ... 51 common frames omitted {noformat} I would have expected this to be caught during the initial validation if we're using db metadata. One problem with doing it so late is that it seems to make the line numbers in the report skew so they don't match up with line numbers in the original input file. Not sure if that's b/c of other validation-related skips or if it's because of the batched db inserts making it hard to give a precise row number.

    Apereo Issues | 3 years ago | Dan McCallum
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO stg_external_person (batch_id,school_id,username,first_name,middle_name,last_name,birth_date,primary_email_address,address_line_1,address_line_2,city,state,zip_code,residency_county,non_local_address,home_phone,work_phone,cell_phone,office_location,office_hours,department_name,actual_start_term,actual_start_year,marital_status,ethnicity,gender,f1_status,balance_owed,coach_school_id,photo_url,student_type_code,race_code) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);]; Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails.; nested exception is java.sql.BatchUpdateException: Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails.
  2. 0

    From the field today... if {{external_person.csv}} has empty values for the {{non_local_address}} column, this problem doesn't seem to be discovered until the writes to the staging table: {noformat} 16:40:01.381 [taskExecutor-1] ERROR o.j.s.u.i.j.l.StagingAndUpsertSkipListener - ERROR on Stage/Upsert Write org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO stg_external_person (batch_id,school_id,username,first_name,middle_name,last_name,birth_date,primary_email_address,address_line_1,address_line_2,city,state,zip_code,residency_county,non_local_address,home_phone,work_phone,cell_phone,office_location,office_hours,department_name,actual_start_term,actual_start_year,marital_status,ethnicity,gender,f1_status,balance_owed,coach_school_id,photo_url,student_type_code,race_code) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);]; Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails.; nested exception is java.sql.BatchUpdateException: Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.jasig.ssp.util.importer.job.staging.SqlServerStagingTableWriter.write(SqlServerStagingTableWriter.java:119) ~[ssp-data-importer-impl-1.0.0.jar:1.0.0] at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source) ~[na:na] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.6.0_31] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.6.0_31] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:132) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:120) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at $Proxy0.write(Unknown Source) ~[na:na] at org.springframework.batch.item.support.CompositeItemWriter.write(CompositeItemWriter.java:51) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source) ~[na:na] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.6.0_31] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.6.0_31] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:132) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:120) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at $Proxy8.write(Unknown Source) ~[na:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.scan(FaultTolerantChunkProcessor.java:580) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.access$900(FaultTolerantChunkProcessor.java:50) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$3.doWithRetry(FaultTolerantChunkProcessor.java:348) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:255) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:188) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.BatchRetryTemplate.execute(BatchRetryTemplate.java:217) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.write(FaultTolerantChunkProcessor.java:423) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) [spring-tx-3.2.0.RELEASE.jar:3.2.0.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368) [spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) [spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) [spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:253) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:139) [spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:136) [spring-batch-core-2.2.2.RELEASE.jar:na] at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) [na:1.6.0_31] at java.util.concurrent.FutureTask.run(Unknown Source) [na:1.6.0_31] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) [na:1.6.0_31] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.6.0_31] at java.lang.Thread.run(Unknown Source) [na:1.6.0_31] Caused by: java.sql.BatchUpdateException: Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails. at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:947) ~[jtds-1.2.4.jar:1.2.4] at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) ~[commons-dbcp-1.2.2.jar:1.2.2] at org.jasig.ssp.util.importer.job.staging.SqlServerStagingTableWriter$1.doInPreparedStatement(SqlServerStagingTableWriter.java:144) ~[ssp-data-importer-impl-1.0.0.jar:1.0.0] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] ... 51 common frames omitted {noformat} I would have expected this to be caught during the initial validation if we're using db metadata. One problem with doing it so late is that it seems to make the line numbers in the report skew so they don't match up with line numbers in the original input file. Not sure if that's b/c of other validation-related skips or if it's because of the batched db inserts making it hard to give a precise row number.

    Apereo Issues | 3 years ago | Dan McCallum
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO stg_external_person (batch_id,school_id,username,first_name,middle_name,last_name,birth_date,primary_email_address,address_line_1,address_line_2,city,state,zip_code,residency_county,non_local_address,home_phone,work_phone,cell_phone,office_location,office_hours,department_name,actual_start_term,actual_start_year,marital_status,ethnicity,gender,f1_status,balance_owed,coach_school_id,photo_url,student_type_code,race_code) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);]; Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails.; nested exception is java.sql.BatchUpdateException: Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails.
  3. 0

    org.springframework.dao.DataIntegrityViolationException Caused by: java.sql.BatchUpdateException: Data truncation

    Stack Overflow | 2 years ago
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO USERS_temp(uid, FirstName, MI, LastName, SyncDate, Status, DisabledDateTime, Exclude, Notes, UpdateID, UpdateDateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?)]; Data truncation; nested exception is java.sql.BatchUpdateException: Data truncation
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    sonarqube: violation of primary key in 'issues'

    Stack Overflow | 1 year ago | nilleb
    org.apache.ibatis.executor.BatchExecutorException: org.sonar.core.issue.db.IssueMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint 'PK__issues__3213E83FDBC62EB5'. Cannot insert duplicate key in object 'dbo.issues'. The duplicate key value is (147442).
  6. 0

    Hibernate sets and many to many index column issue

    Stack Overflow | 4 years ago | The Thom
    com.acs.gs.juror.dao.FailedInsertException: Unable to save object:ALTF Funds By Case|ALTF Funds By Case Report|java.util.GregorianCalendar[time=18000000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="America/New_York",offset=-18000000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=America/New_York,offset=-18000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=1970,MONTH=0,WEEK_OF_YEAR=1,WEEK_OF_MONTH=1,DAY_OF_MONTH=1,DAY_OF_YEAR=1,DAY_OF_WEEK=5,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=-18000000,DST_OFFSET=0]|null|ALTFFundsByCase||PDF|[]|Lexptrain|Lexptrain|314||REPORTING|OUTPUT_TYPE|Browser|Browser|Jan 1, 1970 12:00:00 AM||null|null|null|null|null|null|null|null|null|null|null|

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.BatchUpdateException

      Cannot insert the value NULL into column 'non_local_address', table 'ssp-2-2.dbo.stg_external_person'; column does not allow nulls. INSERT fails.

      at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch()
    2. jTDS
      JtdsStatement.executeBatch
      1. net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:947)[jtds-1.2.4.jar:1.2.4]
      1 frame
    3. Commons DBCP
      DelegatingStatement.executeBatch
      1. org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)[commons-dbcp-1.2.2.jar:1.2.2]
      1 frame
    4. org.jasig.ssp
      SqlServerStagingTableWriter$1.doInPreparedStatement
      1. org.jasig.ssp.util.importer.job.staging.SqlServerStagingTableWriter$1.doInPreparedStatement(SqlServerStagingTableWriter.java:144)[ssp-data-importer-impl-1.0.0.jar:1.0.0]
      1 frame
    5. Spring Framework
      JdbcTemplate.execute
      1. org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      2. org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      2 frames
    6. org.jasig.ssp
      SqlServerStagingTableWriter.write
      1. org.jasig.ssp.util.importer.job.staging.SqlServerStagingTableWriter.write(SqlServerStagingTableWriter.java:119)[ssp-data-importer-impl-1.0.0.jar:1.0.0]
      1 frame
    7. Java RT
      Method.invoke
      1. sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)[na:na]
      2. sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)[na:1.6.0_31]
      3. java.lang.reflect.Method.invoke(Unknown Source)[na:1.6.0_31]
      3 frames
    8. Spring AOP
      JdkDynamicAopProxy.invoke
      1. org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      2. org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      3. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      4. org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:132)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      5. org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:120)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      6. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      7. org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      7 frames
    9. Unknown
      $Proxy0.write
      1. $Proxy0.write(Unknown Source)[na:na]
      1 frame
    10. Spring Batch Infrastructure
      CompositeItemWriter.write
      1. org.springframework.batch.item.support.CompositeItemWriter.write(CompositeItemWriter.java:51)[spring-batch-infrastructure-2.2.2.RELEASE.jar:na]
      1 frame
    11. Java RT
      Method.invoke
      1. sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)[na:na]
      2. sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)[na:1.6.0_31]
      3. java.lang.reflect.Method.invoke(Unknown Source)[na:1.6.0_31]
      3 frames
    12. Spring AOP
      JdkDynamicAopProxy.invoke
      1. org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      2. org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      3. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      4. org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:132)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      5. org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:120)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      6. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      7. org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE]
      7 frames
    13. Unknown
      $Proxy8.write
      1. $Proxy8.write(Unknown Source)[na:na]
      1 frame
    14. Spring Batch Core
      FaultTolerantChunkProcessor$3.doWithRetry
      1. org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175)[spring-batch-core-2.2.2.RELEASE.jar:na]
      2. org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.scan(FaultTolerantChunkProcessor.java:580)[spring-batch-core-2.2.2.RELEASE.jar:na]
      3. org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.access$900(FaultTolerantChunkProcessor.java:50)[spring-batch-core-2.2.2.RELEASE.jar:na]
      4. org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$3.doWithRetry(FaultTolerantChunkProcessor.java:348)[spring-batch-core-2.2.2.RELEASE.jar:na]
      4 frames
    15. Spring Retry
      RetryTemplate.execute
      1. org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:255)[spring-retry-1.0.2.RELEASE.jar:na]
      2. org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:188)[spring-retry-1.0.2.RELEASE.jar:na]
      2 frames
    16. Spring Batch Core
      TaskletStep$ChunkTransactionCallback.doInTransaction
      1. org.springframework.batch.core.step.item.BatchRetryTemplate.execute(BatchRetryTemplate.java:217)[spring-batch-core-2.2.2.RELEASE.jar:na]
      2. org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.write(FaultTolerantChunkProcessor.java:423)[spring-batch-core-2.2.2.RELEASE.jar:na]
      3. org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199)[spring-batch-core-2.2.2.RELEASE.jar:na]
      4. org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75)[spring-batch-core-2.2.2.RELEASE.jar:na]
      5. org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395)[spring-batch-core-2.2.2.RELEASE.jar:na]
      5 frames
    17. Spring Tx
      TransactionTemplate.execute
      1. org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)[spring-tx-3.2.0.RELEASE.jar:3.2.0.RELEASE]
      1 frame
    18. Spring Batch Core
      StepContextRepeatCallback.doInIteration
      1. org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267)[spring-batch-core-2.2.2.RELEASE.jar:na]
      2. org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)[spring-batch-core-2.2.2.RELEASE.jar:na]
      2 frames
    19. Spring Batch Infrastructure
      RepeatTemplate.iterate
      1. org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)[spring-batch-infrastructure-2.2.2.RELEASE.jar:na]
      2. org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)[spring-batch-infrastructure-2.2.2.RELEASE.jar:na]
      3. org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)[spring-batch-infrastructure-2.2.2.RELEASE.jar:na]
      3 frames
    20. Spring Batch Core
      TaskExecutorPartitionHandler$1.call
      1. org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:253)[spring-batch-core-2.2.2.RELEASE.jar:na]
      2. org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195)[spring-batch-core-2.2.2.RELEASE.jar:na]
      3. org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:139)[spring-batch-core-2.2.2.RELEASE.jar:na]
      4. org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:136)[spring-batch-core-2.2.2.RELEASE.jar:na]
      4 frames
    21. Java RT
      Thread.run
      1. java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)[na:1.6.0_31]
      2. java.util.concurrent.FutureTask.run(Unknown Source)[na:1.6.0_31]
      3. java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)[na:1.6.0_31]
      4. java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)[na:1.6.0_31]
      5. java.lang.Thread.run(Unknown Source)[na:1.6.0_31]
      5 frames