MySQL Bugs: #27156: Not even if strict mode will halt on warnings

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

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.

test.sql

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

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.

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!

Sveta, there is no issue with your test since there is no truncation.

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.