Bolt: Issues with MySQL in Strict Mode

[michaelhthomas]

The "template" column in "bolt_pages" table is set to a type of varchar(256), and therefore returns an error when MySQL is set to strict mode.

Details

• Relevant Bolt Version: 3.4
• Install type: Tarball
• PHP version: 7.2
• Used web server: Apache version 2.4.27 (Ubuntu)

Reproduction

Bug summary

The MySql database for Bolt uses a type of varchar(256) for the "template" column in the "bolt_pages" table, which is a common trick used to create a SQL column that can vary freely in length (Provided it does not exceed the maximum of 65,535 bytes, which is shared among all the rows of the table). This is a clever solution, however it does not work properly when MySQL's strict mode is enabled (which it is by default in MySQL 14.14). This is an issue for users on shared hosts, where it may not be possible to disable strict mode, and is overall not a good practice.

This really ends up being two problems in one however, as not only is this a bad practice that is repeated multiple times in bolt, but it became visible to me because of another, smaller, issue. This smaller bug is first visible when prefilling the database (/bolt/prefill?force=1). This script unintentionally prefills the aforementioned "template" column with a large quantity of Lorem Ipsum, instead of the expected value, a template filename. This Lorem Ipsum string reaches a total of 1930 characters, much more than the varchar() limit of 256 characters. This is what brought my attention to the issue with strict mode.

Specifics

• URL: /bolt/prefill?force=1
• Bolt Version: 3.4.2
• Installation Method: Tarball
• Browser Agnostic
• Full Error Message (on my system): https://pastebin.com/raw/ntYDrZ68

Steps to reproduce

• Download Bolt & Run Setup Script
• Set database type to MySQL
• Make sure MySQL is set to "strict mode"
• Install Bolt
• Click "add some Lorem Ipsum dummy content."
• Error!

Expected result

Dummy content is successfully inserted, template is set to a path.

Actual result

SQL error due to both a bug in the sample content generator as well as simply bad practice.

[bobdenotter]

Thanks for the detailed issue report, @linux-overuser.

The templateselect shouldn't be prefilled, so that's a bug we want fixed at least.

"not a good practice" … "bad practice" … "bad practice" …

While you might have a point there, I'm not sure if it's something that's feasible to fix. Bolt uses Doctrine, and it required (and still does) quite a bit of work to make it work consistently across MySQL, Postgres and Sqlite.

[michaelhthomas]

@bobdenotter
You raise a fair point that it is very difficult to implement a database strategy that works consistently across multiple different database types. I really wasn't trying to call you out for a bad practice, I was simply alerting you to the fact that the folks at MySQL aren't so fond of it. I think that in this case, it is probably the best decision, keeping in mind that if the string has the reasonable chance of exceeding 256 characters, then a different field type should be used.

Thank you for your acceptance and I hope that you continue to develop such an outstanding piece of software.

[bobdenotter]

Hi @linux-overuser,

In #7244 I've submitted a fix for the actual bug you encountered. About the way the columns are defined, that's not as easy to fix. In Bolt 4, we'll do some major work on this part of the code, and we might improve this, at that time.