Updating Staging Site

Product Support
1

My Mautic version is: Current
My PHP version is: 7.4
My Database type and version is: Maria DB 10.7.8

Your problem
My problem is: Difficulty quickly updating staging site

I manage a live Mautic site for a financial services company on a Cloudways managed server on Digital Ocean. We keep a staging site of this for testing as it uses a virtual SMTP (Mailosaur) so we can do complete test sends with real segment lists and not worry about sending to the actual customer.

The problem is that when I try to ONLY update changed tables in the database, it never works. Doesn’t matter if I do it through Cloudways staging GUI, export tables and import into the staging via MySQL or PHP MyAdmin. Fails every time. The ONLY way it works if if I do a COMPLETE staging overwrite of all database files and web files. That works…BUT it takes FOREVER…like 2 hours because the site and database files are 43GB and growing.

Is there a reliable way to update a Mautic staging site to match the live site that is quick and reliable? I’ve fought with this problem for a couple years now and have tried all sorts of methods to update and it never works unless everything is updated. I’ve worked with Cloudways support on this and they are fantastic but they cannot find a valid reason why it’s failing…as many other websites and apps that I maintain on their servers work perfectly while using their staging tools and/or minimal exports and imports between the sites. So Mautic is the only app that has this issue out of literally half a dozen app types (all PHP based).

So it seems it’s something specific to Mautic considering the problem hasn’t occurred anywhere else on my three servers. And yes I’ve tried completely deleting my staging site and starting over with a fresh clone…then doing the partial updates…same problem. They fail and the staging site is then missing campaigns, segments, emails etc.

Thanks for any suggestions.
Chris Blair

2

Hi, this sounds interesting.

A couple of very basic guidelines first:

  • make sure that mariaDB version, php version and all other dependencies are exactly the same on both servers
  • use git to sync files between staging/production
  • note that you will still have to do something about media, themes folder and app/config folder (probably just local.php file). Those folders are not necessary included in git (but that is about it, when it comes to files on the disk)

For the source of mautic, there is really no excuse to not do it correctly and it should be simple enough. I would suggest for the source and other files inside mautic folder to really use git & and for the files that are not under git, rsync or something similar. That should take care of files, without exceptions.

Now for the database. Surely someone looked at the logs in the past when mautic broke after sync, what were the errors that were happening?

Anyway I will take a guess here and say that you did not synchronize the databases correctly. It works when you drop the old db, create a new one and import entire dump because it really syncs the data.

As far as database goes I think the problem lays in detecting the actual changes in the database and generating the appropriate statements and executing it in the right order (for example you cannot put a new lead on to the segment, before that lead actually exists).

Question to ask yourself: Do you really need all the info from production database on your staging? Or is it enough to have emails, segments (configuration), after all this is what you are probably testing on staging

Suggestions (keep in mind that I did not really try most of this myself)

  1. When you are importing SQL file with all changes between prod/staging db try disabling foreign key checks for the time of the import (and then turn it, back on).
  2. Your scenario also sounds like something replication could solve: < https://dev.mysql.com/doc/refman/8.0/en/replication.html >
  3. Preform a backup during inactive hours and then import to staging database. If there is time users do not use mautic, you would not really care if takes time.
  4. Depending on what your answer is to a question above, I would consider just syncing the relevant tables (here you need to understand the relations between the tables and implications of importing just specific tables - so this might or might not work for you).

Anyway I would love to take a closer at your situation and help you solve it, if you are interesting in this part, drop me a PM.

I hope this was useful.

Regards, M.

1 Like
3

Sorry for late reply to this thread, but all good suggestions. But some questions.

  1. What are foreign key checks? I’m not a programmer or IT guy, although I do know quite a lot about IT related stuff and am fairly proficient with Linux and web servers.

  2. I’m also not a MySQL guru…so while replication sounds easy enough for somebody working with databases all the time, it’s something that would probably take me hours to learn. I’m so busy now I can barely keep up with client demands, so may see if I could hire somebody to do this.

  3. That’s actually what I’ve been doing, initiating the migration to staging overnight.

  4. I’ve tried just syncing relevant/changed tables using my current migration setup, which supposedly supports it, but it never works properly…so I just migrate everything in the database.

We’re moving the entire system to a dedicated server soon so I’m hopeful that will also provide a significant speed boost to this.

Chris

4

Hi @chrisb_hallcomm ,

  1. Database has a setting that either enables or disables the check of validity of data in columns that are marked as foreign keys. Foreign keys are mechanism of relational database such as MySQL to reference record from one table to the other.

When you disable foreign key checks it means that the data will not get checked against FK constraints, also going deeper types of columns are not checked.

  1. Have not done this before so I cannot speak about that, but most likely you are right here :).

Should you need any further assistance, let me know.

Regards, M.