Bug#809211: tracker.debian.org: sqlite backend not usable

[Christophe Siraut]

Hi,

Raphael Hertzog wrote:
> Also does this also happen if you drop debug_toolbar from INSTALLED_APPS ?

It still happens without debug_toolbar.

> I have the feeling that this code path is only used when you actually want
> to print the SQL query that has been executed and this is usally only
> needed during development.
> 
> The regression has been introduced by this commit:
> 
> https://github.com/django/django/commit/4f6a7663bcddffb114f2647f9928cbf1fdd8e4b5
> 
> commit 4f6a7663bcddffb114f2647f9928cbf1fdd8e4b5
> Author: Aymeric Augustin 
> Date:   Sun Sep 13 09:30:35 2015 +0200
> 
> Refs #14091 -- Fixed connection.queries on SQLite.
> 
> It's supposed to fix https://code.djangoproject.com/ticket/14091 for
> SQLite. Christophe, can you verify that the issue is gone if you revert
> that change in your Django ?

Yes, reverting that one fixes my issue.

Thanks,
Christophe

Bug#809211: tracker.debian.org: sqlite backend not usable

[Raphael Hertzog]

Hi,

On Fri, 08 Jan 2016, Aymeric Augustin wrote:
> I didn’t realize SQLITE_MAX_COLUMN would be the limit here.
> 
> Would you mind opening a ticket on https://code.djangoproject.com/?

Done: https://code.djangoproject.com/ticket/26063

Cheers,
-- 
Raphaël Hertzog ◈ Debian Developer

Support Debian LTS: http://www.freexian.com/services/debian-lts.html
Learn to master Debian: http://debian-handbook.info/get/

Bug#809211: tracker.debian.org: sqlite backend not usable

[Aymeric Augustin]

Thanks!

I submitted a patch: https://github.com/django/django/pull/5963

If the reporter could test it and confirm that it fixes the issue, that would 
be great.

-- 
Aymeric.

Bug#809211: tracker.debian.org: sqlite backend not usable

[Aymeric Augustin]

Hello Raphael,

Thanks for a great analysis. I can confirm this is a bug in Django. 

I didn’t realize SQLITE_MAX_COLUMN would be the limit here.

Would you mind opening a ticket on https://code.djangoproject.com/?

(Alternatively I can do it.)

Best regards,

-- 
Aymeric.

> On 8 janv. 2016, at 10:55, Raphael Hertzog  wrote:
> 
> [ CCing upstream Django developer to have its opinion ]
> 
> Hello,
> 
> On Fri, 08 Jan 2016, Christophe Siraut wrote:
>> Sorry for the confusion, I have the same libsqlite3 version as you, and
>> it is that one I recompiled with SQLITE_MAX_COLUMN 32767.
> 
> I can't reproduce the issue with Django 1.8. It looks like a regression of
> Django 1.9 that uses an SQL request to generate quoted values for the
> parameters fed into requests of the from "WHERE foo IN (...value
> list...)".
> 
> Also does this also happen if you drop debug_toolbar from INSTALLED_APPS ?
> I have the feeling that this code path is only used when you actually want
> to print the SQL query that has been executed and this is usally only
> needed during development.
> 
> The regression has been introduced by this commit:
> 
> https://github.com/django/django/commit/4f6a7663bcddffb114f2647f9928cbf1fdd8e4b5
> 
> commit 4f6a7663bcddffb114f2647f9928cbf1fdd8e4b5
> Author: Aymeric Augustin 
> Date:   Sun Sep 13 09:30:35 2015 +0200
> 
>Refs #14091 -- Fixed connection.queries on SQLite.
> 
> It's supposed to fix https://code.djangoproject.com/ticket/14091 for
> SQLite. Christophe, can you verify that the issue is gone if you revert
> that change in your Django ?
> 
> Aymeric, the above change is problematic because for a request that
> looks like MyModel.objects.filter(foo__in=my_array) it will break as soon
> as my_array goes above 2000 entries in length since that's the default
> limit for SQLITE_MAX_COLUMN and you will trigger it with your huge
> "SELECT QUOTE(?)," query.
> 
> I know the query can also trigger the limit set with
> SQLITE_MAX_VARIABLE_NUMBER which defaults to 999 but at least on Debian
> we increased that limit significantly (to 25) so that limit
> is not a practical problem. The above limit is more problematic
> as we can't increase it to the same value (the max is 32767) and
> while the limit on the variable number was unreasonably low, I can
> understand the limit on the number of columns much better.
> 
> See https://sqlite.org/limits.html
> 
> Aymeric, what do you think ? Shall we open a ticket for this regression ?
> 
> Can you update the code to process parameters by batch of less than 2000
> entries ?
> 
> You can look at https://bugs.debian.org/809211 for the former discussion
> in this bug report.
> 
> Cheers,
> -- 
> Raphaël Hertzog ◈ Debian Developer
> 
> Support Debian LTS: http://www.freexian.com/services/debian-lts.html
> Learn to master Debian: http://debian-handbook.info/get/

Bug#809211: tracker.debian.org: sqlite backend not usable

[Raphael Hertzog]

[ CCing upstream Django developer to have its opinion ]

Hello,

On Fri, 08 Jan 2016, Christophe Siraut wrote:
> Sorry for the confusion, I have the same libsqlite3 version as you, and
> it is that one I recompiled with SQLITE_MAX_COLUMN 32767.

I can't reproduce the issue with Django 1.8. It looks like a regression of
Django 1.9 that uses an SQL request to generate quoted values for the
parameters fed into requests of the from "WHERE foo IN (...value
list...)".

Also does this also happen if you drop debug_toolbar from INSTALLED_APPS ?
I have the feeling that this code path is only used when you actually want
to print the SQL query that has been executed and this is usally only
needed during development.

The regression has been introduced by this commit:

https://github.com/django/django/commit/4f6a7663bcddffb114f2647f9928cbf1fdd8e4b5

commit 4f6a7663bcddffb114f2647f9928cbf1fdd8e4b5
Author: Aymeric Augustin 
Date:   Sun Sep 13 09:30:35 2015 +0200

Refs #14091 -- Fixed connection.queries on SQLite.

It's supposed to fix https://code.djangoproject.com/ticket/14091 for
SQLite. Christophe, can you verify that the issue is gone if you revert
that change in your Django ?

Aymeric, the above change is problematic because for a request that
looks like MyModel.objects.filter(foo__in=my_array) it will break as soon
as my_array goes above 2000 entries in length since that's the default
limit for SQLITE_MAX_COLUMN and you will trigger it with your huge
"SELECT QUOTE(?)," query.

I know the query can also trigger the limit set with
SQLITE_MAX_VARIABLE_NUMBER which defaults to 999 but at least on Debian
we increased that limit significantly (to 25) so that limit
is not a practical problem. The above limit is more problematic
as we can't increase it to the same value (the max is 32767) and
while the limit on the variable number was unreasonably low, I can
understand the limit on the number of columns much better.

See https://sqlite.org/limits.html

Aymeric, what do you think ? Shall we open a ticket for this regression ?

Can you update the code to process parameters by batch of less than 2000
entries ?

You can look at https://bugs.debian.org/809211 for the former discussion
in this bug report.

Cheers,
-- 
Raphaël Hertzog ◈ Debian Developer

Support Debian LTS: http://www.freexian.com/services/debian-lts.html
Learn to master Debian: http://debian-handbook.info/get/

Bug#809211: tracker.debian.org: sqlite backend not usable

[Raphael Hertzog]

Hi,

On Mon, 28 Dec 2015, Christophe Siraut wrote:
> I am trying to run tracker configured with debian repositories using the 
> sqlite
> backend, but several tasks are giving:
> 
>   OperationalError: too many columns in result set

Weird, the limit is at 2000 columns by default and I don't think that we
have that many fields in the entire codebase?

What version of sqlite do you have?

It would be interesting to be able to analyze the queries which are giving
this error...

Cheers,
-- 
Raphaël Hertzog ◈ Debian Developer

Support Debian LTS: http://www.freexian.com/services/debian-lts.html
Learn to master Debian: http://debian-handbook.info/get/

Bug#809211: tracker.debian.org: sqlite backend not usable

[Christophe Siraut]

Package: tracker.debian.org
Severity: normal

I am trying to run tracker configured with debian repositories using the sqlite
backend, but several tasks are giving:

  OperationalError: too many columns in result set

Tasks that are frequently failing:

  UpdateRepositoriesTask
  UpdatePackageBugStats
  UpdateLintianStatsTask
  UpdateExcusesTask
  UpdateBuildLogCheckStats
  DebianWatchFileScannerUpdate
  UpdateSecurityIssuesTask
  UpdateUbuntuStatsTask
  UpdateWnppStatsTask

I have tried the following with little success:

  Use a small subset of repositories
  Recompile libsqlite with #define SQLITE_MAX_COLUMN 32767
  Hack the querysets like 
http://stackoverflow.com/questions/7106016/too-many-sql-variables-error-in-django-witih-sqlite3

I think sqlite does not scale and we should not recommend it, in the setting 
files:

  # If you want to run a development setup close to what's running on
  # tracker.debian.org, then uncomment the next two lines.
  from .debian import *
- from .db_sqlite import DATABASES
+ from .db_postgresql import DATABASES

and remove db_sqlite.py.

Note we have a warning in the documentation:

  Distro Tracker does not rely on any database specific features and as such 
should be
  able to run on top of any database server. The only possible known issue is 
when
  using sqlite3 which has a limit on the number of query parameters of 999 on
  some systems.

Cheers,
Christophe