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

[15 Dec 2007 10:02] Olaf van der Spek
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)
[15 Dec 2007 12:16] Peter Laursen
@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)
[15 Dec 2007 12:22] Olaf van der Spek
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?
[15 Dec 2007 12:34] Peter Laursen
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);
[15 Dec 2007 12:36] Olaf van der Spek
> set sql_mode = '';

Doesn't that disable everything and not just STRICT_ALL_TABLES?
[15 Dec 2007 21:27] Peter Laursen
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.
[15 Dec 2007 21:39] Olaf van der Spek
> 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.
[15 Dec 2007 23:15] Peter Laursen
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)!
[17 Dec 2007 15:05] Susanne Ebrecht
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.
[17 Dec 2007 15:08] Olaf van der Spek
> 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.
[17 Dec 2007 15:32] Susanne Ebrecht
We are not able to change it, without loosing compatibility.
That's why we won't fix it.
[17 Dec 2007 16:11] Olaf van der Spek
Compatibility with what?
[18 Dec 2007 11:35] Daniel Fischer
Compatibility with previous releases.

How would you like defaults that your application relies on changing suddenly?
[18 Dec 2007 11:44] Olaf van der Spek
> 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.
[18 Dec 2007 12:59] Susanne Ebrecht
> 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.
[18 Dec 2007 13:01] Susanne Ebrecht
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.
[18 Dec 2007 13:12] Olaf van der Spek
> 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.
[18 Dec 2007 13:23] Daniel Fischer
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.
[18 Dec 2007 13:27] Susanne Ebrecht
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.
[18 Dec 2007 13:40] Olaf van der Spek
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?
[14 Feb 2008 10:50] Olaf van der Spek
Susanne?
[19 Apr 2008 15:20] Olaf van der Spek
Susanne?
[19 Apr 2008 15:39] Peter Laursen
@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! :-)
[19 Apr 2008 15:43] Olaf van der Spek
> 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).