MySQL Bugs: #61174: new errors in new releases require new ways to turn them off

Bug #61174	new errors in new releases require new ways to turn them off
Submitted:	13 May 2011 18:29	Modified:	18 Jun 2011 19:10
Reporter:	Mark Callaghan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6.2, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
5.6.2 and 5.5 check for integer overflow and raises an error. 5.1 had no such check. Replicating from 5.1 master to awesome 5.6.2 labs preview with parallel replication apply soon ends up with halted replication from SQL that assigned -1 to an unsigned int column. This gets a warning on the master so it is in the binlog without an error code. It gets an error on the slave.

There is no way to prevent the slave from raising this error as sql_mode settings do not control the behavior. New errors require new sql_mode behavior if we are to introduce new MySQL releases without too much pain.

How to repeat:
Read Item_func_plus::decimal_op
assign -1 to unsigned int column in 5.1, 5.5 and 5.6

Suggested fix:
add sql_mode option to prevent this error

I see the same warnings/errors (depending on sql_mode) on recent 5.1 also:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.59-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tu(c1 int unsigned) engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> select @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> insert into tu values(-1);
ERROR 1264 (22003): Out of range value for column 'c1' at row 1
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> insert into tu values(-1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1264
Message: Out of range value for column 'c1' at row 1
1 row in set (0.00 sec)

What am I doing wrong in the test above?

I don't know but we use 5.1.52 in production.

I can't reproduce this without a production setup and my previous setup was removed. Please close this and I will reopen it if I repro it later.

OK