Unable to run kolibri docker container with postgres container

Manish · July 22, 2021, 2:06pm

Hi,

I am trying to connect kolibri container with postgresql container and getting an error as below

“sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: character varying = integer
LINE 3: …eduledjobs.queue = ‘kolibri’ AND scheduledjobs.id = 0) AS an…”
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

[SQL: SELECT EXISTS (SELECT 1
FROM scheduledjobs
WHERE scheduledjobs.queue = %(queue_1)s AND scheduledjobs.id = %(id_1)s) AS anon_1]
The environment variable related to postgres are overridden in entrypoint.py file.

Technical details

Version: 15
OS: Linux (GCP VM)

Could you please suggest what is missing?
I have connected kolibri with postgres without container and it worked fine.

Best Regards,
Manish Deshmukh

richard · July 22, 2021, 3:48pm

This is on the develop branch?

Does the same setup work on 0.14?

I ask, as my first guess is that this might be a bug related to updates to the task system in the develop branch.

I assume you are setting the following environment variables:

KOLIBRI_DATABASE_ENGINE - Which database backend to use, choices are 'sqlite' or 'postgresql'

KOLIBRI_DATABASE_NAME - 
                For SQLite - the name of a database file to use for the main Kolibri database.
                For Postgresql, the name of the database to use for all Kolibri data.
            

KOLIBRI_DATABASE_PORT - The port on which to connect to the database, Postgresql only.

KOLIBRI_DATABASE_PASSWORD - The password to authenticate with when connecting to the database, Postgresql only.

KOLIBRI_DATABASE_HOST - The host on which to connect to the database, Postgresql only.

KOLIBRI_DATABASE_USER - The user to authenticate with when connecting to the database, Postgresql only.

Manish · July 23, 2021, 3:12am

Hi Richard,

Thanks, Yes I am setting up those environment variables. However the value “postgresql” against KOLIBRI_DATABASE_ENGINE throws an error so I have used “prostgres”, and assume that does not relate with the issue.
Version 0.14 also throws same error. I shall try 0.13 today to see if that works.

Best Regards,
Manish Deshmukh

aronasorman · July 23, 2021, 2:47pm

Hi @Manish! Aron here, one of the infrastructure engineers for Learning Equality.

Do you know what the error is when you use postgresql? It may be attempting to connect but other issues may prevent it from connecting.

Manish · July 23, 2021, 4:36pm

Hi Aron,

I get the following error:
“ERROR Error processing environment variable option KOLIBRI_DATABASE_ENGINE: the value “postgresql” is unacceptable.
CRITICAL Aborting: Could not process options config (see errors above for more details)
error Command failed with exit code 1.”

Best Regards,
Manish Deshmukh

richard · July 23, 2021, 5:02pm

This is expected - the allowed values are sqlite (the default) and postgres.

I think @aronasorman was looking for more detail of the error you saw when it was correctly configured though?

Manish · July 23, 2021, 5:15pm

Hi Richard, Aron,

Apologies for the confusion and such a big message below.

Complete error message from the logs.

ERROR ENGINE Error in ‘start’ listener <bound method ServicesPlugin.start of <kolibri.utils.server.ServicesPlugin object at 0x7feff7a2f4e0>>
Traceback (most recent call last):
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py”, line 1284, in _execute_context
cursor, statement, parameters, context
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py”, line 590, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedFunction: operator does not exist: character varying = integer
LINE 3: …eduledjobs.queue = ‘kolibri’ AND scheduledjobs.id = 0) AS an…
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File “/usr/local/lib/python3.6/dist-packages/cherrypy/process/wspbus.py”, line 230, in publish
output.append(listener(*args, **kwargs))
File “/kolibri/kolibri/utils/server.py”, line 93, in start
if SCH_PING_JOB_ID not in scheduler:
File “/usr/local/lib/python3.6/dist-packages/django/utils/functional.py”, line 239, in inner
return func(self._wrapped, *args)
File “/kolibri/kolibri/core/tasks/scheduler.py”, line 79, in contains
self._ns_query(session).filter_by(id=job_id).exists()
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py”, line 3469, in scalar
ret = self.one()
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py”, line 3436, in one
ret = self.one_or_none()
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py”, line 3405, in one_or_none
ret = list(self)
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py”, line 3481, in iter
return self._execute_and_instances(context)
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py”, line 3506, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py”, line 1020, in execute
return meth(self, multiparams, params)
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py”, line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py”, line 1139, in _execute_clauseelement
distilled_params,
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py”, line 1324, in execute_context
e, statement, parameters, cursor, context
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py”, line 1518, in handle_dbapi_exception
sqlalchemy_exception, with_traceback=exc_info[2], from=e
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py”, line 178, in raise
raise exception
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py”, line 1284, in _execute_context
cursor, statement, parameters, context
File “/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py”, line 590, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: character varying = integer
LINE 3: …eduledjobs.queue = ‘kolibri’ AND scheduledjobs.id = 0) AS an…
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

[SQL: SELECT EXISTS (SELECT 1
FROM scheduledjobs
WHERE scheduledjobs.queue = %(queue_1)s AND scheduledjobs.id = %(id_1)s) AS anon_1]
(Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)

INFO ENGINE Started monitor thread ‘Autoreloader’.
INFO ENGINE Serving on http://0.0.0.0:8000
INFO ENGINE Serving on http://0.0.0.0:8888
ERROR ENGINE Shutting down due to error in start listener:
Traceback (most recent call last):
File “/usr/local/lib/python3.6/dist-packages/cherrypy/process/wspbus.py”, line 268, in start
self.publish(‘start’)
File “/usr/local/lib/python3.6/dist-packages/cherrypy/process/wspbus.py”, line 248, in publish
raise exc
cherrypy.process.wspbus.ChannelFailures: ProgrammingError("(psycopg2.errors.UndefinedFunction) operator does not exist: character varying = integer\nLINE 3: …eduledjobs.queue = ‘kolibri’ AND scheduledjobs.id = 0) AS an…\n ^\nHINT: No operator matches the given name and argument types. You might need to add explicit type casts.\n",)

INFO ENGINE Bus STOPPING
INFO ENGINE HTTP Server cherrypy._cpwsgi_server.CPWSGIServer((‘0.0.0.0’, 8000)) shut down
INFO ENGINE HTTP Server cheroot.wsgi.Server((‘0.0.0.0’, 8888)) shut down
INFO ENGINE Stopped thread ‘Autoreloader’.
INFO ENGINE Bus STOPPED
INFO ENGINE Bus EXITING
INFO ENGINE Bus EXITED
error Command failed with exit code 70.

Best Regards,
Manish Deshmukh

richard · July 23, 2021, 5:26pm

Is this with latest develop? This bug appears to be the result of a job id being set to an integer rather than a string, but that was fixed in this PR: tests: ensure jobs persist on server restart by vkWeb · Pull Request #8030 · learningequality/kolibri · GitHub

Manish · July 23, 2021, 8:44pm

Hi Richard,

Many thanks!! I wasn’t using the latest . The latest one works fine. I am greatful for you and Aron for swift responses.

Best Regards,
Manish Deshmukh