# Help! Mautic unusable - Contact data scrambled **URL:** \ **Category:** Product Support\ **Tags:** mautic-4\ **Created:** [August 24, 2022, 2:30pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158 "2022-08-24T14:30:27Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [August 24, 2022, 2:30pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/1 "2022-08-24T14:30:27Z")
**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). EP ---
**Author:** ![techbill](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/techbill/32/8325_2.png) [@techbill](https://forum.mautic.org/u/techbill)\ **Post date:** [August 24, 2022, 4:37pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/2 "2022-08-24T16:37:43Z")
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. ---
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [August 24, 2022, 4:41pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/3 "2022-08-24T16:41:00Z")
Hi @techbill 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. ---
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [August 24, 2022, 6:23pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/4 "2022-08-24T18:23:15Z")
On closer inspection, in phpMyAdmin, I checked the ‘leads’ table. Unfortunately, the data is congruently messed up there too 😢 I was really hoping this was just a php issue on the front end. Has anyone else experienced their Mautic data being totally jumbled and messed up across datapoints? Here’s what the data looks like for a test contact: ![image](https://us1.discourse-cdn.com/flex020/uploads/mautic/original/2X/1/1471e32bab6d6091e4361a8f1996e09d2a903a37.png) The data should be: firstname: Test lastname: Smith company: Entrepositive LLC position: NULL email: test [at] entrepositive [dot] com ---
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [August 26, 2022, 8:09pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/5 "2022-08-26T20:09:28Z")
My ‘leads’ table is totally corrupted in the DB. 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?** EP ---
**Author:** ![mzagmajster](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mzagmajster/32/687_2.png) [@mzagmajster](https://forum.mautic.org/u/mzagmajster)\ **Post date:** [August 26, 2022, 8:26pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/6 "2022-08-26T20:26:10Z")
Hi, sorry to hear about your troubles. 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 :). Regards, M. ---
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [August 29, 2022, 3:00am UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/7 "2022-08-29T03:00:45Z")
Thank you so much for this info. 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?) Thank you so much @mzagmajster 👍 EP ---
**Author:** ![mzagmajster](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mzagmajster/32/687_2.png) [@mzagmajster](https://forum.mautic.org/u/mzagmajster)\ **Post date:** [August 29, 2022, 9:24am UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/8 "2022-08-29T09:24:51Z")
It should be something like (you execute those statements as SQL): ```auto SET GLOBAL FOREGIN_KEY_CHECKS=0; /* Turn off */ SET GLOBAL FOREGIN_KEY_CHECKS=1; /* Turn on */ ``` I do not know about phpmyadmin (option you mentioned might achieve what you want or not I am not sure). I think for the statements above you need root access to the database. ---
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [August 29, 2022, 2:11pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/9 "2022-08-29T14:11:09Z")
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). Thanks. EP ---
**Author:** ![mzagmajster](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mzagmajster/32/687_2.png) [@mzagmajster](https://forum.mautic.org/u/mzagmajster)\ **Post date:** [August 29, 2022, 2:49pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/10 "2022-08-29T14:49:01Z")
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). - import new contacts Regards, M. ---
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [August 29, 2022, 2:58pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/11 "2022-08-29T14:58:57Z")
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. ---
**Author:** ![mzagmajster](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mzagmajster/32/687_2.png) [@mzagmajster](https://forum.mautic.org/u/mzagmajster)\ **Post date:** [August 29, 2022, 3:52pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/12 "2022-08-29T15:52:03Z")
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.** ---
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [August 29, 2022, 9:26pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/13 "2022-08-29T21:26:46Z")
This could be over my head. How much should I expect to pay a pro to do this? I would provide a SQL of missing entries and any associated data for leads table. EP ---
**Author:** ![mzagmajster](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mzagmajster/32/687_2.png) [@mzagmajster](https://forum.mautic.org/u/mzagmajster)\ **Post date:** [August 30, 2022, 8:24am UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/14 "2022-08-30T08:24:42Z")
Hi, 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 :). Regards, M. ---
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [September 7, 2022, 1:42pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/15 "2022-09-07T13:42:01Z")
@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: > [@1-time MySQL leads table update/repair from CSV](https://forum.mautic.org/t/1-time-mysql-leads-table-update-repair-from-csv/25280): > > The opportunity is: I made a bad API call and it totally wrecked my leads table, with corrupted data that got split up over multiple columns - very messy. I recovered the data, fortunately, in a CSV file with ~3k contacts incl anon. I also have a backup of the live DB in its current state (with corrupted leads data), so we can run through this process as many times as we need. I managed to get the leads table rebuilt from the CSV, but it’s not clean and not 100% correct. Front end in Mautic st… 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. EP ---
**Author:** ![entrepositive](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/entrepositive/32/9645_2.png) [@entrepositive](https://forum.mautic.org/u/entrepositive)\ **Post date:** [September 13, 2022, 5:49pm UTC](https://forum.mautic.org/t/help-mautic-unusable-contact-data-scrambled/25158/16 "2022-09-13T17:49:05Z")
In the end, I hired an expert, a rather superb expert who did a top job! I don’t know the exact details but my Mautic seems to be back to normal and leads data recovered, at least all the critical stuff. If I can, I will post a more robust solution here. EP