Was unable to update to 4.0, here's what finally worked for me

I’ve been updating Mautic since v2 and I guess I had some old problems with the DB schema.

I was on 3.3.1 and I was able to update to 3.3.4 no problem, but then all hell broke loose when I tried to update to 4.0

I was getting errors related to the oauth2_clients table foreign key constraints.

I wasted a lot of time trying to figure this out, finally what worked for me I created a brand new 3.3.4 install, allowed the installation wizard to create a new database.

I then dumped all of my existing Mautic database but I only dumped the data, I didn’t dump the DB schema at all.

I used this command to do it

mysqldump  --skip-triggers --no-create-info --add-locks --complete-insert db_mautic | sed -r 's/LOCK TABLES (`[^`]+`)/TRUNCATE TABLE \1; \n\nLOCK TABLES \1/g' | gzip > .//db_mautic-data-only.sql.gz

I then restored this sql dump on the clean schema created from the clean 3.3.4 install.

I then noticed that a few tables were missing, notably the migrations table had not been re-created, and my custom form tables were missing. So I repeated the whole process again, but this time before importing my “data-only” dump I imported those missing tables.

I also noticed that the leads table no longer had a googleplus column, and a custom column I had added was missing. So I made those changes first.

I also noticed the emails table had some records with a ‘headers’ value of empty, I changed those to the default of ‘’ since it’s a json column.

Finally I imported my data-only dump on top of the clean 3.3.4 schema and received no errors.

I then visited mauticurl/s/update/schema and it executed 28 migrations and populated my migrations table.

I was then able to update to 4.0 without getting any errors.

At first the dashboard was giving me a 500 error, but after running bin/console cache:clear then it worked and everything seems good so far

4 Likes

I’m struggling with an upgrade from 3.2.5 to 4.1.1 and the web upgrade kept failing / stuck on copying files.
Then I’ve tried to run it from CLI and this time it has completed the apply step, but on the --finish it’s giving me a similar SQL migration error as in your case (and many more people who have ran into the same problem)…
I was wondering if you wouldn’t mind sharing your empty database backup file with the upgraded 4.1.1 schema so that I can just restore my SQL from it and hopefully it’ll run with a fresh 4.1.1 install?

The reason I’m suggesting this might work is that a fresh 4.1.1 installation appears to be working with an old 3.2.5 database, except that it fails in many places due to the incompatible schema… However, I have no way of creating an empty 4.1.1 database because the fresh install is stuck on step1,1 - connects to SQL, creates the app/config/local.php file and then gets stuck in step1,1 never leaving it no matter what I try :frowning: I’m hoping that with an empty SQL db, compatible with 4.1.1 it might actually work - we don’t need to preserve any old data from 3.2.5 - we can start fresh, just need some 4.1.1 features.

Regards,
-Piotr

I had the same issue with migrations around oauth2. Tried few times bin/console m:u:a --finish and bin/console cache:clear and started to work, don’t know why at all. :man_shrugging: