accounts_contacts.date_modified

[tj@estreet.com]

Trying to pin down the logic that causes an UNNECESSARY update to accounts_contacts table date_modified.

We are synchronizing our PRIMARY db to numerous SATELLITES on tablet pc in the field, and notice
that the accounts_contacts table is frequently out of sync simply by the fact that date_modified gets
updated at some point (presumably by some update to either a related accounts or contacts record)
while there is NO OTHER CHANGE TO SAID RELATIONSHIP.

Please,
If accounts_contacts.contact_id, and accounts_contacts.account_id remain unchanged, why does
accounts_contacts.date modified get updated (where is the logic that causes this update)?

[tj@estreet.com]

Here's an example, this morning I assigned 9 contacts to 2 different accounts.
Upon synchronization there were 305 updated accounts_contacts records, which
amounts to the sum of all contacts assigned to these 2 accounts.
The update... date_modified.
Nobody cares. none of the pertinent data for any of these records has changed.
id, account_id, contact_id, and deleted remain unchanged.
Only the date_modified is updated, and UNNECESSARILY SO, but I can't figure out
when, where or why.

Anyone????

[eggsurplus]

Hey tj,

In data/SugarBean.php in save() if $this->update_date_modified is set to true it'll set the date modified. It should only do that to affected records though so it must be happening somewhere up the tree more. Can you enable logging by turning on all logs in log4php.properties, going to the page before when all records get updated, clear sugarcrm.log, then duplicate the issue? Then we can see if every record is being updated or if it's a bad query that's updated (not specifying the id).

[tj@estreet.com]

Thanks Eggsurplus! Can do.
I forgot to mention in that the contacts updated in my example above were assigned via mass_update from contacts list view. will run furter tests, log and post.
Again, thanks for the response!

[tj@estreet.com]

Okay, here is the sugarcrm.log. I see now, looking through it for the first time
in a very long while, that we are still running queries on long deprecated custom
fields in an equally long deprecated contacts_ custom.table. Don't know if related
to issue at hand but am looking at getting rid of said custom_table.

Thanks

07/30/07 11:08:09,209 [1836] DEBUG 192.168.1.1 - SugarBean.load_relationships, Loading relationship (accounts).
07/30/07 11:08:09,210 [1836] DEBUG 192.168.1.1 - Link Constructor, relationship name: accounts_contacts
07/30/07 11:08:09,211 [1836] DEBUG 192.168.1.1 - Link Constructor, Table name:
07/30/07 11:08:09,212 [1836] DEBUG 192.168.1.1 - Link Constructor, Key name:
07/30/07 11:08:09,213 [1836] DEBUG 192.168.1.1 - Link Constructor, _bean_table_name: contacts
07/30/07 11:08:09,214 [1836] DEBUG 192.168.1.1 - Link Constructor, _bean_key_name: id
07/30/07 11:08:09,214 [1836] DEBUG 192.168.1.1 - Link Constructor, relationship record found.
07/30/07 11:08:09,217 [1836] DEBUG 192.168.1.1 - relationship_exists query(SELECT id FROM accounts_contacts WHERE account_id='3739dd02-1f0f-8881-780a-45773b3d8bd2'AND contact_id='10627259-df64-d8ab-1cd5-46447767d5e4'AND deleted=0)
07/30/07 11:08:09,218 [1836] INFO 192.168.1.1 - Query:SELECT id FROM accounts_contacts WHERE account_id='3739dd02-1f0f-8881-780a-45773b3d8bd2'AND contact_id='10627259-df64-d8ab-1cd5-46447767d5e4'AND deleted=0
07/30/07 11:08:09,220 [1836] INFO 192.168.1.1 - Query Execution Time:0.001773
07/30/07 11:08:09,221 [1836] DEBUG 192.168.1.1 - Relationship Update Statement :UPDATE accounts_contacts SET contact_id='10627259-df64-d8ab-1cd5-46447767d5e4' ,account_id='3739dd02-1f0f-8881-780a-45773b3d8bd2' ,deleted='0' ,date_modified='2007-07-30 17:08:09' WHERE account_id='3739dd02-1f0f-8881-780a-45773b3d8bd2'AND contact_id='10627259-df64-d8ab-1cd5-46447767d5e4'AND deleted=0
07/30/07 11:08:09,222 [1836] INFO 192.168.1.1 - Query:UPDATE accounts_contacts SET contact_id='10627259-df64-d8ab-1cd5-46447767d5e4' ,account_id='3739dd02-1f0f-8881-780a-45773b3d8bd2' ,deleted='0' ,date_modified='2007-07-30 17:08:09' WHERE account_id='3739dd02-1f0f-8881-780a-45773b3d8bd2'AND contact_id='10627259-df64-d8ab-1cd5-46447767d5e4'AND deleted=0
07/30/07 11:08:09,274 [1836] INFO 192.168.1.1 - Query Execution Time:0.050767
07/30/07 11:08:09,283 [1836] DEBUG 192.168.1.1 - get_list: order_by = 'account_name desc' and where = 'contacts.last_name like 'testing123%' and contacts.first_name like 'TJ%' and (contacts.email1 like 'tj@estreet%' OR contacts.email2 like 'tj@estreet%' OR contacts.email1 like '%tj@estreet%' OR contacts.email2 like '%tj@estreet%')' and limit = ''
07/30/07 11:08:09,285 [1836] INFO 192.168.1.1 - tableExists: contacts_cstm
07/30/07 11:08:09,285 [1836] INFO 192.168.1.1 - Query:SHOW TABLES LIKE 'contacts_cstm'
07/30/07 11:08:09,289 [1836] INFO 192.168.1.1 - Query Execution Time:0.002636
07/30/07 11:08:09,290 [1836] DEBUG 192.168.1.1 - process_list_query: SELECT

[eggsurplus]

If you check a row in the list view and click on Mass Update it'll always reset the date_modified even if the row has the same value of the field that you are changing. I suppose a change could be made to check to see if the record has the value already.

My guess is that currently it does something like this:
update contacts
set contacts.lastname = 'eggsurplus'
,date_modified = now()
where contacts.id in ('myidlist')

If that's the case and it's changed to this then you'd get what you're looking for:
update contacts
set contacts.lastname = 'eggsurplus'
,date_modified = now()
where contacts.lastname != 'eggsurplus'
and contacts.id in ('myidlist')

[tj@estreet.com]

Right, but...

update contacts
set contacts.lastname = 'eggsurplus'
,date_modified = now()
where contacts.id in ('myidlist')

SHOULD HAVE NO IMPACT on accounts_contacts.date_modified
Should it?????

[eggsurplus]

I'm trying to trace how it's possible and I'm having no luck by just guessing the steps that you may be doing. Can you layout the steps on how you get the date_modified to be updated? Is it something like:
1. Go to Contacts listview
2. Select all rows
3. Set Sync to Outlook to yes in Mass Update
4. Click on Update in Mass Update
All accounts_contacts selected have the date_modified field updated
????

Thanks, what a doozie

[tj@estreet.com]

With regard to "synchronization" we aren't using Outlook, but rather synchronizing mirror
sugar_crm dbs on remote tablet pc's in the field via SQLyog Enterprise.

As for the "steps" --just about any mass_update from contacts listview will do the trick.
For Example:
1. Go to Contacts listview
2. Select desired rows
3. Assign to: [SELECT_USER]
4. Click on Update in Mass Update
All accounts_contacts.date_modified ARE updated
WHERE accounts_contacts.account_id = accounts_contacts.account_id IN(selected contact)

Weird Huh?

I wouldn't be surprised if this was happening to everyone, only nobody notices because... well...
outside of the burden of db_synchronization... NOBODY ever notices because... Who cares Right?

That is to say, this could be happening to Sugar Users everywhere, but nobody has noticed or cared
but us, and then only because of unexpected synchronization results in our sync_logs right?
I mean, it doesn't hurt anything. It just makes for some otherwise unnecessary synchronization? (not to understate THAT)

[eggsurplus]

Yep, just verified the same thing here. Does it for any record that is checked. I'll dig into how that's happening. Thanks for the steps.