Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Hyland Connect
- Enterprise Platforms
- Alfresco
- Alfresco Archive
- foreign key constraint fails aaar_datamart.dm_dim_...
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
foreign key constraint fails aaar_datamart.dm_dim_months
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-08-2014 12:56 PM
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, year_id FROM dm_dim_months WHERE ( ( id = ? ) ) ]
2014/12/04 12:09:48 - AAAR_DataMart - Preparing statement:
2014/12/04 12:09:48 - AAAR_DataMart - INSERT INTO dm_dim_months (id, `DESC`, month_desc, year_id) VALUES ( ?, ?, ?, ?)
2014/12/04 12:09:48 - dm_dim_months.0 - Setting update preparedStatement to [UPDATE dm_dim_months
2014/12/04 12:09:48 - dm_dim_months.0 - SET `DESC` = ?
2014/12/04 12:09:48 - dm_dim_months.0 - , month_desc = ?
2014/12/04 12:09:48 - dm_dim_months.0 - , year_id = ?
2014/12/04 12:09:48 - dm_dim_months.0 - WHERE ( ( id = ? ) ) ]
2014/12/04 12:09:48 - dm_dim_months.0 - Values set for lookup: [2000-01-01]
2014/12/04 12:09:48 - dm_dim_months.0 - Values set for lookup: [2014-12-01]
2014/12/04 12:09:48 - dm_dim_months.0 - Values set for lookup: [2015-01-01]
2014/12/04 12:09:48 - AAAR_DataMart - Connection to database closed!
2014/12/04 12:09:48 - START - Finished processing (I=3, O=0, R=0, W=3, U=0, E=0)
2014/12/04 12:09:48 - dm_dim_minutes.0 - Checking row: [-1], [UNKNOWN], [-1]
2014/12/04 12:09:48 - dm_dim_minutes.0 - Field [minute] has nr. 0
2014/12/04 12:09:48 - dm_dim_minutes.0 - Field [minute] has nr. 0
2014/12/04 12:09:48 - dm_dim_minutes.0 - Field [desc] has nr. 1
2014/12/04 12:09:48 - dm_dim_minutes.0 - Field [hour_id] has nr. 2
2014/12/04 12:09:48 - dm_dim_minutes.0 - Setting preparedStatement to [SELECT id, `DESC`, hour_id FROM dm_dim_minutes WHERE ( ( id = ? ) ) ]
2014/12/04 12:09:48 - AAAR_DataMart - Preparing statement:
2014/12/04 12:09:48 - AAAR_DataMart - INSERT INTO dm_dim_minutes (id, `DESC`, hour_id) VALUES ( ?, ?, ?)
2014/12/04 12:09:48 - dm_dim_minutes.0 - Setting update preparedStatement to [UPDATE dm_dim_minutes
2014/12/04 12:09:48 - dm_dim_minutes.0 - SET `DESC` = ?
2014/12/04 12:09:48 - dm_dim_minutes.0 - , hour_id = ?
2014/12/04 12:09:48 - dm_dim_minutes.0 - WHERE ( ( id = ? ) ) ]
2014/12/04 12:09:48 - dm_dim_minutes.0 - Values set for lookup: [-1]
2014/12/04 12:09:48 - minute.0 - Signaling 'output done' to 1 output rowsets.
2014/12/04 12:09:48 - minute.0 - Finished reading query, closing connection.
2014/12/04 12:09:48 - AAAR_DataMart - Connection to database closed!
2014/12/04 12:09:48 - minute.0 - Finished processing (I=1441, O=0, R=0, W=1441, U=0, E=0)
2014/12/04 12:09:48 - dm_dim_minutes.0 - Values set for lookup: [0]
2014/12/04 12:09:48 - dm_dim_minutes.0 - Values set for lookup: [1]
2014/12/04 12:09:48 - dm_dim_months.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Unexpected error
2014/12/04 12:09:48 - dm_dim_months.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
2014/12/04 12:09:48 - dm_dim_months.0 - Error in step, asking everyone to stop because of:
2014/12/04 12:09:48 - dm_dim_months.0 -
2014/12/04 12:09:48 - dm_dim_months.0 - Error inserting/updating row
2014/12/04 12:09:48 - dm_dim_months.0 - Cannot add or update a child row: a foreign key constraint fails (`aaar_datamart`.`dm_dim_months`, CONSTRAINT `fk_dm_dim_months_dm_dim_years` FOREIGN KEY (`year_id`) REFERENCES `dm_dim_years` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
</blockquote>
I found another thread about the same issue, but I don't believe that the connection between Alfresco and AAAR is the problem here.
Executing AAAR_Extract.sh yields:
<blockquote>
2014/12/04 12:09:48 - dm_dim_months.0 - Setting preparedStatement to [SELECT id, `DESC`, month_desc, year_id FROM dm_dim_months WHERE ( ( id = ? ) ) ]
2014/12/04 12:09:48 - AAAR_DataMart - Preparing statement:
2014/12/04 12:09:48 - AAAR_DataMart - INSERT INTO dm_dim_months (id, `DESC`, month_desc, year_id) VALUES ( ?, ?, ?, ?)
2014/12/04 12:09:48 - dm_dim_months.0 - Setting update preparedStatement to [UPDATE dm_dim_months
2014/12/04 12:09:48 - dm_dim_months.0 - SET `DESC` = ?
2014/12/04 12:09:48 - dm_dim_months.0 - , month_desc = ?
2014/12/04 12:09:48 - dm_dim_months.0 - , year_id = ?
2014/12/04 12:09:48 - dm_dim_months.0 - WHERE ( ( id = ? ) ) ]
2014/12/04 12:09:48 - dm_dim_months.0 - Values set for lookup: [2000-01-01]
2014/12/04 12:09:48 - dm_dim_months.0 - Values set for lookup: [2014-12-01]
2014/12/04 12:09:48 - dm_dim_months.0 - Values set for lookup: [2015-01-01]
2014/12/04 12:09:48 - AAAR_DataMart - Connection to database closed!
2014/12/04 12:09:48 - START - Finished processing (I=3, O=0, R=0, W=3, U=0, E=0)
2014/12/04 12:09:48 - dm_dim_minutes.0 - Checking row: [-1], [UNKNOWN], [-1]
2014/12/04 12:09:48 - dm_dim_minutes.0 - Field [minute] has nr. 0
2014/12/04 12:09:48 - dm_dim_minutes.0 - Field [minute] has nr. 0
2014/12/04 12:09:48 - dm_dim_minutes.0 - Field [desc] has nr. 1
2014/12/04 12:09:48 - dm_dim_minutes.0 - Field [hour_id] has nr. 2
2014/12/04 12:09:48 - dm_dim_minutes.0 - Setting preparedStatement to [SELECT id, `DESC`, hour_id FROM dm_dim_minutes WHERE ( ( id = ? ) ) ]
2014/12/04 12:09:48 - AAAR_DataMart - Preparing statement:
2014/12/04 12:09:48 - AAAR_DataMart - INSERT INTO dm_dim_minutes (id, `DESC`, hour_id) VALUES ( ?, ?, ?)
2014/12/04 12:09:48 - dm_dim_minutes.0 - Setting update preparedStatement to [UPDATE dm_dim_minutes
2014/12/04 12:09:48 - dm_dim_minutes.0 - SET `DESC` = ?
2014/12/04 12:09:48 - dm_dim_minutes.0 - , hour_id = ?
2014/12/04 12:09:48 - dm_dim_minutes.0 - WHERE ( ( id = ? ) ) ]
2014/12/04 12:09:48 - dm_dim_minutes.0 - Values set for lookup: [-1]
2014/12/04 12:09:48 - minute.0 - Signaling 'output done' to 1 output rowsets.
2014/12/04 12:09:48 - minute.0 - Finished reading query, closing connection.
2014/12/04 12:09:48 - AAAR_DataMart - Connection to database closed!
2014/12/04 12:09:48 - minute.0 - Finished processing (I=1441, O=0, R=0, W=1441, U=0, E=0)
2014/12/04 12:09:48 - dm_dim_minutes.0 - Values set for lookup: [0]
2014/12/04 12:09:48 - dm_dim_minutes.0 - Values set for lookup: [1]
2014/12/04 12:09:48 - dm_dim_months.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Unexpected error
2014/12/04 12:09:48 - dm_dim_months.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
2014/12/04 12:09:48 - dm_dim_months.0 - Error in step, asking everyone to stop because of:
2014/12/04 12:09:48 - dm_dim_months.0 -
2014/12/04 12:09:48 - dm_dim_months.0 - Error inserting/updating row
2014/12/04 12:09:48 - dm_dim_months.0 - Cannot add or update a child row: a foreign key constraint fails (`aaar_datamart`.`dm_dim_months`, CONSTRAINT `fk_dm_dim_months_dm_dim_years` FOREIGN KEY (`year_id`) REFERENCES `dm_dim_years` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
</blockquote>
I found another thread about the same issue, but I don't believe that the connection between Alfresco and AAAR is the problem here.
Labels:
- Labels:
-
Archive
2 REPLIES 2
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-09-2014 02:04 AM
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.
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.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-09-2014 01:00 PM
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:
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.
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.
Related Content
- Alfresco Community Edition 23.4 Release Notes in Alfresco Blog
- AFTS query doesn't work with text in Alfresco Forum
- Alfresco workflow - multi instance task with completion condition in Alfresco Forum
- SQLIntegrityConstraintViolationException when inserting data in ACT_HI_VARINST table in Alfresco Forum
- PermissionService.deletePermissions throwing DataIntegrityViolationException in Alfresco Forum
Getting started
Tags
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.
