Checking Solutions with HypoPG does not return

[thijslemmens]

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]

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]

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]

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]

@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]

@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]

For the record, the invalid character problem was probably a bug in pg_qualstats, which I fixed in powa-team/pg_qualstats@ee7255d.

As I said, the problem with queries having const in the SELECT part is known (see #14), so I'm closing this issue and will link this one as a reference.