cPanel How to – disable strict mode in MySQL

Need to disable strict mode in MySQL on a cPanel / WHM server? Here’s how you do it.

What is “strict mode” anyway?

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. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE. – From MySQL documentation

Follow these steps to disable strict mode –

  1. Login to your server via ssh
  2. Edit “/etc/my.cnf” configuration file
  3. Add a new line at the end sql_mode=””
  4. Restart MySQL with “service mysqld restart”

That should be it. Now let’s confirm if the strict mode is indeed turned off.

  1. Log in to your MySQL server (assuming you are the root user), this can be done by simply issuing “mysql” command
  2. Then use command “SHOW VARIABLES LIKE ‘sql_mode’;
  3. The output should have a blank value

Published by Nishant

With over 9 years in the industry, initially started as a Linux administrator and transitioned into DevOps Engineer. I work with deployment and infrastructure automation as well as application release management processes.

Leave a comment

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

Hooman? *