MySQL strict mode > my module will not...

More and more hosting companies are configuring their MySQL servers in strict mode (the default for MySQL 5.7).
Using this strict mode will help security a lot because it will not allow database actions that are not complete or badly coded.

Unfortunately, many (older) modules for WebsiteBaker (and even older WebsiteBaker versions) rely on MySQL not being strict.
The best solution is to ask the module developer to fix this and make the module work on strict MySQL, but in real life the module developer might not be as fast as you would like them to, or not maintaining the module at all anymore.

The problem

Many tables in the WebsiteBaker database are defined with default values that should be used if a new table row is created.
For some field types it is not possible to set this default value.

One problem with strict mode is that the module installer tries to create tables with default content when it is not allowed.
Strict mode will complain, and not install the table at all!

A second problem is that new records are created with no data for fields that don't have a default value but have the setting that the field should not be "NULL" (undefined)
Also in this case strict mode will complain and not insert a new row in the table.

The best solution

The best solution is to make both the installer as well as every INSERT mysql command "Strict safe".
Unfortunately, this will take time and does not help you when your customer needs to add content to their website.

The trick

A simple trick will disable this strict mode for your website.
We can simply send an instruction to the MySQL database to change the mode.

Every page (admin or frontend) will start with loading the config.php.
When that is finished, there is a database connection ready to be used.

After the last line (calling /framework/initialize.php) we can send the command to the database to change the MySQL mode by adding this line:

$database->query("SET sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';");

This will set the mode to the same as what was default in older MySQL versions and should allow you to install or add stuff in incompatible modules.

In the meantime, lets all work on this problem and make modules MySQL strict mode safe.

This article is tagged with:
MySQL Tricks Modules