How to change ‘sql_mode’ in mysql
PHP , Uncategorized / 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…

Share this page in social media platforms