As usual, upgrade (to 4.2.2 this time) effed up my installation

Product Support Mautic 4 Install/Upgrade Support
1

Your software
My PHP version is : 7.4.29
My MySQL/MariaDB version is (delete as applicable): 10.3.34-MariaDB, pdo_mysql

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

These errors are showing in the installer :

These errors are showing in the Mautic log :

Your problem
My problem is :

Upgrade looks mostly successful, but on Contact pages I now get:

The site is currently offline due to encountering an error. If the problem persists, please contact the system administrator.

System administrators, check server logs for errors.

Steps I have tried to fix the problem : Nothing yet, just getting started on this - where are “the server logs”? Sunday 11 pm. I had planned to send out a mailing tomorrow, now not going to happen.

The file error_log only has old stuff from 2021. There are several very dodgy looking files in the root of my installation folder, like phpstan-baseline.neon (4,675 Kb). Where are the logs I am supposed to check?

Does anyone recognize the problem? Possible solution?

1 Like
2

Hello @modifiedcontent,

To localize log files, you can follow this steps : Troubleshooting | Mautic
Or :

  1. Go to parameters menu
  2. System Info
  3. Journal (you can see your error log on this page)

Pierre

3

Clear your cache

4

@EJL, I had already tried that.

@pierre_a , thanks for the pointers. The log in the Mautic admin gives me only this error and only after I tried to access a contact again:

[2022-05-20 21:14:21] mautic.ERROR: Doctrine\DBAL\Exception\InvalidFieldNameException: An exception occurred while executing ‘SELECT t0.id AS id_1, t0.tag AS tag_2, t0.description AS description_3 FROM mtc_lead_tags t0 INNER JOIN mtc_lead_tags_xref ON t0.id = mtc_lead_tags_xref.tag_id WHERE mtc_lead_tags_xref.lead_id = ? ORDER BY t0.tag ASC’ with params [“3110”]: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘t0.description’ in ‘field list’ - in file /home/myusername/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php - at line 79

5

I am now going to fix my failed Mautic upgrade by just going over the crappy upgrade process again and hammering the same commands that you’re supposed to use:

php bin/console doctrine:migration:migrate goes through with the following error message:

  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error i
  n your SQL syntax; check the manual that corresponds to your MariaDB server
   version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_S
  EARCH`' at line 1

php bin/console doctrine:schema:update --force goes through with the following errors:

  SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a for
  eign key constraint 'FK_818C32519EB6921'

php bin/console cache:clear goes through without errors, but nothing is fixed.

Trying to run the latest update again. After php bin/console mautic:update:apply --finish I get this error:

Step    1 [>---------------------------] Migrating database schema...

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

My Mautic installation is now completely dead/unreachable. From the daily log in the Mautic installation directory /var/logs:

[2022-05-21 14:42:52] console.CRITICAL: Error thrown while running command "'doctrine:migration:migrate'". Message: "An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1" {"exception":"[object] (Doctrine\\DBAL\\Exception\\SyntaxErrorException(code: 0): An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':\n\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:98, Doctrine\\DBAL\\Driver\\PDO\\Exception(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 MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, 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 MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:132)","command":"'doctrine:migration:migrate'","message":"An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':\n\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1"} []
[2022-05-21 14:47:24] console.CRITICAL: Error thrown while running command "doctrine:schema:update --force". Message: "An exception occurred while executing 'ALTER TABLE mtc_oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED DEFAULT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE scope scope VARCHAR(191) DEFAULT NULL':  SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921'" {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'ALTER TABLE mtc_oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED DEFAULT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE scope scope VARCHAR(191) DEFAULT NULL':\n\nSQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921' at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: HY000): SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921' at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921' at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:132)","command":"doctrine:schema:update --force","message":"An exception occurred while executing 'ALTER TABLE mtc_oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED DEFAULT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE scope scope VARCHAR(191) DEFAULT NULL':\n\nSQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921'"} []
[2022-05-21 14:53:29] console.CRITICAL: Error thrown while running command "doctrine:migrations:migrate --quiet --no-interaction". Message: "An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1" {"exception":"[object] (Doctrine\\DBAL\\Exception\\SyntaxErrorException(code: 0): An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':\n\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:98, Doctrine\\DBAL\\Driver\\PDO\\Exception(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 MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, 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 MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:132)","command":"doctrine:migrations:migrate --quiet --no-interaction","message":"An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':\n\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1"} []

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDEX IDX_SEARCH TO mtc_IDX_SEARCH’ at line 1" {“exception”:"[object] (Doctrine\DBAL\Exception\SyntaxErrorException(code: 0)

Same/similar issue here?

Apparently a way to fix it is to manually drop the problem-causing table from the database, run upgrade again and then presumably (?) manually recreate it later.

What/where is the table I should drop? I don’t see IDX_SEARCH, with or without prefix, in my database (via PHPMyAdmin).

The SQL the upgrade script is trying to run is apparently this:

ALTER TABLE mtc_lead_event_log CHANGE id id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, CHANGE lead_id lead_id BIGINT UNSIGNED DEFAULT NULL, CHANGE user_name user_name VARCHAR(191) DEFAULT NULL, CHANGE bundle bundle VARCHAR(191) DEFAULT NULL, CHANGE object object VARCHAR(191) DEFAULT NULL, CHANGE action action VARCHAR(191) DEFAULT NULL;
     DROP INDEX idx_search ON mtc_lead_event_log;
     CREATE INDEX mtc_IDX_SEARCH ON mtc_lead_event_log (bundle, object, action, object_id, date_added);

If I try to run that directly via phpmyadmin I get this error message:

ALTER TABLE mtc_lead_event_log CHANGE id id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, CHANGE lead_id lead_id BIGINT UNSIGNED DEFAULT NULL, CHANGE user_name user_name VARCHAR(191) DEFAULT NULL, CHANGE bundle bundle VARCHAR(191) DEFAULT NULL, CHANGE object object VARCHAR(191) DEFAULT NULL, CHANGE action action VARCHAR(191) DEFAULT NULL

And:

MySQL said: Documentation

#1832 - Cannot change column 'lead_id': used in a foreign key constraint 'FK_78B7E97955458D'

Clearing the cache still did not fix anything. Mautic installation still completely dead with 403.

6

Upgrade failed on this SQL from the upgrade process:

‘… CHANGE id id BIGINT …’ looks odd. Should that be ‘… CHANGE id BIGINT …’ instead? Same with ‘lead_id lead_id’ and ‘bundle bundle’ and ‘action action’? Or is that all correct SQL?

How can I get beyond this error and restore my installation? Should I delete the table mtc_lead_event_log or will that cascade into other problems?

7

Can I just upgrade to 4.3.1 or will that make the mess worse?

8

I’m having similar problems, but your problem might be easier to fix. Maybe try using phpmyAdmin (or other MySQL client) to manually change the current values of your “mtc_lead_event_log” table to the ones in your error message.

ALTER TABLE mtc_lead_event_log
CHANGE id id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, 
CHANGE lead_id lead_id BIGINT UNSIGNED DEFAULT NULL, 
CHANGE user_name user_name VARCHAR(191) DEFAULT NULL, 
CHANGE bundle bundle VARCHAR(191) DEFAULT NULL, 
CHANGE object object VARCHAR(191) DEFAULT NULL, 
CHANGE action action VARCHAR(191) DEFAULT NULL;

(My code has the same double names, so I think that’s just how it’s written, not sure, though.)

1 Like
9

Thank you for the suggestion @crozilla! At least something to try.

I got this response:

MySQL returned an empty result set (i.e. zero rows). (Query took 1.0548 seconds.)

Not sure what that means. The table mtc_lead_tags seems to be missing - ‘Column not found’. Could I create it manually in some way or skip that step from the upgrade process?

My installation is still completely dead. Tried upgrading to 4.3.1 next…

Upgrade seems to go through OK. Then on the apply --finish command, still this error:

Step    1 [>---------------------------] Migrating database schema...

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

Installation still completely dead with error 403.

I’ll leave this thread unresolved and open a new one about failed 4.3.1 installation.

10

I think you can resolve the error 403 by commenting out some lines in your .htaccess file. @modifiedcontent

# Apache 2.4+
<IfModule authz_core_module>
    # Deny access via HTTP requests to all PHP files.
    # <FilesMatch "\.php$">
    #    Require all denied
    # </FilesMatch>

    # Deny access via HTTP requests to composer files.
    <FilesMatch "^(composer\.json|composer\.lock)$">
        Require all denied
    </FilesMatch>

    # Except those allowed below. 
    #<If "%{REQUEST_URI} =~ m#^/(index|index_dev|upgrade/upgrade)\.php#"> <FilesMatch "^(index|index_dev|filemanager|upgrade).php$"> Require all granted </FilesMatch> #</If>
</IfModule>

Try replacing the existing lines in your .htaccess file (in your Mautic root directory) with these lines.

1 Like
11

Thanks again @crozilla. I couldn’t make editing the lines as you described worked. I temporarily removed this entire block:

# Apache 2.4+
<IfModule authz_core_module>
    # Deny access via HTTP requests to all PHP files.
    <FilesMatch "\.php$">
        Require all denied
    </FilesMatch>

    # Deny access via HTTP requests to composer files.
    <FilesMatch "^(composer\.json|composer\.lock)$">
        Require all denied
    </FilesMatch>

    # Except those allowed below.
    <If "%{REQUEST_URI} =~ m#^/(index|index_dev|upgrade/upgrade)\.php#">
        Require all granted
    </If>
</IfModule>

# Fallback for Apache < 2.4
<IfModule !authz_core_module>
    # Deny access via HTTP requests to all PHP files.
    <FilesMatch "\.php$">
        Order deny,allow
        Deny from all
    </FilesMatch>

    # Deny access via HTTP requests to composer files
    <FilesMatch "^(composer\.json|composer\.lock)$">
        Order deny,allow
        Deny from all
    </FilesMatch>

    # Except those allowed below.
    <If "%{REQUEST_URI} =~ m#^/(index|index_dev|upgrade/upgrade)\.php#">
        Order allow,deny
        Allow from all
    </If>
</IfModule>

I now have access to Mautic admin. I still get the same error on individual Contacts as before. I am going to try if removing certain custom fields fixes the database issues.

But removing these lines from .htaccess is probably not a good idea; they must be there for a reason? We’re creating security problems here?

Is there anyone on this community forum who knows how the system works and can help us fix these issues?

12

The blocking part is this bit:

# Deny access via HTTP requests to all PHP files.
    <FilesMatch "\.php$">
        Require all denied
    </FilesMatch>

This part is supposed to plug security holes, I believe.

# Except those allowed below. 
    #<If "%{REQUEST_URI} =~ m#^/(index|index_dev|upgrade/upgrade)\.php#"> <FilesMatch "^(index|index_dev|filemanager|upgrade).php$"> Require all granted </FilesMatch> #</If>

Sadly, I can’t find the link in the forums where I stole this from…EDIT: Found it: [upgrade] 4.1.2 to 4.2.0 issue with .htaccess · Issue #10913 · mautic/mautic · GitHub

13

It’s supposed to work together in clever/mysterious ways. I’m pretty sure you can’t just remove lines from this. OK for troubleshooting, but I guess I’ll have to put them back when I have a real solution to the database problem.

What does lead_event_log do? Is there any way to update/restore/change that table, so the upgrade script doesn’t choke on it again?

Apparently you can use this to clean up “something” involving lead_event_log:

php bin/console mautic:maintenance:cleanup --days-old=365 --dry-run

Without ‘–dry-run’ it executes the actual cleaning and testing again with it looks like old entries have been remove, so lead_event_log has been edited in a way that pleases Mautic?

Contacts still produce the same errors. doctrine:migration:migrate still produces the same error below. Cache clearing still does nothing.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your S
  QL syntax; check the manual that corresponds to your MariaDB server version for the
   right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1

doctrine:schema:update produces this error:

An exception occurred while executing 'ALTER TABLE mtc_oauth2_accesstokens CHANGE c
  lient_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED DEFAULT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE scope scope VARCHAR(191)
  DEFAULT NULL':

  SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a fo
  reign key constraint 'FK_818C32519EB6921'

Any other commands I could try? Trying to find a way to fix whatever in the database is conflicting with the upgrade script.

What does this mean?

Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921' at /home/myuser/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: HY000): SQLSTATE[HY000]:

Apparently you can circumvent this by (temporarily) disabling foreign key checks in MySQL/MariaDB, with something like this:

ALTER TABLE table_name DISABLE KEYS;

Or

SET FOREIGN_KEY_CHECKS=0;

Or

SET foreign_key_checks = 0;
-- run some queries
SET foreign_key_checks = 1;

Applied to one of the offending pieces of SQL:

SET foreign_key_checks = 0;
ALTER TABLE mtc_oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL,
CHANGE user_id user_id INT UNSIGNED DEFAULT NULL,
CHANGE token token VARCHAR(191) NOT NULL,
CHANGE scope scope VARCHAR(191) DEFAULT NULL;
SET foreign_key_checks = 1

This went through, but now when I try doctrine:schema:update --force I get another error:

LATEST FOREIGN KEY ERROR
------------------------
2022-05-28 11:08:55 0x7fa4040f2700 Error in foreign key constraint of table `mydb_mautic`.`mtc_oauth2_accesstokens`:
 FOREIGN KEY (client_id) REFERENCES mtc_oauth2_clients (id) ON DELETE CASCADE:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to https://mariadb.com/kb/en/library/foreign-keys/ for correct foreign key definition.
Alter  table `mydb_mautic`.`mtc_oauth2_accesstokens` with foreign key constraint failed. Field type or character set for column 'client_id' does not mach referenced column 'id' near ' FOREIGN KEY (client_id) REFERENCES mtc_oauth2_clients (id) ON DELETE CASCADE'.

Trying to clear the cache I now get this error:

Unable to write in the "/home/myuser/public_html/mrktng/var/cache/prod" directory
  .

After messing with ownership/permission and endless cache clearing and --force whatever, the upgrade finally went through and individual Contact pages work again - but only if I remove all those lines from .htaccess. I started another thread for that issue.