CODEWITHSUNDEEP
mysql
Frexius
Frexius

Reputation: 35

Mysql Not Null Field on Insert

This is my query: 

INSERT INTO tabla (campo1,campo2,campo3) VALUES ('$campo1','$campo2','$campo3')

Since 'tabla' has many more columns and some of them are not null and neither have a default value, I'm getting the mysql message: "Fiel 'column4' doesn't have a default value".

I'm running that on my local server, tried it out at online server and it does insert the row with no issues.

Does anyone knows if this has to do with some sort of configuration of the mysql.ini file or something like that? There are too many columns like that on my db, so changing columns to 'null' or set them a default value would take a lot of time. I think it could be fixed as it works perfect on the server.

Thanks.

UPDATE:

It is the SQL mode. I queried SELECT @@GLOBAL.sql_mode;

My local server returned: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
The remote server returned an empty row.

Where can I change that setting on my localhost?

Upvotes: 0

Views: 1103

Answers (3)

fthiella
fthiella

Reputation: 49089

If you are sure that you are using the same schema both locally and on the remote server, it's probably the SQL mode of the remote server that's set different than the local SQL mode, you can check it with one of those:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

This will reproduce the problem:

CREATE TABLE tabla (
  campo1 VARCHAR(255) NOT NULL,
  campo2 VARCHAR(255) NOT NULL,
  campo3 VARCHAR(255) NOT NULL,
  campo4 VARCHAR(255) NOT NULL);

SET @@SESSION.sql_mode= 'STRICT_TRANS_TABLES';

INSERT INTO table (campo1, campo2, campo3) VALUES ('a', 'b', 'c');

and this will work, even if there's a column costraint on campo4:

CREATE TABLE tabla (
  campo1 VARCHAR(255) NOT NULL,
  campo2 VARCHAR(255) NOT NULL,
  campo3 VARCHAR(255) NOT NULL,
  campo4 VARCHAR(255) NOT NULL);

SET @@SESSION.sql_mode= '';

INSERT INTO table (campo1, campo2, campo3) VALUES ('a', 'b', 'c');

Please see fiddle here.

Have a look at the manual at the Server SQL Modes page.

Upvotes: 1

Andy W
Andy W

Reputation: 2192

This weird behavior might depend on "sql_mode" system variable - it controls vast amount of quirks and settings.

So, check the mode by running select @@sql_mode; command on your both servers - it must output different values, so you know what mode is used on your local server.

Upvotes: 1

Seti
Seti

Reputation: 2314

If the column is not null you must provide its value when inserting record. There is no other way - it is not null just for forcing ANY data in it (even if its just EMPTY string. But you must provide it in the insert query.

You may add default value for the column. Try importing remote table to your local one - that will minimize future problems with schema.

Upvotes: 0

Related Questions