Mautic 4.20 throws 500 internal error in asset and form page/tab

Your software
My PHP version is : 7.2.34
My MySQL version is: 5.7.37-cll-lve

Updating/Installing Errors
I am (delete as applicable): Updating
Upgrading/installing via (delete as applicable) : Command Line

These errors are showing in the Mautic :
500 internal error

These errors are showing in the Mautic log :
[2022-03-24 11:00:47] 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_.form_attr AS form_attr_14, m0_.cached_html AS cached_html_15, m0_.post_action AS post_action_16, m0_.post_action_property AS post_action_property_17, m0_.publish_up AS publish_up_18, m0_.publish_down AS publish_down_19, m0_.template AS template_20, m0_.in_kiosk_mode AS in_kiosk_mode_21, m0_.render_style AS render_style_22, m0_.form_type AS form_type_23, m0_.no_index AS no_index_24, m0_.progressive_profiling_limit AS progressive_profiling_limit_25, (SELECT count(m1_.id) AS sclr_27 FROM mautic_form_submissions m1_ WHERE m1_.form_id = m0_.id) AS sclr_26 FROM mautic_forms m0_ LEFT JOIN mautic_categories m2_ ON m0_.category_id = m2_.id ORDER BY m0_.date_modified DESC) dctrn_result) dctrn_table': SQLSTATE[42S22]: Column not found: 1054 Unknown column 'm0_.no_index' in 'field list'" at /home/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 79 {"exception":"[object] (Doctrine\\DBAL\\Exception\

Your problem
My problem is :
In Mautic 4.20 version, I can see there are still some 500 internal error even though I did clear cache via terminal, after updating from 2.14 / 2.16.
Some instances that are not working include:

  • Viewing Assets (brochures, documents), lead to 500 internal error when trying to view asset in full path
  • Cannot click Forms tab to access Forms page

Steps I have tried to fix the problem :

  • Clear cache in var/cache and also via command line.
1 Like

At first glance it looks like you have not executed all permissions. Database index is missing. Please check status of migrations and execute potential missing migrations.

Please check status of migrations and execute potential missing migrations.

@mzagmajster What would be some recommended steps to do this? Is this done from the console with a command?

Yes, do database backup before you go into this:

php bin/console doctrine:migrations:status
php bin/console doctrine:migrations:migrate

@mzagmajster which database to backup? I assume you are referring to Mautic 4.0 database. I have tried migrate doctorine command couple of times before until only ended up with the error message .

Yes backup mauitc database (on mysql server) before you start any migration. I have written one answer how & why to go about errors during migration:

I would try something similar in your situation.

@mzagmajster , thanks for that.

I did ran cli command you suggested: php bin/console doctrine:migrations:status and php bin/console doctrine:migrations:migrate. Attached is screenshot confirming the status and migrate result


It seems that the DROP index error showed up, not sure how I can fix the index error issue.

It would be beneficial for non-programmers/non tech-literate marketers to have some sort of documentation on resolving the issue in Mautic docs that don’t require database skills (as Mautic can be used by marketers as well).

Thanks in advance.

Hi,

the thing is that you are attempting to fix the database, at some point you are bound to run in a situation where some technical expertise. If you run mautic for business I would strongly suggest you find company / individual that can take care of technical aspect of mautic and ensures it runs smoothly for you - then you can focus on .your field of expertise.

I can offer such services (PM me for more details), but I am by no means the only one :).

Now about the situation on the screenshots… as described on the link that I pasted above there is a procedure to follow here (I did and I rescued the database).

Is it possible that migration that contains drop statement does not need to be executed. In this case you just need to insert record to migrations table.

What you have to do now is:

  • figure out which migration is failing
  • check the file corresponding migration file (in migrations folder)
  • check the database for the index script is trying to drop
  • if migration does not have to be executed then insert record to migrations table

Hi Matic,
Just letting you know I have transferred payment via Paypal as discussed.
I am not sure if you have additional resources or notes on how you did in terms of manually re-inserting records to migration table as I saw you did pretty very quick so I may have missed details out.

Best regards
Marty from Duplex

Hi, I do not really have notes sorry :). and exact SQL statements would not really help the others. Basically what we did is bring database schema up to date which in your case required to manually check for differences in schema and migrations and then based on that execute SQL statements. We partially followed this procedure: MySQL error upgrading to 4.2 - #6 by mzagmajster

1 Like

No worries Matic thanks for the kind update and clarification, muchly appreciated.