MySQL Bugs: #33261: Don't enable strict mode by default

Bug #33261	Don't enable strict mode by default
Submitted:	15 Dec 2007 10:02	Modified:	19 Apr 2008 15:43
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	*	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Hi,

I've written applications that use multi-row updates (see #1873). Strict mode doesn't really support that trick.
Could you not enable strict mode by default until at least multi-row updates have been implemented?

http://bugs.mysql.com/bug.php?id=1873

How to repeat:
create table t 
(
    pid int not null, 
    rank int not null, 
    points int not null,  
    primary key (pid)
);

INSERT t (rank, pid) VALUES (1, 1), (2, 4), (3, 9)
    ON DUPLICATE KEY UPDATE pid=VALUES(pid)

@Olaf

1) 
The cofiguration wizard bundled with installer for windows lets you choose strict or non-strict mode at install time. 

2)
sql_mode can be set by the client as a LOCAL option for the session.  You can just do that ("SET SQLMODE = ''" or "SET SQLMODE = 'no_engine_substitution'") in the beginning of a script or when an application connects.

3)
another solution is to declare NULL.  If you need NOT NULL then have a default for the column.

(written by a non-mysql persion)

1. Yes, but when installing MySQL users (probably) don't know strict mode should be disabled.
3. NULL shouldn't be used in those columns and neither should default values. 

2. 
I've thought about that, but I'd like to disable just STRICT_ALL_TABLES.
Can I do that with a single query?

For instance this works

create table t 
(
    pid int not null auto_increment, 
    rank int null, 
    points int not null default -1,  
    primary key (pid)
);

set sql_mode = 'strict_all_tables';

INSERT t (rank, pid) VALUES (1, 1), (2, 4), (3, 9);

as well as this 

create table t 
(
    pid int not null, 
    rank int not null, 
    points int not null,  
    primary key (pid)
);

set sql_mode = '';

INSERT t (rank, pid) VALUES (1, 1), (2, 4), (3, 9);

> set sql_mode = '';

Doesn't that disable everything and not just STRICT_ALL_TABLES?

yes of course ...

so you can (for instance)

1) "select @@sql_mode" after connection
2) save to a variable
3) 'strip' 'strict modes' (strict_all_tables, strict_trans_tables) from it into a new variable <`strict_stripped_sql_mode`>
4) set sqlmode = <your `strict_stripped_sql_mode` variable>
5) do the INSERTS
6) restore original sql_mode from 1st saved variable
.. if disabling strict modes is the solution alone to what you want to do.

Anyway:

If you do a non-complete INSERT (one with only a subset of the columns available) the server does not have a monkey-man's chance to decide what to write to the columns that are not specified in the INSERT unless
* NULL is allowed for those columns
* or columns have a default.

But I also think you have a point/wish/request that I do not fully understand!  I only read the link to your first post (that I do not undestand) after posting .. and that was a mistake by me!

I am not sure that disabling STRICT mode(s) will let you do what you want to do!  But it will allow you to do 'incomplete INSERTs' - just as having default for the columns will.

> so you can (for instance): 1 2 3 4 

Looks like a lot of string manipulation for just disabling a bit. I'm not happy with that. It could easily introduce a bug.

> .. if disabling strict modes is the solution alone to what you want to do.

I do.

> But I also think you have a point/wish/request that I do not fully understand!  

I'm basically doing updates, not inserts. However, since multi-row updates aren't supported (see the linked feature request), I'm (ab)using the insert ... on duplicate key update statement for that.
This has two disadvantages:
1. It does an insert when the key doesn't exist, which is not what I want.
2. It doesn't work with strict mode.

Yes of course that is a litle coding!  However if you compare for instance 'mysqldump' you will see that that it what it does with 'sql_mode', 'foreign_key_checks' etc.

If you are in control of the server where your application runs you can set the global sql_mode in configuration as you like.

But if you want to be sure that it will run on every server (no matter what is the global sql_mode of that server) you will basically need to do something like that.  Setting the mode = '' is most simple and will only affect that session and will often be OK if the application is coded carefully (depending on what user input is possisble of course)!

Olaf,

this is not a bug. You can configure SQL MODE at you my.cnf (or my.ini on Windows).

Please read:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Many thanks for choosing MySQL.

> this is not a bug. 

I know. It's a 'feature' request. ;)

> You can configure SQL MODE at you my.cnf (or my.ini on Windows).

I know. But I'd like to see the default changed.

We are not able to change it, without loosing compatibility.
That's why we won't fix it.

Compatibility with what?

Compatibility with previous releases.

How would you like defaults that your application relies on changing suddenly?

> Compatibility with previous releases.

But only for new installations.
It's just the default for the installer anyway, as if you don't have a mode line in your my.cnf, strict mode isn't enabled.
And doesn't disabling strict mode relax rules instead of tightening them?
What kind of apps would break due to this?

> How would you like defaults that your application relies on changing suddenly?

If my app relies on certain settings, I'd set those settings explicitly instead of using the defaults.
That's a good question though. ;)
My app relies on strict mode being disabled. This works fine on old Windows installs and on most if not all Linux installs. But you decided to change the default from disabled to enabled and you broke my app in the process. ;)
That's why I'm asking you to disable it by default.

> Could you not enable strict mode by default until at least multi-row updates have been
implemented?

That's from your first statement here.

> That's why I'm asking you to disable it by default.

That's from your last statement.

Generally, strict mode is disabled, when you install the packages or the source that we support.

If you install a package, that your distributor provides, we can't they if they changed strict mode at their packages. In this case, please ask your distributor.

Sorry, typo ...
I wanted to write:
f you install a package, that your distributor provides, we can't say if they changed
strict mode at their packages. In this case, please ask your distributor.

> That's from your first statement here.

> That's from your last statement.

Yes, and? ;)
The first asks for not enabling it, the second asks for disabling it. Isn't that equivalent?

> Generally, strict mode is disabled, when you install the packages or
> the source that we support.

That's good to hear.
However, unfortunately, your Windows installer enables it by default.
And to be clear, I'm asking for it to be disabled.

I apologize for the misunderstanding then. In the (non-canonical) version of English that I use, with the first statement you're asking us to *enable* the flag.

Anyway - It should be disabled by default, except on Windows, where you get to choose during installation. 

If you find the situation to be different from what I just described, please add the details to this bug report.

Sorry, this was my misstake, because I am more familiar with Unix.

You are right, on Windows we support it enabled and on Unix disabled.

But it doesn't matter, we can't change this anyway without loosing compatibility.

When the user make his applications, that they always trust strict mode, they will fail when the mode is disabled and when the user make his applications, that they always trust that strict mode is disabled, they will fail when we enable it.

Sorry for causing the confusion.

> Anyway - It should be disabled by default, except on Windows, where you get to choose during installation. 

Choice is fine, but I'd like the default choice to be disabled.
There are probably tons of apps out there that don't support strict mode.

Having one default on platform A and another default on platform B doesn't make sense either in my opinion.

> When the user make his applications, that they always trust strict
mode, they will fail when the mode is disabled and when the user make
his applications, that they always trust that strict mode is disabled,
they will fail when we enable it.

Do you have an example of a proper use case in which this is true?
If an application depends on strict mode, don't you think it should enable it (explicitly), given that it's disabled on the majority of platforms?

Susanne?

Susanne?

@Olaf ..

I think when status is 'won't fix' or 'closed' developers are not notified.
You will need to change status to 'open' I think!

... but you are wasting your time, I believe! :-)

> I think when status is 'won't fix' or 'closed' developers are not notified.

:(

> You will need to change status to 'open' I think!

I can't, I can only set status to closed or won't fix in this case.

> ... but you are wasting your time, I believe! :-)

I'm afraid so. I'd still like to know why defaults are not consistant across platforms.
Strict mode is nice, but some queries just can't work when it's enabled (at the moment).