Bug 8147 - Clicking on the "Recipes" link for a project displays no recipes
Summary: Clicking on the "Recipes" link for a project displays no recipes
Status: VERIFIED FIXED
Alias: None
Product: Toaster
Classification: Build System, Metadata & Runtime
Component: toaster (show other bugs)
Version: 1.9
Hardware: x86 x86_64
: Medium+ normal
Target Milestone: 2.0
Assignee: Michael Wood
QA Contact: Alexandru Roman
URL:
Whiteboard:
: 8292 (view as bug list)
Depends on:
Blocks:
 
Reported: 2015-08-11 10:26 UTC by Elliot Smith
Modified: 2015-09-30 13:30 UTC (History)
7 users (show)

See Also:
OS type for building Yocto: ---
Type of Regression: ---
Verified:
Documentation change: No (bug/feature does not impact docs)


Attachments
extract from Toaster log showing error thrown for recipes table (3.23 KB, text/x-log)
2015-08-11 10:26 UTC, Elliot Smith
no flags Details
Toaster web log showing "too many SQL variables" error (10.38 KB, text/x-log)
2015-08-25 16:52 UTC, Elliot Smith
no flags Details
Toaster command line showing recipe errors (19.12 KB, text/x-log)
2015-08-25 16:53 UTC, Elliot Smith
no flags Details
test program to print out some sqlite limit numbers. (661 bytes, application/octet-stream)
2015-08-25 21:54 UTC, brian avery
no flags Details
patch for sqlite to allow fedora to have enough variables (416 bytes, application/octet-stream)
2015-08-25 21:56 UTC, brian avery
no flags Details

Note You need to log in before you can comment on or make changes to this bug.
Description Elliot Smith 2015-08-11 10:26:18 UTC
Created attachment 2659 [details]
extract from Toaster log showing error thrown for recipes table

Preconditions/Environment
-------------------------

Toaster version:  poky-contrib / master (e47e1c4)

Triggering Action/Cause
-----------------------

1. Set up bitbake and Toaster as per instructions (https://www.yoctoproject.org/docs/1.8/toaster-manual/toaster-manual.html). I'm using a Python virtualenv to run Toaster.

2. Start Toaster in managed mode (from within virtualenv): TOASTER_MANAGED=1 source toaster start

(using SQLite as the database back-end)

3. Navigate to http://localhost:8000/, then create new project "test" with "Yocto Project master" as the release.

4. When created, click on the "Compatible Metadata" > "Recipes" link in the left-hand panel.

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
-----

An error appears in the toaster_web.log file:

OperationalError: too many SQL variables

I've attached an extract from the log with the traceback.
Comment 1 Elliot Smith 2015-08-13 13:12:26 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
Comment 2 Elliot Smith 2015-08-25 16:52:52 UTC
Created attachment 2680 [details]
Toaster web log showing "too many SQL variables" error
Comment 3 Elliot Smith 2015-08-25 16:53:19 UTC
Created attachment 2681 [details]
Toaster command line showing recipe errors
Comment 4 Elliot Smith 2015-08-25 17:27:20 UTC
(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.
Comment 5 brian avery 2015-08-25 21:48:37 UTC
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.
Comment 6 brian avery 2015-08-25 21:54:47 UTC
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
Comment 7 brian avery 2015-08-25 21:56:16 UTC
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
Comment 8 Elliot Smith 2015-08-26 09:15:45 UTC
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.
Comment 9 brian avery 2015-08-26 18:54:51 UTC
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!
Comment 10 Elliot Smith 2015-08-27 10:28:14 UTC
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
Comment 11 Elliot Smith 2015-09-07 10:50:20 UTC
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.
Comment 12 Belen Barros Pena 2015-09-08 09:16:25 UTC
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.
Comment 13 Michael Wood 2015-09-28 15:48:25 UTC
*** Bug 8292 has been marked as a duplicate of this bug. ***
Comment 14 Michael Wood 2015-09-30 10:14:44 UTC
I believe this is now fixed in master:

http://git.yoctoproject.org/cgit/cgit.cgi/poky/commit/?id=f902dc646dbfdf31fded69a425fb88944a7d8fe3

(Bitbake rev: 505979ab931e3a2a218d7030d6064987e8f9ff14)
Comment 15 Alexandru Roman 2015-09-30 13:30:49 UTC
Verified on master: 4bc3f0994e68b3302a0523a3156dd0dca0cac7a0