foreign key constraint fails aaar_datamart.dm_dim_months

marko_d — Mon, 08 Dec 2014 17:56:10 GMT

Alfresco Audit Analysis and Reporting 2.2 STABLE installed on Linux, with biserver-5.2.0.0, pentaho-5.2.0.0, Alfresco 5.0.b. Database is MariaDB.Executing AAAR_Extract.sh yields:<blockquote> 2014/12/04 12:09:48 - dm_dim_months.0 - Setting preparedStatement to [SELECT id, `DESC`, month_desc, ye

Re: foreign key constraint fails aaar_datamart.dm_dim_months

fcorti — Tue, 09 Dec 2014 07:04:33 GMT

Hi Marko,

The problem is there:
> 2014/12/04 12:09:48 - dm_dim_months.0 - Values set for lookup: [2015-01-01]

As you can see, the ETL tries to insert a date in the future and the foreign key of the data mart crashes.
It's an old issue that appears only on MySql platform and the last month of the year… it's an end year bug! 🙂

I have received some advice and I've updated the 2.2 release directly.
So, you can solve this issue in two different ways:
Path 1) Download again the AAAR from the Pentaho marketplace and re-install it from scratch.
Path 2) Manually insert into 'dm_dim_years' table the year 2015. The bug does not affect the results but only a sort of generic calendar.

Ehi, you are using MariaDB… nice test!
Please, keep me informed.
If interested, contact me at fcorti at gmail dot com… I would like to dive deep in this use case.

Last but not least, I would like you to advice about Alfresco CE 5.0.b.
As you can read below, the release has some important problems regarding CMIS layer.
http://fcorti.com/2014/11/11/alfresco-5-0-b-bug-solr4-cmis-1-1/

For this reason the community asked me to develop this matrix of compatibility:
http://fcorti.com/alfresco-audit-analysis-reporting/aaar-alfresco-pentaho/

I hope this will help you.

Re: foreign key constraint fails aaar_datamart.dm_dim_months

marko_d — Tue, 09 Dec 2014 18:00:13 GMT

Hi Francesco,

Thanks for the quick reply.
I tried the 2nd option (shorter path) and it worked with no errors in the AAAR log.
But some of the reports like Documents per mime type were empty, so I thought that could be caused by the CMIS layer and an upgrade to Alfresco 5.0.c should bring some improvements.
Then I upgraded Alfresco to Community Edition 5.0.c.
Now I'm getting a different error:

 
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Because of an error, this step can't continue: 
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : org.pentaho.di.core.exception.KettleException: 
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Error batch inserting rows into table [stg_cmis_documents_partial].
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Errors encountered (first 10):
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - 
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - 
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Error updating batch
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Duplicate entry '1-5bf231cf-8e36-11dd-af79-81831ee8bbc0;1.0' for key 'PRIMARY'
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - 
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - 
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:342)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:118)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at java.lang.Thread.run(Thread.java:744)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseBatchException: 
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Error updating batch
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Duplicate entry '1-5bf231cf-8e36-11dd-af79-81831ee8bbc0;1.0' for key 'PRIMARY'
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - 
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1377)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:289)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    … 3 more
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Caused by: java.sql.BatchUpdateException: Duplicate entry '1-5bf231cf-8e36-11dd-af79-81831ee8bbc0;1.0' for key 'PRIMARY'
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2056)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:285)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    … 3 more
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1-5bf231cf-8e36-11dd-af79-81831ee8bbc0;1.0' for key 'PRIMARY'
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.Util.getInstance(Util.java:386)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2460)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2008)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 -    … 5 more
2014/12/09 12:23:51 - Get CMIS documents and folders - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Errors detected!
2014/12/09 12:23:51 - Modified Java Script Value 2.0 - Finished processing (I=0, O=0, R=6000, W=6000, U=0, E=0)
2014/12/09 12:23:51 - stg_cmis_documents_partial 2.0 - Finished processing (I=0, O=5999, R=6000, W=5000, U=0, E=1)
2014/12/09 12:23:51 - Get CMIS documents and folders - Transformation detected one or more steps with errors.
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I dropped the aaar_datamart database and aaar_kettle and let the AAAR wizard to create it again but that didn't help.

Any idea for this one?

Yap, MariaDB 10.0.14 on CentOS 7 works fine so far, but there is one ugly hack: for pentaho I'm still using the mysql driver. Once the reports are generated without errors I would like to add a new config set for MariaDB in AAAR Configure step (right now the options are MySQL and PostgreSQL), maybe you can give me some hints about what needs to be done.

topic foreign key constraint fails aaar_datamart.dm_dim_months in Alfresco Archive

foreign key constraint fails aaar_datamart.dm_dim_months

Re: foreign key constraint fails aaar_datamart.dm_dim_months

Re: foreign key constraint fails aaar_datamart.dm_dim_months