| Summary: | Clicking on the "Recipes" link for a project displays no recipes | ||
|---|---|---|---|
| Product: | [Build System, Metadata & Runtime] Toaster | Reporter: | Elliot Smith <elliot.smith> |
| Component: | toaster | Assignee: | Michael Wood <michael.g.wood> |
| Status: | VERIFIED FIXED | QA Contact: | Alexandru Roman <alexandru.costinx.roman> |
| Severity: | normal | ||
| Priority: | Medium+ | CC: | alexandru.costinx.roman, alexandru.damian, belen.barros.pena, brian.avery, cristiana.voicu, jessica.zhang, michael.g.wood |
| Version: | 1.9 | ||
| Target Milestone: | 2.0 | ||
| Hardware: | x86 | ||
| OS: | x86_64 | ||
| Whiteboard: | |||
| OS type for building Yocto: | --- | Type of Regression: | --- |
| Verified: | Documentation change: | No (bug/feature does not impact docs) | |
| Attachments: | |||
|
Description
Elliot Smith
2015-08-11 10:26:18 UTC
I have tried this on poky/master a16e0b40 with the same results.
Note that I'm using Python 2.7.5.
I added a line to django/db/backends/sqlite3/base.py to print out the SQL statements, and think this is the offending query:
SELECT COUNT(*) FROM "orm_recipe" WHERE ("orm_recipe"."layer_version_id" IN (SELECT "orm_layer_version"."id" FROM "orm_layer_version" LEFT OUTER JOIN "orm_branch" ON ( "orm_layer_version"."up_branch_id" = "orm_branch"."id" ) LEFT OUTER JOIN "orm_build" ON ( "orm_layer_version"."build_id" = "orm_build"."id" ) LEFT OUTER JOIN "orm_layersource" ON ( "orm_layer_version"."layer_source_id" = "orm_layersource"."id" ) LEFT OUTER JOIN "orm_releaselayersourcepriority" ON ( "orm_layersource"."id" = "orm_releaselayersourcepriority"."layer_source_id" ) WHERE ((("orm_branch"."name" = ? AND "orm_layer_version"."project_id" IS NULL) OR "orm_layer_version"."project_id" = ? OR "orm_build"."project_id" = ? ) AND ("orm_layer_version"."layer_source_id" IS NULL OR "orm_releaselayersourcepriority"."release_id" = ? ))) AND "orm_recipe"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ... (many more '?' characters omitted for brevity)
May be related to this Django issue: https://code.djangoproject.com/ticket/17788
Created attachment 2680 [details]
Toaster web log showing "too many SQL variables" error
Created attachment 2681 [details]
Toaster command line showing recipe errors
(In an attempt to make this easier to reproduce by others, I've covered the steps I used in more detail below.) Preconditions/Environment ------------------------- Toaster version: poky / master (15c8835) Environment: * Fedora 20 * SQLite3 version 3.8.10.1 2015-05-09 12:14:55 05b4b1f2a937c06c90db70c09890038f6c98ec40 * Python 2.7.5 * I haven't installed pysqlite or pysqlite2 in the virtualenv and don't have them on my system; I'm letting Django use sqlite3, with these versions: ** sqlite3.version = 2.6.0 ** sqlite3.sqlite_version = 3.8.10.1 The steps below cover setting up a virtualenv with these versions. Triggering Action/Cause ----------------------- Use these steps to try to reproduce the issue (they start from as clean a state as possible): # clone poky (I got it at commit 15c8835) mkdir ~/toaster-dev cd ~/toaster-dev git clone git@git.yoctoproject.org:poky poky # setup virtualenv mkdir ~/toaster-dev/toaster-run/ virtualenv ~/toaster-dev/toaster-run/toaster-env source ~/toaster-dev/toaster-run/toaster-env/bin/activate # (now inside the virtualenv in ~/toaster-run) # install toaster deps pip install -r ~/toaster-dev/poky/bitbake/toaster-requirements.txt # start toaster mkdir ~/toaster-dev/toaster-run/build cd ~/toaster-dev/toaster-run/build TOASTER_MANAGED=1 TOASTER_DEVEL=1 ~/toaster-dev/poky/bitbake/bin/toaster # answer first question "no" # second question, press "Enter" to accept default # third question, press "Enter" to accept default # fourth question, use ~/toaster-dev/toaster-run/build/toaster-build-artifacts # (expand the tilde) # when presented with the configuration file choice, select [1] # (in my case, this was /home/ell/toaster-dev/poky/meta-yocto/conf/toasterconf.json) # wait for it to start, then navigate to http://localhost:8000/ # create a new project called "project one" # set the release to "fido" # once created, click on the "Recipes" option in the left-hand menu Expectation ----------- List of compatible recipes is displayed. Actual Result ------------- No compatible recipes are shown, and the count in the table title remains empty - "Compatible recipes ()". Reproducibility --------------- 100% Notes ----- See attached Toaster log ('Toaster web log showing "too many SQL variables" error') for the error messages. I've also included the command line log ('Toaster command line showing recipe errors'), which contains numerous errors about recipes with duplicate unique fields, though I don't think this is relevant. I've done some investigation today, and the consensus seems to be that this is due to one or more of the limits in https://www.sqlite.org/limits.html being exceeded. However, other people in the team obviously aren't seeing the same thing, so I'm not sure if it's some limit just compiled into my version of SQLite. SQLite (by default) has a hard upper limit of 999 parameters set by SQLITE_LIMIT_VARIABLE_NUMBER, which everyone else should be exceeding (I *think* there are several thousand parameters to the query). Note that this issue can't be fixed easily by batching (as some of the comments in https://code.djangoproject.com/ticket/17788 suggest), as it occurs on a SELECT, not an INSERT or DELETE. Also note that I don't have this issue with MySQL. So, Here's the issue: There's a variable called SQLITE_MAX_VARIABLE_NUMBER. It is set at compile time for sqlite. By default it is set to 999 for the newer sqlites (3.8.10,11) On fedora 20 and 22 it is 999. On ubuntu 14.04 it is 250000. So, our query works on ubuntu (and maybe nowhere else?). I'm adding a short C file that can be run to determine the limits. compile it with gcc -o limits limits.c -lsqlite3 Test against installed: ./limits Test against built lib: LD_LIBRARY_PATH=/opt/lib ./limits Clearly, we need to fix this, though we are not alone in wishing it were bigger. As a short term workaround you can roll your own: 1) download the sqlite : https://www.sqlite.org/download.html l I used sqlite-autoconf-3081101.tar.gz ; unpack it. 2) change SQLITE_MAX_VARIABLE_NUMBER in sqlite3.c to 250000 (it was 999). Patch also attached. 3) ./configure --prefix=/opt 4) make install (make sure opt is writable or do sudo make install) 5) then to run toaster do: LD_LIBRARY_PATH=/opt/lib TOASTER_MANAGED=1 TOASTER_DEVEL=1 poky/bitbake/bin/toaster 6) you will se recipes. Created attachment 2682 [details]
test program to print out some sqlite limit numbers.
Make sure you have sqlite-devel (fedora) or libsqlite3-dev (ubuntu) installed. If the compile can't find sqlite3.h then you are missing the dev/devel package.
compile with
gcc -o limits limits.c -lsqlite3
Created attachment 2683 [details]
patch for sqlite to allow fedora to have enough variables
This is a temp fix for fedora people who need to use sqlite. build you own sqlite with the patch applied. It ups the max number of variables to 250000
Thanks Brian, this works for me. Do we want to fix this in Toaster? e.g. bundle our own SQLite (which seems excessive). Or should we just document the work-around? I don't think this can be fixed at runtime, as SQLite seems to prevent you raising the limit beyond the compile-time value. Nope, this can't be fixed at runtime, you're correct. The compile time value is a max and you can lower it but not increase it. Bundling our own SQLite probably isn't an acceptable option, nor is forcing users to build and use their own SQLite. Seems counter to how the rest of the project works. I need to better understand how the layers are being chosen before I know how we should solve this. I'm currently inclined to make a new table for the project recipes so that we don't have the horrendous SELECT IN statement. We would need to update it each time we hit it, i believe. Thoughts/comments welcome! We have an AvailableRecipe in the image customisation branch already, which may be relevant: http://git.yoctoproject.org/cgit/cgit.cgi/poky-contrib/commit/?h=elliot/toaster/ic-3&id=24445ec7edfbf84527ff9e42c8223dcf65db7e99 I did some further investigation of this issue, and tracked it down to a pair of queries in the Project class in models.py. In the case of the compatible recipes table, the query occurs in the get_all_compatible_recipes() method:
### start code
def get_all_compatible_recipes(self):
""" Returns QuerySet of all the compatible Recipes available to the
project including ones from Layers not currently added """
compatible_layerversions = self.compatible_layerversions()
queryset = Recipe.objects.filter(layer_version__in = compatible_layerversions)
# PROBLEM HERE
search_maxids = map(lambda i: i[0], list(queryset.values('name').distinct().annotate(max_id=Max('id')).values_list('max_id')))
queryset = queryset.filter(id__in=search_maxids).select_related('layer_version', 'layer_version__layer', 'layer_version__up_branch', 'layer_source')
return queryset
### end code
The problem is the search_maxids list being used to filter the queryset. This is what causes the "too many SQL variables" error, as that list contains thousands of items.
This is a very inefficient and inaccurate way of doing the filtering: what's happening is that distinct recipe names are paired with all the IDs associated with that name; then the recipe with the largest ID with that name is shown in the recipes table (the assumption being that the recipe added latest is the best one to show, which isn't always the case).
Rather than using this approach, the recipes shown should be the ones which are compatible with the project's release and layers.
*******
After chatting with Brian, we decided that it might be better to use the AvailableRecipe table as a replacement for the above query. This table tracks all recipes and whether or not they are compatible with the current project, which is what we want to base the compatible recipes table on.
However, the AvailableRecipe work is at the bottom of a large stack of changes for the image customisation feature (in elliot/toaster/ic-3), and currently being worked on by Michael Wood. We can't really do the work to fix how compatible recipes are found and stored until that work is in a decent state to be merged (even if not complete).
The AvailableRecipe table/model also needs some work to ensure it is correctly updated when the Recipe table or other related tables change. Alex was in the middle of this work when he left, and in discussion with Ed about it, and it was left in an uncertain and unfinished state.
Just a side note on how to collapse recipes. Recipes are duplicates when they have: * the same name * the same version / revision * and are provided by the same layer 2 recipes with the same name, version and revision provided by different layers should be shown as separate entries in the recipes table. This means that duplicate recipes should be always the result of build information. *** Bug 8292 has been marked as a duplicate of this bug. *** I believe this is now fixed in master: http://git.yoctoproject.org/cgit/cgit.cgi/poky/commit/?id=f902dc646dbfdf31fded69a425fb88944a7d8fe3 (Bitbake rev: 505979ab931e3a2a218d7030d6064987e8f9ff14) Verified on master: 4bc3f0994e68b3302a0523a3156dd0dca0cac7a0 |