STRICT_TRANS_TABLES and stricter typing

[coolov]

I have enabled STRICT_TRANS_TABLES on my local machine and get all sorts of errors from directus.

STRICT_TRANS_TABLES enforces strict typing, if for example '9' is written to an int column an error is thrown. We need to make sure that every INSERT and UPDATE respects the datatypes of the columns.

[benhaynes]

@jel-massih Do you know if this is still an issue?

[benhaynes]

@wellingguzman Not really sure what is meant by this old issue – if you don't see a reason to keep it open we can close it.

[wellingguzman]

@benhaynes this still an issue, I turned strict mode on, and I'm always getting error, (on directus_activity) trying to insert null values on columns that does not accept null or does not have any default value to fallback on.

[benhaynes]

Fair enough – we'll leave it open until we have time to fix it.

[benhaynes]

@wellingguzman is this the ticket you meant to tag in that last fix?

[wellingguzman]

@benhaynes yes, I meant this ticket, at some point some numbers are converted to string, and make it difficult to compare.

[benhaynes]

@wellingguzman Ahh, that makes sense now. Should we continue to test this one a bit more or should we close it?

[wellingguzman]

@benhaynes Oh no, this hasn't been fixed yet. I only meant this ticket to keep on eye on this one as a results of having that type of error.

[benhaynes]

Cool, thanks for the update!

[wellingguzman]

The issue here is far from Directus, PDO converts the number into string so it can store large number in php. one solution could be casting the string into numeric values, BUT the catch here is that the value is bigger than php can store it will be chopped, so that will be a worst result.

[davidbwaters]

Hi. I know the limitations of PDO make this one tricky and somewhat out of dev's control, but wanted to note how much the lack of hosting support for disabling strict mode could potentially limit exposure for those considering/learning/testing Directus.

Not being able to install Directus on shared hosts and cloud hosts like Heroku could be a hurdle for many. Virtual machines and AWS still work, but for me, and I'm sure many others, the major appeal of Directus is the ability to self-host . For clients, I would use the paid plan to avoid maintenance, but it would be nice to easily test working with the API with an install on my own server.

[wellingguzman]

Hey @davidbwaters, did you have problem installing Directus on a shared hosting? strict mode will give you some issue working with Directus, but it should be less than when this ticket was open, can you elaborate more so we can help you install it (if possible).

[davidbwaters]

Oh, awesome, I hadn't tried it yet, actually. I have it installed locally and know the installation has warnings about it, so I assumed it would cause lots of problems. I'd been looking into Docker hosting, which let's you configure my.cnf, but will test it out on a shared service, I assume I just need make my own config file with DB credentials, etc?

So if I'm building a small site with mostly text fields, files, and no giant numbers, this shouldn't cause any problems?

I'm really glad you guys are still streamlining this, thanks for the quick reply. I've been following this project for a long time and love seeing it become more and more stable.

[wellingguzman]

Has anyone experienced errors when using Directus with strict mode enabled?

It has been a long way trying to solve this issue, we have improved and reduce all the errors this mode bring, hopefully finding the rest of the bugs will allow us to finally close this ticket for good.

[benhaynes]

We have not been experiencing any issues lately with STRICT MODE. MySQL 5.7 is strict by default now – so please let us know if you experience any issues that seem to be related to this DB configuration.

Closing... almost 4 years later... 📆