search

twomice / com.joineryhq.jsumfields

Provides additional fields under the Summary Fields framework.
Other
1 stars 10 forks source link

When upgrading summary fields and probably initial install getting DB Error:No such field #9

Open Paul-Tahoe opened 4 years ago

Paul-Tahoe commented 4 years ago

I came across this awhile ago and thought I had created an issue for this but I guess I didn't. Maybe I felt I was too down rev and couldn't believe it. I'm sure it's working in some environments just not mine.

The actual error is: Unknown column 'trigger_table.contact_id' in 'where clause'

This is happening when it's trying to populate the temp table to initially come up with all the summaries and then to put them into the right fields.

The problem is with the relationship queries. For some reason mysql isn't going down deep enough to see an inline alias or maybe it's just not correct. Maybe there is some mysql setting to make it do it. I didn't find anything.

Here is just one part of the overall query that fails on it's own with mysql 5.7.29. (sadly all the indents to make this easier to read get dropped and I'm too lazy to figure out how to add them back in)

SELECT contact_id, ( select coalesce(sum(total), 0) as total from ( select sum(total_amount) as total from ( select contact_id_a, r.relationship_type_id, r.is_active, ctrb.financial_type_id, ctrb.receive_date, ctrb.total_amount, ctrb.contribution_status_id from civicrm_relationship r inner join civicrm_contribution ctrb ON ctrb.contact_id = r.contact_id_b UNION ALL select contact_id_b, r.relationship_type_id, r.is_active, ctrb.financial_type_id, ctrb.receive_date, ctrb.total_amount, ctrb.contribution_status_id from civicrm_relationship r inner join civicrm_contribution ctrb ON ctrb.contact_id = r.contact_id_a UNION ALL select ctrb.contact_id, 0 as relationship_type_id, 1 as is_active, ctrb.financial_type_id, ctrb.receive_date, ctrb.total_amount, ctrb.contribution_status_id from civicrm_contribution ctrb ) t where t.relationship_type_id in (7, 0) and t.is_active and t.financial_type_id in (23,41,48,53,57,11,29,14,27,32,15,50,38,20,56,17,31,3,24,61,47,39,60,40,49,5,34,10,2,52,21,18,26,25,7,8,16,1,55,13,9) AND t.contribution_status_id = 1 AND contact_id_a = trigger_table.contact_id group by contact_id_a ) t ) FROM civicrm_contribution AS trigger_table GROUP BY contact_id

It fails on the AND contact_id_a = trigger_table.contact_id.
I played with this a bit and fixed it by removing one of the inline queries. The select sum... from (select sum...) isn't really needed I'm pretty sure.

Here is the same code that works with comments code commented out or changed and commented

SELECT contact_id, ( -- select coalesce(sum(total), 0) as total select coalesce(sum(total_amount), 0) as total -- changed total to total_amount -- from -- ( -- select sum(total_amount) as total from ( select contact_id_a, r.relationship_type_id, r.is_active, ctrb.financial_type_id, ctrb.receive_date, ctrb.total_amount, ctrb.contribution_status_id from civicrm_relationship r inner join civicrm_contribution ctrb ON ctrb.contact_id = r.contact_id_b UNION ALL select contact_id_b, r.relationship_type_id, r.is_active, ctrb.financial_type_id, ctrb.receive_date, ctrb.total_amount, ctrb.contribution_status_id from civicrm_relationship r inner join civicrm_contribution ctrb ON ctrb.contact_id = r.contact_id_a UNION ALL select ctrb.contact_id, 0 as relationship_type_id, 1 as is_active, ctrb.financial_type_id, ctrb.receive_date, ctrb.total_amount, ctrb.contribution_status_id from civicrm_contribution ctrb ) t where t.relationship_type_id in (7, 0) and t.is_active and t.financial_type_id in (23,41,48,53,57,11,29,14,27,32,15,50,38,20,56,17,31,3,24,61,47,39,60,40,49,5,34,10,2,52,21,18,26,25,7,8,16,1,55,13,9) AND t.contribution_status_id = 1 AND contact_id_a = trigger_table.contact_id group by contact_id_a ) t -- ) FROM civicrm_contribution AS trigger_table GROUP BY contact_id.

This same type structure appears a number of times in the relationship part of jsumfields.php. I've attached a fixed file.
jsumfields.php.txt You can diff it against the current file to see where it was changed. Oh, I also fixed the coalesce(sum, 0) thing, bug enhancement #3

twomice commented 4 years ago

Thanks for the technical detail @Paul-Tahoe . It will hep me a lot if you can also tell me:

Those are all needed to debug properly. Thank you!

Paul-Tahoe commented 4 years ago

Sorry. I did provide the mysql version. The civi, php and mysql version don't seem to matter but I should have included all of that anyway even though I'm 100% certain it's a sql code issue.

I've seen the error with: civi 5.9.1 (and I think prior), 5.21.1, 5.22.0) php 5.5.9, 7.2.24 mysql 5.5.62, 5.7.29 You should also want the summary fields as well Summary fields 3.1.3, 4.0.2 And maybe how I've configured summary fields and more summary fields. Since the error is with relationships and not including any of the related contributions makes the issue go away, I'll just say how that's configured. We've checked every Related Contribution Field available, picked a number of specific financial types and are using Relationship Type Household member of/Household is. (Though, you can probably see that in the sql I sent).

Reproduce: Anytime Summary Fields needs gen or regen all the summary fields. In this latest case, as the title says, upgrading summary fields. The upgrade to 4.0.2 requires a database upgrade, which results in that error. I also saw it with the previous upgrade. I ended up running the db upgrade from cv ext:upgrade-db which gave me the code it's barfing on. You get the same error pretty fast when the db upgrade is run from browser but from cv I get more on what it's unhappy with plus after I "fixed" the sql, it times out before finishing when running from the browser.

My guess is if you take the first chunk of sql or the simplified test code below and try running it in your environment from a sql prompt or phpmyadmin or whatever it will work. I can't see how it wouldn't. Otherwise, the related stuff would have never been tested. Since, I can boil it down to sql that is just coming from jsummaryfields.php and can run that sql just by itself in different versions of mysql and have it fail and I guess you don't, that seems to point to something about the mysql. So what database and version are you using?

I tried to find some mysql setting to no avail. In trying to figure out what was wrong with the sql, I've done a lot sql over the years with a inline views as you've had to do here, I realized the trigger_table.id is not getting passed into two depths of inline views. I find somethings saying you can only pass one level deep. I also realized one of those levels isn't really needed in this case.

Just to prove the one level deep thing I wrote some simple sql

select id,
      (select id
       from
             (select id
               from   civicrm_contact
               where  id = trigger_table.id
              ) as T1
        ) as T2 
from civicrm_contact as trigger_table
where trigger_table.external_identifier = 1  -- using something other than id so the error is obvious which where clause it's unhappy with.

and I get this error with running this 1054 - Unknown column 'trigger_table.id' in 'where clause'

It can't pass trigger_table.id into the derived table T2 and then on into T1

Now if I removed one depth

select id,
--      (select id
--       from
             (select id
               from   civicrm_contact
               where  id = trigger_table.id
              ) as T1
--        ) as T2 
from civicrm_contact as trigger_table
where trigger_table.external_identifier = 1  -- using something other than id so the error is obvious which where clause it's unhappy with.

it's happy.

One could probably add the reference to the outermost query and maybe pass that on into the inner one. I tried with my simple case but wasn't able to and since in your case the outermost query isn't needed or can be combined with the inner one, it isn't worth figuring out.

twomice commented 4 years ago

Thank you @Paul-Tahoe