Mautic 2.6.14 to Mautic 3 error: DB migration ALTER TABLE

[atz21]

Upgrading from Mautic 2.16.4 to Mautic 3.2.4
My PHP version is: 7.3.28
My MySQL/MariaDB version is: MySQL 5.7.32

Upgrading/installing via Mautic Dashboard on web (however, I also attempted the upgrade via the command line and received the same error)

These errors are showing in the Installer:

Steps I have tried to fix the problem: I have unsuccessfully tried following steps outlined in this forum post (it may be an issue with my technical skills as I know how to access the command line, but am not very experienced troubleshooting with that): Upgrade v2.16.0 to v3.0.2 - #15 by dennisameling

I’m looking for assistance/clarity on which commands could be run to help resolve the DB migration.

[thomastc]

I also ran into this error, and we’re not alone: Error updating Mautic 2.16.4 to 3.2

There is an issue report: SQL Syntax Error when Update to 3.1.0 · Issue #9180 · mautic/mautic · GitHub I just posted a workaround there that worked for me, but I’m not entirely certain that it’s the “right” way, so please do make a backup and thoroughly test things afterwards.

[atz21]

@thomastc great! I have a follow up question if you wouldn’t mind answering, it would be a huge help to me (apologies for my lack of skills/knowledge with shell):

Should I run the Mautic upgrade script, receive the error, and then run the SQL command from your comment? Or do I run the SQL command and the “bin/console doctrine:migrations:migrate:” command BEFORE running the Mautic upgrade script? If before, should I keep my PHP version at 7.1 (version which Mautic 2.6.14 works) or put that at 7.2+ (version which Mautic 3 needs) for running the scripts?

[thomastc]

Weren’t you already running PHP 7.3 though? In any case, the upgrade script will bail out if your PHP version is too low, before it does anything. So you need to do that first.

Then run:

php upgrade_v3.php

and get the SQL syntax error.

At this point you can (and probably should!) try the migration again:

php bin/console doctrine:migration:migrate

but for me, it failed with the same error.

So then I ran the two SQL statements:

ALTER TABLE email_stats ADD generated_sent_date DATE AS (CONCAT(YEAR(date_sent), "-", LPAD(MONTH(date_sent), 2, "0
"), "-", LPAD(DAY(date_sent), 2, "0"))) COMMENT '(DC2Type:generated)';
ALTER TABLE email_stats ADD INDEX `generated_sent_date_email_id`(generated_sent_date, email_id);

And finally tried the migrations again:

php bin/console doctrine:migration:migrate

This time it succeeded and I was running Mautic 3.2.4.

(There were a bunch of warnings about unknown registered migrations, going back to 2015 or something; I forget the exact message. To the best of my understanding, they are harmless.)

Remember to update your cron jobs to use bin/console instead of app/console at this point!

There are some steps after the database migration, which probably didn’t get executed in my case, most notably “restore user data”. Regardless, it seems I ended up with a working installation, including plugins and themes. Maybe the subsequent update to 3.3.3, which I performed immediately after, has taken care of that.

[atz21]

Yes, I’ve been running PHP 7.3 before preforming the upgrade.

I ran the upgrade script, received the error, tried the migrations again using the script you provided, and received the same error.

When trying to run the ALTER TABLE commands, I received a syntax error:

syntax error near unexpected token `(’

Below is a screenshot of the command and the resulting error. Can you tell where I’m going wrong?

[thomastc]

They’re SQL statements, not shell commands, so you need to run them in a mysql shell, not in the system’s bash shell. You can type

mysql -hHOSTNAME -uUSERNAME -p DATABASENAME

to connect, replacing HOSTNAME, USERNAME and DATABASENAME as appropriate for your system. It’ll prompt you for the password if the rest of the arguments are correct. Then run the two SQL statements, and type exit or press Ctrl+D to exit the mysql shell.

If you have access to phpMyAdmin or a similar tool, you can use that to run SQL commands as well.

[atz21]

@thomastc I think we’re getting close. Thank you for your patience and continued help! I was able to login to the MySQL database, but have received a new syntax error. I know next to nothing about MySQL syntax so I’m not sure where to start troubleshooting… does this error give you any insight into what is wrong?

[thomastc]

It looks like you missed a part of the query (MONTH is not there). Are you typing it all out by hand? Try copying and pasting instead

[atz21]

You’re right, I missed part of the command (wow that’s embarrassing… ask for help and be totally inept ). I am typing by hand-- long story, but I was spending more time messing up my connection (Control V doesn’t work) and googling about how to copy/paste in shell that it actually saved me time to type rather than continue researching.

Anyway-- IT WORKED!!! There was just one small thing I needed to change (I will write it out here in case someone else comes across this). When entering the MySQL command, I received the following error:

ERROR 1146 (41S02) : Table ‘$MyDatabaseName.email_stats’ doesn’t exist

I did a quick scan of the database table list via PHP MyAdmin. I saw that my tables all started with “maugk_”. I’m not sure if that is unique to my Mautic setup and have no idea why/how that text string is there. But I re-entered your two MySQL commands and changed “email_stats” to

maugk_email_stats

And then it worked! I too received a warning about previously executed migrations in the database that are not registered migrations. I continued anyway and the migration occurred (I did receive an error of “Can’t DROP ‘maugk_tweet_text_index’; check that column/key exists”. However I do not use Mautic to manage any Twitter/tweets so I’m not concerned with that piece. I did a quick check of my email stats, contacts, campaigns, etc. and it appears everything is working while running the new version of Mautic.

Thank you @thomastc! You rock!

[thomastc]

I did a quick scan of the database table list via PHP MyAdmin. I saw that my tables all started with “maugk_”. I’m not sure if that is unique to my Mautic setup and have no idea why/how that text string is there.

Good point, I forgot to mention that, glad you figured it out by yourself! The prefix is there as a (pretty thin) line of defense, in case some malicious script gains access to the database but isn’t clever enough to figure out what table prefix to use. It is randomly generated for each install.

I did receive an error of “Can’t DROP ‘maugk_tweet_text_index’; check that column/key exists”.

I didn’t see that error, but if the migration needed something gone that was already gone, it’s probably fine