How to Disable MySQL Strict Mode on a cPanel Server

Some older PHP applications were written for MySQL servers that quietly accepted bad data, such as an empty string in a number column. On a modern MySQL or MariaDB server they fail with errors like these:

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'count' at row 1
SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'title' at row 1

That is strict mode at work. This guide shows how to turn it off on a cPanel server, and how to do it with the least side effects.

Quick answer

1. Check the current mode: mysql -e 'SELECT @@GLOBAL.sql_mode;'

2. In /etc/my.cnf, under [mysqld], set sql_mode to the same list without STRICT_TRANS_TABLES (and STRICT_ALL_TABLES, if present). For example, on MariaDB:

   [mysqld]
   sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
   

3. Restart the database: /scripts/restartsrv_mysql

4. Check again with the command from step 1.

What you need: root SSH access to the cPanel server.

Before you change it: is there a better fix?

Strict mode protects your data. Without it, the server silently changes bad values instead of rejecting them: it cuts long text short, and turns invalid numbers into 0. MySQL Turning it off for the whole server affects every website on it.

Better options, if you can:

• Update the application. Most current versions of popular apps work in strict mode.

• Turn it off for that one application only. If you can edit the app's database connection code, have it run this right after connecting:

  SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
  

  The session value only affects that application's connections. MariaDB

Change the server-wide setting only when neither is possible.

Step 1: Check the current mode

mysql -e 'SELECT @@GLOBAL.sql_mode;'

The defaults are different for each database: MySQL MariaDB

STRICT_TRANS_TABLES (and the stricter STRICT_ALL_TABLES) is what turns strict mode on. Write down the value your server shows; you need it in the next step, and to undo the change.

To see which database you run: mysql -V.

Step 2: Edit /etc/my.cnf

1. Make a copy of the file first:

   cp -a /etc/my.cnf /etc/my.cnf.bak
   

2. Open it with vi /etc/my.cnf.

3. Find the [mysqld] section. If it already has a sql_mode (or sql-mode) line, edit that line; otherwise add one below [mysqld].

4. Set it to the value from Step 1, minus STRICT_TRANS_TABLES and STRICT_ALL_TABLES.

For MySQL 8.4, that is:

[mysqld]
sql_mode="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

For MariaDB:

[mysqld]
sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Do not copy NO_AUTO_CREATE_USER into a MySQL 8 configuration: MySQL 8 no longer has that mode, and an unknown mode stops the server from starting.

Why not an empty value? sql_mode="" also clears strict mode, but it removes all the other safety checks too. MySQL Keep the rest unless the application needs otherwise. Old applications also often fail on ONLY_FULL_GROUP_BY (errors mentioning GROUP BY); remove that mode as well if you see those.

Step 3: Restart the database

/scripts/restartsrv_mysql

This restarts MySQL or MariaDB the cPanel way. Websites lose their database connection for a few seconds.

If it fails to start, there is a typo in /etc/my.cnf. Put the copy back with cp -a /etc/my.cnf.bak /etc/my.cnf, restart again, and retry the edit.

Step 4: Check it worked

mysql -e 'SELECT @@GLOBAL.sql_mode;'

The output should no longer contain STRICT_TRANS_TABLES. Then reload the application and repeat what caused the error.

If the value did not change, another configuration file sets sql_mode after /etc/my.cnf. Search for it:

grep -rn 'sql.mode' /etc/my.cnf /etc/my.cnf.d/ /etc/mysql/ 2>/dev/null

Undo

To turn strict mode back on, restore the copy and restart:

cp -a /etc/my.cnf.bak /etc/my.cnf
/scripts/restartsrv_mysql