Open Stack

On 2014-01-23 11:50, Jay Pipes wrote:
> On Thu, 2014-01-23 at 11:29 -0600, Ben Nemec wrote:
>> A while back a change (https://review.openstack.org/#/c/47820/) was 
>> made
>> to allow enabling mysql traditional mode, which tightens up mysql's
>> input checking to disallow things like silent truncation of strings 
>> that
>> exceed the column's allowed length and invalid dates (as I understand
>> it).
>> 
>> IMHO, some compelling arguments were made that we should always be 
>> using
>> traditional mode and as such we started logging a warning if it was 
>> not
>> enabled.  It has recently come to my attention
>> (https://review.openstack.org/#/c/68474/) that not everyone agrees, so 
>> I
>> wanted to bring it to the list to get as wide an audience for the
>> discussion as possible and hopefully come to a consensus so we don't 
>> end
>> up having this discussion every few months.
>> 
>> I remain of the opinion that traditional mode is a good thing and we
>> _should_ be enabling it.  I would call silent truncation and bogus 
>> date
>> values bugs that should be fixed, but maybe there are other 
>> implications
>> of this mode that I'm not aware of.
> 
> Why traditional? Why not STRICT_ALL_TABLES?

If I'm reading the documentation correctly, traditional is actually more 
strict than the STRICT_* modes.  According to 
http://www.mysqlfaqs.net/mysql-faqs/Client-Server-Commands/What-is-sql-mode-in-MySQL-and-how-can-we-set-it , "The TRADITIONAL mode, enables strict mode plus other restrictions on date checking and division by zero." which jives with what I see in the official documentation.  Note that "strict mode" in this case refers to the combination of STRICT_TRANS_TABLES and STRICT_ALL_TABLES.

> 
>> It was also pointed out that the warning is logged even if the user
>> forces traditional mode through my.cnf.  While this certainly solves 
>> the
>> underlying problem, it doesn't change the fact that the application 
>> was
>> trying to do something bad.  We tried to make it clear in the log
>> message that this is a developer problem and the user needs to pester
>> the developer to enable the mode, but maybe there's more discussion 
>> that
>> needs to go on there as well.
> 
> What I was trying to point out with that is that if I see a warning in 
> a
> log file about not enabling traditional mode, and yet I've set my 
> my.cnf
> server sql_mode to STRICT_TRANS_TABLES, TRADITIONAL, or
> STRICT_ALL_TABLES, then I shouldn't see a warning in the code...
> 
> It's easy enough to check... SHOW [GLOBAL] VARIABLES LIKE 'sql_mode'...

Yeah, we could do that.  I guess part of the problem is that if an 
application works in traditional mode the developers should be enabling 
it themselves, and if it doesn't then setting it manually in my.cnf 
isn't going to work anyway.  But I suppose if someone knows to set it in 
my.cnf then they hopefully understand the implications and can figure 
out for themselves whether it works.  I could get on board with 
suppressing the warning when it's set in my.cnf.

-Ben