+++ This bug was initially created as a clone of Bug #880829 +++ After the migration happened in Bug 880829, BMO returns the following internal database error: > DBD::mysql::db selectall_arrayref failed: Too many tables; > MariaDB can only use 61 tables in a join [for Statement ... ] Example: https://bugzilla.mozilla.org/buglist.cgi?bug_id=880829&columnlist=all https://api-dev.bugzilla.mozilla.org/1.3/bug?include_fields=_default&id=880829

Sigh. The issue is that the tracking flags before migration were all columns in the normal bugs table and did not need to be joined when getting the values for them in buglist.cgi. Now that they are in separate mapping tables they need to be joined to get the same data into the results. And we have a lot of tracking flags that need to be returned when using columnlist=all. This is hitting MySQL's hard coded limit of 61 JOINs. We will need to rework the search backend code to either: 1) use sub selects for the columns to get the current value for each tracking flag 2) or break the larger query into two or more separate queries and union the results when the number of tracking flags exceeds a certain amount. Either one will need time to implement and push. You can switch to using the native REST API search for this which doesn't have the issue you are seeing at the moment. https://bugzilla.mozilla.org/rest/bug?id=880829 Will see what I can do. dkl

Also is there a specific reason to be using columnlist=all instead of picking the columns you really need? This may be an issue that will require limiting the number of columns returned by columnlist=all to less that 61. This issue is not specific to Tracking Flags as if a Bugzilla instance has more than 50-60 multi select custom fields, the same issue would surface in MySQL and probably other DB's as well. dkl

(In reply to David Lawrence [:dkl] from comment #1) > You can switch to using the native REST API search for this which doesn't > have the issue you are seeing at the moment. I didn't know that the native REST API was already available. Will update my app to use it. (In reply to David Lawrence [:dkl] from comment #2) > Also is there a specific reason to be using columnlist=all instead of > picking the columns you really need? I was just using the include_fields=_default option of the BzAPI as mentioned in my comment 0.

(In reply to Kohei Yoshino [:kohei] from comment #3) > I was just using the include_fields=_default option of the BzAPI as > mentioned in my comment 0. Yeah after looking at the BzAPI code I realize that BzAPI is making the call to Bugzilla with columnlist=all by default for most searches. It will ask for all columns if _default or _all or no include_fields is provided. So to make it work on your end you could specify only the columns you need by using for example, include_fields=id,status,summary or whichever fields you need. dkl

Patch that skips tracking flag columns when columnlist=all as a temp workaround until we can either 1) Figure out a different way of creating the tracking flag table joins that minimizes the number of joins needed, or 2) fix BzAPI to stop requesting columnlist=all and to switch to using the native REST API bug search method. Another thing to note is I have not yet found anywhere in the Bugzilla code itself that passes columnlist=all and am not sure it is even needed or desired. If a user wants to select specific columns thn can highlight all the ones they need in colchange.cgi. dkl

(In reply to David Lawrence [:dkl] from comment #5) > Created attachment 816305 [details] [diff] [review] > 926109_1.patch > > Patch that skips tracking flag columns when columnlist=all as a temp > workaround until we can either 1) Figure out a different way of creating the > tracking flag table joins that minimizes the number of joins needed, or 2) > fix BzAPI to stop requesting columnlist=all and to switch to using the > native REST API bug search method. > > Another thing to note is I have not yet found anywhere in the Bugzilla code > itself that passes columnlist=all and am not sure it is even needed or > desired. > If a user wants to select specific columns thn can highlight all the ones > they need in colchange.cgi. Gerv, can you comment on the feasibility of option 2) in the above recommendations? Native REST API supports full search now and buglist.cgi?columnlist=all&ctype=csv is no longer necessary IMO. dkl

I am checking in my patch for now as all BzAPI searches are broken at the moment and I need to get a push scheduled as soon as possible. Glob, if you see a better solution before I can get more time to work on this please feel free to commit a change and schedule a new push. We can address this on BzAPI as well in the beginning of the week. Committing to: bzr+ssh://dlawrence%40mozilla.com@bzr.mozilla.org/bmo/4.2 modified buglist.cgi Committed revision 9060. dkl

Excluding these fields from columnlist=all (presuming they can still be specifically requested) seems like a good interim step. After the weekend I will look at a longer-term fix. (Requesting columnlist=all used to be merely saying "everything in the bugs table" and so was not a significant performance issue. Which is why we thought it would be OK for the default field set to be "everything returnable by a search".) Gerv

Comment on attachment 816305 [details] [diff] [review] 926109_1.patch clearing review request on this patch as it has already been committed.

(In reply to Gervase Markham [:gerv] from comment #8) > Requesting columnlist=all used to be merely saying "everything in the bugs > table" and so was not a significant performance issue. this has never been the case; "all" includes many fields which require joining to other tables (product/component name, flags, single and multi-select fields, etc).

this patch removes the tracking flags from the query and replaces the values with a "---" placeholder. after the main search query has been run, it runs a new query which retrieves the tracking flags values and injects them into the results. i set a cutoff at 30 fields .. if 30 or fewer tracking flags are requested the current method using joins is employed; more than 30 will result in the new method. there is a performance hit for this post-processing, but it's better than failing completely.

Comment on attachment 816615 [details] [diff] [review] 926109_2.patch Review of attachment 816615 [details] [diff] [review]: ----------------------------------------------------------------- Code looks fine overall but this is causing timeout locally. How fast was this returning on your dev environment?

(In reply to David Lawrence [:dkl] from comment #12) > Code looks fine overall but this is causing timeout locally. How fast was > this returning on your dev environment? there wasn't any sizeable performance difference. can you provide a query which timed out?

Comment on attachment 816615 [details] [diff] [review] 926109_2.patch Review of attachment 816615 [details] [diff] [review]: ----------------------------------------------------------------- r=dkl

(In reply to David Lawrence [:dkl] from comment #6) > Gerv, can you comment on the feasibility of option 2) in the above > recommendations? Native REST API supports full search now and > buglist.cgi?columnlist=all&ctype=csv is no longer necessary IMO. Are there docs anywhere for the Native REST API's search method parameters? http://www.bugzilla.org/docs/tip/en/html/api/Bugzilla/WebService/Bug.html#search is still the old stuff, so I assume it hasn't made it to Bugzilla's trunk yet. If you can point me at docs, and a method of performantly deciding whether to use REST or CSV (it's not immediately obvious how to do that) I can certainly look at enhancing BzAPI to optionally use the new REST. I think that, in the long term, it needs to be possible to do the equivalent of a columnlist=all search in Bugzilla's UI - that's a feature which is independent of BzAPI, and I don't think it's great to break it. But I can see this will require work. (I note glob's patch.) In the mean time, I could make the same fix on BzAPI that you made on Bugzilla - exclude custom fields from the _default set. But given that you've made the fix, I can't see much point in making it on BzAPI instead and backing i out from Bugzilla. Can you think of one? Gerv

in our bmo meeting we decided that given how close we are to the bzapi compatibility layer, refactoring bzapi to use native rest wouldn't be a good use of time. Committing to: bzr+ssh://bjones%40mozilla.com@bzr.mozilla.org/bmo/4.2/ modified buglist.cgi modified Bugzilla/Search.pm modified extensions/TrackingFlags/Extension.pm modified extensions/TrackingFlags/lib/Flag.pm Committed revision 9073.

The native REST API is not compatible with BzAPI and there is still room for improvement, therefore my app continues to use BzAPI for now. The only obvious advantage of using the native API is that it exposes full email addresses of people involved. I don't know why BzAPI doesn't. Anyways I'll file feature requests.

kohei: in general, BzAPI can only return the data Bugzilla gives it; if you aren't getting full email addresses, it's because Bugzilla isn't giving them to BzAPI. Try logging in to an account - that may well help. Gerv

The native API returns full email addresses without having to log-in. BzAPI doesn't. I think it's a "bug" of BzAPI.

Just checked the source of BzAPI. BzAPI returns a result from https://bugzilla.mozilla.org/show_bug.cgi?id=926109&ctype=xml instead of https://bugzilla.mozilla.org/rest/bug/926109 That's why full email addresses cannot be retrieved.

bzapi's return email isn't related to this issue; file a bug in the bzapi component if you feel that issue needs attention.

(In reply to Kohei Yoshino [:kohei] from comment #20) > Just checked the source of BzAPI. > > BzAPI returns a result from > https://bugzilla.mozilla.org/show_bug.cgi?id=926109&ctype=xml > instead of https://bugzilla.mozilla.org/rest/bug/926109 > > That's why full email addresses cannot be retrieved. There is a setting to filter email address in webservice methods that BMO does not have enabled at this time. So api calls to BMO will show full addresses whether you are logged in or not. Since bzapi is not using the webservice directly for bug results, and instead using the templatized version of the buglist.cgi output, the email addresses are filtered based on whether you are logged in. dkl