Getting json_array error after upgrade to v5

Your software
My Mautic version is: 5.03
My PHP version is: 8.1.27
My Database type and version is: 8.0.28-0ubuntu0.20.04.3

Your problem
My problem is:
Half of mautic functionality not working.

These errors are showing in the log:
mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception: “Unknown column type “json_array” requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgotten to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.” at /home/username/domainname.com/mautic5/vendor/doctrine/dbal/src/Exception.php line 115 {“exception”:“[object] (Doctrine\DBAL\Exception(code: 0): Unknown column type "json_array" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgotten to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information. at /home/username/domainname.com/mautic5/vendor/doctrine/dbal/src/Exception.php:115)”} {“hostname”:“XXXXXXXXX”,“pid”:421955}

Steps I have tried to fix the problem:
Created this support post, because I have no idea how to fix this.

2 Likes

Went back and started over, installed NPM first, that seemed to be the problem.

1 Like

I have the same problem. Could you please elaborate a bit NPM install ? IS it a prerequisite ? Shouldn’t this been installed by default ?

Thank oyu,
Daniel

Yeah, it’s a prerequisite, but I don’t know why. I found this article by my host to set it up. Might be different for your webhost.

Hope that helps.

HEllo @crozilla - I have installed npm , tried “composer upgrade” still I am getting some errors :
"npm ERR! Cannot read property ‘@ckeditor/ckeditor5-adapter-ckfinder’ of undefined

npm ERR! A complete log of this run can be found in:
npm ERR! …/.npm/_logs/2024-04-27T05_31_46_540Z-debug.log"

How did you fixed it ?
What commands did you run ?

I use CLI, not composer, so I can’t really help you further, sorry.

I"m also having this issue. +1 following.

I am also having this problem. Can’t find a fix for it. Will try to install npm as mentioned by crozilla. I am trying to upgrade from 4.4.12 to 5.0.4.

I am not able to install NPM on my Mautic instance now so I’ll just post my error log and information here. Surely, installing NPM should not be a obvious fix for this issue anyways?

Your software
OS: CentOS 7
My Mautic version is: 4.4.12 and trying to upgrade to 5.0.4
My PHP version is: 8.0.30
My Database type and version is: mysql Ver 8.0.39 for Linux on x86_64 (MySQL Community Server - GPL)

Extra info
Before trying to upgrade from 4.4.12, I’ve also upgraded from 4.2.0 to 4.4.12 as well as upgrading MySQL from 5.7 to 8.0.39 due to EOL of MySQL, and upgraded PHP from PHP 7.4.x to PHP 8.0.30.

The Mautic instance is running behind a proxy that is not allowed to access github, thus I have downloaded the upgrade package locally.

Upgrade steps

[root@mautic-node production]# php bin/console mautic:update:apply --update-package=/home/viktor/5.0.4-update.zip
Are you sure you wish to update Mautic to the latest version? yes
Step    5 [----->----------------------] Clearing the cache

<warning>IMPORTANT: Run the same command again with --finish. For example 'php bin/console mautic:update:apply --finish'</warning>

[root@mautic-node production]# php bin/console mautic:update:apply --update-package=/home/viktor/5.0.4-update.zip --finish
Step    1 [>---------------------------] Migrating database schema...

An error occurred while updating the database. Check log for more details.

Mautic log

[2024-08-16T09:55:26.657631+02:00] console.CRITICAL: Error thrown while running command "doctrine:migrations:migrate --quiet --no-interaction". Message: "Unknown column type "json_array" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgotten to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information." {"exception":"[object] (Doctrine\\DBAL\\Exception(code: 0): Unknown column type \"json_array\" requested. Any Doctrine type that you use has to be registered with \\Doctrine\\DBAL\\Types\\Type::addType(). You can get a list of all the known types with \\Doctrine\\DBAL\\Types\\Type::getTypesMap(). If this error occurs during database introspection then you might have forgotten to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information. at /var/www/html/production/vendor/doctrine/dbal/src/Exception.php:115)","command":"doctrine:migrations:migrate --quiet --no-interaction","message":"Unknown column type \"json_array\" requested. Any Doctrine type that you use has to be registered with \\Doctrine\\DBAL\\Types\\Type::addType(). You can get a list of all the known types with \\Doctrine\\DBAL\\Types\\Type::getTypesMap(). If this error occurs during database introspection then you might have forgotten to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information."} {"hostname":"mautic-node","pid":66939}

So, what am I supposed to do to fix this problem? Switch to composer? Am I missing a PHP module? Am I doing the upgrade wrong?

Hoping to get some attention to this old post :slight_smile:

Best
Viktor

Hi,

you need to change the json arrays in your old system to match the new requirements.
Here is an example how you can do it:
Look for your json_array in your DB:

SELECT table_schema, table_name, column_name, column_comment
    -> FROM information_schema.columns
    -> WHERE column_comment LIKE '%(DC2Type:json_array)%';

The ones you found need to be changed. This is just an example, nut you need to follow this format for the right tables and columns:

ALTER TABLE reports MODIFY COLUMN settings longtext COLLATE utf8mb4_unicode_ci COMMENT '(DC2Type:json)';

Once you do it for all json fields, it will continue the migrations!

Joey

1 Like

Hi,

Thank you very much @joeyk ! :grinning: That got rid of the json array errors. Now I get other DB related errors though…

When running the upgrade command with --finish at the end I get a character set related error.

[2024-08-19T12:31:43.927125+02:00] console.CRITICAL: Error thrown while running command "doctrine:migrations:migrate --quiet --no-interaction". Message: "An exception occurred while executing a query: 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 'CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`' at line 1" {"exception":"[object] (Doctrine\\DBAL\\Exception\\SyntaxErrorException(code: 1064): An exception occurred while executing a query: 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 'CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`' at line 1 at /var/www/html/production/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:86)\n[previous exception] [object] (Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 1064): 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 'CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`' at line 1 at /var/www/html/production/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28)\n[previous exception] [object] (PDOException(code: 42000): 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 'CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`' at line 1 at /var/www/html/production/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:71)","command":"doctrine:migrations:migrate --quiet --no-interaction","message":"An exception occurred while executing a query: 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 'CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`' at line 1"} {"hostname":"svg-mautic1.mnemonic.no","pid":1945}

Is the character set of my DB outdated or something like that? I am not a MySQL wizard so I don’t understand what I am supposed to do with errors like this.

Best
Viktor