failed INSERT from inside transaction returns illegal RID

[rdelangh]

OrientDB Version: 2.2.18

Java Version: N/A

OS: Ubuntu 16

Expected behavior

• "myClass" has a unique index on some key values
• when I try to INSERT a record whose key values do already exist in another record, I obviously get an error thrown back:

INSERT INTO myClass (keyProperties, ...) VALUES (duplicateValues) RETURN @RID
Error: com.orientechnologies.orient.core.storage.ORecordDuplicatedException: Cannot index record blabla  in index 'blabla' previously assigned to the record #360:0

• but when I do the same inside an SQL script, the behaviour is strange:

script sql
begin;
let newRid = INSERT INTO myClass (keyProperties, ...) VALUES (duplicateValues) RETURN @RID;
end;
Server side script executed in 0.002000 sec(s). Value returned is: #362:-2

So a value gets returned unexpectedly : in that script, I wanted to have it kick out with a similar "ORecordDuplicatedException" error, or at least not return any value for a new @Rid so that any next steps in that script would NOT be executed.

If I put extra steps after that INSERT which are using the new @Rid , surrounded with a test if the INSERT succeeded, then these extra steps are failing:

script sql
begin;
let newRid = INSERT INTO myClass (keyProperties, ...) VALUES (duplicateValues) RETURN @RID;
if ($newRid.size() > 0) {
   UPDATE $otherRID ADD links $newRid ;
}
end;
"java.lang.IllegalArgumentException: Cluster segment #-2 does not exist in database 'mobile'

They DO get executed now, because the "if ($newRid.size() > 0)" evaluates to TRUE, apparently. When the $newRid is used, that the "-2" in $newRid seems to cause that error. Irrelevant, because the "if" should never be reached.

[luigidellaquila]

Hi @rdelangh

Did you try to add a COMMIT to the script?

Thanks

Luigi

[rdelangh]

@luigidellaquila
hi Luigi,
yes indeed, the last instruction in the script is an "commit".

[rdelangh]

the complete script (sent via REST API):

begin
let rid_partial = INSERT INTO class1 (propertiesList, ...) VALUES (valuesList, ...) RETURN @RID  ;
let aggrSet = (SELECT @rid FROM (SELECT expand(rid) FROM index:class2_index WHERE key = [keyValuesList, ...]) WHERE somecriteria) ;
if($aggrSet.size() > 0) {
    UPDATE $aggrSet SET   someProps=newValues ;
   UPDATE $aggrSet ADD PARTIALS = $rid_partial ; 
};
if($aggrSet.size() < 1) {
    let aggrRid = INSERT INTO class2 (listOfProperties, ...) VALUES (listOfValues, ...) RETURN @RID;
   UPDATE $aggrRid ADD PARTIALS = $rid_partial; 
}
commit;

The reply:

Response-Code: 500
Response: {
  "errors": [
    {
      "code": 500,
      "reason": 500,
      "content": "java.lang.IllegalArgumentException: Cluster segment #-2 does not exist in database 'mobile'"
    }
  ]
}

[luigidellaquila]

Hi @rdelangh

I think the problem is this one:

let aggrSet = (SELECT @rid FROM (SELECT expand(rid) FROM index:class2_index WHERE key = [keyValuesList, ...]) WHERE somecriteria) ;

Technically speaking you are returning a projection, this is why you are having a cluster ID like #-2

You can try to change it as follows:

let aggrSet = (SELECT FROM (SELECT expand(rid) FROM index:class2_index WHERE key = [keyValuesList, ...]) WHERE somecriteria) ;

It should work fine

Thanks

Luigi

[rdelangh]

hi Luigi,

I do not agree, please see my initial post where the error occurs already at the very first INSERT which is failing as expected because the INSERTed record exists already in the class and is blocked by a unique index, but it returns a @Rid equal to #358:-2 instead of aborting with an error :

orientdb {db=mobile}> script sql                                                                                     [Started multi-line command. Type just 'end' to finish and execute]
orientdb {db=mobile}> begin;let rid_partial = INSERT INTO myClass (listOfProperties, ...) VALUES (listOfValues, ...) RETURN @RID ;
orientdb {db=mobile}> end

Server side script executed in 0.006000 sec(s). Value returned is: #358:-2
orientdb {db=mobile}>

[rdelangh]

@luigidellaquila
Luigi, did you find anything yet? Do you have enough info from me to analyze?
thx in advance!

[rdelangh]

referring to #7198 , there is no possibility to catch a failing INSERT inside a script. The script is supposed to fail with an error if trying to insert a record for which an entry exists already in the UNIQUE index.

When I try to perform exactly the same INSERT as used above in my SQL script, but now without a script, then we see the expected failure because that same record exists already in the class:

orientdb {db=mobile}> INSERT INTO myClass (listOfProperties) VALUES (listOfValues) RETURN @RID

Error: com.orientechnologies.orient.core.storage.ORecordDuplicatedException: Cannot index record myClass{prop1:value1,prop2:value2,...,propN:valueN}: found duplicated key 'OCompositeKey{keys=[valueA, valueB, valueC]}' in index 'idx_myClass_0' previously assigned to the record #360:0
        DB name="mobile"
        DB name="mobile" INDEX=idx_myClass_0 RID=#360:0

[luigidellaquila]

Hi @rdelangh

mmm... got it, it must be a problem with the script itself. I'm checking it

Thanks

Luigi

[luigidellaquila]

Hi @rdelangh

I just tried it, the problem here is that the return is calculated before the commit operation, that happens only when you close the db.

If you add a commit, everything works as expected.

script sql;
begin;
let $a = insert into ...;
commit;
end;

Thanks

Luigi

[rdelangh]

@luigidellaquila
indeed, when I place a 'commit' right behind the INSERT in the script, then the error from the insert-attempt (duplicate record) happens and no @Rid seems to be returned.

However, the whole goal of this transaction-concept is just to avoid having to do a COMMIT after the INSERT, because I want extra actions to happen after that INSERT. If these extra actions fail, then the INSERT also should not have been commited. Otherwise this is not one single transaction that fails or succeeds, but two separate transactions which might fail or succeed independently.

[luigidellaquila]

Hi @rdelangh

The index constraints (together with other validation) are checked at commit time, so there is no way for the INSERT to fail before it.
If you have a multi-statement script, you just have to add a COMMIT at the end, the script will just fail as a whole

Thanks

Luigi

[rdelangh]

hi @luigidellaquila
the failure occured with a commit as last statement, pls see my initial post.

The script is like this:

begin;
let rid_partial = INSERT INTO myClass1 (class2properties, ...) VALUES (class1values, ...) RETURN @RID ;  
let aggrSet = (SELECT @rid FROM ( SELECT expand(rid) FROM index:idx_myClass2 WHERE key = [class2keyValues] ) WHERE criteria); 
if($aggrSet.size() > 0) {;
UPDATE $aggrSet ADD partials = $rid_partial;
} ; 
if($aggrSet.size() < 1) {    
let aggrRid = INSERT INTO myClass2 (class2properties, ..., PARTIALS) VALUES (class2values, ..., []) RETURN @RID;  
UPDATE $aggrRid ADD PARTIALS = $rid_partial ;
} ;
commit;

The very first INSERT seems to return a RID in all cases. The next "if($aggrSet.size() > 0)" evaluates to TRUE, hence that UPDATE is launched using the RID (with a negative cluster-nr "-2") from the very first INSERT. It is there that the error occurs:

Error: com.orientechnologies.orient.core.exception.OCommandExecutionException: Error on execution of command: sql.begin; let rid_partial  ...
}; commit;
        DB name="mobile"
        DB name="mobile"

Error: java.lang.IllegalArgumentException: Cluster segment #-2 does not exist in database 'mobile'

[luigidellaquila]

Hi @rdelangh

I still couldn't figure out how to isolate the problem. I tried with this script

create class uni
create property uni.name String
create index uni.name on uni (name) unique

create class uni2

insert into uni set name = 'a'

begin;
let $a = insert into uni set name = 'a' return @RID;
insert into uni2 set uni = $a;
select from uni2
commit;

but it works fine, as expected... am I missing something?
If you have a test case or a script to reproduce the dataset it would help a lot

Thanks

Luigi

[rdelangh]

@luigidellaquila
the difference might be in the use of the LINKSET property "PARTIALS" in my case.
In "myClass2", that property is a LINKSET. If this property gets ADDed with the invalid RID from the very first (failing) INSERT, then the error occurs.
So the point seems that this UPDATE where the ADD happens, should not occur, but it does (trying to ADD the invalid RID, hence the error).

[luigidellaquila]

Ok, I tried with the linkset, the UPDATE ADD and the IF, just to make sure none of them is the reason for the problem:

create class uni
create property uni.name String
create index uni.name on uni (name) unique

create class uni2
create property uni2.ls linkset

insert into uni set name = 'a'
insert into uni set ls = []

begin;
let $a = insert into uni  (name) values ('a') return @RID;
if(1 = 1){
  update uni2 add ls = $a;
}
commit;

Still the same result, the script fails as espected...

Thanks

Luigi

[rdelangh]

I found the error:

This one works ok:

script sql
begin;
let $rid_partial = INSERT INTO myClass1 (properties, ...) VALUES (valuesList, ...) RETURN @RID ; 
let aggrSet = (SELECT set(@rid) AS x FROM ( SELECT expand(rid) FROM index:idx_...); 
if($aggrSet.size() > 0) {;
UPDATE $aggrSet.x  SET SESSION_START = min(SESSION_START.asLong(), 1487922005000).asDatetime(), SESSION_STOP = min(SESSION_STOP.asLong(),1487922905000).asDatetime();
UPDATE $aggrSet.x ADD PARTIALS = $rid_partial;
} ;
commit;
end

Server side script executed in 0.009000 sec(s). Value returned is: 1

The error seems that the SELECT is returning a set of RIDs, but I did not return it as a set.
Doing so, now does not give an error because the entire transaction fails (as expected) if the first INSERT fails.

:-)

[luigidellaquila]

Good :)

so the collision was on the UPDATE partials, not on the INSERT, did I get it right?

Thanks

Luigi

[rdelangh]

The very first INSERT fails, as expected. No prob so far, that is as expected.

The SELECT from myclass2 is returning a record. No prob so far, that is as expected.
The "if($aggrSet.size() > 0)" which follows is evaluating as TRUE, as expected.

But then the next "UPDATE $aggrSet" does get executed anyhow despite that the very initial INSERT was failing. And "$aggrSet" is not a single RID, but it is a set of RIDs. Hence the error about the negative (-2) cluster.

Difficult to understand that the script is executing that "UPDATE $aggrSet" in all cases...