Bug #27156 Not even if strict mode will halt on warnings
Submitted: 14 Mar 2007 23:33 Modified: 6 Nov 2010 17:37
Reporter: Stephen Gornick Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0, 5.1, 5.6.99 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: client, client, error, errors, execute, Execution, force, halt, halted, mode, mode, SQL, sql mode, SQL_MODE, stop, stopped, strict, strict mode, strict_all_tables, strict_trans_tables, traditional, warning, warnings
Triage: Triaged: D5 (Feature request)

[14 Mar 2007 23:33] Stephen Gornick
Description:
I want my script to stop when there are Warnings.

I know that I have the option of setting sql_mode='strict_all_tables' which will cause insert/update warnings to be escalated as errors.  

From the documentation for sql_mode: TRADITIONAL
 (which includes sql_mode='strict_all_tables')
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. 

Given the example below, I am probably mis-applying what "strict mode" exists for, because the problem isn't in trying to insert the value "test", but instead that there was a Warning generated in the query whose results are being inserted, however the insert succeeds regardless.

So if "strict mode" isn't the right method for forcing execution of the script to be halted when any Warning occurs, shouldn't there be some other method that will?

How to repeat:
mysql> set sql_mode='strict_all_tables';
mysql> create schema test;
mysql> create table test.x (id int);
mysql> create table test.y (id int, col1 varchar(10));
mysql> create table test.z (col1 varchar(10));
mysql> insert into test.x values (1);
mysql> insert into test.y values (1, 'a');

mysql> insert into test.z select 'test' as col1 from test.x inner join test.y on (y.id = 1) group by col1;
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

Suggested fix:
Command line setting (or variable) for MySQL client to allow halt on warning.
[14 Mar 2007 23:43] Stephen Gornick
test.sql

Attachment: test.sql (application/octet-stream, text), 410 bytes.

[14 Mar 2007 23:48] Stephen Gornick
Running test.sql as a script,
  $ mysql -uroot -p < test.sql
Warning (Code 1052): Column 'col1' in group statement is ambiguous
col1
test
123

which shows that execution didn't stop after the warning.
[6 Nov 2010 17:37] Sveta Smirnova
Thank you for the report.

Verified as described.

Also there is inconsistency when one inserts integer into varchar filed:

create table test.z (col1 varchar(10)) engine=innodb;
insert into test.z values (123);
-- success!
[9 Nov 2010 19:37] Konstantin Osipov
Sveta, there is no issue with your test since there is no truncation.
[9 Nov 2010 19:48] Sveta Smirnova
Kostja,

correct. But type conversion still exists. we should have at least a note at "type conversion rules" in our user manual about this silent type conversion.