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
26Let me know if you find it useful.
