sqlalchemy.exc.StatementError when setting up the database with postgres?

[DataPsycho]

My Current Setup: postgres local server: 11.5 , python 3.6, knowledge repo 0.8.8 . My sequential workflow:

• create a local repo: kr_local
• adding a remote repo in kr_local: git init; git remote add origin https://github.com/DataPsycho/knowledge-posts.git
• Export knowledge_repo: export KNOWLEDGE_REPO=kr_local
• install pyscopg2 binary
• Adding db URI to config.py file: postgresql+psycopg2://knowledge_repo:knowledge_repo@localhost:5432/knowledge_repo
• initialised knowledge repo: knowledge_repo init (successful)
• running with db server: knowledge_repo runserver --config config.py

That create all the tables (alembic_version, tables, posts .... votes etc). But when try to connect to the app getting the following error:

Using deploy has the same problem. I am getting the same error.

### Creating Posts
export KNOWLEDGE_REPO=/Users.../KrTest/post_maker
knowledge_repo init
knowledge_repo create ipynb example_post.ipynb
knowledge_repo add example_post.ipynb -p project/example_ipynb
knowledge_repo preview project/example_ipynb
# Git push to the branch and merge with master
### Deploying The App
# Initialize Git in new `live_repo` directory and git pull the resource from master
git init
git remote add origin https://github.com/DataPsycho/knowledge-posts.git
git pull origin master
### deploy the application
export KNOWLEDGE_REPO=/Users/s.../KrTest/live_repo
knowledge_repo init
knowledge_repo deploy --config /Users/..../KnowledgeRepo/config.py

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

[SQL: SELECT tags.description AS tags_description, tags.id AS tags_id, tags.name AS tags_name, tags.created_at AS tags_created_at 
FROM tags 
WHERE tags.name = %(name_1)s 
 LIMIT %(param_1)s]
[parameters: {'name_1': 'private', 'param_1': 1}]
(Background on this error at: http://sqlalche.me/e/e3q8)
ERROR:knowledge_repo.app.app:Exception on /static/modules/bootstrap/js/bootstrap.min.js [GET]

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

[SQL: SELECT tags.description AS tags_description, tags.id AS tags_id, tags.name AS tags_name, tags.created_at AS tags_created_at 
FROM tags 
WHERE tags.name = %(name_1)s 
 LIMIT %(param_1)s]
[parameters: {'name_1': 'private', 'param_1': 1}]
(Background on this error at: http://sqlalche.me/e/e3q8)
....
sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
[SQL: SELECT index_metadata.id AS index_metadata_id, index_metadata.type AS index_metadata_type, index_metadata.name AS index_metadata_name, index_metadata.value AS index_metadata_value, index_metadata.updated_at AS index_metadata_updated_at 
FROM index_metadata 
WHERE index_metadata.type = %(type_1)s AND index_metadata.name = %(name_1)s 
 LIMIT %(param_1)s]
[parameters: [immutabledict({})]]

Also time to time getting the following errors and warning:

stderr: 'fatal: Unable to create '....git/index.lock': File exists.
WARNING:knowledge_repo.app.index:Master indexing thread has died. Restarting...

Though I tried to delete manually the index.lock file but the file does not exist.

If any one knows what is the problem?

[ralbertazzi]

I'm having the same error as well. Did you find a solution?

[DataPsycho]

@ralbertazzi I resolved it in different way by using 2 different config file. The config file with running server with disabled indexing. Another config file with a cron job which run indexing every time we push some notebook to master.

But at the end we did not implement knowledge repo. It has lot other bugs and I do not see any recent commit to knowledge repo any more. So we decided to not to use it. Probably the development has been stopped for KR. Currently we in the company will try to develop something similar but more simpler. We are also evaluating Kyso which has same feature. If kyso works well we will use kyso I guess but its a paid service.

[bulam]

@DataPsycho Sorry this repo was a little inactive last year. I work at Airbnb and I'm planning to be actively maintaining it along with a few others.

I've been fixing some bugs filed in the issues and merging in new features and planning to merge a UI update https://github.com/airbnb/knowledge-repo/blob/b4c534526bdda98e8ac6d32478e2a05a2aeadd88/README.md

If you do end up reconsidering adopting the KR, we would welcome other bug reports and feature requests.

[DataPsycho]

@bulam Hey,
Nice to hear about that. Looking forward. Do you think some how it is possible to add SOS service like OneLogin https://github.com/onelogin/python3-saml in to the app. The we can have user statistics and security much better in the app. I already have implemented Onlelogin one of my Flask app. It was not that complex.

[bulam]

@DataPsycho Yes, you can definitely use OneLogin for signing. In fact we use OneLogin to access the Knowledge Repo at Airbnb. To set it up, you can configure by modifying two configs here

knowledge-repo/knowledge_repo/app/config_defaults.py

Lines 146 to 167 in 7d910d0

	# You can also forgo a fully-fledged sign in process for users by hosting the
	# knowledge repository behind a proxy server that pre-authenticates users, and
	# adds the appropriate user identifier to the http headers of the request. If
	# enabled below, then they take precedence over any other forms of
	# authentication. If the call to `AUTH_MAP_REQUEST_HEADERS` results in a null
	# user identifier, then the authentication flow will fall back to use any of the
	# providers specified above.
	AUTH_USE_REQUEST_HEADERS = False
	# If using headers to authenticate, the following function should be implemented
	# to transform a dictionary of headers into a dictionary of user attributes.
	# Currently only 'identifier', 'avatar_uri', 'name' and 'email' are supported.
	# If this method returns `None`, or `identifier` is not supplied, then the
	# authorization flow will fall back to other authentication methods.
	def AUTH_MAP_REQUEST_HEADERS(headers):
	return {
	# 'identifier': None,
	# 'avatar_uri': None,
	# 'name': None,
	# 'email': None
	}

An example config would be:

# You can also forgo a fully-fledged sign in process for users
--
  | # by hosting the knowledge repository behind a proxy server that
  | # pre-authenticates users, and adds the appropriate user identifier
  | # to the http headers of the request. If the headers are
  | # specified below, then they take precedence over any other forms
  | # of authentication. If they are specified but not populated, then
  | # the authentication flow will fall back to use any of the providers
  | # specified above.
  | AUTH_USER_IDENTIFIER_REQUEST_HEADER = 'auth_header'
  |  
  |  
  | # If the identifier used above needs some transformation to match the canonical
  | # identifier format used in this repository, you can specify a mapping using
  | # the below config option.
  | def AUTH_USER_IDENTIFIER_REQUEST_HEADER_MAPPING(identifier):
  | return user_id

Eventually it would be good to provide official documentation and example code for SSO set up.

[DataPsycho]

Will try that out. But that postgres issue needed to be fixed.

[stephenposhmark]

@DataPsycho your errors are little different but we were running into similar issues when trying to deploy with multiple workers. We were able to resolve our issues by calling dispose on the db engine prior to indexing. It hasn't been tested outside of our environment so YMMV but it's easy to test out:

In knowledge_repo/app/app.py

 @self.before_first_request
        def start_indexing():
            current_app.db.engine.dispose() #add this line
            if self.config['INDEXING_ENABLED']:
                self.start_indexing()

Works for us on Postgres 11, MySQL 5.7/8.0 (single and multi worker deployments) and SQLite (single worker)