Pentaho Error: Duplicate entry for non primary key

Question

Hi guys i am new into pentaho, using pentaho kettle version 7.1 and mysql jdbc driver version is 5.1.43. I have two same table in different database. Let's called db1 is first database which hosted in production and db2 is another database which hosted in development. Both db1 and db2 have same table called dashboard_user. Table dashboard_user has primary key id which is autoincrement. But the problem is when i attempt to copy value from production to development without delete existing data in development it returns like below:

017/08/16 15:13:02 - Table output.0 - Connected to database [localhost] (commit=1000) 2017/08/16 15:13:02 - Table output.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Because of an error, this step can't continue: 2017/08/16 15:13:02 - Table output.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleException: 2017/08/16 15:13:02 - Table output.0 - Error inserting row into table [dashboard_domains] with values: [sonar.id], [2016/07/17 07:27:31.000000000], [2017/07/25 08:08:26.000000000] 2017/08/16 15:13:02 - Table output.0 - 2017/08/16 15:13:02 - Table output.0 - Error inserting/updating row 2017/08/16 15:13:02 - Table output.0 - Duplicate entry 'sonar.id' for key 'index_dashboard_domains_on_domain' 2017/08/16 15:13:02 - Table output.0 - 2017/08/16 15:13:02 - Table output.0 - 2017/08/16 15:13:02 - Table output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:385) 2017/08/16 15:13:02 - Table output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:125) 2017/08/16 15:13:02 - Table output.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62) 2017/08/16 15:13:02 - Table output.0 - at java.lang.Thread.run(Thread.java:748) 2017/08/16 15:13:02 - Table output.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 2017/08/16 15:13:02 - Table output.0 - Error inserting/updating row 2017/08/16 15:13:02 - Table output.0 - Duplicate entry 'sonar.id' for key 'index_dashboard_domains_on_domain' 2017/08/16 15:13:02 - Table output.0 - 2017/08/16 15:13:02 - Table output.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1321) 2017/08/16 15:13:02 - Table output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:262) 2017/08/16 15:13:02 - Table output.0 - ... 3 more 2017/08/16 15:13:02 - Table output.0 - Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'sonar.id' for key 'index_dashboard_domains_on_domain' 2017/08/16 15:13:02 - Table output.0 - at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) 2017/08/16 15:13:02 - Table output.0 - at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) 2017/08/16 15:13:02 - Table output.0 - at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 2017/08/16 15:13:02 - Table output.0 - at java.lang.reflect.Constructor.newInstance(Constructor.java:423) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.Util.getInstance(Util.java:408) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2494) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104) 2017/08/16 15:13:02 - Table output.0 - at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998) 2017/08/16 15:13:02 - Table output.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1288) 2017/08/16 15:13:02 - Table output.0 - ... 4 more 2017/08/16 15:13:02 - Table output.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)

it says:

Error inserting/updating row 2017/08/16 15:13:02 - Table output.0 - Duplicate entry 'sonar.id' for key 'index_dashboard_domains_on_domain' 2017/08/16 15:13:02 - Table output.0 - 2017/08/16 15:13:02 - Table output.0 - 2017/08/16 15:13:02 - Table output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:385)

sonar.id is username and not primary key, i was wondering why i could not transfer data ? but returning error which says duplicate keys ?

Answer 1

It tries to do exactly what it tells you: insert a key with a duplicate id.

First solution is to delete (truncate) the output table before to insert the record again.

Second solution is to use an Output table with an Error handler: Put a Dummy step and select Error handling when defining the hop/arrow. This step will receive all errors, including duplicates.

Third solution is to make an incremental load with a CRUD. The step is named Joins/Merge Row (diff). You specify the reference (existing) data and the new incoming data, as well as the key to compare. Kettle then create a new field containing 'new', 'deleted', 'identical' or 'updated' from which you can filter out only the new data.