0

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.

Tim
  • 1
  • 1

1 Answers1

0

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

Akina
  • 39,301
  • 5
  • 14
  • 25
  • The problem is that now I do not set it anywhere and still it seems like the other schema is using strict mode. DB is used by SaaS service which has the same kind of connection and practices for both schemas. Does MySQL remember the last sql_mode session setting? It might be that the newer schema has mede with newer settings, though the structure on MySQL is the same on both schemas... So if I now go to the newer schema and set the session sql_mode, the MySQL server remembers that setting on that schema/session now and in future? – Tim Feb 10 '23 at 06:31
  • @Tim *Does MySQL remember the last sql_mode session setting?* ??? Session settings acts within the session until it is closed. When you create new session then global settings are copied to session ones. You may alter them in runtime. So open session, set needed SQL Mode, then execute your queries within the same session. – Akina Feb 10 '23 at 11:46
  • I do understand that. But how could the same DB instance have two different sql_mode:s on, when I do not have it on code at all? But still, when SaaS system connects to another schema it switches to strict mode (at least I think so) and when connecting to the older schema it does not. It is behaving like there is a saved setting for each schema for that... – Tim Feb 10 '23 at 16:01