search

powa-team / powa-web

PoWA user interface
http://powa.readthedocs.io/
73 stars 30 forks source link

Checking Solutions with HypoPG does not return #25

Closed thijslemmens closed 5 years ago

thijslemmens commented 7 years ago

Whenever I use the button "Optimize this database", I get the suggestion of indexes, but the hypopg "simulation" does not run. I'm using version 3.1.2 on Postgres 9.6.2. This is the error message in my log:

postgresql_1 | 2017-04-10 15:29:38 UTC [1891]: [3-1] db=alfresco,user=alfresco,app=[unknown],client=172.18.0.2 ERROR: syntax error at or near "," at character 615 postgresql_1 | 2017-04-10 15:29:38 UTC [1891]: [4-1] db=alfresco,user=alfresco,app=[unknown],client=172.18.0.2 STATEMENT: EXPLAIN select postgresql_1 | cd.id as id, postgresql_1 | cd.version as version, postgresql_1 | cd.content_url_id as content_url_id, postgresql_1 | cu.content_size as content_size, postgresql_1 | cd.content_mimetype_id as content_mimetype_id, postgresql_1 | cd.content_encoding_id as content_encoding_id, postgresql_1 | cd.content_locale_id as content_locale_id postgresql_1 | from postgresql_1 | alf_content_data cd postgresql_1 | join alf_node_properties np on (cd.id = np.long_value) postgresql_1 | left join alf_content_url cu on (cd.content_url_id = cu.id) postgresql_1 | where postgresql_1 | np.node_id in postgresql_1 | (
postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | ) postgresql_1 | and (np.actual_type_n = ? or np.actual_type_n = ?) postgresql_1 | 2017-04-10 15:29:38 UTC [1891]: [5-1] db=alfresco,user=alfresco,app=[unknown],client=172.18.0.2 ERROR: syntax error at or near ")" at character 689 postgresql_1 | 2017-04-10 15:29:38 UTC [1891]: [6-1] db=alfresco,user=alfresco,app=[unknown],client=172.18.0.2 STATEMENT: EXPLAIN select postgresql_1 | COALESCE(np.long_value, node.id) as id, postgresql_1 | node.type_qname_id as type_qname_id, postgresql_1 | node.transaction_id as txn_id, postgresql_1 | node.uuid as uuid, postgresql_1 | node.acl_id as acl_id, postgresql_1 | store.protocol as protocol, postgresql_1 | store.identifier as identifier, postgresql_1 |
postgresql_1 | shardkey.string_value as shard_key postgresql_1 |
postgresql_1 | from postgresql_1 | alf_transaction txn postgresql_1 | join alf_node node on (txn.id = node.transaction_id) postgresql_1 | join alf_store store on (store.id = node.store_id) postgresql_1 | left outer join alf_node_properties np on (np.node_id = node.id and np.qname_id = ?) postgresql_1 |
postgresql_1 | left outer join alf_node_properties shardkey on (shardkey.node_id = node.id and shardkey.qname_id = ?) postgresql_1 |
postgresql_1 | WHERE txn.id in postgresql_1 | ( postgresql_1 | ? postgresql_1 | , postgresql_1 | ? postgresql_1 | ) postgresql_1 |
postgresql_1 |
postgresql_1 |
postgresql_1 |
postgresql_1 | and store.protocol = ? postgresql_1 |
postgresql_1 |
postgresql_1 | and store.identifier = ? postgresql_1 | order by node.id ASC powa-web_1 | [E 170410 15:29:38 web:1548] Uncaught exception POST /database/alfresco/suggest/ (10.0.1.109) powa-web_1 | HTTPServerRequest(protocol='http', host='localhost:7777', method='POST', uri='/database/alfresco/suggest/', version='HTTP/1.1', remote_ip='10.0.1.109', headers={'Origin': 'http://localhost:7777', 'Content-Length': '37401', 'Accept-Language': 'en-US,en;q=0.8,nl;q=0.6,fr;q=0.4', 'Accept-Encoding': 'gzip, deflate, br', 'Host': 'localhost:7777', 'Accept': '/', 'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.98 Safari/537.36', 'Connection': 'keep-alive', 'X-Requested-With': 'XMLHttpRequest', 'Referer': 'http://localhost:7777/database/alfresco/overview/', 'Cookie': 'csrftoken=JRAs3ugp0BPJgqA82uDLyOSZ5uXSMPpd; _ga=GA1.1.1375522710.1480416893; Drupal.toolbar.collapsed=0; Idea-b4bf169f=0b75f030-f202-467f-9152-eb11b0fbac7b; username="2|1:0|10:1491825422|8:username|12:YWxmcmVzY28=|5a3f9552129db18592e79cbf5e94d6c7e554c67b16f5ddd4d86440370a68e05e"; password="2|1:0|10:1491825422|8:password|8:YWRtaW4=|0f922c78c26aba43ab43471eea6d478214eb5be91908677dc3d14b7e57031eba"; server="2|1:0|10:1491825422|6:server|8:ZG9ja2Vy|c0f7896cda687c3cf481a881ec952f49ea4ec82fa9763b07965a0d9d1ab487a2"; _flashes="2|1:0|10:1491838170|8:_flashes|4:Ti4=|e711fd6a2f0fc5fd04e189b2e62b06088e4dd16ef07b05a9d4433aef4a5edaf8"', 'Content-Type': 'application/json'}) powa-web_1 | Traceback (most recent call last): powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/tornado/web.py", line 1467, in _execute powa-web_1 | result = method(*self.path_args, *self.path_kwargs) powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/powa/wizard.py", line 58, in post powa-web_1 | to_date) powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/powa/sql/init.py", line 402, in get_any_sample_query powa-web_1 | _from, _to, 'most executed') powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/powa/sql/init.py", line 366, in get_unjumbled_query powa-web_1 | queries=[queryid]) powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/powa/sql/init.py", line 446, in qualstat_get_figures powa-web_1 | row = quals.first() powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/result.py", line 1202, in first powa-web_1 | self.cursor, self.context) powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1405, in _handle_dbapi_exception powa-web_1 | util.reraise(exc_info) powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/result.py", line 1198, in first powa-web_1 | row = self._fetchone_impl() powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/result.py", line 1055, in _fetchone_impl powa-web_1 | return self.cursor.fetchone() postgresql_1 | 2017-04-10 15:29:38 UTC [1891]: [7-1] db=alfresco,user=alfresco,app=[unknown],client=172.18.0.2 LOG: disconnection: session time: 0:00:00.064 user=alfresco database=alfresco host=172.18.0.2 port=49448 powa-web_1 | File "/usr/local/lib/python2.7/dist-packages/psycopg2/_json.py", line 185, in typecast_json postgresql_1 | 2017-04-10 15:29:38 UTC [1890]: [3-1] db=powa,user=alfresco,app=[unknown],client=172.18.0.2 LOG: disconnection: session time: 0:00:00.070 user=alfresco database=powa host=172.18.0.2 port=49446 powa-web_1 | return loads(s) powa-web_1 | File "/usr/lib/python2.7/json/init.py", line 338, in loads powa-web_1 | return _default_decoder.decode(s) powa-web_1 | File "/usr/lib/python2.7/json/decoder.py", line 366, in decode powa-web_1 | obj, end = self.raw_decode(s, idx=_w(s, 0).end()) powa-web_1 | File "/usr/lib/python2.7/json/decoder.py", line 382, in raw_decode powa-web_1 | obj, end = self.scan_once(s, idx) powa-web_1 | UnicodeDecodeError: 'utf8' codec can't decode byte 0xaa in position 81: invalid start byte powa-web_1 | [E 170410 15:29:38 web:1971] 500 POST /database/alfresco/suggest/ (10.0.1.109) 79.36ms

Any idea?

rjuju commented 7 years ago

Hello,

Sorry for long delay.

It looks like you're having two errors: bad SQL query and invalid character. I've no idea if they're related though.

I suppose this is logs from a docker compose setup? Could you share relevant postgres logs when this error occurs?

Can you also say which encoding is used for your alfresco database?

thijslemmens commented 7 years ago

Yes, this is a docker-compose setup. The error in postgres is already included and prefixed with postgresql_1 | The error in POWA is prefixed with powa-web_1 My understanding is that the POWA error is a result from the postgres error.

The encoding of the Alfresco db is UTF8 alfresco=# select * from pg_database where datname='alfresco'; datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl ----------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------- alfresco | 10 | 6 | en_US.utf8 | en_US.utf8 | f | t | -1 | 12403 | 133762921 | 1 | 1663 | (1 row)

kind regards

kemko commented 6 years ago

I'm also run into this issue. It seems that powa-web try to use question marks in EXPLAIN queries. Postgres gives an error with a query like EXPLAIN SELECT ?. The correct query must contain any data according field type (e.g. int, string, array).

rjuju commented 6 years ago

@thijslemmens sorry I somehow lost track of this issue.

Your db is in utf8 encoding, and \xaa is apparently not a valid start byte. I don't know how it can happen, if you still have this issue it could be useful to get a dump of the recorded const for this query.

rjuju commented 6 years ago

@kemko the question marks come from pg_stat_statements normalization. The UI should replace them with const recorded by pg_qualstats, but pg_qualstats only handles parameters in WHERE or JOIN clauses, not on a SELECT clause. So unfortunately, a query containing consts in the SELECT clause won't work for now, it's a known limitation.

rjuju commented 5 years ago

For the record, the invalid character problem was probably a bug in pg_qualstats, which I fixed in https://github.com/powa-team/pg_qualstats/commit/ee7255d72f27131b3b52ca55b0a338fd2bcc71d8.

As I said, the problem with queries having const in the SELECT part is known (see https://github.com/powa-team/powa-web/issues/14), so I'm closing this issue and will link this one as a reference.