Remove STRICT_TRANS_TABLES in SQL Mode for WHMCS


WHMCS, a billing and support software for the web hosts, may experience a conflict with default settings of certain MySQL or MariaDB server versions. This conflict prevents critical cron jobs from completing in WHMCS, causing automation settings to fail.

Check SQL Mode Settings in phpMyAdmin

To verify this, log into cPanel then access phpMyAdmin. Click on "Variables" on the right top side. Search for "SQL Mode" and see which entry is listed there. If you see:

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Then this indicates SQL Strict Mode is enabled, which prevents WHMCS from completing SQL queries with INSERT or UPDATE statements. We will need to modify this setting in order for new entries to be added to the database table.

Manually Remove STRICT_TRANS_TABLES in MySQL Configuration

To change the mode on the server, SSH with root access to your server. Edit the MySQL configuration by modifying the my.cnf file, located typically in the /etc folder. Add the following entry to your my.cnf file:

[mysqld]
sql-mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

If a [mysqld] section already exists, add the "sql-mode" property in the existing [mysqld] settings.

Restart the database.

  • In CentOS 6:
    service mysqld restart
  • In CentOS 7-8:
    systemctl restart mysqld

Verifying SQL Mode Updates

Verify the SQL Mode values by repeating "SQL Mode" search in phpMyAdmin > Variables. Refresh the page and verify the following value:

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

If making the change above doesn't work, there may be additional my.cnf files (such as /usr/my.cnf) that overwrite the primary MySQL configuration file.

In this case, if you find the sql-mode entry in that file, you can remove the line from the file. Then save the file and restart the mysqld service.

Get Support

If you are unsure or unable to make this change, you may want to contact support to have the Support Team perform these changes.

  • whmcs, mysql, mariadb
  • 14 Users Found This Useful
Was this answer helpful?

Related Articles

Video: Installing WHMCS

This video tutorial provides Step-by-Step instructions for installing WHMCS to your web hosting...

Video: How to Change Account Settings in WHMCS

This video tutorial provides Step-by-Step instructions for adjusting your account settings in...

Video: Setup Support Departments in WHMCS

This video tutorial provides Step-by-Step instructions for setting up the support departments in...

Video: Setup Administrative Roles in WHMCS

This video tutorial provides Step-by-Step instructions for setting up administrator roles within...

Video: Enable and Configure Fraud Protection in WHMCS

This video tutorial provides Step-by-Step instructions for enabling and configuring the fraud...