NULL value is not accepted for changes to a column where a DEFAULT value defined

[ghost]

If a null value is passed as a column value in an INSERT or UPDATE statement when a function call to query is made and that column is defined to have a DEFAULT value, then that default value should be applied to the column on the the row insert/update operation. This is the standard behavior for MySQL.

But, the mysql module returns the response "ER_BAD_NULL_ERROR: Column '[column_name]' cannot be null". The NPM doc for mysql states that "undefined / null are converted to NULL" under the Escaping Query Values section (https://www.npmjs.com/package/mysql#escaping-query-values). If JavaScript null is passed and converted to MySQL NULL, then the mysql module should process it like a regular SQL query that has NULL value passed, in which the column's DEFAULT value is applied.

This issue is related to the feature request #559. However, I am not suggesting js undefined should act like MySQL NULL in regards to applying the column default value. I'm saying there is a bug on SQL statement execution: the mysql module does not properly behave when MySQL NULL is passed after (correctly being converted from) a js null value.

[dougwilson]

Please provide a pull request to patch the issue, thank you!

[ghost]

@dougwilson Ok, I will do my best to submit a pull request when I get some time a bit later to isolate the issue and propose a solution. Thanks

[dougwilson]

Awesome! If you're not able to, we can always look into this issue, but we need a lot more information in order to replicate the issue, like the version of Node.js, the version of this module, the version of your MySQL server, any system variables that change the strictness in your SQL server, all necessary DDL to setup the tables to replicate, and finally the JavaScript code to run in order to reproduce the issue. Let me know if you want to go down this path instead of submitting a PR :)

[bbito]

@garudacrafts

If a null value is passed as a column value in an INSERT or UPDATE statement when a function call to query is made and that column is defined to have a DEFAULT value, then that default value should be applied to the column on the the row insert/update operation. This is the standard behavior for MySQL.

I was curious about this, and just tried this out with MySQL 5.7 Command Line Client on Windows with:

CREATE TABLE `new_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_default_nn` varchar(45) NOT NULL DEFAULT 'This is default value - NN',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Using the Command Line Client I cannot insert NULL into that NOT NULL field to get the DEFAULT.

mysql> INSERT INTO new_table VALUES (0, NULL);
ERROR 1048 (23000): Column 'test_default_nn' cannot be null

I can only get default if I do not insert any value into that field, e.g.:

mysql> INSERT INTO new_table (id) VALUES (0);
Query OK, 1 row affected (0.01 sec)

EDIT: I can also get default by inserting DEFAULT, e.g.:

mysql> INSERT INTO new_table VALUES (0, DEFAULT);
Query OK, 1 row affected (0.01 sec)

So this is to say that I don't think that your assertion is correct that:

...like a regular SQL query that has NULL value passed, in which the column's DEFAULT value is applied.

My experience is that MySQL only applies the default value to a column if no value is specified or the keyword DEFAULT is specified.

[ghost]

@bbito Indeed, I replicated your results! I thought I was taking crazy pills for a sec, then I realized that I was able to successfully run my INSERT statements with NULLs when multiple rows are added in a single statement execution, like so:

INSERT INTO new_table VALUES (NULL, NULL), (NULL, NULL), (NULL, NULL);

But, this resulted in empty values for the 'test_default_nn' column, NOT the DEFAULT, as I was expecting. I was mistaken. I had thought the inserts were applying the default!

So, it seems this issue is better off as a feature request as previously filed in #559 , and not a bug. I will close it.

Thanks for your quick response!

[bbito]

Yeah, I came across an article about this MySQL "Quirk":
https://www.noelherrick.com/blog/mysql-quirk-not-null-defaults
That article states that the error will be thrown with multiple INSERTs as well if the sql_mode is set to strict_all_tables

[bbito]

@garudacrafts - Interestingly, I can't run your SQL on my MySQL 5.7.11 - it still errors

mysql> INSERT INTO new_table VALUES (NULL, NULL), (NULL, NULL), (NULL, NULL);
ERROR 1048 (23000): Column 'test_default_nn' cannot be null

Maybe this "quirk" was "fixed"?

[ghost]

For additional reference (and some back story), in my script I have something like:

connection.query('INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?)', [val1, val2, val3], callback)

Now, val3 may or may not be passed in to the data parameter of the function wrapping this call. If it is, then I want to pass it in to the SQL statement. If it isn't, then I want MySQL database to apply the DEFAULT value defined for this table column. But, since it doesn't exist, I must assign some value to val3. So, I assigned it null (javascript value), which resulted in the error posted in my initial submission. I also tried to pass 'DEFAULT' as its value, but this did not work either.

[bbito]

@garudacrafts I'm curious what happens if you pop your last value from the array if you have no value. I'm not sure from your wording if you've tried that and it didn't work...

[ghost]

@bbito - I was able to successfully run that command, probably because my MySQL mode is not set to strict. You could try to turn off strict mode with Set sql_mode = ""; and then re-run to reproduce my results. At any rate, I found the correct way to do it, per the docs, is to pass DEFAULT, not NULL. So, I guess it stands that the feature request #559 may be the best way to add this functionality to mysql npm module?

[ghost]

@bbito - Yes, I confirmed a simple work-around is to check if val3 exists and if it doesn't, then run an alternate query like connection.query('INSERT INTO table (col1, col2) VALUES (?, ?)', [val1, val2], callback) and then the DEFAULT value for col3 is applied to the row by MySQL. For more complex queries, it may be nice to have the option to explicitly pass and insert DEFAULT (MySQL value), just like we can insert NULL (MySQL) by passing null (JavaScript value). Cheers!

[dougwilson]

So looks like a bunch of conversation occurred since I last dropped by, and the issue was even closed. I assume everything is good to go, then? The discussion sounds like it's said that this is not actually a bug in this module, rather a misunderstanding of how MySQL works, so I tagged it as a MySQL Server question.

[bbito]

@dougwilson I think the only open question out of this thread relating to this module is:
Is there or should there be a way to pass the MySQL DEFAULT keyword as a column value?

[dougwilson]

@bbito that is the purpose of the issue #559

[bbito]

@dougwilson Got it - Thanks for the reply!

[ghost]

@dougwilson - Yes, the issue as filed is not a bug, but rather stems from my misunderstanding of how MySQL handles NULL for columns with DEFAULT values set. Although it does illuminate the need for a mechanism to pass MySQL DEFAULT. I've submitted pull request mysqljs/sqlstring#11.
@bbito - Thanks for your replies and for your help.

[ghost]

@dougwilson & @bbito - I just re-read the MySQL doc on default value data types (http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html) and found something interesting that relates to this discussion. If an UPDATE statement sets the column value to NULL, then MySQL will apply the implicit DEFAULT value for the column data type in certain cases (namely, when the column is defined as NOT NULL and has no explicit DEFAULT value defined, and strict mode is not enabled). I think I've used this on auto_increment INT's (e.g. primary keys) and timestamps before, which what led to my confusion that the same applied on INSERT statements (not true!).

[lebog]

hello
someone can help me?

i create a table in mysql and i put one column NOT NULL
but when i insert a null data in this column it's inserted in the column as an empty data without return an error...i want error be returned because i insert a null value in a not null columns

that is how i create my table:

CREATE TABLE tab (
idm int(11) NOT NULL,
cln varchar(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

here i insert data:
INSERT INTO tab (idm, cln) VALUES
(1, null);

and i found result like this : INSERT INTO tab (idm, cln) VALUES
(1, "");
instead to return an error.

I work under lunix(ubuntu)

[ghost]

@lebog try setting sql_mode to STRICT_ALL_TABLES. Further further help, try Stack Overflow.