Sql_mode parsing and setting for MySQL

54140286

70432123

After upgrading MySQL, actually use the error. Update time prompt timestamp type error, prompt no default value error

The original is MySQL upgraded security, the original 0000-00-00 is not allowed, the default value must also be acquired set up

You need to change it, but you haven't found a quick way to change it, but you have to change the MySQL settings first:

First, view Sql_mode

MySQL>Select@ @sql_mode;  

Second, the meaning of Sql_mode value:

Only_full_group_by:

For a group by aggregation operation, if the column in select does not appear in group by, the SQL is considered illegal because the column is not in the GROUP BY clause

Strict_trans_tables:

In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted and no restrictions are made on the non-transactional table

No_zero_in_date:

In strict mode, the month or day part is not accepted as a 0 date. If you use the Ignore option, we insert ' 0000-00-00 ' for a similar date. In non-strict mode, the date can be accepted, but a warning is generated.

No_zero_date:

In strict mode, do not make ' 0000-00-00 ' a legal date. You can still insert the 0 period with the Ignore option. In non-strict mode, the date can be accepted, but a warning is generated

Error_for_division_by_zero:

In strict mode, if 0 is removed (or mod (x,0)) during insert or update, an error is generated (otherwise, warning). If this mode is not given, MySQL returns null when 0 is removed. If you use Insert ignore or update ignore, the MySQL build is 0 apart from the warning, but the result of the operation is null.

No_auto_create_user

Prevents grant from automatically creating new users unless a password is also specified.

No_engine_substitution:

Throws an error if the required storage engine is disabled or not compiled. When this value is not set, the default storage engine is substituted and an exception is thrown

Iii. Change of my.conf

Remove the red from above

#sql_mode =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_substitutionsql_mode= ' Only_full_group_by,error_for_division_by_zero,no_ Auto_create_user,no_engine_substitution '

Four, restart

Mysql.serve restart

Systemctl Restart Mysqlnd

Sql_mode parsing and setting for MySQL