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

Mass Update Domain Registrar Using SQL Query

This article provides a guide to mass update domain registrar using SQL query in WHMCS. To bulk...

Slow WHMCS Domain Spinner WHOIS Lookup

Slow WHMCS domain lookups are a source of frustration for some clients, as this, in turn, leads...

Manually Create config.inc.php for phpMyAdmin

This article discusses the manual creation of config.inc.php for phpMyAdmin installation....

List of Banned Subdomain Prefixes

This article provides suggestions for the Banned Subdomain Prefixes field of WHMCS administrative...

Install MySQL on CentOS 7 Server

This tutorial provides step-by-step instructions for installing MySQL database server on CentOS...