Sql ошибка 1364

In this article, we will discuss why Error 1364 occurs and how to resolve it. 

Table of Contents

• Introduction
• Error code 1364 resolution with AUTO_INCREMENT
• Error code 1364 resolution with DEFAULT value

Introduction

MySQL server throws the Error 1364 if the query or statement tries to insert a row without a value for a particular column defined as NOT NULL. We can say that the absence of a NOT NULL column value during insertion causes this error to be thrown by the MySQL server. 

Error 1364  indicates that the value of the particular field should be something other than NULL. One way to resolve the error forever is to make the column as DEFAULT NULL in table definition but if that does not meet your requirement, let us see some ways to fix this error in the below sections.

We will be creating a sample table employee_details for illustration of the concept.

Advertisements

#create the table employee_details
 CREATE TABLE employee_details(
  emp_id int ,
  emp_enroll_no varchar(255) NOT NULL,
  emp_firstName varchar(255) DEFAULT NULL,
  emp_lastName varchar(255) DEFAULT NULL,
  primary key(emp_id)
);

Here, the column emp_id and emp_enroll_no both cannot be NULL.

DESC employee_details;

Output:-

Error code 1364 resolution with AUTO_INCREMENT

In this section, we will recreate error 1364 and will fix it using the AUTO_INCREMENT keyword. AUTO_INCREMENT in MySQL assigns a numeric value to a column starting from 1 (when another starting number not specified) and then increments the value by 1 for consecutive inserts.

Let us try to insert a row without specifying any value for column emp_id.

INSERT INTO employee_details (emp_enroll_no,emp_firstName,emp_lastName) VALUES("1-N","Henry","Smith");

Action Output:-

Since we did not specify any value for emp_id in the insert statement, the output in image_2 shows that the error 1364 is thrown with the message response: Error Code: 1364. Field ’emp_id’ doesn’t have a default value.

Observe the below ALTER query for the solution. Any insert happening after executing the below statement will assign a value to emp_id starting with 1 and incremented by 1 in successive inserts. 

ALTER TABLE employee_details MODIFY emp_id int NOT NULL AUTO_INCREMENT;

Action Output:-

Let us again try to execute the insert statement.

INSERT INTO employee_details (emp_enroll_no,emp_firstName,emp_lastName) VALUES("1-N","Henry","Smith");

Action Output:-

Insert is successful this time.

SELECT * FROM employee_details;

Output:-

Error code 1364 resolution with DEFAULT value

This section will recreate error 1364 and fix it by assigning a DEFAULT value to the column.

Let us try to insert a row without specifying any value for column emp_enroll_no.

 INSERT INTO employee_details (emp_id, emp_firstName, emp_lastName) VALUES(2, "Richa", "Johnson");

Action Output:-

Since we did not specify any value for emp_enroll_no in the insert statement, the output in image_6 shows that the error 1364 is thrown with the message response: Error Code: 1364. Field ’emp_enroll_no’ doesn’t have a default value.

Observe the below ALTER query for the solution. Here, we will give a default value to the column emp_enroll_no such that if any insert happens without any value for emp_enroll_no, a default value “N-N” will be inserted.

ALTER TABLE employee_details MODIFY emp_enroll_no varchar(255) NOT NULL DEFAULT "N-N";

Action Output:-

Let us again try to execute the same insert statement.

INSERT INTO employee_details (emp_id, emp_firstName, emp_lastName) VALUES(2, "Richa", "Johnson");

Action Output:-

Insert is successful this time.

SELECT * FROM employee_details;

Output:-

The output in image_9 shows that a default value of “N-N” was inserted in second row.

READ MORE:

• MySQL: Error 1264 Out of range value for a column [Solved]

We hope this article helped you understand and resolve Error 1364 in MySQL. Good Luck!!!

Advertisements

Thanks for reading.

MySQL Error 1364 can now be fixed with any of these methods provided in this article. At Bobcares, as part of our MySQL Support Service, we answers all MySQL inquiries, large or small.

When will we see ‘MySQL Error 1364’?

If a query or statement tries to insert/update a record without a value for a specific column that is NOT NULL, MySQL server throws the Error 1364. We can say that the MySQL server throws this error because there wasn’t a NOT NULL column value during the insertion process.

This exception only occurs when using MySQL in Strict mode. We can find out easily what mode the MySQL server is running in by executing the following command:

SELECT @@sql_mode;

How To Fix MySQL Error 1364?

Let’s see the ways our Support team suggests to fix this error.

1. Logic Solution: When trying to insert a record, the value will be null but the error won’t be thrown if you provide a value for the field that is causing the exception or specify that the field can be null. For instance, with a column that cannot be null, the following query would throw the exception.

   INSERT INTO user (column_a, column_b) VALUES ("Value First Column", NULL);

   Now we alter the column to make the column nullable.

   ALTER TABLE YourTable MODIFY column_b VARCHAR(255) DEFAULT NULL;

   The MySQL Error 1364 will not appear again.

2. Server Side Solution: Uninstalling MySQL’s strict mode is a simple solution. By changing the sql_mode setting to an empty string or by removing the STRICT_TRANS_TABLES option that puts MySQL in Strict Mode and enabling the NO_ENGINE_SUBSTITUTION mode, we can disable the strict mode in MySQL’s configuration file (my.ini in Windows or my.cnf in Linux).

   [mysqld]
   sql-mode=""
   # Or
   # sql-mode="NO_ENGINE_SUBSTITUTION"
   

   Now save the changes, restart MySQL, and the MySQL Error 1364 won’t show up again.

3. Using AUTO_INCREMENT: When no other starting number is specified, MySQL’s AUTO INCREMENT function assigns a numeric value to each column starting at 1 and increases that value by 1 with each additional insert. Let’s take an example:

   CREATE TABLE employee_details(
     emp_id int ,
     emp_enroll_no varchar(255) NOT NULL,
     emp_firstName varchar(255) DEFAULT NULL,
     emp_lastName varchar(255) DEFAULT NULL,
     primary key(emp_id)
   );

   Here the columns, emp_id and emp_enroll_no both cannot be NULL. Then insert a record with the command without specifying any value for column emp_id.

   INSERT INTO employee_details (emp_enroll_no,emp_firstName,emp_lastName) VALUES("1-N","Henry","Smith");

   Since we left emp_id blank in the insert statement, the output reveals the MySQL Error 1364 with the following message response: Error Code 1364. So we can fix it by ALTER query. Any insert after executing the below statement will assign a value to emp_id starting with 1 and increments by 1 in successive inserts.

   ALTER TABLE employee_details MODIFY emp_id int NOT NULL AUTO_INCREMENT;

   Now executing the insert statement again without specifying any value for column emp_id will result in a successful insert this time.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, Our Support team briefly explains about the MySQL Error 1364. The article also includes some of the simple solutions to fix the error.

MySQL Error 1364 | How To Resolve It?

by Shahalamol R | Aug 11, 2022

MySQL Error 1364 can now be fixed with any of these methods provided in this article. At Bobcares, as part of our MySQL Support Service, we answers all MySQL inquiries, large or small.

When will we see ‘MySQL Error 1364’?

If a query or statement tries to insert/update a record without a value for a specific column that is NOT NULL, MySQL server throws the Error 1364. We can say that the MySQL server throws this error because there wasn’t a NOT NULL column value during the insertion process.

This exception only occurs when using MySQL in Strict mode. We can find out easily what mode the MySQL server is running in by executing the following command:

How To Fix MySQL Error 1364?

Let’s see the ways our Support team suggests to fix this error.

Logic Solution: When trying to insert a record, the value will be null but the error won’t be thrown if you provide a value for the field that is causing the exception or specify that the field can be null. For instance, with a column that cannot be null, the following query would throw the exception.

Now we alter the column to make the column nullable.

The MySQL Error 1364 will not appear again.

Now save the changes, restart MySQL, and the MySQL Error 1364 won’t show up again.

Here the columns, emp_id and emp_enroll_no both cannot be NULL. Then insert a record with the command without specifying any value for column emp_id.

Since we left emp_id blank in the insert statement, the output reveals the MySQL Error 1364 with the following message response: Error Code 1364. So we can fix it by ALTER query. Any insert after executing the below statement will assign a value to emp_id starting with 1 and increments by 1 in successive inserts.

Now executing the insert statement again without specifying any value for column emp_id will result in a successful insert this time.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, Our Support team briefly explains about the MySQL Error 1364. The article also includes some of the simple solutions to fix the error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

Источник

Error Code: 1364 [Solved] Field doesn’t have a default value

In this article, we will discuss why Error 1364 occurs and how to resolve it.

Table of Contents

Introduction

MySQL server throws the Error 1364 if the query or statement tries to insert a row without a value for a particular column defined as NOT NULL. We can say that the absence of a NOT NULL column value during insertion causes this error to be thrown by the MySQL server.

Error 1364 indicates that the value of the particular field should be something other than NULL. One way to resolve the error forever is to make the column as DEFAULT NULL in table definition but if that does not meet your requirement, let us see some ways to fix this error in the below sections.

We will be creating a sample table employee_details for illustration of the concept.

Here, the column emp_id and emp_enroll_no both cannot be NULL.

Output:-

Error code 1364 resolution with AUTO_INCREMENT

In this section, we will recreate error 1364 and will fix it using the AUTO_INCREMENT keyword. AUTO_INCREMENT in MySQL assigns a numeric value to a column starting from 1 (when another starting number not specified) and then increments the value by 1 for consecutive inserts.

Let us try to insert a row without specifying any value for column emp_id.

Action Output:-

Since we did not specify any value for emp_id in the insert statement, the output in image_2 shows that the error 1364 is thrown with the message response: Error Code: 1364. Field ’emp_id’ doesn’t have a default value.

Observe the below ALTER query for the solution. Any insert happening after executing the below statement will assign a value to emp_id starting with 1 and incremented by 1 in successive inserts.

Action Output:-

Let us again try to execute the insert statement.

Action Output:-

Insert is successful this time.

Output:-

Error code 1364 resolution with DEFAULT value

This section will recreate error 1364 and fix it by assigning a DEFAULT value to the column.

Let us try to insert a row without specifying any value for column emp_enroll_no.

Action Output:-

Since we did not specify any value for emp_enroll_no in the insert statement, the output in image_6 shows that the error 1364 is thrown with the message response: Error Code: 1364. Field ’emp_enroll_no’ doesn’t have a default value .

Observe the below ALTER query for the solution. Here, we will give a default value to the column emp_enroll_no such that if any insert happens without any value for emp_enroll_no, a default value “N-N” will be inserted.

Action Output:-

Let us again try to execute the same insert statement.

Action Output:-

Insert is successful this time.

Output:-

The output in image_9 shows that a default value of “N-N” was inserted in second row.

READ MORE:

We hope this article helped you understand and resolve Error 1364 in MySQL. Good Luck.

Источник

Как решить MySQL исключение SQLSTATE [HY000]: Общая ошибка: 1364 Поле field_name не имеет значения по умолчанию

Общая ошибка 1364 в основном возникает, когда вы пытаетесь вставить / обновить запись в вашей базе данных, где поле, а именно то, которое вызывает исключение, не может быть нулевым, однако вы пытаетесь установить его значение как нулевое (вы не предоставляя значение для этого). Обычно это исключение срабатывает только при использовании MySQL в строгом режиме, Вы можете получить режим, который использует ваш сервер MySQL, с помощью следующего запроса:

Если вы столкнулись с этим исключением, возможно, потому что у вас включен строгий режим ( STRICT_TRANS_TABLES ). В этой статье мы расскажем вам о двух способах предотвращения появления этого исключения.

А. Логическое решение

Чтобы предотвратить это исключение, просто предоставьте значение для поля, которое вызывает исключение, или определите, что поле может быть пустым, поэтому при попытке вставить запись значение будет нулевым, но ошибка не будет выдана, для Например, со столбцом, который не может быть пустым, следующий запрос выдаст исключение:

Таким образом, вы можете изменить столбец, чтобы сделать его обнуляемым:

И это все, исключение больше не должно появляться.

Б. Серверное решение

Если вы не можете самостоятельно определить значение столбца, потому что вы используете платформу или другой тип логики, которая сначала вставляет запись с нулевым значением, а затем обновляется, вы можете положиться на простой способ, который удаляет строгий режим MySQL. Это в некоторой степени не рекомендуется, так как это предотвращает, например, вставку пустого значения в столбец, который не может быть пустым, вызывая упомянутое исключение. Все происходит в основном как мера контроля, которая предотвращает сбой вашей логики и предотвращает написание нестабильного кода, где вы можете упустить неправильную написанную логику и т. Д.

В других случаях написанная вами логика работает в вашей локальной среде, потому что, вероятно, вы не установили там строгий режим, но на вашем рабочем сервере он не работает из-за этого режима. В этом случае вы можете отключить строгий режим, изменив конфигурационный файл MySQL ( my.ini в Windows или my.cnf в Linux) установка sql_mode в пустую строку или удаление STRICT_TRANS_TABLES опция, которая устанавливает MySQL в строгом режиме и имеет NO_ENGINE_SUBSTITUTION режим включен:

Сохраните изменения, перезапустите службу mysql, и ошибка больше не будет появляться, вместо этого для столбца будет установлено значение null, хотя это не разрешено.

Источник

MySQL: ERROR1364 fix

It’s all about the money

Saving $s has become a hard fact for many individuals and companies these days. In this process I started questioning whether I really need an AWS RDS database instance of whether I run my mysql installation on my EC2. After a rather simple switch, I set up a cron-job for dumps and planned on storing them in a separate S3 bucket (you never know, right?).

The complete process took me less than two hours and I started to rethink some DevOps concepts after being very happy with the benchmarks & performance of my brave EC2 micro instance.

Failing inserts

However, something was off. Did people stop using the POC installation of blua.blue? Since I am a big proponent of privacy, monitoring is limited to a minimum. But API traffic seemed normal.

I tried logging in, changing settings, signing up, commenting, viewing — all seemed working. Until I failed to create a new article.

Unusual debugging

I was unable to reproduce the error locally and none of the error logs on my server showed anything. Hm, how was my error reporting set up again? Sometimes the bold methods are the right choice: I logged every SQL-transaction result and found the following error-number on some transactions: 1364

Field doesn’t have a default value

A quick google search revealed that apparently some of my tables contain columns requiring a value. But didn’t I use exactly the same schemata as previously used via RDS? Thankfully, I am not the first one running into this issue and it seemed I should check somewhere else then I expected: in my MySQL configuration. The fist thing I did was verifying that what I read online is indeed my problem:

Exit fullscreen mode

And sure enough, there it was: STRICT_TRANS_TABLES

What this variable does is setting the your MySQL to reject any empty (or most likely undefined) field or your query unless there is a defined default value.
I don’t know about you, but I always lived quite happily with the fact that nullable fields will default to null. And minimizing constraints is definitely a plus on top of the laziness.
So how can I get rid of that behavior?

Changing the configuration

The easiest way is to override your mysql configuration. Depending on your setup, your will find various .cnf files that will offer possibilities. If you are not sure, simply create a new one in the conf-directory (likely /etc/mysql/conf.d/ ).

Here are the steps to take:

1. copy your existing mode-variable (see above SQL-query)
2. define your new sql_mode variable (under mysqld block)
3. restart the mysql service

If you created your own .cnf-file (e.g. custom.cnf ), it could now look like this:

Exit fullscreen mode

After restarting your service, you might want to check your setup again

$ sudo service mysql restart

$ mysql -u root -p
mysql> SHOW VARIABLES LIKE ‘sql_mode’;

Источник

How to solve MySQL exception SQLSTATE[HY000]: General error: 1364 Field ‘field_name’ doesn’t have a default value

Carlos Delgado

Learn why this exception is thrown in MySQL and how to bypass it.

The general error 1364 is basically triggered when you are try to insert/update a record on your database where a field, namely the one that is triggering the exception cannot be null, however you are trying to set its value as null (you are not providing a value for it). Normally, this exception is only triggered when you are using MySQL in Strict mode, you can get the mode that your MySQL server is using with the following query:

If you face this exception, it’s probably because you have the strict mode enabled ( STRICT_TRANS_TABLES ). In this article, we’ll share with you 2 ways to prevent this exception from appearing easily.

A. Logic Solution

To prevent this exception, simply provide a value for the field that is triggering the exception or define that the field can be null, so when you try to insert a record, the value will be null but the error won’t be thrown, for example with a column that cannot be null, the following query would throw the exception:

So you could alter the column to make the column nullable:

And that’s it, the exception shouldn’t appear anymore.

B. Server Side Solution

If you are unable to define by yourself the value of the column because you are using a framework or other kind of logic that first inserts the record with null and then it’s updated, you may rely on the easy way that is removing the strict mode of MySQL. This is someway not recommended as this prevents for example the insertion of an empty value in a column that cannot be null throwing the mentioned exception. All happens basically as a measure of control that prevents that your logic fails and prevents you from writing flappy code, where you may overlook wrong written logic etc.

In other cases, the logic that you wrote works on your local environment, because probably you don’t have set the strict mode in there, but on your production server it doesn’t work because of this mode. In this case, you can disable the strict mode modifying the configuration file of MySQL ( my.ini in Windows or my.cnf in Linux) setting the sql_mode to an empty string or removing the STRICT_TRANS_TABLES option that sets MySQL in Strict Mode and having the NO_ENGINE_SUBSTITUTION mode enabled:

Save changes, restart the mysql service and the error won’t appear anymore, instead the column will be set to null although it’s not allowed.

Источник