×
How to change ‘sql_mode’ in mysql


How to change ‘sql_mode’ in mysql

July 2, 2018

To see Mysql mode settings,do the following query. Mysql mode is not linked to any particular database. Modes can be set locally to the session (connection), or for the server globally.

This gives results like below

 

Now to change sql_mode,you need to run query like this

Example

Some times after  importing database and changing some table structure,you may get an error like this.

This is because NO_ZERO_IN_DATE and NO_ZERO_DATE is enabled in and database has invalid date values.

NO_ZERO_IN_DATE

The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. Example – ‘2010-00-01’ or ‘2010-01-00’

NO_ZERO_DATE

This mode is deprecated

This determines whether the server permits ‘0000-00-00’ . NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled.

Removing above modes from sql_mode  lets the server allow invalid dates in the aforementioned format

 

 

Or to set it PERMANANTLY, change etc/my.cnf

and restart

WHAT is sql_mode

Server SQL modes determine what SQL syntax MySQL should support and what kind of data validation checks it should use. This makes it easier to use MySQL in different environments.

See more on  list of sql modes from docs

Full List of SQL Modes

ALLOW_INVALID_DATES

Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.

ANSI_QUOTES

Treat ” as an identifier quote character and not as a string quote character.

ERROR_FOR_DIVISION_BY_ZERO

The ERROR_FOR_DIVISION_BY_ZERO mode affects handling of division by zero, which includes MOD(N,0). For data-change operations (INSERT, UPDATE), its effect also depends on whether strict SQL mode is enabled.

If this mode is not enabled, division by zero inserts NULL and produces no warning.

If this mode is enabled, division by zero inserts NULL and produces a warning.

If this mode and strict mode are enabled, division by zero produces an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, division by zero inserts NULL and produces a warning.

For SELECT, division by zero returns NULL. Enabling ERROR_FOR_DIVISION_BY_ZERO causes a warning to be produced as well, regardless of whether strict mode is enabled.

As of MySQL 5.6.17, ERROR_FOR_DIVISION_BY_ZERO is deprecated and setting the sql_mode value to include it generates a warning.

HIGH_NOT_PRECEDENCE

The precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). In some older versions of MySQL, the expression was parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behavior can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode.

IGNORE_SPACE

Permit spaces between a function name and the ( character. This causes built-in function names to be treated as reserved words.

NO_AUTO_CREATE_USER

Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless authentication information is specified.

NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.
This mode can be useful if 0 has been stored in a table’s AUTO_INCREMENT column.

NO_BACKSLASH_ESCAPES

Disable the use of the backslash character (\) as an escape character within strings. With this mode enabled, backslash becomes an ordinary character like any other.

NO_DIR_IN_CREATE

When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers.

NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

NO_FIELD_OPTIONS

Do not print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

NO_KEY_OPTIONS

Do not print MySQL-specific index options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

NO_TABLE_OPTIONS

Do not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

NO_UNSIGNED_SUBTRACTION

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, an error results.

NO_ZERO_DATE

Explained before

NO_ZERO_IN_DATE

Explained before

ONLY_FULL_GROUP_BY

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause.

PAD_CHAR_TO_FULL_LENGTH

By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval.

REAL_AS_FLOAT

Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE.

STRICT_ALL_TABLES

Enable strict SQL mode for all storage engines. Invalid data values are rejected. For details, see Strict SQL Mode.

STRICT_TRANS_TABLES

Enable strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines. For details, see Strict SQL Mode.

Server SQL modes determine what SQL syntax MySQL should support and what kind of data validation checks it should use. This makes it easier to use MySQL in different environments.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

 

One Comment

  • allen peters July 4, 2018 at 5:18 am

    Thanks .I had invalid default value error after migratinga db to my local machine.This got me to solve it

Leave a Reply

Your email address will not be published. Required fields are marked *