MySQL non-nullable Guids problem

Boy does time fly!

This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and
ensure you're using the most recent build of any software if your question regards a particular product.

This thread has been locked

This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz

I've attached a small app which reproduces the problem. It appears to be caused when a subclass (single table inheritence) contains a non-nullable Guid property but its siblings do not. When a sibling class is loaded from the database, it appears to be tripped up by the empty string that LightSpeed stores in the non-nullable Guid (Char(36)) column.

I can workaround the problem in a few cases by forcing "old guids=true" on the mysql connection string but this appears to cause all sorts of other exceptions from within LightSpeed/MySQL and it feels a bit "wrong" to have to specify this level of database-specific detail in our connection strings. Any ideas what else I could try to fix this?

I'm a bit confused here. When I run your repro, under 3.11 or 2.2, I get an error during the insert, reporting that EntityProperty1 doesn't have a default value. (EventSubclass2 doesn't declare EntityProperty1, so LightSpeed doesn't include it in the INSERT.) This is expected behaviour. As far as I'm aware LightSpeed doesn't store empty strings for sibling fields and never has.

So I suspect that the empty string is a database default in your production database which didn't make it into the repro table definition. When I set the database default to '' on that column, I was able to reproduce your problem with LightSpeed 3.11 and your success with LightSpeed 2.2, as you reported.

If that is the issue, then this is a MySQL issue, not a LightSpeed issue. MySQL Connector 6.2.3 (which is what LightSpeed 3.11 was built against) seems to be magically inferring GUIDtude where MySQL Connector 5.2.5 (which is what LightSpeed 2.2 was built against) did not. You can verify this by reproducing the error using ADO.NET:

Another option here is to set EntityProperty1 to be nullable in the database. Your database script has it currently set for that column to be non nullable which triggers the issue of the empty strings being inserted.

Thanks both for the advice. I've now set all relevant GUID columns to nullable and that seems to have done the trick but the reason that those columns were non-nullable is because the LightSpeed designer set them up that way when I "update database".

So now the designer is complaining about many of my tables being out of sync and I'm worried that one day someone (probably me!) will "update database", forget to untick the commands to set all the columns to non-nullable and we'll end up back at square one.

Can the designer be changed to take account of this situation? Maybe setting guid columns to nullable whenever there are other classes in the same table which do not utilise that guid property, regardless of whether the guid property itself is nullable?

I could workaround by setting all affected Guids to nullable in the designer but this is not an accurate representation of our buisness model so I'm reluctant to do so becuase I think such a change will come back to bite us in the future.

Using "old guids=true" causes no Id to be inserted to the database so I'm not convinced that's a sensible direction for us to go in.

I can't actually see any indication that the default column value has been manually set for those non-null guid columns so the empty string must be MySQL's "default default" for guid columns (at least for our version 5.1 on Linux) - I think it should be a Guid.Empty value really but it's clearly a MySQL issue either way.

We've previously looked at this and it is a bit tricky. The problem comes more from the 'update model from database' direction. Suppose we see a nullable column in the database and a non-nullable field in a STI derived class in the model. Does the nullability of the column mean that the user now wants the field to be nullable (and we should offer to update the model), or does it merely mean that the column is unused in a sibling class (and we should therefore not offer to update the model)?

I guess the counter-argument is that we can always offer to update the model and the user can always decline, and that this will result in fewer wrong guesses on our part than the current strategy. Or that we could suppress the spurious update when going in the 'update database' direction.

I know I'm not necessarilly the best example of an "average customer" but for me, the model-first approach makes much more sence so I would prefer if LightSpeed's tools were optimised for that situation rather than the other way around.

So yes, ideally I would like to see this complication converted from "do you want to update the database?" to "do you want to update the model?" since I will likely never even see the latter!

Giving some consideration to those users that might be affected by this change though, is there any way that the designer can offer to remember the rejected updates (in either direction)? Naturally, there would need to be a way to manage / undo those exclusions too.