StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHSQLDB: weird "unique constraint or index violation" with data read from CSV
text
Body
copied!<p>I have a tool which reads a CSV file, selects from it using HSQLDB, and saves the result as another CSV file. More here: <a href="http://ondra.zizka.cz/stranky/programovani/java/apps/CsvCruncher-csv-manipulation-sql.texy" rel="nofollow noreferrer">http://ondra.zizka.cz/stranky/programovani/java/apps/CsvCruncher-csv-manipulation-sql.texy</a></p> <p>Now when I used it for some task, I have got:</p> <p><code>java -jar CsvCruncher-1.0.jar result.csv foo.csv 'SELECT * FROM indata'</code></p> <pre><code>INFO: SQL: CREATE TEXT TABLE indata ( xrelease VARCHAR(255), xtype VARCHAR(255), xartifact VARCHAR(255), xversion VARCHAR(255) ) INFO: SQL: CREATE TEXT TABLE output ( XRELEASE VARCHAR(255), XTYPE VARCHAR(255), XARTIFACT VARCHAR(255), XVERSION VARCHAR(255) ) INFO: User's SQL: INSERT INTO output (SELECT * FROM indata) INFO: Database closed Exception in thread "main" java.sql.SQLException: integrity constraint violation: unique constraint or index violation: SYS_IDX_10027 at org.hsqldb.jdbc.Util.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source) at org.jboss.qa.cvscruncher.Cruncher.crunch(Cruncher.java:187) at org.jboss.qa.cvscruncher.App.main(App.java:26) at Crunch.main(Crunch.java:9) Caused by: org.hsqldb.HsqlException: integrity constraint violation: unique constraint or index violation: SYS_IDX_10027 at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.index.IndexAVL.insert(Unknown Source) at org.hsqldb.persist.RowStoreAVLDiskData.indexRow(Unknown Source) at org.hsqldb.Table.insertSingleRow(Unknown Source) at org.hsqldb.StatementDML.insertRowSet(Unknown Source) at org.hsqldb.StatementInsert.getResult(Unknown Source) at org.hsqldb.StatementDMQL.execute(Unknown Source) at org.hsqldb.Session.executeCompiledStatement(Unknown Source) at org.hsqldb.Session.execute(Unknown Source) ... 5 more </code></pre> <p>As can be seen from the log, there are no indexes created, at least not explicitely. I have tried to find some auto-created constraint in HSQLDB's manual, didn't find.</p> <p>When I do only <code>SELECT 1 FROM indata</code>, it's fine. So I guess it's something with the data itself. For that case, here they are: <a href="http://pastebin.com/8QiY2HXx" rel="nofollow noreferrer">http://pastebin.com/8QiY2HXx</a> (<code>x</code> to prevent keyword clash).</p> <p><strong>Update:</strong></p> <p>When I dump the data read from the CSV, it's a bit weird:</p> <pre><code> ------- XRELEASE: 5.1.0-SNAPSHOT XTYPE: DEP XARTIFACT: org.apache.maven:maven-ant-tasks 5XVERSION: 2.0.9 ------- XRELEASE: 5.1.0-SNAPSHOT XTYPE: DEP XARTIFACT: org.jboss.seam.integration:jboss-seam-int-microcontainer 5XVERSION: 5.1.0.CR1 ------- XRELEASE: 5.1.0-SNAPSHOT XTYPE: DEP XARTIFACT: org.jboss.seam.integration:jboss-seam-int-jbossas 5XVERSION: 5.1.0.CR1 ------- ... </code></pre> <p>Which seems like the <code>xversion</code> column is modified somehow. The code is simply <code>System.out.println(" "+ metaData.getColumnLabel(i) + ": "+ rs.getObject(i) );</code></p> <p>Any idea what can cause this?</p> <p>Thanks, Ondra</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload