Upgrade vom 3.3.5 to 4 (latest) error: Opening segments / Opening old Emails

[lanaprinzip]

My PHP version is* : 7.4.32
My MySQL/MariaDB version is* (delete as applicable): MySQL/MariaDB version: 10.3.36

I am Upgrading/installing via Command Line. It is not the live system, but a clone to test the upgrade procedure. So the original Installation is running fine on 3.3.5.

INSTALLER ERRORS:
The upgrade went through fine. No errors reported.
The cache was cleared.
I also checked for outstanding migrations BEFORE and AFTER the upgrade. An older upgrade had failed once and had outstanding migrations (before this upgrade), which was corrected. The installation was stable. After this latest migration: Also no outstanding operations marked red.

THE ERROR:
I can not open segments anymore.
I can open old emails, statistics are not shown, nor can the email be edited.
I can create new emails fine.

Steps I have tried to fix the problem : I ran through the standard mautic procedure: Mautic update failed - how to recover | Mautic

SERVER LOG:

2022-10-19 06:21:25	Error	178.115.58.0	500	GET /s/emails-graph-stats/53/0/2022-09-19/2022-10-19?_=1666153000349&mauticUserLastActive=290&mauticLastNotificationId= HTTP/1.0			54.9 K	SSL/TLS-Zugriff für Apache

|2022-10-19 06:21:30|Error|178.115.58.0|500|GET /s/emails/edit/53?_=1666153000350&mauticUserLastActive=295&mauticLastNotificationId= HTTP/1.0|

2022-10-19 06:21:22 Error 178.115.58.0 500 GET /s/segments?_=1666153000346&mauticUserLastActive=287&mauticLastNotificationId= HTTP/1.0

MAUTIC LOG:

[2022-10-19 05:47:18] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: “An exception occurred while executing ‘SELECT COUNT() AS dctrn_count FROM (SELECT DISTINCT id_10 FROM (SELECT m0_.is_published AS is_published_0, m0_.date_added AS date_added_1, m0_.created_by AS created_by_2, m0_.created_by_user AS created_by_user_3, m0_.date_modified AS date_modified_4, m0_.modified_by AS modified_by_5, m0_.modified_by_user AS modified_by_user_6, m0_.checked_out AS checked_out_7, m0_.checked_out_by AS checked_out_by_8, m0_.checked_out_by_user AS checked_out_by_user_9, m0_.id AS id_10, m0_.name AS name_11, m0_.description AS description_12, m0_.alias AS alias_13, m0_.public_name AS public_name_14, m0_.filters AS filters_15, m0_.is_global AS is_global_16, m0_.is_preference_center AS is_preference_center_17, m0_.last_built_date AS last_built_date_18 FROM mtclead_lists m0_ LEFT JOIN mtccategories m1_ ON m0_.category_id = m1_.id ORDER BY m0_.date_modified DESC) dctrn_result) dctrn_table’: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘m0_.last_built_date’ in ‘field list’" at /var/www/MASKED.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 79 {“exception”:"[object] (Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0): An exception occurred while executing 'SELECT COUNT() AS dctrn_count FROM (SELECT DISTINCT id_10 FROM (SELECT m0_.is_published AS is_published_0, m0_.date_added AS date_added_1, m0_.created_by AS created_by_2, m0_.created_by_user AS created_by_user_3, m0_.date_modified AS date_modified_4, m0_.modified_by AS modified_by_5, m0_.modified_by_user AS modified_by_user_6, m0_.checked_out AS checked_out_7, m0_.checked_out_by AS checked_out_by_8, m0_.checked_out_by_user AS checked_out_by_user_9, m0_.id AS id_10, m0_.name AS name_11, m0_.description AS description_12, m0_.alias AS alias_13, m0_.public_name AS public_name_14, m0_.filters AS filters_15, m0_.is_global AS is_global_16, m0_.is_preference_center AS is_preference_center_17, m0_.last_built_date AS last_built_date_18 FROM mtclead_lists m0_ LEFT JOIN mtccategories m1_ ON m0_.category_id = m1_.id ORDER BY m0_.date_modified DESC) dctrn_result) dctrn_table’:\n\nSQLSTATE[42S22]: Column not found: 1054 Unknown column ‘m0_.last_built_date’ in ‘field list’ at /var/www/MASKED.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:79, Doctrine\DBAL\Driver\PDO\Exception(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘m0_.last_built_date’ in ‘field list’ at /var/www/MASKED.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘m0_.last_built_date’ in ‘field list’ at /var/www/vhosts/MASKED.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141)”} {“hostname”:“MASKED.com”,“pid”:211928}

Any help is highly appreciated.
Thank you,
Matthias

[mzagmajster]

I would start with checking migrations status again:

php bin/console doctrine:migrations:status

and comparing the entities with database schema with

php bin/console doctrine:schema:update --dump-sql

What is the output of those commands ?

[lanaprinzip]

Hi mzagmajster,
thank you for your reply, which helped me sort the issue:

• The migration:status did not show outstanding migrations.
• I had previously run the schema check using the webend: example.com/s/update/schema which said, that all updates had been done.
• Now I used php bin/console doctrine:schema:update --dump-sql and saw about 30 outstanding tasks
• I updated the schema again from the command line, now it works.

Thank you so much! I would have not thought of that.
The strange thing is, that the schema check on the webend reported it wrongly. The command line check reported it correctly.

Best,
Matthias

[mzagmajster]

I would always prefer CLI way of upgrading mautic over web ui. There are many additional issues you can face just because you go down Web UI path.

[lanaprinzip]

I actually upgraded using the CLI, later on, I did use the web interface (when troubleshooting) to check the schema update (as it was described like this on the Mautic Troubleshooting site). But you are right of course, the web interface for upgrading does not really work well…