Hi, back in September I've investigated strange issue in H2 when performing
alter table on table with constraints. After modifying table we were not
able to insert some rows - it failed with error Unique index or primary key
violation, but it should've been OK. We examined our indexes and they were
not as intended. I think I found the reason indexes were corrupted, but It
seemed arrogant to just try to fork h2 or send a patch... trying it here
first ;-) I'am not very familiar with H2 source code, so I'd like some
feedback from the developers if possible before trying to patch anything
possibly breaking something :-) Here is my testcase for the issue with
description and proposed fixes:
CREATE TABLE "domains" (
"id" bigint NOT NULL auto_increment PRIMARY KEY
);
CREATE TABLE "users" (
"id" bigint NOT NULL auto_increment PRIMARY KEY,
"username" varchar_ignorecase(255),
"domain" bigint,
);
/* adds constraint on (domain,username) and generates unique index
domainusername_key_INDEX_xxx */
ALTER TABLE "users" ADD CONSTRAINT "domainusername_key" UNIQUE
("domain","username");
/* adds foreign key on domain - if domainusername_key didn't exist it would
create unique index on domain, but it reuses the existing index */
ALTER TABLE "users" ADD CONSTRAINT "udomain_fkey" FOREIGN KEY ("domain")
REFERENCES "domains"("id") ON DELETE RESTRICT;
/* now we drop the domainusername_key, but domainusername_key_INDEX_xxx is
used by udomain_fkey and is not dropped - this is an issue, because it's
unique index and still enforcing constraint on (domain,username) */
ALTER TABLE "users" DROP CONSTRAINT "domainusername_key";
insert into "users" ("username","domain") VALUES ('test',1);
insert into "users" ("username","domain") VALUES ('test',1); /* should
work,because we dropped domainusername_key, but fails: Unique index or
primary key violation: "domainusername_key_INDEX_6 ON
PUBLIC.""users""(""domain"", ""username"") VALUES (1, CAST('test' AS
VARCHAR_IGNORECASE), 1)"; */
/*
I believe, the problem is in AlterTableAddConstraint getIndex function
caleld from case
CommandInterface.ALTER_TABLE_ADD_CONSTRAINT_REFERENTIAL: branch
index is found calling getIndex with moreColumnOk, but if the resulting
index is UNIQUE, it's a problem, because it will be referenced by the new
constraint and will enforce key uniqueness even if the original owning
constraint is dropped
private static Index getIndex(Table t, IndexColumn[] cols, boolean
moreColumnOk) {
if (t.getIndexes() == null) {
return null;
}
for (Index idx : t.getIndexes()) {
if (canUseIndex(idx, t, cols, moreColumnOk)) {
return idx;
}
}
return null;
}
I think, either moreColumnOk should be false, or it should be applied
only to non-unique indexes.
Another similar problem is in
CommandInterface.ALTER_TABLE_ADD_CONSTRAINT_UNIQUE: branch where
getUniqueIndex is called.
It tries to reuse existing index, but it uses even index that is not
unique for given fields. As I understand it ConstraintUnique is enforced by
the underlying unique index.
There is even a comment in the class
@Override
public void checkRow(Session session, Table t, Row oldRow, Row newRow) {
// unique index check is enough
}
But in canUseUniqueIndex there is a comment:
// all columns of the index must be part of the list,
// but not all columns of the list need to be part of the index
So the canUseUniqueIndex is passed list of columns that must be unique.
I would expect that to be unique, all must be in the index key, the order
doesn't matter and there must be no other fields in the index key.
I have implemented my own and it fixes issue, we found during testing,
when incorrect index (with less columns) was being reused and din't ensure
uniqueness for the constraint.
private static boolean canUseUniqueIndexFixed(Index idx, Table table,
IndexColumn[] cols) {
if (idx.getTable() != table || !idx.getIndexType().isUnique()) {
return false;
}
Column[] indexCols = idx.getColumns();
HashSet<Column> indexColsSet = New.hashSet();
for (Column c : indexCols) {
indexColsSet.add(c);
}
HashSet<Column> set = New.hashSet();
for (IndexColumn c : cols) {
set.add(c.column);
}
return set.equals(indexColsSet);
}
*/
Thank you for any feedback, Petr
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
