Mysql error 1364 Field doesn’t have a default values

Spread the love

When get this error: mysql error 1364 Field doesn’t have a default values.

This happens when there’s key index but no values given in STRICT_TRANS_TABLES mode.

So we need to change sql_mode which is defined in my.cnf file.

When I search about the file, unfortunately I couldn’t find the file.

So rather than modify file, use query to change mysql configuration:

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'

If this doesn’t work or it doesn’t allow you to query, you can modify mysql.cnf file.

vi /etc/mysql/conf.d/mysql.cnf

Then add these line

[mysqld] sql_mode = "ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

And reboot mysql.

sudo service mysql restart

If still not working, add this disable_strict_mode.cnf file below location.

vi  /etc/mysql/conf.d/disable_strict_mode.cnf

Then add these 2 lines.

sql_mode = ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After reboot, check it by running this query:

SHOW VARIABLES LIKE 'sql_mode';
Facebook Comments

Published by

Titus Choi

I love technology. Working for information technology. Interested in AVR technology. Playing with RC stuff.