Some Content Management systems like Typo, Contao, Contenido or other systems have problems with the since MySQL 5.1 available Strict Mode. This has been the default setting since version 5.7.
So that one can also use these CMS systems there is still the possibility to switch off the strict mode on the server side. Of course, this is only conditionally recommended.
What does the Strict mode do?
MySQL/MariaDB's strict mode controls how invalid or missing values are handled in data change queries, including INSERT, UPDATE, and CREATE declarations.
Invalid or missing Data lead to warnings or errors when the SQL query is executed. If the Scrict mode is switched off, the invalid queries or missing values are adjusted and a simple warning is issued. This can lead to unexpected results.
An example would be a character limit. If the Strict mode is switched off, excess characters will be cut off to make it fit.
Why does this only occur with some CMS systems?
One can only speculate. Probably the code is so extensive that no one can figure it out. On a content management system work a lot of people, sometimes they bring individual functions that are then no longer looked at for years. As long as no serious problems occur it is not so bad.
With a system like whmcs, which is also affected, the situation is quite different. If important data is truncated, incorrect assignments will occur. This can be problematic in the case of a settlement.
Where can these instructions be applied?
The instructions refer to Linux systems with MySQL 5.6/5.7 or MariaDB 10.1 and higher where Strict Mode is active.
Root access is required to make the changes. Our instructions always refer to Centos Linux. With other distributions, the configuration files may be located in other directories.
Step 1: Create backups
When editing files, you should always make a backup. It's far too easy to delete something unintentionally.
cp -a /etc/my.cnf{,.strict.bak}
The command creates a my.cnf.strict.bak file from the my.cnf in the same directory.
Step 2: Disable MySQL Strict Mode
The file normally does not contain sql_mode=... so the following line should be added to the section [mysqld] :
sql_mode=""
Step 3: Restart MySQL Service
After editing the file, the service must be restarted to apply the changes.
systemctl restart mariadb
After the service has been restarted you can check the setting by executing the following command. Here as an example with the password on a Plesk server. Alternatively with root and the password.
mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "SELECT @@sql_mode;"
The following return should then appear:
+————+
| @@sql_mode |
+————+
| |
+————+
