DatabaseError: too many SQL variables
Hobica Bobjob
have 11,108 of these products entered (although that's just for the
test, the final version should hold around 35,000).
They are all in the same category. When I pull up the category, I get
the error "Too many SQL Variables".
Specifically, the problem occurs here:
python manage.py shell>
...
category = Category.objects.get_by_site(slug="myproduct")
product_list = list(category.active_products())
sale = find_best_auto_discount(product_list)
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/usr/local/lib/python2.6/dist-packages/Satchmo-0.9_2-py2.6.egg/product/utils.py",
line 35, in find_best_auto_discount
if len(discs) > 0:
File "/usr/lib/pymodules/python2.6/django/db/models/query.py", line
81, in __len__
self._result_cache = list(self.iterator())
File "/usr/lib/pymodules/python2.6/django/db/models/query.py", line
269, in iterator
for row in compiler.results_iter():
File "/usr/lib/pymodules/python2.6/django/db/models/sql/compiler.py",
line 672, in results_iter
for rows in self.execute_sql(MULTI):
File "/usr/lib/pymodules/python2.6/django/db/models/sql/compiler.py",
line 727, in execute_sql
cursor.execute(sql, params)
File "/usr/lib/pymodules/python2.6/django/db/backends/util.py", line
15, in execute
return self.cursor.execute(sql, params)
File "/usr/lib/pymodules/python2.6/django/db/backends/sqlite3/base.py",
line 200, in execute
return Database.Cursor.execute(self, query, params)
DatabaseError: too many SQL variables
How would I go about fixing this?
Thanks in advance!
On 9/6/10, satchmo-us...@googlegroups.com
<satchmo-us...@googlegroups.com> wrote:
> =============================================================================
> Today's Topic Summary
> =============================================================================
>
> Group: satchm...@googlegroups.com
> Url: http://groups.google.com/group/satchmo-users/topics
>
> - Users are only able to post one comment each per 'product' [1 Update]
> http://groups.google.com/group/satchmo-users/t/84ba3b812da1bed4
>
>
> =============================================================================
> Topic: Users are only able to post one comment each per 'product'
> Url: http://groups.google.com/group/satchmo-users/t/84ba3b812da1bed4
> =============================================================================
>
> ---------- 1 of 1 ----------
> From: Alex Robbins <alexander...@gmail.com>
> Date: Sep 05 10:16PM -0500
> Url: http://groups.google.com/group/satchmo-users/msg/ddaf6e282babb64f
>
> My guess is that this has something to do with the productratings
> module. I think it makes sure that each user can only rate items one
> time. I don't remember if it uses comment moderation or a signal
> handler. It has been a little while since I looked at the code.
>
> Alex
>
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Satchmo users" group.
> To post to this group, send email to satchm...@googlegroups.com.
> To unsubscribe from this group, send email to
> satchmo-user...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/satchmo-users?hl=en.
>
>
Shaun Laughey
I'd first look at your design. Depending on the database being used you'll likely be hitting a limit on the width of a row or the size of a query. Every database has different limits some more than others but all have limits to the size of queries and the size of rows returned.
If you want to share more detail about these extra fields and why there are so many we might be able to suggest other approaches.
Regards,
Shaun Laughey.
hobi
The product is a diamond, which has tons of fields, all of which
matter to consumers or sellers. It includes the obvious, like cut/
clarity/cost/carat, the less obvious like pavilion angle, measurement
length, width, height, to things that the seller needs to know, such
as which supplier the stone came from, what it's ID is, how much it's
discount is from list price, etc.
I could probably reduce it by 8 fields if I wanted to be terse.
I can remove the following, but I'd rather not:
girdle (I have this split into two fields but I keep a copy of the
original string), measurements (same story as with girdle),
availability (tested to be True before being inserted to the stock
list), matched pair separable (also tested as true), pair stock #,
parcel number of stones
And I can stuff a few of them into one field:
seller code+seller name, city+state+country
Also I could separate fancy colored and white diamonds, because they
have a few different features which are mutually exclusive, a savings
of 2 fields for white, and 1 field for fancy.
I have alot of the fields as foreignkeys, the numbers are Decimal with
4 digits, but the strings are all set to 255 characters. Would
reducing string size be worthwhile?
I've already written a custom view so that I can try to get around
whatever huge SQL statement find_best_auto_discount() is generating
(or maybe exclude it, I don't even know what that function does yet, I
haven't had time since I posted the question). Perhaps the SQL can be
split into two? Or maybe reduce the result set field size? I assume
that find_best_auto_discount() doesn't need every field. Am I right?
I'm using sqlite3, with plans on moving to mysql when it's closer to
completion.
Thanks again
On Sep 24, 2:52 am, Shaun Laughey <shaun.laug...@gmail.com> wrote:
> Hi
>
> I'd first look at your design. Depending on the database being used you'll
> likely be hitting a limit on the width of a row or the size of a query.
> Every database has different limits some more than others but all have
> limits to the size of queries and the size of rows returned.
>
> If you want to share more detail about these extra fields and why there are
> so many we might be able to suggest other approaches.
>
> Regards,
> Shaun Laughey.
>
> > <satchmo-us...@googlegroups.com<satchmo-users%2Bno...@googlegroups.com>>
>
> > =============================================================================
> > > Today's Topic Summary
>
> > =============================================================================
>
> > > Group: satchm...@googlegroups.com
> > > Url:http://groups.google.com/group/satchmo-users/topics
>
> > > - Users are only able to post one comment each per 'product' [1 Update]
> > > http://groups.google.com/group/satchmo-users/t/84ba3b812da1bed4
>
> > =============================================================================
> > > Topic: Users are only able to post one comment each per 'product'
> > > Url:http://groups.google.com/group/satchmo-users/t/84ba3b812da1bed4
>
> > =============================================================================
>
> > > ---------- 1 of 1 ----------
> > > Url:http://groups.google.com/group/satchmo-users/msg/ddaf6e282babb64f
>
> > > My guess is that this has something to do with the productratings
> > > module. I think it makes sure that each user can only rate items one
> > > time. I don't remember if it uses comment moderation or a signal
> > > handler. It has been a little while since I looked at the code.
>
> > > Alex
>
> > > --
> > > You received this message because you are subscribed to the Google Groups
> > > "Satchmo users" group.
> > > To post to this group, send email to satchm...@googlegroups.com.
> > > To unsubscribe from this group, send email to
> > .
> > >http://groups.google.com/group/satchmo-users?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Satchmo users" group.
> > To post to this group, send email to satchm...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > .
Alex Robbins
"The default setting for SQLITE_MAX_COLUMN is 2000. You can change it
at compile time to values as large as 32767. On the other hand, many
experienced database designers will argue that a well-normalized
database will never need more than 100 columns in a table. "
There are a lot of other limits on that page, maybe you are running
into one of them. I'd switch to mysql now, since you are planning to
do that anyway and see if it fixes it. (PostgreSQL is the new hotness,
you might try it instead, but MySQL works)
Alex
> To unsubscribe from this group, send email to satchmo-user...@googlegroups.com.
John-Scott Atlakson
If you notice in your stack trace, the last line before it hits the db is:
"""
File "/usr/local/lib/python2.6/dist-packages/Satchmo-0.9_2-py2.6.egg/product/utils.py",
line 35, in find_best_auto_discount
if len(discs) > 0:
"""
def find_auto_discounts(product):
if not type(product) in (types.ListType, types.TupleType):
product = (product,)
today = datetime.date.today()
discs = Discount.objects.filter(automatic=True, active=True, startDate__lte=today, endDate__gt=today)
return discs.filter(Q(valid_products__in=product) | Q(allValid=True)).order_by('-percentage')
def find_best_auto_discount(product):
discs = find_auto_discounts(product)
if len(discs) > 0:
return discs[0]
else:
return None
Once "len(discs)" is called in ``find_best_auto_discount``, that evaluates the QuerySet.
In the last line of ``find_auto_discounts`` we have this filter: "Q(valid_products__in=product)"
In your case, you are passing in 11,108 product objects.
This will result in a query like "SELECT * FROM foo WHERE id IN ( LIST OF 11,108 values);".
And your database is telling you that is far too many SQL variables.
I don't know what the solution is for you, but you might reconsider having all of your products in a single category.
Perhaps white diamond and a fancy colored would be good choices for a couple of categories. But it's your business, you'd know better than me how to organize your products. Just saying that if all products are in a single category and you are using Satchmo's category_view, you won't be able to get around this huge query. Viewing by category wouldn't even make sense in this case.
Cheers,
John-Scott
To unsubscribe from this group, send email to satchmo-user...@googlegroups.com.