CODEWITHSUNDEEP
mysqlmodestrict
Čamo
Čamo

Reputation: 4163

General error: 1364 Field 'xxxx' doesn't have a default value

I got a project, which make insertions which inserting no values(not empty values) to the columns with NOT NULL and NO DEFAULT values. I believed that is impossible to make insertion with missing required values, and it always throws an error: Field 'xxxx' doesn't have a default value. But as I see here mysql can be set to 

sql-mode="NO_ENGINE_SUBSTITUTION"

I am confused, cause I think it is dangerous. And if I switch it OFF it will apply to all projects and it could be really bad. So what should I do? Is it possible to set the mode only for one mysql database while other databases will be on STRICT mode? What do you think about it? Is it an issue or not?

Upvotes: 0

Views: 1935

Answers (3)

Čamo
Čamo

Reputation: 4163

So if somebody wants to know how it looks like in PHP/PDO for one concrete session:

$pdo = new PDO(
     $dsn, 
     $username, 
     $password, 
     array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="NO_ENGINE_SUBSTITUTION"') 
);

Upvotes: 0

Achref Gassoumi
Achref Gassoumi

Reputation: 1056

The solution that i propose is to made a trigger so when there is a no value it will insert a null value to that column

this is an example :

CREATE TRIGGER upd_check BEFORE UPDATE ON account
     FOR EACH ROW
     BEGIN
         IF NEW.amount < 0 THEN
             SET NEW.amount = 0;
         END IF;
END;

Upvotes: 0

user1881277
user1881277

Reputation:

The sql-mode system variable is available at both global and session level. Which means either you have to set this for entire server or particular connection. So there is no way to configure this for subset of DBs at server level. However you can specify the sql mode when you are making the connection. So those connections will run in strict mode.

Upvotes: 1

Related Questions