Kenneth Brill

SQL From the SugarCRM.log file

Discussion created by Kenneth Brill on Mar 7, 2020
Latest reply on May 27, 2020 by Viktor Ranga

I might be wrong but ever since the change over to Prepared Statements the queries from errors or slow queries in the logs have been difficult to use at best.  I have had to manually reconstruct more than a few queries.  Maybe there is a tool out there that will run them formatted that way but I have not seen it yet, so I wrote my own.  If you know of a tool that runs them right from the log let me know.

 

So, in the log file queries might look like this

SELECT contacts_cstm.contact_type_c, contacts.mkto_id, contacts.primary_address_state, 
contacts_cstm.cert_owner_new_c, contacts_cstm.partner_type_2_c, case when
jt1_following_link.id IS NOT NULL then 1 else 0 end following, case when
jt9_favorite_link.id IS NOT NULL then 1 else 0 end my_favorite, contacts.salutation,
contacts.first_name, contacts.last_name, contacts_cstm.star2star_userid_c,
contacts.title, jt2_email_addresses_primary.email_address email, contacts.phone_work,
contacts.date_entered, contacts_cstm.contact_role_c, jt3_accounts.id account_id,
jt3_accounts.assigned_user_id account_id_owner, jt3_accounts.name account_name,
jt3_accounts.assigned_user_id account_name_owner, contacts_cstm.permissions_role_c,
jt4_accounts_contacts_2.id accounts_contacts_2accounts_ida,
jt4_accounts_contacts_2.assigned_user_id accounts_contacts_2accounts_ida_owner,
jt4_accounts_contacts_2.name accounts_contacts_2_name,
jt4_accounts_contacts_2.assigned_user_id accounts_contacts_2_name_owner,
jt5_accounts_contacts_4.id accounts_contacts_4accounts_ida,
jt5_accounts_contacts_4.assigned_user_id accounts_contacts_4accounts_ida_owner,
jt5_accounts_contacts_4.name accounts_contacts_4_name,
jt5_accounts_contacts_4.assigned_user_id accounts_contacts_4_name_owner,
jt6_assigned_user_link.first_name rel_assigned_user_name_first_name,
jt6_assigned_user_link.last_name rel_assigned_user_name_last_name,
jt6_assigned_user_link.created_by assigned_user_name_owner, contacts.date_modified,
jt7_created_by_link.first_name rel_created_by_name_first_name,
jt7_created_by_link.last_name rel_created_by_name_last_name,
jt7_created_by_link.created_by created_by_name_owner, jt8_modified_user_link.first_name
rel_modified_by_name_first_name, jt8_modified_user_link.last_name
rel_modified_by_name_last_name, jt8_modified_user_link.created_by
modified_by_name_owner, contacts_cstm.portal_last_login_dt_c,
contacts.primary_address_city, contacts.primary_address_country,
contacts.assigned_user_id, contacts.created_by, contacts.modified_user_id, contacts.id,
contacts_erased.data erased_fields, jt2_email_addresses_primary_erased.data
email_addresses_primary_erased_fields FROM contacts LEFT JOIN erased_fields
contacts_erased ON contacts_erased.bean_id = contacts.id AND contacts_erased.table_name
= ? LEFT JOIN sugarfavorites sf_contacts ON (sf_contacts.module = ?) AND
(sf_contacts.record_id = contacts.id) AND (sf_contacts.assigned_user_id = ?) AND
(sf_contacts.deleted = ?) LEFT JOIN contacts_cstm contacts_cstm ON contacts_cstm.id_c
= contacts.id LEFT JOIN subscriptions contacts_following ON (contacts.id =
contacts_following.parent_id) AND (contacts_following.deleted = ?) AND
(contacts_following.parent_type = ?) AND (contacts_following.created_by = ?) LEFT JOIN
users jt1_following_link ON (jt1_following_link.id = contacts_following.created_by)
AND (jt1_following_link.deleted = ?) LEFT JOIN email_addr_bean_rel
contacts_email_addresses_primary ON (contacts.id =
contacts_email_addresses_primary.bean_id) AND (contacts_email_addresses_primary.deleted
= ?) AND (contacts_email_addresses_primary.bean_module = ?) AND
(contacts_email_addresses_primary.primary_address = ?) LEFT JOIN email_addresses
jt2_email_addresses_primary ON (jt2_email_addresses_primary.id =
contacts_email_addresses_primary.email_address_id) AND
(jt2_email_addresses_primary.deleted = ?) LEFT JOIN erased_fields
jt2_email_addresses_primary_erased ON jt2_email_addresses_primary_erased.bean_id =
jt2_email_addresses_primary.id AND jt2_email_addresses_primary_erased.table_name = ?
LEFT JOIN accounts_contacts accounts_contacts ON (contacts.id =
accounts_contacts.contact_id) AND (accounts_contacts.deleted = ?) AND
(accounts_contacts.primary_account = ?) LEFT JOIN accounts jt3_accounts ON
(jt3_accounts.id = accounts_contacts.account_id) AND (jt3_accounts.deleted = ?) LEFT
JOIN accounts_contacts_2_c accounts_contacts_2 ON (contacts.id =
accounts_contacts_2.accounts_contacts_2contacts_idb) AND (accounts_contacts_2.deleted =
?) LEFT JOIN accounts jt4_accounts_contacts_2 ON (jt4_accounts_contacts_2.id =
accounts_contacts_2.accounts_contacts_2accounts_ida) AND
(jt4_accounts_contacts_2.deleted = ?) LEFT JOIN accounts_contacts_4_c
accounts_contacts_4 ON (contacts.id =
accounts_contacts_4.accounts_contacts_4contacts_idb) AND (accounts_contacts_4.deleted =
?) LEFT JOIN accounts jt5_accounts_contacts_4 ON (jt5_accounts_contacts_4.id =
accounts_contacts_4.accounts_contacts_4accounts_ida) AND
(jt5_accounts_contacts_4.deleted = ?) LEFT JOIN users jt6_assigned_user_link ON
(contacts.assigned_user_id = jt6_assigned_user_link.id) AND
(jt6_assigned_user_link.deleted = ?) LEFT JOIN users jt7_created_by_link ON
(contacts.created_by = jt7_created_by_link.id) AND (jt7_created_by_link.deleted = ?)
LEFT JOIN users jt8_modified_user_link ON (contacts.modified_user_id =
jt8_modified_user_link.id) AND (jt8_modified_user_link.deleted = ?) LEFT JOIN
sugarfavorites contacts_favorite ON (contacts.id = contacts_favorite.record_id) AND
(contacts_favorite.deleted = ?) AND (contacts_favorite.module = ?) AND
(contacts_favorite.created_by = ?) LEFT JOIN users jt9_favorite_link ON
(jt9_favorite_link.id = contacts_favorite.modified_user_id) AND
(jt9_favorite_link.deleted = ?) WHERE (contacts_cstm.contact_type_c IN (?)) AND
(contacts.deleted = ?) ORDER BY contacts.date_modified DESC, contacts.id DESC LIMIT
26Params: {"1":"contacts","2":"Contacts","3":"0b069d40-2b7f-11e7-b49b-
52d504b662cb"
,"4":0,"5":"0","6":"Contacts","7":"0b069d40-2b7f-11e7-b49b-
52d504b662cb"
,"8":0,"9":0,"10":"Contacts","11":1,"12":0,"13":"email_addresses","14":0,
"15":1,"16":0,"17":"0","18":0,"19":"0","20":0,"21":0,"22":0,"23":0,"24":"0","25":
"Contacts","26":"0b069d40-2b7f-11e7-b49b-52d504b662cb","27":0,"28":"Customer","29":0}Types: {"1":2,"2":2,"3":2,"4":2,"5":2,"6":2,"7":2,"8":5,"9":5,"10":2,"11":5,"12":5,"13":2,"14"
:5,"15":5,"16":5,"17":2,"18":5,"19":2,"20":5,"21":5,"22":5,"23":5,"24":2,"25":2,"26":2,
"27":5,"28":2,"29":5}

Some SQL and some JSON.  I tried using it in every tool I have and none would recombine it back into SQL.  So I wrote a little tool to do just that.

 

Just go here

 

SugarCRM Logged SQL processor

 

Paste in that blob of SQL from above and it will recombine it back into a runnable query.  Nothing is saved other than some tracking data so I can see if anyone uses the tool.   It will even format it for you for easier reading, something like this

 

SELECT 
  contacts_cstm.contact_type_c,
  contacts.mkto_id,
  contacts.primary_address_state,
  contacts_cstm.cert_owner_new_c,
  contacts_cstm.partner_type_2_c,
  case when jt1_following_link.id IS NOT NULL then 1 else 0 end following,
  case when jt9_favorite_link.id IS NOT NULL then 1 else 0 end my_favorite,
  contacts.salutation,
  contacts.first_name,
  contacts.last_name,
  contacts_cstm.star2star_userid_c,
  contacts.title,
  jt2_email_addresses_primary.email_address email,
  contacts.phone_work,
  contacts.date_entered,
  contacts_cstm.contact_role_c,
  jt3_accounts.id account_id,
  jt3_accounts.assigned_user_id account_id_owner,
  jt3_accounts.name account_name,
  jt3_accounts.assigned_user_id account_name_owner,
  contacts_cstm.permissions_role_c,
  jt4_accounts_contacts_2.id accounts_contacts_2accounts_ida,
  jt4_accounts_contacts_2.assigned_user_id accounts_contacts_2accounts_ida_owner,
  jt4_accounts_contacts_2.name accounts_contacts_2_name,
  jt4_accounts_contacts_2.assigned_user_id accounts_contacts_2_name_owner,
  jt5_accounts_contacts_4.id accounts_contacts_4accounts_ida,
  jt5_accounts_contacts_4.assigned_user_id accounts_contacts_4accounts_ida_owner,
  jt5_accounts_contacts_4.name accounts_contacts_4_name,
  jt5_accounts_contacts_4.assigned_user_id accounts_contacts_4_name_owner,
  jt6_assigned_user_link.first_name rel_assigned_user_name_first_name,
  jt6_assigned_user_link.last_name rel_assigned_user_name_last_name,
  jt6_assigned_user_link.created_by assigned_user_name_owner,
  contacts.date_modified,
  jt7_created_by_link.first_name rel_created_by_name_first_name,
  jt7_created_by_link.last_name rel_created_by_name_last_name,
  jt7_created_by_link.created_by created_by_name_owner,
  jt8_modified_user_link.first_name rel_modified_by_name_first_name,
  jt8_modified_user_link.last_name rel_modified_by_name_last_name,
  jt8_modified_user_link.created_by modified_by_name_owner,
  contacts_cstm.portal_last_login_dt_c,
  contacts.primary_address_city,
  contacts.primary_address_country,
  contacts.assigned_user_id,
  contacts.created_by,
  contacts.modified_user_id,
  contacts.id,
  contacts_erased.data erased_fields,
  jt2_email_addresses_primary_erased.data email_addresses_primary_erased_fields
FROM
  contacts
  LEFT JOIN erased_fields contacts_erased ON contacts_erased.bean_id = contacts.id
  AND contacts_erased.table_name = 'contacts'
  LEFT JOIN sugarfavorites sf_contacts ON (sf_contacts.module = 'Contacts')
  AND (
    sf_contacts.record_id = contacts.id
  )
  AND (
    sf_contacts.assigned_user_id = '0b069d40-2b7f-11e7-b49b-52d504b662cb'
  )
  AND (sf_contacts.deleted = 0)
  LEFT JOIN contacts_cstm contacts_cstm ON contacts_cstm.id_c = contacts.id
  LEFT JOIN subscriptions contacts_following ON (
    contacts.id = contacts_following.parent_id
  )
  AND (contacts_following.deleted = 0)
  AND (
    contacts_following.parent_type = 'Contacts'
  )
  AND (
    contacts_following.created_by = '0b069d40-2b7f-11e7-b49b-52d504b662cb'
  )
  LEFT JOIN users jt1_following_link ON (
    jt1_following_link.id = contacts_following.created_by
  )
  AND (jt1_following_link.deleted = 0)
  LEFT JOIN email_addr_bean_rel contacts_email_addresses_primary ON (
    contacts.id = contacts_email_addresses_primary.bean_id
  )
  AND (
    contacts_email_addresses_primary.deleted = 0
  )
  AND (
    contacts_email_addresses_primary.bean_module = 'Contacts'
  )
  AND (
    contacts_email_addresses_primary.primary_address = 1
  )
  LEFT JOIN email_addresses jt2_email_addresses_primary ON (
    jt2_email_addresses_primary.id = contacts_email_addresses_primary.email_address_id
  )
  AND (
    jt2_email_addresses_primary.deleted = 0
  )
  LEFT JOIN erased_fields jt2_email_addresses_primary_erased ON jt2_email_addresses_primary_erased.bean_id = jt2_email_addresses_primary.id
  AND jt2_email_addresses_primary_erased.table_name = 'email_addresses'
  LEFT JOIN accounts_contacts accounts_contacts ON (
    contacts.id = accounts_contacts.contact_id
  )
  AND (accounts_contacts.deleted = 0)
  AND (
    accounts_contacts.primary_account = 1
  )
  LEFT JOIN accounts jt3_accounts ON (
    jt3_accounts.id = accounts_contacts.account_id
  )
  AND (jt3_accounts.deleted = 0)
  LEFT JOIN accounts_contacts_2_c accounts_contacts_2 ON (
    contacts.id = accounts_contacts_2.accounts_contacts_2contacts_idb
  )
  AND (accounts_contacts_2.deleted = 0)
  LEFT JOIN accounts jt4_accounts_contacts_2 ON (
    jt4_accounts_contacts_2.id = accounts_contacts_2.accounts_contacts_2accounts_ida
  )
  AND (
    jt4_accounts_contacts_2.deleted = 0
  )
  LEFT JOIN accounts_contacts_4_c accounts_contacts_4 ON (
    contacts.id = accounts_contacts_4.accounts_contacts_4contacts_idb
  )
  AND (accounts_contacts_4.deleted = 0)
  LEFT JOIN accounts jt5_accounts_contacts_4 ON (
    jt5_accounts_contacts_4.id = accounts_contacts_4.accounts_contacts_4accounts_ida
  )
  AND (
    jt5_accounts_contacts_4.deleted = 0
  )
  LEFT JOIN users jt6_assigned_user_link ON (
    contacts.assigned_user_id = jt6_assigned_user_link.id
  )
  AND (
    jt6_assigned_user_link.deleted = 0
  )
  LEFT JOIN users jt7_created_by_link ON (
    contacts.created_by = jt7_created_by_link.id
  )
  AND (jt7_created_by_link.deleted = 0)
  LEFT JOIN users jt8_modified_user_link ON (
    contacts.modified_user_id = jt8_modified_user_link.id
  )
  AND (
    jt8_modified_user_link.deleted = 0
  )
  LEFT JOIN sugarfavorites contacts_favorite ON (
    contacts.id = contacts_favorite.record_id
  )
  AND (contacts_favorite.deleted = 0)
  AND (
    contacts_favorite.module = 'Contacts'
  )
  AND (
    contacts_favorite.created_by = '0b069d40-2b7f-11e7-b49b-52d504b662cb'
  )
  LEFT JOIN users jt9_favorite_link ON (
    jt9_favorite_link.id = contacts_favorite.modified_user_id
  )
  AND (jt9_favorite_link.deleted = 0)
WHERE
  (
    contacts_cstm.contact_type_c IN ('Customer')
  )
  AND (contacts.deleted = 0)
ORDER BY
  contacts.date_modified DESC,
  contacts.id DESC
LIMIT
  26

Let me know if you find it useful.

Outcomes