Browse By

Disable/Enable MySQL strict mode in localhost phpMyAdmin (Xampp)

 

Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

Strict SQL Mode. Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE . A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. For more detailed information visit https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html.

MySql Error – Field ‘column_name’ doesn’t have a default value

This is not inserting into database, because the field Ex: ‘column_name’ (and maybe some others) is checked as “NOT NULL”. It means that the value of that field must be something other that NULL (NULL – no data at all).

Setting fields as not null on database structure is usually a great way to ensure that some data will always be present in the field. Depending on your needs, you can also mark it as NULL so it will never throw an error and will save into DB without the need for anything to be inserted into a specified field.

On other way, if its really required to support the server environment, disable the strict mode on MySql.

Setting the SQL Mode

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

STRICT_TRANS_TABLES is the setting for MySQL strict mode.

To find out strict mode status, run the below sql

SHOW VARIABLES LIKE 'sql_mode';

If the output value contains STRICT_TRANS_TABLES, then strict mode is enabled.

To disable strict mode, run the below sql

SET GLOBAL sql_mode = '';

To enable strict mode, run the below sql

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

 

Latest Posts