Your software
My Mautic version is: 4.1.2
My PHP version is: 7.4.30
My Database type and version is: mysql 8.0.30
My problem is:
Contact data is all scrambled and has rendered Mautic instance unusable. Contacts’ names have been rearranged, partial strings are present in the wrong fields (eg. while viewing Contacts in Mautic, the “Name” field is screwed up with partial strings added and part of contact name missing, “Email” column is empty even for known contacts, the emails are stored under “Location” column, “stage” column has extremely large negative numbers, “points” field contains a string “231855 days ago”, and “Last Active” contains the contact’s ID, while the “ID” column remains empty for most contacts). I really hope I can recover this data…
This is not 100% consistent. A few known contacts’ data was scrambled differently.
There are also other issues. The SMTP settings became somehow disconnected.
These errors are showing in the log:
[2022-08-24 01:28:30] mautic.ERROR: Doctrine\ORM\EntityNotFoundException: Entity of type ‘Mautic\StageBundle\Entity\Stage’ for IDs id([myUserID]) was not found - in file /…/mautic/vendor/doctrine/orm/lib/Doctrine/ORM/EntityNotFoundException.php - at line 47
Steps I have tried to fix the problem:
Attempted to recover (contact) data.
Hired an IT / web server expert who said it’s a mautic related issue at php level and MySql log shows data is recovered. He cannot help more than that.
I hope this is recoverable (and really hope this is not malicious…that would be a tragedy for the mautic community).
What was being done prior to the scramble? Was Mautic being updated?
Do you have a backup prior to the scramble?
It seems like something had happened to your MySQL application itself that scrambled the database or a corrupted backup was inserted into the database overwriting the existing one.
I doubt there anything in within Mautic that could had bugger out to a point where it would scramble a database like you described but it would help us if we know what was done prior the scramble.
Thanks for your response. I noticed this originally after attempting to update the “tags” on a contact from an API call in a testing environment on the automation platform n8n.
The api call failed, and then I checked the test contact in mautic and everything went bonkers.
I have a backup, but there were contacts added (fortunately, not too many) between the backup and the time it became corrupted.
So I’ve extracted the good ‘leads’ table from the backup. Now I have to create a new DB, dump the backed up ‘leads’ table. Manually add the contacts that were added after the backup. Then I match up the same IDs that these contacts had from the corrupted table - I should be able to creatively piece together the data.
This should fix the other tables that reference the ‘leads’ table by “lead_id”, as long as they match up.
This process includes renaming the ‘leads’ table to ‘leads_old’, then dumping a new ‘leads’ table with more or less the same data. Does anything know if this might create a problem in Mautic?
First… before you are merging and adding anything to a restored database. Make sure you have a copy of everything so that you can do this process N times if necessary.
Since a lot of tables reference the leads table, I would suggest prior to renaming and inserting the data into new leads table I would turn off foreign key checks. When you are done you turn on foreign key checks back on of course :).
Yes the backups - absolute certainty. A mistake you only make once!
I’m ever grateful you mentioned the foreign key checks, as I would have blindly stepped right off that ledge without your warning.
I notice in my IMPORT menu within PHPmyadmin, I have an option to disable foreign key checks. Is that where you are referring? Or is there another higher-level option I need to be looking for that?
Conceptually, I understand why we would disable this. Same reason you depressurize a pressurized system before dissembling. I don’t want other Tables to be referencing the new table until it’s all set and done in the DB. Right?
Where do I find that option exactly? (And then, how do I re-enable it after the new table dump?)
OK this is certainly achievable. (EDIT: probably achievable)
Now Mautic is constantly creating countless anon contacts in the leads table. If I ignore those categorically as I add the new contacts into the backed up leads table (which is missing the newly added contacts), would I encounter a problem with other tables referencing those IDs?
Alternatively, if I create table rows for all new IDs in the leads table, but leave everything else blank or NULL, might I still encounter a problem with other tables referencing those IDs? (Speaking strictly for the anon contacts created in leads table).
Anonymous contact gets created when mautic tracking script gets fired for the first time. That same contact gets updated with data when lead actually fills out some data and data gets stored in mautic.
I would leave anonymous contacts as they are. The contacts that are referenced by other tables must exist in the lead table since otherwise when you turn back on the foreign key checks records from other tables will reference parent record (lead) that does not exist.
Your strategy about setting just lead id and leaving everything else NULL might work, I do not see any advantage going this route.
I would avoid deleting and inserting any records while your constraints are off. Personally I would go about this as follows:
determine which records in current table are new and you would like to insert them to the new leads table
turn off FK checks
drop corrupted table
import table from backup
turn on FK checks
check the status of migrations (if all FKs in leads table are valid and fix it manually if they are not).
OK this makes sense. And the strategy you suggested is what I will follow.
To clear up a potential miscommunication, I am not deleting any data. Rather, data has become corrupted in the leads table (only; other tables unaffected by data corruption).
The only backup I have is older so new important contacts have been added to the “live” leads table since the backup, and that data has become corrupted. This also includes many anon contacts who are not yet known. The known and important contacts since the backup is a relatively small number and I am able to manually recover this data.
I will not be “deleting” data but rather replacing the entire leads table with “cleaned” data. In a sense, I suppose that is a deletion. But a rather backwards approach.
Now - the backup leads table, I was able to isolate from the total DB backup. Now I just have to add new (uncorrupted) contact data from there. For anon contacts I suppose i will just add IDs with all null values and hope for the best.
A third approach: Within Mautic I could view the anon contacts and attempt to delete all newly added anon contacts since the backup. This should delete them from the corrupted leads table; and I could then ignore them in my newly created leads table… Sacrifice here is I lose some historical anon data. To me, that’s a small price to pay.
If I understand this correctly there is a potential for child tables (tables connected to leads table) to contain references that do not exist in leads table anymore. After your import from backup I would:
create a left join on every table leads table is connected to and see if there are any records that do not have parent record in lead table (and then insert them into leads table like you suggested). This way you should minimize data loss.
If you can lose history on anonymous contacts I would delete anonymous contacts but I would do that after your backup is restored and constraints turned on and if at all possible via mysql terminal not web Ui.
IDs have to match exactly, so when you are inserting new leads make sure that you use the id from the corrupted table.
I think the final price highly depends on the amount of data and database size, because the more data, more time is required. I can look into your database situation just not before next week.
If you are interested drop me a PM with your email and I will contact you, when I have time to analyze the data and make an actual offer.
But I do hope you manage to resolve the issue as soon as possible - with or without my help :).
@mzagmajster I tried a lot of different angles here and followed your suggestions above as well.
I was able to upload the csv into a new_leads table, then truncate data from leads table and copy data over from new_leads table, leaving structure the way it was. On the front end this cleaned up the data A LOT, however it’s not fixed yet. Contact IDs are (mostly) consistently in the incorrect column, despite their being in the correct column in the DB…
I guess I made one or more mistakes in the data migration and or tampering with the generated_email_domain column.
In the effort to solve this issue rapidly, I have created an opportunity:
I need help on this. I’ve spoken with a Mautic developer who can’t manage the MySQL side, and a MySQL expert who doesn’t know Mautic… I’m stuck here.
