1. Connect to "travel" Java DB.
2. Expand PERSON table, rightclick "Indexes" node and choose "Add Index".
3. Set any checkbox(es) and click "OK".
Result: Dialog disappears, but index isn't added and it's not clear.
That's weird, I just tried it before checking in and it worked fine. I'll try to reproduce.
Works for me on the Mac. Note that the default index name is one that already exists. I tried it without changing the
index name and it says the index already exists.
Can you please verify with the latest build?
I tried with latest build on Windows XP and Mac OS X. The result is the same.
And actually in default "travel" Java DB, for PERSON table, suggested by default name for new index differs from
I have one index for this table and it's name looks like "SQL070823014403450". I tried to create index with default name
"PERSON_idx" and without success and any error message.
One more point, which is probably the real reason, is that I checked only "PERSONID" checkbox for the index.
OK, reproduced *when I use the primary key* as the index column. This only happens with Java DB. It works fine on MySQL.
I'll see what happens if I do this directly in SQL.
If I try to execute this with straight SQL:
create index customer_idx on customer(customer_id);
and then refresh the table, I get the same behavior - the index appears to have been created successfully but doesn't
show up in the index list.
If I execute the same statement again, it succeeds. I can do the same thing with any index where I'm using the same
columns for an index that already exists.
It appears Derby is making the call that "hey, this index already exists, so why bother creating it again."
I've sent an email asking that they at least tell the user that the index with those columns already exists.
I do think it's strange, actually, that MySQL lets you create multiple indexes with the same column set. I wonder what
they do under the cover. Do they actually maintain two separate indexes? Or is the name just an alias?
But at this point this is a Derby "feature" and out of our hands, so closing the issue.
But customer don't understand what is going on. There is no any error message. Can we handle this?
Ok, perhaps we can detect that no column was added and provide a helpful error message. But I disagree this is a P2.
Reassigned to new owner.
It is more or less a user error to try create index for already indexed row. Then it is easy to check other indexes and
fix it. I will not add any extra warning for such situation.