`INSERT INTO SELECT` converte valores dependendo do cliente que executa a consulta

Question

Estou executando uma consulta muito básica que espero falhar corretamente, e funciona como esperado quando executada por meio de qualquer cliente MySQL. Mas quando executado dentro de um script PHP com mysqli, ele faz uma conversão de dados estranha.

A próxima consulta:

INSERT INTO gk_process_log (process_name, id_instance, date_start) select 'TestLog', NULL, '2020-10-19 14:29:11';");

A coluna id_instance é NOT NULLassim que a consulta deve falhar - a definição completa da tabela está na parte inferior. Como eu disse, ele falha "corretamente" quando executado em qualquer cliente MySQL.

Mas quando executo essa consulta de dentro de um script PHP:

mysqli_query($this->getConnection(), "INSERT INTO gk_process_log (process_name, id_instance, date_start) select 'TestLog', NULL, '2020-10-19 14:29:11';");

ele insere um zero dentro da coluna NOT NULL 🤯 A execução da consulta é simples como mostrado, não há ligação de parâmetro envolvida.

Alguém pode explicar por que isso acontece e como posso contornar isso?

Definição da tabela:

            CREATE TABLE gk_process_log (
              id_process_log int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
              process_name varchar(50) NOT NULL,
              id_instance int(11) NOT NULL,
              iteration_total INT NULL,
              iteration VARCHAR(255) NULL,
              iteration_step VARCHAR(255) NULL,
              is_finished TINYINT(1) NULL,
              date_start DATETIME NOT NULL,
              date_iteration VARCHAR(255) NULL,
              date_iteration_step VARCHAR(255) NULL,
              date_end DATETIME NULL DEFAULT NULL,
              state_data VARCHAR(255) NULL,
              result_data VARCHAR(255) NULL,
              UNIQUE INDEX instance (process_name, id_instance),
              INDEX (process_name),
              INDEX (id_instance),
              INDEX (is_finished)
            ) ENGINE=InnoDB CHARSET=utf8

Answer 1

O comportamento de "conversão de valor" é um recurso questionável do MySQL que é configurado pelo "Modo SQL". Do manual :

Se o modo estrito não estiver em vigor, o MySQL insere valores ajustados para valores inválidos ou ausentes e produz avisos

Portanto, no cenário em que o NULL foi convertido para zero, o modo estrito não foi habilitado e o MySQL converteu o valor inválido automaticamente para o valor padrão do campo para contornar a restrição NOT NULL.

Este cenário, entretanto, só é observado com NOT NULLrestrições quando a INSERT INTO SELECTsintaxe é usada. Quando uma INSERTsintaxe regular é usada, o erro 'A coluna não pode ser nula' está em seu lugar, independentemente do modo SQL.

Sobre o comportamento dependente do cliente, a resposta encontra-se no modo SQL . A única coisa que pode alterar a maneira como uma mesma consulta funciona de forma diferente em conexões diferentes é que uma delas mudou o modo SQL, de modo que o servidor se comporta de forma diferente, de acordo com as configurações atuais do modo SQL da conexão. Isso pode ser verificado executando select @@sql_mode;- mostra o modo SQL atual para a conexão atual - e select @@GLOBAL.sql_mode;- mostra o modo SQL global configurado no servidor, que será o modo padrão para qualquer nova conexão criada até que seja alterada.

No cenário do OP, o cliente MySQL que ele estava usando para construção e teste de consultas SQL manuais era o IntelliJ IDEA, com um conector Java MySQL embutido, e parece que a implementação do cliente estava alterando o modo SQL para suas conexões. Então, o comportamento das consultas executadas através do IntelliJ IDEA não era o mesmo observado ao executar as consultas através do mysqli do PHP. OP interpretou o comportamento do mysqli como não padrão, mas era de fato o modo SQL configurado no servidor, e o comportamento do IntelliJ IDEA era o errado aqui. Este comportamento inesperado do IntelliJ IDEA foi compartilhado com seus desenvolvedores aqui

Answer 2

De acordo com as respostas do fórum MySQL :

Observe que o Conector / J define SQL_MODE = STRICT_TRANS_TABLES se a propriedade de conexão jdbcCompliantTruncation for 'true' (que é por padrão). Esta é a única alteração no SQL_MODE que ocorre nos bastidores.

O mesmo que pode ser visto no código-fonte do Conector MySQL / J , consulte o método ConnectionImpl.java setupServerForTruncationChecks