Insert query skip NOT NULL constraint

All we need is an easy explanation of the problem, so here it is.

Mysql Version: 5.5.62 and 5.6.10

lib version: 2.18.1

CREATE TABLE IF NOT EXISTS temp_user ( 
  email         varchar(255),
  name          varchar(255)  NOT NULL,
  surname   varchar(255)  NOT NULL,
  PRIMARY KEY (email)
) ENGINE=InnoDB;

When I insert a new user with a wrong query:

insert into temp_user (email) VALUES ('[email protected]');

If I execute in Mysql IDE (DBeaver) it throw an error: Errore SQL [1364] [HY000]: Field ‘name’ doesn’t have a default value

But the node library insert the record with null in name and surname.

await pool.query(`insert into temp_user (email) VALUES ('[email protected]')`);

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

That error is caused when you have the STRICT_TRANS_TABLES sql mode enabled. It sounds like that mode is not enabled on your server by default and your IDE is enabling the setting for your session. You can either just enable STRICT_TRANS_TABLES sql mode on your server so all connections will be that way by default or run a query to change the sql mode with this module’s connection prior to running your insert query (which is what your IDE is doing under the covers).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply