Same tables and structure gives overload error on other schema

Question

I have a simple insert to the table with a smallint(6). Now on our newest schema, the table structure gives an overload error when inserting a value over 32767 into that field.

At the same time on the same instance and with the same table structure inserted values are truncated to 32767 when getting over that max. limit.

My question is, what setting or flag might cause this behaviour? I know there are SQL modes and so on, but those are global settings. We have the same environment for both of those.

Are there some user-specific settings about this?

Mysql version 5.7.39

I have tried to find if there is user or schema based settings somewhere.

Answer 1

Disable STRICT SQL Mode.

DEMO

create table test (id serial primary key, val smallint);

insert into test (val) select 100;

set session sql_mode = '';
insert into test (val) select 100000;

show warnings;

Level	Code	Message
Warning	1264	Out of range value for column 'val' at row 1

set session sql_mode = 'STRICT_TRANS_TABLES';
insert into test (val) select 200000;

Out of range value for column 'val' at row 1

show warnings;

Level	Code	Message
Error	1264	Out of range value for column 'val' at row 1

select * from test;

id	val
1	100
2	32767

fiddle