SQL bug affecting retrieval of all Blocks after update to Bolt 4.1

[crim3hound]

After updating from Bolt 4.0 to 4.1, there seems to be an SQL bug affecting retrieval and listing of all blocks from the database. Individual Blocks can still be added and edited, but the listing of existing Blocks cannot be browsed. The following error is thrown:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TEXT)) ASC) dctrn_result) dctrn_table' at line 1

Details

Question	Answer
Bolt Version	4.1
Install type	Composer install
BC Break	No
PHP version	7.4.4
Web server	Apache (version 2.4.35)
Database	MySQL (version 5.7.24)
Environment	Local Development

Steps to Reproduce

Bug summary

SQL error is thrown when executing SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id_0 FROM (SELECT b0_.id AS id_0, b0_.content_type AS content_type_1, b0_.status AS status_2, b0_.created_at AS created_at_3, b0_.modified_at AS modified_at_4, b0_.published_at AS published_at_5, b0_.depublished_at AS depublished_at_6 FROM bolt_content b0_ LEFT JOIN bolt_field b1_ ON b0_.id = b1_.content_id AND b1_.type IN ('generic', 'block', 'checkbox', 'collection', 'data', 'date', 'email', 'embed', 'file', 'filelist', 'hidden', 'html', 'image', 'imagelist', 'markdown', 'number', 'select', 'set', 'slug', 'templateselect', 'textarea', 'text', 'article', 'redactor') LEFT JOIN bolt_field_translation b2_ ON b1_.id = b2_.translatable_id WHERE b0_.content_type = ? AND b0_.status <> ? AND b1_.name = ? ORDER BY LOWER(CAST(b2_.value AS TEXT)) ASC) dctrn_result) dctrn_table.

Specifics

• The error occurs in the file \vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractMySQLDriver.php on line 98
• Bolt version affected is 4.1 (folllowing update from 4.0 via composer)

Steps to reproduce

• Install Bolt 4.0 via composer to local development environment.
• Add a few blocks to the database and access each block and all blocks from the admin console.
• Update to Bolt 4.1 via composer, clear cache, and try to access blocks again.

Expected result

Listing of all blocks should be accessible similar to other records added to the database.

Actual result

The following SQL error is thrown:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TEXT)) ASC) dctrn_result) dctrn_table' at line 1

[LordSimal]

I would guess this relates to #1977

[Wieter]

It's the same problem as in issue #1971: an outdated config/packages/doctrine.yaml
I would suggest to amend that file as described in that issue, and see if that solves things for you.

[crim3hound]

@LordSimal @Wieter thanks! I've updated my config/packages/doctrine.yaml file at line 38 as follows:

dql:
            string_functions:
                JSON_EXTRACT: Bolt\Doctrine\Functions\JsonExtract
                CAST: DoctrineExtensions\Query\Mysql\Cast

to

dql:
            string_functions:
                JSON_EXTRACT: Bolt\Doctrine\Functions\JsonExtract
                JSON_GET_TEXT: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonGetText
                CAST: Bolt\Doctrine\Query\Cast

That seems to have solved the issue.

[arthur-padua-menezes-martins]

I'm coming back with the discussion, another point of failure in the execution of the query in MySQL, now with Laravel/Eloquent framework, I'm looking for a forced conversion of the data type in a column of a view, where by default the MEDIUMTEXT DataType is generated and its conversion for TEXT / VARCHAR / others that I didn't need to test is generating an error.

DB::raw( 'CASE WHEN sl_solicitacoes.origem_solicitante = "portal" THEN (SELECT CAST(usuarios_portal.nome AS TEXT) FROM usuarios_portal WHERE usuarios_portal.id_portal = sl_solicitacoes.id_solicitante) WHEN sl_solicitacoes.origem_solicitante = "intranet" THEN CAST(CONCAT(pessoa_solicitante.nome_pessoa) AS TEXT) END AS nome_solicitante' )

The following SQL error is thrown:
PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TEXT)