Checking integrity of SugarCRM installation and database prior to migrating to SuiteCRM

šŸ“¦ Install & Upgrade Help
1

I am curious if there are any scripts I can run to verify integrity of current SugarCRM installation prior to migrating to SuireCRM? I am at SugarCRM 6.5. Ideally the script(s) should check both database and file integrity. MySQL script for checking database integrity? php script for checking installation integrity?

Thanks.

2

I donā€™t think there are. But I think you will find this article I wrote useful:

Those sample queries for deleted records and (two kinds of) orphaned records are essentially what you need to look at.

You can start by running the first query on this post, it provides good map of where to start:

3

Thank you, I will run them on my SugarCRM installation. I think it is important to verify integrity of the current installation /before/ migrating to SuiteCRM.

One of the things I would also like to do is compare what SugarCRM believes I have stored in my files directory and what actually exists.

Would you happen to have a MySQL script for that as well? I am comfortable with SQL but somewhat lacking in my understanding of the workings of SugarCRM and SuiteCRMā€¦

4

I donā€™t have it, but I have posts documenting it:

Finally, I wrote this high-level overview for upgrades:

5

Thank you for the references. I think it is important to clean up the existing SugarCRM installation before migrating to SuiteCRM.

I began by looking at which tables also have a _cstm version under the assumption that these tables are extensions of the standard tables /and/ that any orphaned records in any of the _cstm tables are not referenced anywhere else and therefore can safely be removed. Itā€™s like pruning from the bottom of the database tree of tables before checking other tables.

Here are the queries I ran and the results:
SELECT * FROM accounts_cstm ChildTable LEFT JOIN accounts ParentTable ON ChildTable.id_c = ParentTable.id WHERE ParentTable.id IS NULL;
Empty set
SELECT * FROM contacts_cstm ChildTable LEFT JOIN contacts ParentTable ON ChildTable.id_c = ParentTable.id WHERE ParentTable.id IS NULL;
Empty set

The results from two queries above were empty sets, as would be expected.

SELECT * FROM leads_cstm ChildTable LEFT JOIN leads ParentTable ON ChildTable.id_c = ParentTable.id WHERE ParentTable.id IS NULL;
1774 rows
SELECT * FROM notes_cstm ChildTable LEFT JOIN notes ParentTable ON ChildTable.id_c = ParentTable.id WHERE ParentTable.id IS NULL;
3318 rows
SELECT * FROM prospects_cstm ChildTable LEFT JOIN prospects ParentTable ON ChildTable.id_c = ParentTable.id WHERE ParentTable.id IS NULL;
6460 rows
SELECT * FROM tasks_cstm ChildTable LEFT JOIN tasks ParentTable ON ChildTable.id_c = ParentTable.id WHERE ParentTable.id IS NULL;
92 rows

The rows from the last four queries flashing by on the screen seemed to be all NULLs, probably the results of some faulty operations a long time ago (most likely import operations).

Would you agree that it is completely safe to delete any all-NULL rows found in the last four queries?

6

I know there are a few instances where documents added to the CRM cannot be found with the message ā€œInvalid file referenceā€ after a crash fairly recently.

Does that mean there are missing links in the notes table, ie the filenames in the query below are not in the upload folder?

SELECT notes.id, emails.id, CONCAT(ā€œupload/ā€, filename), file_mime_type FROM notes INNER JOIN emails ON notes.parent_type = ā€˜Emailsā€™ AND notes.parent_id = emails.id INNER JOIN emails_text ON emails.id = emails_text.email_id;

7

I confess I donā€™t feel I should be telling people on the Internet that ā€œitā€™s completely safe to deleteā€ their data :sweat_smile:

Itā€™s your call. Export the data before deleting it, in case you need it back.

But once you understand the logic of database, it shouldnā€™t be too risky. An entry in a custom table that does not have a main record pointing to it, will never be used. And itā€™s a known fact that SuiteCRM doesnā€™t do these clean ups when the main records are deleted, so itā€™s no surprise to find these left-overs.

About the files, also make sure you understand the logic. Note that the files are stored on disk based on the id, not the filename. That filename field is not the ā€œname of the file as SuiteCRM stores itā€, itā€™s just ā€œthe name the file originally had when it was uploadedā€. Itā€™s supposed to be the filename that means something to the user, not to SuiteCRM. Once you understand this, and can properly see which files are referenced by each of the Notes records, you can be safe deleting the ones that donā€™t point at anything.