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

38 views
Skip to first unread message

Nicolaas Thiemen Francken

unread,
Apr 18, 2017, 6:58:00 PM4/18/17
to silverstripe-dev


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

unread,
Apr 22, 2017, 5:52:37 AM4/22/17
to SilverStripe Core Development
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

unread,
Apr 23, 2017, 6:33:28 PM4/23/17
to silverstripe-dev
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: 

Inline images 1



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

Inline images 2

Line 1313 ... 1316 above executes a query:

Inline images 1


call to DB::prepared_query:

Inline images 2

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

Inline images 1

I have created a ticket:


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

Reply all
Reply to author
Forward
0 new messages