SQL importer web UI should return the DatabaseServiceException message instead of generic error

[braiam]

I use openrefine to import projects directly from a database to process it and exporting as a file to share with others. I created a custom user for this to reduce the possibility of a bug or attack vector that would modify/damage the database. That means that I have to explicitly grant permissions on tables/schemas. Sometimes I forget this step, and the logs accurately reflect this issue:

refine[730]: com.google.refine.extension.database.DatabaseServiceException: ERROR: permission denied for table name_table

But the message in the web UI is just a generic "error:Bad Request" without further details.

Proposed solution

If there's an error, the top error message (without the at details) should be returned along the "error:Bad Request" via the UI, so that users are informed of the issue and work out the solution. It would provide immediate actionable feedback of what is wrong with the query, so they can fix it.

Alternatives considered

While the error logs do point out the problem, in case of a shared instance where there isn't an easy way to access the box where openrefine resides, it would take time to figure out if the SQL query is correct, login the remote box, pull the logs, find the specific message, and fix the issue with the database.

[thadguidry]

Agreed, we should probably propagate the real error message so that it's more useful.
@braiam Do we also need to make any changes to the message box UI itself? centering, text size, anything else?

[braiam]

Not really, just more information about the error.

[aileenpalafox]

Hi, first time working on this project. Can I give it a try? @braiam

[braiam]

@aileenpalafox this is a open source project, so break a leg!

[thadguidry]

@aileenpalafox Thanks! Let us know in this issue if you have questions or you can ask on our dev mailing list

[aileenpalafox]

Hello, again @thadguidry I faced some problems when trying to solve this one and ran out of time so I'm gonna drop it for now. In case anyone else want to work on it they can give it a try.

[thadguidry]

@aileenpalafox No problem, perhaps you can work on another issue we have marked with a label called "good first issue"? If not and you are too busy, we certainly understand and thanks anyways for offering to help!

[aileenpalafox]

Thank you for the offer @thadguidry, right now I've other stuff going on so I'll have to pass. Good luck with the project!

[wetneb]

Posted by @bhargavii on our Gitter channel

I'm new to this project, so was looking into issues with good first issue tag,I came across the "#4007 SQL importer web UI should return the DatabaseServiceException message instead of generic error" issue ,
when I tried replicating the error, the message in the web UI isn't generic "error:Bad Request", instead it is specifying the corresponding SQL query followed by "command denied to user".
So I believe this issue doesn't persist anymore.
If it does persist, can I know which folders do I have to look into in order to resolve this.

[braiam]

@wetneb Are we sure? I'm using version 3.5-beta1 [1b9907e] and still can reproduce the issue. Is using master recommended? With a postgresql database:

Aug 31 06:23:48 dtbhome refine[766]: 06:23:48.894 [         TestQueryCommand] TestQueryCommand::Post::DatabaseServiceException::{} (4ms)
Aug 31 06:23:48 dtbhome refine[766]: com.google.refine.extension.database.DatabaseServiceException: ERROR: permission denied for table cerdef2021
Aug 31 06:23:48 dtbhome refine[766]:         at com.google.refine.extension.database.pgsql.PgSQLDatabaseService.testQuery(PgSQLDatabaseService.java:229)
Aug 31 06:23:48 dtbhome refine[766]:         at com.google.refine.extension.database.cmd.TestQueryCommand.doPost(TestQueryCommand.java:82)
Aug 31 06:23:48 dtbhome refine[766]:         at com.google.refine.RefineServlet.service(RefineServlet.java:187)
Aug 31 06:23:48 dtbhome refine[766]:         at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)

[wetneb]

@braiam thanks for the confirmation! I don't know if @bhargavii is still interested in working on this, perhaps this additional information will help them understand how to reproduce the issue.