How does this data truncation possible?-CodePudding

I am trying to insert some datetime values and I came up with a non sense data truncation problem, and I have no idea how to solve and what is causing it. Hope someone helps!

I have the following table:

CREATE TABLE tb_cliente ( 
    id bigint unsigned NOT NULL AUTO_INCREMENT, 
    st_servidor tinyint(1) NOT NULL, 
    nm_nome varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    nm_nome_social varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    cd_matricula varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    cd_siape varchar(255) CHARACTER SET utf8mb4 
            COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    dt_nascimento timestamp NOT NULL, 
    sexo varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL, 
    nacionalidade varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    ano_chegada_no_pais varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    naturalidade varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    uf_nascimento varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    estado_civil varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    sn_conjuge_servidor tinyint(1) DEFAULT NULL, 
    nm_conjuge varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nm_pai varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nm_mae varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    raca varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    cor_dos_olhos varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    cor_dos_cabelos varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    sn_alergico tinyint(1) DEFAULT NULL, 
    grau_de_instrucao varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    dt_conclusao timestamp NULL DEFAULT NULL, 
    altura decimal(8,2) DEFAULT NULL, 
    tipo_sanguineo varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    sn_doador_de_orgaos tinyint(1) DEFAULT NULL, 
    nome_anterior varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    endereco varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nr_cep varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nr_cpf varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    telefone varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    telefone_celular varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    email_institucional varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    email_externo varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    email_comunicados varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    endereco_correspondencia varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nr_cep_correspondencia varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    created_at timestamp NULL DEFAULT NULL, 
    updated_at timestamp NULL DEFAULT NULL, 
    user_id bigint unsigned DEFAULT NULL, 
    PRIMARY KEY (id), 
    UNIQUE KEY tb_servidor_nr_cpf_unique (nr_cpf), 
    KEY tb_servidor_user_id_index (user_id), 
    CONSTRAINT tb_servidor_user_id_foreign FOREIGN KEY (user_id) 
        REFERENCES tb_users (id) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

When I try to insert timestamp using year 1948, I get this error:

SQL Error [1292] [22001]: Data truncation: Incorrect datetime value: '1948-07-19 00:00:00' for column 'dt_nascimento' at row 1

But when I try to insert timestamp using year 1999, it inserts successfully using the following query:

insert into tb_cliente (
            updated_at, created_at, st_servidor, 
            nm_nome, cd_matricula, dt_nascimento, 
            sexo, nacionalidade, uf_nascimento, 
            estado_civil, grau_de_instrucao, nr_cpf, 
            telefone, telefone_celular, email_institucional, email_externo) 
values ('2022-10-25 00:00:00', '2022-10-25 00:00:00', '1', 
        'User name 1', '000000', '1999-07-19 00:00:00','M', 
        '1', 'AC', 'CASADO','ENSINO' , '000000', '000000', '000000', 
        '[email protected]', '[email protected]' )


Mysql Server 8.0

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-syntax.html says:

A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC.

In other words, '1948-07-19 00:00:00' is more than 21 years earlier than the earliest date a TIMESTAMP can store.

You should use DATETIME if you want dates outside this range.