double entries on unique keys - best way to deal with them in Silverstripe

[Nicolaas Thiemen Francken]

​

Hi,

We are inserting data into a table which has a unique index (relationA+relationB+relationC sort of thing). We are creating these rows using the standard SS method, e.g. 

$array = array(

    'RelationAID' => $x,

    'RelationBID' => $y,

    'RelationCID' => $z

);

$obj = MyObject::get()->filter($array)->first();

if( ! $obj ) {

    $obj = MyObject::create($array);

}

....

$obj->write(); 

That works fine.  However, for some reason we ended up with an entry where the columns for the relations are all 0. This means no further rows can be added to the database because, AFAIK, the way Silverstripe writes to the Database is that it firstly creates an "empty" record and then adds the details with a second write.  This stops any record from being added. 

What is the best way to "fix" this?  I am still researching it a bit more (my assumptions may be wrong), but it would be good to get some ideas .... Here are some solutions that I thought of:

a. remove unique index because it does not work well with SS? Instead use SS core code to avoid duplicates.

b. work out why the unique entry was created in the first place and avoid it happening at all times (table locking / rollback code / switch to Postgres or whatever other more sophisticated methods are out there). 

c. add a bit of code to requireDefaultRecords (or similar) to remove any empty records from time to time.

d. before creating any records, remove any "empty" ones (onBeforeWrite) that are older than a minute or so (to avoid deleting ones that are being written at the same time).

e. validate before write? 

OR .... 

I wish this problem could solve itself, but otherwise (d) sounds like the most practicable solution (albeit a big hack).  

Has anyone dealt with this problem before?

-- 

Nicolaas 

[Simon Erkelens]

I'm somewhat unaware of this, but as far as I know, the initial `write` you are talking about, is not an active write, but just a bootstrapping of the object.

[Nicolaas Thiemen Francken]

Hi Simon,

Thank you for your response.  I had a closer look at this.  It does appear that there is a real initial write.  Here is the call stack:

... starting from the DataObject::write() call: 

And here is the DataObject::writeBaseRecord method ...

Line 1313 ... 1316 above executes a query:

call to DB::prepared_query:

All comments seem to indicate that there is an actual write:

I have created a ticket:

https://github.com/silverstripe/silverstripe-framework/issues/6819

I have had plenty of issues with unique indexes over the year and I think the above explanation may explain some of those issues.

Should I jump through additional hoops when using unique indexes or should I simply avoid them?

Thank you

Nicolaas

​