upgrade from v6 to v7, users tab cannot be opened due to exceptions

[truebit]

I manual installed v6, and then upgrade to v7 manually using manage.py db upgrade.
Anything else is OK, but Users tab cannot be opened in browser.
Below is the stack trace and logs:

[2019-04-01 11:42:25,806][PID:116370][DEBUG][metrics] table=organizations query=select duration=1.67
[2019-04-01 11:42:25,807][PID:116370][DEBUG][root] Current organization: ABQA (1) (slug: default)
[2019-04-01 11:42:25,811][PID:116370][DEBUG][metrics] table=users query=select duration=1.82
[2019-04-01 11:42:25,819][PID:116370][DEBUG][metrics] table=users query=select duration=1.74
[2019-04-01 11:42:25,828][PID:116370][DEBUG][metrics] table=alerts query=select duration=1.94
[2019-04-01 11:42:25,836][PID:116370][DEBUG][metrics] table=data_sources query=select duration=1.93
[2019-04-01 11:42:25,849][PID:116370][DEBUG][metrics] table=queries query=select duration=2.61
[2019-04-01 11:42:25,857][PID:116370][DEBUG][metrics] table=dashboards query=select duration=1.44
[2019-04-01 11:42:25,857][PID:116370][INFO][metrics] method=GET path=/api/organization/status endpoint=redash_organization_status status=200 content_type=application/json content_length=96 duration=45.42 query_count=7 query_duration=13.14
[2019-04-01 11:42:25,906][PID:116393][DEBUG][metrics] table=organizations query=select duration=1.49
[2019-04-01 11:42:25,906][PID:116393][DEBUG][root] Current organization: ABQA (1) (slug: default)
[2019-04-01 11:42:25,910][PID:116393][DEBUG][metrics] table=users query=select duration=1.72
[2019-04-01 11:42:25,913][PID:116393][DEBUG][metrics] table=groups query=select duration=1.25
[2019-04-01 11:42:25,916][PID:116393][DEBUG][metrics] table=groups query=select duration=1.21
[2019-04-01 11:42:25,931] ERROR in app: Exception on /api/users [GET]
Traceback (most recent call last):
  File "/home/redash/env/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/redash/env/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/redash/env/lib/python2.7/site-packages/flask_restful/__init__.py", line 477, in wrapper
    resp = resource(*args, **kwargs)
  File "/home/redash/env/lib/python2.7/site-packages/flask_login/utils.py", line 228, in decorated_view
    return func(*args, **kwargs)
  File "/home/redash/env/lib/python2.7/site-packages/flask/views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/home/redash/redash-7.0.0/redash/handlers/base.py", line 31, in dispatch_request
    return super(BaseResource, self).dispatch_request(*args, **kwargs)
  File "/home/redash/env/lib/python2.7/site-packages/flask_restful/__init__.py", line 587, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/home/redash/redash-7.0.0/redash/permissions.py", line 48, in decorated
    return fn(*args, **kwargs)
  File "/home/redash/redash-7.0.0/redash/handlers/users.py", line 114, in get
    return paginate(users, page, page_size, serialize_user)
  File "/home/redash/redash-7.0.0/redash/handlers/base.py", line 91, in paginate
    count = query_set.count()
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3230, in count
    return self.from_self(col).scalar()
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2983, in scalar
    ret = self.one()
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2954, in one
    ret = self.one_or_none()
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2924, in one_or_none
    ret = list(self)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2995, in __iter__
    return self._execute_and_instances(context)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3018, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
ProgrammingError: <unprintable ProgrammingError object>
[2019-04-01 11:42:25,931][PID:116393][ERROR][redash] Exception on /api/users [GET]
Traceback (most recent call last):
  File "/home/redash/env/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/redash/env/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/redash/env/lib/python2.7/site-packages/flask_restful/__init__.py", line 477, in wrapper
    resp = resource(*args, **kwargs)
  File "/home/redash/env/lib/python2.7/site-packages/flask_login/utils.py", line 228, in decorated_view
    return func(*args, **kwargs)
  File "/home/redash/env/lib/python2.7/site-packages/flask/views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/home/redash/redash-7.0.0/redash/handlers/base.py", line 31, in dispatch_request
    return super(BaseResource, self).dispatch_request(*args, **kwargs)
  File "/home/redash/env/lib/python2.7/site-packages/flask_restful/__init__.py", line 587, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/home/redash/redash-7.0.0/redash/permissions.py", line 48, in decorated
    return fn(*args, **kwargs)
  File "/home/redash/redash-7.0.0/redash/handlers/users.py", line 114, in get
    return paginate(users, page, page_size, serialize_user)
  File "/home/redash/redash-7.0.0/redash/handlers/base.py", line 91, in paginate
    count = query_set.count()
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3230, in count
    return self.from_self(col).scalar()
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2983, in scalar
    ret = self.one()
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2954, in one
    ret = self.one_or_none()
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2924, in one_or_none
    ret = list(self)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2995, in __iter__
    return self._execute_and_instances(context)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3018, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/redash/env/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
ProgrammingError: <unprintable ProgrammingError object>
[2019-04-01 11:42:25,933][PID:116393][INFO][metrics] method=GET path=/api/users endpoint=users status=500 content_type=application/json content_length=36 duration=22.26 query_count=4 query_duration=5.67

[truebit]

According to

 File "/home/redash/redash-7.0.0/redash/handlers/users.py", line 114, in get
    return paginate(users, page, page_size, serialize_user)

I tried to print out users itself, get weird SQL statement:

SELECT users.profile_image_url AS users_profile_image_url, users.groups AS users_groups, users.updated_at AS users_updated_at, users.created_at AS users_created_at, users.id AS users_id, users.org_id AS users_org_id, users.name AS users_name, users.email AS users_email, users.password_hash AS users_password_hash, users.api_key AS users_api_key, users.disabled_at AS users_disabled_at, users.details AS users_details
FROM users
WHERE %(param_1)s = users.org_id AND users.disabled_at IS NULL AND CAST((users.details ->> %(details_1)s) AS BOOLEAN) IS NOT true ORDER BY users.created_at DESC

[ranbena]

Hey @truebit, as noted in the github issue template, we use GitHub only for bug reports
but for support questions plz use https://discuss.redash.io/c/support, so go ahead and copy this over to there.

Also, plz make sure to read the v7 upgrade notes here https://discuss.redash.io/t/redash-v7-released/3477

[arikfr]

@truebit also make sure that you applied migrations. It looks like you're missing a column.

[truebit]

I finally find the cause. It's because the postgresql version.
I installed redash v6 with postgresql 9.2.24 on centos 7.
Now redash v7 requires postgresql 9.5 and above.
After upgrade postgresql to 9.6 and migrated sql data, it works now