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

Related articles

Testing your new template on a live website

Sooner or later it happens to all of us.
A live website needs to get a new template/layout but you do not want "the public" to see it yet.

Read the full article

Problems reaching the WebsiteBaker websites?

The problems with the DNS servers for WebsiteBaker.org are solved. You should have no problems reaching the websites anymore!

Some time ago the master DNS servers of WebsiteBaker were changed. Since that time it is difficult to reach the WebsiteBaker websites for some.

It was reported in the websitebaker forum, but if you cannot connect there you might have missed it.

Read the full article

Comments on this article - Note: comments will be reviewd by a moderator before publishing!

Eric
Apr 18, 2018 - 12:02:56

Thank you for publishing this trick. i just had to add pages to an older WebsiteBaker site, and got stuck on the error message. This helped me to bypass the problem and get going again. Cheers!

Add your comment

Your name:
Email address:
Website:
Your comment: