[Dolibarr-dev] No more implicit cast ! Please Activate MySQL Strict Mode

[Damien Clochard]

Hey guys !

I'm not active on this mailing-list, so first of all let me say how 
grateful I am for all the time and energy spent inside the Dolibarr 
community. Many thanks and respect to all of you.

I'm here to talk about a recurring problem that created new bugs in 
every single major versions. I'm here to talk about implicit casts and 
MySQL

In a nutshell : By default MySQL allows you to do things like :

CREATE TABLE wtf (i integer);
INSERT INTO wtf VALUES ('abcdefg');

This is a really bad behaviour because this means by default MySQL is 
making implicit casts for datatypes that have no clear conversion rules, 
in this case a varchar transformed into an integer. This behaviour is 
responsible of numerous type errors in the php code.

Unfortunatelly, I think many dolibarr developpers are using MySQL to 
develop and this leads to error like this :

2016-07-26 11:41:38 DEBUG 192.168.0.3 sql=INSERT INTO 
llx_element_contact (element_id, fk_socpeople, datecreate, statut, 
fk_c_type_contact) VALUES (2104, 3302 , '2016-07-26 11:41:38', 4, '' )
2016-07-26 11:41:38 ERR 192.168.0.3 DoliDBPgsql::query SQL Error 
message: ERROR: 22P02: invalid input syntax for integer: ""
LINE 1: ...ontact) VALUES (2104, 3302 , '2016-07-26 11:41:38', 4, '' )


(You can see that PostgreSQL refuses this kind of nonsense and returns 
an error )

Over the last 6 years, I've spotted literraly dozens of implicit casts 
like this one and every new major version brings new ones on the tables. 
This is frustrating.

So I have a simple request to you, my fellow dolibarr developpers :

**Please join the fight against the implicit casts and activate the 
MySQL Strict Mode**

It's easy ! just add this parameter to the my.conf file :

sql-mode="STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_DATE"

And in caseyou're wondering where this config comes from... just have to 
look to dolibarr's development guidelines :)

https://wiki.dolibarr.org/index.php/Language_and_development_rules#Mysql_specificities


or you can just use PostgreSQL and never worry about this anymore :)

Cheers,



PS : More about strict mode :

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict 
(english)
http://blog.remirepo.net/post/2008/04/27/MySQL-and-sql_mode (french)
https://www.youtube.com/watch?v=emgJtr9tIME (hilarious)







--
Damien Clochard