# Error when 'prodigy drop'

**URL:** <https://support.prodi.gy/t/error-when-prodigy-drop/928>\
**Category:** Uncategorized\
**Tags:** done, database\
**Created:** [October 30, 2018, 2:14am UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928 "2018-10-30T02:14:41Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Arul](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@Arul](https://support.prodi.gy/u/Arul)\
**Post date:** [October 30, 2018, 2:14am UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/1 "2018-10-30T02:14:41Z")

</div>

I get this error when i try to ‘drop’ the db.  
“prodigy drop equinor\_split\_20p”

```
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: too many SQL variables

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/runpy.py", line 193, in _run_module_as_main
    " __main__", mod_spec)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/prodigy/ __main__.py", line 251, in <module>
    plac.call(commands[command], arglist=args, eager=False)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/plac_core.py", line 328, in call
    cmd, result = parser.consume(arglist)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/plac_core.py", line 207, in consume
    return cmd, self.func(*(args + varargs + extraopts), **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/prodigy/ __main__.py", line 148, in drop
    dropped = DB.drop_dataset(set_id)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/prodigy/components/db.py", line 354, in drop_dataset
    if len(link_examples) == 0:
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/peewee.py", line 3298, in __len__
    return len(self.execute())
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/peewee.py", line 3274, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/peewee.py", line 2939, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/peewee.py", line 3837, in execute_sql
    self.commit()
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/peewee.py", line 3656, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/peewee.py", line 135, in reraise
    raise value.with_traceback(tb)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: too many SQL variables

```

When i say “prodigy stats -l” i can still see the name. if i copy-paste the same name in ‘drop’ to delete it, i get this error.

---

<div class="post-metadata">

**Author:** ![spreston](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/spreston/32/537_2.png) [@spreston](https://support.prodi.gy/u/spreston)\
**Post date:** [November 19, 2018, 9:21pm UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/2 "2018-11-19T21:21:07Z")

</div>

I’m seeing this same error, did you figure out what the problem was?

---

<div class="post-metadata">

**Author:** ![Arul](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@Arul](https://support.prodi.gy/u/Arul)\
**Post date:** [November 19, 2018, 9:32pm UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/3 "2018-11-19T21:32:02Z")

</div>

No… I have noticed that it happens in mac. Does not occur in Unix systems. But i didn’t find a solution yet.

---

<div class="post-metadata">

**Author:** ![spreston](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/spreston/32/537_2.png) [@spreston](https://support.prodi.gy/u/spreston)\
**Post date:** [November 19, 2018, 9:47pm UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/4 "2018-11-19T21:47:06Z")

</div>

Ah, yes I'm on a mac too.

It looks like it's related to the `SQLITE_MAX_VARIABLE_NUMBER` limit, which on my machine is set to 999 -- this is around the number of training examples where dropping a dataset gives an error. It's discussed a bit in this answer:

> <https://stackoverflow.com/questions/35616602/peewee-operationalerror-too-many-sql-variables-on-upsert-of-only-150-rows-8-c>

but I don't have a fix for it.

---

<div class="post-metadata">

**Author:** ![Arul](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@Arul](https://support.prodi.gy/u/Arul)\
**Post date:** [November 19, 2018, 10:09pm UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/5 "2018-11-19T22:09:20Z")

</div>

Hmm… this could be a bug. it works for some datasets and does not work for some. It doesn’t break for all of it though. May be i should check for the size of datasets.

---

<div class="post-metadata">

**Author:** ![Arul](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@Arul](https://support.prodi.gy/u/Arul)\
**Post date:** [November 19, 2018, 10:52pm UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/6 "2018-11-19T22:52:16Z")

</div>

Ok, This seems to be a bug. happening from db.py of the prodigy/components.  
In line 354, it is checking len(link\_examples) for zero and non-zero for the contents. but the parameter ‘len’ does not seem to work for that class \<class ‘prodigy.components.db.Link’\>. (that class “Link” does not have an overriding method for ‘len’ too) It might work if that is a list. I have not dug into why it works on unix though. maybe it is available in a different python version?  
@ines @honnibal - is this really a bug?

---

<div class="post-metadata">

**Author:** ![ines](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/ines/32/3_2.png) [@ines](https://support.prodi.gy/u/ines)\
**Post date:** [November 20, 2018, 11:36am UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/7 "2018-11-20T11:36:47Z")

</div>

Thanks so much for the detailed analysis!

If this indeed comes down to the `SQLITE_MAX_VARIABLE_NUMBER`, we could probably have a workaround for this. From the StackOverflow thread, it sounds like simply doing it in several batches is a solution? So in `drop_examples` and/or `drop_examples` in the `db.py`, you could try splitting the items to delete in batches of \< 999 and then make several passes over them to delete them (instead of doing it all in one).

---

<div class="post-metadata">

**Author:** ![spreston](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/spreston/32/537_2.png) [@spreston](https://support.prodi.gy/u/spreston)\
**Post date:** [November 25, 2018, 3:18am UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/8 "2018-11-25T03:18:38Z")

</div>

yes, I believe that would fix the errors on mac, presumably the default is set (much?) higher on unix. I’m going to try to get some time next week to test and make sure it fixes the issue.

---

<div class="post-metadata">

**Author:** ![ines](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/ines/32/3_2.png) [@ines](https://support.prodi.gy/u/ines)\
**Post date:** [November 25, 2018, 2:08pm UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/9 "2018-11-25T14:08:04Z")

</div>

Okay, turns out batching up the records definitely seems to fix this. So we’ll have a patch for this in the upcoming version of Prodigy 🙂

---

<div class="post-metadata">

**Author:** ![claudio84destri](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/claudio84destri/32/242_2.png) [@claudio84destri](https://support.prodi.gy/u/claudio84destri)\
**Post date:** [November 28, 2018, 9:12am UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/10 "2018-11-28T09:12:32Z")

</div>

> [@ines](#):
>
> \< 999

Hi,

I am getting the same error in a windows machine with prodigy version 1.6.1, which was not present in version 1.5.1

```
Traceback (most recent call last):
  File "C:\Users\sa064014\AppData\Local\Continuum\anaconda3\lib\site-packages\peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: too many SQL variables

```

I am currently using another python environment with Prodigy 1.5.1 to drop datrasets

thank you in advance  
kind regards  
claudio

---

<div class="post-metadata">

**Author:** ![SwarupSelvaraj](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/swarupselvaraj/32/577_2.png) [@SwarupSelvaraj](https://support.prodi.gy/u/SwarupSelvaraj)\
**Post date:** [December 26, 2018, 7:00pm UTC](https://support.prodi.gy/t/error-when-prodigy-drop/928/11 "2018-12-26T19:00:06Z")

</div>

Hello Everyone!

We can replace the drop\_dataset function in db.py located at

> …/prodigy/components/db.py

PS: Ofcourse, as always it is good to have a database backup prior to testing

```python
    def drop_dataset(self, name):
        """
        name (unicode): The name of the dataset to drop.
        RETURNS (bool): True if dataset was dropped.
        """
        with self.db.atomic():
            log("DB: Dropping dataset '{}'".format(name))
            dataset = Dataset.get(Dataset.name == name)
            links = Link.select().where(Link.dataset_id == dataset.id)
            found_datasets = [dataset.id]
			
            # Grab all the example ids to be deleted
            original_example_ids = Link.select(Link.example_id).where(Link.dataset_id == dataset.id)

            # Grab the list of link ids to be deleted (same dataset and linked datasets sharing the same examples)
            link_others = Link.select(Link.id).where(Link.example_id << original_example_ids).select(Link.id).where(Link.dataset_id != dataset.id)
            link_self = Link.select(Link.id).where(Link.example_id << original_example_ids).select(Link.id).where(Link.dataset_id == dataset.id)

            # Grab the list of datasets ids sharing the examples
            for ds in Link.select().where(Link.example_id << original_example_ids).select(Link.dataset_id).distinct().where(Link.dataset_id != dataset.id):
                found_datasets.append(ds.dataset_id)

            # Delete all the matching examples
            Example.delete().where(Example.id << original_example_ids).execute()
            
            # Delete all the matching links from other datasets
            Link.delete().where(Link.id << link_others).execute()

            # Delete all the matching links from selected dataset
            Link.delete().where(Link.id << link_self).execute()
            
            # Delete the datasets without any links
            for ds in found_datasets:
                if Link.select(Link.id).where(Link.dataset_id == ds).count() == 0 :
                    Dataset.delete().where(Dataset.id == ds).execute()
            
            self.db.commit()
            log("DB: Removed dataset '{}'".format(name))
        return True

```

Note:

The following order of deletion must be followed (_unless you plan on cutting the branch you are sitting on_)

1. Example
2. Link (other datasets sharing the same examples)
3. Link (selected dataset)
4. Dataset (selected and other datasets if they are empty)

We can also clean the prodigy database (with unreferenced examples/datasets), by connecting to prodigy db using **SQLite studio** and executing the following query

```python
    delete from link where link.id in (SELECT id FROM link GROUP BY example_id HAVING count(*) = 1);
    delete from example where example.id not in (select link.example_id from link);
    delete from dataset where dataset.id not in (select link.dataset_id from link);
    VACUUM;

```

Thanks and Regards,  
Swarup Selvaraj.