daymansiege daymansiege - 6 months ago 32
Linux Question

Error Code: 1264. Out of range value for column 'XXX' at row 1

My stored procedure has IN parameter for page number (

IN page INT
)
And everything works fine for any value
<= 2147483647
.
And if value is
> 2147483647
I get error
1264
.
All this happens on
5.6.26 Community Server compiled for Linux (x86_64)
and the same for
5.6.24 Community Server compiled for Win64 (x86_64)

And all of the above is fine and I understand the error.

However, on server
5.6.30-1+deb.sury.org~xenial+2 (Ubuntu) compiled for debian-linux-gnu (x86_64)
this is not reproduced. Input value is just cut to 2147483647 no matter what value I pass there.

What setting is responsible for this "auto max value cap"? How can I synchronize settings on different environments without re-installation to have the same behavior?

Thank you!

Answer

Check 5.1.7 Server SQL Modes.

mysql> SELECT @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE;
+--------------------------------------------+--------------------------------------------+
| @@GLOBAL.SQL_MODE                          | @@SESSION.SQL_MODE                         |
+--------------------------------------------+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> DELIMITER //

mysql> DROP PROCEDURE IF EXISTS `sp_test`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `sp_test`(IN `page` INT)
    -> BEGIN
    ->   SELECT `page`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `sp_test`(2147483647);
+------------+
| `page`     |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL `sp_test`(2147483648);
ERROR 1264 (22003): Out of range value for column 'page' at row 1

mysql> SET @@SESSION.SQL_MODE = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE;
+--------------------------------------------+------------------------+
| @@GLOBAL.SQL_MODE                          | @@SESSION.SQL_MODE     |
+--------------------------------------------+------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> CALL `sp_test`(2147483648);
+------------+
| `page`     |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'page' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)