Error AAAR - Error in extraction

[flavia]

HiFrancesco Corti! 

Congratulations on the AAAR! I think it's an incredible tool and I'm trying to use it.
Some errors appear after extraction and I do not know how to solve them. Could you help me please?

2018/01/04 11:21:07 - stg_workflow_instances.0 - Finished reading query, closing connection.
2018/01/04 11:21:07 - stg_workflow_instances.0 - Finished processing (I=546, O=0, R=0, W=546, U=0, E=0)
org.pentaho.di.core.exception.KettleStepException:
Error in step, asking everyone to stop because of:
Error inserting/updating row
Cannot add or update a child row: a foreign key constraint fails (`aaar_datamart`.`dm_dim_workflow_instances`, CONSTRAINT `fk_dm_dim_workflow_instances_dm_dim_dates_due` FOREIGN KEY (`due_date_id`) REFERENCES `dm_dim_dates` (`id`))
at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:313)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
Error inserting/updating row
Cannot add or update a child row: a foreign key constraint fails (`aaar_datamart`.`dm_dim_workflow_instances`, CONSTRAINT `fk_dm_dim_workflow_instances_dm_dim_dates_due` FOREIGN KEY (`due_date_id`) REFERENCES `dm_dim_dates` (`id`))
at org.pentaho.di.core.database.Database.insertRow(Database.java:1321)
at org.pentaho.di.core.database.Database.insertRow(Database.java:1245)
at org.pentaho.di.core.database.Database.insertRow(Database.java:1233)
at org.pentaho.di.core.database.Database.insertRow(Database.java:1221)
at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:114)
at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:299)
... 2 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`aaar_datamart`.`dm_dim_workflow_instances`, CONSTRAINT `fk_dm_dim_workflow_instances_dm_dim_dates_due` FOREIGN KEY (`due_date_id`) REFERENCES `dm_dim_dates` (`id`))
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
at org.pentaho.di.core.database.Database.insertRow(Database.java:1288)
... 7 more
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Unexpected error
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - Error in step, asking everyone to stop because of:
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 -
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - Error inserting/updating row
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - Cannot add or update a child row: a foreign key constraint fails (`aaar_datamart`.`dm_dim_workflow_instances`, CONSTRAINT `fk_dm_dim_workflow_instances_dm_dim_dates_due` FOREIGN KEY (`due_date_id`) REFERENCES `dm_dim_dates` (`id`))
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 -
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 -
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:313)
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - at java.lang.Thread.run(Thread.java:745)
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - Error inserting/updating row
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - Cannot add or update a child row: a foreign key constraint fails (`aaar_datamart`.`dm_dim_workflow_instances`, CONSTRAINT `fk_dm_dim_workflow_instances_dm_dim_dates_due` FOREIGN KEY (`due_date_id`) REFERENCES `dm_dim_dates` (`id`))
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 -
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1321)
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1245)
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1233)
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1221)
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:114)
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:299)
2018/01/04 11:21:08 - dm_dim_workflow_instances.0 - ... 2 more

...

[fcorti]

HI Flavia,

Thank you for the feedback.

The issue is well known and it is due to the foreign key violation on dates.

Cannot add or update a child row: a foreign key constraint fails (`aaar_datamart`.`dm_dim_workflow_instances`, CONSTRAINT `fk_dm_dim_workflow_instances_dm_dim_dates_due` FOREIGN KEY (`due_date_id`) REFERENCES `dm_dim_dates` (`id`))

Most of the times, the issue is related to a "wrong date" in the workflows instances.

Sometimes happens that the dm_dim_dates is not correctly filled.

To solve the issue, you can:

1) Check the dm_dim_dates is filled with some data (it should contain several dates).

2) Check the wrong values into the stg_workflow_instances.duedate field. For example you can create a SQL query searching for the duedate values that are not included into the dm_dim_dates.id values.

Once found the issue, the solution is quite easy:

1) There is a problem in the ETL to fill the dates and it should be solved understanding why the values are not correctly filled (usually date formats).

2) Adjust the duedate on Alfresco side because it is often a wrong value.

Cheers.