Data import fails

Hi,

I trying to import data from a tab-delimited text fileinto a table (IJC 5.3.1, mysql-5.0.77-4.el5_4.2) but this fails, I get the following errors (I removed some repetetive lines):

COMPOUND_VIALS_ID is mapped to current field COMPOUND_VIALS_ID
Vial ID is mapped to current field Vial ID
Row is mapped to current field Row
Column is mapped to current field Column
Rack ID is mapped to current field Rack ID
Starting to import data...
Warning: Failed to import row 1: PreparedStatementCallback; SQL [INSERT INTO COMPOUND_RACKS (Vial_ID,Rack_Row,Rack_ID,Rack_Column,COMPOUND_VIALS_ID) VALUES (?,?,?,?,?)]; Duplicate entry '0' for key 1; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 1

Warning: Failed to import row 2: PreparedStatementCallback; SQL [INSERT INTO COMPOUND_RACKS (Vial_ID,Rack_Row,Rack_ID,Rack_Column,COMPOUND_VIALS_ID) VALUES (?,?,?,?,?)]; Duplicate entry '0' for key 1; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 1
.
.
.
Warning: Failed to import row 25: PreparedStatementCallback; SQL [INSERT INTO COMPOUND_RACKS (Vial_ID,Rack_Row,Rack_ID,Rack_Column,COMPOUND_VIALS_ID) VALUES (?,?,?,?,?)]; Duplicate entry '0' for key 1; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 1

Error:

TOO MANY CONSECUTIVE ERRORS, TERMINATING IMPORT


Import completed in 0s.
0 entries successfully imported.
25 Errors.
Bad records can be found at
/mnt/disk1/evehom/Racks.txt.2._errors

Nothing gets imported. If i instead try to add one row to the table manually, and fill in some values, I get:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 1
    at sun.reflect.GeneratedConstructorAccessor129.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2371)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2289)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2274)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
Caused: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO COMPOUND_RACKS (Rack_Column,Rack_ID,Rack_Row,COMPOUND_VIALS_ID,Vial_ID) VALUES (?,?,?,?,?)]; Duplicate entry '0' for key 1; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 1
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:228)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:815)
    at com.im.df.impl.db.dao.PKAfterInsertDao.insertUsingAutoincrement(PKAfterInsertDao.java:68)
    at com.im.df.impl.db.dao.PKAfterInsertDao$AutoincrementDMLImpl.insert(PKAfterInsertDao.java:131)
    at com.im.df.impl.db.dao.DFDaoSupport.insert(DFDaoSupport.java:116)
    at com.im.df.impl.db.dao.DFDaoWrapper.insert(DFDaoWrapper.java:65)
    at com.im.df.impl.db.DBEntityDataProviderImpl.doInsertRow(DBEntityDataProviderImpl.java:357)
    at com.im.df.impl.db.DBEntityDataProviderImpl.access$5(DBEntityDataProviderImpl.java:297)
    at com.im.df.impl.db.DBEntityDataProviderImpl$3.doInTransaction(DBEntityDataProviderImpl.java:288)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
    at com.im.df.impl.db.DBEntityDataProviderImpl.insertOrFindRow(DBEntityDataProviderImpl.java:284)
    at com.im.df.impl.db.InsertionHandler.doInsertData(InsertionHandler.java:307)
    at com.im.df.impl.db.InsertionHandler.doInTransaction(InsertionHandler.java:267)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
    at com.im.df.impl.db.DBSchemaDataProviderImpl.insert(DBSchemaDataProviderImpl.java:187)
    at com.im.ijc.core.api.actions.InsertRowAction$3.phase1InRequestProcessor(InsertRowAction.java:419)
    at com.im.commons.progress.BackgroundRunner.processTheTask(BackgroundRunner.java:126)
    at com.im.commons.progress.BackgroundRunner$1.run(BackgroundRunner.java:90)
    at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:577)
[catch] at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:1030)

Upon disconnecting and reconnecting, this row has been added indeed, but gets an ID of 0 (zero) in the table, and I cannot add additional rows manually.

Kind regards,

Evert

 

How was the table created? It seems that the table's primary key column does not have the auto_increment keyword defined for it. If the table was created in IJC then that should happen automatically. Was the table created outside IJC without the auto_increment keyword?

If it was promoted from a table created outside IJC was the correct column chosen as the ID field (the auto_increment column)?

Tim

Hi Tim,

This table was created in IJC.

Evert

Hi again,

I deleted the table, recreated it (agian from within IJC), and now data import worked. I don't know what went wrong the first time, must have been a glitch.

Thanks nonetheless,

Evert

OK, greate. Let us know if it resurfaces.

Tim

Hi again,

As I replied I managed to import the data, but now I can't seem to build a relationship to another table (see attached error message). Both tables contain a common field for building the relationship.

I also noticed that the automatically generated ID field for a new table is by default just called 'ID', while I recall that before 5.3 this would contain the name of the table, e.g. like COMPOUND_BATCHES_ID, if the imported table was called Compound Batches. The newer behaviour with a non-unique ID field name seems risky to me when building relationships.

Evert

Yes, we did change the name of column for the ID field. Having the same column name in multiple tables is perfectly legal, and in no way prevents adding a foreign key constraint. The reasons for the change were:

1. to conform better with database conventions where the primary key column is usually named ID (no table name) and colunm used for foreign key constraints to other tables were named OTHERTABLENAME_ID.

2. We changed the way table and column names were validated so as to allow the full length allowed by the database. So if, for instance, we allowed creation of a table name with the full 30 characters allowed by Oracle then there were no space left to allow _ID to be appended (or course this doesn't solve all cases when this could happen).

The error you report does not seem to be related to the column names. It seems to due to incompatible data in the 2 tables. You seem to be trying to create a foreign key constraint where not all the values in the table have coresponding values in the referred column. e.g. one of the rows in the "foreign key" column might have a value of 999, but there is no such value in the "primary key" column.

Tim