`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

Is that your actual php code, or are you binding your parameters. If "binding" then [perhaps this answers the question](https://stackoverflow.com/questions/5798863/how-do-i-get-null-into-a-mysql-integer-column-with-php-mysqli) — JNevill, Oct 19 '20 at 15:50
@JNevill yes, it's actual code. No, no binding. That's why I'm freaking out. — Áxel Costas Pena, Oct 19 '20 at 15:56
I don't know if it will lead to answer, but I'm wondering 1) What the DDL for the table looks like and 2) What your SQLMode is set at on the server `SELECT @@sql_mode;`. — JNevill, Oct 19 '20 at 15:58
Earlier MySQL versions did stuff like this all the time. To address that without breaking existing apps they introduced the [`TRADITIONAL` SQL mode](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_traditional). Is it possible that your PHP script is relaxing SQL mode for current session? — Álvaro González, Oct 19 '20 at 16:03
The behavior you described is *highly* improbable. Therefore, you are supposed to provide MCVE to support your claim — Your Common Sense, Oct 19 '20 at 16:14
on a side note, why such a strange syntax? why don't you use a conventional insert syntax? — Your Common Sense, Oct 19 '20 at 16:32
Guys commenting SQL mode, it's indeed different, will play with that and comment the results — Áxel Costas Pena, Oct 19 '20 at 16:33
@YourCommonSense this is the simplest case I could extract from a much larger SQL, so the syntax is inherited from bigger version — Áxel Costas Pena, Oct 19 '20 at 16:33
@JNevill you were the first suggesting the mode so feel free to fill the answer if you want. That automatic conversion is the "normal behavior" when [strict mode](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict) is on, and indeed I was getting a different sql_mode from MySQL client and from PHP's mysqli — Áxel Costas Pena, Oct 19 '20 at 16:41
Are you setting the strict mode manually? Does mysqli change the strict mode for you? — Dharman, Oct 19 '20 at 16:42
@Dharman no I am not setting it intentionally. Feels strange for me but I suppose I would have to blame the hosting about this. Anyway I won't dig deeper since now I am going to use another query if its behavior will vary depending on MySQL parameters, don't feel comfortable with that. — Áxel Costas Pena, Oct 19 '20 at 16:46
Your findings are wrong. 1. There is no such thing as "sql_mode from a client". It belongs to a server only. 2. Moreover, no strict mode affects a NOT NULL constraint. Only IGNORE keyword does. SO you are running your query with IGNORE keyword which *is* the actual explanation — Your Common Sense, Oct 19 '20 at 17:08
@YourCommonSense Both IGNORE and `sql_mode = ""` will produce a warning instead of error when you try to insert NULL into NOT NULL column. Given that OP does not use IGNORE in the MCVE then they must either have SQL_MODE incorrectly set for the server, or they reset it for each session. — Dharman, Oct 19 '20 at 17:19
@YourCommonSense 1: what I mean is that the sql mode I see when connecting on any client, must be the default, and somewhat at the hosting changes the sql mode when I query through mysqli, since sql_mode is indeed different when I do that. I don't know what it is, since it seems absurd evey way i think about it (PHP, mysqli library, mysql itself... seems absurd to blame anyone of them for the different sql mode, but they are the facts) — Áxel Costas Pena, Oct 19 '20 at 17:19
@YourCommonSense 2. look at the manual: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict `If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings (see Section 13.7.5.40, “SHOW WARNINGS Statement”).` so it's automatically converting the null value, which I expected to make the query fail, into a zero — Áxel Costas Pena, Oct 19 '20 at 17:20
@Dharman i ran `select @@sql_mode` on IntelliJ IDEA (mysql java client), then I ran `select @@sql_mode` on php (with mysqli) — Áxel Costas Pena, Oct 19 '20 at 17:22
It could be the fault of IntelliJ then. Try another client. Also, see if `SELECT @@GLOBAL.sql_mode;` produces the same result — Dharman, Oct 19 '20 at 17:22
so the correct quote would be "For statements such as SELECT that do not change data, invalid values generate a warning in strict mode, not an error" but I would rather callit an error not a feature — Your Common Sense, Oct 19 '20 at 17:38
And so the last question to answer is why and how the strict mode is dropped. — Your Common Sense, Oct 19 '20 at 17:54
Indeed, it was IntelliJ IDEA or the java mysql driver who was changing the sql_mode for the session - strange behavior, I will talk to them about that. Now, the whole issue is explained. Editing question title and answering the question then — Áxel Costas Pena, Oct 20 '20 at 06:04
Sorry for delay, after enough tests I feel confident this is an unexpectable implementation on IntelliJ IDEA, reported [here](https://youtrack.jetbrains.com/issue/DBE-11925) — Áxel Costas Pena, Nov 02 '20 at 08:53

Áxel Costas Pena · Accepted Answer · 2020-11-02T08:54:50.473

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

Special thanks go to @ JNevill, @ YourCommonSense and @ Dharman for very useful help on investigating the issue. — Áxel Costas Pena, Oct 20 '20 at 18:29
Instersting that the same Vasily two years ago suggested a [solution](https://intellij-support.jetbrains.com/hc/en-us/community/posts/360000348064-Session-variables-not-recognized?page=1#community_comment_360000159690) which looks more reliable to me :) — Your Common Sense, Nov 20 '20 at 12:14
And thanks again for bringing it up - today I had the same issue at my job, but luckily I knew where to look! — Your Common Sense, Nov 20 '20 at 12:15

Vasilii Chernov · Answer 2 · 2020-11-07T10:52:45.927

1

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

edited Nov 07 '20 at 10:52

answered Nov 06 '20 at 13:41

Vasilii Chernov

1,169
8
17

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

2 Answers2