`INSERT INTO SELECT` converts values depending on the client which runs the query

Question

I am running a very basical query I expect to correctly fail, and it does as expected when ran through any MySQL client. But when run inside a PHP script with mysqli, it does a strange data conversion.

Query is next:

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

Column id_instance is NOT NULL so query should fail - full table definition is at bottom. As I said, it "properly" fails when ran through any MySQL client.

But when I run that query from inside a PHP script:

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

it does insert a zero inside the NOT NULL column Query execution is plain as shown, there's no parameter binding involved.

Please anyone can explain why this happens and how can I workaround it?

Table definition:

            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

The "value conversion" behavior is a questionable MySQL feature which is configured by "SQL Mode". From the manual:

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings

So, on the scenario where the NULL was converted to zero, Strict Mode wasn't enabled, and so MySQL converted the invalid value automatically to the field default value to workaround the NOT NULL constraint.

This scenario, however, is only observed with NOT NULL constraints when INSERT INTO SELECT syntax is used. When a regular INSERT syntax is used, the 'Column cannot be null' error is on its place regardless of the SQL mode.

About the client dependant behavior, the answer is found on the SQL mode. The only thing which can alter the way a same query works different on different connections is that one of them have changed the SQL mode, so the server behaves differently, according to the connection's current SQL mode settings. That can be checked by running select @@sql_mode; - shows the current SQL mode for the current connection - and select @@GLOBAL.sql_mode; - shows the global SQL mode set up at the server, which will be the default mode for any new connection created until it's changed.

On the OP's scenario, the MySQL client he was using for manual SQL queries building and testing was IntelliJ IDEA, with a Java MySQL connector built in, and it seems that the client's implementation was changing the SQL mode for its connections. Then, the behavior of the queries ran through IntelliJ IDEA was not the same as the one observed when running the queries through PHP's mysqli. OP interpreted the mysqli behavior as being nonstandard, but it was indeed the SQL mode set up at the server, and the IntelliJ IDEA's behavior was the wrong one here. This unexpected IntelliJ IDEA behavior has been shared with its developers here

Answer 2

According to MySQL forum answers:

Note that Connector/J sets SQL_MODE=STRICT_TRANS_TABLES if the connection property jdbcCompliantTruncation is 'true' (which is by default). This is the only change to SQL_MODE that happens under the hood.

The same one can see in MySQL Connector/J source code, see ConnectionImpl.java setupServerForTruncationChecks method