NoneType object has no attribute __getitem__

[ribbles]

powa-web: 3.0.2

Receive this error and the page at /database/container_one/query/4294199449/overview/ shows up with blank query. This is the only error in the log.

[E 160204 23:54:53 web:1496] Uncaught exception GET /metrics/database/container_one/query/4294199449/indexes?from=2016-02-04+14%3A56%3A40-0800&to=2016-02-04+15%3A56%3A40-0800 (ip)
    HTTPServerRequest(protocol='http', host='powa:5699', method='GET', uri='/metrics/database/container_one/query/4294199449/indexes?from=2016-02-04+14%3A56%3A40-0800&to=2016-02-04+15%3A56%3A40-0800', version='HTTP/1.1', remote_ip='ip', headers={})
    Traceback (most recent call last):
      File "/usr/lib/python2.7/dist-packages/tornado/web.py", line 1413, in _execute
        result = method(*self.path_args, **self.path_kwargs)
      File "/usr/local/lib/python2.7/dist-packages/powa/query.py", line 178, in get
        'quals')
      File "/usr/local/lib/python2.7/dist-packages/powa/sql/__init__.py", line 243, in resolve_quals
        attname = attnames["%s.%s" % (v["relid"], v["attnum"])]
    TypeError: 'NoneType' object has no attribute '__getitem__'

[rjuju]

Hello,

Do you mean that the query page didn't show any information, or only the query text was missing?

FWIW, this error is about index suggestion, so only the "Predicates" tab should have some error.

[ribbles]

The query text, detail, and information on each of the tabs. All the static HTML was visible.

[rjuju]

Sorry for the delay.

Can you post the sql query text? You can retrieve it with the following query on powa database:

SELECT s.query
FROM powa_statements s
JOIN pg_database d on s.dbid = d.oid
WHERE d.datname = 'powa'
AND s.queyid = 4294199449

[nghayal]

Hit the same error on the Predicates query page:

HTTPServerRequest(protocol='http', host='10.118.230.4:9000', method='GET', uri='/metrics/database/consulting_deloitte_com/query/2603134150/indexes?from=2017-06-20+14%3A31%3A54-0400&to=2017-06-20+15%3A31%3A54-0400', version='HTTP/1.1', remote_ip='10.21.188.150', headers={'Accept-Language': 'en-US,en;q=0.8', 'Accept-Encoding': 'gzip, deflate, sdch', 'X-Requested-With': 'XMLHttpRequest', 'Host': '10.118.230.4:9000', 'Accept': '*/*', 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36', 'Connection': 'keep-alive', 'Referer': 'http://10.118.230.4:9000/database/consulting_deloitte_com/query/2603134150/overview/', 'Cookie': 'username="2|1:0|10:1497981045|8:username|8:cG93YQ==|4a346d2d8257d689e814852e84cd5e558e8170a30886b58c90795ffc8b07e127"; password="2|1:0|10:1497981045|8:password|20:ZGRycHBvd2FhZG1pbg==|f87259b1682a7cc4fdda4c04ff1bc10f2e9020bb0bfde815d9912124871d3ac9"; server="2|1:0|10:1497981045|6:server|20:VUFULUNvbnN1bHRpbmc=|fa19ee197f0ddc3054b12d6e687c91b60cc54148d287ae4d3c1b8cb958c0f55d"; _flashes="2|1:0|10:1497987114|8:_flashes|4:Ti4=|9fa8fc91276eb314255c1d49bb2f53f3b7f4888a17609c19e451a207c2942eb2"'})
Traceback (most recent call last):
  File "/usr/lib64/python2.7/site-packages/tornado/web.py", line 1509, in _execute
    result = method(*self.path_args, **self.path_kwargs)
  File "/usr/lib/python2.7/site-packages/powa/query.py", line 207, in get
    self.get_argument("to"))
  File "/usr/lib/python2.7/site-packages/powa/sql/__init__.py", line 402, in get_any_sample_query
    _from, _to, 'most executed')
  File "/usr/lib/python2.7/site-packages/powa/sql/__init__.py", line 374, in get_unjumbled_query
    values[kind].get('constants', []))
TypeError: 'NoneType' object has no attribute '__getitem__'

powa=# SELECT s.query FROM powa_statements s JOIN pg_database d on s.dbid = d.oid WHERE d.datname = 'powa' AND s.queryid = 2603134150; query

(0 rows)

[rjuju]

Hello,

Sorry I made a mistake in my previous answer, you have to specify your database name, ie.

SELECT s.query
FROM powa_statements s
JOIN pg_database d on s.dbid = d.oid
WHERE d.datname = 'consulting_deloitte_com'
AND s.queryid = 2603134150;

[nghayal]

powa=# SELECT s.query powa-# FROM powa_statements s powa-# JOIN pg_database d on s.dbid = d.oid powa-# WHERE d.datname = 'consulting_deloitte_com' powa-# AND s.queryid = 2603134150; query

                                                                                   +

SELECT json_object_agg(oid, value) + FROM ( + + SELECT oproid as oid, json_build_object( + ?, oprname, + ?, + coalesce( + json_object_agg(am, opclass_oids::jsonb + ORDER BY am) + FILTER (WHERE am is NOT NULL)), + ?, + coalesce( + json_object_agg( + amname, + opclass_names ORDER BY am) + FILTER (WHERE am is NOT NULL))) as value + FROM + ( + SELECT oprname, pg_operator.oid as oproid, + pg_am.oid as am, to_json(array_agg(distinct c.oid)) as opclass_oids, + amname, + to_json(array_agg(distinct CASE + WHEN opcdefault IS TRUE THEN ? + WHEN opcdefault IS FALSE THEN opcname + ELSE ? END)) as opclass_names + FROM + pg_operator + LEFT JOIN pg_amop amop ON amop.amopopr = pg_operator.oid + LEFT JOIN pg_am ON amop.amopmethod = pg_am.oid AND pg_am.amname != ? + LEFt JOIN pg_opfamily f ON f.opfmethod = pg_am.oid AND amop.amopfamily = f.oid+ LEFT JOIN pg_opclass c ON c.opcfamily = f.oid + WHERE pg_operator.oid in (?, ?) + GROUP BY pg_operator.oid, oprname, pg_am.oid, amname + ) by_am + GROUP BY oproid, oprname + ) detail +

(1 row)

[rjuju]

Ok. You have a different issue than the OP.

The problem with your query is that it has some constants in the SELECT clause, which by definition isn't tracked by pg_qualstats, and thus can't be un-normalized.

We keep track of one not-normalized query for each queryid, so we could return it if available. However, we can't in this case distinguish most frequent, most filtering... quals. We'll see which workaround is best and fix this case.

[rjuju]

This problem being tracked in https://github.com/powa-team/powa-web/issues/14, I'm closing this issue.

[Paulpatou]

Hello, I have a similar error in the powa collector debug. I use powa-collector 1.0.0 and powa-web 4.0.0 Debug Powa-collector:

WARNING: Could not retrieve last snapshot time: 'NoneType' object has no attribute '__getitem__'

Debug Powa-web:

[E 200526 08:31:00 web:1670] Uncaught exception GET /server/8/config_changes/?from=2020-05-26+07%3A30%3A59%2B0200&to=2020-05-26+08%3A30%3A59%2B0200 (192.168.1.107)
    HTTPServerRequest(protocol='http', host='192.168.1.14:8888', method='GET', uri='/server/8/config_changes/?from=2020-05-26+07%3A30%3A59%2B0200&to=2020-05-26+08%3A30%3A59%2B0200', version='HTTP/1.1', remote_ip='192.168.1.107')
    Traceback (most recent call last):
      File "/home/utilisateur/.local/lib/python2.7/site-packages/tornado/web.py", line 1590, in _execute
        result = method(*self.path_args, **self.path_kwargs)
      File "/home/utilisateur/powa-web-4.0.0/powa/dashboards.py", line 95, in get
        values = self.execute(query, params=url_params)
      File "/home/utilisateur/powa-web-4.0.0/powa/framework.py", line 343, in execute
        return engine.execute(query, **params)
      File "/home/utilisateur/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2182, in execute
        return connection.execute(statement, *multiparams, **params)
      File "/home/utilisateur/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute
        return meth(self, multiparams, params)
      File "/home/utilisateur/.local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/home/utilisateur/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
        distilled_params,
      File "/home/utilisateur/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context
        e, statement, parameters, cursor, context
      File "/home/utilisateur/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
        util.raise_from_cause(sqlalchemy_exception, exc_info)
      File "/home/utilisateur/.local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=cause)
      File "/home/utilisateur/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
        cursor, statement, parameters, context
      File "/home/utilisateur/.local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 581, in do_execute
        cursor.execute(statement, parameters)
    InternalError: (psycopg2.errors.InternalError_) ERREUR:  failed to find conversion function from unknown to text

    [SQL: SELECT * FROM
    (
      WITH src AS (
        select ts, name,
        lag(setting_pretty) OVER (PARTITION BY name ORDER BY ts) AS prev_val,
        setting_pretty AS new_val,
        lag(is_dropped) OVER (PARTITION BY name ORDER BY ts) AS prev_is_dropped,
        is_dropped as is_dropped
        FROM public.pg_track_settings_history h
        WHERE srvid = %(server)s
        AND ts <= %(to)s
      )
      SELECT extract("epoch" FROM ts) AS ts, 'global' AS kind,
      json_build_object(
        'name', name,
        'prev_val', prev_val,
        'new_val', new_val,
        'prev_is_dropped', coalesce(prev_is_dropped, true),
        'is_dropped', is_dropped
      ) AS data
      FROM src
      WHERE ts >= %(from)s AND ts <= %(to)s
    ) AS global

    UNION ALL

    SELECT * FROM
    (
      WITH src AS (
        select ts, name,
        lag(setting) OVER (PARTITION BY name, setdatabase, setrole ORDER BY ts) AS prev_val,
        setting AS new_val,
        lag(is_dropped) OVER (PARTITION BY name, setdatabase, setrole ORDER BY ts) AS prev_is_dropped,
        is_dropped as is_dropped,
        d.datname,
        h.setrole
        FROM public.pg_track_db_role_settings_history h
        LEFT JOIN public.powa_databases d
          ON d.srvid = h.srvid
          AND d.oid = h.setdatabase
        WHERE h.srvid = %(server)s

        AND ts <= %(to)s
      )
      SELECT extract("epoch" FROM ts) AS ts, 'rds' AS kind,
      json_build_object(
        'name', name,
        'prev_val', prev_val,
        'new_val', new_val,
        'prev_is_dropped', coalesce(prev_is_dropped, true),
        'is_dropped', is_dropped,
        'datname', datname,
        'setrole', setrole
      ) AS data
      FROM src
      WHERE ts >= %(from)s AND ts <= %(to)s
    ) AS rds

    UNION ALL

    SELECT extract("epoch" FROM ts) AS ts, 'reboot' AS kind,
    NULL AS data
    FROM public.pg_reboot AS r
    WHERE r.srvid = %(server)s
    AND r.ts>= %(from)s
    AND r.ts <= %(to)s
    ORDER BY ts]
    [parameters: {'to': u'2020-05-26 08:30:59+0200', 'from': u'2020-05-26 07:30:59+0200', 'server': u'8'}]
    (Background on this error at: http://sqlalche.me/e/2j85)
[E 200526 08:31:00 web:2162] 500 GET /server/8/config_changes/?from=2020-05-26+07%3A30%3A59%2B0200&to=2020-05-26+08%3A30%3A59%2B0200 (192.168.1.107) 24.25ms

Thanks for help

[rjuju]

Hello @Paulpatou

As far as I can see this is an unrelated issue, and it's coming from powa-web, not powa-collector.

This is caused by the error ERREUR: failed to find conversion function from unknown to text. What version of postgres are you using?

[Paulpatou]

I've edited my post, I forgot the error message for powa-collector

[Paulpatou]

The powa base is in 9.6 The server remotes are in 9.4

[rjuju]

About the powa-collector message, do you have the full stack trace? Also, is this preventing powa-collector from working, and is the message emitted each time to restart powa-collector?

[Paulpatou]

Indeed the message appears only at startup, when I reload powa-collector there is no error.

Full trace: `2020-05-26 11:57:46,599 - INFO : Starting powa-collector... 2020-05-26 11:57:46,599 - DEBUG : Connecting on repository... 2020-05-26 11:57:46,611 - DEBUG : Connected. 2020-05-26 11:57:46,617 192.168.1.203:5432 DEBUG : Creating worker 192.168.1.203:5432: {'dsn': {'host': '192.168.1.203', 'password': 'Motdepasse', 'port': 5432, 'dbname': 'Nomdelabase', 'user': 'powa'}, 'frequency': 300, 'srvid': 9}

2020-05-26 11:57:46,617 192.168.1.203:5432 INFO : Starting worker 2020-05-26 11:57:46,617 192.168.1.38:5432 DEBUG : Creating worker 192.168.1.38:5432: {'dsn': {'host': '192.168.1.38', 'password': 'Motdepasse', 'port': 5432, 'dbname': 'Nomdelabase', 'user': 'powa'}, 'frequency': 300, 'srvid': 8}

2020-05-26 11:57:46,617 192.168.1.203:5432 DEBUG : Connecting on repository... 2020-05-26 11:57:46,618 192.168.1.38:5432 INFO : Starting worker 2020-05-26 11:57:46,618 - INFO : List of workers: 2020-05-26 11:57:46,619 - INFO : 192.168.1.203:5432: {'host': '192.168.1.203', 'password': 'Motdepasse', 'port': 5432, 'dbname': 'Nomdelabase', 'user': 'powa'} (no connection to remote server)

2020-05-26 11:57:46,619 - INFO : 192.168.1.38:5432: {'host': '192.168.1.38', 'password': 'Motdepasse', 'port': 5432, 'dbname': 'Nomdelabase', 'user': 'powa'} (no connection to remote server)

2020-05-26 11:57:46,618 192.168.1.38:5432 DEBUG : Connecting on repository... 2020-05-26 11:57:46,629 192.168.1.203:5432 DEBUG : Connected. 2020-05-26 11:57:46,629 192.168.1.38:5432 DEBUG : Connected. 2020-05-26 11:57:46,632 192.168.1.38:5432 DEBUG : Connecting on remote database... 2020-05-26 11:57:46,633 192.168.1.203:5432 DEBUG : Connecting on remote database... 2020-05-26 11:57:46,641 192.168.1.38:5432 DEBUG : Connected. 2020-05-26 11:57:46,642 192.168.1.203:5432 DEBUG : Connected. 2020-05-26 11:57:46,648 192.168.1.203:5432 WARNING: Could not retrieve last snapshot time: 'NoneType' object has no attribute 'getitem'

2020-05-26 11:57:46,650 192.168.1.203:5432 INFO : No datasource configured for server 9 2020-05-26 11:57:46,650 192.168.1.203:5432 DEBUG : Committing transaction 2020-05-26 11:57:46,675 192.168.1.38:5432 WARNING: Could not retrieve last snapshot time: 'NoneType' object has no attribute 'getitem'

2020-05-26 11:57:46,677 192.168.1.38:5432 INFO : No datasource configured for server 8 2020-05-26 11:57:46,678 192.168.1.38:5432 DEBUG : Committing transaction 2020-05-26 11:58:23,806 - DEBUG : Received async command: RELOAD r12769 [''] 2020-05-26 11:58:23,806 - INFO : List of workers: 2020-05-26 11:58:23,806 - INFO : 192.168.1.203:5432: {'host': '192.168.1.203', 'password': 'Motdepasse', 'port': 5432, 'dbname': 'Nomdelabase', 'user': 'powa'} (running)

2020-05-26 11:58:23,806 - INFO : 192.168.1.38:5432: {'host': '192.168.1.38', 'password': 'Motdepasse', 'port': 5432, 'dbname': 'Nomdelabase', 'user': 'powa'} (running)

2020-05-26 11:58:23,806 - INFO : Reloading... 2020-05-26 11:58:23,807 - INFO : Reload done `

[rjuju]

Apparently there's something wrong with this server configuration, as not datasource is found and probably the cause for the WARNING is that there's no row in powa_snapshot_meta table.

How did you register those two remote servers?

[Paulpatou]

I had modified the hostname directly in the database, I think that's why I had these errors. I restored a backup and modified the hostname with SELECT powa_configure_server

I don't have any more mistakes, so everything looks okay. Thanks for the help.

[rjuju]

Ok, thanks for the confirmation. I'll add some extra checks in powa-collector to detect this case and raise an appropriate hint.

[rjuju]

Done in https://github.com/powa-team/powa-collector/commit/8bdb65b46a8cbc5a293ce1643e11250e48b7765f