Creating a Contact from an account makes my DB explode

[abisbe]

Hi everyone,

I'm having serious issues with my operation as since 3 days ago when certain actions are performed in my instance the CPU performance gets very damaged and eventually the whole server is blocked. I have at least, as I don't know if it's the only one, found one way to replicate this.

When I create a contact from the contact entity, I get a multiple processes at the DB server that goes something like this, I got this one when looking INSERT INTO `job` (`id`, `name`, `deleted`, `status`, `execute_time`, `queue`, `group`, `target_group`, `attempts`, `created_at`, `modified_at`, `scheduled_job_id`) VALUES ('XXXXXXXXXXXX', 'Submit Popup Reminders', 0, 'Pending', '2024-12-02 20:43:01', NULL, NULL, NULL, 1, '2024-12-02 20:43:01', '2024-12-02 20:43:01', 'YYYYYYYYYYYYY') and everything works like a charm.

When I create a contact from an account record, so, automatically related to the account, the process gets blocked, and I find this process stuck at the MYSQL server:

I'll write it in the next post as I exceed the limit of characters per post.

This looks terribly complex, and I'm thinking that I may have messed up something and this is not going through? I had in previous CRMs issues with queries being too long, therefore breaking the system when trying to run, could it be the issue?

Oh ESPO logs are clean and nothing is there when this gets stuck, but the site where I'm creating the contact gets held and finally says timeout.

Let me know if you need further clarification, or if I displayed information that would be better kept hidden, I tried to remove some personal data from it.

Thanks a lot!

[abisbe]

SELECT contact.id AS `id`, NULLIF(TRIM(CONCAT(IFNULL(contact.first_name, ''), ' ', IFNULL(contact.last_name, ''))), '') AS `name`, contact.deleted AS `deleted`, contact.salutation_name AS `salutationName`, contact.first_name AS `firstName`, contact.last_name AS `lastName`, accountContactPrimary.role AS `title`, contact.description AS `description`, emailAddresses.name AS `emailAddress`, phoneNumbers.name AS `phoneNumber`, contact.do_not_call AS `doNotCall`, contact.address_street AS `addressStreet`, contact.address_city AS `addressCity`, contact.address_state AS `addressState`, contact.address_country AS `addressCountry`, contact.address_postal_code AS `addressPostalCode`, accountContactPrimary.is_inactive AS `accountIsInactive`, account.type AS `accountType`, contact.created_at AS `createdAt`, contact.modified_at AS `modifiedAt`, portalUser.id IS NOT NULL AS `hasPortalUser`, contact.last_name2 AS `lastName2`, contact.tc_press AS `tcPress`, contact.tc_recruiter AS `tcRecruiter`, contact.tc_scientific_council AS `tcScientificCouncil`, contact.tc_affiliated_professor AS `tcAffiliatedProfessor`, contact.tc_sponsor AS `tcSponsor`, contact.tc_alumni AS `tcAlumni`, contact.tc_institutional AS `tcInstitutional`, contact.tc_marketing AS `tcMarketing`, contact.tc_professor AS `tcProfessor`, contact.tc_researcher AS `tcResearcher`, contact.tc_student AS `tcStudent`, contact.tc_sales_prospect AS `tcSalesProspect`, contact.tc_participant AS `tcParticipant`, contact.birthdate AS `birthdate`, contact.age AS `age`, contact.tc_applicant AS `tcApplicant`, contact.contact_applications_period AS `contactApplicationsPeriod`, contact.contact_applications_type AS `contactApplicationsType`, contact.numberofapplications AS `numberofapplications`, contact.passport_number AS `passportNumber`, contact.rs_academic_unit AS `rsAcademicUnit`, contact.rs_academic_unit_category AS `rsAcademicUnitCategory`, contact.rs_b_s_e_category AS `rsBSECategory`, contact.rs_affiliation AS `rsAffiliation`, contact.b_s_e_web_profile_page AS `bSEWebProfilePage`, contact.o_r_c_i_d AS `oRCID`, contact.rs_scopus_i_d AS `rsScopusID`, contact.rs_researcher_i_d AS `rsResearcherID`, contact.rs_google_scholar AS `rsGoogleScholar`, contact.rs_r_e_p_e_c_web_page AS `rsREPECWebPage`, contact.rs_h_index_s_c_o_p_u_s AS `rsHIndexSCOPUS`, contact.rs_articles_published AS `rsArticlesPublished`, contact.rs_citations_s_c_o_p_u_s AS `rsCitationsSCOPUS`, contact.rs_s_c_o_p_u_s_data_checked_date AS `rsSCOPUSDataCheckedDate`, contact.rs_university_ph_d AS `rsUniversityPhD`, contact.rs_ph_d_date AS `rsPhDDate`, contact.rs_country_ph_d AS `rsCountryPhD`, contact.rs_r_area_macro_business_cycle AS `rsRAreaMacroBusinessCycle`, contact.rs_r_area_macro_fiscal_policy AS `rsRAreaMacroFiscalPolicy`, contact.rs_r_area_macro_financial_economics AS `rsRAreaMacroFinancialEconomics`, contact.rs_r_area_macro_growth AS `rsRAreaMacroGrowth`, contact.rs_r_area_experimental_behavioral AS `rsRAreaExperimentalBehavioral`, contact.rs_r_area_micro_theory AS `rsRAreaMicroTheory`, contact.rs_r_area_applied_development AS `rsRAreaAppliedDevelopment`, contact.rs_r_area_applied_industrial AS `rsRAreaAppliedIndustrial`, contact.rs_r_area_applied_labor_public AS `rsRAreaAppliedLaborPublic`, contact.rs_r_area_applied_statistics_maths AS `rsRAreaAppliedStatisticsMaths`, contact.rs_a_p_vinculation_date AS `rsAPVinculationDate`, contact.rs_a_p_drop_date AS `rsAPDropDate`, contact.rs_a_p_leave_start_date AS `rsAPLeaveStartDate`, contact.rs_a_p_leave_end_date AS `rsAPLeaveEndDate`, contact.rs_a_p_current_drop_affiliation AS `rsAPCurrentDropAffiliation`, contact.rs_a_p_merits AS `rsAPMerits`, contact.rs_a_p_e_r_c AS `rsAPERC`, contact.rs_a_p_merits_i_c_r_e_a AS `rsAPMeritsICREA`, contact.r_s_a_p_merits_h_h_r_r AS `rSAPMeritsHHRR`, contact.rs_a_p_merits_r_e_p_e_c_top AS `rsAPMeritsREPECTop`, contact.rs_a_p_merits_econometric_society AS `rsAPMeritsEconometricSociety`, contact.rs_a_p_merits_european_economic_associatio n AS `rsAPMeritsEuropeanEconomicAssociation`, contact.rs_a_p_e_r_c_grant_code AS `rsAPERCGrantCode`, contact.rs_a_p_e_r_c_type AS `rsAPERCType`, contact.rs_a_p_e_r_c_is_current AS `rsAPERCIsCurrent`, contact.rs_a_p_e_r_c_start_date AS `rsAPERCStartDate`, contact.rs_a_p_e_r_c_end_date AS `rsAPERCEndDate`, contact.rs_a_p_e_r_c_amount AS `rsAPERCAmount`, contact.rs_teaching_m_r_e_s AS `rsTeachingMRES`, contact.rs_teaching_phd_track AS `rsTeachingPhdTrack`, contact.rs_teaching_econ AS `rsTeachingEcon`, contact.rs_teaching_finance AS `rsTeachingFinance`, contact.rs_teaching_macro AS `rsTeachingMacro`, contact.rs_teaching_e_p_p AS `rsTeachingEPP`, contact.rs_teaching_i_t_f_d AS `rsTeachingITFD`, contact.rs_teaching_c_o_m_p AS `rsTeachingCOMP`, contact.rs_teaching_d_s_methodology AS `rsTeachingDSMethodology`, contact.rs_teaching_d_s_decision_making AS `rsTeachingDSDecisionMaking`, contact.rs_associate_research_professor AS `rsAssociateResearchProfessor`, contact.rs_research_professor AS `rsResearchProfessor`, contact.rs_a_p_start_date AS `rsAPStartDate`, contact.rs_a_p_end_date AS `rsAPEndDate`, contact.rs_r_p_start_date AS `rsRPStartDate`, contact.rs_professor_type AS `rsProfessorType`, contact.p_d_post_doc_starting_date AS `pDPostDocStartingDate`, contact.p_d_post_doc_end_date AS `pDPostDocEndDate`, contact.pd_post_doc_supervisor AS `pdPostDocSupervisor`, contact.v_r_visiting_researcher_start_date AS `vRVisitingResearcherStartDate`, contact.visiting_researcher_end_date AS `visitingResearcherEndDate`, contact.v_r_visiting_researcher_former_dates AS `vRVisitingResearcherFormerDates`, contact.ph_d_student_program_start_date AS `phDStudentProgramStartDate`, contact.ph_d_student_program_end_date AS `phDStudentProgramEndDate`, contact.ph_d_student_dissertation_date AS `phDStudentDissertationDate`, contact.ph_d_student_scholarship AS `phDStudentScholarship`, contact.ph_d_student_job_market AS `phDStudentJobMarket`, contact.ph_d_student_placement_institution AS

[yuri]

Is it enough disk space on the server?

[abisbe]

Yes, both the CRM server and the MYSQL server are about 50% free

[abisbe]

`phDStudentPlacementInstitution`, contact.ph_d_student_position AS `phDStudentPosition`, contact.gender AS `gender`, contact.sugarid AS `sugarid`, contact.empty_array AS `emptyArray`, contact.consent_given AS `consentGiven`, contact.loadduplicate AS `loadduplicate`, contact.erp_i_d AS `erpID`, contact.utmsource AS `utmsource`, contact.utmmedium AS `utmmedium`, contact.utmcampaign AS `utmcampaign`, contact.utmcontent AS `utmcontent`, contact.utmdate AS `utmdate`, contact.press_institution AS `pressInstitution`, contact.rs_researcher_status AS `rsResearcherStatus`, contact.rs_citizenship AS `rsCitizenship`, contact.twitter AS `twitter`, contact.instagram AS `instagram`, contact.personal_web_page AS `personalWebPage`, contact.rs_b_s_e_webpage AS `rsBSEWebpage`, contact.rs_top5_publications AS `rsTop5Publications`, contact.rs_top3_publications AS `rsTop3Publications`, contact.rs_teaching_i_d_e_a AS `rsTeachingIDEA`, contact.rs_r_prof_end_date AS `rsRProfEndDate`, contact.rs_a_prof_expected_end_date AS `rsAProfExpectedEndDate`, contact.rs_b_s_e_alumni AS `rsBSEAlumni`, contact.consent_given_detail AS `consentGivenDetail`, contact.tephone_number AS `tephoneNumber`, citizenship.continent AS `citizenshipContinent`, contact.summer_forum_organizer AS `summerForumOrganizer`, contact.apply_u_r_l AS `applyURL`, citizenship.reporting_name AS `citizenshipShortName`, citizenship.spanish_name AS `citizenshipShortNameESP`, contact.linkedin AS `linkedin`, contact.tc_board_of_trustees AS `tcBoardOfTrustees`, contact.instposition AS `instposition`, contact.inst_institution AS `instInstitution`, contact.inst_protocol_treatment AS `instProtocolTreatment`, contact.tc_staff AS `tcStaff`, contact.tc_multi AS `tcMulti`, contact.bse_email AS `bseEmail`, contact.aneca_acreditation AS `anecaAcreditation`, contact.a_q_u_accreditation AS `aQUAccreditation`, contact.a_q_u_accreditation_year AS `aQUAccreditationYear`, contact.a_n_e_c_a_accreditation_year AS `aNECAAccreditationYear`, contact.n_i_s AS `nIS`, contact.send_training AS `sendTraining`, contact.actions AS `actions`, contact.bio_web AS `bioWeb`, contact.year_of_incorporation_b_s_e AS `yearOfIncorporationBSE`, contact.navision_supplier AS `navisionSupplier`, contact.department_of_b_s_e_managing_the_contact AS `departmentOfBSEManagingTheContact`, contact.roles_in_their_institution AS `rolesInTheirInstitution`, contact.rs_employer AS `rsEmployer`, contact.gender_not_listed AS `genderNotListed`, contact.anyneix AS `anyneix`, contact.i_b_a_n_bank_account AS `iBANBankAccount`, contact.bank_name AS `bankName`, contact.bank_account_holder AS `bankAccountHolder`, contact.fiscal_residence AS `fiscalResidence`, contact.employed_u_a_b_u_p_f AS `employedUABUPF`, contact.professor_data_changed AS `professorDataChanged`, contact.inactive_contact AS `inactiveContact`, contact.c_is_main_contact AS `cIsMainContact`, contact.middle_name AS `middleName`, emailAddresses.opt_out AS `emailAddressIsOptedOut`, emailAddresses.invalid AS `emailAddressIsInvalid`, phoneNumbers.opt_out AS `phoneNumberIsOptedOut`, phoneNumbers.invalid AS `phoneNumberIsInvalid`, contact.rs_a_p_e_r_c_amount_currency AS `rsAPERCAmountCurrency`, contact.account_id AS `accountId`, account.name AS `accountName`, contact.campaign_id AS `campaignId`, campaign.name AS `campaignName`, contact.created_by_id AS `createdById`, NULLIF(TRIM(CONCAT(IFNULL(createdBy.first_name, ''), ' ', IFNULL(createdBy.last_name, ''))), '') AS `createdByName`, contact.modified_by_id AS `modifiedById`, NULLIF(TRIM(CONCAT(IFNULL(modifiedBy.first_name, ''), ' ', IFNULL(modifiedBy.last_name, ''))), '') AS `modifiedByName`, contact.assigned_user_id AS `assignedUserId`, NULLIF(TRIM(CONCAT(IFNULL(assignedUser.first_name, ''), ' ', IFNULL(assignedUser.last_name, ''))), '') AS `assignedUserName`, portalUser.id AS `portalUserId`, NULLIF(TRIM(CONCAT(IFNULL(portalUser.first_name, ''), ' ', IFNULL(portalUser.last_name, ''))), '') AS `portalUserName`, originalLead.id AS `originalLeadId`, NULLIF(TRIM(CONCAT(IFNULL(originalLead.first_name, ''), ' ', IFNULL(originalLead.last_name, ''))), '') AS `originalLeadName`, (contact.rs_a_p_e_r_c_amount * rsAPERCAmountCurrencyRate.rate) AS `rsAPERCAmountConverted`, contact.citizenship_id AS `citizenshipId`, citizenship.name AS `citizenshipName`, contact.person_id AS `personId`, NULLIF(TRIM(CONCAT(IFNULL(person.first_name, ''), ' ', IFNULL(person.last_name, ''))), '') AS `personName`, contact.account_main_contact_id AS `accountMainContactId`, accountMainContact.name AS `accountMainContactName`, contact.bank_country_id AS `bankCountryId`, bankCountry.name AS `bankCountryName`, contact.passport_image_id AS `passportImageId`, passportImage.name AS `passportImageName`, cStaff.id AS `cStaffId`, NULLIF(TRIM(CONCAT(IFNULL(cStaff.first_name, ''), ' ',IFNULL(cStaff.last_name, ''))), '') AS `cStaffName` FROM `contact` AS `contact` LEFT JOIN `attachment` AS `passportImage` ON contact.passport_image_id = passportImage.id LEFT JOIN `c_staff` AS `cStaff` ON contact.id = cStaff.contact_id AND cStaff.deleted = 0 LEFT JOIN `country` AS `bankCountry` ON contact.bank_country_id = bankCountry.id LEFT JOIN `account` AS `accountMainContact` ON contact.account_main_contact_id = accountMainContact.id LEFT JOIN `person` AS `person` ON contact.person_id = person.id LEFT JOIN `country` AS `citizenship` ON contact.citizenship_id = citizenship.id LEFT JOIN `lead` AS `originalLead` ON contact.id = originalLead.created_contact_id AND originalLead.deleted = 0 LEFT JOIN `campaign` AS `campaign` ON contact.campaign_id = campaign.id LEFT JOIN `account` AS `account` ON contact.account_id = account.id LEFT JOIN `user` AS `assignedUser` ON contact.assigned_user_id = assignedUser.id LEFT JOIN `user` AS `modifiedBy` ON contact.modified_by_id = modifiedBy.id LEFT JOIN `user` AS `createdBy` ON contact.created_by_id = createdBy.id LEFT JOIN `entity_email_address` AS `emailAddressesMultipleMiddle` ON contact.id = emailAddressesMultipleMiddle.entity_id AND emailAddressesMultipleMiddle.deleted = 0 AND emailAddressesMultipleMiddle.entity_type = 'Contact' LEFT JOIN `email_address` AS `emailAddressesMultiple` ON emailAddressesMultiple.id = emailAddressesMultipleMiddle.email_address_id AND emailAddressesMultiple.deleted = 0 LEFT JOIN `account_contact` AS `accountContactPrimary` ON contact.id = accountContactPrimary.contact_id AND contact.account_id = accountContactPrimary.account_id AND accountContactPrimary.deleted = 0 LEFT JOIN `entity_email_address` AS `emailAddressesMiddle` ON contact.id = emailAddressesMiddle.entity_id AND emailAddressesMiddle.deleted = 0 AND emailAddressesMiddle.primary = 1 AND emailAddressesMiddle.entity_type = 'Contact' LEFT JOIN `email_address` AS `emailAddresses` ON emailAddresses.id = emailAddressesMiddle.email_address_id AND emailAddresses.deleted = 0 LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle` ON contact.id = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = 0 AND phoneNumbersMiddle.primary = 1 AND phoneNumbersMiddle.entity_type = 'Contact' LEFT JOIN `phone_number` AS `phoneNumbers` ON phoneNumbers.id = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = 0 LEFT JOIN `user` AS `portalUser` ON contact.id = portalUser.contact_id AND portalUser.deleted = 0 LEFT JOIN `currency` AS `rsAPERCAmountCurrencyRate` ON rsAPERCAmountCurrencyRate.id = contact.rs_a_p_e_r_c_amount_currency WHERE (((contact.first_name = 'XXX') AND (contact.last_name = 'YYY')) OR ((emailAddressesMultiple.lower = LOWER('test@test.com')))) AND contact.deleted = 0 LIMIT 0, 1

[abisbe]

yuri I have been testing this, and I can't find any other actions that crashes the CRM, creating a contact is ok, creating a contact from a different related record is ok, so far, only creating a contact from an account makes the MYSQL go nuts, can it be that something is indexed wrong or something and that it just keeps looking for this?

we are currently using the latest ESPO version and latest advanced pack version, running on a MySQL 5.7.44 though, we are planning to upgrade to 8 this winter break.

maybe this can help somehow!

[shalmaxb]

Did you have a look into the server logs?

[abisbe]

Yes, I have some warnings about failing to open 'classes.php', but nothing really weird

[yuri]

How many records in contact and email_address tables do you have?

[abisbe]

aprox 38.000 contacts and 72.000 email_address records, but isn't it weird that if I create a contact from another related entity doesn't create this crash? Only from the Account entity?

[yuri]

Unfortunately, we lost some messages after forum upgrade. I recommended to try to disable customizations and check.