mysql error for drop_dataset and get_dataset

[sked]

Hi,

I have a mysql database set up. The name of the database is ‘prodigy’

db.add_dataset(‘test_dataset’) works fine.

But when I try db.get_dataset(‘test_dataset’) or db.drop_dataset(‘test_dataset’),
I get the below error:

Traceback (most recent call last):
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1014, in _read_packet
    packet.check_error()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1146, "Table 'prodigy.link' doesn't exist")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/prodigy/components/db.py", line 231, in get_dataset
    .where(Dataset.id == dataset.id)).execute()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/peewee.py", line 3274, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/peewee.py", line 2939, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/peewee.py", line 3837, in execute_sql
    self.commit()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/peewee.py", line 3656, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/peewee.py", line 135, in reraise
    raise value.with_traceback(tb)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1014, in _read_packet
    packet.check_error()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
peewee.ProgrammingError: (1146, "Table 'prodigy.link' doesn't exist")

Thank you!

[ines (Ines Montani)]

Hmm, it looks like the tables weren't created correctly. Did you give permission for CREATE operation? Alternatively, you can also set up the tables Dataset, Example and Link manually.

The PRODIGY_README.html has a section on this with more details – also copying it over for reference:

Permissions and user privileges

When setting up your database integration, you might not want to give your
user permission to perform all operations. Prodigy uses the following
operations, some of which are optional and only required for certain commands
or initial setup:

Operation	Required	Details
SELECT		Retrieving datasets and annotations.
INSERT		Adding datasets and annotations.
UPDATE		Updating datasets and annotations.
DELETE		Deleting datasets. Only used for the prodigy drop command, so permission can be omitted if you don't need this feature or prefer to delete records manually.
CREATE		Creation of tables Dataset, Example and Link. Not required if you create them manually.

To test your database connection, you can also write a simple Python script that
connects to Prodigy's database and performs the most important operations.
For more details, see the database API docs or check out
this thread on the support forum.

from prodigy.components.db import connect   # import the database connector

db = connect()                              # uses the settings in your prodigy.json
db.add_dataset('test_dataset')              # add a dataset
assert 'test_dataset' in db                 # check that the dataset was added
examples = [{'text': 'hello world', '_task_hash': 123, '_input_hash': 456}]
db.add_examples(examples, ['test_dataset'])  # add examples to the dataset
dataset = db.get_dataset('test_dataset')     # retrieve a dataset
assert len(dataset) == 1                     # check that the examples were added

[sked]

It was a permissions issue and is working now.

Thank you.