[MySQL learning] OUT of range value for column and DATA truncated for column corresponding to STRICT_ALL_TABLES

Yesterday, the insert into a select from B statement was used in the stored procedure. When the bigint field is inserted into the int field in the application, the value is truncated. The inserted value is no longer the expected value. Because exception capture is not used in the stored procedure, no errors are found at the beginning, but they are only found during data comparison. After comparing the two tables, we found that the field type is different. Next I tried to verify it (the test environment in the window ).

Create two tables

CREATE TABLE T_INT(id INT ,vname VARCHAR(20));CREATE TABLE T_BIGINT(id BIGINT ,vname VARCHAR(40));

Insert bigint to int: if the maximum value of int is exceeded, it is directly truncated to the maximum value power ()-1. A warning will be prompted if you are directly operating in the command line.

</pre><p></p><p></p><pre code_snippet_id="397037" snippet_file_name="blog_20140618_3_2598704" name="code" class="sql">INSERT INTO t_int(id,vname)VALUES(POWER(2,31),'0123456789abcdefghij');SHOW WARNINGS;1264 OUT of RANGE VALUE FOR COLUMN 'id' AT ROW 1;SELECT * FROM t_int;SELECT POWER(2,31)21474836482147483647

Test varchar (40) inserted to varchar (20): the maximum value of the field is also intercepted. A warning is thrown at the command line.

INSERT INTO t_int(id,vname)VALUES(POWER(2,31),'0123456789abcdefghij1');SHOW WARNINGS;1264 OUT of RANGE VALUE FOR COLUMN 'id' AT ROW 1;1265 DATA truncated FOR COLUMN 'vname' AT ROW 1;idvname21474836470123456789abcdefghij21474836470123456789abcdefghijINSERT INTO T_BIGINT(id,vname)VALUES(POWER(2,31),'0123456789abcdefghij1');INSERT INTO t_int SELECT * FROM t_bigint;

Because SQLyog is used and the default mode is null, a problem occurs during debugging. An error occurs in the application because SQL _mode of the client is not specified in my url.

/*[17:15:32][  31 ms]*/ SHOW VARIABLES LIKE 'lower_case_table_names';/*[17:15:32][   0 ms]*/ SET NAMES 'utf8';/*[17:15:32][   0 ms]*/ SET sql_mode='';/*[17:15:32][  16 ms]*/ SHOW DATABASES;

Directly connect to MySQL in cmd, because I have configured SQL _mode in the configuration file.

mysql> INSERT INTO t_int(id,vname)VALUES(POWER(2,31),'0123456789abcdefghij');ERROR 1264 (22003): Out of range value for column 'id' at row 1mysql>mysql> show variables like '%mode%';+--------------------------+----------------------------------------------------------------+| Variable_name            | Value            |+--------------------------+----------------------------------------------------------------+| innodb_autoinc_lock_mode | 1            || slave_exec_mode          | STRICT            || sql_mode                 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+--------------------------+----------------------------------------------------------------+3 rows in set (0.00 sec)mysql>

However, if an insert statement is encapsulated into a stored procedure, no error is reported, but only a warning is reported.

mysql> call PRO_INSERT();Query OK, 1 row affected, 3 warnings (0.01 sec)mysql> select * from t_int;+------------+----------------------+| id         | vname                |+------------+----------------------+| 2147483647 | 0123456789abcdefghij || 2147483647 | 0123456789abcdefghij |+------------+----------------------+2 rows in set (0.00 sec)mysql> set sql_mode='STRICT_ALL_TABLES';Query OK, 0 rows affected (0.00 sec)mysql> call PRO_INSERT();Query OK, 1 row affected, 3 warnings (0.03 sec)mysql> select * from t_int;+------------+----------------------+| id         | vname                |+------------+----------------------+| 2147483647 | 0123456789abcdefghij || 2147483647 | 0123456789abcdefghij || 2147483647 | 0123456789abcdefghij || 2147483647 | 0123456789abcdefghij |+------------+----------------------+4 rows in set (0.00 sec)mysql> show variables like '%mode%';+--------------------------+-------------------+| Variable_name            | Value             |+--------------------------+-------------------+| innodb_autoinc_lock_mode | 1                 || slave_exec_mode          | STRICT            || sql_mode                 | STRICT_ALL_TABLES |+--------------------------+-------------------+3 rows in set (0.00 sec)mysql>

The cause of the problem is SQL _mode. There is also no exception handling mechanism. Next post SQL _mode first.

Solution, directly throwing an error. SET SQL _mode = 'strict _ TRANS_TABLES, NO_ENGINE_SUBSTITUTION 'in the stored procedure; (set SQL _mode In the Stored Procedure)

mysql> call PRO_INSERT();Query OK, 1 row affected, 3 warnings (0.01 sec)mysql> show warnings;+---------+------+---------------------------------------------+| Level   | Code | Message                                     |+---------+------+---------------------------------------------+| Warning | 1264 | Out of range value for column 'id' at row 1 || Warning | 1264 | Out of range value for column 'id' at row 2 || Warning | 1265 | Data truncated for column 'vname' at row 2  |+---------+------+---------------------------------------------+3 rows in set (0.00 sec)mysql> call PRO_INSERT();ERROR 1264 (22003): Out of range value for column 'id' at row 1mysql>

Stored Procedure

DELIMITER $$USE `test`$$DROP PROCEDURE IF EXISTS `PRO_INSERT`$$CREATE DEFINER=`root`@`%` PROCEDURE `PRO_INSERT`()BEGINSET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';INSERT INTO t_int(id,vname)VALUES(POWER(2,31),'0123456789abcdefghij');INSERT INTO t_int(id,vname)VALUES(POWER(2,31),'0123456789abcdefghij1');    END$$DELIMITER ;