MySQL insert varchar value into int auto increment column

Question

I noticed strange MySQL behaviour on projects staging server. Given:

create table test_table
(
    id   int auto_increment,
    name varchar(64) not null,
    constraint test_table_pk
        primary key (id)
);

insert into test_table (id, name)
VALUES (3, 'value 3');
insert into test_table (id, name)
VALUES (5, 'value 5');
insert into test_table (id, name)
VALUES ('value 6 id', 'value 6');

select *
from test_table;

Results:

mysql> select * from test_table;
+----+---------+
| id | name    |
+----+---------+
|  3 | value 3 |
|  5 | value 5 |
|  6 | value 6 |
+----+---------+
2 rows in set (0.01 sec)

First two insert statements works fine as expected. What's the magic behind the third one? It works and it seems incorrect type value is replaced with auto increment value. I'm trying to create docker version of the DB server. But I can't find what's config option to enable this behaviour. The third insert statement is rejected by docker DB instance with error message:

Incorrect Integer value: 'value 6 id' for column 'id'

mysql> SHOW VARIABLES LIKE 'version';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| version       | 5.7.27-30 |
+---------------+-----------+
1 row in set (0.01 sec)

UPDATE

On Staging server:

show variables like '%sql_mode%';

Returns:

IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

My docker-compose.yaml

version: "3.8"
services:
 db:
    image: percona/percona-server:5.7.27
    volumes:
      -db-data:/var/lib/mysql
    env_file:
      - ./db.env
    command:
      --sql_mode=IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    ports:
      - "3306:3306"
  flyway:
    image: flyway/flyway:7.15.0
    command: migrate
    volumes:
      - ./../../sql/migrations:/flyway/sql
      - ./flyway/conf:/flyway/conf
    depends_on:
      -db
volumes:
  db-data:

Any ideas? Thank you

The docker DB is doing the right thing. What exactly is your point? — Salman Arshad
– Salman Arshad, Commented May 17, 2023 at 15:09
I want to create docker based DB instance that behaves the same way staging instance does. It's not about right/wrong behaviour. — Marius
– Marius, Commented May 17, 2023 at 15:49
strict_trans_table is a common sense setting. It should be enabled in all environments, not the other way round. — Salman Arshad
– Salman Arshad, Commented May 17, 2023 at 16:11
Agree. It's very strange behaviour in an example I provided. — Marius
– Marius, Commented May 17, 2023 at 19:06

SelVazi · Accepted Answer · 2023-05-17 15:14:15Z

2

You have enabled STRICT_TRANS_TABLES which causes invalid data values to be rejected, this should be removed from the list:

SET sql_mode='IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Demo here

edited May 17, 2023 at 15:14

answered May 17, 2023 at 15:06

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Salman Arshad Over a year ago

That is correct but what does it do and why should it be removed?

SelVazi Over a year ago

The OP need invalid data to be inserted as well

Salman Arshad Over a year ago

Yes but what does it do?

RP.S · Accepted Answer · 2023-05-17 14:17:45Z

1

This has something to do with sql_mode. You can check the sql_mode parameters of two database instances to find out the secrets.

show variables like '%sql_mode%';

answered May 17, 2023 at 14:17

RP.S

8377 silver badges13 bronze badges

Collectives™ on Stack Overflow

MySQL insert varchar value into int auto increment column

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related