Problem with long unique index
Posted by: jochen brueger
Date: August 08, 2013 05:56PM

In my Java application, I connect to my database like this:

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/db?useServerPrepStmts=false&rewriteBatchedStatements=true",
"myuser","mypass");

create a table like this:

java.sql.Statement createSt = conn.createStatement();
createSt.executeUpdate(String.format("CREATE TABLE IF NOT EXISTS %s.%s ( "
+ "id INT NOT NULL AUTO_INCREMENT,"
+ "res1 VARCHAR(255) NULL ,"
+ "res2 VARCHAR(255) NULL ,"
+ "similarity DOUBLE NULL ,"
+ "PRIMARY KEY (id) ,"
+ "UNIQUE INDEX bothres (res1, res2) ,"
+ "INDEX (similarity) )", dbName, tableName));
Create a prepared statement:

PreparedStatement pst = conn.prepareStatement(String.format("INSERT INTO %s.%s values (default, ?, ?, ?)", dbName, tableName));

And then insert data in batches like this:

public void insert(String uri1, String uri2, double sim){
pst.setString(1, uri1);
pst.setString(2, uri2);
pst.setDouble(3, simil);
pst.addBatch();
if(++batchCount%maxBatch == 0){
pst.executeBatch();
}
}

The uri1 and uri2 Strings are simply urls like http://xmlns.com/foaf/0.1/Document or http://xmlns.com/foaf/0.1/Person
or any other URI.
Even though I am 100% sure that the insert method is never called with the same pair of URIs, on executeBatch() an
BatchUpdateException - Duplicate entry 'http://reference.data.gov.uk/def/intervals/Minute-http://bio2rdf'; for key 'bothres'
is thrown.
That is weird to me is the fact that the second uri is always truncated, as if the database would not store it completely. Is the length of such keys limited, and if so, how can I increase the length?

Int the example above, the error is likely caused because I am calling insert several times with
uri1 = "http://reference.data.gov.uk/def/intervals/Minute";
and uri2 = "http://bio2rdf/foo/uri1";, uri2 = "http://bio2rdf/foo/uri2";, etc. but never with the exact same uris.

I found that InnoDB index key prefixes have limited length, but I am not sure if this is the issue here. I set
innodb_large_prefix=true
innodb_file_format=Barracuda
innodb_file_per_table=1
in my.cnf, but this doesnt help.

If anyone can give me a hint how to resolve this, I would be very thankful!

Options: ReplyQuote


Subject
Written By
Posted
Problem with long unique index
August 08, 2013 05:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.