Sql error 1364 sqlstate hy000 - Исправление ошибок и поиск оптимальных решений проблем

My table looks like

create table try ( name varchar(8), CREATED_BY varchar(40) not null);

and then I have a trigger to auto populate the CREATED_BY field

create trigger autoPopulateAtInsert BEFORE INSERT on try for each row set new.CREATED_BY=user();

When I do an insert using

insert into try (name) values ('abc');

the entry is made in the table but I still get the error message

Field 'CREATED_BY' doesn't have a default value Error no 1364

Is there a way to suppress this error without making the field nullable AND without removing the triggfer? Otherwise my hibernate will see these exceptions ( even though the insertions have been made) and then application will crash.

asked Mar 15, 2013 at 17:41

Open phpmyadmin and goto ‘More’ Tab and select ‘Variables’ submenu.
Scroll down to find sql mode.
Edit sql mode and remove ‘STRICT_TRANS_TABLES’
Save it.

answered Oct 19, 2014 at 15:41

Nilesh DhangareNilesh Dhangare

1,0611 gold badge7 silver badges2 bronze badges

In phpmyadmin, perform the following:

select @@GLOBAL.sql_mode

In my case, I get the following:

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES ,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Copy this result and remove STRICT_TRANS_TABLES. Then perform the following:

set GLOBAL sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

answered Aug 26, 2016 at 7:43

KamilKamil

8701 gold badge8 silver badges18 bronze badges

Set a default value for Created_By (eg: empty VARCHAR) and the trigger will update the value anyways.

create table try ( 
     name varchar(8), 
     CREATED_BY varchar(40) DEFAULT '' not null
);

zardilior

2,66224 silver badges29 bronze badges

answered Mar 15, 2013 at 17:47

KinSlayerUYKinSlayerUY

1,87316 silver badges22 bronze badges

When I had this same problem with mysql5.6.20 installed with Homebrew, I solved it by going into my.cnf

nano /usr/local/Cellar/mysql/5.6.20_1/my.cnf

Find the line that looks like so:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Comment above line out and restart mysql server

mysql.server restart

Error gone!

answered Sep 15, 2014 at 13:37

Run mysql console:

mysql -u your_username -p

, select database:

USE your_database;

and run (also from mysql console):

SET GLOBAL sql_mode='';

That will turn off strict mode and mysql won’t complain any more.

To make things clear: your database definition says «this field must have default value defined», and by doing steps from above you say to MySql «neah, just ignore it». So if you just want to do some quick fix locally this solution is ok. But generally you should investigate in your database definition and check if field really needs default value and if so set it. And if default value is not needed this requirement should be removed to have clean situation.

answered Aug 17, 2018 at 8:22

MilanGMilanG

6,9162 gold badges35 silver badges64 bronze badges

As others said, this is caused by the STRICT_TRANS_TABLES SQL mode.

To check whether STRICT_TRANS_TABLES mode is enabled:

SHOW VARIABLES LIKE 'sql_mode';

To disable strict mode:

SET GLOBAL sql_mode='';

answered Aug 24, 2018 at 12:35

Damjan PavlicaDamjan Pavlica

29.5k8 gold badges69 silver badges76 bronze badges

Before every insert action I added below line and solved my issue,

SET SQL_MODE = '';

I’m not sure if this is the best solution,

SET SQL_MODE = ''; INSERT INTO  `mytable` (  `field1` ,  `field2`) VALUES ('value1',  'value2');

answered Mar 22, 2017 at 17:12

VinithVinith

1,26614 silver badges25 bronze badges

Modify your query and add «IGNORE» as:

INSERT IGNORE INTO  `mytable` (  `field1` ,  `field2`) VALUES ('value1',  'value2');

answered Feb 15, 2018 at 9:05

Its work and tested Copy to Config File: /etc/mysql/my.cnf OR /bin/mysql/my.ini

[mysqld]
port = 3306
sql-mode=""

then restart MySQL

ddb

2,4137 gold badges28 silver badges38 bronze badges

answered Aug 30, 2016 at 7:44

This appears to be caused by a long-standing (since 2004) bug (#6295) in MySQL, titled

Triggers are not processed for NOT NULL columns.

It was allegedly fixed in version 5.7.1 of MySQL (Changelog, last entry) in 2013, making MySQL behave as “per the SQL standard” (ibid).

answered Apr 24, 2015 at 17:52

B98B98

1,22912 silver badges20 bronze badges

For Windows WampServer users:

WAMP > MySQL > my.ini

search file for sql-mode=""

Uncomment it.

answered Apr 21, 2017 at 16:23

AndrewAndrew

17.8k12 gold badges102 silver badges112 bronze badges

In Windows Server edit my.ini (for example program filesmysqlmysql server n.nmy.ini)

I would not simply set the sql-mode=»», rather I suggest one removes STRICT_TRANS_TABLES from the line, leave everything as-was, and then restart MySQL from the services utility. Add a comment for future programmers who you are and what you did.

answered Dec 3, 2018 at 18:14

Most of these answers are a lot of work for the not-seasoned coder. Like mentioned the issues is with STRICT_TRANS_TABLES.

First verify STRICT_TRANS_TABLES is running.

$ mysql -u root -p -e "SHOW VARIABLES LIKE 'sql_mode';"

You can disable strict mode on your MySQL server by running the following command on your Linode’s command line:

$ mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';"

Then, you can verify that the mode is set by running the following:

$ mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

This answer was found here https://www.linode.com/community/questions/17070/how-can-i-disable-mysql-strict-mode

answered Sep 30, 2022 at 21:24

i set the fields to not null and problem solved, it updates when an information is commanded to store in it, no more showing msqli message that the field was empty cus you didnt insert value to it, well application of this solution can work on some projects depends on your project structure.

answered Nov 25, 2016 at 1:34

This is for SYNOLOGY device users:

How to set global variables (strict mode OFF) on SYNOLOGY device.
(checked on DSM 7.0.1-42218 — device model DS418)

Used PUTTY to connect:
login as root and
sudo su after… (to be admin total)

if not exist create my.cnf in:

MariaDB 5:
/var/packages/MariaDB/etc
MariaDB 10:
/var/packages/MariaDB10/etc

this should be in the file (at least for strict mode off)

# custom configs
[mysqld]
innodb_strict_mode = OFF
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

restart mysqld daemon:
MariaDB 5:
/usr/syno/bin/synopkg restart MariaDB
MariaDB 10:
/usr/syno/bin/synopkg restart MariaDB10
check for strict mode enabled at these two global options — both should be not there or off (see config above)
log into mysql:
mysql -u root -p
enter password:

show variables like ‘sql_mode’;
show variables like ‘%STRICT%’;

answered Jan 9, 2022 at 6:32

i solved problem changing my.ini file located in data folder. for mysql 5.6 my.ini file moved to data folder rather the bin or mysql installation folder.

answered Jun 2, 2019 at 9:58

I think in name column have null values in this case.

update try set name='abc' where created_by='def';

answered Jun 25, 2020 at 5:56

I am using Xampp 7.3.28-1 for Linux. It uses MariaDB 10.4.19. Its configuration file is:
/opt/lampp/etc/my.cnf

It does NOT contain an entry that defines sql_mode.
However the query «select @@GLOBAL.sql_mode;» does return a result and it contains the problematic STRICT_TRANS_TABLES. I guess it is by default now.

My solution was to explicitly define the mode by adding this line below [mysqld]:
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

You can define the modes that you need or just leave it blank.

answered Jun 17, 2021 at 19:05

M.PaunovM.Paunov

1,5971 gold badge13 silver badges19 bronze badges

I found that once I removed what was a doubling up of a foreign key and primary key, when I could have just used the foreign key as the primary key alone in the table. All my code then worked and I was able to upload to db.

answered Nov 21, 2021 at 10:34

SpinstazSpinstaz

2327 silver badges11 bronze badges

Источник

My table looks like

create table try ( name varchar(8), CREATED_BY varchar(40) not null);

and then I have a trigger to auto populate the CREATED_BY field

create trigger autoPopulateAtInsert BEFORE INSERT on try for each row set new.CREATED_BY=user();

When I do an insert using

insert into try (name) values ('abc');

the entry is made in the table but I still get the error message

Field 'CREATED_BY' doesn't have a default value Error no 1364

asked Mar 15, 2013 at 17:41

Open phpmyadmin and goto ‘More’ Tab and select ‘Variables’ submenu.
Scroll down to find sql mode.
Edit sql mode and remove ‘STRICT_TRANS_TABLES’
Save it.

answered Oct 19, 2014 at 15:41

Nilesh DhangareNilesh Dhangare

1,0611 gold badge7 silver badges2 bronze badges

In phpmyadmin, perform the following:

select @@GLOBAL.sql_mode

In my case, I get the following:

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES ,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Copy this result and remove STRICT_TRANS_TABLES. Then perform the following:

set GLOBAL sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

answered Aug 26, 2016 at 7:43

KamilKamil

8701 gold badge8 silver badges18 bronze badges

Set a default value for Created_By (eg: empty VARCHAR) and the trigger will update the value anyways.

create table try ( 
     name varchar(8), 
     CREATED_BY varchar(40) DEFAULT '' not null
);

zardilior

2,66224 silver badges29 bronze badges

answered Mar 15, 2013 at 17:47

KinSlayerUYKinSlayerUY

1,87316 silver badges22 bronze badges

When I had this same problem with mysql5.6.20 installed with Homebrew, I solved it by going into my.cnf

nano /usr/local/Cellar/mysql/5.6.20_1/my.cnf

Find the line that looks like so:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Comment above line out and restart mysql server

mysql.server restart

Error gone!

answered Sep 15, 2014 at 13:37

Run mysql console:

mysql -u your_username -p

, select database:

USE your_database;

and run (also from mysql console):

SET GLOBAL sql_mode='';

That will turn off strict mode and mysql won’t complain any more.

answered Aug 17, 2018 at 8:22

MilanGMilanG

6,9162 gold badges35 silver badges64 bronze badges

As others said, this is caused by the STRICT_TRANS_TABLES SQL mode.

To check whether STRICT_TRANS_TABLES mode is enabled:

SHOW VARIABLES LIKE 'sql_mode';

To disable strict mode:

SET GLOBAL sql_mode='';

answered Aug 24, 2018 at 12:35

Damjan PavlicaDamjan Pavlica

29.5k8 gold badges69 silver badges76 bronze badges

Before every insert action I added below line and solved my issue,

SET SQL_MODE = '';

I’m not sure if this is the best solution,

SET SQL_MODE = ''; INSERT INTO  `mytable` (  `field1` ,  `field2`) VALUES ('value1',  'value2');

answered Mar 22, 2017 at 17:12

VinithVinith

1,26614 silver badges25 bronze badges

Modify your query and add «IGNORE» as:

INSERT IGNORE INTO  `mytable` (  `field1` ,  `field2`) VALUES ('value1',  'value2');

answered Feb 15, 2018 at 9:05

Its work and tested Copy to Config File: /etc/mysql/my.cnf OR /bin/mysql/my.ini

[mysqld]
port = 3306
sql-mode=""

then restart MySQL

ddb

2,4137 gold badges28 silver badges38 bronze badges

answered Aug 30, 2016 at 7:44

This appears to be caused by a long-standing (since 2004) bug (#6295) in MySQL, titled

Triggers are not processed for NOT NULL columns.

It was allegedly fixed in version 5.7.1 of MySQL (Changelog, last entry) in 2013, making MySQL behave as “per the SQL standard” (ibid).

answered Apr 24, 2015 at 17:52

B98B98

1,22912 silver badges20 bronze badges

For Windows WampServer users:

WAMP > MySQL > my.ini

search file for sql-mode=""

Uncomment it.

answered Apr 21, 2017 at 16:23

AndrewAndrew

17.8k12 gold badges102 silver badges112 bronze badges

In Windows Server edit my.ini (for example program filesmysqlmysql server n.nmy.ini)

answered Dec 3, 2018 at 18:14

Most of these answers are a lot of work for the not-seasoned coder. Like mentioned the issues is with STRICT_TRANS_TABLES.

First verify STRICT_TRANS_TABLES is running.

$ mysql -u root -p -e "SHOW VARIABLES LIKE 'sql_mode';"

You can disable strict mode on your MySQL server by running the following command on your Linode’s command line:

$ mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';"

Then, you can verify that the mode is set by running the following:

$ mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

This answer was found here https://www.linode.com/community/questions/17070/how-can-i-disable-mysql-strict-mode

answered Sep 30, 2022 at 21:24

answered Nov 25, 2016 at 1:34

This is for SYNOLOGY device users:

How to set global variables (strict mode OFF) on SYNOLOGY device.
(checked on DSM 7.0.1-42218 — device model DS418)

Used PUTTY to connect:
login as root and
sudo su after… (to be admin total)

if not exist create my.cnf in:

MariaDB 5:
/var/packages/MariaDB/etc
MariaDB 10:
/var/packages/MariaDB10/etc

this should be in the file (at least for strict mode off)

# custom configs
[mysqld]
innodb_strict_mode = OFF
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

restart mysqld daemon:
MariaDB 5:
/usr/syno/bin/synopkg restart MariaDB
MariaDB 10:
/usr/syno/bin/synopkg restart MariaDB10
check for strict mode enabled at these two global options — both should be not there or off (see config above)
log into mysql:
mysql -u root -p
enter password:

show variables like ‘sql_mode’;
show variables like ‘%STRICT%’;

answered Jan 9, 2022 at 6:32

i solved problem changing my.ini file located in data folder. for mysql 5.6 my.ini file moved to data folder rather the bin or mysql installation folder.

answered Jun 2, 2019 at 9:58

I think in name column have null values in this case.

update try set name='abc' where created_by='def';

answered Jun 25, 2020 at 5:56

I am using Xampp 7.3.28-1 for Linux. It uses MariaDB 10.4.19. Its configuration file is:
/opt/lampp/etc/my.cnf

My solution was to explicitly define the mode by adding this line below [mysqld]:
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

You can define the modes that you need or just leave it blank.

answered Jun 17, 2021 at 19:05

M.PaunovM.Paunov

1,5971 gold badge13 silver badges19 bronze badges

answered Nov 21, 2021 at 10:34

SpinstazSpinstaz

2327 silver badges11 bronze badges

Источник

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

SELECT @@sql_mode;

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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:-

image_1

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:-

image_2

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:-

image_3

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:-

image_4

Insert is successful this time.

SELECT * FROM employee_details;

Output:-

image_5

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:-

image_6

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:-

image_7

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:-

image_8

Insert is successful this time.

SELECT * FROM employee_details;

Output:-

image_9

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.

Источник

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:

SELECT @@sql_mode;

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:

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

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

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

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:

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

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.

Happy coding !

Источник

Today We are Going To Solve SQLSTATE[HY000]: General error: 1364 Field ‘name’ doesn’t have a default value laravel 5.5 in Php. Here we will Discuss All Possible Solutions and How this error Occurs So let’s get started with this Article.

Contents

1 How to Fix SQLSTATE[HY000]: General error: 1364 Field ‘name’ doesn’t have a default value laravel 5.5 Error?
- 1.1 Solution 1 : Change your config/database.php
- 1.2 Solution 2 : Edit your migration script
- 1.3 Solution 3 : Add the below code
2 Conclusion
- 2.1 Also Read These Solutions

How to Fix SQLSTATE[HY000]: General error: 1364 Field ‘name’ doesn’t have a default value laravel 5.5 Error?
To Fix SQLSTATE[HY000]: General error: 1364 Field ‘name’ doesn’t have a default value laravel 5.5 Error just Change your config/database.php. to solve this issue you have to just change your config/database.php in the connections.mysql section by putting ‘strict’ => false. this will help you to solve the issue. Try it!
SQLSTATE[HY000]: General error: 1364 Field ‘name’ doesn’t have a default value laravel 5.5
To Fix SQLSTATE[HY000]: General error: 1364 Field ‘name’ doesn’t have a default value laravel 5.5 Error just Edit your migration script. Just edit your migration script and put default value on this field to solve the issue. You can learn it better by given the below example. I hope this will help you. $table->string('name')->default('');

Solution 1 : Change your config/database.php

to solve this issue you have to just change your config/database.php in the connections.mysql section by putting ‘strict’ => false. this will help you to solve the issue. Try it!

Solution 2 : Edit your migration script

Just edit your migration script and put default value on this field to solve the issue. You can learn it better by given the below example. I hope this will help you.

$table->string('name')->default('');

Solution 3 : Add the below code

To solve this error just add the below one to your migration file. It will help you.

$table->string('name')->nullable();

Conclusion

So these were all possible solutions to this error. I hope your error has been solved by this article. In the comments, tell us which solution worked? If you liked our article, please share it on your social media and comment on your suggestions. Thank you.

Bug report

What I did

Want to create categories with its parent.

Migration:

Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('slug')->unique();
            $table->integer('parent_id')->unsigned()->nullable();
            $table->timestamps();
        });

Model:

protected $fillable = ['name', 'slug', 'parent_id'];
public function children()
    {
        return $this->hasMany('AppModelsCategory', 'parent_id');
    }

Controller:

protected function setupCreateOperation()
    {
        $this->crud->setValidation(CategoryRequest::class);

        // TODO: remove setFromDb() and manually define Fields
        $this->crud->addField(['name' => 'name', 'type' => 'text', 'label' => 'Name']);
        $this->crud->addField(['name' => 'slug', 'type' => 'text', 'label' => 'Slug']);
        $this->crud->addField(['name' => 'parent_id', 'type' => 'select2', 'label' => 'Parent Category', 'entity' => 'children', 'attribute' => 'slug']);
    }

What I expected to happen

To store normally in database

What happened

SQLSTATE[HY000]: General error: 1364 Field ‘name’ doesn’t have a default value (SQL: insert into categories (parent_id, updated_at, created_at) values (2, 2020-05-04 18:13:42, 2020-05-04 18:13:42))

It writes this error but simultaniously inserts in database with name slug and parent, that means everything is working just getting this error

What I’ve already tried to fix it

I tried to put in BackpackCRUDappHttpControllersOperationsCraeteOperation
dd($request); in store() function and output was

#parameters: array:6 [▼
      "_token" => "JtVZd8Ejcomf6khWHQgMeDbtE9towuqO4hN6BKzx"
      "http_referrer" => "http://localhost:8000/admin/category/create"
      "parent_id" => "1"
      "name" => "asdasd"
      "slug" => "asdasd"
      "save_action" => "save_and_back"
    ]

Meaning that request does have name and slug attribute so I can not understand why I am getting this error.

Backpack, Laravel, PHP, DB version

When I run php artisan backpack:version the output is: 4.0, 7.9.2 , 7.3.1

Источник

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:

mysql> SHOW VARIABLES LIKE 'sql_mode';

Enter fullscreen mode

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:

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

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

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Enter fullscreen mode

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';

Источник

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

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

Introduction

Error code 1364 resolution with AUTO_INCREMENT

Error code 1364 resolution with DEFAULT value

A. Logic Solution

B. Server Side Solution

Solution 1 : Change your config/database.php

Solution 2 : Edit your migration script

Solution 3 : Add the below code

Conclusion

Also Read These Solutions

Bug report

What I did

What I expected to happen

What happened

What I’ve already tried to fix it

Backpack, Laravel, PHP, DB version

It’s all about the money

Failing inserts

Unusual debugging

Field doesn’t have a default value

Changing the configuration